天天写SQL,这些神奇的特性你知道吗?
来源 | OSCHINA 社区
作者 | 华为云开发者联盟-龙哥手记
一 SQL 的第一个神奇特性
听我解释
DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (
id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
sno varchar(12) NOT NULL COMMENT '学号',
cno varchar(5) NOT NULL COMMENT '班级号',
cname varchar(20) NOT NULL COMMENT '班级名',
PRIMARY KEY (id)
) COMMENT='学生班级表';
-- ----------------------------
-- Records of tbl_student_class
-- ----------------------------
INSERT INTO tbl_student_class VALUES ('1', '20190607001', '0607', '影视7班');
INSERT INTO tbl_student_class VALUES ('2', '20190607002', '0607', '影视7班');
INSERT INTO tbl_student_class VALUES ('3', '20190608003', '0608', '影视8班');
INSERT INTO tbl_student_class VALUES ('4', '20190608004', '0608', '影视8班');
INSERT INTO tbl_student_class VALUES ('5', '20190609005', '0609', '影视9班');
INSERT INTO tbl_student_class VALUES ('6', '20190609006', '0609', '影视9班');
SELECT cno,cname,count(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno,cname;
SELECT cno,cname,count(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno;
[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_student_class.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
提示信息:SELECT 列表中的第二个表达式(cname)不在 GROUP BY 的子句中,同时它也不是**聚合函数**;这与 sql 模式:ONLY_FULL_GROUP_BY 不相容的哈
1.0 SQL 模式
语法支持类
ONLY_FULL_GROUP_BY
对于 GROUP BY 聚合操作,如果在 SELECT 中的列、HAVING 或者 ORDER BY 子句的列,没有在 GROUP BY 中出现,那么这个 SQL 是不合法的ANSI_QUOTES
启用 ANSI_QUOTES 后,不能用双引号来引用字符串,因为它被解释为识别符。设置它以后,update t set f1="" …,会报 Unknown column ‘’ in field list 这样的语法错误PIPES_AS_CONCAT
把 || 视为字符串的连接操作符而非 或 运算符,这种和 Oracle 数据库是一样的哈,也和字符串的拼接函数 CONCAT () 有点类似NO_TABLE_OPTIONS
使用 SHOW CREATE TABLE 时不会输出 MySQL 特有的语法部分,如 ENGINE,这个在使用 mysqldump 跨 DB 种类迁移的时候需要考虑NO_AUTO_CREATE_USER
字面意思不自动创建用户,在给 MySQL 用户授权时,我们习惯用 GRANT … ON … TO dbuser,顺道一起创建用户。设置该选项后就与 oracle 操作类似,授权之前必须先建立好用户
1.1 数据检查类
NO_ZERO_DATE
NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES
1.2 默认模式
-- 查看 MySQL 版本
SELECT VERSION();
-- 查看 sql_mode
SELECT @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-- 宽松模式下 可以执行
SELECT cno,cname,count(sno),MAX(sno)
FROM tbl_student_class
GROUP BY cno;
二 SQL 的第二个神奇特性
2.1 问题描述下
with A as
(
select distinct(id) as id from Table_A
),
B as
(
select distinct(id) as id from Table_B
),
result as
(
select * from A where id in (select id from B)
)
select count(*) from result
with A as
(
select distinct(id) as id from Table_A
),
B as
(
select distinct(id) as id from Table_B
),
result as
(
select * from A where id not in (select id from B)
)
select count(*) from result
If you use NOT IN (subquery), it compares every returned value and in case of NULL on any side of comparison it stops immediately with non defined result if you use NOT IN (subquery), it compares every returned value and in case of NULL on any side of comparison it stops immediately with non defined result
2.2 去掉 null 值
with A as
(
select distinct(id) as id from Table_A
),
B as
(
select distinct(id) as id from Table_B where id is not null
),
result as
(
select * from A where id not in (select id from B)
)
select count(*) from result
2.3 用 not exists 代替 not in
with A as
(
select distinct(id) as id from Table_A
),
B as
(
select distinct(id) as id from Table_B where id is not null
),
result as
(
select * from A where not exists (select * from B where A.id=B.id)
)
select count(*) from result
三 SQL 的第三个神奇特性
3.1 环境准备
3.2 使用高效的查询
3.3 使用 EXISTS 代替 IN
1、如果连接列(customer_id)上建立了索引,那么查询 tbl_recharge_record 时可以通过索引查询,而不是全表查询
2、使用 EXISTS,一旦查到一行数据满足条件就会终止查询,不用像使用 IN 时一样进行扫描全表(NOT EXISTS 也一样)
3.4 使用连接代替 IN
3.5 避免排序
3.6 使用 EXISTS 来代替 DISTINCT
SQL:SELECT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id = tc.id
SELECTDISTINCT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id = tc.id
3.7 在极值函数中使用索引
3.8 WHERE 子句的条件里可以使用索引
使用索引
减少临时表
1、临时表相当于原表数据的一份备份,会耗费内存资源
2、很多时候(特别是聚合时),临时表没有继承原表的索引结构
灵活使用 HAVING 子句
小结下
四 高斯数据库特性为啥优异
首先,能释放 CPU 多核心的计算资源
然后,是并行查询
主键查询、二级索引查询
主键扫描、索引扫描、范围扫描、索引等值查询,索引逆向查询
并行条件过滤(where/having)、投影计算
并行多表 JOIN(包括 HashJoin、NestLoopJoin、SemiJoin 等)查询
并行聚合函数运算,包括 SUM/AVG/COUNT/BIT_AND/BIT_OR/BIT_XOR 等
并行表达式运算,包括算术运算、逻辑运算、一般函数运算及混合运算等
并行分组 group by、排序 order by、limit/offset、distinct 运算
并行 UNION、子查询、视图查询
并行分区表查询
并行查询支持的数据类型包括:整型、字符型、时间类型、浮点型等等
其他查询
总而言之
END
往期精彩给IDEA换个酷炫的主题,这有点,惊艳啊!Spring Boot 使用 Disruptor 做内部高性能消息队列
Spring Boot 中使用 @Valid 注解 + Exception 全局处理器优雅处理参数验证
ClickHouse 与 Elasticsearch 压测实践
Spring Boot + minio 实现高性能存储服务
关注后端面试那些事,回复【2022面经】
获取最新大厂Java面经