SQL基础篇

20201219（1—数据库操作命令_1）

①显示当前数据库：show databases；
②创建数据库：create database 数据库名称 【charset =数据库编码】；
③删除数据库：drop database 【 if exists 】 数据库名称 ；
④修改数据库：alter database 数据库名称 charset =新的编码名；
⑤显示数据库创建语句：show create database 数据库名称；
⑥进入数据库：use 数据库名称；

20201220（2.1—数据库对表的命令_2）

①创建表：create table 表名(
字段1 数据类型 字段属性，
…
字段n 数据类型 字段属性
);
②查看数据表列表：show tables;
③查看数据表结构：desc 数据表名;
④查看数据表建表语句：show create table 数据表名;
⑤删除数据表：drop table 数据表名;

20201221（2.2—数据库对表的命令_3）

①修改表名 ：alter table 旧表名 rename 新表名;
②修改字段的数据类型 alter table 表名 modify 字段名 新数据类型;
③修改字段名 ：alter table 表名 change 旧字段名 新字段名 新数据类型;
④添加字段： alter table 表名 add 字段名 数据类型 [约束条件] [first|after 已存在字段];
first作用：将该字段添加到表的第一个字段
after 已存在字段：将该字段添加到指定的已存在字段的后面
⑤删除字段 ：alter table 表名 drop 字段名;
⑥修改字段的排列顺序： alter table 表名 modify 字段1 数据类型 first|after 字段2;
7.修改表的字符集:alter table 表名 default character set utf8;
8.修改表字段的字符集:alter table 表名 change 字段名 字段名varchar(255) character set utf8;
修改字符集的作用:防止插入字符串的数据时无法插入

20201222（3.1—数据操作语句_1）

①插入单行数据：insert into 表名 (字段名列表(逗号隔开)) values(值列表(逗号隔开));
②插入多行数据 ：insert into 表名(字段名列表) values (值列表1), … ,(值列表n);
③将查询结果插入到新表中：create table 新表(select 字段1, … ,from 原表);
④更新数据（修改数据）：update 表名 set 列名=更新值 where 更新条件;
⑤删除数据1：delete from 表名 where 删除条件;
⑥删除数据2：truncate table 表名;

20201223（3.2—数据操作语句_2）

①使用select查询：select 列名/表达式/函数/常量 from 表名 where 查询条件 order by 排序的列名asc/desc;
②查询所有的数据行和列：select * from 表名；
③查询部分行和列：select 列名… from 表名 where 查询条件；
④在查询中使用列的别名：select 列名 AS 新列名 from 表名 where 查询条件;

20201224（3.3—数据操作语句_3）

①使用select查询：select 列名/表达式/函数/常量 from 表名 where 查询条件 order by 排序的列名asc/desc;
②查询所有的数据行和列：select * from 表名；
③查询部分行和列：select 列名… from 表名 where 查询条件；
④在查询中使用列的别名：select 列名 AS 新列名 from 表名 where 查询条件；

20201225（4.1—单表查询_1）

①字符拼接concat：select concat（列名1，列名2，…）from 表名；
②指定拼接符concat_ws：select concat_ws（’拼接符’，列名1，列名2，…） from 表名；
③内容去重distinct：select distinct 列名 from 表名；
④结果排序order by：select from 表名 order by 排序列名 asc/desc；
⑤中文文本排序：select from 表名 order by convert（排序列名 using gbk）；
注：asc/desc只作用于紧跟着的前一列名，作用于多列则每列都需要加入asc/desc关键词；

20201228（4.2—单表查询_2）

①模糊查询like：select from 表名 where 列名 like ’查询名’；
注：like后的通配符，_表示单个，任何字符；%表示任意数目，任何字符；
image.png

image.png
②取值限制in：select from 表名 where【字段】in （值1，值2，…）；
image.png
③数值过滤：指定数值在某两个值之间：select from 表名 where 【列】between 【前段值】and 【后端值】；
④空值过滤：判断列为空值：select from 表名 where 【列】is null；

20201229（4.3—单表查询_3）

①数据处理函数：
绝对值函数：ABS；平方根函数：SQRT；指数函数：EXP；四舍五入函数：ROUND;
②文本处理函数：
字符长度：char_length（str）计算单位：字符，不管汉字还是数字或者是字母都算是一个字符。
字节数：length（str）计算单位：字节，utf-8编码：一个汉字三个字节，一个数字或字母一个字节；gbk编码：一个汉字两个字节，一个数字或字母一个字节。
去除左边空值：LTRIM，LTRIM(‘ home’)结果为home;
去除右边空值：RTRIM，RTRIM(‘home ’)结果为home；
去除空值：TRIM，RTRIM(‘ home ’)结果为home；
大写字母转换：UPPER，UPPER(‘home’)=HOME;
小写字母转换：LOWER，LOWER(‘HOME’)=home;
③日期处理函数：
获取当前日期时间：NOW();
获取当前日期：CURDATE();
获取当前时间： CURTIME();
时间增加：DATE_ADD，DATE_ADD(‘20201129’，interval 1 MONTH) 结果为20201229
时间减少：DATE_SUB，DATE_SUB(‘20201129’，interval 1 YEAR) 结果为20191129

20201230（4.4—单表查询_4）

①聚合函数count：
count(*)或者count(1)确定表中行的数目或者符合特定条件的行的数目。
count(distinct col)：对行去重计数。
②分组数据：select … from 表名 where 【条件】 group by 【分组列名】 having 【条件】；
image.png
注：语句执行顺序：from…、where…、group by…、having…、select…。
（选择数据表进行数据过滤，再将数据分组，各组中进行聚合，对聚合结果再次筛选后查找出数）
详细语句执行顺序：http://blog.csdn.net/u010833154/article/details/50696583

20201231（5.1多表查询—子查询_1）

①标量子查询：子查询得到结果是一个数据 （一行一列）。
语法：select * from 数据源 where 条件判断 =/<> (select 字段名 from 数据源 where 条件判断);
例子：查询所有商品价格大于日用品类平均价格的商品，并且按价格降序排序
image.png

②关联子查询：嵌套于其他查询中的查询，适用于组内比较。
语法：SELECT <列名1>, <列名2>, …FROM <表名> AS <别名1>WHERE <列名2> > (SELECT <表达式>FROM <表名> AS <别名2>WHERE <别名1>.<列名1> = <别名2>.<列名1>【GROUP BY 列名3】);
例子：查询各类别的商品中，价格高于该类商品均价的部分
image.png

20210104（5.2多表查询—子查询_2）

①列子查询：子查询得到的结果是一列数据（一列多行）
语法：主查询 where 条件 in (列子查询);
例子：查询供货地为南京的所有商品信息
image.png
②行子查询：子查询返回的结果是一行多列
语法：主查询 where 条件 [(构造一个行元素)] = (行子查询);
行元素：字段元素是指一个字段对应的值，行元素对应的就是多个字段，多个字段合起来作为一个元素参与运算，这种情况成为行元素。例子：image.png
③exists子查询：子查询返回的结果只有true或者false（成立或者不成立）
语法：select * from 数据源 where exists(查询语句);
image.png

20210105（5.3多表查询—组合查询）

①Mysql中的UNION：UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
image.png
②Mysql中的UNION ALL:UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
image.png
总结：使用UNION，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了DISTINCT,如果多表查询结果中有完全一致的数据，Mysql将自动去重；使用UNION ALL，则不会排重，返回所有的行。

20210106（5.4多表查询—表联结_1）

注：联结是一种机制，关联多个表，返回一组输出，在一条select语句中实现；
联结可以分为3类：内部联结，交叉联结和外部联结。
　　(1) 内部联结：join，inner join
　　(2) 交叉联结：cross join
　　(3) 外部联结：left join，left outer join，right join，right outer join
image.png
演示表如下所示：
image.png
a表与b表如下所示：
image.pngimage.png
①内部联结：两个表中同时满足某条件的数据记录组合。也就是表A和表B中满足条件a.id = b.a_id的所有记录。
image.png
注：当表A/B中的一条记录对应表B/A中的多条记录时，会以重复的方式对应多条表A/B记录出现在结果集中；inner join on 中的inner join可换成逗号，on换成where。
②交叉联结：即为表A与表B的笛卡尔乘积。
image.png

20210107（5.5多表查询—表联结_2）

① 左联结：会以左边的表为主表，返回所有行，即使右表B中没有匹配的行。
image.png
② 右联结：与左联结一样，区别在于主表的确定，以右边的表为主表，返回所有行，两者之间可以相互转换。
image.png

20210108（补充—窗口函数1）

1.窗口函数的基本语法如下：<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
注意:MySQL数据库不支持ROW_NUMBER(),RANK(),DENSE_RANK()这3个窗口函数
2.作用：业务需求（组内排名问题：各部门按业绩来排名，topN问题：找出各部门前N的员工进行奖励）
3.<窗口函数>的位置，可以放以下两种函数：
专用窗口函数

  • 序号函数：row_number() / rank() / dense_rank()
  • 分布函数：percent_rank() / cume_dist()
  • 前后函数：lag() / lead()
  • 头尾函数：first_value() / last_value()
  • 其他函数：nth_value() (用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名)/ntile()（用途：将分区中的有序数据分为n个桶，记录桶号

聚合函数，如sum()， avg()， count()， max()， min()等
4.注：
1）窗口函数对where和group by子句处理后的结果进行操作，所以原则上窗口函数只能写在select子句中。
2）聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，查询结果并不会改变记录条数。
5.练习表—建表语句：
student表：学号，姓名，班级；
course表：课程号，学科；
score表：学号，课程号，分数。

  1. CREATE TABLE STUDENT (SID INT NOT NULL,SNAME VARCHAR(200));
  2. CREATE TABLE COURSE (CID INT NOT NULL,CNAME VARCHAR(200));
  3. CREATE TABLE SCORE(SID INT NOT NULL,CID INT,SCORE INT);
  5. #更改表的字符集和字符串字段的字符集
  6. alter table STUDENT default character set utf8;
  7. alter table STUDENT change SNAME SNAME varchar(255) character set utf8;
  8. alter table COURSE default character set utf8;
  9. alter table COURSE change CNAME CNAME varchar(255) character set utf8;
  13. INSERT INTO STUDENT VALUES(1,'小明');
  14. INSERT INTO STUDENT VALUES(2,'小杰');
  15. INSERT INTO STUDENT VALUES(3,'小可');
  16. INSERT INTO STUDENT VALUES(4,'小李');
  17. INSERT INTO STUDENT VALUES(5,'小王');
  20. INSERT INTO COURSE VALUES(1,'语文');
  21. INSERT INTO COURSE VALUES(2,'数学');
  22. INSERT INTO COURSE VALUES(3,'外语');
  24. INSERT INTO SCORE VALUES(1,1,80);
  25. INSERT INTO SCORE VALUES(2,2,100);
  26. INSERT INTO SCORE VALUES(3,3,87);
  27. INSERT INTO SCORE VALUES(4,1,66);
  28. INSERT INTO SCORE VALUES(5,2,100);
  29. INSERT INTO SCORE VALUES(1,3,30);
  30. INSERT INTO SCORE VALUES(2,1,48);
  31. INSERT INTO SCORE VALUES(3,2,60);
  32. INSERT INTO SCORE VALUES(4,3,79);
  33. INSERT INTO SCORE VALUES(5,1,20);
  34. INSERT INTO SCORE VALUES(1,2,97);
  35. INSERT INTO SCORE VALUES(2,3,99);
  36. INSERT INTO SCORE VALUES(3,1,88);
  37. INSERT INTO SCORE VALUES(4,2,85);
  38. INSERT INTO SCORE VALUES(5,3,79);

image.pngimage.png
image.png

专用窗口函数:
①序号函数：row_number() 、 rank() 、 dense_rank()
注意:MySQL数据库不支持ROW_NUMBER(),RANK(),DENSE_RANK()这3个窗口函数
用法含义：
row_number 相同成绩不会并列，按出现顺序排名；
rank 相同成绩会并列，并且会跳号；
dense_rank 相同成绩会并列，并且不会跳号。

例子：根据课程分类，对每门课程按照分数进行排名
image.png
注：3个序号函数分别按照课程进行进行分类，按照分数进行排名，如科目为数学所示，分数存在重复值89分。
row_number()函数中，会忽略并列的情况，即：成绩89分的有两个人，则成绩85分的是第二名，通过row_number()函数，名次为1,2,3，……；
rank()函数中，如果有并列情况，会占用下一个名次的位置，即：成绩89分的有两个人，则成绩85分的是第二名，通过rank()函数，名次为1,1,3，……；
dense_rank()函数中，如果有并列的情况，不会占用下一个名次的位置，即：成绩89分的有两个人，则成绩85分的是第二名，通过rank()函数，名次是：1,1,2，……。

20210111（补充—窗口函数2）

专用窗口函数
②分布函数：percent_rank() / cume_dist()

  • percent_rank()
    用途：和之前的RANK()函数相关，每行按照如下公式进行计算：(rank - 1) / (rows - 1)，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

    1. 应用场景：作为分析函数使用，对一组行的集合进行计算并返回多个值。

例子：根据课程分类，对每门课程按照分数进行排名并查看排名分布情况
image.png
注：以数学科目为例，rank为ranking（）函数所得结果，rows即为6行。
cume_dist()
用途：计算一个值在一组值中的累积分布，返回值的范围为(0,1]，这个函数比percen_rank使用场景更多。
应用场景：某门课程各同学排名在前百分之几

例子：查看各门课程中，各同学排名所占百分比
image.png
注：以数学科目为例，高虎和史立的数学成绩并列第一，在班级排名前33.33%

20210112（补充—窗口函数3）

专用窗口函数
③头尾函数：first_value()/last_value()
first_value() 函数：指定排序字段，不同分区中，指定字段在窗口范围第一个值。
last_value() 函数：指定排序字段，不同分区中，指定字段在窗口范围最后一个值。
应用场景：快速查看某个窗口范围中的第一个或者最后一个指定的字段的数值。
image.png
注：以史立为例，他的得分范围按顺序是76，89，68；first_value()函数计算的那一列firstVal得到的数据即为76；last_value()函数计算的那一列lastVal得到的数据即为68。

20210113（补充—窗口函数4）

专用窗口函数
④前后函数：lag和lead函数可以在同一次查询中取出同一字段的前N行数据（lag）和后N行数据（lead）
LAG()函数：LAG(EXP_STR,OFFSET,DEFVAL)OVER()
LEAD()函数：LEAD(EXP_STR,OFFSET,DEFVAL)OVER()
EXP_STR：要取的列
OFFSET： 取偏移后的第几行数据
DEFVAL：无偏移值的取值，默认为NULL
应用场景：求每个用户相邻两次浏览的时间差；求每个同学相邻两门考试的成绩差等
image.png
注：以子健为例，他的得分范围按顺序是95，79，34；lev这一列是按照score这一列的数据整体上移，即为79,34,null（为什么空值不是78，因为是按照名字分组）；lav这一列是按照score这一列的数据整体下移，即为null,95,79。

20210114（补充—窗口函数5）

专用窗口函数
⑤其他函数：ntile()
用途：将分区中的有序数据分为n个桶，记录桶号。
注：此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均，多出来的部分则依次加给第一组、第二组···直到分配完。
image.png
注：以sid=01和sid=03为例，sid=01，有3条数据，由于ntile是分为2个数据桶的，所以均分无法被2整除，所以多出来的部分就加给了第一组，因此第一组2个，第二组1个；而sid=03，有4条数据，可以被2整除，所以第一组和第二组分别为2个。

20210115（6—视图）

1.定义：视图是一个虚拟表，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
注1：可以将查询过程中的临时表摘出来，用视图去实现，以后再想操作该临时表的数据时就无需重写复杂的sql，直接去视图中查找即可。
视图里面存储的就是临时表的数据,查询视图本质就是查询的复杂SQL产生的临时表的数据
注2：视图有明显地效率问题，同时视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，就意味着扩展sql极为不便。
2.练习表：
t_course表：（课程id，课程名，老师id）
t_teacher表：（老师id，老师名）

  1. CREATE TABLE T_COURSE (CID INT NOT NULL,CNAME VARCHAR(200),TID INT);
  2. CREATE TABLE TEACHER (TID INT NOT NULL,TNAME VARCHAR(200));
  5. alter table T_COURSE default character set utf8;
  6. alter table T_COURSE change CNAME CNAME varchar(255) character set utf8;
  7. alter table TEACHER default character set utf8;
  8. alter table TEACHER change TNAME TNAME varchar(255) character set utf8;
  10. INSERT INTO T_COURSE VALUES(1,'语文',1);
  11. INSERT INTO T_COURSE VALUES(2,'数学',1);
  12. INSERT INTO T_COURSE VALUES(3,'英语',3);
  13. INSERT INTO T_COURSE VALUES(4,'体育',2);
  14. INSERT INTO T_COURSE VALUES(5,'音乐',1);
  15. INSERT INTO T_COURSE VALUES(6,'科学',3);
  16. INSERT INTO TEACHER VALUES(1,'黎明');
  17. INSERT INTO TEACHER VALUES(2,'张三');
  18. INSERT INTO TEACHER VALUES(3,'王五');

image.png image.png
①创建视图：CREATE VIEW 视图名称 AS SQL语句；
例子：create view teacher_view as select tid from t_teacher where tname=’李四’;
查询李四老师的课程名的sql可以改写为：select cname from t_course where teacher_id = (select tid from teacher_view);

  1. #创建视图
  2. CREATE VIEW TEACHER_VIEW AS SELECT TID FROM TEACHER WHERE TNAME = '黎明';
  3. #运用视图
  4. SELECT CNAME FROM T_COURSE
  5. WHERE TID = (SELECT TID FROM TEACHER_VIEW);
  7. #对于上述SQL的讲解
  8. SELECT TID FROM TEACHER_VIEW;
  9. 就等价于
  10. SELECT TID FROM (SELECT TID FROM TEACHER WHERE TNAME = '黎明');

image.png
②修改视图：ALTER VIEW 视图名称 AS SQL语句
例子：alter view teacher_view as select * from course where cid>3;

原视图：
image.png
更新后视图：
image.png
②删除视图：DROP VIEW 视图名称
image.png

20210118（7.1—正则表达式查询REGEXP-1）

1.语法：属性名 REGEXP ‘匹配方式’
其中，“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。

匹配选项 定义说明 示例
^ 匹配文本的开始字符 ‘^b’ 匹配以字母 b 开头的字符串
$ 匹配文本的结束字符 ‘st$’ 匹配以 st 结尾的字符串
. 匹配任何单个字符 ‘b.t’ 匹配任何 b 和 t 之间有一个字符
* 匹配零个或多个在它前面的字符 ‘f*n’ 匹配字符 n 前面有任意个字符 f
+ 匹配前面的字符 1 次或多次 ‘ba+’ 匹配以 b 开头，后面至少紧跟一个 a
<字符串> 匹配包含指定字符的文本 ‘fa’ 匹配包含‘fa’的文本
[字符集合] 匹配字符集合中的任何一个字符 ‘[xz]’ 匹配 x 或者 z
[^] 匹配不在括号中的任何字符 ‘[^abc]’ 匹配任何不包含 a、b 或 c 的字符串
字符串{n,} 匹配前面的字符串至少 n 次 ‘b{2}’ 匹配 2 个或更多的 b
字符串
{n,m}		 匹配前面的字符串至少 n 次， 至多 m 次 ‘b{2,4}’ 匹配最少 2 个，最多 4 个 b

①查询以特定字符和字符串开头的记录：字符^用来匹配以特定字符或字符串开头的记录。
例子：在 tb_students_info 表中，查询 name 字段以“J”开头的记录
image.png
②查询以特定字符或字符串结尾的记录：字符$用来匹配以特定字符或字符串结尾的记录。
例子：在 tb_students_info 表中，查询 name 字段以“y”结尾的记录
image.png
③替代字符串中的任意一个字符：字符.用来替代字符串中的任意一个字符。
例子：在 tb_students_info 表中，查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录
image.png

20210119（7.2—正则表达式查询REGEXP-2）

④匹配指定字符串：正则表达式可以匹配字符串。当表中的记录包含这个字符串时，就可以将该记录查询出来；指定多个字符串时，需要用|隔开，只要匹配这些字符串中的任意一个即可。
例子：在 tb_students_info 表中，查询 name 字段值包含字符串“an”或“en”的记录
image.png
⑤匹配指定字符串中的任意一个：使用方括号[ ]可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符，该记录就会被查询出来。例如，通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。
例子：在 tb_students_info 表中，查询 name 字段值包含字母“i”或“o”的记录
image.png
⑥匹配指定字符以外的字符：[^字符集合]用来匹配不在指定集合中的任何字符
例子：在 tb_students_info 表中，查询 name 字段值包含字母 a~t 以外的字符的记录
image.png

⑦使用{n,}或者{n,m}来指定字符串连续出现的次数：字符串{n,}表示字符串连续出现 n 次；字符串{n,m}表示字符串连续出现至少 n 次，最多 m 次。
例子：在 tb_students_info 表中，查询 name 字段值出现字母‘e’ 至少 2 次的记录
image.png

SQL实例篇

针对目前开发的新数据库内容，按照各知识点内容进行实例化演示。

20210120（数据表基本操作）

1.创建数据表：
create table 表名(
字段1 数据类型 字段属性，
…
字段n 数据类型 字段属性
)ENGINE=MyISAM/InnoDB DEFAULT CHARSET=utf8;
新库实例语句：创建排班详情表（dwd_fact_staff_attendances_working_schedule_ss_d）
image.png
MyISAM和InnoDB存储引擎的区别：
核心区别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename engine=innodb）。
应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

20210125（MYSQL日期函数）

1.DATE_ADD和DATE_SUB函数：
DATE_ADD(date,INTERVAL expr type)：给日期添加指定的时间间隔。
DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。
注：date 参数是合法的日期表达式；expr 参数是您希望添加的时间间隔,比如1,2,3…..；type参数是时间类型项，例如：HOUR,DAY,MONTH等。
①举例说明：

  1. SELECT DATE_ADD('20210511',INTERVAL 2 DAY) AS DA,DATE_SUB('20200511',INTERVAL 1 YEAR) AS DS

image.png
2.CURDATE和CURRENT_DATE：获取系统当前日期
CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回。
具体格式根据函数用在字符串或数字语境中而定。
①举例说明：
使用日期函数 CURDATE 和 CURRENT_DATE 获取系统当前日期，输入的 SQL 语句和执行结果如下所示

  1. select CURRENT_DATE(),CURDATE(),CURRENT_TIME(),CURRENT_TIMESTAMP(),CURRENT_DATE()+0

image.png
由运行结果可以看到，两个函数的作用相同，返回了相同的系统当前日期，“CURRENT_DATE() +/- N”将当前日期值转换为数值型的。
②新库实例语句：查询鹰眼-销售管理明细表中每日流水情况
image.png
注：当按照日期查询总流水时，比较复杂的就是对时间段的处理，对于时间段范围即要有开始日期，还要有查询前一天的日期；同时SQL为通用，无需根据当天日期的改变而修改SQL。因此CURDATE() 和 CURRENT_DATE() 函数派上用场。
SQL复杂项解释说明：DATE_ADD(CURRENT_DATE,interval -day(CURRENT_DATE)+1 day) and DATE_ADD(CURDATE(),INTERVAL -1 DAY)，以今日（2021-01-25）查询为例。
CURRENT_DATE：2021-01-25
-day(CURRENT_DATE)+1 day：当前日期的日取反加1，即首先得到2021-01-25的日为25，负数取反即-25再加1即-24
DATE_ADD(CURRENT_DATE,interval -day(CURRENT_DATE)+1 day)：相当于DATE_ADD(2021-01-25,INTERVAL-24day)=2021-01-01
DATE_ADD(CURDATE(),INTERVAL -1 DAY)：相当于DATE_ADD(2021-01-25,INTERVAL -1 DAY)=2021-01-24
因此日期的范围即为2021-01-01至2021-01-24，同时随着查询时间的变化，SQL语句也无需改变。

20210128（MYSQL合计函数）

1.合计函数：合计函数的操作面向一系列的值，并返回一个单一的值。
①AVG函数：AVG函数返回数值列的平均值。NULL 值不包括在计算中。
语法：SELECT AVG(column_name) FROM table_name
②COUNT函数：COUNT() 函数返回匹配指定条件的行数。
语法1：COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT AVG(column_name) FROM table_name
语法2：COUNT() /COUNT(1)函数返回表中的记录数
SELECT COUNT() FROM table_name
语法3：COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT column_name) FROM table_name
③FIRST函数：FIRST() 函数返回指定的字段中第一个记录的值(可使用 ORDER BY 语句对记录进行排序)
语法：SELECT FIRST(column_name) FROM table_name
④LAST函数：LAST() 函数返回指定的字段中最后一个记录的值(可使用 ORDER BY 语句对记录进行排序)
语法：SELECT LAST(column_name) FROM table_name
⑤SUM函数：SUM 函数返回数值列的总数（总额）
语法：SELECT SUM(column_name) FROM table_name
实例1：查看每日1号至查询日的前一天净流水情况。
image.png
实例2：查询五维总览表中1月8日所有人产生的流水，现金量和人员数。
image.png

20210129（MYSQL中case when函数）

1.case when函数：根据筛选条件，返回一个值，适用于一个条件有多个值分别执行不同操作的场景。
2.应用场景：已知数据按照另外一种方式进行分组；两个表数据是否一致的检查等等。
①简单case函数：
case 列名
when 条件1 then 值1
when 条件2 then 值2
else 值3
end
②case搜索函数：
case when 列1=条件1 then 值1
when 列2=条件2 then 值2
else 值3
end
③case函数在没有匹配到结果时的处理规则为：
1）若没有匹配到结果值，则返回 ELSE后结果；
2）若没有ELSE部分，则返回值为 NULL；
3）若字段为 NOT NULL，则根据字段类型返回不同值。例如，字符串类型时，返回空字符串，数字类型返回 0。
3.优点：一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。
4.实例：
1）将报表中bf订单标签以特定方式进行重新命名分组。
image.png
2）对报表中多种报名渠道进行重新优化分组。
image.png

20210201（MYSQL中拼接函数）

1.CONCAT函数：用于将多个字符串连接成一个字符串。
语法：CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。
适用场景：组合多列信息为一列，对列的信息进行补充。
实例：将订单详情表中，学员信息与学员id拼接为一个字符串。
image.png
2.CONCAT_WS函数：指定参数之间的分隔符,将多个字符串连接成一个字符串。
语法：CONCAT_WS(separator,str1,str2,…)；CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
适用场景：多字段拼接，字段间使用同样的拼接符。
实例：将订单详情表中，学员信息与学员id拼接为一个字符串，拼接符为“-”。
image.png
3.GROUP_CONCAT函数：返回一个字符串结果，该结果由分组中的值连接组合而成。
语法：GROUP_CONCAT([DISTINCT] 要连接的字段 [ORDER BY ASC/DESC 排序字段] [SEPARATOR ‘分隔符’]) ；
注释：在 MySQL 中，你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序，可以使用 ORDER BY 子句。SEPARATOR 是一个字符串值，它被用于插入到结果值中。缺省为一个逗号 (“,”)，可以通过指定 SEPARATOR “” 完全地移除这个分隔符。
实例：查看10项产品小类中，各学员的id号。
image.png

20210202（MYSQL高级函数）

1.IF函数：
语法：IF(expr1,expr2,expr3)
说明：如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。
例子：
mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> ‘yes’
mysql> select IF(strcmp(‘test’,’test1’),’yes’,’no’);
-> ‘no’
2.IFNULL函数：
语法：IFNULL(expr1,expr2)
说明：如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。
例子：
mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
实例：下述查询结果为北京员工排班工作情况。
image.png
3.FORMAT函数：将数据内容格式化的，可以将数据格式化为整数或者带几位小数的浮点数（四舍五入）。
说明：如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。
例子：
mysql> SELECT FORMAT(100000,2);——数据内容格式化，格式化后得到结果：＃＃＃，＃＃＃，＃＃＃＃＃。
-> 100,000.00
mysql> SELECT FORMAT(100.31111,2);——格式化数据为整数或者浮点数。
->100.31
mysql> SELECT FORMAT(423423234.65534453,2);——具有四舍五入的功能
-> 423,423,234.66
4.DATE_FORMAT函数：以不同的格式显示日期/时间数据。
语法：DATE_FORMAT(date,format)
说明：date 参数是合法的日期。format 规定日期/时间的输出格式。
常用格式项：%c月，数值；%d月的天，数值(00-31)；%H小时 (00-23)；%e月的天，数值(0-31)；%i分钟，数值(00-59)；%h小时 (01-12)；%I小时 (01-12)；%l小时 (1-12)；%k小时 (0-23)；%m月，数值(00-12)；%S秒(00-59)；%s秒(00-59)；%y年，2 位；%Y年，4 位。
例子：
mysql> SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’);
-> 2021-02-02
mysql> SELECT DATE_FORMAT(NOW(),’%Y/%m/%d %H:%i:%s’);
->2021/02/02 20:30:48

20210203（MYSQL分组函数）

1.GROUP BY ：用于结合合计函数，根据一个或多个列对结果集进行分组。
语法：
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
说明： aggregate_function为某种合计函数，例如avg（），sum（），count（）等等；
实例：查找各事业部的名片总数
image.png
2.WITH ROLLUP：可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
实例：查找各事业部的名片总数，同时输出整个事业部的名片总数。
image.png
注：其中记录 NULL 表示所有事业部的名片总数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称。
coalesce 语法：select coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。
image.png

20210207（MYSQL函数）

1.LCASE() 函数 ：LCASE 函数把字段的值转换为小写
语法：SELECT LCASE(column_name) FROM table_name
类似函数：LOWER(),同样也能将字符串中所有字母字符转换为小写

2.RCASE() 函数 ：UCASE 函数把字段的值转换为大写
语法：SELECT UCASE(column_name) FROM table_name
类似行数：UPPER(),同样也能将字符串中所有字母字符转换为大写
案例：将负责人英文名从小写改成大写，并对比展示。
image.png
2.MID()函数:MID函数用于从文本字段中提取字符
语法：
SELECT MID(column_name,start[,length]) FROM table_name
SELECT MID(column_name FROM start[,FOR length]) FROM table_name
说明：column_name：必需项，要提取字符的字段；start：必需项，规定开始位置（起始值是 1）；length：可选项，要返回的字符数。如果省略，则返回开始位置后所有文本。
案例：对于地域隶属字段查找城市名称。
image.png
image.png
注：以上两种语法执行结果是相同的，mid函数的缺点是只能对某一字段中的字符串按照固定规则进行字符提取，无法灵活提取字符串相应位置的字符，
比如上例中地域隶属若出现黑龙江地区，则函数只能保留黑龙两个字；因此明日将会讲解如何灵活提取我们想要的字符，即mid函数与字符串查找函数灵活搭配。

20210208（MYSQL中字符串查找函数）

1.FIND_IN_SET()函数：
语法：FIND_IN_SET(str,strlist)
说明：字符串str在由N个子链组成的字符串列表strlist 中, 则返回值的范围在 1 到 N 之间；如果str不在strlist 或strlist 为空字符串，则返回值为 0 , 并且若任意一个参数为空，则返回值为 0 也可以说返回NULL。
例子：SELECT FIND_IN_SET(‘mysql’,’oracle,sql sever,mysql’) 位置；
image.png
注：上述例子的意思是查找第一个字符串mysql，第一次出现在字符串列表中的位置
2.FIELD()函数：
语法：FIELD（str,str1,str2,……）
说明：字段str按照字符串str1，str2，str3，str4的顺序返回1 到 N 之间的值。如果表中str字段值不存在于str1，str2，str3，str4中的记录，则返回值为 0。
例子:SELECT FIELD(‘mysql’,’sql’,’mysql’,’sql sever’,’oracle’) 位置
image.png
注：上述例子的意思是查找第一个字符串mysql，第一次出现在后续字符串中的位置
3.子字符串匹配函数：
函数项：
①LOCATE(str1,str)
②POSITION(str1 in str)
③INSTR(str,str1)
说明：以上三个函数都是查询str1字符串在str中的开始位置。
例子：
image.png
实例：优化昨日文本字段提取字符项
image.png
说明：按照昨日内容进行优化，首先要求是我们保留地域隶属中的城市名称，将“地区”这两个字符剔除；我们依旧使用MID函数，MID函数只能保留固定长度的字段，因此我们在MID的第三个参数中应用我们今日所学函数，因为要剔除“地区”，我们就要找到它的位置，使用LOCATE函数确定“地区”两个字在地域隶属中的位置为3，但因为该位置并不是目标位置，因此需要对该位置值减1。
举个例子，比如地域隶属中的一项为黑龙江地区，那MID( 地域隶属, 1,( LOCATE( ‘地区’, 地域隶属 )- 1 ))函数，首先LOCATE( ‘地区’, 地域隶属 )- 1 )的结果为3，MID（地域隶属,1,3）的结果值为黑龙江。

20210210（MYSQL中字符串其他函数）

1.LPAD()函数/RPAD()函数：
语法：LPAD(s1,len,s2)和RPAD(s1,len,s2)
说明：RPAD函数在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len；LPAD函数是在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
例子：
image.png
2.LEFT()函数/RIGHT()函数：
语法：LEFT(s,n)和RIGHT(s,n)
说明：LEFT函数返回字符串 s 的前 n 个字符；RIGHT函数返回字符串 s 的后 n 个字符
例子：
image.png
实例：统计定金明细表中，每日定金金额合计值。
image.png
3.SUBSTR()函数/SUBSTRING()函数：
语法：SUBSTR(s, start, length)和SUBSTRING(s, start, length)
说明：SUBSTR和SUBSTRING函数都是从字符串 s 的 start 位置截取长度为 length 的子字符串
例子：
image.png

20210218（MYSQL触发器）

触发器trigger：
含义：监视某种情况，并触发某种操作，它是保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器创建语法四要素：①监视地点(table)；②监视事件(insert/update/delete)；③触发时间trigger_time(after/before)；④触发事件trigger_event(insert/update/delete)
1.创建触发器SQL语句：
create trigger trigger_name before/after trigger_event on table_name for each row trigger_STMT ;
trigger_event :表示触发事件，触发器执行条件，包含insert、update和delete语句；
table_name:表示触发事件操作表的名字；
for each row:表示任何一条记录上的操作满足触发条件都会触发该触发器；
trigger_STMT:表示激活触发器后被执行的语句，即（BEGIN SQL语句 END）。
例子：
DELIMITER CREATE TRIGGER demo
BEFORE DELETE ON users FOR EACH ROW BEGIN INSERT INTO logs VALUES(NOW()); ENDDELIMITER ;
案例说明：创建触发器触发器名字为demo，触发事件为“删除之前”，触发事件操作的表名字为users表，触发器执行语句为“时间插入日志表当中”；所以含义就是，创建一个触发器demo，当对users表每次进行删除操作的时候，则先在logs表中执行插入语句，即将当前时间插入日志表当中，然后再执行删除操作。
注：其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开，一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突
为解决此问题可用DELIMITER，如：DELIMITER ，可以将结束符号变成，当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;
实例：
image.png
2.查看触发器SQL语句：show triggers；
image.png
3.删除触发器SQL语句： DROP TRIGGER TRIGGER_NAME;
image.png

20210219（MYSQL函数补充）

1.INSERT函数：
语法：INSERT(str,pos,len,newstr)
说明：str：指定字符串；pos：开始被替换的位置； len：被替换的字符串长度；newstr：新的字符串
总结：替换掉 str 范围为 [ pos, pos+len ] 的字符串，如果 pos > str 长度以内，则返回 str 不会被替换；如果 len > str 剩余字符串的长度，则将 str 剩下所有字符都替换成 newstr。
例子：
image.png
2.REPEAT函数：
语法：REPEAT(str,count)
说明：返回一个由重复计数次数的字符串str组成的字符串。 如果count小于1，则返回一个空字符串。 如果str或count为NULL，则返回NULL。
例子：
image.png
3.REPLACE函数：
语法：REPLACE(str，a，b)
说明：使用字符串 b替换字符串 str 中所有出现的字符串a
例子：
image.png
4.STRCMP函数：
语法：STRCMP(str1，str2)
说明：比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1。
例子：
image.png
5.CEIL和FLOOR函数：
语法：CEIL(x)和FLOOR(x)
说明：CEIL函数返回最小整数值，但不能小于X,该函数与CEILING函数同理;FLOOR函数返回小于等于该值的最大整数。
例子：
image.png
image.png

20210220（MYSQL存储过程1）

存储过程
定义：存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象；存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行；存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
存储过程特征：
优点：
①存储过程可封装，并隐藏复杂的商业逻辑。
②存储过程可以回传值，并可以接受参数。
③存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
④存储过程可以用在数据检验，强制实行商业逻辑等。
缺点：
①存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
②存储过程的性能调校与撰写，受限于各种数据库系统。

1.存储过程的创建和调用
语法：CREATE PROCEDURE 过程名称([proc_parameter[,…]])
[characteristic …] routine_body
说明：
①proc_parameter ：表示 [ IN | OUT | INOUT ] param_name type
IN 输入参数：表示调用者向过程传入值
OUT 输出参数：表示过程向调用者传出值
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值
type ：表示任何有效的MySQL数据类型
②characteristic :表示 LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER } | COMMENT ‘string’
LANGUAGE SQL：说明 routine_body部分是由SQL语言的语句组成，数据库系统默认值。
NOT DETERMINISTIC ：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC。
CONTAINS SQL ：表示子程序不包含读或写数据的语句 NO SQL表示子程序不包含SQL语句 READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的
MODIFIES SQL DATA表示子程序包含写数据的语句
SQL SQCURITY{DEFINER|INVOKER}:指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。默认为DEFINER。
COMMENT’string’：存储过程注释信息。
③routine_body：为存储过程体，BEGIN…END标志存储过程体的开始和结束。存储过程体可以是SELECT、UPDATE、INSERT、DELETE、CREATE TABLE、等SQL语句，也可以嵌入调用其他存储过程的代码，还可以是其他代码。
存储过程的参数举例：
1）无参数存储过程：
image.png
2）IN参数存储过程：
image.png
注：后续会继续对out，inout进行举例说明，同时会对存储过程中的控制语句（变量作用域、条件语句、循环语句、ITERATE迭代）进行撰写。

20210223（MYSQL存储过程2）

注意：
in类型：内部运算变化不影响外部；
out类型：内部运算变化影响外部变化并且传参到储存过程时默认初始化参数为null；

3）out参数存储过程：
image.pngimage.png
注：因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
image.png
注：调用了out_param存储过程，输出参数，改变了p_out变量的值。
解释：第一二张图首先将1赋值给变量p_out，然后调用存储过程，由于该存储过程参数为out，因此不接收输入参数，所以第一条语句select p_out结果为null，
第二条语句将2赋值给变量p_out，然后再次执行select p_out结果即为2；第三张图中select @p_out语句的结果为2，是因为内部运算变化影响外部变化；内部变化值为2，因此结果输出为2。
4）inout参数存储过程：
image.pngimage.png
image.png
注：调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
解释：第一二张图首先将1赋值给变量p_out，然后调用存储过程，由于该存储过程参数为inout，因此接收输入参数，所以第一条语句select p_out结果为1，
第二条语句将2赋值给变量p_out，然后再次执行select p_out结果即为2；第三张图中select @p_out语句的结果为2，是因为内部运算变化影响外部变化；内部变化值为2，因此结果输出为2。**

20210224（MYSQL存储过程3）

2.存储过程之变量
（1）变量定义：
sql代码：DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
说明：mysql中的DECLARE语句是在复合语句中声明变量的指令；datatype为MySQL的数据类型，如:int, float, date, varchar(length)
（2）变量赋值：
sql代码：
例子：
image.png
注：首先创建一个存储过程p8，存储过程语句首先定义两个整型变量a和b，将5分别赋值变量a和b；然后将a值插入到表t当中同时执行特定条件下的查询语句。
（3）用户变量：用户变量名一般以@开头
①在MySQL客户端中使用用户变量：
image.png
②在存储过程中使用用户变量：**
image.png
③在存储过程间传递全局范围的用户变量：
image.png

20210225（MYSQL存储过程4）

（1）变量作用域：内部的变量在其作用域范围内享有更高的优先权，当执行到end变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储
过程外再也不能找到这个声明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。
image.pngimage.png
说明：由于默认值为inner的声明语句在其作用域范围优先权更高，因此第一条select语句执行结果为inner；当执行到end变量时，内部变量消失即声明变量x1为inner消失，此时执行第二条select语句执行结果则为外部的声明变量，即x1为outer。
（2）条件语句
①if-then-else语句：
image.pngimage.png
说明：首先将-1赋值给parameter变量，声明整型变量var，然后令var变量值为0，进入判断条件，刚好满足条件，即在t表中插入数值17；继续向下执行，再次进入判断语句，此时不满足该条件，因此执行else后的语句，更新t表将t表中s1字段的值加2，故结果为19。
②case语句：
image.pngimage.png
说明：0赋值给parameter，var值即为1，执行case……when……then语句，由于var为1因此在t表中插入值为18。
（3）循环语句
①while ···· end while：
while 条件 do
—循环体
endwhile
image.pngimage.png
说明：while循环在每次迭代开始时检查表达式。 如果表达式为TRUE，MySQL将执行while和end while之间的语句，直到表达式为FALSE。while循环称为预先测试条件循环，因为它总是在执行前检查语句的表达式。
②repeat···· end repeat：
repeat —循环体 until 循环条件 end repeat;
image.pngimage.png
说明：上述sql首先被mysql执行，完事mysql会评估求值表达式(expression)，如果表达式(expression)的计算结果为FALSE，则mysql将重复执行该语句，直到该表达式计算结果为TRUE。因为REPEAT循环语句在执行语句后检查表达式(expression)，因此REPEAT循环语句也称为测试后循环。
③loop ·····end loop:

image.pngimage.png
说明：一方面，loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环；另一方面，LOOP语句之前放置一个loop_label循环标签。

20210301（MYSQL的几种排序方式）

（1）单列排序：
sql代码：SELECT FROM table_name ORDER BY column_name desc/asc;
例子：
image.png
（2）多列排序：
sql代码：SELECT FROM table_name ORDER BY column_name1, column_name2 DESC;
例子：
image.png
（3）自定义函数排序：
sql代码：SELECT FROM table_name ORDER BY FIELD(column_name1, 1, 2, 3, 4)DESC/ASC ,column_name2 DESC/ASC;
例子：
image.png
注：“事业部”字段先按照1，2，3，4的顺序进行排序，但由于事业部并未在1,2,3,4中，因此结果值为0,；所以接下来就按照“em”字段降序排序。
（4）其他条件排序：
sql代码：SELECT FROM table_name ORDER BY column_name1 < NOW(), IF ( column_name1 < NOW(), 0, column_name1 ), column_name1 DESC;
例子：
image.pngimage.png

注：如上图所示，第二张图的日期顺序即为最终排序，即order by 后的语句含义先按大于等于0115升序，再按小于0115降序

20210304（MYSQL之innodb存储引擎优化）

1.MYISAM存储引擎： 基于传统的 ISAM 类型，它是存储记录和文件的标准方法。与其他存储引擎比较， MyISAM 具有
检查和修复表格的大多数工具。 MyISAM 表格可以被压缩 , 而且它们支持全文搜索。它们不是事务安全的，而且也不支持外键。
2.INNODB存储引擎：默认类型，支持 ACID 特性，还支持外键。 InnoDB 表格速度很快。如果需要一个事务安全的存储引擎或者是需要大量的
INSERT 或 UPDATE ，应该使用 InnoDB 表。
3.MyISAM和InnoDB
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename engine=innodb）。
4.应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

（1）innodb_buffer_pool_size :（重要）如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。
调优参考计算方法： val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
如果val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%大小作为innodb_buffer_pool_size大小 ；
如果val < 95% 则考虑减小 innodb_buffer_pool_size， 建议innodb_buffer_pool_size设置为：Innodb_buffer_pool_pages_data Innodb_page_size 1.05 / (102410241024)
（2）innodb_log_file_size ：在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。因此一般设置为 64-512MB，跟据服务器大小而异。
（3）innodb_flush_logs_at_trx_commit :（重要）默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
（4）innodb_flush_method： 某些版本GNU/Linux 系统，使用fsync() 或者相关方法进行刷盘时，速度会非常慢。这时，如果影响到数据库性能，那么可以通过设置innodb_flush_method = O_DSYNC来变更刷盘策略。
（5）大数据载入优化：
①导入数据时，关闭autocommit 模式，避免每次行插入导致的日志刷盘。在执行开始及结束使用SET autocommit 及 COMMIT 语句：
SET autocommit=0;
…SQLimport statements …
COMMIT;
②如果在二级索引键上有 UNIQUE 限制，可以在载入时暂时关闭比检查：
SET unique_checks=0; …SQLimport statements … SET unique_checks=1;
说明：对于较大的表，此操作可以节省大量的磁盘I/O，因为InnoDB 可以使用它的 change buffer（change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果）来批量写二级索引记录。确保数据不包含重复键。
③如果表键包含FOREIGN KEY 限制。可以再导入期间关闭此限制：
SET foreign_key_checks=0; …SQLimport statements … SET foreign_key_checks=1;
实例测试：以ods_sales_manage_fivedimension_leg_ss_d为例，在新表中插入总行数为197155行的数据，优化跑数时间。
①myisam:
image.png
②innodb：（默认，未进行任何优化）
image.png
③innodb（修改innodb_buffer_pool_size进行调优）：
image.pngimage.png
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total 100%=506/512100%=98.8%>95%,故建议使用物理内存的75%
image.pngimage.png
说明：本次测试将该参数由原来的8M修改为100M，经过测试，innodb时间由20.401s——>7.93s，时间优化为原来的1/3左右，而与myisam相比约为innodb时间的2倍左右，因此待明日进行进一步优化！

20210305（MYSQL的事务管理）

1.事务管理：管理必须成批执行的SQL操作，使之完全执行或完全不执行。
管理对象：insert，update，delete
commit：将SQL语句的执行结果写入数据库表中
rollback：回退撤销SQL语句执行结果
savepoint：时间点的备份，可以对其发布回退（一定要释放，否则占内存）
注：CREATE/DROP是不可以进行回退的
例子：比如有三条语句，insert……update……delete……，我在delete前设置savepoint，当我执行了三条语句之后使用rollback to savepoint，则回到保留点，同时回退delete前。
具体实例：
（1）在mysql后台执行：
假如在某个表中插入一条语句，当我们插入之后使用rollback，再重新查一下，发现插入数据仍然存在没有回退，这是因为mysql中自动提交commit的功能，因此我们必须把自动提交机制关闭，即set autocommit=0（关闭）
注：有commit则rollback就会失效
（2）在navicat中执行：
不关闭自动提交功能，通常使用begin；方法
创建一个事务管理：begin；（不会触发自动提交机制），然后对数据库进行增删改，可以正常使用rollback。
image.png
student表： t_teacher表：
image.png image.png
image.png
注：回退到sp1点，因此在sp1点之后执行语句全部进行回退
image.png
注：由于已经回退到sp1点，此时还没有创建sp2，所以释放不了
image.png

SQL实战篇

20210308（实战练习1）

1.题目描述：有一个员工employees表简况如下所示（提供建表语句）

  1. CREATE TABLE `employees` (
  2. `emp_no` int(11) NOT NULL, 
  3. `birth_date` date NOT NULL,
  4. `first_name` varchar(14) NOT NULL,
  5. `last_name` varchar(16) NOT NULL,
  6. `gender` char(1) NOT NULL,
  7. `hire_date` date NOT NULL,
  8. PRIMARY KEY (`emp_no`));
  9. INSERT INTO employees VALUES(1001,'1953-09-02','Georgi','Facello','M','1986-06-26')
  10. INSERT INTO employees VALUES(1002,'1964-06-02','Bezalel','Simmel','F','1985-11-21')
  11. INSERT INTO employees VALUES(1003,'1959-12-03','Parto','Bamford','M','1986-08-28')
  12. INSERT INTO employees VALUES(1004,'1954-05-01','Chirstian','Koblick','M','1986-12-01')
  13. SELECT * FROM employees;

image.png
问题1.1：请根据此表查找employees里最晚入职员工的所有信息
问题1.2：请你查找employees里入职员工时间排名倒数第三的员工所有信息。(02)
2.题目描述：有一个全部员工的薪水表salaries简况和各个部门的领导表dept_manager简况如下（提供建表语句）**

  1. CREATE TABLE `salaries` (
  2. `emp_no` int(11) NOT NULL,
  3. `salary` int(11) NOT NULL,
  4. `from_date` date NOT NULL,
  5. `to_date` date NOT NULL,
  6. PRIMARY KEY (`emp_no`,`from_date`));
  7. INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01')
  8. INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01')
  9. INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01')

image.png

  1. CREATE TABLE `dept_manager` (
  2. `dept_no` char(4) NOT NULL,
  3. `emp_no` int(11) NOT NULL,
  4. `to_date` date NOT NULL,
  5. PRIMARY KEY (`emp_no`,`dept_no`));
  6. INSERT INTO dept_manager VALUES('d001',10001,'9999-01-01')
  7. INSERT INTO dept_manager VALUES('d002',10003,'9999-01-01')

image.png
问题2.1：请你查找各个部门领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列。(03)
问题2.2：请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次，并按照逆序显示。(08)

实例练习1答案：
问题1.1：请根据此表查找employees里最晚入职员工的所有信息。(01)
image.png
问题1.2：请你查找employees里入职员工时间排名倒数第三的员工所有信息。(02)
image.png
问题2.1：请你查找各个部门领导薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列。(03)
image.png
注：左连接查询，以薪水信息表为主表，勿忘记on条件和题目规定的最后一列dept_no
问题2.2：请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示。(08)
image.png

20210309（实战练习2—插入与创建）

1.题目描述：创建一个actor表，包含如下列信息
image.png
2.题目描述：请你对于表actor批量插入如下数据(不能有2条insert语句)

  1. 首先执行以下建表语句：
  2. drop table if exists actor;
  3. CREATE TABLE actor (
  4.    actor_id  smallint(5)  NOT NULL PRIMARY KEY,
  5.    first_name  varchar(45) NOT NULL,
  6.    last_name  varchar(45) NOT NULL,
  7.    last_update  DATETIME NOT NULL)

image.png
3.题目描述：对于表actor插入如下数据,如果数据已经存在，请忽略(不能使用replace操作)

  1. 首先执行以下建表语句：
  2. drop table if exists actor;
  3. CREATE TABLE actor (
  4.    actor_id  smallint(5)  NOT NULL PRIMARY KEY,
  5.    first_name  varchar(45) NOT NULL,
  6.    last_name  varchar(45) NOT NULL,
  7.    last_update  DATETIME NOT NULL);
  8. insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

image.png
4.题目描述：创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表
image.png

注：答案见下期