超详细的MySQL CRUD 并配备了大量的测试用例，包教包会

标签：00 name -- 包教包会 CRUD 测试用例 sec NULL math

MySQL数据库表的增删查改

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

Create

INSERT [INTO] table_name [(column [, column] …)] VALUES (value_list) [, (value_list)] … value_list: value, [, value] …

insert语句主要有两种情况，一种是全行插入，就是在table_name后面不跟插入列的名称，直接在全行进行插入，还有就是指定列进行插入操作

-- 创建一张学生表
CREATE TABLE students (
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   sn INT NOT NULL UNIQUE COMMENT '学号',
   name VARCHAR(20) NOT NULL,
   qq VARCHAR(20)
);

单行数据+全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
-- 注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
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)

这里student后面不跟列的名称就是一种全列插入，在values后要根据顺序把每一列的值填写进去

多行插入 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
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)

这里table_name后面指定了哪些列的内容就插入到哪些列当中，而且可以在插入内容后加上 , 继续进行插入操作 -> 多行插入

插入否则更新

这主要使用于当插入内容与主键或者unique冲突时更新内容使用

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: 表中有冲突数据，并且数据已经被更新

替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)

-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

在MySQL中，REPLACE 语句是一种用于插入或更新数据的语句。它类似于 INSERT 语句，但有一个关键的区别：如果目标表中已经存在一条具有相同唯一键或主键值的记录，**REPLACE 会首先删除旧的记录，然后插入新的记录。**这种操作方式有时也被称为“插入或替换”。

REPLACE 语句的基本语法如下：
REPLACE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
或者使用另一种形式：
REPLACE INTO table_name
SET column1 = value1, column2 = value2, ...;
工作原理

查找冲突：MySQL会检查目标表中的数据，查看是否存在与新数据具有相同唯一键或主键的记录。
删除旧记录：如果找到匹配的记录，MySQL会删除这条记录。此删除操作会触发任何相关的删除触发器。
插入新记录：然后，MySQL插入新的记录。这个插入操作会触发任何相关的插入触发器。

注意事项

删除和插入的开销：由于 REPLACE 先删除旧记录再插入新记录，这个过程可能比简单的更新操作要慢，尤其是在有外键约束或触发器时。此外，这个过程还可能导致自动增长列的值跳跃。
唯一性约束：REPLACE 语句只能应用于有唯一键（如主键或唯一索引）的表中。如果没有唯一键，REPLACE 将始终插入新记录。
触发器：如上所述，REPLACE 操作会触发相关的触发器，所以在设计表和数据库逻辑时需要小心处理这些触发器的影响。

使用场景

REPLACE 语句常用于那些需要确保数据唯一性的场景，比如：

同步数据：例如从其他数据源同步数据时，如果记录存在则更新，不存在则插入。
数据去重：在某些情况下，可能需要根据唯一键去重，这时可以使用 REPLACE 代替复杂的检查和更新逻辑。

Retrive

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

全列查询

#通常情况下不建议使用 * 进行全列查询
#1. 查询的列越多，意味这要传输的数据量越大
#2. 可能影响到索引的使用 后面会出博客讲解
mysql> 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)

指定列查询

mysql> select id, name, chinese from exam_result;
+----+-----------+---------+
| id | name      | chinese |
+----+-----------+---------+
|  1 | 唐三藏    |      67 |
|  2 | 孙悟空    |      87 |
|  3 | 猪悟能    |      88 |
|  4 | 曹孟德    |      82 |
|  5 | 刘玄德    |      55 |
|  6 | 孙权      |      70 |
|  7 | 宋公明    |      75 |
+----+-----------+---------+
7 rows in set (0.00 sec)

查询字段为表达式

可以直接将Chinese, English，Math进行相加并且重命名

mysql> SELECT id, name, chinese + math + english 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.01 sec)

这里可以使用 as total 也可以直接使用total进行重命名

where 条件

MySQL的where语句就像是c/c++的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)

between

语文成绩在 [80, 90] 分的同学及语文成绩\

-- 使用 AND 进行条件连接 
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90; 
+-----------+-------+ 
| name | chinese | 
+-----------+-------+ 
| 孙悟空 | 87 | 
| 猪悟能 | 88 | 
| 曹孟德 | 82 | 
+-----------+-------+ 
3 rows in set (0.00 sec) 

-- 使用 BETWEEN ... AND ... 条件  
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90; 
+-----------+-------+ 
| name | chinese | 
+-----------+-------+ 
| 孙悟空 | 87 | 
| 猪悟能 | 88 | 
| 曹孟德 | 82 | 
+-----------+-------+ 
3 rows in set (0.00 sec)

In

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

-- 使用 OR 进行条件连接 
 
SELECT name, math FROM exam_result 
 WHERE math = 58 
 OR math = 59 
 OR math = 98 
 OR math = 99; 
+-----------+--------+ 
| name | math | 
+-----------+--------+ 
| 唐三藏 | 98 | 
| 猪悟能 | 98 | 
+-----------+--------+ 
2 rows in set (0.01 sec) 
-- 使用 IN 条件 
 
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99); 
+-----------+--------+ 
| name | math | 
+-----------+--------+ 
| 唐三藏 | 98 | 
| 猪悟能 | 98 | 
+-----------+--------+ 
2 rows in set (0.00 sec)

like

姓孙的同学 及 孙某同学 

-- % 匹配任意多个（包括 0 个）任意字符 
 
SELECT name FROM exam_result WHERE name LIKE '孙%'; 
+-----------+ 
| name | 
+-----------+ 
| 孙悟空 | 
| 孙权 | 
+-----------+ 
2 rows in set (0.00 sec) 
-- _ 匹配严格的一个任意字符 
 
SELECT name FROM exam_result WHERE name LIKE '孙_'; 
+--------+ 
| name | 
+--------+ 
| 孙权 | 
+--------+ 
1 row in set (0.00 sec)

where不能使用别名

6.2.2.6 总分在 200 分以下的同学 
-- WHERE 条件中使用表达式 
-- 别名不能用在 WHERE 条件中 
 
SELECT name, chinese + math + english 总分 FROM exam_result 
 WHERE chinese + math + english < 200; 
+-----------+--------+ 
| name | 总分 | 
+-----------+--------+ 
| 刘玄德 | 185 | 
| 宋公明 | 170 | 
+-----------+--------+ 
2 rows in set (0.00 sec)

这里有一个注意到点就是哪一个先运行，这里是先选出总分<20的，然后在进行显示，所以显示的是后运行的，所以不能使用别名

结果排序

语法：

-- ASC 为升序（从小到大） 
-- DESC 为降序（从大到小） 
-- 默认为 ASC 
 
SELECT ... FROM table_name [WHERE ...] 
 ORDER BY column [ASC|DESC], [...];

多级排序

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示 

-- 多字段排序，排序优先级随书写顺序 
 
SELECT name, math, english, chinese FROM exam_result 
 ORDER BY math DESC, english, chinese; 
+-----------+--------+--------+-------+ 
| name | math | english | chinese | 
+-----------+--------+--------+-------+ 
| 唐三藏 | 98 | 56 | 67 | 
| 猪悟能 | 98 | 90 | 88 | 
| 刘玄德 | 85 | 45 | 55 | 
| 曹孟德 | 84 | 67 | 82 | 
| 孙悟空 | 78 | 77 | 87 | 
| 孙权 | 73 | 78 | 70 | 
| 宋公明 | 65 | 30 | 75 | 
+-----------+--------+--------+-------+ 
7 rows in set (0.00 sec)

这里是使用不同的优先级，就是先按照math降序排列，如果math相同就按照English升序排列（如果不写desc还是asc默认是升序），如果这两个都相同就按照Chinese排序

使用别名

SELECT name, chinese + english + math total FROM exam_result 
 ORDER BY 总分 DESC; 
+-----------+--------+ 
| name | total | 
+-----------+--------+ 
| 猪悟能 | 276 | 
| 孙悟空 | 242 | 
| 曹孟德 | 233 | 
| 唐三藏 | 221 | 
| 孙权 | 221 | 
| 刘玄德 | 185 | 
| 宋公明 | 170 | 
+-----------+--------+ 
7 rows in set (0.00 sec)

这里排序是可以使用别名的，因为排序是先选择在进行排序

Update

语法：

UPDATE table_name SET column = expr [, column = expr ...] 
 [WHERE ...] [ORDER BY ...] [LIMIT ...]

这里的update一般与where和limit等一起使用，因为如果不使用这些作为限制的话，就可能会导致整行元素直接消失,所以update要小心使用，以防造成数据的大量丢失

对查询结果进行行列值更新

使用where

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分 

-- 一次更新多个列 
 
-- 查看原数据

SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
| name | math | chinese | 
+-----------+--------+-------+ 
| 曹孟德 | 84 | 82 | 
+-----------+--------+-------+ 
1 row in set (0.00 sec) 
 
-- 数据更新 
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德'; 
Query OK, 1 row affected (0.14 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 
 
-- 查看更新后数据 
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德'; 
+-----------+--------+-------+ 
| name | math | chinese | 
+-----------+--------+-------+ 
| 曹孟德 | 60 | 70 | 
+-----------+--------+-------+ 
1 row in set (0.00 sec)

使用order by

-- 更新值为原值基础上变更 
 
-- 查看原数据 
-- 别名可以在ORDER BY中使用 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 ORDER BY 总分 LIMIT 3; 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 宋公明 | 65 | 170 | 
| 刘玄德 | 85 | 185 | 
| 曹孟德 | 60 | 197 | +-----------+--------+--------+ 
3 rows in set (0.00 sec) 
 
-- 数据更新，不支持 math += 30 这种语法 
UPDATE exam_result SET math = math + 30 
 ORDER BY chinese + math + english LIMIT 3; 
 
-- 查看更新后数据 
-- 思考：这里还可以按总分升序排序取前 3 个么？ 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 WHERE name IN ('宋公明', '刘玄德', '曹孟德'); 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 曹孟德 | 90 | 227 | 
| 刘玄德 | 115 | 215 | 
| 宋公明 | 95 | 200 | 
+-----------+--------+--------+ 
3 rows in set (0.00 sec) 
 
-- 按总成绩排序后查询结果 
SELECT name, math, chinese + math + english 总分 FROM exam_result 
 ORDER BY 总分 LIMIT 3; 
+-----------+--------+--------+ 
| name | math | 总分 | 
+-----------+--------+--------+ 
| 宋公明 | 95 | 200 | 
| 刘玄德 | 115 | 215 | 
| 唐三藏 | 98 | 221 | 
+-----------+--------+--------+ 
3 rows in set (0.00 sec)

从这里我们可以看出，update不仅可以使用where进行挑选，而且可以使用order by + limit,就是先选出我们要进行更改的行，然后再进行update

Delete

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

#删除孙悟空的成绩
mysql> select * from exam_result where name = '孙悟空' ;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  2 | 孙悟空    |     174 |  100 |      77 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

mysql> delete from exam_result where name = '孙悟空';\
Query OK, 1 row affected (0.01 sec)

mysql> select * from exam_result where name = '孙悟空' ;
Empty set (0.00 sec)

删除整张表

mysql> CREATE TABLE for_delete ( 
    ->  id INT PRIMARY KEY AUTO_INCREMENT, 
    ->  name VARCHAR(20) 
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from for_delete
    -> ;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.01 sec)

mysql> delete from for_delete;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from for_delete;
Empty set (0.01 sec)

#在这里可以看到auto_increment=4,证明这样删除并不会将auto_increment置空
mysql> show create table for_delete \G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

#所以下次再进行插入时id会从4开始
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | A    |
|  5 | B    |
|  6 | C    |
+----+------+
3 rows in set (0.00 sec)

delete 并不会清空auto_increment 下面我们说的截断表 truncate则会将auto_increment置空

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事
物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

-- 准备测试表 
CREATE TABLE for_truncate ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(20) 
); 
Query OK, 0 rows affected (0.16 sec) 
 
-- 插入测试数据 
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); 
Query OK, 3 rows affected (1.05 sec) 
Records: 3 Duplicates: 0 Warnings: 0 
 
-- 查看测试数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
| 1 | A | 
| 2 | B | 
| 3 | C | 
+----+------+ 
3 rows in set (0.00 sec) 
-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作 
TRUNCATE for_truncate; 
Query OK, 0 rows affected (0.10 sec) 
 
-- 查看删除结果 
SELECT * FROM for_truncate; 
Empty set (0.00 sec) 
-- 再插入一条数据，自增 id 在重新增长 
INSERT INTO for_truncate (name) VALUES ('D'); 
Query OK, 1 row affected (0.00 sec)  
-- 查看数据 
SELECT * FROM for_truncate; 
+----+------+ 
| id | name | 
+----+------+ 
| 1 | D | 
+----+------+ 
1 row in set (0.00 sec) 
 
-- 查看表结构，会有 AUTO_INCREMENT=2 项 
SHOW CREATE TABLE for_truncate\G 
*************************** 1. row *************************** 
 Table: for_truncate 
Create Table: CREATE TABLE `for_truncate` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `name` varchar(20) DEFAULT NULL, 
 PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 
1 row in set (0.00 sec) 

# 再次进行插入id会从0开始

插入查询结果

有时候我们会有这样的需求，就是将一张表的查询结果作为数据插入到另一张表中，这时就要用到插入查询结果了

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

mysql> CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO duplicate_table VALUES 
    ->  (100, 'aaa'), 
    ->  (100, 'aaa'), 
    ->  (200, 'bbb'), 
    ->  (200, 'bbb'), 
    ->  (200, 'bbb'), 
    ->  (300, 'ccc'); 
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

#创建表时使用like可以创建与原来表结构相同的表
mysql> create table no_duplicate_table like duplicate_table;
Query OK, 0 rows affected (0.08 sec)

#进行插入操作
mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from no_duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

#进行重命名
mysql> rename table duplicate_table to old_duplicate_table, no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from duplicate_table;
+------+------+
| id   | name |
+------+------+
|  100 | aaa  |
|  200 | bbb  |
|  300 | ccc  |
+------+------+
3 rows in set (0.00 sec)

聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

gruop by语句

语法：

select column1, column2, .. from table group by column;

给大家一份脚本代码作为练习

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);

大家可以把他source一下，然后得到一份表，用于后面的练习

表中的数据

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 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 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

选出部门的最高工资和平均工资

mysql> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
|     10 | 2916.666667 |  5000.00 |
+--------+-------------+----------+
3 rows in set (0.02 sec)

这里是可以显示部门的，因为它是分过组的。

显示每个部门的每个岗位的平均工资和最低工资

mysql> select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     20 | CLERK     |  950.000000 |   800.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     10 | CLERK     | 1300.000000 |  1300.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

显示平均工资低于两千的部门，和他的平均工资

统计各个部门的平均工资

select avg(sal) from EMP group by deptno

hanving 和 gruop by混合使用，对gruop by进行过滤

select avg(sal) as myavg from EMP group by deptno having myavg<2000; 
 
--having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where。

having 与 where的区别

条件筛选的阶段是不同的，where是对具体的任意列进行条件筛选，having是对分组聚合之后的结果进行条件筛选。

标签：00,name,--,包教包会,CRUD,测试用例,sec,NULL,math
From： https://blog.csdn.net/xiyun_king/article/details/140833345

超详细的MySQL CRUD 并配备了大量的测试用例，包教包会

MySQL数据库表的增删查改

Create

单行数据+全列插入

多行插入 + 指定列插入

插入否则更新

替换

工作原理

注意事项

使用场景

Retrive

全列查询

指定列查询

查询字段为表达式

where 条件

between

In

like

where不能使用别名

结果排序

多级排序

使用别名

Update

对查询结果进行行列值更新

使用where

使用order by

Delete

删除整张表

截断表

插入查询结果

聚合函数

gruop by语句

having 与 where的区别

相关文章

赞助商

阅读排行

超详细的MySQL CRUD 并配备了大量的测试用例， 包教包会

MySQL数据库表的增删查改

Create

单行数据+全列插入

多行插入 + 指定列插入

插入否则更新

替换

工作原理

注意事项

使用场景

Retrive

全列查询

指定列查询

查询字段为表达式

where 条件

between

In

like

where不能使用别名

结果排序

多级排序

使用别名

Update

对查询结果进行行列值更新

使用where

使用order by

Delete

删除整张表

截断表

插入查询结果

聚合函数

gruop by语句

having 与 where的区别

相关文章

赞助商

阅读排行

超详细的MySQL CRUD 并配备了大量的测试用例，包教包会