【MySQL】06.MySQL表的增删查改

1. insert

我们先创建一个表结构，这部分操作我们使用这张表完成我们的操作：

mysql> create table student(-> id int primary key auto_increment,-> name varchar(20) not null,-> qq varchar(20) unique-> );
Query OK, 0 rows affected (0.04 sec)

示例演示： 

-- 单行插入
mysql> insert into student values(100, '张三', '11111');
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(101, '李四', NULL);
Query OK, 1 row affected (0.01 sec)-- 多行插入
mysql> insert into student(name,qq) values('王五', '22222'),('赵六',' ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from student;
+-----+--------+-------+
| id  | name   | qq    |
+-----+--------+-------+
| 100 | 张三   | 11111 |
| 101 | 李四   | NULL  |
| 102 | 王五   | 22222 |
| 103 | 赵六   |       |
+-----+--------+-------+
4 rows in set (0.00 sec)-- 插入冲突更新
mysql> insert into student(id,name,qq) values(102,'周七', '33333');
ERROR 1062 (23000): Duplicate entry '102' for key 'student.PRIMARY'mysql> insert into student(id,name,qq) values(102,'周七', '33333') on duplicate key update name = '周七', qq = '33333';
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+-----+--------+-------+
| id  | name   | qq    |
+-----+--------+-------+
| 100 | 张三   | 11111 |
| 101 | 李四   | NULL  |
| 102 | 周七   | 33333 |
| 103 | 赵六   |       |
+-----+--------+-------+
4 rows in set (0.00 sec)-- 替换
mysql> replace into student (id, name) VALUES (100, '田八');
Query OK, 2 rows affected (0.00 sec)mysql> select * from student;
+-----+--------+-------+
| id  | name   | qq    |
+-----+--------+-------+
| 100 | 田八   | NULL  |
| 101 | 李四   | NULL  |
| 102 | 周七   | 33333 |
| 103 | 赵六   |       |
+-----+--------+-------+
4 rows in set (0.00 sec)

2. update

对查询到的结果进行列值更新：

-- 将张三同学的数学成绩变更为 100 分
mysql> update exam_result set math = 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |  100 |      56 |
|  3 | 李四   |      87 |   78 |      77 |
|  4 | 王五   |      88 |   98 |      90 |
|  5 | 赵六   |      82 |   84 |      67 |
|  6 | 田八   |      70 |   73 |      78 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)-- 将张三同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> update exam_result set math = 60,chinese = 70 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      70 |   60 |      56 |
|  3 | 李四   |      87 |   78 |      77 |
|  4 | 王五   |      88 |   98 |      90 |
|  5 | 赵六   |      82 |   84 |      67 |
|  6 | 田八   |      70 |   73 |      78 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      70 |   90 |      56 |
|  3 | 李四   |      87 |   78 |      77 |
|  4 | 王五   |      88 |   98 |      90 |
|  5 | 赵六   |      82 |   84 |      67 |
|  6 | 田八   |      70 |  103 |      78 |
|  7 | 刘九   |      75 |   95 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)-- 将所有同学的语文成绩更新为原来的 2 倍
mysql> update exam_result set chinese = chinese * 2;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |     140 |   90 |      56 |
|  3 | 李四   |     174 |   78 |      77 |
|  4 | 王五   |     176 |   98 |      90 |
|  5 | 赵六   |     164 |   84 |      67 |
|  6 | 田八   |     140 |  103 |      78 |
|  7 | 刘九   |     150 |   95 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)

3. delete

-- 删除张三同学的考试成绩
mysql> delete from exam_result where name = '张三';
Query OK, 1 row affected (0.00 sec)mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  3 | 李四   |     174 |   78 |      77 |
|  4 | 王五   |     176 |   98 |      90 |
|  5 | 赵六   |     164 |   84 |      67 |
|  6 | 田八   |     140 |  103 |      78 |
|  7 | 刘九   |     150 |   95 |      30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)-- 删除整张表
delete from for_delete; 
Query OK, 3 rows affected (0.00 sec) SELECT * FROM for_delete; 
Empty 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) -- delete 和 truncate 的其中一个区别是truncate会清空auto_increment。

4. select

这部分使用下面的表进行操作：

mysql> create table exam_result(-> id int primary key auto_increment,-> name varchar(20) not null,-> chinese float default 0,-> math float default 0,-> english float default 0-> );
Query OK, 0 rows affected (0.03 sec)mysql> insert into exam_result(name,chinese,math,english) values('张三',67,98,56),-> ('李四',87,78,77);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into exam_result(name,chinese,math,english) values('王五', 88, 98, 90), ('赵六', 82, 84, 67),('田八', 70, 73, 78),('刘九', 75, 65, 30);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

4.1 简单查询 

-- 全列查询
mysql> select * from exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |   98 |      56 |
|  3 | 李四   |      87 |   78 |      77 |
|  4 | 王五   |      88 |   98 |      90 |
|  5 | 赵六   |      82 |   84 |      67 |
|  6 | 田八   |      70 |   73 |      78 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)-- 查询部分列
mysql> select id,name,chinese from exam_result;
+----+--------+---------+
| id | name   | chinese |
+----+--------+---------+
|  2 | 张三   |      67 |
|  3 | 李四   |      87 |
|  4 | 王五   |      88 |
|  5 | 赵六   |      82 |
|  6 | 田八   |      70 |
|  7 | 刘九   |      75 |
+----+--------+---------+
6 rows in set (0.00 sec)-- 查询字段为表达式
mysql> select id,name,chinese+math+english from exam_result;
+----+--------+----------------------+
| id | name   | chinese+math+english |
+----+--------+----------------------+
|  2 | 张三   |                  221 |
|  3 | 李四   |                  242 |
|  4 | 王五   |                  276 |
|  5 | 赵六   |                  233 |
|  6 | 田八   |                  221 |
|  7 | 刘九   |                  170 |
+----+--------+----------------------+
6 rows in set (0.00 sec)-- 为查询列指定别名
mysql> select id,name,chinese+math+english 总分 from exam_result;
+----+--------+--------+
| id | name   | 总分   |
+----+--------+--------+
|  2 | 张三   |    221 |
|  3 | 李四   |    242 |
|  4 | 王五   |    276 |
|  5 | 赵六   |    233 |
|  6 | 田八   |    221 |
|  7 | 刘九   |    170 |
+----+--------+--------+
6 rows in set (0.00 sec)-- 去重查询
mysql> select distinct math from exam_result;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   73 |
|   65 |
+------+
5 rows in set (0.01 sec)

4.2 where

比较运算符：

运算符	说明
>, >=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= values <=a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR
NOT

示例演示：

-- 英语不及格的同学及英语成绩 ( < 60 )
mysql> select name,english from exam_result where english < 60;
+--------+---------+
| name   | english |
+--------+---------+
| 张三   |      56 |
| 刘九   |      30 |
+--------+---------+
2 rows in set (0.00 sec)-- 语文成绩在 [80, 90] 分的同学及语文成绩
mysql> select name,chinese from exam_result where chinese between 80 and 90;
+--------+---------+
| name   | chinese |
+--------+---------+
| 李四   |      87 |
| 王五   |      88 |
| 赵六   |      82 |
+--------+---------+
3 rows in set (0.00 sec)mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
+--------+---------+
| name   | chinese |
+--------+---------+
| 李四   |      87 |
| 王五   |      88 |
| 赵六   |      82 |
+--------+---------+
3 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;
+--------+------+
| name   | math |
+--------+------+
| 张三   |   98 |
| 王五   |   98 |
+--------+------+
2 rows in set (0.00 sec)mysql> select name,math from exam_result where math in(58,59,98,99);
+--------+------+
| name   | math |
+--------+------+
| 张三   |   98 |
| 王五   |   98 |
+--------+------+
2 rows in set (0.01 sec)-- 姓张的同学 及 赵某同学
mysql> select name from exam_result where name like '张%' or name like '赵_';
+--------+
| name   |
+--------+
| 张三   |
| 赵六   |
+--------+
2 rows in set (0.00 sec)-- 语文成绩好于英语成绩的同学
mysql> select name,chinese,english from exam_result where chinese > english;
+--------+---------+---------+
| name   | chinese | english |
+--------+---------+---------+
| 张三   |      67 |      56 |
| 李四   |      87 |      77 |
| 赵六   |      82 |      67 |
| 刘九   |      75 |      30 |
+--------+---------+---------+
4 rows in set (0.00 sec)-- 总分在 200 分以下的同学
mysql> select name,chinese+math+english from exam_result where chinese + math + english < 200;
+--------+----------------------+
| name   | chinese+math+english |
+--------+----------------------+
| 刘九   |                  170 |
+--------+----------------------+
1 row in set (0.00 sec)-- 语文成绩 > 80 并且不姓张的同学
mysql> select name,chinese from exam_result where chinese > 80 and name not like '张%';
+--------+---------+
| name   | chinese |
+--------+---------+
| 李四   |      87 |
| 王五   |      88 |
| 赵六   |      82 |
+--------+---------+
3 rows in set (0.00 sec)-- 张某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
mysql> select * from exam_result where name like '张%' or (chinese + math + english > 200 and chinese < math and english >80);
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |   98 |      56 |
|  4 | 王五   |      88 |   98 |      90 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)

4.3 order by

-- 同学及数学成绩，按数学成绩升序显示
mysql> select name,math from exam_result order by math;
+--------+------+
| name   | math |
+--------+------+
| 刘九   |   65 |
| 田八   |   73 |
| 李四   |   78 |
| 赵六   |   84 |
| 张三   |   98 |
| 王五   |   98 |
+--------+------+
6 rows in set (0.01 sec)-- 同学及总分，按总分排序显示
mysql> select name,chinese + math + english 总分 from exam_result order by 总分;
+--------+--------+
| name   | 总分   |
+--------+--------+
| 刘九   |    170 |
| 张三   |    221 |
| 田八   |    221 |
| 赵六   |    233 |
| 李四   |    242 |
| 王五   |    276 |
+--------+--------+
6 rows in set (0.00 sec)-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
mysql> select * from exam_result order by math desc,english,chinese;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |   98 |      56 |
|  4 | 王五   |      88 |   98 |      90 |
|  5 | 赵六   |      82 |   84 |      67 |
|  3 | 李四   |      87 |   78 |      77 |
|  6 | 田八   |      70 |   73 |      78 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
6 rows in set (0.00 sec)-- 查询姓张的同学或者姓刘的同学数学成绩，结果按数学成绩由高到低显示
mysql> select * from exam_result where name like '张%' or name like '刘%' order by math desc;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |   98 |      56 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)

4.4 limit offset

对查询结果进行分页处理：

mysql> select * from exam_result limit 3 offset 0;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  2 | 张三   |      67 |   98 |      56 |
|  3 | 李四   |      87 |   78 |      77 |
|  4 | 王五   |      88 |   98 |      90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select * from exam_result limit 3 offset 3;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  5 | 赵六   |      82 |   84 |      67 |
|  6 | 田八   |      70 |   73 |      78 |
|  7 | 刘九   |      75 |   65 |      30 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)mysql> select * from exam_result limit 3 offset 6;
Empty set (0.00 sec)

4.5 insert into

mysql> insert into son_table select * from exam_result;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> select * from son_table;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  3 | 李四   |     174 |   78 |      77 |
|  4 | 王五   |     176 |   98 |      90 |
|  5 | 赵六   |     164 |   84 |      67 |
|  6 | 田八   |     140 |  103 |      78 |
|  7 | 刘九   |     150 |   95 |      30 |
+----+--------+---------+------+---------+
5 rows in set (0.00 sec)

4.6 聚合函数

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

-- 统计表中的人数
-- 使用 * 做统计，不受 NULL 影响
mysql> select count(*) 人数 from exam_result;
+--------+
| 人数   |
+--------+
|      5 |
+--------+
1 row in set (0.01 sec)-- 使用表达式做统计 
mysql> select count(name) 人数 from exam_result;
+--------+
| 人数   |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)-- 统计数学成绩总分
mysql> select sum(math) 数学总分 from exam_result;
+--------------+
| 数学总分     |
+--------------+
|          458 |
+--------------+
1 row in set (0.00 sec)-- 统计平均总分
mysql> select avg(math + chinese + english) 平均分 from exam_result;
+-----------+
| 平均分    |
+-----------+
|     320.8 |
+-----------+
1 row in set (0.00 sec)-- 返回最高总分
mysql> select max(math + chinese + english) 最高总分 from exam_result;
+--------------+
| 最高总分     |
+--------------+
|          364 |
+--------------+
1 row in set (0.00 sec)-- 返回 > 70 分以上的数学最低分
mysql> select min(math) 数学低分 from exam_result where math > 70;
+--------------+
| 数学低分     |
+--------------+
|           78 |
+--------------+
1 row in set (0.00 sec)-- having 结构
mysql> select math + chinese + english .总分 from exam_result having 总分 > 320;
+--------+
| 总分   |
+--------+
|    329 |
|    364 |
|    321 |
+--------+
3 rows in set (0.00 sec)

4.7 group by

在select中使用group by 子句可以对指定列进行分组查询

-- 显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno; -- 每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;