【MySQL学习】MySQL表的增删改查操作1：https://developer.aliyun.com/article/1384028
注意：
- 别名不能用在 WHERE 条件中
- 语文成绩 > 80 并且不姓孙的同学
-- AND 与 NOT 的使用 mysql> select name, chinese from exam_result where chinese > 80 and name not like '孙%'; +-----------+---------+ | name | chinese | +-----------+---------+ | 猪悟能 | 88 | | 曹孟德 | 82 | +-----------+---------+ 2 rows in set (0.01 sec) mysql>
- 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
-- 综合性查询 mysql> SELECT name, chinese, math, english, chinese + math + english 总分 -> FROM exam_result -> WHERE name LIKE '孙_' OR ( -> chinese + math + english > 200 AND chinese < math AND english > 80 -> ); +-----------+---------+------+---------+--------+ | name | chinese | math | english | 总分 | +-----------+---------+------+---------+--------+ | 猪悟能 | 88 | 98 | 90 | 276 | | 孙权 | 70 | 73 | 78 | 221 | +-----------+---------+------+---------+--------+ 2 rows in set (0.00 sec)
2.3 ORDER BY结果排序
2.3.1 排序语法
-- ASC 为升序（从小到大） -- DESC 为降序（从大到小） -- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];
注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
2.3.2 排序案例
- 同学名及数学成绩，按数学成绩升序显示
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.00 sec) mysql>
- 同学及 qq 号，按 qq 号排序显示
-- NULL 视为比任何值都小，升序出现在最上面 mysql> select name, qq from students order by qq; +-----------+-------+ | name | qq | +-----------+-------+ | 唐玄奘 | NULL | | 孙仲谋 | NULL | | 曹阿瞒 | NULL | | 孙悟空 | 11111 | +-----------+-------+ 4 rows in set (0.00 sec) -- NULL 视为比任何值都小，降序出现在最下面 mysql> select name, qq from students order by qq desc; +-----------+-------+ | name | qq | +-----------+-------+ | 孙悟空 | 11111 | | 唐玄奘 | NULL | | 孙仲谋 | NULL | | 曹阿瞒 | NULL | +-----------+-------+ 4 rows in set (0.00 sec) mysql>
- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
-- 多字段排序，排序优先级随书写顺序 mysql> select name, chinese, math, english from exam_result order by math desc, english, chinese; +-----------+---------+------+---------+ | name | chinese | math | english | +-----------+---------+------+---------+ | 唐三藏 | 67 | 98 | 56 | | 猪悟能 | 88 | 98 | 90 | | 刘玄德 | 55 | 85 | 45 | | 曹孟德 | 82 | 84 | 67 | | 孙悟空 | 87 | 78 | 77 | | 孙权 | 70 | 73 | 78 | | 宋公明 | 75 | 65 | 30 | +-----------+---------+------+---------+ 7 rows in set (0.00 sec) mysql>
- 查询同学及总分，由高到低
-- ORDER BY 中可以使用表达式 mysql> SELECT name, chinese + english + math FROM exam_result -> ORDER BY chinese + english + math DESC; +-----------+--------------------------+ | name | chinese + english + math | +-----------+--------------------------+ | 猪悟能 | 276 | | 孙悟空 | 242 | | 曹孟德 | 233 | | 唐三藏 | 221 | | 孙权 | 221 | | 刘玄德 | 185 | | 宋公明 | 170 | +-----------+--------------------------+ 7 rows in set (0.00 sec) -- ORDER BY 子句中可以使用列别名 mysql> SELECT name, chinese + english + math 总分 FROM exam_result -> ORDER BY 总分 DESC; +-----------+--------+ | name | 总分 | +-----------+--------+ | 猪悟能 | 276 | | 孙悟空 | 242 | | 曹孟德 | 233 | | 唐三藏 | 221 | | 孙权 | 221 | | 刘玄德 | 185 | | 宋公明 | 170 | +-----------+--------+ 7 rows in set (0.00 sec) mysql> -- 原因在于最后排序是根据查询出来的结果进行排序的，即 select 比 order by 先执行
- 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
-- 结合 WHERE 子句 和 ORDER BY 子句 mysql> select name, math from exam_result -> where name like '孙%' or name like '曹%' -> order by math desc; +-----------+------+ | name | math | +-----------+------+ | 曹孟德 | 84 | | 孙悟空 | 78 | | 孙权 | 73 | +-----------+------+ 3 rows in set (0.00 sec) mysql>
2.4 LIMIT 筛选结果分页
2.4.1 分页语法
-- 起始下标为 0 -- 从 0 开始，筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从 s 开始，筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n; -- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
建议：
对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。
案例：
按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页
-- 第 1 页 mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 0; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 1 | 唐三藏 | 67 | 98 | 56 | | 2 | 孙悟空 | 87 | 78 | 77 | | 3 | 猪悟能 | 88 | 98 | 90 | +----+-----------+---------+------+---------+ 3 rows in set (0.00 sec) -- 第 2 页 mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 3; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 4 | 曹孟德 | 82 | 84 | 67 | | 5 | 刘玄德 | 55 | 85 | 45 | | 6 | 孙权 | 70 | 73 | 78 | +----+-----------+---------+------+---------+ 3 rows in set (0.00 sec) -- 第 3 页，如果结果不足 3 个，不会有影响 mysql> select id, name, chinese, math, english from exam_result order by id limit 3 offset 6; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 7 | 宋公明 | 75 | 65 | 30 | +----+-----------+---------+------+---------+ 1 row in set (0.00 sec) mysql>
2.4.2 分页案例
三、Update操作
3.1 Update语法
语法：
UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
其作用是对查询到的结果进行列值更新。
3.2 案例
- 将孙悟空同学的数学成绩变更为 80 分
-- 更新值为具体值 -- 查看原数据 mysql> select name, math from exam_result where name = '孙悟空'; +-----------+------+ | name | math | +-----------+------+ | 孙悟空 | 78 | +-----------+------+ 1 row in set (0.00 sec) -- 数据更新 mysql> update exam_result set math = 80 where name = '孙悟空'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 查看更新后数据 mysql> select name, math from exam_result where name = '孙悟空'; +-----------+------+ | name | math | +-----------+------+ | 孙悟空 | 80 | +-----------+------+ 1 row in set (0.00 sec)
- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
-- 一次更新多个列 -- 查看原数据 mysql> select name, math, chinese from exam_result where name = '曹孟德'; +-----------+------+---------+ | name | math | chinese | +-----------+------+---------+ | 曹孟德 | 84 | 82 | +-----------+------+---------+ 1 row in set (0.00 sec) -- 数据更新 mysql> update exam_result set math = 60, chinese = 70 where name = '曹孟德'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- 查看更新后数据 mysql> select name, math, chinese from exam_result where name = '曹孟德'; +-----------+------+---------+ | name | math | chinese | +-----------+------+---------+ | 曹孟德 | 60 | 70 | +-----------+------+---------+ 1 row in set (0.00 sec)
- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
-- 更新值为原值基础上变更 -- 查看原数据 -- 别名可以在ORDER BY中使用 mysql> 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 这种语法 mysql> update exam_result set math = math + 30 order by chinese + math + english limit 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 -- 查看更新后数据 -- 注意：这里不能在按照总分前三来查看更新后的数据，因为排名已经发生变化 mysql> 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) -
- 将所有同学的语文成绩更新为原来的 2 倍
注意：更新全表的语句慎用！
-- 没有 WHERE 子句，则更新全表 -- 查看原数据 mysql> 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) -- 数据更新 mysql> update exam_result set chinese = chinese * 2; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 -- 查看更新后数据 mysql> 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)
四、Delete操作
4.1 删除数据
语法：
DELETE FROM table_name [WHERE …] [ORDER BY …] [LIMIT …]
案例：
- 删除孙悟空同学的考试成绩
-- 查看原数据 mysql> select * from exam_result where name = '孙悟空'; +----+-----------+---------+------+---------+ | id | name | chinese | math | english | +----+-----------+---------+------+---------+ | 2 | 孙悟空 | 174 | 80 | 77 | +----+-----------+---------+------+---------+ 1 row in set (0.00 sec) -- 删除数据 mysql> delete from exam_result where name = '孙悟空'; Query OK, 1 row affected (0.00 sec) -- 查看删除结果 mysql> select * from exam_result where name = '孙悟空'; Empty set (0.00 sec)
- 删除整张表数据
注意：删除整表操作要慎用！
-- 准备测试表 CREATE TABLE for_delete ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- 插入测试数据 INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); -- 查看测试数据 mysql> SELECT * FROM for_delete; +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 3 | C | +----+------+ 3 rows in set (0.00 sec) -- 删除整表数据 mysql> DELETE FROM for_delete; Query OK, 3 rows affected (0.00 sec) -- 查看删除结果 mysql> SELECT * FROM for_delete; Empty set (0.00 sec) -- 再插入一条数据，自增 id 在原值上增长 mysql> INSERT INTO for_delete (name) VALUES ('D'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM for_delete; +----+------+ | id | name | +----+------+ | 4 | D | +----+------+ 1 row in set (0.00 sec) -- 查看表结构，会有 AUTO_INCREMENT=n 项 mysql> 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) ); -- 插入测试数据 INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C'); -- 查看测试数据 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)
【MySQL学习】MySQL表的增删改查操作3：https://developer.aliyun.com/article/1384030
