MySQL·表数据的操作-CSDN博客

本文链接：https://blog.csdn.net/weixin_67595436/article/details/138091546

英语不及格的同学及英语成绩 ( < 60 )

语文成绩在 [80, 90] 分的同学及语文成绩

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

查询同学及总分，由高到低

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

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

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

删除表中的的重复复记录，重复的数据只能有一份

补充5：为什么最后是通过rename的方式进行的

显示每个部门的每种岗位的平均工资和最低工资

显示平均工资低于2000的部门和它的平均工资

补充4：MySQL的执行顺序修正3

表数据的增删改查

关于表中数据的操作，其实无非就是异性这几种操作，学习这四种操作基本上就好了

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

Create

增加，对表中数据的增加，其中 [ ] 内的表示可以省略

INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

老规矩，我们创建一张表进行实验

插入

单行数据 + 全列插入

多行数据 + 指定列插入

插入否则更新

语法

INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...

由于主键或者唯一键对应的值已经存在而导致插入失败，即让这两的插入，不报错，而是进行更新修改，我们对这种操作可以选择性的进行同步更新操作

当我们插入并更新时，会发现两行受到影响

当我们插入没有更新时，会发现只有一行受到影响

当我们插入没有更新时，并且插入的数据是一样的，会发现没有数据受到影响

情况总结如下

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

替换

这个和上面的插入否则更新有点不一样，不过功能是差不多的

总结如下

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入

Retrieve

语法

[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

其实在之前我们就已经使用过这个select的查询功能了，这里就详细说明一下
准备工作

创建表结构

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);

插入数据

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

SELECT

全列查询

通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面介绍）

指定列查询

查询字段为表达式

表达式包含一个字段

表达式包含多个字段

为查询结果指定别名

SELECT column [AS] alias_name [...] FROM table_name;

as可以进行省略

结果去重

WHERE 条件

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

注意null因为不参与计算，所以衍生出来了很多关于null的运算符

逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

这个和语言里面是一样的

为了更好的演示，这里就直接用案例来演示了

英语不及格的同学及英语成绩 ( < 60 )

语文成绩在 [80, 90] 分的同学及语文成绩

mysql不区分大小写

我们也可以使用 between …… and …… 这个操作符

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

我们也可以使用 in 操作符来进行筛选

姓孙的同学及孙某同学

补充1：模糊匹配

其中 % 表示全匹配， _ 表示单字匹配(一个 _ 代表一个字符)

语文成绩好于英语成绩的同学

总分在 200 分以下的同学

补充2：MySQL的执行顺序

我们可以看到这里的表达式很长，那么我们是否可以使用重命名的方法去简化呢？

如下是不能的，这里涉及到了MySQL的执行顺序了，我们也可以看到报错是因为unknown未知错误

那我们交换顺序呢？

很遗憾也不允许，重命名操作只有当数据已经筛选出来之后才能进行的

语文成绩 > 80 并且不姓孙的同学

孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

NULL 的查询

-- 查询 students 表
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 唐大师 | NULL |
| 101 | 10001 | 孙悟空 | 11111 |
| 103 | 20002 | 孙仲谋 | NULL |
| 104 | 20001 | 曹阿瞒 | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
-- 查询 qq 号已知的同学姓名
SELECT name, qq FROM students WHERE qq IS NOT NULL;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)
-- NULL 和 NULL 的比较，= 和 <=> 的区别
SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL | NULL | NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1 | 0 | 0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

结果排序

语法

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

asc：ascending order -- 升序

desc：descending order -- 降序

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

老规矩用案例进行验证

同学及数学成绩，按数学成绩升序显示

同学及 qq 号，按 qq 号排序显示

-- NULL 视为比任何值都小，升序出现在最上面
SELECT name, qq FROM students ORDER BY qq;
+-----------+-------+
| name | qq |
+-----------+-------+
| 唐大师 | NULL |
| 孙仲谋 | NULL |
| 曹阿瞒 | NULL |
| 孙悟空 | 11111 |
+-----------+-------+
4 rows in set (0.00 sec)
-- NULL 视为比任何值都小，降序出现在最下面
SELECT name, qq FROM students ORDER BY qq DESC;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
| 唐大师 | NULL |
| 孙仲谋 | NULL |
| 曹阿瞒 | NULL |
+-----------+-------+
4 rows in set (0.00 sec)

可以看出null比空串还小，null比任何值都要小

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

mysql中的order by 默认是asc(升序)，不过在实际操作中建议明确排序方式

查询同学及总分，由高到低

这里我们发现order by是在重命名后面的，所以我们可以使用，因为MySQL的逻辑是是先有数据，再排序的，所以我们可以使用重命名的方式进行简洁

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

补充3：MySQL的执行顺序修正1

筛选分页结果

语法

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

下标从0开始

补充4：MySQL的执行顺序修正2

Update

语法

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

老规矩用案例进行验证

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

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

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

先拿出倒数前三的3位同学

将所有同学的语文成绩更新为原来的 2 倍

注意：更新全表的语句慎用！

Delete

语法

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

老规矩用案例进行验证

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

删除班上倒数第一的学生

删除整张表数据

注意：删除整表操作要慎用

为了更好的测试，这里准备了一张新表

-- 准备测试表
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)

特性1：删除并不会让自增计算器归0

也就意味着着，当我们再插入一个新的数据的时候，会从4开始

截断表

为了上面删除表却有些计算器没有重置而存在的

语法

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚(就是真正意义上的删除，并不会保存日志)
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

当我们进行删除的时候，会发现 auto_increment 没了

其实就是被隐藏起来了，从1开始

插入查询结果

语法

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)
COUNT([DISTINCT] expr) 返回查询到的数据的 数量
SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义
-- 将 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)

1.首先创建一张一模一样的空表，创建表可以使用like

2.用 distinct 查询去重，把查询出来的结果插入到新表之中

3.重命名两张表名字即可

补充5：为什么最后是通过rename的方式进行的

这里衍生了一个现象，我们都知道数据库本质上就是一个个的文件夹，其中的表就是文件夹里面的文件，当我们进行插入的时候，其实就是向文件写入数据，那么一旦涉及到了写入的操作，那么就一定不是原子的，也无法做到原子性，但是就像move(移动)可以是原子的，rename(重命名)这个操作也同样是原子的，所以这个为什么使用rename来完成，其实很好解释，就是单纯的想等一切都就绪了(先将数据写入到临时表中)，然后统一放入、更新、生效

聚合函数

其实MySQL也提供了很多可以供程序员使用的函数，我们可以使用这些函数来进行查询

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

使用案例进行演示

统计班级共有多少同学

统计本次考试的数学成绩分数个数

应该先去重，再统计

统计数学成绩总分

当然我们也可以计算数学成绩的平均分，select后面是可以接表达式的，也可以直接使用它进行计算，不过后面的AVG可以更好的解决这个

统计英语成绩不及格的人数

统计平均总分

使用avg函数可以直接得出平均分

返回英语最高分

不过需要注意的是这样查询是不被允许的，因为聚合是先有数据，并且数据是被允许聚合的，显然这里的name是不能被聚合的，因此我们想要知道英语最高成绩的名字是不能这样做的，后面谈到分组之后才能够较好的解决这个问题，目前的知识只能使用之前的order by + limit 的方式拿出来

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

group by子句的使用

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

语法

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

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）
EMP员工表
DEPT部门表
SALGRADE工资等级表

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
USE `scott`;
 
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);
 
 
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
 
 
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);
 
 
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
 
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
 
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);