本文深入探讨MySQL数据库的各种高级特性，包括自增主键行为、数据类型对比、锁机制详解、存储引擎差异、数据库规范化、SQL注入防御、数据去重策略、大对象存储、更新操作的影响、查询语法与执行流程、基础命令、建表语句、时间戳自动记录、复杂SQL语句构造、日期时间函数应用、系统日期函数、去重技巧、函数汇总、悲观锁使用、Tinyint数据格式、聚合函数运用、分组连接函数、NULL值处理、索引优化、exists关键字、高级函数、高级查询技巧等，旨在提升数据库管理和查询效率。
MySQL
- MySql面试题
- 1. 在Mysql中有一张表，其ID是自增主键，有10条记录，ID从1到10，现在删掉最后2条记录，重启MySql服务器，再往表中insert一条记录，这条记录的ID是11还是9？
- 2. CHAR和VARCHAR的区别？
- 3. MySql有哪几种锁？怎么查看InnoDB的锁？
- 4. MySQL数据库中MyISAM和InnoDB的区别?
- 5. 数据库三大范式？
- 6. 什么是Sql注入？
- 7. CLOB字符串大对象 和 BLOB二进制大对象
- 8. 往mysql执行2次相同的update操作，mysql受影响行数第一次为1，第二次为0 ，但是受影响行数为0 的时候，mybatis依旧返回1，这时候可以使用useAffectedRows=true,再次执行相同更新的时候就返回0了
- MySql学习
- S0. 事务ACID的实现原理
- S0+. MySQL的OFFSET
- S1. Mysql完整查询语法和执行顺序
- S2. MySql基础
- S3. 建表语句示例
- S4. mysql5.6 支持两个timestamp列，新建或更新记录时，自动记录时间戳
- S5. case when then else end语法
- S6. date_format函数和str_to_date函数
- S7. sysdate函数
- S8. Distinct 单列去重+列组合去重
- S9. count 函数
- S10. ALL | ANY | SOME | IN
- S11. Union | Union ALL
- S12. 实现Oracle的nulls first 和 nulls last功能，即null在前和null在后
- S13. MySql的索引
- S14. exists关键字
- S15. 常用函数汇总
- S16. SELECT FOR UPDATE 悲观锁
- S17. Tinyint(1)数据格式和实体类中的Boolean类型自动转换
- S18. SUM()
- S19. GROUP_CONCAT
- Sql优化
- MySql实战
MySql面试题
1. 在Mysql中有一张表，其ID是自增主键，有10条记录，ID从1到10，现在删掉最后2条记录，重启MySql服务器，再往表中insert一条记录，这条记录的ID是11还是9？
- ① 如果表的类型是MyISAM的话，结果是11，因为MyISAM表会把自增主键的最大值持久化到文件中，重启依旧不会丢失最大值。
- ② 如果表的类型是InnoDb的话，结果是9。因为InnoDb表只是把自增主键的最大值存到内存中，一旦重启mysql服务器就丢失了最大值。
2. CHAR和VARCHAR的区别？
| 特性 | CHAR(n) | VARCHAR(n) |
|---|---|---|
| 存储方式 | 固定长度 | 可变长度 |
| 额外开销 | 无 | 1~2 字节记录长度 |
| 尾部空格 | 不足长度尾部补空格存储；查询自动去除尾部空格 | 原样保存空格 |
| 最大长度 | 255 | 65535 (行总长度受限) |
| 性能 | 读取快，无需计算长度 | 略慢，需要解析长度 |
| 空间利用率 | 数据短时浪费空间 | 节省存储空间 |
| 使用场景 | 长度固定数据，手机号、身份证、MD5、枚举编码 | 长短不一文本，用户名、地址、备注 |
3. MySql有哪几种锁？怎么查看InnoDB的锁？
-
MyISAM只支持表锁， InnoDB支持表锁和行锁`
- 1.表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发量最低。
- 2.行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发量也最高。
-
InnoDB行级锁类型
共享锁S：又称读锁，简单讲就是多个事务对同一数据进行共享一把锁，都能访问到数据，但是只能读不能修改。排他锁X：又称写锁，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的排他锁，只有获取排他锁的事务可以对数据进行修改。
-
如何查看 InnoDB 的锁信息？
- 示例
# A事务 不提交
START TRANSACTION;
UPDATE bbc t set t.area = area+1 where t.`name` = 'Albania';
# B事务 不提交
START TRANSACTION;
UPDATE bbc t set t.area = area+1 where t.`name` = 'Albania';
# 查看此时innodb锁状态信息 X：代表排他锁 S：代表共享锁（读锁）
select * from information_schema.innodb_locks;
show engine innodb status; #InnoDB整体状态，其中包括锁的情况
4. MySQL数据库中MyISAM和InnoDB的区别?
| 对比项 | MyISAM | InnoDB（默认） |
|---|---|---|
| 事务 | ❌ 不支持 | ✅ 支持事务、ACID |
| 锁粒度 | 表锁 | 行锁 + 表锁；支持 MVCC |
| 外键 | ❌ 不支持 | ✅ 支持外键 |
| 崩溃恢复 | ❌ 易损坏，需修复 | ✅ 依靠 redo/undo 日志安全恢复 |
| 并发读写 | 差，写阻塞读、读阻塞写 | 优秀，读写可并发（MVCC） |
| 主键 | 可有可无 | 强烈建议必须主键 |
| 缓存 | 只缓存索引 | 缓存索引 + 数据页 |
| 聚集索引 | ❌ 非聚集索引 | ✅ 聚集索引结构 |
| 全文索引 | 原生支持 | 5.6 之后支持 |
| 硬盘存储 | MyISAM包含表定义文件，数据文件和索引文件 | InnoDB没有专门的数据文件，它的数据和索引都放在一个文件中，数据以主键进行聚集存储，真正的数据保存在索引的叶子节点上 |
5. 数据库三大范式？
- 所谓范式就是设计规范。
第一范式（1NF）：每一列属性都是不可再分的属性值。
如: 电话可以拆分成 家庭电话，公司电话，手机号。就不符合第一范式。
第二范式（2NF）：在满足1NF的基础上，要求数据库表中的每一列属性都与主键属性相关。如学生表中有主键学号，那么表中其它字段都可以根据学号去获取。
《学生表》
[学号] [姓名] [年龄] [手机号] [所在院校] [院校信息] [所在宿舍]
第三范式（3NF）：在满足2NF的基础上，要求数据不能存在传递关系，即每一列属性都跟主键有直接关系而不是间接关系。
//院校地址和院校电话和学生没有直接关系，不符合第三范式
Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
//将表分为Student表和school表就达到第三范式的要求
Student表（学号，姓名，年龄，性别，所在院校）
school表（所在院校，院校地址，院校电话）
6. 什么是Sql注入？
- 所谓SQL注入就是一种将SQL代码添加到输入参数中，传递到SQL服务器并执行的一种攻击手法.。
String sql =
"select * from user where username='"+username+"'and password='"+password+"'";
SQL--> select * from user where username='username' and password='password';
-----注入的两种方式-----
1.在用户名后面拼接一个永等式，输入zs' or '1' = '1
SQL-->select * from user where username='zs' or '1' = '1' and password='password';
2.使用'#注释后面的SQL语句,在用户名后面拼接一个'#
SQL-->select * from user where username='zs'#'and password='password';
7. CLOB字符串大对象 和 BLOB二进制大对象
-
BLOB 和 CLOB 都是大字段类型（
MySQL 无 CLOB，TEXT 对应 CLOB） -
BLOB 是按二进制来存储的，而 CLOB 是可以直接存储文字的
✅ BLOB：图片、音频、二进制文件
✅ CLOB / TEXT：长篇文字、富文本 -
ORACLE数据库BLOB：通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。CLOB： 文章或者是较长的文字，就用CLOB存储，这样对以后的查询更新存储等操作都提供很大的方便。
8. 往mysql执行2次相同的update操作，mysql受影响行数第一次为1，第二次为0 ，但是受影响行数为0 的时候，mybatis依旧返回1，这时候可以使用useAffectedRows=true,再次执行相同更新的时候就返回0了
jdbc:mysql://localhost:3306/nz1906?characterEncoding=utf-8&useAffectedRows=true
MySql学习
S0. 事务ACID的实现原理
原子性（Atomicity）
- innodb实现核心：利用undo log 回滚日志来实现
1.执行事务修改数据前，innodb会把旧数据拷贝到undo log中
2.如果事务执行失败/手动回滚，innodb会直接读取undo log中的旧数据进行还原
一致性（Consistency）
- 数据库总是从一个一致性状态转换到另外一个一致性状态
1.比如转账前后的总金额不变: 账户A-100 账户B+100
2.主键约束保证了每一条的数据的ID是唯一的
隔离性（Isolation）
-
多个事务并发执行时，互不干扰，互相看不见，像串行执行一样
-
innodb实现隔离核心：MVCC（多版本并发控制，给每行数据维护多个版本）+ 锁机制（ 行锁，表锁，间隙锁 {解决幻读问题} ），完美解决脏读、不可重复读、幻读（事务并发会产生的3个问题）1.脏读： 执行事务时，读到其它事务未提交的脏数据2.不可重复度：在同一事务中，同样的查询条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）3.幻读：事务A在按查询条件读取某个范围的记录时，事务B又在该范围内插入了新的满足条件的记录，当事务A再次按条件查询记录时，会产生新的满足条件的记录（幻行）
-
MVCC机制读不加锁、读写互不阻塞，解决并发读一致性问题，支撑事务隔离，MVCC机制原理如下：- 核心前提：Innodb引擎 + 隔离级别（读已提交/可重复读）
- 三个核心日志：undoLog回滚日志 + 行隐藏列 + ReadView读视图
- 极简总结：
表隐藏列记录版本 +UndoLog串联历史版本链，ReadView判定数据可见，实现无锁并发读写隔离。
## 一、undo Log 版本链
数据每次修改，生成新版本，旧版本存入undo log，通过roll_ptr串联成版本链表。
## 二、行隐藏列
DB_TRX_ID = 最后修改该行的事务ID
DB_ROLL_PTR = 指向undo log的历史版本指针
DB_ROW_ID = 行唯一标识（无主键时使用）
## 三、ReadView 读视图（判断可见性核心）
事务快照，查询瞬间生成，4 个关键属性
1. m_ids：当前活跃未提交事务 ID 集合
2. min_trx_id：活跃事务最小 ID
3. max_trx_id：下一个待分配事务 ID
4. creator_trx_id：当前查询事务自身 ID
### 可见性判断规则
遍历版本链，满足任一即可读取该行：
1. 记录的事务ID < min_trx_id：已提交，可见
2. 记录的事务ID > max_trx_id：未开启，不可见
3. 介于区间内：不在活跃集合则可见，活跃则不可见
## 四、两大隔离级别下 MVCC 差异
### 1. 可重复读 RR（MySQL 默认）
- **事务首次查询才生成 ReadView**，全程复用
- 整个事务快照一致，**解决不可重复读**
- 配合间隙锁，规避幻读
### 2. 读已提交 RC
- **每次查询都重新生成 ReadView**
- 能读到其他事务已提交新数据，无法避免不可重复读
## 五、 删改查版本操作
1. **查询**：不走锁，按 ReadView 遍历版本链，返回可见版本
2. **插入**：生成新版本，trx_id 标记当前事务
3. **更新**：拷贝旧数据生成新版本，旧版本入 undoLog 版本链
4. **删除**：标记删除位，真正删除由 purge 线程后台清理旧版本
持久性（Durability）
- 事务一旦提交成功，数据就永久保存到数据库，即使宕机、断电也不会丢失
- innodb实现核心：redo log重做日志 + 刷盘机制
1.INNODB先把修改记录到redo log中（顺序写 极快）
2.事务提交后，只要redo log落盘，就代表事务提交成功
3.后台线程慢慢把redo log同步到数据文件
4.数据库崩溃重启时，INNODB会读取redo log恢复已提交的数据
# redo log逻辑上是一条一条的物理修改记录，描述 “哪个表空间、哪个页、哪个偏移、改成了什么
S0+. MySQL的OFFSET
1. 语法
SELECT * FROM table LIMIT offset, size;
-- 等价写法
SELECT * FROM table LIMIT size OFFSET offset;
- 跳过前 20 条，取出接下来 10 条
SELECT * FROM user LIMIT 10 OFFSET 20;
2. 底层真实执行逻辑（重点！）
MySQL 不会直接跳到第 offset 行
执行流程：
- 从磁盘 / 缓冲区读取前 offset + size 条数据
- 丢弃前面 offset 条
- 返回剩余 size 条给客户端
⚠️ 致命问题：
当 offset 很大（offset>100000），数据库需要扫描大量数据，性能急剧下降。
3. 使用场景
✅ 适用场景（小分页）
-
后台管理系统分页
管理员一般只翻看前几十页，offset 很小，压力不大。
-- 查看第3页，每页20条 limit 20 offset 40
❌ 不适用场景（大分页、APP 下拉翻页）
- 用户拉到第 10000 页、商品列表无限下拉
offset 100000 limit 20超级慢
4. OFFSET 分页的痛点案例
-- 慢！扫描100020条，扔掉100000条
select * from user limit 20 offset 100000;
5. 大分页优化方案（替代 OFFSET）
主键游标分页（推荐，又叫 “书签分页”）
利用有序主键，记住上一页最后一条 id，不用大量扫描
-- 上一页最后id=100000
select * from user where id > 100000 limit 20;
优势：索引直接定位，不需要扫描前面数据。
限制：不能跳页（无法直接跳到第 1000 页），适合 APP 下拉加载。
6. 面试一句话总结
OFFSET 作用：跳过指定数量结果行；
原理：先查询 offset+size 条，丢弃 offset 条；
适合：小页码后台分页；
不适合：禁止大数据量深分页，深分页改用主键游标分页。
7.补充拓展（容易混淆）
- LIMIT 0,10 → offset=0，跳过 0 条，取 10 条（第一页）
- OFFSET 只是
逻辑跳过，无法规避全量扫描，索引只能优化找到起始位置，不能消除扫描开销 - ORDER BY 必须配套使用！不加 order by，offset 分页结果不稳定（存储引擎返回顺序随机）
S1. Mysql完整查询语法和执行顺序
-- 查询完整语法--
SELECT DISTINCT [column_list]
FROM [table]
JOIN [table] ON [condition]
WHERE [condition]
GROUP BY [column_name]
HAVING [condition]
ORDER BY [condition][ASC/DESC]
LIMIT [num1,num2]
-- SQL执行顺序--
1.FROM 表
2.JOIN 表
3.ON 条件
4.WHERE 分组前筛选
5.GROUP BY 分组
6.聚合函数 AVG() MAX() MIN()
7.HAVING 分组后筛选
8.SELECT 查询
9.DISTINCT 去重
10.ORDER BY 排序
11.LIMIT 分页
S2. MySql基础
| 命令 | 说明 |
|---|---|
| SELECT VERSION() | 返回MYSQL数据库的版本号 |
| SELECT CURRENT_USER() | 返回当前数据库用户 |
| SELECT DATABASE() | 返回当前使用的数据库名 |
| SHOW INDEX FROM [表名] | 查看表中所有索引 |
| CREATE INDEX [索引名] ON [表名(列名)] | 创建索引 |
| DROP INDEX [索引名] ON [表名] | 删除表中某个索引 |
1.mysql -u [用户名] -p [密码] ：登录mysql
2.quit :退出数据库
3.show tables : 展示当前数据库的所有表
4.create database [库名]: 创建一个数据库
5.drop database [库名]: 删除一个数据库
6.alter database [库名] character [编码格式]: 修改数据库的编码格式
7.use [库名] : 使用某个库
8.desc [表名] : 查看表结构
9.CREATE TABLE [表名] ([字段名] [值类型] [约束], [字段名] [值类型] [约束]...)
10.DROP TABLE [表名] : 删除表
11.DELETE FROM [表名] : 逐条删除所有记录
12.TRUNCATE FROM [表名] : 删除整张表，然后再构件一个与原来结构相同的表
13.INSERT INTO [表名] ([字段1名]，[字段2名].. ) VALUES ([字段1的值],[字段2的值])
14.UPDATE [表名] SET [字段A]=[对应值] ,[字段B]=[对应值],[字段C]=[对应值] WHERE [条件]
15.DELETE FROM [表名] WHERE [条件]
16.SELECT * from tb_emp WHERE ISNULL(`name`):查找name值等于null的记录,不能使用name=null.
17.COUNT(*): 在统计结果数量的时候,不会忽略null值的记录,COUNT(列名)会忽略为null的记录
18.ALTER TABLE [表名] ADD [新列名] [类型] [约束] : 往表中添加新的字段
19.ALTER TABLE [表名] DROP [列名] : 删除表中的字段
20.ALTER TABLE [表名] CHANGE [旧列名] [新列名] [旧列名类型] : 修改字段的名称
21.WHERE 是分组前的条件筛选 GROUP BY之前
22.HAVING 是分组后的条件筛选 GROUP BY之后
23.DISTINCT 关键字: 实现去重。
如: SELECT DISTINCT ID,NAME FROM user 表示ID拼接NAME之后的结果出现重复就会被过滤掉。
24.PRIMARY KEY(user_id,class_id) ：联合主键，userId拼接classId之后的结果出现重复就会被过滤掉。
S3. 建表语句示例
create table tb_spu
(
id bigint auto_increment comment '商品ID',
title varchar(256) not null default '' comment '标题',
sub_tile varchar(256) not null default '' comment '子标题',
brand_id bigint not null comment '品牌ID',
create_time datetime null comment '创建时间',
update_time datetime null comment '更新时间',
is_saled tinyint(1) not null default 1 comment '是否上架，1上架，0下架',
is_valid tinyint(1) not null default 1 comment '是否有效，1有效，0已删除',
primary key(id)
)comment 'spu表，该表描述的是一个抽象性的商品，比如 iphone8';
-----------------------------------------------------------------------------------------
CREATE TABLE `t_product` (
`pid` int(6) NOT NULL AUTO_INCREMENT,
`pname` varchar(20) DEFAULT NULL,
`p_tid` int(4) DEFAULT NULL,
`punit` varchar(8) DEFAULT NULL,
`pnumber` int(6) DEFAULT NULL,
`premark` varchar(500) DEFAULT NULL,
`pdel` int(6) DEFAULT NULL,
PRIMARY KEY (`pid`),
KEY `FK_TID` (`p_tid`) USING BTREE, --为列p_tid建立索引，使用BTREE数据结构
CONSTRAINT `FK_TID` FOREIGN KEY (`p_tid`) REFERENCES `t_type` (`tid`) --设置外键
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
--AUTO_INCREMENT=n命令来重设自增的起始值。
-----------------------------------------------------------------------------------------
CREATE TABLE `hc_project_vote` (
`rcd_id` INT (11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`create_time` datetime COMMENT '创建时间',
`update_time` datetime COMMENT '更新时间',
`sn` VARCHAR (30) DEFAULT '' COMMENT 'sn',
`project_sn` VARCHAR (30) DEFAULT '' COMMENT '项目sn',
`user_sn` VARCHAR (30) DEFAULT '' COMMENT '投票者sn',
`vote_time` datetime COMMENT '投票时间',
`visit_ip` VARCHAR (15) DEFAULT '' COMMENT '投票者ip',
PRIMARY KEY (`rcd_id`),
KEY `idx_hc_vote_project_sn` (`project_sn`) USING BTREE,
KEY `idx_hc_vote_user_sn` (`user_sn`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '项目投票记录表';
S4. mysql5.6 支持两个timestamp列，新建或更新记录时，自动记录时间戳
CREATE TABLE temp
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
S5. case when then else end语法
UPDATE tb_user
SET name=
CASE id
WHEN 1 THEN '小1'
WHEN 2 THEN '小2'
WHEN 3 THEN '小3'
WHEN 4 THEN '小4'
WHEN 5 THEN '小5'
ELSE 'xx'
END
WHERE id in (1,2,3,4,5,6);
--------------------
<update id="updateDeptChildren" parameterType="java.util.List">
update sys_dept set ancestors =
<foreach collection="depts" item="item" index="index"
separator=" " open="case dept_id" close="end">
when #{item.deptId} then #{item.ancestors}
</foreach>
where dept_id in
<foreach collection="depts" item="item" index="index"
separator="," open="(" close=")">
#{item.deptId}
</foreach>
</update>
S6. date_format函数和str_to_date函数
- mysql日期和字符相互转换
date_format(date,'%Y-%m-%d') ::oracle中的to_char()函数
str_to_date(date,'%Y-%m-%d') ::oracle中的to_date()函数
%Y：代表4位的年份
%y：代表2为的年份
%m：代表月, 格式为(01……12)
%c：代表月, 格式为(1……12)
%d：代表月份中的天数,格式为(00……31)
%e：代表月份中的天数, 格式为(0……31)
%H：代表小时,格式为(00……23)
%k：代表 小时,格式为(0……23)
%h： 代表小时,格式为(01……12)
%I： 代表小时,格式为(01……12)
%l ：代表小时,格式为(1……12)
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T：代表 时间,格式为24 小时(hh:mm:ss)
SELECT *
FROM t_02
WHERE DATE_FORMAT(create_time,'%y%m%d%H')
BETWEEN DATE_FORMAT('2020-06-02 00:00:00','%y%m%d%H')
AND DATE_FORMAT('2020-06-04 23:30:30','%y%m%d%H')
---------------------------------------------------------------------------------------
<select id="selectConfigList" parameterType="SysConfig" resultMap="SysConfigResult">
<include refid="selectConfigVo"/>
<where>
<if test="configName != null and configName != ''">
AND config_name like concat('%', #{configName}, '%')
</if>
<if test="configType != null and configType != ''">
AND config_type = #{configType}
</if>
<if test="configKey != null and configKey != ''">
AND config_key like concat('%', #{configKey}, '%')
</if>
<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
and date_format(create_time,'%y%m%d') >=
date_format(#{beginTime},'%y%m%d')
</if>
<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
and date_format(create_time,'%y%m%d') <=
date_format(#{endTime},'%y%m%d')
</if>
</where>
</select>
S7. sysdate函数
<update id="updateConfig" parameterType="SysConfig">
update sys_config
<set>
<if test="configName != null and configName != ''">
config_name = #{configName},
</if>
<if test="configKey != null and configKey != ''">
config_key = #{configKey},
</if>
<if test="configValue != null and configValue != ''">
config_value = #{configValue},
</if>
<if test="configType != null and configType != ''">
config_type = #{configType},
</if>
<if test="updateBy != null and updateBy != ''">
update_by = #{updateBy},
</if>
<if test="remark != null">
remark = #{remark},
</if>
update_time = sysdate()
</set>
where config_id = #{configId}
</update>
<insert id="insertDept" parameterType="SysDept">
insert into sys_dept(
<if test="deptId != null and deptId != 0">dept_id,</if>
<if test="parentId != null and parentId != 0">parent_id,</if>
<if test="deptName != null and deptName != ''">dept_name,</if>
<if test="ancestors != null and ancestors != ''">ancestors,</if>
<if test="orderNum != null and orderNum != ''">order_num,</if>
<if test="leader != null and leader != ''">leader,</if>
<if test="phone != null and phone != ''">phone,</if>
<if test="email != null and email != ''">email,</if>
<if test="status != null">status,</if>
<if test="createBy != null and createBy != ''">create_by,</if>
create_time
)values(
<if test="deptId != null and deptId != 0">#{deptId},</if>
<if test="parentId != null and parentId != 0">#{parentId},</if>
<if test="deptName != null and deptName != ''">#{deptName},</if>
<if test="ancestors != null and ancestors != ''">#{ancestors},</if>
<if test="orderNum != null and orderNum != ''">#{orderNum},</if>
<if test="leader != null and leader != ''">#{leader},</if>
<if test="phone != null and phone != ''">#{phone},</if>
<if test="email != null and email != ''">#{email},</if>
<if test="status != null">#{status},</if>
<if test="createBy != null and createBy != ''">#{createBy},</if>
sysdate()
)
</insert>
S8. Distinct 单列去重+列组合去重
单列去重：name重复就会呗过滤
select distinct name
from t_test
列组合去重:name和age组合起来重复的话，就会呗过滤掉
select distinct name,age
from t_test
S9. count 函数
==============COUNT(字段名)================
COUNT(column_name):返回指定列的元素个数，NULL不计入结果
select count(column_name) from table_name
==============COUNT(*)或者count(数字):================
COUNT(*)或者count(数字):返回符合条件的所有记录数量
select count(*)
from user
where name='zs'
============COUNT(DISTINCT 字段名)==========
COUNT(DISTINCT 字段名)：返回该字段不重复的记录数
SELECT COUNT(DISTINCT num1)
FROM t_test01
S10. ALL | ANY | SOME | IN
select s1 from t1 where s1 > ANY(select s1 from t2)
select s1 from t1 where s1 > ALL(select s1 from t2)
只要 表t1中的s1列元素 与t2中s1列中有一个元素不相同就被帅选出来
select s1 from t1 where s1 <> any (select s1 from t2);
select s1 from t1 where s1 <> some (select s1 from t2);语句some是any的别名，用法相同
IN 相当于: =any 等于其中的任何一个
NOT IN 相当于: <>all 不等于其中全部的值
S11. Union | Union ALL
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。
|--------|
Score Id
3.5 1
3.65 2
4 3
3.85 4
4 5
3.65 6
|--------|
select * from scores WHERE Id =3 OR Score = 3.65
上面语句可替换成
SELECT * from scores WHERE Id =3
UNION
SELECT * from scores WHERE Score = 3.65
UNION 用于将不同表中相同列中查询的数据展示出来；（去重）
UNION ALL 用于将不同表中相同列中查询的数据展示出来；（没有去重）
S12. 实现Oracle的nulls first 和 nulls last功能，即null在前和null在后
实现null在前功能，使用isnull函数默认不排序，排序需要追加
select num1,num2
from t_test01
order by not isnull(num1),num1 desc
实现null在后功能，使用isnull函数默认不排序，排序需要追加
select num1,num2
from t_test01
order by isnull(num1),num1 desc
S13. MySql的索引
- 索引：是帮助MySql高效获取数据的数据结构，可以加快数据库查询速度，通俗的来说索引就好比书本的目录。
- MySql的默认存储引擎是innodb，索引的结构是B+Tree
SHOW INDEX FROM [TABLE_NAME] : 查看表中所有索引
CREATE INDEX [INDEX_NAME] ON [TABLE_NAME(COLUMN_NAME)] :创建索引
DROP INDEX [INDEX_NAME] ON [TABLE_NAME] : 删除表中某个索引
- 联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引.
create table test(
a int,
b int,
c int,
KEY index_demo(a,b,c));
1.多列索引在and查询中应用
select * from test where a=? and b=? and c=?；查询效率最高，索引全覆盖。
select * from test where a=? and b=?；索引覆盖a和b。
select * from test where b=? and a=?；经过mysql的查询分析器的优化，索引覆盖a和b。
select * from test where a=?；索引覆盖a。
select * from test where b=? and c=?；没有a列，不走索引，索引失效。
select * from test where c=?；没有a列，不走索引，索引失效。
2.多列索引在范围查询中应用
select * from test where a=? and b between ? and ? and c=?；索引覆盖a和b，因b列是范围查询，因此c列不能走索引。
select * from test where a between ? and ? and b=?；a列走索引，因a列是范围查询，因此b列是无法使用索引。
select * from test where a between ? and ? and b between ? and ? and c=?；a列走索引，因a列是范围查询，b列是范围查询也不能使用索引。
3.多列索引在排序中应用
select * from test where a=? and b=? order by c；a、b、c三列全覆盖索引，查询效率最高。
select * from test where a=? and b between ? and ? order by c；a、b列使用索引查找，因b列是范围查询，因此c列不能使用索引，会出现file sort。
4.总结
联合索引的使用在写where条件的顺序无关，mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力，最好和索引的从左到右的顺序一致。使用等值查询，多列同时查询，索引会一直传递并生效。因此等值查询效率最好。索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。排序也能使用索引，合理使用索引排序，避免出现file sort。
========================================================================================
在使用联合索引的时候，我们遵守一个最左原则
即INDEX LianHeIndex (name,age)支持 name | name age组合查询,而不支持age查询
换句话说，在执行
select * from stu where name=? 索引有效
select * from stu where name=? and age=? 索引有效
select * from stu where age=? 索引无效
如果我们是在name和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在name、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(name, age)的复合索引，那么其实相当于创建了(name)、(name,age)两个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
【注意事项】
只要列中包含有NULL值都将不会被包含在索引中
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的，所以我们在数据库设计时尽可能不要让字段的默认值为NULL。
S14. exists关键字
EXISTS(包括 NOT EXISTS )子句的返回值是一个布尔值。
EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。
其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
通俗的理解：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，
这一行行可作为外查询的结果行，否则不能作为结果.
【将customers查出的每一行带入内查询作为检验，返回true就显示】
SELECT *
FROM day1106.customers a
WHERE EXISTS(SELECT *
FROM day1106.forcustomers b
WHERE b.name = a.name )
S15. 常用函数汇总
============================数字函数===========================
ABS(-1):求-1的绝对值，返回1 SELECT ABS(-1);
AVG(column):求平均值
CEIL(1.5):返回大于或等于1.5的最小整数--> 2
FLOOR(1.5):返回小于或等于1.5的最大整数-->1
EXP(3):返回e的三次方 e在数学中代表自然对数的底数 e=2.71828..
GREATEST(ex1,ex2,ex3):返回列表中的最大值
LEAST(ex1,ex2,ex3):返回列表中的最小值
LN(2)：返回2的自然对数
LOG(x):返回自然对数(以 e 为底的对数)
MAX(expression):返回字段中的最大值
MIN(expression)返回字段 expression 中的最大值
POW(x,y)：返回x的y次方
RAND():返回0到1的随机数
ROUND(x)：返回离 x 最近的整数 SELECT ROUND(1.23456);返回1
ROUND(x,y):指定x保留几位小数 SELECT ROUND(1.23456，2)返回1.23
SIGN(x):返回数字x的符号 用-1代表负数，0代表0，1代表正数
SQRT(x)返回x的平方根 SELECT SQRT(25)：返回5
SUM(expression)返回指定字段的总和 SELECT SUM(role_id) from sys_role
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值
SELECT TRUNCATE(1.23456,3);返回1.234
============================字符串函数===========================
SELECT ASCII('AB'); 返回第一个字符的ASCII码值：65
SELECT LENGTH('abcd'):返回字符串的长度
CONCAT(s1,s2…sn) ：字符串拼接
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置：
--SELECT FIND_IN_SET("c", "a,b,c,d,e");返回3
FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”,
将 x 保留到小数点后 n 位，最后一位四舍五入
--SELECT FORMAT(250500.5634, 2):返回250,500.56
INSERT(S1,X,LEN,S2)：字符串s2替换s1中从x位置开始LEN个字符
--SELECT INSERT("google.com", 1, 6, "runnob");返回 runnob.com
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置
--SELECT LOCATE('st','myteststring');返回5
LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母
UCASE(S)/UPPER(S)将字符串 s 的所有字母变成大写字母
TRIM(s)去掉字符串 s 开始和结尾处的空格
LTRIM(s)去掉字符串 s 开始处的空格
RTRIM(s)去掉字符串 s 结尾处的空格
SUBSTR/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的 子字符串
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置
REPEAT(s,n)将字符串 s 重复 n 次
REVERSE(s)将字符串s的顺序反过来
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，
如果 s1>s2 返回 1，如果 s1<s2 返回 -1
--SELECT STRCMP('SSS','SSZ');返回-1
============================日期函数===========================
CURDATE()/CURRENT_DATE()返回当前日期 返回2019-02-19
CURRENT_TIME()/CURTIME()返回当前时间 21:15:12
ADDDATE(d,n)计算起始日期 d 加上 n 天的日期
SELECT ADDDATE('2018-01-11',INTERVAL 10 DAY) 返回2018-01-21
ADDTIME(t,n)时间 t 加上 n 秒的时间
--SELECT ADDTIME('2011-11-11 11:11:11', 5); 返回2011-11-11 11:11:16
CURRENT_TIMESTAMP 返回当前日期和时间 2020-01-12 21:19:07
DATE()从日期或日期时间表达式中提取日期值
SELECT DATE('2011-11-11 11:11:11'); 返回2011-11-11
DAY(d)返回日期值 d 的日期部分
--SELECT DAY('2011-11-13 11:11:11');返回13
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
--SELECT DATEDIFF('2001-01-01','2001-02-01');返回-31
DATE_FORMAT按表达式 f的要求显示日期 d
--SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
返回2011-11-11 11:11:11 AM
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
--SELECT DAYNAME('2011-11-11 11:11:11');返回Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天
--SELECT DAYOFMONTH('2011-11-11 11:11:11');返回11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
--SELECT DAYOFWEEK('2020-1-12 11:11:11');返回1
DAYOFYEAR(d)计算日期 d 是本年的第几天
--SELECT DAYOFYEAR('2020-1-12 11:11:11');返回12
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值
--SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16'); 返回15
UNIX_TIMESTAMP()得到时间戳
--SELECT UNIX_TIMESTAMP('2019-2-19'); 返回1550505600
FROM_UNIXTIME()时间戳转日期
--SELECT FROM_UNIXTIME(1550505600);返回2019-02-19 00:00:00
--SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');返回2019-02-19
============================高级函数===========================
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
--SELECT IF(1>0,'yes','no');返回yes
CONV(x,f1,f2)返回f1进制的x变成 f2 进制后的数
--SELECT CONV(88,10,2):返回1011000
SELECT VERSION() 返回MYSQL数据库的版本号
SELECT DATABASE()返回当前使用的数据库名
SELECT CURRENT_USER()返回当前数据库用户 ：root@localhost
S16. SELECT FOR UPDATE 悲观锁
- 悲观锁是对数据被修改持悲观态度（认为数据在被修改的时候一定会存在并发问题），因此在整个数据处理过程中将数据锁定。悲观锁，每次去操作数据的时候，都认为别的事务会修改数据，所以在操作之前会上锁,主键一定是唯一性索引，唯一性索引并不一定就是主键。
- 使用多窗口模拟数据库并发问题。
1.for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。
2.for update的意义:透过的事务机制来确保读取及提交的数据都是正确的。
====================================================================
注1: FOR UPDATE 仅适用于InnoDB，且必须在事务区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL 的Command Mode ，开二个视窗来做测试。
注3: InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。
注4: 如果for update没有命中索引会锁表
#1.明确指定主键，并且查出有数据，使用行锁，别的事务无法修改数据，只能读数据
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id =1 FOR UPDATE;
COMMIT;
#2.明确指定主键，但查无数据，不使用锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id =156555654 FOR UPDATE;
COMMIT;
#3.主键不明确，满足条件的记录都被行锁锁主，不是使用表锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_id>10 FOR UPDATE;
COMMIT;
#4.没有指定主键，有无数据，都使用表锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE obj_name ='list------test' FOR UPDATE;
COMMIT;
#5.明确指定索引，并且有数据，使用行锁，无数据就无锁
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE objdef_id = 3 FOR UPDATE;
COMMIT;
#6.指定的索引是一个范围，使用多个行锁，锁定满足条件的记录
SET autocommit=0;
BEGIN;
SELECT * FROM document WHERE objdef_id >2 FOR UPDATE;
COMMIT;
===========================================================================
1.当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束吗？
答：会的。除非第一个事务commit或者rollback或者断开连接，第二个事务会立马拿到锁进行后面操作。
2.如果没查到记录会锁表吗？
答：数据不存在，是不会锁表的
S17. Tinyint(1)数据格式和实体类中的Boolean类型自动转换
-
mysql中没有布尔型 boolean=tinyint(1)
-
mysql 中 tinyint(1) ===> 0代表false，1代表true
=================前提====================
查询或插入的这个数据库字段的数据类型为Tinyint(1)
=================新增记录=================
往数据库表新增记录时，使用true或false，数据库会自动把true转换为1，false转换为0
=================查询记录=================
查询数数据库时，数据库会把0自动转化为false，1自动转换为true存储到实体类中的Boolean类型属性当中
S18. SUM()
- SUM(表达式)： 满足表达式就 + 1。
- SUM(列名) :对该列所有值进行相加，NULL会被忽略。
- SUM( DISTINCT 列名)：对该列的所有不重复值进行相加，NULL会被忽略。
- SUM(表达式)的应用示例
name sub score
-----------------
张三 数学 90
张三 语文 50
张三 地理 40
李四 语文 55
李四 政治 60
王五 语文 55
-----------------
1.查询出2门及2门以上不及格者的平均成绩
SELECT
s.`name`,
AVG(s.score)
FROM
students s
GROUP BY
s.`name`
HAVING
SUM(s.score < 60) >= 2 ; #SUM(表达式) 满足表达式就 + 1
S19. GROUP_CONCAT
- GROUP_CONCAT(列名)：分组之后，对组内所有该字段值进行拼接。
- GROUP_CONCAT(表达式)：分组之后，把组内的该字段的每个值都代入表达式得到的结果再拼接起来。
id date rst
-------------------
1 2011-1-1 胜
2 2011-1-1 胜
3 2011-1-1 负
4 2011-1-1 负
5 2011-1-2 胜
6 2011-1-2 负
7 2011-1-2 负
8 2011-1-3 胜
9 2011-1-3 胜
10 2011-1-3 胜
11 2011-1-3 胜
12 2011-1-4 负
13 2011-1-4 负
14 2011-1-4 负
15 2011-1-4 负
SELECT
GROUP_CONCAT(c.id)
FROM
match_rec c
GROUP BY
c.date;
1,2,3,4
5,6,7
8,9,10,11
12,13,14,15
SELECT
GROUP_CONCAT(c.id >10)
FROM
match_rec c
GROUP BY
c.date;
0,0,0,0
0,0,0
0,0,0,1
1,1,1,1
Sql优化
1.完整SQL语法和SQL执行顺序
- Mysql查询完整语法
SELECT DISTINCT [column_list]
FROM [table_list]
JOIN(INNER/LEFT/RIGHT) [table] ON [condition]
WHERE [condition]
GROUP BY [column_name]
HAVING [condition]
ORDER BY [column_name] [ASC/DESC]
LIMIT [num1,num2]
- Mysql查询语句执行顺序
1. FROM 对多个表进行求笛卡儿积形成1个虚表。
2. JOIN ON 把符合ON条件的连接表数据连接到虚表。
3. WHERE 分组前筛选,对整个虚表进行筛选。
4. GROUP BY 分组
5. 聚合函数 AVG/MAX/MIN
6. HAVING 分组后筛选
7. SELECT 查询
8. DISTINCT 去重
9. ORDER BY 排序
10. LIMIT 分页
2.避免不走索引的场景
- 尽量避免在字段开头模糊查询，会导致数据库放弃索引进行全表扫描
- 某些列的值可以采用固定开头，例如对象编码：OC-20201224 000001 编码规则
不使用1 而使用固定位数的 000001 是为了模糊查询给定精确编码如 OC-20201224 1
却查询到了 OC-20201224 11 或者 OC-20201224 111.
已知user表的username是有索引的
SELECT * FROM user WHERE username LIKE '%TEST%';
1.采用后模糊可以使用到索引
SELECT * FROM user WHERE username LIKE 'TEST%';
- 尽量避免使用in和not in，会导致数据库放弃索引进行全表扫描
SELECT * FROM user WHERE id IN (2,3,4);
1.如果是连续数值，可以用between代替
SELECT * FROM user WHERE id BETWEEN 2 AND 4;
2.如果是子查询，可以用exists关键字代替
-- 不走索引
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
-- 走索引
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
- 使用union / union all代替or,使用or会导致数据库放弃索引进行全表扫描
- union 会对数据进行去重处理,因此union all效率是高于union 的,能保证数据不重复尽量使用union all
--不走索引
SELECT * FROM user WHERE username = 'zs' or username = 'ls';
--走索引
SELECT * FROM user WHERE username = 'zs';
UNION ALL
SELECT * FROM user WHERE username = 'ls';
- 尽量避免进行NULL值判断,会导致数据库放弃索引进行全表扫描
SELECT * FROM user WHERE username IS NULL;
1.可以给字段添加默认值 '',这样就可以使用到索引了
SELECT * FROM user WHERE username = '';
- 大数据量的时候, 避免使用where 1=1的条件,一般为了方便动态拼装where条件,一旦只有这一个条件成立会导致全表扫描
- 优化: 进行SQL拼接的时候,有条件就进行查询,无条件就不查询
SELECT * FROM user WHERE 1=1;
- where条件仅包含复合索引的非最左侧列,应该遵循Mysql复合索引的最左匹配原则
已知user表存在复合索引: id_name_age
SELECT * FROM user WHERE name = 'zs' AND age = 12;
- 只有order by 字段出现在where条件中时, 才会利用该字段的索引而避免排序,更准确的说, order by 中的字段在执行计划中利用了索引时, 不用排序操作
SELECT * FROM user WHERE id < 5000 ORDER BY id DESC;
- 尽量避免在where条件中等号的左侧进行表达式或函数操作，会导致数据库引擎放弃索引进行全表查询
SELECT * FROM t2 WHERE score/10 = 9
优化方式：
SELECT * FROM t2 WHERE score = 10*9
3.其它优化
- 1.使用Join替代返回字段中的子查询
--查询很慢
SELECT a.id,(SELECT b.typeName
FROM tb_type b WHERE b.id = a.typeId) aType
FROM tb_a a
--优化手段
SELECT a.id,b.typeName
FROM tb_a a
JOIN tb_type b ON b.id = a.typeId
-
2.表中建立索引，优先考虑where / group by / on 使用的字段
-
3.避免出现select *, 查询返回业务需要的字段,避免不必要内存消耗
SELECT * FROM user;
替换成
SELECT username,age FROM user;
- 4.调整Where后面条件的连接顺序，应将过滤数据多的条件往前放，最快速度缩小结果集
MySQL采用从左往右顺序解析where子句
- 5. 遵循小表驱动大表原则（Mysql），让小表成为驱动表
- 当使用left join时，左表是驱动表，右表是被驱动表
- 当使用right join时，右表时驱动表，左表是被驱动表
- 当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
4.sql优化神器 explain
- explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息，对日常生产过程中调优十分有用。
### 1. id 执行编号：
- id 相同：从上到下顺序执行
- id 越大，越优先执行
- id 存在 NULL：最后执行（衍生表、union 结果）
### 2. select_type 查询类型
- **SIMPLE**：简单查询，无 union、无子查询
- **PRIMARY**：外层主查询
- **SUBQUERY**：WHERE 里的子查询
- **DERIVED**：FROM 里的子查询（衍生临时表）
- **UNION**：union 第二个及之后 sql
- **UNION RESULT**：union 合并结果
### 3. table
正在访问哪张表，`<derivedN>`临时表、`<union>`合并集
### 4. type【**最重要！性能等级从优到差**】
`system > const > eq_ref > ref > range > index > ALL`
1. **system**：系统表，极少出现
2. **const**：主键 / 唯一索引等值匹配，最多 1 条（最优）
3. **eq_ref**：联表，主键 / 唯一索引匹配，一对一
4. **ref**：普通索引等值查询，匹配多行
5. **range**：索引范围查询 `> < between in like 前缀匹配`
6. **index**：扫描**整个索引树**（比全表扫描略好）
7. **ALL**：**全表扫描，最坏情况，必须优化**
>
> 优化目标：至少达到 range，尽量 ref；杜绝 ALL
### 5. key
实际最终使用的索引；NULL 代表没走索引
### 6. key_len
用到索引字段最大长度，判断**联合索引使用多少列**
>
> 提示：不包含 null 的字段占用更少，可判断索引是否充分利用
### 7. ref
索引匹配使用到的常量 / 列
### 8. rows
预估需要扫描的数据行数，越小越好
### 9. Extra【高频优化信息】
重点关键字：
- **Using index**：**覆盖索引！无需回表，性能优秀**
- **Using where**：存储引擎返回数据后再过滤
- **Using temporary**：创建临时表（group by/union，需要优化）
- **Using filesort**：**文件排序！没用到索引排序，严重消耗性能**
- **Using join buffer**：联表没用到索引，使用连接缓冲区
- **Impossible where**：where 条件永远不成立，无结果
## 三、高频面试问答
1. **覆盖索引（Using index）**
查询列全部包含在索引内，不需要回表查询主键数据。
2. Using filesort 一定磁盘排序？
不一定，数据量小内存排序；大数据落磁盘，**都代表无法利用索引排序，需要优化**
3. index 和 ALL 区别
index：遍历索引树；ALL 遍历整行数据。
5.数据结构的各种树
-
二叉树： 每个节点最多只能有两颗子树，且有左右次序之分。
-
二叉搜索树： 是一种特殊的二叉树。对节点的值进行了有序的排列，加速了数据的定位。
- 如果当前一个节点的左子树不为空，那么左子树上所有节点的值都小于当前节点的值。
- 如果当前一个节点的右子树不为空，那么右子树上所有节点的值都大于当前节点的值。
-
二叉平衡树AVL： 为了解决二叉搜索树删除节点后退化成线性链表的问题而出现的数据结构。
- 在二叉搜索树的基础上，要求每个节点的左子树和右子树的高度差最多为1。
- 插入或这删除节点的时候，会发生左旋和右旋操作，使得这颗树的左右再次保持一定的平衡。
-
B树： 为了解决AVL树随着数据量的增大，树的高度不断增加，查询效率降低而出现的数据结构。
- 每个节点都存储了key和data。key存储的是一条的记录的键，data存储的一条记录除key之外的内容。
* B树允许一个节点包含多个key，随着数据量增大，它能降低树的总高度，提高查询效率。
- 每个节点都存储了key和data。key存储的是一条的记录的键，data存储的一条记录除key之外的内容。
-
B+树： 为了解决B树范围查询的效率低下问题（需要进行层级别的递归遍历）而出现的数据结构。
- B+树的非叶子节点仅具有索引的作用，非叶子节点只存储key，不存储data。
- B+树的所有叶子节点构成一个key有序的双向链表，同时存储data，这样就大大加速了范围查询效率。
- 由于data都在一个线性结构上，因此遍历速度将会大大提升。
6.索引相关
6.1 聚集索引（主键索引）
- 将 数据 和 索引 放在一起，索引结构的叶子节点保存了 行数据
- 聚集索引默认是主键索引，一个表最多只能有一个聚集索引。
6.2 非聚集索引（辅助索引）
- 将 数据 和 索引 分开存储。索引结构的叶子节点只包含索引列值 和 数据行的主键值。
- 主要包含了 单字段索引 和 联合索引。
- 一般查找流程（回表）： 先通过属性索引找到对应的主键值 ==> 再根据主键值去主键索引中找到需要的数据行
表 ===> user(id,name)
索引 ===> index_demo(name)
index_demo索引叶子节点只包含了name列的值和主键id的值。
如果需要查询全部数据，需要先在辅助索引中拿主键id值 ，再去主键索引中查询数据行。
6.3 聚集索引和非聚集索引的区别
- 聚集索引：可以在索引结构中直接查询到需要返回的任何数据。
- 非聚集索引：可以在索引结构中查询到记录对应的主键值，再根据主键值去主键索引中查找需要的数据。
6.4 覆盖索引
- 需要查询的字段值都可以在辅助索引中拿到。
- sql查询只要在辅助索引当中就可以查询到需要的数据，而不需要在辅助索引中拿到主键值，再去主键索引里去查询数据行。
--创建联合索引
create index index_bu on user(age, userName)
-- age字段用到联合索引index_bu
-- 查询需要返回的userName字段刚好在联合索引index_bu中
-- 这样就可以直接在辅助索引当中查询到数据并返回
select userName from user where age = 10
6.5 索引的劣势
- 当给一个字段建立索引时，字段中的数据就会被复制一份出来，用于生成索引结构。
- 因此，给表添加索引，会增加表的体积，占用磁盘空间。
6.6 索引的适用场景
- 主键自动建立唯一索引和非空约束
- 频繁作为查询条件的字段
- 查询中和其它表进行关联的字段
- 查询中排序的字段
- 查询中分组的字段
6.7 不适合索引的场景
- 经常增删改的表
- 频繁更新的字段，每次更新不仅会更新记录，还要更新索引，加重了IO负担
7.JDBC
7.1 JDBC使用占位符的好处
- 1.可对SQL进行预编译（编译后SQL结构固定），防止SQL注入
- 2.绑定变量，这样可以减少SQL的硬解析，避免每一次都将进行解析并生成执行计划，从而提高执行效率
- 3.增加SQL语句的可读性
8. 联合索引的分析
#给表tb_item_cat添加复合索引
alter table tb_item_cat add INDEX(parent_id,name,type_id);
#设 parent_id --> 1 ,name --> 2, type_id -- > 3,下面进行排列组合
#复合索引有效 - 1
EXPLAIN SELECT * FROM tb_item_cat a WHERE a.parent_id = 1;
#复合索引有效 - 1 2 3
EXPLAIN SELECT * FROM tb_item_cat a WHERE a.parent_id = 1 and a.`name`='电子书刊' AND a.type_id = 35;
#复合索引有效 - 1 2
EXPLAIN SELECT * FROM tb_item_cat a WHERE a.parent_id = 1 and a.`name`='电子书刊';
#复合索引无效 - 1 3
EXPLAIN SELECT * FROM tb_item_cat a WHERE a.parent_id = 1 AND a.type_id = 35;
#复合索引无效 - 2 3
EXPLAIN SELECT * FROM tb_item_cat a WHERE a.`name`='电子书刊' AND a.type_id = 35;
#结论 复合索引符合最左侧原则
9. SQL执行次数分析
- 通过该命令可以查看当前数据库下的增删改查的使用次数,来采取对应的优化处理. value值就是执行的次数.
show global status like 'com_______';
| variable_name | Value |
|---|---|
| Com_backup | 336 |
| Com_binlog | 0 |
| Com_commit | 1385559 |
| Com_delete | 128504 |
| Com_insert | 39742483 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 316716995 |
| Com_signal | 0 |
| Com_update | 48330286 |
| Com_xa_end | 0 |
10. SQL优化~慢查询日志分析
- 慢查询日志会记录超出自己设置的时间还没有执行完毕的sql.
- 默认情况下，Mysql数据库并不启动慢查询日志，
需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。 - 慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
SELECT VERSION(); 10.4.6-MariaDB-log
--开启慢查询日志:
set global slow_query_log=1;
--设置慢查询的超时时间(以秒为单位):
set global long_query_time=2;
--查询慢查询日志相关信息
show variables like '%slow_query_log%';
| variable_name | Value |
|---|---|
| slow_query_log | ON |
| slow_query_log_file | /apps/logs/mysql/slow3306.log |
- 注意: 使用set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf
MySql实战
P1. SQL ZOO实战
name region area population gdp
--------------------------------------------------------------
Afghanistan South Asia 652225 26000000 67144000000
Albania Europe 28728 3200000 6656000000
Algeria Middle East 2400000 32900000 75012000000
Andorra Europe 468 49000 6656000050
Bangladesh South Asia 143998 152600000 67144000000
UnitedKingdom Europe 242514 59600000 2022824000000
Turkey Middle East 168843 12900000 1234567890
Oman Middle East 1800000 10200000 1345000
Indonesia South Asia 2200000 269536482 13345678
xxx Europe 59680000 1024535
af1 Africa 48000 202282800000
af2 Africa 48800 202289400000
af3 Africa 49000 232282400000
--------------------------------------------------------------
1.【選擇代碼以顯示在每個區域人口最小的國家的國家名稱，區域和人口】
select region,name,population
from bbc x
where x.population<=
All(select y.population
from bbc y
where x.region = y.region)
------------------------------------------------
SELECT * FROM bbc x
where x.population =
(SELECT MIN(y.population) FROM bbc y WHERE x.region = y.region);
2.【選擇代碼以顯示國家名稱，該國所在的地區每國人口都超過50000】
select name,region,population
from bbc x
where 50000 <
All(select population
from bbc y
where x.region = y.region)
-----------------------------------------------
SELECT * FROM bbc x WHERE NOT EXISTS(
SELECT 1 FROM bbc y WHERE x.region = y.region AND y.population < 50000
)
ORDER BY x.region DESC,x.population DESC
3.【選擇代碼以顯示國家名稱，該國家人口少於它周圍的任意國家（同洲）的人口三分之一】
SELECT * FROM bbc x
where x.population < ALL(SELECT y.population/3 FROM bbc y
WHERE y.region = x.region
AND x.name <> y.name);
-------------------------------------------------
SELECT * FROM bbc x
where not EXISTS(SELECT 1 FROM bbc y
where x.region = y.region
and x.name <> y.name
and y.population/3 <= x.population )
4.【查询和英国一个洲，且人口数大于英国的国家名称】
SELECT * FROM bbc
WHERE population >
(SELECT population
FROM bbc
WHERE name='United Kingdom')
AND region =
(SELECT region
FROM bbc
WHERE name = 'United Kingdom')
5.【顯示國家名稱，該國有比非洲任何國家有更大的國內生產總值GDP】
SELECT * FROM bbc x
WHERE x.gdp > (SELECT MAX(gdp) FROM bbc x where x.region = 'Africa')
AND x.region <> 'Africa'
----------------------------------------------
yr subject winner
----------------------------------------------
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Madawar
-----------------------------------------------
6.【哪幾年頒發了物理獎，但沒有頒發化學獎?】
select DISTINCT yr from nobel
where subject ='physics'
and yr not in (
select yr from nobel where subject='Chemistry'
)
-------------------------------------------------
select DISTINCT a.yr from nobel a
where not EXISTS (SELECT 1 FROM nobel b WHERE b.yr = a.yr and b.subject='Chemistry' )
7.【首次頒發的經濟獎 (Economics)的得獎者是誰?】
select winner from nobel where subject='Economics'
and yr =
(select yr from nobel
where subject = 'Economics'
order by yr asc
limit 0,1)
8.【日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱】
select winner from nobel
where
yr =(select yr
from nobel
where winner = 'Toshihide Maskawa'
and subject = 'physics')
and winner <> 'Toshihide Maskawa'
and subject = 'physics'
9.【紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份】
select winner,yr from nobel
where subject='Literature' and yr in
(SELECT yr FROM nobel
where winner = 'International Committee of the Red Cross')
==========================================================================
[部门表]
dep_id dep_name
------------------
1 JAVA
2 C
3 PHP
4 H5
5 GO
------------------
[员工表]
emp_id name age salary dep_id
-----------------------------
1 zs 20 4000 1
2 ls 31 5000 2
3 ww 32 6000 3
4 zl 27 7000 3
5 xq 40 10000 2
6 wb 38 3500 4
7 xj 50 6000 5
8 ss 15 2500 5
9 s1 33 8000 1
10 s2 55 12000 4
11 s3 66 5000 1
12 s4 77 5500 2
13 dsb 256 6600 10
-- 1.查询平均工资大于6000的部门
SELECT d.dep_name,AVG(salary)
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY d.dep_name
HAVING AVG(salary)>2000;
-- 2.查询部门平均工资低于6500的部门都有几个人
SELECT d.dep_name,avg(salary), COUNT(*) FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY d.dep_name
HAVING avg(salary)<=6500;
-- 3.查询Java部门中年龄超过20的人
SELECT name, age,dep_name
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE d.dep_name ='JAVA' AND age>20
-- 4.查询java部门的平均工资
SELECT dep_name, avg(salary)
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
GROUP BY dep_name
HAVING dep_name='JAVA';
-- 5.查询java部门中年龄最小的人
SELECT dep_name,name,age
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE dep_name='JAVA'
ORDER BY age ASC
LIMIT 0,1;
-- 6.查询java部门都有哪些人
SELECT dep_name,NAME
FROM tb_emp e
INNER JOIN tb_dep d ON d.dep_id=e.dep_id
WHERE dep_name='JAVA';
==========================================================================
id name salary dep_id
---------------------------
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
5 Janet 69000 1
6 Randy 85000 1
7 Will 7000 1
1.查询第二高的薪水,查不到返回null
select ifnull(
(select distinct Salary
from Employee
order by Salary desc
limit 1,1),null
) as SecondHighestSalary
[爱好表，中间表，用户表]
-- 5.查询爱好是足球的有哪些人
SELECT u.name ,l.love_type
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
WHERE love_type='足球'
-- 6.查询爱好是足球和篮球的有哪些人
SELECT u.name ,l.love_type,COUNT(*)
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
WHERE love_type='足球' OR love_type='篮球'
GROUP BY `name`
HAVING COUNT(*)=2
-- 7.查询哪种球类爱好喜欢的人最多
SELECT COUNT(*),love_type
FROM tb_user u
INNER JOIN tb_love_user lu ON lu.us_id=u.id
INNER JOIN tb_love l ON l.id=lu.lo_id
GROUP BY love_type
ORDER BY COUNT(*) DESC
LIMIT 0,1
//=====ORACLE=====
SELECT
m.dated AS 日期,
sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS 胜 ,
sum(case m.rst when '负' THEN 1 ELSE 0 END) AS 负
FROM
match_rec m
GROUP BY
m.dated;
=========或者=========
SELECT DISTINCT
m.dated AS 日期,
sum(case m.rst when '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS 胜 ,
sum(case m.rst when '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS 负
FROM
match_rec m;
=========或者=========
SELECT DISTINCT
m.dated AS 日期,
sum(case when m.rst like '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS 胜 ,
sum(case when m.rst like '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS 负
FROM
match_rec m;
//=====MYSQL======
SELECT
m.date AS '日期',
SUM(m.rst = '胜') AS '胜',
SUM(m.rst = '负') AS '负'
FROM
match_rec m
GROUP BY
m.date
P2. LeetCode
- 262题：行程和用户
Trips 表中存所有出租车的行程信息。
每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，
Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
==========================题解SQL==========================
SELECT t.Request_at 'Day' ,
ROUND(SUM(Status like 'cancelled%')/COUNT(1),2) 'Cancellation Rate'
FROM Trips t,
Users u1,
Users u2
WHERE t.Client_Id = u1.Users_Id
AND t.Driver_Id = u2.Users_Id
AND u1.Banned <>'Yes'
AND u2.Banned <>'Yes'
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at
- 626题 ：换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。
SELECT t.num as 'id',
t.student
FROM
(SELECT IF(id%2<>0,IF(id<>(SELECT max(id) FROM seat),id+1,id),id-1) as num,student
FROM seat
order by num asc) t
- 185 题 ：部门工资前三高的所有员工
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，
Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。
销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
===思路1：相同部门的员工（包含自己）比自己高的工资去重之后不会大于三个====
SELECT
e. NAME EmployeeName,
e.Salary,
d. NAME DepartMentName
FROM
Employee e
INNER JOIN department d ON e.DepartMentId = d.Id
WHERE 3 >= (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.DepartMentId = e.DepartMentId
AND e2.Salary >= e.Salary
)
ORDER BY e.DepartMentId ASC,e.Salary DESC
===思路2 将部门工资降序去重后，找倒数第三个，找不到就找倒数第二个，找不到就找倒数第一个
SELECT d.name Department,
e.name Employee,
e.Salary Salary
FROM Employee e
INNER JOIN Department d on d.Id = e.DepartmentId
WHERE Salary >= (
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 2,1),
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 1,1),
(select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 0,1)
)
)
)
ORDER BY e.DepartMentId,e.Salary DESC
- 体育馆的人流量
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列的结果表。
查询结果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
====思路：a<b<c a<c<b b<a<c c<a<b b<c<a c<b<a
SELECT DISTINCT
a.*
FROM
Stadium a,
Stadium b,
Stadium c
WHERE a.people>=100
AND b.people>=100
AND c.people>=100
AND ((a.id = b.id-1 AND b.id = c.id -1) OR
(a.id = c.id-1 AND c.id = b.id -1) OR
(b.id = a.id-1 AND a.id = c.id -1) OR
(c.id = a.id-1 AND a.id = b.id -1) OR
(b.id = c.id-1 AND c.id = a.id -1) OR
(c.id = b.id-1 AND b.id = a.id -1)
)
ORDER BY id ASC;
1. 查询第N高的的薪水
set n = N-1;
select distinct Salary
from Employee
order by Salary desc
limit n,1
==========================================================================
2.给Score进行排名，给出Rank值，分数相同Rank相同
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
排名后结果：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
过程分析：
--------------------------------------
最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。
第一部分不难写：
select a.Score as Score
from Scores a
order by a.Score DESC
--------------------------------------
比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？
我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。
先提取集合H：
select b.Score from Scores b where b.Score >= X;
我们要的是集合H去重之后的元素个数，因此升级为：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
--------------------------------------
而从结果的角度来看，第二部分的Rank是对应第一部分的分数来的，所以这里的X就是上面的a.Score，把两部分结合在一起为：
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
==========================================================================
3.编写一个 SQL 查询，查找所有至少连续出现三次的数字
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
预期结果为：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
=======================
Id Num Id Num Id Num
1 1 2 1 3 1
=======================
然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
==========================================================================
4.查询收入超过他们经理的员工的姓名
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
select Name as Employee
from Employee x
where ManagerId is not null
and Salary >
(select Salary
from Employee y
where x.ManagerId = y.id )
-----------------------------------------------------------
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
-----------------------------------------------------------
SELECT
a.NAME AS Employee
FROM Employee AS a
JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
==========================================================================
5.查询每个部门获得前三高工资的所有员工
Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
------------------------------------------------------------
select d.name Department,e.name Employee,e.Salary Salary
from Employee e
inner join Department d on d.Id = e.DepartmentId
where Salary >= (
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 2,1),
ifNull((select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 1,1),
(select distinct em.Salary from Employee em
where em.DepartmentId = e.DepartmentId
order by em.Salary desc
limit 0,1))
)
)
ORDER BY Department,Salary desc;
------------------------------------------------------------
核心思想：思路转变-->将同组工资进行去重，那么大于第三高工资的只有2个，大于第二高的只有1个，大于第一高的0个，换句话说，只要大于前面工资的小于3 就是前三高
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
ORDER BY Department,Salary desc;
==========================================================================
6.查询所有从不订购任何东西的客户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格，你的查询应返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
select c.Name as 'Customers'
from Customers c
left join Orders o on o.CustomerId = c.Id
where o.CustomerId is null
-------------------------------------------------------------------------------------
select c.Name as `Customers`
from Customers c
where c.Name not in
(select b.Name from Customers b inner join Orders o on o.CustomerId = b.Id )
==========================================================================
7.查询每个部门工资最高的员工
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SELECT d.name as 'Department',x.name as 'Employee',x.Salary as Salary
FROM Employee x
INNER JOIN Department d ON d.Id = x.DepartmentId
WHERE x.Salary >=
All(SELECT y.Salary FROM Employee y WHERE x.DepartmentId = y.DepartmentId )
==========================================================================
8.查找与之前（昨天）日期相比温度更高的所有日期的Id
给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如，根据上述给定的 Weather 表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
SELECT w1.id as 'Id'
FROM Weather w1
INNER JOIN Weather w2
ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 # 相当于w1的天数-w2的天数等于1天
AND w1.Temperature > w2.Temperature
---------------------------------------------------------------------------
SELECT
w1.id AS 'Id'
FROM
weather w1
JOIN
weather w2 ON DATE_FORMAT(w1.RecordDate,'%y%m%d')-
DATE_FORMAT(w2.RecordDate,'%y%m%d') = 1
AND w1.Temperature > w2.Temperature
==========================================================================
9.删除 Person 表中所有重复的电子邮箱，保留ID最小的那个
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
DELETE FROM Person
WHERE Id NOT in(
SELECT b.Id
FROM
(select Id
from Person x
where Id <= ALL(select Id from Person y where x.Email = y.Email)) AS b
)
##注意：必须把结果集当做1个表 先查一遍才能删除，
##不然mysql报错 You can't specify target table 'Person' for update in FROM clause
扫一扫
3755
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
