10-SQL语句高级
SQL语句高级
注意:本章适合专业DBA,开发人员，运维(兼职DBA)了解
1、sql_mode介绍
1.什么是sql_mode？
sql_mode是对SQL语法控制和校验的一套规则，用于控制SQL执行的一些特定行为和功能。它定义了MySQL在执行SQL语句时的工作模式和严格性等级，从而影响MySQL数据库的行为和数据处理方式。
2.sql_mode说明
mysql> select @@sql_mode;
|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
解释含义
GROUP BY 是SQL（结构化查询语句）中的一个关键字，用于对查询结果进行分组操作。在使用group by 时通常会结合聚合函数（如`sum`、`count`、`avg`、`max`、`min`、等）一起使用，以便在每个分组中计算出聚合结果。
①ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，mysql5.7以后增加的限制。
②NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
③STRICT_TRANS_TABLES
如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制
④NO_ZERO_IN_DATE(容易理解表达)
不允许日期和月份为零
⑤NO_ZERO_DATE(容易理解表达)
mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
⑥ERROR_FOR_DIVISION_BY_ZERO(容易理解表达)
在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
⑦NO_AUTO_CREATE_USER(容易理解表达)
禁止GRANT创建密码为空的用户
⑧NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
⑨PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似
⑩ANSI_QUOTES
如果启用了 ANSI_QUOTES 模式，MySQL 将遵循 ANSI_SQL 标准，其中单引号 ' 用于字符串字面量'，"而双引号则用于标识标识符"
2、select多子句单表高级实践
1.select多子句高级语法
select <字段1，字段2，...> from <表名> [WHERE 条件]
#读出来grep 10 oldboy.log
[GROUP BY {col_name | expr | position}] 分组,对指定列分组 #uniq -c去重(linux重点)
##grep 10 oldboy.log|sort|uniq -c
[HAVING条件] #分组后条件判断或者过滤
##grep 10 oldboy.log|sort|uniq -c|awk '$1>=1 {print $0}'
[ORDER BY {col_name | expr | position} [ASC | DESC]] #排序ASC升序,DESC降序。
##grep 10 oldboy.log|sort|uniq -c|awk '$1>=1 {print $0}'|sort -rn -k1
[LIMIT {[offset,] row_count | row_count OFFSET offset}] #限制结果集数量。
##grep 10 oldboy.log|sort|uniq -c|awk '$1>=1 {print $0}'|sort -rn -k1|sed -n '2,3p'
类比:
cat oldboy.log|sort|uniq -c|awk '$1>2{print $0}'|sort -rn -k1|head -1
select * from stu group by 列 having 列1>2 order by 列1 desc limit 1
2.select后多子句执行顺序
1.2.1请简述select语句的各个子句的执行顺序？
SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、select计算所有的表达式；
7、使用order by对结果集进行排序。
面试题:select后多子句执行顺序
当SELECT语句被DBMS执行时，其子句会按照固定的先后顺序执行：
（1）FROM
（2）WHERE
（3）GROUP BY
（4）HAVING
（5）SELECT
（6）ORDER BY
基本的工作原理：FROM子句先被执行，通过FROM子句获得一个虚拟表，然后通过WHERE子句从虚拟表中获取满足条件的记录，生成新的虚拟表。将新虚拟表中的记录通过GROUP BY子句分组后得到更新的虚拟表，而后HAVING子句在最新的虚拟表中筛选出满足条件的记录组成另外一个虚拟表中，SELECT子句会将指定的列提取出来组成更新的虚拟表，最后ORDER BY子句对其进行排序得出最终的虚拟表。通常这个最终的虚拟表被称为查询结果集。
原文链接：https://blog.csdn.net/qq_39387475/article/details/78328083
3、常用聚合函数介绍
聚合函数是GROUP BY使用的前提条件
（1）什么是聚合函数？
聚合函数是SQL的基本函数之一，是对一组值执行计算并返回单个值
（2）常用聚合函数
| 聚合函数 | |
|---|---|
| COUNT() | 返回指定组中数据的数量，括号内加列名。 |
| SUM() | 返回指定组中数据之和，只能用于数字列，空值被忽略。 |
| AVG() | 返回指定组中的平均值，空值被忽略。 |
| MAX() | 返回指定数据的最大值。 |
| MIN() | 返回指定数据的最小值。 |
| group_concat() | 返回指定的数据，按逗号分割为一行。A,B,C,D |
（3）练习理解:
# 查询stu表的所有内容
mysql> select * from stu;
+----+---------+-----+--------+--------+
| id | sname | age | gender | telnum |
+----+---------+-----+--------+--------+
| 1 | oldboy | 28 | M | 111 |
| 2 | oldgril | 25 | F | 126 |
| 3 | Jack | 18 | M | 189 |
| 4 | Tim | 35 | F | 183 |
| 5 | oldgril | 0 | N | 0 |
+----+---------+-----+--------+--------+
5 rows in set (0.01 sec)
#返回这个虚拟表中数据的数量。
mysql> select count(*) from stu;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.07 sec)
# 计算age列的和
mysql> select sum(age) from stu;
+----------+
| sum(age) |
+----------+
| 106 |
+----------+
# 求age列的平均值
mysql> select avg(age) from stu;
+----------+
| avg(age) |
+----------+
| 21.2000 |
+----------+
# 求age列的最大值
mysql> select max(age) from stu;
+----------+
| max(age) |
+----------+
| 35 |
+----------+
# 求age列的最小值
mysql> select min(age) from stu;
+----------+
| min(age) |
+----------+
| 0 |
+----------+
# 返回age列的所有数据，以逗号的方式分割
mysql> select group_concat(age) from stu;
+-------------------+
| group_concat(age) |
+-------------------+
| 28,25,18,35,0 |
+-------------------+
4、GROUP BY
（1）group by子句作用
按照指定的条件（列）对数据进行分组。
（2）sql_mode对group by的控制
ONLY_FULL_GROUP_BY是控制select语句中带group by子句的检测，要求select后的选择列,要么是group by的条件，要么是where的条件,要么是通过聚合函数处理，否则语句就会报错。
（3）group by原理
group by原理:
A 省1 1004
B 省1 1001
C 省3 2001
d 省2 1001
e 省2 1001
f 省3 2001
SELECT district,sum(population) FROM city group by district;
1.取出涉及的列.
SELECT district, population FROM city;
省1 1004
省1 1001
省2 1001
省2 1001
省3 2001
省3 2001
2.去重
SELECT district, sum(population) FROM city group by district;
省1 2005
省2 2002
省3 4002
（4）group by语句实践
# 查看city表的表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |主键列
| Name | char(35) | NO | | | |城市名
| CountryCode | char(3) | NO | MUL| | |国家代号
| District | char(20) | NO | | | |省份
| Population | int | NO | | 0 | |人口数量
+-------------+----------+------+-----+---------+----------------+
# 查看国家代号是CHN的前十行
mysql> select * from city where countrycode='chn' limit 10;
+------+--------------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------------+-------------+--------------+------------+
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1899 | Nanking | CHN | Jiangsu | 2870300 |
+------+--------------------+-------------+--------------+------------+
use world;
#1.统计每个国家的总人口
解答分析：
1)所有城市人口加起来sum(Population),select后的列.
select CountryCode,sum(Population)
2)表
from city;
3)条件:每个国家,去重,每个就是分组.
group by CountryCode
答案:
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode ;
#2.统计中国每个省的总人口
解答分析：
1)所有城市人口加起来sum(Population),select后的列.
select District,sum(Population)
2)表
from city
3)中国
统计中国即where条件里代号为中国。
where countrycode='chn'
4) 每个省
group by District
答案:
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |主键列
| Name | char(35) | NO | | | |城市名
| CountryCode | char(3) | NO | MUL| | |国家代号
| District | char(20) | NO | | | |省份
| Population | int | NO | | 0 | |人口数量
+-------------+----------+------+-----+---------+----------------+
#3.统计中国每个省的城市个数
解答分析:
1)城市个数
select district,count(name)
2)表
from city
3)中国
统计中国即where条件里代号为中国。
where countrycode='chn'
4) 每个省
group by District
答案:
SELECT district,COUNT(name) FROM city WHERE countrycode='CHN' GROUP BY district;
#4.统计中国每个省的城市个数和城市名列表
多了个城市名列表
GROUP_CONCAT(NAME) ###湖北城市名列表:武汉,黄冈,随州,孝感,荆州
答案:
SELECT district,COUNT(*),GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
5、having子句介绍与实践
（1）having用途
having子句常用在group by子句过滤后，再进行判断筛选。
（2）having子句实践
#1.统计中国每个省的城市个数，并把超过10个城市个数的输出
SELECT district,COUNT(*),GROUP_CONCAT(NAME) FROM city
WHERE countrycode='CHN' GROUP BY district having count(*)>10;
（3）生产经验：
having子句不走索引，效率低，尽量减少使用，
除非结果集很少，如果结果集大，且非要用，可以从库做。
6、order by子句介绍与实践
（1）order by子句用途
对数据排序，单纯消耗CPU。
（2）order by子句实践
#1.查询中国所有城市,并按人口数排序输出
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population ASC; #<==ASC表示升序，为默认值。DESC表示降序
#2.统计中国每个省的总人口，过滤输出总人口超过1000w,从大到小排序输出
SELECT district, SUM(population) FROM city WHERE countrycode='CHN'
GROUP BY district #<==按省分组。
HAVING SUM(population)>10000000 #<==对group by结果集，总人数在判断。
ORDER BY SUM(population) DESC ; #<==DESC表示降序。从大到小，asc从小到大
7、limit子句介绍与实践
（1）作用与语法
limit用于显示指定的数据行数，一般用于order by排序后，例如：选择top3，倒数前3。
语法:
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
方法1:
LIMIT {[offset,] row_count
#1.limit 2(LIMIT 0,2;)取前两行
#2.LIMIT 2,5,从第3行开始,取5行.
（2）limit实践
SELECT district, SUM(population) FROM city # 查询省份和总人口
WHERE countrycode='CHN' # 只查询国家代号是CHN的
GROUP BY district # 根据省份进行去重，把省份一样的总和起来
HAVING SUM(population)>5000000 # 把人口大于500万的省份过滤出来
ORDER BY SUM(population) DESC # 把人口按从大到小的顺序输出
LIMIT 1,3; # 从第1行开始取值，但是不包含第1行，因为他是从0开始算的，所以是从第二行开始，总共取3行。
8、完整SQL语句执行原理
#查找城市人口大于2003
SELECT district, sum(population) FROM city group by district having sum(population)>2003 order by sum(population)
数据：
A 省1 1004
B 省1 1001
C 省3 2001
d 省2 1001
e 省2 1001
f 省3 2001
1.取出涉及的列.
SELECT district, population FROM city;
省1 1004
省2 1001
省1 1001
省3 2001
省2 1001
省3 2001
2.排序
SELECT district, sum(population) FROM city order by district;
省1 1004
省1 1001
省2 1001
省2 1001
省3 2001
省3 2001
3.去重
SELECT district, sum(population) FROM city group by district;
省1 2005
省2 2002
省3 4002
4.过滤:having sum(population)>2003:
SELECT district, sum(population) FROM city group by district having sum(population)>2003 ;
省1 2005
省3 4002
5.对结果排序:order by sum(population) desc
SELECT district, sum(population) FROM city group by district having sum(population)>2003 order by sum(population) desc;
省3 4002
省1 2005
6.取出一定数量的行,limit 1
SELECT district, sum(population) FROM city group by district having sum(population)>2003 order by sum(population) desc limit 1;
省3 4002
以上内容类似下面命令
##sort oldboy.log|uniq -c|awk '$2>2003 {print $0}'|sort -rn|sed -n '2,3p'
9、union和union all
（1）作用
用来合并两条语句的结果集为一个。union会对合并后的重复的行在去重，而union all不在检查重复。
（2）实践
(SELECT district, SUM(population) FROM city # 查询省份和人口总和
WHERE countrycode='CHN' # 进行过滤只查询国家代号是CHN的
GROUP BY district # 根据省份进行去重
HAVING SUM(population)>5000000 # 大于500万人口
ORDER BY SUM(population) DESC # 根据人数从大到小进行排序
LIMIT 3) # 输出3行
UNION # 连接起来，并去重复行
(SELECT district, SUM(population) FROM city # 查询省份和人口总和
WHERE countrycode='CHN' # 进行过滤只查询国家代号是CHN的
GROUP BY district # 根据省份进行去重
HAVING SUM(population)>5000000 # 大于500万人口
ORDER BY SUM(population) ASC # 根据人数从小到大进行排序
LIMIT 3); # 输出3行
#从大到小三行，从小到大三行
+--------------+-----------------+
| district | SUM(population) |
+--------------+-----------------+
| Liaoning | 15079174 | 辽宁
| Shandong | 12114416 | 山东
| Heilongjiang | 11628057 | 黑龙江
| Anhui | 5141136 | 安徽
| Tianjin | 5286800 | 天津
| Hunan | 5439275 | 湖南
+--------------+-----------------+
6 rows in set (0.00 sec)
2.SQL企业面试题:
1.2 SQL篇
1.2.1 请简述select语句的各个子句的执行顺序？
1.2.2 请列举SQL语句的种类和代表命令？
1.2.3 请简述SQL_MODE的作用？ONLY_FULL_GROUP_BY是干什么用的？
1.2.4 请简述MySQL utf8和utf8mb4区别？
1.2.5 请简述tinyint\int\bigint 如何计算的存储位数？
1.2.6 请简述char(10)和varchar(10)的区别，生产如何选择？并阐述为什么？
1.2.7 请简述datetime和timestamp区别？
1.2.8 请简述你们数据库开发过程，选择数据类型的规范是什么？
浙公网安备 33010602011771号