MySQL 表的CRUD與複合查詢

来源:https://www.cnblogs.com/DSCL-ing/p/18425446
-Advertisement-
Play Games

目錄表的增刪改查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)
    • 不能使用別名計算,別名只在返回結果的列名生效;

    image-20240910140853526

  • 語文成績 > 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
註意：

這個操作慎用

  1. 只能對整表操作，不能像 DELETE 一樣針對部分數據操作；
  2. 實際上 MySQL 不對數據操作，所以比 DELETE 更快，但是TRUNCATE在刪除數據的時候，並不經過真正的事
    物，所以無法回滾
  3. 會重置 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)

多表連接

笛卡爾積

image-20240920000650365

不同的表做笛卡爾積

案例

  • 顯示雇員名、雇員工資以及所在部門的名字因為上面的數據來自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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用Yasboot安裝YashanDB的疑惑和建議
    本文作者：YashanDB高級服務工程師盧智凌 從去年開始，⼀直在學習國產資料庫YashanDB，對YashanDB的總體感覺還是非常不錯的。作為對學習成果的考察，去年通過了YashanDB官方組織的YCA認證，4月底⼜參加了YCP認證，YCP認證包括筆試部分和實操部分。在準備實操部分的練習時，使用 ...
  • mysql進階-鎖
    本文作者：YashanDB高級服務工程師周國超 YashanDB共用集群是崖⼭資料庫系統（YashanDB）的⼀個關鍵特性，它是⼀個單庫多實例的多活資料庫系統。⽤⼾可以連接到任意實例訪問同⼀個資料庫，多個資料庫實例能夠併發讀寫同⼀份數據，同時保證實例之間讀寫的強⼀致性。這種設計賦予了系統⾼可⽤性、⾼ ...
  • OpenGauss 安裝
    參考官網鏈接：https://docs-opengauss.osinfra.cn/zh/docs/5.0.0/docs/InstallationGuide/%E5%8D%95%E8%8A%82%E7%82%B9%E5%AE%89%E8%A3%85.html 其中安裝版本為 5.0，操作系統為 ope ...
  • 讀數據湖倉03不同類型的數據
    1. 結構化數據 1.1. 結構化數據是數據湖倉中最常見的基礎數據之一 1.1.1. 是技術領域中最早出現的數據環境之一 1.2. 每條記錄的結構都是相同的，即便不同記錄中的內容可能不同，但數據的基本佈局完全一樣 1.3. 結構化數據環境都經過了優化，電腦能以最優的方式處理結構化數據 1.4. 很 ...
  • 第二十七講： 讀寫分離有哪些坑？
    本文內容來自YashanDB官網，具體內容請見(https://www.yashandb.com/newsinfo/7488286.html?templateId=1718516) 測試驗證環境說明 測試用例說明 1、相同版本下，新增表數據量，使統計信息失效。優化器優先使用outline的計劃。 2 ...
  • PostgreSQL同步流複製與synchronous_standby_names設置
    PostgreSQL的流複製，從整體上看，可以粗分為同步與非同步兩種模式，其中涉及的主要參數包括synchronous_commit和synchronous_standby_names 主節點synchronous_commit參數設置 synchronous_commit事務提交模式，類似於MySQ ...
  • MySQL History List Length
    本文是翻譯A graph a day, keeps the doctor away ! – MySQL History List Length[1]這篇文章，翻譯如有不當的地方，敬請諒解，請尊重原創和翻譯勞動成果，轉載的時候請註明出處。謝謝！ 這是MySQL趨勢系列文章的第二篇。 正如我之前所寫，了 ...
  • 讀數據湖倉02數據抽象
    1. 不同類型的數據 1.1. 不同類型的數據在存儲方面有各自的特性，這些特性極大地影響了數據在數據湖倉中的存儲和使用方式 1.2. 結構化數據 1.2.1. 在企業等組織中，只有少量的數據是結構化數據 1.2.2. 結構化數據是基於事務的數據，是組織日常業務的副產品 1.3. 文本數據 1.3.1 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類