标签搜索

目 录CONTENT

文章目录

MySQL的基本查询(二).md

小小城
2021-08-22 / 0 评论 / 0 点赞 / 5 阅读 / 7,506 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-05-02,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

MySQL的基本查询(二)

@[toc]

3. Update

语法:

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

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

案例:

3.1 将孙悟空同学的数学成绩变更为 80 分

-- 更新值为具体值
-- 查看原数据
SELECT name, shuxue FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name 		| shuxue |
+-----------+--------+
| 孙悟空 	| 78 	 |
+-----------+--------+
1 row in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET shuxue = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- 查看更新后数据
SELECT name, shuxue FROM exam_result WHERE name = '孙悟空';
+-----------+--------+
| name 	 	| shuxue |
+-----------+--------+
| 孙悟空 	| 80 	 |
+-----------+--------+
1 row in set (0.00 sec)

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

-- 一次更新多个列
-- 查看原数据
SELECT name, shuxue, yuwen FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
| name 		| shuxue | yuwen |
+-----------+--------+-------+
| 曹孟德 	| 84 	 | 82	 |
+-----------+--------+-------+
1 row in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET shuxue = 60, yuwen = 70 WHERE name = '曹孟德';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- 查看更新后数据
SELECT name, shuxue, yuwen FROM exam_result WHERE name = '曹孟德';
+-----------+--------+-------+
| name 		| shuxue | yuwen |
+-----------+--------+-------+
| 曹孟德 	| 60 	 | 70	 |
+-----------+--------+-------+
1 row in set (0.00 sec)

3.3 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

-- 更新值为原值基础上变更
-- 查看原数据
SELECT name, shuxue, yuwen + shuxue + yingyu 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;
+-----------+--------+--------+
| name 		| shuxue | 总分	  |
+-----------+--------+--------+
| 宋公明 	| 65	 | 170 	  |
| 刘玄德 	| 85	 | 185	  |
| 曹孟德 	| 60 	 | 197	  |
+-----------+--------+--------+
3 rows in set (0.00 sec)

-- 数据更新,不支持 shuxue += 30 这种语法
UPDATE exam_result SET shuxue = shuxue + 30
ORDER BY yuwen + shuxue + yingyu LIMIT 3;

-- 查看更新后数据
-- 思考:这里还可以按总分升序排序取前 3 个么?
SELECT name, shuxue, yuwen + shuxue + yingyu 总分 FROM exam_result
WHERE name IN ('宋公明', '刘玄德', '曹孟德');
+-----------+--------+--------+
| name 		| shuxue | 总分	 |
+-----------+--------+--------+
| 曹孟德 	| 90 	 | 227	 |
| 刘玄德 	| 115 	 | 215	 |
| 宋公明 	| 95 	 | 200	 |
+-----------+--------+--------+
3 rows in set (0.00 sec)

-- 按总成绩排序后查询结果
SELECT name, shuxue, yuwen + shuxue + yingyu 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;
+-----------+--------+--------+
| name 		| shuxue | 总分 |
+-----------+--------+--------+
| 宋公明 	| 95 	 | 200 |
| 刘玄德 	| 115 	 | 215 |
| 唐三藏 	| 98 	 | 221 |
+-----------+--------+--------+
3 rows in set (0.00 sec)

3.4 将所有同学的语文成绩更新为原来的 2 倍
注意:更新全表的语句慎用!

在这里插入图片描述
在这里插入图片描述

4. Delete

4.1 删除数据

语法:

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

案例:

4.1.1 删除孙悟空同学的考试成绩

-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
+----+-----------+-------+--------+--------+
| id | name 	 | yuwen | shuxue | yingyu |
+----+-----------+-------+--------+--------+
| 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

注意:这个操作慎用

  1. 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
  2. 实际上 MySQL 不对数据操作,所以比 DELETE 更快
  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)

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

思路:

-- 创建一张空表 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)

6. 聚合函数

在这里插入图片描述

案例:

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(shuxue) 统计的是全部成绩
SELECT COUNT(shuxue) FROM exam_result;
+---------------+
| COUNT(shuxue) |
+---------------+
| 6 			|
+---------------+
1 row in set (0.00 sec)
	
-- COUNT(DISTINCT shuxue) 统计的是去重成绩数量
SELECT COUNT(DISTINCT shuxue) FROM exam_result;
+------------------------+
| COUNT(DISTINCT shuxue) |
+------------------------+
| 5 					 |
+------------------------+
1 row in set (0.00 sec)

6.4 统计数学成绩总分:

SELECT SUM(shuxue) FROM exam_result;
+-------------+
| SUM(shuxue) |
+-------------+
| 569 |
+-------------+
1 row in set (0.00 sec)

-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM(shuxue) FROM exam_result WHERE shuxue < 60;
+-------------+
| SUM(shuxue) |
+-------------+
| NULL 		  |
+-------------+
1 row in set (0.00 sec)

6.4 统计平均总分:

SELECT AVG(yuwen + shuxue + yingyu) 平均总分 FROM exam_result;
+--------------+
| 平均总分 		|
+--------------+
| 297.5 		|
+--------------+

6.5 返回英语最高分:

SELECT MAX(yingyu) FROM exam_result;
+-------------+
| MAX(yingyu) |
+-------------+
| 90 		  |
+-------------+
1 row in set (0.00 sec)

6.6 返回 > 70 分以上的数学最低分:

SELECT MIN(shuxue) FROM exam_result WHERE shuxue > 70;
+-------------+
| MIN(shuxue) |
+-------------+
| 73 		  |
+-------------+
1 row in set (0.00 sec)

7. group by子句的使用

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

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

案例:

  • 准备工作,创建一个雇员信息表
  • EMP员工表
  • DEPT部门表
  • SALGRADE工资等级表
  • 如何显示每个部门的平均工资和最高工资???
select deptno,avg(sal),max(sal) from EMP group by deptno;
  • 显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
  • 显示平均工资低于2000的部门和它的平均工资
  • 统计各个部门的平均工资
select avg(sal) from EMP group by deptno
  • having和group by配合使用,对group by结果进行过滤
select avg(sal) as myavg from EMP group by deptno having myavg<2000;

==SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having >select > distinct > order by > limit==

0

评论区