表的删改
3. Update
语法:
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
对查询到的结果进行列值更新
案例:
3.1 将孙悟空同学的数学成绩变更为 80 分
-- 更新值为具体值
-- 查看原数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-------+----+
| name |math|
+-------+----+
| 孙悟空 | 78 |
+-------+----+
1 row in set (0.00 sec)
-- 数据更新
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 查看更新后数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+-------+----+
| name |math|
+-------+----+
| 孙悟空 | 80 |
+-------+----+
1 row in set (0.00 sec)
3.2 将曹孟德同学的数学成绩变更为 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)
3.3 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
-- 更新值为原值基础上变更
-- 查看原数据
-- 别名可以在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)
3.4 将所有同学的语文成绩更新为原来的 2 倍
==注意:更新全表的语句慎用!==
-- 没有 WHERE 子句,则更新全表
-- 查看原数据
SELECT * FROM exam_result;
+---+-------+-------+----+-------+
| id| name |chinese|math|english|
+---+-------+-------+----+-------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 80 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 90 | 67 |
| 5 | 刘玄德 | 55 | 115| 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 95 | 30 |
+---+-------+-------+----+-------+
7 rows in set (0.00 sec)
-- 数据更新
UPDATE exam_result SET chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
-- 查看更新后数据
SELECT * FROM exam_result;
+---+-------+-------+----+--------+
| id| name |chinese|math|english |
+---+-------+-------+----+--------+
| 1 | 唐三藏 | 134 | 98 | 56 |
| 2 | 孙悟空 | 174 | 80 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 5 | 刘玄德 | 110 |115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+---+-------+-------+----+--------+
7 rows in set (0.00 sec)
4. Delete
4.1 删除数据
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
案例:
4.1.1 删除孙悟空同学的考试成绩
-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
+---+-------+-------+----+-------+
| id| name |chinese|math|english|
+---+-------+-------+----+-------+
| 2 | 孙悟空 | 174 | 80 | 77 |
+---+-------+-------+----+-------+
1 row in set (0.00 sec)
-- 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)
-- 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)
4.1.2 删除整张表数据
注意:删除整表操作要慎用!
-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
-- 查看测试数据
SELECT * FROM for_delete;
+---+----+
| id|name|
+---+----+
| 1 | A |
| 2 | B |
| 3 | C |
+---+----+
3 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)
-- 再插入一条数据,自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)
-- 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4 | D |
+----+------+
1 row in set (0.00 sec)
-- 查看表结构,会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
知识拓展:
4.2 截断表
语法:
TRUNCATE [TABLE] table_name
==注意:这个操作慎用==
- 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
- 实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事
物,所以无法回滚 - 会重置 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)
5. 插入查询结果
语法:
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
思路:
为啥最后通过rename方式进行呢? -- 就是等一切数据就绪再进行统一放入/更新/生效
不先进行rename再插入呢? -- MySQL当中rename的效率是非常高效的,插入是十分低效的而且也不安全(万一在插入的过程中断点丢失数据是非常危险的)
-- 查看最终结果
SELECT * FROM duplicate_table;
+----+-----+
| id | name|
+----+-----+
| 100| aaa |
| 200| bbb |
| 300| ccc |
+----+-----+
3 rows in set (0.00 sec)
6. 聚合函数
+-------------------------+------------------------------------+
| 函数 | 说明 |
+-------------------------+------------------------------------+
| COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值,不是数字没有意义|
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值,不是数字没有意义|
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值,不是数字没有意义|
+-------------------------+------------------------------------+
案例:
6.1 统计班级共有多少同学
-- 使用 * 做统计,不受 NULL 影响
SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
-- 使用表达式做统计
SELECT COUNT(1) FROM students;
+----------+
| COUNT(1) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
6.2 统计班级收集的 qq 号有多少
-- NULL 不会计入结果
SELECT COUNT(qq) FROM students;
+-----------+
| COUNT(qq) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
6.3 统计本次考试的数学成绩分数个数
-- COUNT(math) 统计的是全部成绩
SELECT COUNT(math) FROM exam_result;
+-------------+
| COUNT(math) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
-- COUNT(DISTINCT math) 统计的是去重成绩数量
SELECT COUNT(DISTINCT math) FROM exam_result;
+----------------------+
| COUNT(DISTINCT math) |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)
6.4 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
+-----------+
| SUM(math) |
+-----------+
| 569 |
+-----------+
1 row in set (0.00 sec)
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
+-----------+
| SUM(math) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
6.4 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
+---------+
| 平均总分 |
+---------+
| 297.5 |
+---------+
6.5 返回英语最高分
SELECT MAX(english) FROM exam_result;
+-------------+
| MAX(english)|
+-------------+
| 90 |
+-------------+
1 row in set (0.00 sec)
6.6 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;
+-----------+
| MIN(math) |
+-----------+
| 73 |
+-----------+
1 row in set (0.00 sec)
7. group by子句的使用
在select中使用group by 子句可以对指定列进行分组查询
select column1, column2, .. from table group by column;
案例:
准备工作,创建一个雇员信息表(来自oracle 9i的经典测试表)
这里我将雇员信息表放在gitee上,点击这里即可!!!
- EMP员工表
- DEPT部门表
- SALGRADE工资等级表
下载到本地上之后导入到云服务的MySQL数据目录下:
-rw-r--r-- 1 root root 3878 Mar 25 2022 scott_data.sql
[root@iZ0jl69kyvg0h181cozuf5Z tt]# pwd
/var/lib/mysql/tt
这里tt是我自己建的数据库(自己选择路径)
将数据库还原出来:
mysql> source /var/lib/mysql/tt/scott_data.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
对应的/var/lib/mysql下同样出现对应的数据库内容文件
[root@iZ0jl69kyvg0h181cozuf5Z mysql]# pwd
/var/lib/mysql
[root@iZ0jl69kyvg0h181cozuf5Z mysql]# ll
drwxr-x--- 2 mysql mysql 4096 Mar 21 09:30 scott
7.1 显示每个部门的平均工资和最高工资
7.2 显示每个部门的每种岗位的平均工资和最低工资
mysql> select deptno,job, min(sal) 最低,avg(sal) 平均 from emp group by deptno,job;
+--------+-----------+---------+-------------+
| deptno | job | 最低 | 平均 |
+--------+-----------+---------+-------------+
| 10 | CLERK | 1300.00 | 1300.000000 |
| 10 | MANAGER | 2450.00 | 2450.000000 |
| 10 | PRESIDENT | 5000.00 | 5000.000000 |
| 20 | ANALYST | 3000.00 | 3000.000000 |
| 20 | CLERK | 800.00 | 950.000000 |
| 20 | MANAGER | 2975.00 | 2975.000000 |
| 30 | CLERK | 950.00 | 950.000000 |
| 30 | MANAGER | 2850.00 | 2850.000000 |
| 30 | SALESMAN | 1250.00 | 1400.000000 |
+--------+-----------+---------+-------------+
9 rows in set (0.00 sec)
7.3 显示平均工资低于2000的部门和它的平均工资
统计各个部门的平均工资
select avg(sal) from EMP group by deptno
having和group by配合使用,对group by结果进行过滤
select deptno,avg(sal) 平均 from EMP group by deptno having myavg<2000;
+--------+-------------+
| deptno | 平均 |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)
--having经常和group by搭配使用,作用是对分组进行筛选,作用有些像where
7.4 SMITH员工不参与计算,显示平均工资低于2000的岗位和它的平均工资
mysql> select deptno,job,avg(sal) 平均 from emp where ename !='SMITH' group by deptno,job having avg(sal) < 2000;+--------+----------+-------------+
| deptno | job | 平均 |
+--------+----------+-------------+
| 10 | CLERK | 1300.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 30 | SALESMAN | 1400.000000 |
+--------+----------+-------------+
4 rows in set (0.00 sec)
切屏小技巧:
练习题
在进行练习之前,请务必熟悉两篇博客当中的内容并且动手实践!!!