【MySQL】列的增删查改

目录

Create

insert - 插入

replace - 替换

插入查询结果

Retrieve

select - 查询

基本使用

where 子句

order by - 结果排序

limit - 显示部分结果

聚合函数

group by - 分组查询

Update

Delete

CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）

Create

insert - 插入

语法：

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去重（可能产生大量行锁、日志和长时间阻塞），这种“先建新表，再改名”的方式对线上业务的影响最小。

Retrieve

select - 查询

基本使用

语法：

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);含义：筛选出数学成绩刚好是 58，59，98，99 的同学。
• 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，最后执行 select）

select name,chinese+english+math from exam_result where chinese+english+math as total < 200; // error:不能在where子句进行重命名 select name,chinese+english+math as total from exam_result where total < 200; // error:不能在where子句内使用where子句外的重命名

逻辑运算符：

order by - 结果排序

语法：

-- ASC（ascending order） 为升序 -- DESC （descending order）为降序 -- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

注意：

• 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
• NULL 视为比任何值都小，升序出现在最上面，降序出现在最下面
• order by 的子句内部可以使用外部重命名，比如：select name,chinese+english+math 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 与 having

having 的作用：对分组后的统计数据，进行条件筛选。

案例：求平均工资低于 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 的不同

这两个子句都是用来过滤数据的，但它们的执行时机和过滤对象完全不同：

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 项