MySQL 表的增删查改

这时候我们希望能够进行可以选择性的进行同步更新操作而不是直接报错

语法：

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

第一个错误是因为主键冲突，第二个错误是因为我们尝试更新的数据和其他行数据也冲突了

相当于是多做一次尝试，如果语句冲突了，就把 insert 操作改成 update 操作

需要注意的是你也要保证更新的数据不要和其他行数据的主键发生冲突！！

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，并且数据已经被更新

也可通过 MySQL row_count() 函数获取受到影响的数据行数 (-1 表示没有)

1.6 插入是否替换（replace）

-- 主键 或者 唯一键 没有冲突，则直接插入；

-- 主键 或者 唯一键 如果冲突，则删除后再插入

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，删除后重新插入

二、Retrieve（select）

语法:

SELECT [DISTINCT]//去重 {* | {column [, column] ...} FROM table_name //从某个表里去提取 [WHERE ...] //筛选条件 [ORDER BY column [ASC | DESC], ...] //排序 LIMIT ... //限定筛选出来的结果条数 

-- 创建表结构

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);

2.1 select 列

2.1.1 全列查询（*）

-- 通常情况下不建议使用 * 进行全列查询

-- 1. 查询的列越多，意味着需要传输的数据量越大（线性遍历）；

-- 2. 可能会影响到索引的使用。

2.1.2 指定列查询

-- 指定列的顺序不需要按定义表的顺序来

2.1.3 查询字段为表达式

-- 表达式不包含字段

-- 表达式包含一个字段

-- 表达式包含多个字段

2.1.4 为查询结果指定别名 (as)

语法：

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

多个别名：

2.1.5 结果去重（distinct）

-- 98 分重复了

-- 去重结果 + distinct

2.2 where 条件

比较运算符：

逻辑运算符：

案例 1：英语不及格的同学即英语成绩 (< 60)

SELECT name, english FROM exam_result WHERE english < 60;

案例 2：语文成绩在 [80, 90] 分的同学及语文成绩

-- 使用 AND 进行条件连接

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

-- 使用 BETWEEN ... AND ... 条件

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

案例 3：数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

-- 使用 OR 进行条件连接

SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;

-- 使用 IN 条件

SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

案例 4：姓孙的同学 及 孙某同学

-- % 匹配任意多个（包括 0 个）任意字符

SELECT name FROM exam_result WHERE name LIKE '孙%';

-- _ 匹配严格的一个任意字符

SELECT name FROM exam_result WHERE name LIKE '孙_';

案例 5：语文成绩好于英语成绩的同学

-- WHERE 条件中比较运算符两侧都是字段

SELECT name, chinese, english FROM exam_result WHERE chinese > english;

案例 6：总分在 200 分以下的同学

-- WHERE 条件中使用表达式

SQL 的筛选顺序并不代表执行顺序，首先我得知道 from 哪张表找，然后要带着 where 的筛选条件，接着才是进行具体的筛选

根据上图我们会发现如果直接在筛选条件那里重命名也是不可以的！！因为对列做重命名已经是属于显示范畴了，相当于是已经把数据拿完了然后在最后把列名字改一改，是最后一步了！所以语法上不允许的！！

了解 mysql 语句的执行顺序可以帮助我们更好地理解一些奇怪的现象

-- 别名不能用在 WHERE 条件中

SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200;

案例 7：语文成绩 > 80 并且不姓孙的同学

-- AND 与 NOT 的使用

SELECT name, chinese FROM exam_result WHERE chinese > 80 AND name NOT LIKE '孙%';

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

-- 综合性查询

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);

2.3 NULL 的查询

' ' 和 NULL 没有关系！！

-- NULL 和 NULL 的比较，= 和 <> 的区别

2.4 结果排序 (order by)

语法:

SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...]; //依据哪一列做排序

-- ASC 为升序（从小到大） //ascending order

-- DESC 为降序（从大到小） //descending order

-- 默认为 ASC

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

案例 1：同学及数学成绩，按数学成绩升序显示

案例 2：同学及 qq 号，按姓名排序显示

--NULL 视为比任何值都小，升序出现在最上面。

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

--多字段排序，排序优先级随书写顺序

案例 4：查询同学及总分，由高到低

--ORDER BY 中可以使用表达式

--ORDER BY 子句中可以使用列别名

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

-- 结合 WHERE 子句 和 ORDER BY 子句

2.5 筛选分页结果 (limit)

语法:

-- 起始下标为 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、from 先确保在哪个表里找 2、where 带着筛选条件 3、开始做筛选工作（前三步都是在筛选）4、as 起别名 5、order 根据别名进行排序之后的显示 6、limit 分页显示（后三步是在显示）

三、Update

语法：

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

对查询到的结果进行列值更新（一般要加 where 条件否则会全部被更新）

案例 1：将孙悟空同学的数学成绩变更为 80 分

-- 更新值为具体值

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

-- 一次更新多个列

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

-- 数据更新，不支持 math+=30 这种语法

update exam_result set math=math+30 order by chinese+english+math asc limit 3;

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

注意：更新全表的语句慎用！-- 没有 WHERE 子句，则更新全表

update exam_result set chinese=chinese*2;

四、Delete

4.1 删除数据

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

案例 1：删除孙悟空同学的考试成绩

delete from exam_result where name='孙悟空';

案例 2：去掉班级的第一名

delete from exam_result order by english+math+chinese asc limit 1;

案例 3：删除整张表数据（delete 只是删表数据，不删表结构）

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

-- 先插入一点数据

-- 删掉整张表数据

我们会发现我们只是把表数据给删除了，但是表的结构还在！！计数器没有变

4.2 截断表（truncate）

TRUNCATE [TABLE] table_name

-- 插入数据

-- 清空表

我们会发现计数器被重置了！！

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；

2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是 TRUNCATE 在删除数据的时候，并不经过真正的事务（不会被记录到日志里），所以无法回滚

三种日志：

bin log：历史上操作过的 sql 语句优化之后保留下来——方便主从同步、备份、恢复

redo log：确保宕机、断电的时候数据不丢失（因为数据可能在内存中存着）——保证崩溃安全

undo log：做事务回滚、事务的隔离性

3. 会重置 AUTO_INCREMENT 项

五、插入查询结果（insert+select）

INSERT INTO table_name [(column [, column ...])] SELECT ...

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

-- 建表

-- 插入测试数据

不能用 distinct 的去重，因为他并不影响原表

但是我们可以将 insert 和 select 结合起来用，将 distinct 筛选出来的数据插入到空表中！！然后再改一下表的名字！！

第一步：create table no_duplicate_table like duplicate_table; 建立一张和原表结构相同的空表

第二步：insert into no_duplicate_table select distinct * from duplicate_table; 查询原表去重后的结果然后插入到新表中

第三步：rename table duplicate_table to old_duplicate_table, no_duplicate_table to duplicate_table; 将原表重命名备份一下，然后再把新表的名字改成原表的名字

问题：为什么最后是通过 rename 的方式进行的？

——> 创建一个数据库其实就是创建一个文件夹，创建一张表其实就是创建一个文件，对应的系统调用就是 mkdir 和 touch，而 rename 背后的也是类似 rename 这样的系统调用，平时我们用的 move 指令重命名也是类似的，如果我今天想把一个文件上传到 linux 下，可能上传得很慢，我想等这个文件上传好之后，把这个文件放到某个目录下，我希望他放入的过程是原子的，所以我们一定不能直接把这个文件直接上传到对应的目录下，因为上传的过程一直在写入，一定不是原子的，所以一般我们喜欢这个要上传的文件上传到一个临时的目录下，等全都上传完成之后，再把整个文件 move 到特定的目录下，这个 move 是原子的。

所以总的来说，单纯就是相等一切都就绪了，然后统一放入、更新、生效等。因为我们的move 操作和重命名操作实际上就是在文件系统里就是改这个文件所在的目录里面文件名和 inode 的映射关系，他相较于冗长地向表中插入和冗长的上传行为比起来非常轻。很有可能我这个目录有很多文件包括正在操作的这个文件正在被外部的网站或者各种语言正在访问，所以我们不能着急动这个表而是应该先把这个表先传到临时目录然后再统一 move 过去，这是一种比较推荐的做法

六、聚合函数

案例 1：统计班级共有多少同学

-- 使用 * 做统计，不受 NULL 影响

-- 使用表达式做统计

案例 2：统计本次考试的数学成绩分数个数

-- NULL 不会计入结果

-- COUNT(math) 统计的是全部成绩

-- COUNT(DISTINCT math) 统计的是去重成绩数量

案例 3：统计数学成绩总分

案例 4：统计数学的平均分

案例 5：数学不及格的人有多少

案例 6：统计平均总分

案例 7：返回英语最高分

案例 8：返回 > 70 分以上的数学最低分

聚合函数：1、在应用层上更多的是在未来进行某种程度上的数据统计，是有自己的现实需求的 2、大部分聚合都是简单的场景，还有一部分场景需要对信息做完分组之后做聚合

七、分组聚合统计（group by）

分组的目的是为了方便后面的聚合统计（比如说分成男生女生然后分别做统计）

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

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

案例：准备工作，创建一个雇员信息表（来自 oracle 9i 的经典测试表）

1、EMP 员工表

2、DEPT 部门表

3、SALGRADE 工资等级表

//利用 source 将该备份文件恢复到数据库中
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);

1、显示每个部分的平均工资和最高工资

select deptno,max(sal) 最高，avg(sal) 平均 from EMP group by deptno;

对先分组——>再聚合统计的三种理解：

1、指定列名 (分组的条件 depton)，实际分组是用该列的不同的行数数据（组内一定是相同的—->可以被聚合压缩）来进行分组的！

2、分组，就是把一组按照条件拆分成多个组，然后各自进行组内的聚合统计

3、分组（'分表'），就是把一张表按照条件在调剂上拆成了多个子表，然后分别对各自的子表进行聚合统计

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

select deptno,job,avg(sal) 平均，min(sal) 最低 from EMP group by deptno, job;

注意：不能在 select 后面跟具体的并不是分组条件的列，只能是具体的分组条件的列以及聚合函数

报错原因：ename 没有在分组条件中出现，无法进行压缩聚合

group by 是一个分组函数，你要筛查的数据列，都应该考虑一个情况，就是分组的时候，如果当前分组条件相同，接下来的分组依据是什么！

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

（1）统计各个部门的平均工资

select deptno,avg(sal) deptavg from EMP group by deptno;

（2）having 和 group by 配合使用，对 group by 结果进行过滤

-- having 经常和 group by 搭配使用，作用是对聚合后的统计数据进行条件筛选，作用有些像 where。

select deptno,avg(sal) deptavg from EMP group by deptno having deptavg<2000;

where vs having

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