目录Createinsert - 插入replace - 替换插入查询结果Retrieveselect - 查询基本使用where 子句order by - 结果排序limit - 显示部分结果聚合函数group by - 分组查询UpdateDeleteCRUD : Create(创建), Retrieve(读取)Update(更新)Delete删除Createinsert - 插入语法INSERT [INTO] table_name [(column_name1 column_name2 ...)] VALUES (value1, value2 ...),(value1, value2 ...); // values 左侧的括号可以省略如果省略那就是全列插入value1, value2...与表中 // 从左到右的列一一对应要对每个列都要插入值 // 如果不省略那就是指定列插入value1, value2...与column_name1 column_name2...一一对应案例-- 创建一张学生表 CREATE TABLE students ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sn INT NOT NULL UNIQUE COMMENT 学号, name VARCHAR(20) NOT NULL, qq VARCHAR(20) ); -- 全列插入 INSERT INTO students VALUES (100, 10000, 唐三藏, NULL); Query OK, 1 row affected (0.02 sec) INSERT INTO students VALUES (101, 10001, 孙悟空, 11111); 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) -- 指定列插入 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)设置插入失败就更新有时候我们插入可能会失败可能由于主键冲突或唯一键冲突此时我们可以设置插入失败就更新来替换原来的数据语法INSERT ... ON DUPLICATE KEY UPDATE column_name1 value1 column_name2 value2...含义如果成功插入那么column_name1 value1 column_name2 value2...。如果 INSERT ... 失败可能由于主键冲突或唯一键冲突就把 column_name1 替换为 value1 column_name 2 替换为 value2当然要保证 value1、value2 要与其他值不产生冲突。类似于 C 的 unordered_map 的 operator[] 的功能。replace - 替换将正常插入的语句的 insert 换成 inplace就等同于上面的设置插入失败就更新的功能INPLACE [INTO] table_name [(column_name1 column_name2 ...)] VALUES (value1, value2 ...),(value1, value2 ...);插入查询结果即 insert 和 select 配合使用可以做到去重的效果INSERT INTO table_name 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)为什么要新创建一个表插入数据到新的表呢直接在原表操作不行吗为什么最后以 rename 的方式让 “duplicate_table” 变为去重后的表具体来说有以下几个关键点1. 原子性操作无缝切换RENAME TABLE是一个原子操作在InnoDB引擎中。这意味着执行重命名时系统会瞬间完成表名的交换其他客户端或应用程序在那一瞬间不会看到“表不存在”的错误也不会看到中间状态。如果直接DROP旧表再ALTER新表改名中间存在时间窗口可能导致业务报错。2. 数据安全保障备份回滚重命名后去重前的原始数据依然完好无损地保存在old_duplicate_table中。如果发现去重后的数据有问题比如SELECT DISTINCT *因为字段默认值或字符集问题导致了意外丢失你可以立即执行RENAME换回来恢复原始数据。如果直接删除旧表一旦发现错误数据就彻底丢失了无法挽回。3. 避免锁表时间过长在这个方案中插入去重数据是在no_duplicate_table新表上进行的这个过程不影响原表的读写。最后一步RENAME只是修改数据字典中的表名指针几乎不耗时毫秒级。相比于直接对原表执行DELETE去重可能产生大量行锁、日志和长时间阻塞这种“先建新表再改名”的方式对线上业务的影响最小。Retrieveselect - 查询基本使用语法select * 或者 column_name1,column_name2... from 表名 where 条件; // select 可以计算表达式 mysql select 1 1; ------- | 1 1 | ------- | 2 | ------- 1 row in set (0.00 sec) // 还可以计算包含不同列的数据的表达式 select column_name1,column_name2,column_name1 column_name2 as sum from 表名; // 显示 column_name1,column_name2 列的信息并且计算 column_name1 column_name2 // 将 column_name1 column_name2 的结果作为 sum 列显示出来 // select 可以对显示的列重命名 select column_name [as] ‘重命名’ from 表名; // select 可以对显示的结果进行去重 select distinct column_name from 表名;注意* 表示全列查询通常情况下不建议使用 * 进行全列查询查询的列越多意味着需要传输的数据量越大。where 子句比较运算符注意在 MySQL判断相等不是 而是 。如果判断相等时有 NULL 参与要用 而不是 因为 是 NULL 安全的。但更建议使用 IS NULL 和 IS NOT NULL 判断。IN使用举例select name,math from exam_result where math in (58,59,98,99);含义筛选出数学成绩刚好是 58599899 的同学。LIKE 模糊匹配使用举例select name,math from exam_result where name like 孙%含义筛选出姓孙的同学的数学成绩。如果 孙% 改为 ‘孙_’含义就是名字只有两个字的同学。在比较时一个列可以与一个常数做比较列与列之间也可以做比较select name,chinese,english from exam_result where chinese english; 含义筛选出语文成绩比英语成绩好的同学。不能在 where 子句内重命名或使用 where 子句之外的重命名比如原因是 SQL 语句的执行顺序先执行 from再执行 where最后执行 selectselect name,chineseenglishmath from exam_result where chineseenglishmath as total 200; // error:不能在where子句进行重命名 select name,chineseenglishmath as total from exam_result where total 200; // error:不能在where子句内使用where子句外的重命名逻辑运算符order by - 结果排序语法-- ASCascending order 为升序 -- DESC descending order为降序 -- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];注意没有 ORDER BY 子句的查询返回的顺序是未定义的永远不要依赖这个顺序NULL 视为比任何值都小升序出现在最上面降序出现在最下面order by 的子句内部可以使用外部重命名比如select name,chineseenglishmath as total from exam_result order by total; 原因是 SQL 语句的执行顺序先执行 from然后执行 select最后执行 order by。select 比 order by 先执行的原因是减少排序的元素提高效率。limit - 显示部分结果语法-- 表的第一行的下标为 0 -- 从下标 0 开始包括下标0筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从下标 s 开始包括下标s筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n; -- 从下标 s 开始包括下标s筛选 n 条结果比第二种用法语义更明确建议使用 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;建议对未知表进行查询时最好加一条 LIMIT 1避免因为表中数据过大查询全表数据导致数据库卡死应用分页利用 limit可以做到分页查看的效果select * from table_name limit 3 offset 0; // 第一页 select * from table_name limit 3 offset 3; // 第二页 select * from table_name limit 3 offset 6; // 第三页 // ...聚合函数案例count 使用案例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 count(*) from exam_result; ---------- | count(*) | ---------- | 7 | ---------- 1 row in set (0.00 sec) // 对查询结果进行重命名 mysql select count(*) 总人数 from exam_result; ----------- | 总人数 | ----------- | 7 | ----------- 1 row in set (0.00 sec) mysql select count(math) 有多少个数学成绩 from exam_result; -------------------------- | 有多少个数学成绩 | -------------------------- | 7 | -------------------------- 1 row in set (0.00 sec) // 对去重后的数量进行统计 // 下面是错误演示这是对 count(math) 之后去重就一个数字去什么重 mysql select distinct count(math) 有多少个不同的数学成绩 from exam_result; ----------------------------------- | 有多少个不同的数学成绩 | ----------------------------------- | 7 | ----------------------------------- 1 row in set (0.00 sec) // 下面才是正确演示先对 math 去重再 count mysql select count(distinct math) 有多少个不同的数学成绩 from exam_result; ----------------------------------- | 有多少个不同的数学成绩 | ----------------------------------- | 6 | ----------------------------------- 1 row in set (0.00 sec)sum 使用案例mysql select sum(math)/count(*) 数学平均分 from exam_result; ----------------- | 数学平均分 | ----------------- | 83 | ----------------- 1 row in set (0.00 sec)avg 使用案例mysql select avg(math) 数学平均分 from exam_result; ----------------- | 数学平均分 | ----------------- | 83 | ----------------- 1 row in set (0.00 sec)max 和 min 使用案例mysql select max(math) 数学最高分 from exam_result; ----------------- | 数学最高分 | ----------------- | 98 | ----------------- 1 row in set (0.00 sec) mysql selectt min(math) 数学最低分 from exam_result; ----------------- | 数学最低分 | ----------------- | 65 | ----------------- 1 row in set (0.00 sec)group by - 分组查询语法select column1, column2, .. from table group by column;案例在员工信息表中按部门分组统计各部门的员工的最高工资和平均工资mysql select * from EMP; ---------------------------------------------------------------------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ---------------------------------------------------------------------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | ---------------------------------------------------------------------- 14 rows in set (0.00 sec) mysql select DEPTNO,max(SAL) 最高工资,avg(SAL) 平均工资 from EMP group by DEPTNO; ------------------------------------ | DEPTNO | 最高工资 | 平均工资 | ------------------------------------ | 10 | 5000.00 | 2916.666667 | | 20 | 3000.00 | 2175.000000 | | 30 | 2850.00 | 1566.666667 | ------------------------------------ 3 rows in set (0.00 sec)对 group by 的理解group by DEPTNO 其实可以理解为把一张表按照 DEPTNO 分成不同的子表相同 DEPTNO 的员工在同一个子表然后对每张子表进行统计。每张子表还可以根据不同的列进行分组比如统计不同部门的不同岗位的最高和平均工资mysql select DEPTNO,JOB,max(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 | 1100.00 | 950.000000 | | 20 | MANAGER | 2975.00 | 2975.000000 | | 30 | CLERK | 950.00 | 950.000000 | | 30 | MANAGER | 2850.00 | 2850.000000 | | 30 | SALESMAN | 1600.00 | 1400.000000 | ----------------------------------------------- 9 rows in set (0.00 sec)group by 与 havinghaving 的作用对分组后的统计数据进行条件筛选。案例求平均工资低于 2000 的部门和它的平均工资// 第一步求出各部门的平均工资 mysql select DEPTNO,avg(SAL) 平均工资 from EMP group by DEPTNO; ---------------------- | DEPTNO | 平均工资 | ---------------------- | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | ---------------------- 3 rows in set (0.00 sec) // 第二步对求出的各部门的平均工资进行条件筛选 mysql select DEPTNO,avg(SAL) 平均工资 - from EMP group by DEPTNO having 平均工资 2000; ---------------------- | DEPTNO | 平均工资 | ---------------------- | 30 | 1566.666667 | ---------------------- 1 row in set (0.00 sec)where 和 having 的不同这两个子句都是用来过滤数据的但它们的执行时机和过滤对象完全不同对比项WHEREHAVING过滤时机在分组之前过滤在分组之后过滤过滤对象过滤行记录原始数据过滤分组结果聚合后的数据能否用聚合函数❌ 不能使用聚合函数如 SUM, AVG, COUNT✅ 可以使用聚合函数能否用列别名❌ 大部分数据库不支持✅ 可以用取决于数据库执行顺序先执行 WHERE再 GROUP BY最后 HAVING在 GROUP BY 之后执行Update语法UPDATE table_name SET column_name1 表达式或具体值,column_name2 表达式或具体值,... [WHERE ...] [ORDER BY ...] [LIMIT ...]作用对查询到的结果进行列值更新。如果不进行查询直接进行 UPDATE table_name SET column_name1 num那么表的所有 column_name1 列的数据都被改为了 num这是很危险的行为。通常 update 要先进行查询将孙悟空同学的数学成绩变更为 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)Delete语法DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]-- 查看原数据 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)注意delete 删除操作不影响表的 auto_increment 表的 auto_increment 不变。截断表语法TRUNCATE [TABLE] table_name作用一键清空表的所有数据注意这个操作慎用只能对整表操作不能像 DELETE 一样针对部分数据操作实际上 MySQL 不对数据操作所以比 DELETE 更快但是TRUNCATE在删除数据的时候并不经过真正的事务所以无法回滚会重置 AUTO_INCREMENT 项