SQL基础-mysql版本(8.0.30)

 1 查询

1.1 mysql查询大小写

mysql默认查询是不区分大小写的。由mysql的字符校验规则设置决定。

图 navicat软件创建mysql数据库界面

在数据的存储上，mysql提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持。mysql提供了不同级别的设置，包括server级、database级、table级、column级。

1.1.1 mysql 字符集

字符集	说明
utf8	表示一个字符需要使用1~4个字节。字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能。
utf8mb3	阉割过的utf8字符集，只使用1~3个字节表示字符。
utf8mb4	正宗的utf字符集，使用1~4个字节表示字符。

表 mysql部分常用字符集说明

在utf8mb4下，英文占1个字符，中文3个，特殊符号4个。

为了数据库有更好的兼容性，用mb4，但是会浪费点空间。

1.1.2 mysql排序规则

常用的命名规则：_ci结尾表示大小写不敏感（case insensitive），_cs表示大小写敏感(case sensitive)，_bin表示二进制的比较（大小写敏感）。

排序规则	说明
utf8_bin	字符串每个字符用二进制数据编译存储。区分大小写，可以存储二进制内容。
utf8_general_ci	校对速度快，但准确度稍差。
utf8_unicode_ci	精准度高，但校对速度稍慢。

表 mysql部分常用排序规则说明

1.2 通配符

用在like后的字符串的符号。不匹配NULL。

1.2.1 mysql通配符

符号	说明
%	任何字符串出现任意次数
_	匹配任意一个字符

表 mysql通配符

1.2.2 使用技巧

1，不要过度使用通配符，通配符搜索的处理一般要比其他搜索所花时间更长。

2，尽量不要把它们用在搜索模式的开始处，因为置于该处，搜索起来最慢。

例如：%hello 效率远低于hello%。

1.3 汇总数据

1.3.1 null值

max、min、avg和sum 直接忽略null,不参与运算。

count(*)	返回的是所有记录的总和，含有null值的记录不会被忽略
count(column_name)	如果该列名含有null值，则这条记录会被忽略
count(1)	和count(*)一样。

表 count函数对null的处理

1.3.2 count(*) 和count(1)

count(1): 1并不是表示第一个字段，而是表示一个固定值。其实可以想象成表中有这么一个字段，这个字段就是固定值1。

count(*): 执行时会把*号翻译成字段具体名字，效果和上面一样，多了个翻译动作，比固定值效率稍微低有些。

1.3.3 count效率

count(主键)	遍历整张表，把每一行的id值都取出，返回给server层，server层按行累计
count(1)	遍历整张表，但不取值，server层对返回的每一行，放一个数字进去，按行累计
count(字段)	逐行从记录里读出这个字段，判断是否为空，不为空则累加
count(*)	并不会把所有值取出，mysql做了专门优化，按行累计

表 count在innoDB引擎下的执行原理

执行效率为：count(字段) < count(主键) < count(1） 约等于 count(*)

1.3.4 聚集不同值

对所有列执行计算，指定ALL或不指定（ALL是默认行为）；

只包含不同值，指定DISTINCT,DISTINCT不能用于count(*)；

SELECT AVG(DISTINCT price) FROM goods;

1.4 分组数据

图 操作的数据表

1.4.1 注意事项

1、GROUP BY可以包含任意数目的列（能够对分组进行嵌套）。

2、GROUP BY字句列出的每一个列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。

3、分组列中有NULL，则NULL将作为一个分组返回。

4、GROUP BY子句必须出现在WHERE子句之后，ORDER BY 子句之前。

SELECT * FROM `student`  GROUP BY age < 18;

图 group by表达式执行结果

1.4.2 WITH ROLLUP

使用WITH ROLLUP关键字，可以得到每个分组的小计及每个分组汇总级别的小计。其对GROUP BY 后的第一个字段进行分组求和（各组进行汇总，然后以组为计算级别进行汇总）。

图 WITH ROLLUP使用说明

上图语句等效于：

图 WITH ROLLUP等效语句及运行结果

1.4.3 only_full_group_by

对于GROUP BY操作，如果在SELECT中的列没有在GROUP BY中出现，那么将认为这个SQL不合法。（MYSQL5.7 之后新加的规范）

2 高阶查询

2.1 连接查询

类型	说明
内连接INNER JOIN	返回的数据行数是在前后两张表中同时存在的数据行数。任何一条只存在于某一张表中的数据都不会返回。与JOIN等效。
左外连接LEFT JOIN	left 左边的表为主表，右边的表为从表。返回的数据行数以主表的行数为最后的数据行，对于左边中有些数据行在右表找不到匹配数据记录时，返回结果通常以null来填充。等效于LEFT OUTER JOIN
右外连接RIGHT JOIN	于左连接相反，等效于RIGHT OUTER JOIN

表 mysql连接类型说明

注意：对于连接查询，最终结果数量可能会大于主表数据的数量，这是因为主表与从表是1对多的关系，当从表有多条数据与主表匹配时，其数据都会返回：

2.1.1 驱动表和被驱动表

驱动表在SQL语句执行过程中，总是先读取，而被驱动表在SQL语句执行的过程中，总是后读取。在驱动表数据读取后，放入到join_buffer后，再去读取被驱动表中的数据，来和驱动表中的数据进行匹配。如果匹配上，则作为结果返回，否则丢弃。

图 EXPLAIN命令查看SQL语句的执行计划

在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表为被驱动表。

INNER JOIN查询中，小表为驱动表。小表是指真正参与关联查询的数据量所占用的join_buffer的大小来区分。

2.1.2 连接过程分析

1) 选取驱动表，使用驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

2）对步骤1中查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。

2.1.3 ON和WHERE的区别

WHERE子句的过滤条件不论是内连接还是外连接，凡是不符合WHERE子句中过滤条件的记录都不会被加入到最后的结果集。

ON子句过滤条件对驱动表是无效的，对被驱动表，即使没有记录，也会采用NULL值填充各个字段。

图 主表与从表数据源

查询语句为：

SELECT t.name AS teacherName,c.name AS className

FROM teacher t

LEFT JOIN class c ON t.id = c.teacher_id

图 左查询查询结果

3 函数

3.1 常用函数

类型	名称	说明
数学	ABS(X)	返回X的绝对值
	MOD(N,M)	返回N被M除的余数 N % M
	CEILING(X)	返回不小于X的最小整数值
	ROUND(X)	四舍五入返回整数
字符串	CONCAT(str1,str2,...)	返回来自参数的连接后的字符串
	LOCATE(substr,str)	字串substr在字符串str第一个出现的位置
	LEFT(str,len)	返回字符串str的最左边len个字符
	SUBSTRING(str,pos)	返回字符串str从pos到结尾的字符串
	TRIM(str)	去除前后空格
	REPLACE(str,f_str,t_str)	字符串str中所有的f_str被t_str替代
	REPEAT(str,count)	返回重复count次str的字符串，如果count<= 0,返回一个空字符串，如果str是NULL,则返回NULL
	REVERSE(str)	返回颠倒str字符顺序的字符串
	INSERT(str,pos,len,newStr)	str字符串从pos开始到len长度的子串由newStr代替
	STRCMP(str1,str2)	str1 > str2 返回1，str1 < str2返回-1否则返回0
日期与时间	DAYOFWEEK(date)	返回日期date的星期索引(1=星期天)
	WEEKDAY(date)	返回日期date的星期索引(0=星期一)
	DAYOFMONTH(date)	返回date的月份中的日期(1~31)
	DAYOFYEAR(date)	返回date在一年中的日数(1~366)
	MONTH(date)	返回月份(1~12)
	QUARTER(date)	返回date一年中的季度(1~4)
	WEEK(date,tag)	返回周数(0~52),tag为1则表示从周一开始，为0表示从周日开始
	YEAR(date)	返回date的年份
	HOUR(time)	返回小时(0~23)
	DATE_ADD(date,INTERVAL, num type)	进行日期增加操纵,type为类型:秒，小时...
控制流程	CASE value WHEN [compare-value1] THEN result1 ELSE result2 END	判断value的值，如果为compare-value则返回result1，否则返回result2。可以有多个when。必须END结尾
	IF(expr1,result1,result2)	如果expr1为true则返回result1否则返回result2

表 MYSQL常用函数

3.2 自定义函数

根据返回值当类型可以分为标量函数（返回单个值）和表格值函数（返回一张表） 当前版本不支持表格值函数。

3.2.1 标量函数

语法：

CREATE FUNCTION function_name(@parameter_name parameter_data_type) --(@参数名 参数的数据类型) 在内置函数中，sql语句可以使用函数变量。

RETURNS data_type --返回值的数据类型

[characteristic] –函数特性

[AS]

BEGIN

      function_body –函数体

      RETURN 表达式;

END

函数特性一共有5种：

1）DETERMINISTIC 不确定的。

2）NO SQL 没有SQL语句。

3）READS SQL DATA 只是读取数据。

4）MODIFIES SQL DATA 要修改数据。

5）CONTAINS SQL 包含了SQL语句。

DROP FUNCTION IF EXISTS ageCount;

CREATE FUNCTION ageCount(p_age INT)

RETURNS INT READS SQL DATA

BEGIN

DECLARE p_count INT DEFAULT 0;

SELECT COUNT(*) INTO p_count FROM student WHERE age > p_age;

RETURN p_count;

END;