目錄表的增刪改查Create指定列插入單行數據+全列插入多行數據+全列插入插入否則更新替換 (replace)Retrieve標準語法SELECT列全列查詢限制顯示條目 limit (分頁查詢)基本語法:指定列查詢select 查詢欄位為表達式表達式重命名去重WHERE 條件比較運算符邏輯運算符案例 ...
目錄
表的增刪改查
CRUD : Create(創建), Retrieve(讀取),Update(更新),Delete(刪除)
Create
create == insert
標準語法:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
其中value_list: value, [, value] ...
案例:
-- 創建一張學生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '學號',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
指定列插入
INSERT INTO students(id,sn,name,qq) VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.02 sec)
INSERT INTO students(id,sn,name,qq) VALUES (101, 10001, '孫悟空', '11111');
Query OK, 1 row affected (0.02 sec)
-- 查看插入結果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)
單行數據+全列插入
全列插入時可以省略列名,表示每個列都插入
value_list 數量必須和定義表的列的數量及順序一致
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
Query OK, 1 row affected (0.02 sec)
INSERT INTO students VALUES (101, 10001, '孫悟空', '11111');
Query OK, 1 row affected (0.02 sec)
-- 查看插入結果
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
+-----+-------+-----------+-------+
2 rows in set (0.00 sec)
多行數據+全列插入
INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孫仲謀');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
SELECT * FROM students;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10000 | 唐三藏 | NULL |
| 101 | 10001 | 孫悟空 | 11111 |
| 102 | 20001 | 曹孟德 | NULL |
| 103 | 20002 | 孫仲謀 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
插入否則更新
由於 主鍵 或者 唯一鍵 對應的值已經存在而導致插入失敗
-- 主鍵衝突
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'
-- 唯一鍵衝突
INSERT INTO students (sn, name) VALUES (20001, '曹阿瞞');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'
可以選擇性的進行同步更新操作
標準語法:
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
案例
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大師')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大師';
Query OK, 2 rows affected (0.47 sec)
-- 0 row affected: ### 表中有衝突數據,但衝突數據的值和 update 的值相等
-- 1 row affected: ### 表中沒有衝突數據,數據被插入
-- 2 row affected: ### 表中有衝突數據,並且數據已經被更新
-- 通過 MySQL 函數獲取受到影響的數據行數
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
-- ON DUPLICATE KEY 當發生重覆key的時候
替換 (replace)
-- 主鍵 或者 唯一鍵 沒有衝突,則直接插入;
-- 主鍵 或者 唯一鍵 如果衝突,則刪除後再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞞');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中沒有衝突數據,數據被插入
-- 2 row affected: 表中有衝突數據,刪除後重新插入
Retrieve
retrieve == query
標準語法
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
案例:
-- 創建表結構
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同學姓名',
chinese float DEFAULT 0.0 COMMENT '語文成績',
math float DEFAULT 0.0 COMMENT '數學成績',
english float DEFAULT 0.0 COMMENT '英語成績'
);
-- 插入測試數據
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權', 70, 73, 78),
('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
SELECT列
查詢結果返回表格,表現形式為笛卡爾積:詳見資料庫原理
全列查詢
-- 通常情況下不建議使用 * 進行全列查詢
-- 1. 查詢的列越多,意味著需要傳輸的數據量越大;
-- 2. 可能會影響到索引的使用。
SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)
限制顯示條目 limit (分頁查詢)
網頁中每頁 3 條記錄: 按 id 進行分頁,分別顯示 第 1、2、3 頁
基本語法:
-- 起始下標為 0
-- 從 s 開始,篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 從 0 開始,篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;;
-- 從 s 開始,篩選 n 條結果,比第二種用法更明確,建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
基本案例:
- 顯示前四條數據(預設從0開始)
mysql> select name, math+english+chinese total from exam_result limit 4;
+--------+-------+
| name | total |
+--------+-------+
| 唐三藏 | 221 |
| 孫悟空 | 242 |
| 豬悟能 | 276 |
| 曹孟德 | 233 |
+--------+-------+
4 rows in set (0.02 sec)
- 從下標為2開始,顯示4條數據
mysql> select id, name, math+english+chinese total from exam_result limit 4 offset 2;
+----+--------+-------+
| id | name | total |
+----+--------+-------+
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
+----+--------+-------+
4 rows in set (0.02 sec)
- 以區間方式 從下標為2開始,顯示4條數據
mysql> select id, name, math+english+chinese total from exam_result limit 2,4;
+----+--------+-------+
| id | name | total |
+----+--------+-------+
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
+----+--------+-------+
4 rows in set (0.02 sec)
limit 是左閉右開區間
select * 時,如果未知總數量,最好限制一下回顯條目數量,大約在1000條即可
指定列查詢
-- 指定列的順序不需要按定義表的順序來
SELECT id, name, english FROM exam_result;
+----+-----------+--------+
| id | name | english |
+----+-----------+--------+
| 1 | 唐三藏 | 56 |
| 2 | 孫悟空 | 77 |
| 3 | 豬悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 劉玄德 | 45 |
| 6 | 孫權 | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+--------+
7 rows in set (0.00 sec)
select 查詢欄位為表達式
以下案例能夠說明 select能夠計算各種表達式
- 常量表達式
mysql> select 10 from exam_result;
+----+
| 10 |
+----+
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
| 10 |
+----+
7 rows in set (0.00 sec)
- 常量表達式笛卡爾積
mysql> select id,name,10 from exam_result;
+----+-----------+----+
| id | name | 10 |
+----+-----------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孫悟空 | 10 |
| 3 | 豬悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 劉玄德 | 10 |
| 6 | 孫權 | 10 |
| 7 | 宋公明 | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)
- 基本數值運算
mysql> select id,name,1+1 from exam_result;
+----+-----------+-----+
| id | name | 1+1 |
+----+-----------+-----+
| 1 | 唐三藏 | 2 |
| 2 | 孫悟空 | 2 |
| 3 | 豬悟能 | 2 |
| 4 | 曹孟德 | 2 |
| 5 | 劉玄德 | 2 |
| 6 | 孫權 | 2 |
| 7 | 宋公明 | 2 |
+----+-----------+-----+
7 rows in set (0.00 sec)
- 能夠計算1+1,則也能夠計算欄位值的運算
mysql> select id,name,math+100 from exam_result;
+----+-----------+----------+
| id | name | math+100 |
+----+-----------+----------+
| 1 | 唐三藏 | 198 |
| 2 | 孫悟空 | 178 |
| 3 | 豬悟能 | 198 |
| 4 | 曹孟德 | 184 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 173 |
| 7 | 宋公明 | 165 |
+----+-----------+----------+
7 rows in set (0.00 sec)
- 計算總成績
mysql> select id,name,math+chinese+english from exam_result;
+----+-----------+----------------------+
| id | name | math+chinese+english |
+----+-----------+----------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+----------------------+
7 rows in set (0.00 sec)
表達式重命名
如果計算成績的綜合,會發現表達式math+chinese+english
的返回的結果表格中列名很長;可以使用as
對錶達式進行重命名.
mysql> select id,name,math+chinese+english as total from exam_result;
+----+-----------+-------+
| id | name | total |
+----+-----------+-------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------+
7 rows in set (0.00 sec)
還可以省略as
mysql> select id 編號,name 姓名,math+chinese+english total from exam_result;
+--------+-----------+-------+
| 編號 | 姓名 | total |
+--------+-----------+-------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+--------+-----------+-------+
7 rows in set (0.00 sec)
去重
- 原數據:
mysql> select math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
7 rows in set (0.00 sec)
- 去重數據
mysql> select distinct math from exam_result;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
WHERE 條件
相當於 if
比較運算符
運算符 | 說明 |
---|---|
>, >=, <, <= | 大於,大於等於,小於,小於等於 |
= | 等於,NULL 不安全,例如 NULL = NULL 的結果是 NULL |
<=> | 等於,NULL 安全,例如 NULL <=> NULL 的結果是 TRUE(1) |
!=, <> | 不等於 |
between a0 AND a1 | 範圍匹配,[a0, a1],如果 a0 <= value <= a1,返回TRUE(1) |
IN (option, ...) | 如果是 option 中的任意一個,返回 TRUE(1) |
IS NULL | 是 NULL |
IS NOT NULL | 不是 NULL |
LIKE | 模糊匹配。% 表示任意多個(包括 0 個)任意字元;_ 表示任意一個字元 |
邏輯運算符
運算符 | 說明 |
---|---|
AND | 多個條件必須都為 TRUE(1),結果才是 TRUE(1); 邏輯與,相當於&& |
OR | 任意一個條件為 TRUE(1), 結果為 TRUE(1); 邏輯或,相當於|| |
NOT | 條件為 TRUE(1),結果為 FALSE(0); 邏輯非,相當於 ! |
案例:
-
英語不及格的同學及英語成績 ( < 60 )
-
語文成績在 [80, 90] 分的同學及語文成績
mysql> select chinese from exam_result where chinese>=60 and chinese<90; +---------+ | chinese | +---------+ | 67 | | 87 | | 88 | | 82 | | 70 | | 75 | +---------+ 6 rows in set (0.00 sec)
- between
mysql> select chinese from exam_result where chinese between 60 and 89; +---------+ | chinese | +---------+ | 67 | | 87 | | 88 | | 82 | | 70 | | 75 | +---------+ 6 rows in set (0.00 sec)
-
數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績
mysql> select name 姓名, math 數學 from exam_result WHERE math=58 or math=59 or math=98 or math=99; +-----------+--------+ | 姓名 | 數學 | +-----------+--------+ | 唐三藏 | 98 | | 豬悟能 | 98 | +-----------+--------+ 2 rows in set (0.00 sec)
- in
mysql> select name 姓名, math 數學 from exam_result WHERE math in (58,59,98,99); +-----------+--------+ | 姓名 | 數學 | +-----------+--------+ | 唐三藏 | 98 | | 豬悟能 | 98 | +-----------+--------+ 2 rows in set (0.00 sec)
-
姓孫的同學 ; 孫X同學 (X為一個漢字)
mysql> select name 姓名 FROM exam_result WHERE name like "孫%" ; +-----------+ | 姓名 | +-----------+ | 孫悟空 | | 孫權 | +-----------+ 2 rows in set (0.00 sec)
mysql> select name 姓名 FROM exam_result WHERE name like "孫_" ; +--------+ | 姓名 | +--------+ | 孫權 | +--------+ 1 row in set (0.00 sec)
-
語文成績好於英語成績的同學
mysql> SELECT chinese 語文, english 英語 FROM exam_result WHERE chinese > english; +--------+--------+ | 語文 | 英語 | +--------+--------+ | 67 | 56 | | 87 | 77 | | 82 | 67 | | 55 | 45 | | 75 | 30 | +--------+--------+ 5 rows in set (0.00 sec)
-
總分在 200 分以下的同學
mysql> SELECT name 姓名, chinese+math+english total FROM exam_result WHERE total<200; ERROR 1054 (42S22): Unknown column 'total' in 'where clause' mysql> SELECT name 姓名, chinese+math+english total FROM exam_result WHERE chinese+math+english<200; +-----------+-------+ | 姓名 | total | +-----------+-------+ | 劉玄德 | 185 | | 宋公明 | 170 | +-----------+-------+ 2 rows in set (0.00 sec)
- 不能使用別名計算,別名只在返回結果的列名生效;
-
語文成績 > 80 並且不姓孫的同學
mysql> SELECT name 姓名, chinese 語文 FROM exam_result WHERE name not like '孫%' and chinese>80; +-----------+--------+ | 姓名 | 語文 | +-----------+--------+ | 豬悟能 | 88 | | 曹孟德 | 82 | +-----------+--------+ 2 rows in set (0.00 sec)
-
孫X同學,否則要求總成績 > 200 並且 語文成績 < 數學成績 並且 英語成績 > 80
實際就是 孫X同學 或 總成績 > 200 並且 語文成績 < 數學成績 並且 英語成績 > 80
mysql> SELECT *,chinese+math+english total FROM exam_result WHERE name like '孫_' or (chinese+math+english>200 and chinese<math and english>80);
+----+-----------+---------+------+---------+-------+
| id | name | chinese | math | english | total |
+----+-----------+---------+------+---------+-------+
| 3 | 豬悟能 | 88 | 98 | 90 | 276 |
| 6 | 孫權 | 70 | 73 | 78 | 221 |
+----+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)
結果排序 Order by
基本語法
-- ASC 為升序(從小到大) ## ascending order
-- DESC 為降序(從大到小) ## descending order
-- 預設為 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
註意: 沒有 ORDER BY 子句的查詢,返回的順序是未定義的,永遠不要依賴這個順序
descend縮寫與descript縮寫相同
基本案例:
- 同學及數學成績,按數學成績升序顯示
mysql> select name,math from exam_result order by math asc;
+--------+------+
| name | math |
+--------+------+
| 宋公明 | 65 |
| 孫權 | 73 |
| 孫悟空 | 78 |
| 曹孟德 | 84 |
| 劉玄德 | 85 |
| 唐三藏 | 98 |
| 豬悟能 | 98 |
+--------+------+
7 rows in set (0.02 sec)
## 降序
mysql> select name,math from exam_result order by math desc;
+--------+------+
| name | math |
+--------+------+
| 唐三藏 | 98 |
| 豬悟能 | 98 |
| 劉玄德 | 85 |
| 曹孟德 | 84 |
| 孫悟空 | 78 |
| 孫權 | 73 |
| 宋公明 | 65 |
+--------+------+
7 rows in set (0.02 sec)
- NULL 視為比任何值都小,升序出現在最上面,降序出現在最下麵
mysql> select * from class order by name asc;
+----+------+
| id | name |
+----+------+
| 3 | NULL |
| 1 | a |
| 2 | b |
+----+------+
3 rows in set (0.02 sec)
mysql> select * from class order by name desc;
+----+------+
| id | name |
+----+------+
| 2 | b |
| 1 | a |
| 3 | NULL |
+----+------+
3 rows in set (0.02 sec)
- 多欄位排序,排序優先順序隨書寫順序 (相同時怎麼排)
查詢同學各門成績,依次按 數學降序,英語降序,語文降序的方式顯示(
mysql> select name, math, english, chinese from exam_result order by math desc;
+--------+------+---------+---------+
| name | math | english | chinese |
+--------+------+---------+---------+
| 唐三藏 | 98 | 56 | 67 |
| 豬悟能 | 98 | 90 | 88 |
| 劉玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孫悟空 | 78 | 77 | 87 |
| 孫權 | 73 | 78 | 70 |
| 劉備 | 73 | 78 | 76 |
| 宋公明 | 65 | 30 | 75 |
+--------+------+---------+---------+
8 rows in set (0.02 sec)
mysql> select name, math, english, chinese from exam_result order by math desc, english desc;
+--------+------+---------+---------+
| name | math | english | chinese |
+--------+------+---------+---------+
| 豬悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 劉玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孫悟空 | 78 | 77 | 87 |
| 孫權 | 73 | 78 | 70 |
| 劉備 | 73 | 78 | 76 |
| 宋公明 | 65 | 30 | 75 |
+--------+------+---------+---------+
8 rows in set (0.02 sec)
mysql> select name, math, english, chinese from exam_result order by math desc, english desc, chinese desc;
+--------+------+---------+---------+
| name | math | english | chinese |
+--------+------+---------+---------+
| 豬悟能 | 98 | 90 | 88 |
| 唐三藏 | 98 | 56 | 67 |
| 劉玄德 | 85 | 45 | 55 |
| 曹孟德 | 84 | 67 | 82 |
| 孫悟空 | 78 | 77 | 87 |
| 劉備 | 73 | 78 | 76 |
| 孫權 | 73 | 78 | 70 |
| 宋公明 | 65 | 30 | 75 |
+--------+------+---------+---------+
8 rows in set (0.02 sec)
- 可以使用列別名
order by屬於對結果進行處理了,即在select之後,因此可以使用別名
一定是先有數據,才能進行排序
mysql> select name, math+english+chinese total from exam_result order by total;
+--------+-------+
| name | total |
+--------+-------+
| 宋公明 | 170 |
| 劉玄德 | 185 |
| 唐三藏 | 221 |
| 孫權 | 221 |
| 劉備 | 227 |
| 曹孟德 | 233 |
| 孫悟空 | 242 |
| 豬悟能 | 276 |
+--------+-------+
8 rows in set (0.02 sec)
Update
update是比較危險的行為,使用時要謹慎;
體現在:如果忘記添加條件,可能會導致所有數據被覆蓋;
語法
UPDATE table_name SET column = expr [, column = expr ...] ##express為表達式
[WHERE ...] [ORDER BY ...] [LIMIT ...]
update 的基本原理是: 篩選出數據,再對篩選出的數據做修改;
相當於update隱藏執行了一系列select操作,語法後的where,order by,limit都是提供給select使用;最後再執行update操作;
基本案例
- 將孫悟空同學的數學成績變更為 80 分(一次更新一列)
mysql> select name, math from score where name='孫悟空';
+--------+------+
| name | math |
+--------+------+
| 孫悟空 | 78 |
+--------+------+
1 row in set (0.02 sec)
mysql> update score set math=80 where name='孫悟空';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name, math from score where name='孫悟空';
+--------+------+
| name | math |
+--------+------+
| 孫悟空 | 80 |
+--------+------+
1 row in set (0.02 sec)
- 將曹孟德同學的數學成績變更為 60 分,語文成績變更為 70 分(一次更新多列)
```
mysql> select name, math, chinese from score where name='曹孟德';
+--------+------+---------+
| name | math | chinese |
+--------+------+---------+
| 曹孟德 | 84 | 82 |
+--------+------+---------+
1 row in set (0.02 sec)
mysql> update score set math=60,chinese=70 where name='曹孟德';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select name, math, chinese from score where name='曹孟德';
+--------+------+---------+
| name | math | chinese |
+--------+------+---------+
| 曹孟德 | 60 | 70 |
+--------+------+---------+
1 row in set (0.02 sec)
- 將總成績倒數前三的 3 位同學的數學成績加上 30 分
## 查看倒數前三的信息
mysql> select name, math, chinese+math+english total from score order by total asc limit 3;
+--------+------+-------+
| name | math | total |
+--------+------+-------+
| 宋公明 | 65 | 170 |
| 劉玄德 | 85 | 185 |
| 曹孟德 | 60 | 197 |
+--------+------+-------+
3 rows in set (0.02 sec)
mysql> update score set math=math+30 order by math+chinese+english asc limit 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name, math, chinese+math+english total from score order by total asc limit 3;
+--------+------+-------+
| name | math | total |
+--------+------+-------+
| 宋公明 | 95 | 200 |
| 劉玄德 | 115 | 215 |
| 唐三藏 | 98 | 221 |
+--------+------+-------+
3 rows in set (0.02 sec)
- 將所有同學的語文成績更新為原來的 2 倍
註意:更新全表的語句慎用!
沒有 條件 子句,則更新全表
mysql> select name, chinese from score;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 67 |
| 孫悟空 | 87 |
| 豬悟能 | 88 |
| 曹孟德 | 70 |
| 劉玄德 | 55 |
| 孫權 | 70 |
| 宋公明 | 75 |
| 劉備 | 76 |
+-----------+---------+
8 rows in set (0.00 sec)
mysql> update score set chinese = chinese*2;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select name, chinese from score;
+-----------+---------+
| name | chinese |
+-----------+---------+
| 唐三藏 | 134 |
| 孫悟空 | 174 |
| 豬悟能 | 176 |
| 曹孟德 | 140 |
| 劉玄德 | 110 |
| 孫權 | 140 |
| 宋公明 | 150 |
| 劉備 | 152 |
+-----------+---------+
8 rows in set (0.00 sec)
Delete
語法
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
語法類似Update, Update需要修改欄位,因此多了set; Delete只會刪除整行,只需確定哪些行即可.
基本案例
- 刪除孫悟空同學的考試成績
mysql> delete from score where name = '孫悟空';
Query OK, 1 row affected (0.00 sec)
delete刪除整張表數據
註意:刪除整表操作要慎用!
mysql> alter table score modify id int unsigned auto_increment not null unique key;
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> show create table score\G;
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '同學姓名',
`chinese` float DEFAULT '0' COMMENT '語文成績',
`math` float DEFAULT '0' COMMENT '數學成績',
`english` float DEFAULT '0' COMMENT '英語成績',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> delete from score;
Query OK, 7 rows affected (0.00 sec)
mysql> show create table score\G;
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '同學姓名',
`chinese` float DEFAULT '0' COMMENT '語文成績',
`math` float DEFAULT '0' COMMENT '數學成績',
`english` float DEFAULT '0' COMMENT '英語成績',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 ## 表格信息不變,只刪除了數據
1 row in set (0.00 sec)
truncate刪除整張表數據(截斷表)
語法:
TRUNCATE [TABLE] table_name
註意:
這個操作慎用
- 只能對整表操作,不能像 DELETE 一樣針對部分數據操作;
- 實際上 MySQL 不對數據操作,所以比 DELETE 更快,但是TRUNCATE在刪除數據的時候,並不經過真正的事
物,所以無法回滾 - 會重置 AUTO_INCREMENT 項
案例
刪除前的表信息
mysql> select * from score;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 劉備 | 76 | 73 | 78 |
+----+-----------+---------+------+---------+
8 rows in set (0.00 sec)
mysql> show create table score\G;
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '同學姓名',
`chinese` float DEFAULT '0' COMMENT '語文成績',
`math` float DEFAULT '0' COMMENT '數學成績',
`english` float DEFAULT '0' COMMENT '英語成績',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
刪除後
mysql> truncate score;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from score;
Empty set (0.00 sec)
mysql> show create table score\G;
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '同學姓名',
`chinese` float DEFAULT '0' COMMENT '語文成績',
`math` float DEFAULT '0' COMMENT '數學成績',
`english` float DEFAULT '0' COMMENT '英語成績',
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ## 表被初始化了
1 row in set (0.00 sec)
查詢結果插入 Insert ... Select ...
語法
INSERT INTO table_name [(column [, column ...])] SELECT ...
案例:刪除表中的的重覆復記錄,重覆的數據只能有一份
創建原數據表
CREATE TABLE duplicate_table (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
-- 插入測試數據
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
步驟:
-- 創建一張空表 no_duplicate_table,結構和 duplicate_table 一樣
CREATE TABLE no_duplicate_table LIKE duplicate_table;
Query OK, 0 rows affected (0.00 sec)
-- 將 duplicate_table 的去重數據插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 通過重命名錶,實現原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
Query OK, 0 rows affected (0.00 sec)
-- 查看最終結果
SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
3 rows in set (0.00 sec)
傳輸文件相關操作時(拷貝,移動),推薦做法是先複製一份臨時文件,最後以重命名方式就能實現原子操作.
分組聚合查詢
分組
在select中使用group by 子句可以對指定列進行分組查詢
語法:
select column1, column2, .. from table group by column;
聚合函數
函數 | 說明 |
---|---|
COUNT([DISTINCT] expr) | 返回查詢到的數據的 數量 |
SUM([DISTINCT] expr) | 返回查詢到的數據的 總和,不是數字沒有意義 |
AVG([DISTINCT] expr) | 返回查詢到的數據的 平均值,不是數字沒有意義 |
MAX([DISTINCT] expr) | 返回查詢到的數據的 最大值,不是數字沒有意義 |
MIN([DISTINCT] expr) | 返回查詢到的數據的 最小值,不是數字沒有意義 |
- 服務於group by(分組)
不加group by 其實也是分組,只不過是單獨的一單大組,可以理解為以建表約束的規則進行分組,即以原始表格直接聚合
- distinct
去重之後再進行聚合函數計算
描述
分組:對某一組欄位,不同的值為不同的組,相同的值為一組.
聚合:即合併,對某一組欄位,相同的值可以合併(聚合)在一起.
分組聚合:在某種表達式條件下,對指定一組欄位分組,然後分別對各組的指定的其他列進行統計,然後聚合一起形成一條新的記錄;因為統計後得到的值在該組內都是相同的,因此該組能夠合併到一起(未指定的列需要捨棄掉:因為不相同不能聚合);
分組聚合:可以理解成按分組的條件進行分表,每個組對應一個表(邏輯),分成一個個子表,然後再對各個子表進行聚合查詢
查詢結果,以及中間篩選的結果, 都可以認為是一張表(邏輯),這樣的語義能夠更好理解mysql
having 與 where
主要區別在於條件篩選的階段不同
where用於對原始表格進行篩選,
having 用於對分組聚合後的表(邏輯)進行篩選,(優先順序在select之後).
優先順序:
from -> where -> group by -> select-> having ;
複合查詢
準備工作,創建一個雇員信息表(來自oracle 9i的經典測試表)
- EMP員工表
- DEPT部門表
- SALGRADE工資等級表
scott_data.sql
DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `scott`;
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門編號',
`dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',
`loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點'
);
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號',
`ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇員職位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領導編號',
`hiredate` datetime DEFAULT NULL COMMENT '雇佣時間',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '獎金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號'
);
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '等級',
`losal` int(11) DEFAULT NULL COMMENT '此等級最低工資',
`hisal` int(11) DEFAULT NULL COMMENT '此等級最高工資'
);
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);
案例
- 查詢工資高於500或崗位為MANAGER的雇員,同時還要滿足他們的姓名首字母為大寫的J
法一:
mysql> select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
法二(函數):
mysql> select * from emp where (sal>500 or job='MANAGER') and substring(ename,1,1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)
- 按照部門號升序而雇員的工資降序排序
mysql> select ename,sal,deptno from emp order by deptno asc, sal desc;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| KING | 5000.00 | 10 |
| CLARK | 2450.00 | 10 |
| MILLER | 1300.00 | 10 |
| SCOTT | 3000.00 | 20 |
| FORD | 3000.00 | 20 |
| JONES | 2975.00 | 20 |
| ADAMS | 1100.00 | 20 |
| SMITH | 800.00 | 20 |
| BLAKE | 2850.00 | 30 |
| ALLEN | 1600.00 | 30 |
| TURNER | 1500.00 | 30 |
| WARD | 1250.00 | 30 |
| MARTIN | 1250.00 | 30 |
| JAMES | 950.00 | 30 |
+--------+---------+--------+
14 rows in set (0.00 sec)
- 使用年薪進行降序排序(月薪*12+獎金)
mysql> select ename,sal,comm,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪;
+--------+---------+---------+----------+
| ename | sal | comm | 年薪 |
+--------+---------+---------+----------+
| SMITH | 800.00 | NULL | 9600.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| WARD | 1250.00 | 500.00 | 15500.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| ALLEN | 1600.00 | 300.00 | 19500.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| KING | 5000.00 | NULL | 60000.00 |
+--------+---------+---------+----------+
14 rows in set (0.00 sec)
- 顯示每個部門的平均工資和最高工資
mysql> select deptno,format(avg(sal),2),max(sal) from emp group by deptno;
# 語義: 按部門分組(分成多張邏輯子表),然後分別對各個組(每張邏輯子表)計算平均值和最大值,平均值保留兩位小數.
+--------+--------------------+----------+
| deptno | format(avg(sal),2) | max(sal) |
+--------+--------------------+----------+
| 10 | 2,916.67 | 5000.00 |
| 20 | 2,175.00 | 3000.00 |
| 30 | 1,566.67 | 2850.00 |
+--------+--------------------+----------+
3 rows in set (0.00 sec)
- 顯示平均工資低於2000的部門號和它的平均工資
mysql> select deptno, avg(sal) from emp group by deptno having avg(sal)<2000;
# 題意:以部門號為單位,即按部門號分組,然後 聚合查詢;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
- 顯示每種崗位的雇員總數,平均工資
mysql> select job, count(*), format(avg(sal),2) from emp group by job;
# 語義: 以崗位類型進行分組(分表), 分別對各組計算記錄數就是雇員總數;
+-----------+----------+--------------------+
| job | count(*) | format(avg(sal),2) |
+-----------+----------+--------------------+
| ANALYST | 2 | 3,000.00 |
| CLERK | 4 | 1,037.50 |
| MANAGER | 3 | 2,758.33 |
| PRESIDENT | 1 | 5,000.00 |
| SALESMAN | 4 | 1,400.00 |
+-----------+----------+--------------------+
5 rows in set (0.00 sec)
子查詢
子查詢是指嵌入在其他sql語句中的select語句,也叫嵌套查詢
- 顯示工資最高的員工的名字和工作崗位
mysql> select ename, job from emp where sal=(select max(sal) from emp);
+-------+-----------+
| ename | job |
+-------+-----------+
| KING | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)
- 顯示工資高於平均工資的員工信息
mysql> select * from emp where sal>(select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)
多表連接
笛卡爾積
不同的表做笛卡爾積
案例
- 顯示雇員名、雇員工資以及所在部門的名字因為上面的數據來自EMP和DEPT表,因此要聯合查詢
mysql> select ename, sal, dname from emp, dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| SMITH | 800.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| JONES | 2975.00 | RESEARCH |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES |
| ADAMS | 1100.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| FORD | 3000.00 | RESEARCH |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)
- 顯示部門號為10的部門名,員工名和工資
mysql> select dname, ename, sal from emp,dept where emp.deptno=10;
+------------+--------+---------+
| dname | ename | sal |
+------------+--------+---------+
| ACCOUNTING | CLARK | 2450.00 |
| ACCOUNTING | KING | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
| RESEARCH | CLARK | 2450.00 |
| RESEARCH | KING | 5000.00 |
| RESEARCH | MILLER | 1300.00 |
| SALES | CLARK | 2450.00 |
| SALES | KING | 5000.00 |
| SALES | MILLER | 1300.00 |
| OPERATIONS | CLARK | 2450.00 |
| OPERATIONS | KING | 5000.00 |
| OPERATIONS | MILLER | 1300.00 |
+------------+--------+---------+
12 rows in set (0.00 sec)
- 顯示各個員工的姓名,工資,及工資級別
法一:
mysql> select ename,sal,grade, losal, hisal from emp,salgrade where sal>=losal and sal <hisal;
+--------+---------+-------+-------+-------+
| ename | sal | grade | losal | hisal |
+--------+---------+-------+-------+-------+
| SMITH | 800.00 | 1 | 700 | 1200 |
| ALLEN | 1600.00 | 3 | 1401 | 2000 |
| WARD | 1250.00 | 2 | 1201 | 1400 |
| JONES | 2975.00 | 4 | 2001 | 3000 |
| MARTIN | 1250.00 | 2 | 1201 | 1400 |
| BLAKE | 2850.00 | 4 | 2001 | 3000 |
| CLARK | 2450.00 | 4 | 2001 | 3000 |
| KING | 5000.00 | 5 | 3001 | 9999 |
| TURNER | 1500.00 | 3 | 1401 | 2000 |
| ADAMS | 1100.00 | 1 | 700 | 1200 |
| JAMES | 950.00 | 1 | 700 | 1200 |
| MILLER | 1300.00 | 2 | 1201 | 1400 |
+--------+---------+-------+-------+-------+
12 rows in set (0.00 sec)
法二
mysql> select ename,sal,grade, losal, hisal from emp,salgrade where sal between losal and hisal;
+--------+---------+-------+-------+-------+
| ename | sal | grade | losal | hisal |
+--------+---------+-------+-------+-------+
| SMITH | 800.00 | 1 | 700 | 1200 |
| ALLEN | 1600.00 | 3 | 1401 | 2000 |
| WARD | 1250.00 | 2 | 1201 | 1400 |
| JONES | 2975.00 | 4 | 2001 | 3000 |
| MARTIN | 1250.00 | 2 | 1201 | 1400 |
| BLAKE | 2850.00 | 4 | 2001 | 3000 |
| CLARK | 2450.00 | 4 | 2001 | 3000 |
| SCOTT | 3000.00 | 4 | 2001 | 3000 |
| KING | 5000.00 | 5 | 3001 | 9999 |
| TURNER | 1500.00 | 3 | 1401 | 2000 |
| ADAMS | 1100.00 | 1 | 700 | 1200 |
| JAMES | 950.00 | 1 | 700 | 1200 |
| FORD | 3000.00 | 4 | 2001 | 3000 |
| MILLER | 1300.00 | 2 | 1201 | 1400 |
+--------+---------+-------+-------+-------+
14 rows in set (0.00 sec)
自連接
相同的表自己連接自己
案例
- 顯示員工FORD的上級領導的編號和姓名(mgr是員工領導的編號--empno)
法一:
mysql> select ename,empno from emp where empno=(select mgr from emp where ename='FORD');
+-------+--------+
| ename | empno |
+-------+--------+
| JONES | 007566 |
+-------+--------+
1 row in set (0.00 sec)
法二:
mysql> select t1.ename,t1.mgr,t2.ename leader from emp t1,emp t2 where t1.ename='FORD' and t1.mgr=t2.empno;
+-------+------+--------+
| ename | mgr | leader |
+-------+------+--------+
| FORD | 7566 | JONES |
+-------+------+--------+
1 row in set (0.00 sec)
內連接
上文用的笛卡爾積實際就是內連接;內連接也有特定的語法:
內連接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選,也是在開發過程中使用的最多的連接查詢。
select 欄位 from 表1 inner join 表2 on 連接條件 and 其他條件;
使用語法能夠能夠提高模塊化程度與可讀性,同樣的學習成本也提高了
外連接
外連接分為左外連接和右外連接
左外連接
如果聯合查詢,左側的表完全顯示我們就說是左外連接。
保留左表(左表始終所有數據始終可見),右表如果不滿足則填充NULL;
顯然,核心就是以左表為主的思想
select 欄位名 from 表名1 left join 表名2 on 連接條件; ## 實際就是把inner換成left或right
右外連接
語法:
select 欄位 from 表名1 right join 表名2 on 連接條件;
本文來自博客園,作者:HJfjfK,原文鏈接:https://www.cnblogs.com/DSCL-ing/p/18425446