MYSQL--第五和第六章笔记

# 按照函数的定义，可分为内置函数和自定义函数，自定义函数后面再写。内置函数就现成的函数。
# 不同的数据库之间DBMS函数差别很大！移植性比较差
# 按照实现的功能角度，mysql的内置函数可分：数值函数、字符串函数、日期和时间函数、
# 流程控制函数、加密与解密函数、获取mysql信息函数、聚合函数。。。
# 又可再分为：单行函数和多行函数（聚合函数）
# 单行函数：只对一行进行变换、每行返回一个结果。。。
# 多行函数：对多行进行变换，多行返回一个结果。。

# 数值函数：
# ABS(x)：返回x的绝对值 SIGN(X):返回x的符号，正数返回1，负数返回-1，0就返回0
# PI(X):返回圆周率Π的值 CEIL(X)或CEILING(X):返回大于或等于x的最小整数
# FLOOR(X)：返回小于或等于x的最大整数 LEAST(value1,value2,...)：返回列表中的最小值
# GREATEST(value1,value2,...)：返回列表中最大值 `MOD`(N,M)：返回n除以m后的余数
# RAND(x)：返回0~1的随机数，其中x作为种子值，相同的x值会产生相同的随机数
# RAND()：返回0~1的随机数 ROUND(x)：返回一个对x值进行四舍五入后，最接近x的整数
# ROUND(x,y)：返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面y位
# TRUNCATE`(X,y)；返回数字x取y位小数的结果 SQRT(X)：返回x的平方根，如果x为负数，返回null
SELECT ABS(34),ABS(-35),SIGN(3),SIGN(-3),SIGN(0),PI(),CEIL(34.12),CEIL(-34.32),FLOOR(34.12),
FLOOR(-34.12),MOD(12,5) FROM DUAL;
SELECT CEIL(5) FROM DUAL;-- 如果是整数，取本身
SELECT RAND(),RAND(),RAND(2),RAND(2) FROM DUAL;
SELECT ROUND(18.578),ROUND(28.579,2),ROUND(58.579,-2) FROM DUAL;
SELECT TRUNCATE(28.579,2) FROM DUAL;

# RADIANS(x):将角度转化为弧度，其中x是角度值 DEGREES(X)：将弧度转化为角度，其中x是弧度值
# SIN(X)：返回x的正弦值，其中，x 是弧度值 ASIN(X)：返回x的反正弦值，获取正弦为x的值，如果x不在1和-1之间，返回null
# COS(X):返回x的余弦值，其中，x是弧度值 ACOS(X)：返回x的反余弦值，获取余弦为x的值，如果x不在1和-1之间，返回null
# TAN(X)：返回x的正切值，其中，x是弧度值 ATAN(X)：返回x的反正切值，即返回正切值为x的值
# ATAN2(Y，x):返回两个参数的反正切值 COT(X)：返回x的余弦值，其中x是弧度值

# POW(X,Y)或POWER(X,Y):返回x的y次方 EXP(X)：返回e的x次方，其中e为2.718。。。
# LN(X)或LOG（x）：返回以e为底的对数，但x<=0时返回null
# LOG10(X):返回以10为底的对数，但x<=0时返回null
# LOG2(X):返回以2为底的对数，但x<=0时返回null

# BIN(N):返回n的二进制编码 HEN（x）：返回x的十六进制编码
# OCT(N)：返回n的八进制编码 CONV（x,f1,f2）:返回把x从f1进制数编程转化为f2进制的数

# 字符串函数
# ASCII(str)：返回字符串str中第一个字符的ascll码值
# CHAR_LENGTH(str):返回字符串str的字符数,作用和CHARACTER_LENGTH(S)相同-- 一个汉字一个字符
# LENGTH(str):返回字符串str的字节数，和字符集有关-- 不同字符集，汉字可能是三个或四个字节
# CONCAT(str1,str2,...):把str1、str2等连接起来
# CONCAT_WS(separator,str1,str2,...):同上一个函数，但每个字符串之间要加上separator
# INSERT(STR,IDX,LEN,REPLACESTR):将字符串str从idx位置开始到len长度的子串替换成REPLACESTR
-- 字符串的索引是从1开始！
# REPLACE`(str,from_str,to_str):将字符串str中from_str替换成to_str
-- 如果str没有from_str返回str，替换失败
# UPPER(str)或UCASE（s）：将字符串str的所有字符换成大写
# LOWER(str)或LCASE（str）：将字符串str的所有字符换成小写
# LEFT（str,n）:返回字符串str最左边的n个字符
# RIGHT（str，n）：返回字符串str最右边的n个字符
# LPAD(str,len,padstr):在字符串str最左边用padstr进行填充，直到str的长度为len个字符
# RPAD(str,len,padstr):在字符串str最右边用padstr进行填充，直到str的长度为len个字符

SELECT ASCII('ASDC'),ASCII('AV'),CHAR_LENGTH('HELLO'),CHAR_LENGTH('中国')
,LENGTH('hello'),LENGTH('中国') FROM DUAL;-- UTFMD3

SELECT CONCAT(e.name,'工作地点：',ed.city) AS "工作地点" FROM employees e JOIN employees_detailed ed
ON e.location_id = ed.location_id;

SELECT CONCAT_WS('','H','E','L','L','O') FROM DUAL;

SELECT CONCAT_WS('-','H','E','L','L','O') FROM DUAL;

# LTRIM(str):去掉字符串str左侧的空格
# RTRIM(str):去掉字符串str右侧的空格
# TRIM(STR):去掉字符串str首左侧和右侧的空格
# TRIM（s1 FROM S2）:去掉字符串s2的左侧和右侧的s1
# TRIM(LEADING s1 FROM s):去掉字符串s左侧的s1
# TRIM（TRAILING s1 FROM s）：去掉字符串s右侧的s1
# REPEAT(str,n):返回str重复n次的结果
# SPACE(n)：返回n个空格
# STRCMP(s1,s2):比较字符串s1、s2的ascll码值的大小
-- 前面的字符串ascll大返回1，反正-1，一样0
# SUBSTR(str,pos,len):返回字符串str从pos到len的子字符串
# LOCATE(SUBSTR,STR):返回字符串substr在字符串str中首次出现的位置
-- 没找到返回0
# ELT(N,str1,str2,str3,...):返回指定位置的字符串，如果n=1，返回str1，n=2，返回str2.。类推
# FIELD(str,str1,str2,str3,...):返回字符串str在字符串列表中第一次出现的位置
# FIND_IN_SET(str,STR_ARR):和上面那个差不多，只是把字符串列表放在了一起,中间用逗号隔开
# REVERSE(str):将字符串str进行反转
# NULLIF(expr1,expr2)：比较两个字符串，如果相等，返回null，不等返回exper1
SELECT ELT(3,'H','L','L','O'),FIELD('A','V','S','A','DE'),FIND_IN_SET('A','D,S,A,W,E,D') FROM
DUAL;

# 日期和时间函数

# 获取日期，时间
# CURDATE()或CURRENT_DATE():返回当前日期，只包含年、月、日
# CURTIME()或CURRENT_TIME():返回当前时间，只包含时、分、秒
# NOW()或SYSDATE()或CURRENT_TIMESTAMP()或LOCALTIME()或LOCALTIMESTAMP():返回当前系统日期和时间
# UTC_DATE():返回utc（世界标准时间）日期
# UTC_TIME():返回utc（世界标准时间）时间

# 日期和时间戳（毫秒数）的转换
# UNIX_TIMESTAMP()：以UNIX时间戳的形式返回当前时间
# UNIX_TIMESTAMP(date)：返回指定date时间的时间戳
# FROM_UNIXTIME(unix_timestamp)：将时间戳转换为普通格式的时间

# 获取月份、星期、星期数、天数等函数
# `YEAR`(date)、MONTH(date)、DAY(date)：返回具体的日期值
# `HOUR`(time)、MINUTE(time)、SECOND（time）：返回具体的时间值
# MONTHNAME(date):返回月份：january。。
# DAYNAME(date):返回星期几：MONDAY、TUESDAY。。。
# WEEKDAY(date):返回周几-- 注意周1是0,周日是6
# `QUARTER`(date)：返回日期对应得季度,范围1~4
# WEEK(date)或WEEKOFYEAR(date)：返回一年中的第几周
# DAYOFYEAR(date):返回日期是一年中的第几天
# DAYOFMONTH(date):返回日期位于所在月份的第几天
# DAYOFWEEK(date):返回周几-- 注意周1是0,周日是6

# 日期的操作函数
# EXTRACT(unit FROM date):返回指定日期中特定的部分，type指定返回的值
# type的取值：
# MICROSECOND:返回毫秒数
# SECOND:返回秒数
# MINUTE：返回分钟数
# HOUR：返回小时数
# DAY：返回天数
# WEEK：返回日期在一年中的第几个星期
# MONTH：返回日期在一年中的第几个月
# QUARTER：返回日期在一年中的第几个季度
# YEAR：返回日期的年份
# SECOND_MICROSECOND：返回秒和毫秒值
# MINUTE_MICROSECOND：返回分钟和毫秒值
# MINUTE_SECOND：返回分钟和秒值
# HOUR_MICROSECOND:返回小时和毫秒值
# HOUR_SECOND:返回小时和秒值
# HOUR_MINUTE:返回小时和分钟
# DAY_MICROSECOND:返回天和毫秒值
# DAY_SECOND：返回天和秒值
# DAY_MINUTE：返回天和分钟
# DAY_HOUR：返回天和小时
# YEAR_MONTH:返回年和月
SELECT EXTRACT(MINUTE_SECOND FROM NOW()),EXTRACT(YEAR_MONTH FROM NOW()) FROM DUAL;#4931,49分31秒

# 时间和秒钟转换的函数
# TIME_TO_SEC(time):将time转化为秒并返回结果值。转换公式：小时*3600+分钟*60+秒
# SEC_TO_TIME(seconds):将seconds转化为包含小时、分钟、秒的函数

# DATE_ADD(date,INTERVAL expr type):返回与给定日期时间相差INTERVAL时间段的日期时间
# DATE_SUB(date,INTERVAL expr type)：返回与date相差INTERVAL时间间隔的日期
-- INTERVAL固定，expr要间隔的时间，date表示给定的时间
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR_MONTH) FROM DUAL;-- 2021-06-02 15:04:02

# ADDTIME(time1,time2):返回time1加上time2后的时间，当time2为一个数字时，代表的是秒，可以是负数
# SUBTIME(time1,time2)：返回time1减去time2后的时间，当time2作为一个数字时，代表的是秒，可以是负数
# DATEDIFF(time1,time2)：返回time1-time2的日期间隔天数
# TIMEDIFF(time1,time2)：返回time1-time2的时间间隔
# FROM_DAYS(N):返回从0000年1月1日起，n天后的日期
# TO_DAYS(date)：返回日期date距离0000年1月1日的天数
# LAST_DAY(date):返回date所在月份的最后一天的日期
# MAKEDATE(year,day)：返回给定年份year和给定的day组合而成的日期
# MAKETIME(hour,minute,second):返回将给定小时、分钟、秒组合而成的时间
# PERIOD_ADD(time,n)：返回time加上n的时间

# 日期的格式化和解析：格式化：把日期转化成字符串 解析：字符串转化成日期
# DATE_FORMAT(date,format):按照字符串fmt格式化日期date值
# TIME_FORMAT(time,format):按照字符串fmt格式化时间time值
# GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}):返回日期字符串的显示格式
# STR_TO_DATE(str,format):按照字符串fmt对str进行解析，解析为一个日期
# fmt格式符：
# %Y:4位数的年份 %y:2位数的年份
# %M:月名表示月份（January。。） %m:两位数表示月份（01，02，03.。。）
# %b：英文缩写的月名（Jan。。） %c：数字表示月份（1、2、3。。。）
# %D：英文后缀表示月中的天数（1st、2nd。。） %d：两位数表示月中的天数（01、02.。）
# %e：数字形式表示月中的天数（1、2、3、4.。） %H:两位数表示小数，25小时制（01、02。。）
# %h：两位数表示小时，12小时制（1、2、3、4.。） %k：数字形式的小时，24小时制（1、2、3.。）
# %l：数字形式表示小时，12小时制（1、2、3） %i：两位数字表示分钟（00、01、02）
# %s：两位数子表示秒（00、01、02） %W:一周中的星期名称（Monday..）
# %a：一周中星期名称的缩写（Mon） %w：以数字表示周中的天数（0表示Sunday、1表示Monday）
# %p:AM或pm %%：表示%
SELECT DATE_FORMAT(NOW(),'%Y-%b-%d %k:%i:%s'),STR_TO_DATE('2022-Jun-02 15:45:00','%Y-%b-%d %k:%i:%s')
FROM DUAL;#2022-Jun-02 15:52:27 2022-06-02 15:45:00

# 流程控制函数
# `IF`(expr1,expr2,expr3):如果满足expr1，实行expr2，否则实行expr3。。类似三目运算符
SELECT name,sex,score,IF(score >= 95,'高分','一般') AS "成绩评判" FROM student;
# IFNULL(expr1, expr2)：对expr1进行判断，如果是null返回expr2，否则返回自己
# CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END;类似if。。else if。。 else if。。
SELECT name,sex,salary,CASE WHEN salary > 9000 THEN '666'
WHEN salary > 7000 THEN '不错'
WHEN salary > 5000 THEN '还好'
WHEN salary > 2000 THEN '还需努力'
ELSE 'fw' END "评价"
FROM employees;-- 不能用as，直接双引号起别名，else可省略

# CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END;类似switch。。case那个
SELECT name,sex,salary,CASE salary WHEN 10000 THEN '库库库'
WHEN 7700 THEN '不错'
ELSE 'ODK' END "评价"
FROM employees;

# 加密与解密函数
# PASSWORD(str)：返回字符串str的加密版本，41位长的字符串，加密结果不可逆，常用于用户的密码加密
-- mysql8.0不支持password，5.7可以
# MD5(str):返回字符串str的mds加密后的值，也是一种加密方式，若参数为NULL,则返回null
# SHA(str)：比mds更安全，计算返回加密后的密码字符串，若参数为null，返回null
# MD5和SHA都是不可逆的，只能从明文到暗文

SELECT VERSION() FROM DUAL;-- 可知目前是mysql8.0
# SELECT PASSWORD('斗气话马') FROM DUAL;-- 在mysql8.0不支持使用
# ENCODE(str,pass_str)和DECODE在mysql8.0也不可使用
SELECT MD5('斗气话马'),SHA('斗气话马') FROM DUAL;

# VERSION():返回当前mysql的版本号
# CONNECTION_ID():返回当前mysql服务器的连接数
# DATABASE()或SCHEMA():返回mysql命令行当前所在的数据库
# USER():返回当前连接mysql的用户名
# CHARSET(str)：返回字符串str自变量的字符集
# `COLLATION`(str):返回字符串str的比较规则

# 聚合函数（多行函数）
# 常见的：AVG / SUM MAX / MIN COUNT
# AVG：平均值 SUM(expr)：总和 -- 如果有null当0处理
SELECT AVG(salary),SUM(salary),AVG(name) FROM employees;
-- 如果AVG和SUM的对象不是数值型的话，会报错，mysql不太严谨就给了0
# MAX和MIN同理，但字符串可以比较大小
# COUNT：计算指定字段在查询结构中出现的个数
# 计算表中有多少行记录：
# 1、COUNT(*)
# 2、COUNT(常数，建议是1)
# 3、COUNT(具体字段):不建议使用，有时候不一定对，如果字段中有空值null ，就不对了
# 如果使用的是MyISAM 存储引擎，三者效率相同
# 如果使用的是InnoDB 存储引擎，三者效率：COUNT(*) = COUNT(1)> COUNT(具体字段)

# GROUP BY的使用：分组操作
SELECT location_id,AVG(salary),COUNT(*) AS "地区人数" FROM employees GROUP BY location_id ORDER BY location_id ASC;
# 多级分组操作
# SELECT ... FROM ... GROUP BY 目标字段1,目标字段2...;
-- 目标字段可以换位置不影响结果
# 注意：SELECT中出现的非聚合函数的字段必须在GROUP BY中也有，
# 而GROUP BY中出现的非聚合函数的字段可以不出现在SELECT中
# GROUP BY声明在from后面，where后面，ORDER BY前面,LIMIT前面

# HAVING的使用：用于过滤数据
# 错误写法：
# SELECT location_id,AVG(salary) FROM employees WHERE AVG(salary) > 9000 GROUP BY location_id;
# 正确写法：HAVING要写在GROUP BY的后面
SELECT location_id,AVG(salary)
FROM employees
GROUP BY location_id
HAVING AVG(salary) > 5000;
# 如果过滤条件是关于聚合函数的，必须用HAVING替换where，否则报错
# 建议：HAVING使用的前提是使用了GROUP BY！

# 查询location_id为2，5，7，10的员工的平均工资大于5000的location_id
# 方式一：建议使用这个，执行效率高于方式二
SELECT location_id,AVG(salary) FROM employees
WHERE location_id IN (2,5,7,10)
GROUP BY location_id
HAVING AVG(salary) > 5000;
# 方式二：
SELECT location_id,AVG(salary) FROM employees
GROUP BY location_id
HAVING AVG(salary) > 5000 AND location_id IN (2,5,7,10);
# 结论：当过滤条件中有聚合函数，聚合函数的条件必须声明在HAVING中，
# 当过滤条件没有聚合函数，则过滤条件可以在where也可以在HAVING中，但建议在where中，效率高点

# WHERE和HAVING的对比：
# 1、适用范围：HAVING的使用范围更广
# 2、如果过滤条件中没有聚合函数，where的执行效率高于HAVING（看执行顺序就可以知道）

# sql底层执行原理
# 1、SELECT语句的完整结构

# sql99的语法
# SELECT ..... (存在聚合函数)
# FROM ... (LEFT/RIGHT) JOIN 其他表名 ON 多表连接条件 (LEFT/RIGHT) JOIN ... ON ...
# WHERE 其他的过滤条件（不包含聚合函数的过滤条件）
# GROUP BY 要分组的字段
# HAVING 聚合函数的过滤条件
# ORDER BY 排序的字段（ASC\DESC）
# LIMIT 分页操作。。

# sql语句的执行过程：
# FROM -> ON -> (LEFT/RIGHT) JOIN -> WHERE -> ORDER BY ->HAVING
# -> SELECT -> DISTINCT -> ORDER BY -> LIMIT