MySQL其四，各种函数，以及模拟了炸裂函数创建用户等操作

一、MySQL中的函数

1、IFNULL

2、IF

3、case （难点）

4、exists(难) --存在的意思

二、常见的函数

1、字符串函数

2、数学函数

3、日期函数（使用频率不是很高）

4、其他函数

5、关于字符集的问题

6、mysql炸裂函数explode（mysql8.0 才支持的函数）

三、TCL（王牌彩电）

1、创建用户

2、赋予权限

3、如何修改mysql允许远程登录

1）该表法

2）通过TCL语句修改

4、在企业中mysql如何保证数据安全？

四、小练习

一、MySQL中的函数

什么是函数？一个功能就是一个函数，一个函数就代表了一个功能。

1、IFNULL

当前⾯的值是null的时候，使⽤后⾯的默认值

什么是函数：sql中带括号的就是函数

（）其实就是用来传递参数的，有些函数有参数，有些没有，有些有一个参数，有些有多个。

ifnull 参数就是两个，第一个参数需要判断的字段，第二个是默认值

返回值：一个函数运行完之后的结果就叫做返回值

select ifnull(null,100); --> 100
select ifnull(10,20);  --> 10
select ifnull(comm,0) from emp; --> ifnull 在此时会执行很多次，就会有很多的返回值

2、IF

前⾯的条件如果成⽴，取值第⼀个，否则取值第⼆个。

if 函数有三个参数，第一个参数 boolean(布尔类型true false) , 第二个参数和第三个参数都是值
select if(10>20,1,0);

mysql> select 800 > null;
+------------+
| 800 > null |
+------------+
| NULL       |
+------------+
1 row in set (0.06 sec)

mysql> select if(sal > ifnull(comm,0) ,sal,comm) from emp;
+------------------------------------+
| if(sal > ifnull(comm,0) ,sal,comm) |
+------------------------------------+
| 800.00                             |
| 1600.00                            |
| 1250.00                            |
| 2975.00                            |
| 1400.00                            |
| 2850.00                            |
| 2450.00                            |
| 3000.00                            |
| 5000.00                            |
| 1500.00                            |
| 1100.00                            |
| 950.00                             |
| 3000.00                            |
| 1300.00                            |
| NULL                               |
+------------------------------------+
15 rows in set (0.09 sec)

3、case （难点）

造数据：

-- 建表
create table `sc` (
`sname` varchar(20),
 `subject` varchar(20),
 `score` int
);
insert into `sc` values 
	('张小三', '语文', 78),
	('张小三', '数学', 77),
	('张小三', '英语', 90),
	('张小三', '历史', 89),
	('张小三', '体育', 80),
	('李小四', '数学', 90),
	('李小四', '英语', 80),
	('李小四', '体育', 88),
	('李小四', '政治', 88),
	('李小四', '历史', 78),
	('王小五', '语文', 90),
	('王小五', '英语', 80),
	('王小五', '政治', 89),
	('王小五', '体育', 90);

mysql> select * from sc;
+--------+---------+-------+
| sname  | subject | score |
+--------+---------+-------+
| 张小三 | 语文    |    78 |
| 张小三 | 数学    |    77 |
| 张小三 | 英语    |    90 |
| 张小三 | 历史    |    89 |
| 张小三 | 体育    |    80 |
| 李小四 | 数学    |    90 |
| 李小四 | 英语    |    80 |
| 李小四 | 体育    |    88 |
| 李小四 | 政治    |    88 |
| 李小四 | 历史    |    78 |
| 王小五 | 语文    |    90 |
| 王小五 | 英语    |    80 |
| 王小五 | 政治    |    89 |
| 王小五 | 体育    |    90 |
+--------+---------+-------+

第一个需求：行转列

使用if 来编写这个需求：

select sname,
  if(subject='语文',score,0) 语文,
  if(subject='数学',score,0) 数学,
if(subject='英语',score,0) 英语,
if(subject='历史',score,0) 历史,
if(subject='政治',score,0) 政治,
if(subject='体育',score,0) 体育
 from sc;
统计的结果：
+--------+------+------+------+------+------+------+
| sname  | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 |
+--------+------+------+------+------+------+------+
| 张小三 |   78 |    0 |    0 |    0 |    0 |    0 |
| 张小三 |    0 |   77 |    0 |    0 |    0 |    0 |
| 张小三 |    0 |    0 |   90 |    0 |    0 |    0 |
| 张小三 |    0 |    0 |    0 |   89 |    0 |    0 |
| 张小三 |    0 |    0 |    0 |    0 |    0 |   80 |
| 李小四 |    0 |   90 |    0 |    0 |    0 |    0 |
| 李小四 |    0 |    0 |   80 |    0 |    0 |    0 |
| 李小四 |    0 |    0 |    0 |    0 |    0 |   88 |
| 李小四 |    0 |    0 |    0 |    0 |   88 |    0 |
| 李小四 |    0 |    0 |    0 |   78 |    0 |    0 |
| 王小五 |   90 |    0 |    0 |    0 |    0 |    0 |
| 王小五 |    0 |    0 |   80 |    0 |    0 |    0 |
| 王小五 |    0 |    0 |    0 |    0 |   89 |    0 |
| 王小五 |    0 |    0 |    0 |    0 |    0 |   90 |
+--------+------+------+------+------+------+------+

select sname,
sum(if(subject='语文',score,0)) 语文,
sum(if(subject='数学',score,0)) 数学,
sum(if(subject='英语',score,0)) 英语,
sum(if(subject='历史',score,0)) 历史,
sum(if(subject='政治',score,0)) 政治,
sum(if(subject='体育',score,0)) 体育
 from sc group by sname;

+--------+------+------+------+------+------+------+
| sname  | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 |
+--------+------+------+------+------+------+------+
| 张小三 | 78   | 77   | 90   | 89   | 0    | 80   |
| 李小四 | 0    | 90   | 80   | 78   | 88   | 88   |
| 王小五 | 90   | 0    | 80   | 0    | 89   | 90   |
+--------+------+------+------+------+------+------+

notepad++ 也支持 alt + 鼠标选择多行进行操作

学习一下case的用法：

case  when ... then  ...  else ... end 
分析：
case 开头
end 结尾
中间是 类似于  if   else 

需求：将分数分为优（80分以上）、良（60分到80分）、不及格（60分以下的）
select *,
  if(score >80,'优',if(score <60,'不及格','良')) 等级 
from sc;

另一种写法：
select *,
  case 
    when score >80 then '优'
    when score <60 then '不及格'
    else '良'
  end  等级
from sc;

使用case语句重新编写第一个需求：

select sname,
 sum(case  when subject='语文' then score else 0 end)  '语文',
 sum(case  when subject='数学' then score else 0 end) '数学',
 sum(case  when subject='英语' then score else 0 end) '英语',
 sum(case  when subject='历史' then score else 0 end) '历史',
 sum(case  when subject='政治' then score else 0 end) '政治',
 sum(case  when subject='体育' then score else 0 end) '体育'
from sc group by sname;

+--------+------+------+------+------+------+------+
| sname  | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 |
+--------+------+------+------+------+------+------+
| 张小三 | 78   | 77   | 90   | 89   | 0    | 80   |
| 李小四 | 0    | 90   | 80   | 78   | 88   | 88   |
| 王小五 | 90   | 0    | 80   | 0    | 89   | 90   |
+--------+------+------+------+------+------+------+

需求：将学科的名称翻译为英⽂的

mysql> select * from sc;
+--------+---------+-------+
| sname  | subject | score |
+--------+---------+-------+
| 张小三 | 语文    |    78 |
| 张小三 | 数学    |    77 |
| 张小三 | 英语    |    90 |
| 张小三 | 历史    |    89 |
| 张小三 | 体育    |    80 |
| 李小四 | 数学    |    90 |
| 李小四 | 英语    |    80 |
| 李小四 | 体育    |    88 |
| 李小四 | 政治    |    88 |
| 李小四 | 历史    |    78 |
| 王小五 | 语文    |    90 |
| 王小五 | 英语    |    80 |
| 王小五 | 政治    |    89 |
| 王小五 | 体育    |    90 |
+--------+---------+-------+

此时的需求是添加一列，这一列就是英文即可，一列 == 1个case
以下写法有点类似于if:
select *,
  case 
    when subject ='语文' then 'chinese'
    when subject ='数学' then 'Math'
    when subject ='英语' then 'english'
    when subject ='历史' then 'history'
    when subject ='政治' then 'politics'
    when subject ='体育' then 'sport'
  end 英文学科名称
from sc;

答案：
+--------+---------+-------+--------------+
| sname  | subject | score | 英文学科名称 |
+--------+---------+-------+--------------+
| 张小三 | 语文    |    78 | chinese      |
| 张小三 | 数学    |    77 | Math         |
| 张小三 | 英语    |    90 | english      |
| 张小三 | 历史    |    89 | history      |
| 张小三 | 体育    |    80 | sport        |
| 李小四 | 数学    |    90 | Math         |
| 李小四 | 英语    |    80 | english      |
| 李小四 | 体育    |    88 | sport        |
| 李小四 | 政治    |    88 | politics     |
| 李小四 | 历史    |    78 | history      |
| 王小五 | 语文    |    90 | chinese      |
| 王小五 | 英语    |    80 | english      |
| 王小五 | 政治    |    89 | politics     |
| 王小五 | 体育    |    90 | sport        |
+--------+---------+-------+--------------+

case的另一种写法： 类似于switch的写法
select *,
 case subject
    when '语文' then 'chinese'
    when '数学' then 'Math'
    when '英语' then 'english'
    when '历史' then 'history'
    when '政治' then 'politics'
    when '体育' then 'sport'
  end 英文学科名称
from sc;

需求：

select sname 姓名,sum(score) 总成绩 from sc group by sname;
| 姓名   | 总成绩 |
+--------+--------+
| 张小三 | 414    |
| 李小四 | 424    |
| 王小五 | 349    |
+--------+--------+

if版本的：
select sname 姓名,
sum(if(subject='语文',score,0)) 语文,
sum(if(subject='数学',score,0)) 数学,
sum(if(subject='英语',score,0)) 英语,
sum(if(subject='历史',score,0)) 历史,
sum(if(subject='政治',score,0)) 政治,
sum(if(subject='体育',score,0)) 体育,
sum(score) 总成绩
 from sc group by sname;

case 版本：
select sname 姓名,
 sum(case  when subject='语文' then score else 0 end)  '语文',
 sum(case  when subject='数学' then score else 0 end) '数学',
 sum(case  when subject='英语' then score else 0 end) '英语',
 sum(case  when subject='历史' then score else 0 end) '历史',
 sum(case  when subject='政治' then score else 0 end) '政治',
 sum(case  when subject='体育' then score else 0 end) '体育',
 sum(score) 总成绩
from sc group by sname;

+--------+------+------+------+------+------+------+--------+
| 姓名   | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 | 总成绩 |
+--------+------+------+------+------+------+------+--------+
| 张小三 | 78   | 77   | 90   | 89   | 0    | 80   | 414    |
| 李小四 | 0    | 90   | 80   | 78   | 88   | 88   | 424    |
| 王小五 | 90   | 0    | 80   | 0    | 89   | 90   | 349    |
+--------+------+------+------+------+------+------+--------+

需求：

分析：可以通过两种不同的集合合并在一起


select subject,sum(score) 总成绩 from sc group by subject;

+---------+--------+
| subject | 总成绩 |
+---------+--------+
| 语文    | 168    |
| 数学    | 167    |
| 英语    | 250    |
| 历史    | 167    |
| 体育    | 258    |
| 政治    | 177    |
+---------+--------+

select 
 '总成绩' as '姓名',
 sum(case when subject='语文' then score else 0 end) '语文',
 sum(case when subject='数学' then score else 0 end) '数学',
 sum(case when subject='英语' then score else 0 end) '英语',
 sum(case when subject='历史' then score else 0 end) '历史',
 sum(case when subject='政治' then score else 0 end) '政治',
 sum(case when subject='体育' then score else 0 end) '体育',
 sum(score) '总成绩'
from sc;
结果集：底座
+--------+------+------+------+------+------+------+--------+
| 姓名   | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 | 总成绩 |
+--------+------+------+------+------+------+------+--------+
| 总成绩 | 168  | 167  | 250  | 167  | 177  | 258  | 1187   |
+--------+------+------+------+------+------+------+--------+

合并两个结果集：
select sname 姓名,
 sum(case  when subject='语文' then score else 0 end)  '语文',
 sum(case  when subject='数学' then score else 0 end) '数学',
 sum(case  when subject='英语' then score else 0 end) '英语',
 sum(case  when subject='历史' then score else 0 end) '历史',
 sum(case  when subject='政治' then score else 0 end) '政治',
 sum(case  when subject='体育' then score else 0 end) '体育',
 sum(score) 总成绩
from sc group by sname
union all
select 
 '总成绩' as '姓名',
 sum(case when subject='语文' then score else 0 end) '语文',
 sum(case when subject='数学' then score else 0 end) '数学',
 sum(case when subject='英语' then score else 0 end) '英语',
 sum(case when subject='历史' then score else 0 end) '历史',
 sum(case when subject='政治' then score else 0 end) '政治',
 sum(case when subject='体育' then score else 0 end) '体育',
 sum(score) '总成绩'
from sc;

+--------+------+------+------+------+------+------+--------+
| 姓名   | 语文 | 数学 | 英语 | 历史 | 政治 | 体育 | 总成绩 |
+--------+------+------+------+------+------+------+--------+
| 张小三 | 78   | 77   | 90   | 89   | 0    | 80   | 414    |
| 李小四 | 0    | 90   | 80   | 78   | 88   | 88   | 424    |
| 王小五 | 90   | 0    | 80   | 0    | 89   | 90   | 349    |
| 总成绩 | 168  | 167  | 250  | 167  | 177  | 258  | 1187   |
+--------+------+------+------+------+------+------+--------+

if版本的写法：
-- if实现版本
select `sname` as '姓名',
    sum(if(`subject` = '语文', `score`, 0)) as '语文',
    sum(if(`subject` = '数学', `score`, 0)) as '数学',
    sum(if(`subject` = '英语', `score`, 0)) as '英语',
    sum(if(`subject` = '历史', `score`, 0)) as '历史',
    sum(if(`subject` = '政治', `score`, 0)) as '政治',
    sum(if(`subject` = '体育', `score`, 0)) as '体育',
    sum(`score`) as '总成绩'
from `sc` group by `sname`
union
select '总成绩' as '姓名',
    sum(if(`subject` = '语文', `score`, 0)) as '语文',
    sum(if(`subject` = '数学', `score`, 0)) as '数学',
    sum(if(`subject` = '英语', `score`, 0)) as '英语',
    sum(if(`subject` = '历史', `score`, 0)) as '历史',
    sum(if(`subject` = '政治', `score`, 0)) as '政治',
    sum(if(`subject` = '体育', `score`, 0)) as '体育',
    sum(`score`) as '总成绩'
from `sc`;

解释一下：
select 'chufaxingjin' 姓名 from sc ;

4、exists(难) --存在的意思

顾名思义，就是判断数据是否存在的！exists的作用为判断一个表中的数据，是否在另外的一张表中能够查询到与之对应的数据

效率要比连接查询和子查询高！

案例1: 查询有员工的部门
select * from dept where deptno in (select distinct deptno from emp);

+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
+--------+------------+----------+

语法：
select xxx from  表  where [not] exists (集合)
使用exists 编写sql:
select * from dept where exists(
   select * from emp where emp.deptno = dept.deptno
);
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
+--------+------------+----------+

select * from dept where exists(
   select * from emp where emp.deptno = dept.deptno
);

查询一个数据是否存在，存在里面的结果集中，如果存在，显示出来，不存在不显示。
里面查询的是什么结果集不重要，主要存在即可。

案例2: 查询没有员工的部门
select * from dept where not exists(
   select * from emp where emp.deptno = dept.deptno
);
+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.10 sec)

-- 案例3: 查询有部门的员工
select * from emp where exists
 (
   select 1 from dept where emp.deptno = dept.deptno
 );

-- 案例4: 查询有下属的员工信息
select * from emp where 
  exists(
    select 1 from emp e2 where e2.mgr = emp.empno
  );
-- 查询有领导的员工信息
select * from emp where 
  exists(
    select 1 from emp e2 where e2.empno = emp.mgr
  );

exists : 都可以使用我们以前的sql语句替换，但是使用exists 执行效率高。

二、常见的函数

1、字符串函数

字符串是万能的！！！不管在任何学科中！！！

1、获取字符串⻓度
select char_length('Hello');
2、变⼤写
select upper('Hello');
select UCASE('Hello');
3、变⼩写
select lower('Hello');
select lcase('Hello');
4、空⽩字符串切割
select trim(' Hello '); // 左右两边的空⽩字符全部切掉
select ltrim(' Hello ');// 只切除左边的空⽩字符
select rtrim(' Hello ');// 只切右边
5、⽐较两个字符串是否相等，⽐较的肯定是内容
select strcmp('hello','hello'); // 如果相等返回0，不等于返回 1 或者 -1
select strcmp('hello2','hello');// 1
6、截取⼀段字符串
select substr('hello',2,3); // 2 代表的是第⼆个字符的位置，3代表的是截取的⻓度
7、将字符串进⾏反转
select reverse('hello');
8、替换
select replace('hello','l','a');
9、字符串的拼接，有两种
1）使⽤某个拼接符进⾏拼接
select concat_ws(':','hello','world');
2) 不指定拼接符
select concat('hello','world');
10、数据进⾏格式化处理 最后⼀位进⾏四舍五⼊的处理
select format(3.1415926,2);
select format(3.145926,2);

拓展：

-- 字符串相关的函数
-- 返回ASCII码
select ASCII('a');
-- 返回字符串长度
select char_length('abcedf');
-- 拼接多个字符串
select concat('abc','-','bcd');
-- 将后面集合中的元素按照什么符号进行拼接
select concat_ws('~','a','b','c','e');
-- 第一个字符在后面集合的第几个位置
select FIELD('c','a','b','c','e');
select find_in_set('c','a,b,c');
-- 格式化数字 "#,###.##" 形式，并且四舍五入
select format(123123.1362,2);
-- 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
SELECT INSERT("google.com", 1, 5, "runoob");
-- 	从字符串 s 中获取 s1 的开始位置
SELECT LOCATE('st','myteststring');  -- 5
SELECT POSITION('st' in 'myteststring'); -- 5
-- 变小写
 select lcase("Abc");
select lower("Abc");
-- 变大写
select upper("abc");
-- 返回字符串的前几个字符
select left("abcedf",3);
-- 想截取哪一段截取哪一段
select substr("abcedf",1,3);
SELECT MID("RUNOOB", 2, 3) ;
-- 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
select lpad("1",5,"0");
select lpad("10000",5,"0");
select rpad("1",5,"0");
-- 去重空格
select ltrim(" abceff  ");
select rtrim(" abceff  ");
select trim(" abceff  ");
-- 返回第2个* 左边的所有数据
SELECT SUBSTRING_INDEX('a*b*c*d','*',2);
-- 返回空格键
select space(10);

select "str" != "str";
select strcmp("a","b");
select strcmp("aaa","abc");

-- 跟 left 相反
select right('abcedf',2);
-- 字符串反转
select reverse("abc");
-- 将字符串 s 重复 n 次
select repeat("hello",5);
-- 替换字符串
select replace("hello",'l','a');

2、数学函数

-- 绝对值
select abs(-1);
-- 天花板
select ceil(1.99);
-- 地板砖
select FLOOR(1.99);
-- 除以
select 10 div 5;
select 10/5;
-- 求最⼩值和最⼤值
select least(10,20,4,50,18);
select greatest(10,20,4,50,18) as 最⼤值;
-- 求余数
select 5%2;
select MOD(5,2);
-- 求次⽅
select POW(2,3);
-- 开根号
select sqrt(16);
-- PI
select PI();
-- 获取0到1之间的随机数，不包含1
select rand();
-- 随机获取 [3,10)
select floor (rand() * 7 + 3 );
-- 四舍五⼊
select round(1.56); -- 2
select round(1.22); -- 1
-- 保留⼩数点后⼏位，不会四舍五⼊
select TRUNCATE(1.35675,3);

3、日期函数（使用频率不是很高）

--获取当前时间
select now() ;  -- 百脑汇  Buy Now!

-- 查询当前时间 年⽉⽇的形式
select CURRENT_DATE();
-- 时分秒的形式
select CURRENT_TIME();
-- 年⽉⽇时分秒
select CURRENT_TIMESTAMP();

mysql> select adddate('2023-4-13',interval -1 day);
+--------------------------------------+
| adddate('2023-4-13',interval -1 day) |
+--------------------------------------+
| 2023-04-12                           |
+--------------------------------------+
1 row in set (0.09 sec)

mysql> select adddate('2023-4-13',interval 1 day);
+-------------------------------------+
| adddate('2023-4-13',interval 1 day) |
+-------------------------------------+
| 2023-04-14                          |
+-------------------------------------+
1 row in set (0.09 sec)

mysql> select addtime(now(),'01:00:00');
+---------------------------+
| addtime(now(),'01:00:00') |
+---------------------------+
| 2023-04-13 16:15:43       |
+---------------------------+
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Thursday       |
+----------------+
1 row in set (0.08 sec)

-- 某个⽇期多少天以后
select ADDDATE('2022-07-21',INTERVAL 10 DAY);
select ADDDATE('2022-07-21',10);
-- 某个时间多少⼩时分钟秒之后
select ADDTIME('2022-07-21 09:57:00','2:00:00');
-- 查询当前时间 年⽉⽇的形式
select CURRENT_DATE();
-- 时分秒的形式
select CURRENT_TIME();
-- 年⽉⽇时分秒
select CURRENT_TIMESTAMP();
-- 获取两个时间的差值
select abs(DATEDIFF('2022-07-11','2022-07-21'));
-- 将数据格式化为其他的样式 %r 可以展示上午还是下午
select DATE_FORMAT('2022-07-11','%y年%m⽉%d⽇');
-- 获取天
select day('2022-07-11');
select YEAR('2022-07-11');
-- 获取给定的⽉份
select MONTH('2022-07-11');
-- 该⽇期是这个⽉的第⼏天 == day
select DAYOFMONTH('2022-07-11');
select DAYOFWEEK('2022-07-11'); -- 2
select DAYOFYEAR('2022-07-11'); -- 192
-- 获取当前⽇期⼀个⽉之后的⽇期，并且告知是星期⼏
select DAYNAME(ADDDATE(CURRENT_DATE(),INTERVAL 1 MONTH)); -- Sunday
-- 获取某个⽉的最后⼀天的⽇期
select LAST_DAY(CURRENT_DATE());
-- 获取当前时间，包含时分秒，跟CURRENT_TIMESTAMP 效果⼀样
select now();
-- 专⻔⽤于减天数的函数
select SUBDATE(now(),1);

4、其他函数

mysql> select cast('1' as SIGNED);
+---------------------+
| cast('1' as SIGNED) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.06 sec)

mysql> select cast('2023-03-04' as Date);
+----------------------------+
| cast('2023-03-04' as Date) |
+----------------------------+
| 2023-03-04                 |
+----------------------------+
1 row in set (0.07 sec)

-- 通过cast 进⾏数据类型转换
select CAST('2022-07-21' AS DATE);
-- 返回第⼀个⾮空字符串
select coalesce(null,null,'hello','world');
-- 查看当前所在的数据库
select DATABASE();
select CURRENT_USER();

-- 获取最后⼀个主键的id值 插入错误也算一次
select LAST_INSERT_ID();

5、关于字符集的问题

所有乱码问题，都是字符集不匹配的问题

1、ASCII码 --老美老英

2、ISO8859-1

后来欧洲人也玩计算机，发现不行啊，还有很多符号（法语，德语）ASCII没办法表示啊，于是欧洲人自己也撸了一套编码，一个字节的长度，这套编码叫ISO。

3、GBK

再后来中国人也玩计算机，问题同理,整出一套GBK, 兼容了ASCII编码。

支持中文的字符集 GBK,GB2312，UTF-8

4、为什么会出现乱码？

任何的乱码都是字符集的问题。

比如：你在txt文档中编写了一篇中文的文章，字符集是gbk的。粘贴到notepad++中，notepad++此时的字符集假如是utf-8，中文有可能乱码。

读取的时候和写入的时候字符集不一致，就可能出现乱码。

5、数据库的字符集

1）每一个字段都可以设置单独的字符集（必须是字符串字段）

假如一个字段没有设置字符集，默认字符集是多少？看数据库的字符集是多少！

2）创建数据库的时候，可以指定字符集。假如没有指定，默认要看你安装数据库的时候指定的字符集是什么！

查看mysql 安装时，指定的是什么字符集？
show variables like '%character%';

一般mysql8.0之前，默认字符集是latin,mysql8.0之后已经修改为了utf8。

6、mysql炸裂函数explode（mysql8.0 才支持的函数）

或者统计每种水果各有多少人喜欢？

create table user_like_eat(
  uid int(11),
  `like` varchar(200)
);
insert into  user_like_eat values
(1,'香蕉,黄瓜,番茄'),
(2,'香蕉,冬瓜,黄瓜');

mysql中如何实现explode 的效果呢？先看一个案例：

use zuoye;

CREATE TABLE `wow_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `role` varchar(255) DEFAULT NULL COMMENT '角色简称',
  `role_cn` varchar(255) DEFAULT NULL COMMENT '角色类型',
  `role_pinyin` varchar(255) DEFAULT NULL COMMENT '角色拼音',
  `zhuangbei` varchar(255) DEFAULT NULL COMMENT '装备类型',
  `tianfu` varchar(255) DEFAULT NULL COMMENT '天赋类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

INSERT INTO `wow_info` VALUES (1, 'fs', '法师', 'fashi', '布甲', '冰法|火法|奥法');
INSERT INTO `wow_info` VALUES (2, 'ms', '牧师', 'mushi', '布甲', '神牧|戒律|暗牧');
INSERT INTO `wow_info` VALUES (3, 'ss', '术士', 'shushi', '布甲', '毁灭|痛苦|恶魔');
INSERT INTO `wow_info` VALUES (4, 'dz', '盗贼', 'daozei', '皮甲', '狂徒|刺杀|敏锐');
INSERT INTO `wow_info` VALUES (5, 'ws', '武僧', 'wuseng', '皮甲', '酒仙|踏风|织雾');
INSERT INTO `wow_info` VALUES (6, 'xd', '德鲁伊', 'xiaode', '皮甲', '恢复|平衡|野性|守护');
INSERT INTO `wow_info` VALUES (7, 'dh', '恶魔猎手', 'emolieshou', '皮甲', '复仇|浩劫');
INSERT INTO `wow_info` VALUES (8, 'lr', '猎人', 'lieren', '锁甲', '兽王|生存|射击');
INSERT INTO `wow_info` VALUES (9, 'sm', '萨满', 'saman', '锁甲', '恢复|增强|元素');
INSERT INTO `wow_info` VALUES (10, 'long', '龙人', 'longren', '锁甲', '湮灭|恩护|增辉');
INSERT INTO `wow_info` VALUES (11, 'dk', '死亡骑士', 'siwangqishi', '板甲', '鲜血|冰霜|邪恶');
INSERT INTO `wow_info` VALUES (12, 'zs', '战士', 'zhanshi', '板甲', '武器|狂暴|防护');
INSERT INTO `wow_info` VALUES (13, 'sq', '圣骑士', 'shengqi', '板甲', '神圣|防护|惩戒');


SELECT role
	, SUBSTRING_INDEX(SUBSTRING_INDEX(tianfu, '|', numbers.n), '|', -1) AS exploded_value,
	  CHAR_LENGTH(tianfu) - CHAR_LENGTH(REPLACE(tianfu, '|', '')),
	  numbers.n - 1
FROM wow_info
	JOIN (
		SELECT 1 AS n
		UNION ALL
		SELECT 2
		UNION ALL
		SELECT 3
		UNION ALL
		SELECT 4
	) numbers
	ON CHAR_LENGTH(tianfu) - CHAR_LENGTH(REPLACE(tianfu, '|', '')) >= numbers.n - 1;

select substring_index('冰法|火法|奥法','|',2);
select substring_index(substring_index('冰法|火法|奥法','|',1),'|',-1);
select char_length('冰法|火法|奥法');
select CHAR_LENGTH(REPLACE('冰法|火法|奥法', '|', ''));

知道了这个之后，我们修改一下我们的代码：

SELECT uid,
	SUBSTRING_INDEX(SUBSTRING_INDEX(`like`, ',', numbers.n), ',', -1) AS shuiguo
FROM user_like_eat
	JOIN (
		SELECT 1 AS n
		UNION ALL
		SELECT 2
		UNION ALL
		SELECT 3
	) numbers
	ON CHAR_LENGTH(`like`) - CHAR_LENGTH(REPLACE(`like`, ',', '')) >= numbers.n - 1;

补充：mysql 如何实现split的效果呢？

第一种办法：使用存储过程：

DROP PROCEDURE IF EXISTS `splitString`;
DELIMITER $$
CREATE PROCEDURE `splitString`(IN f_string VARCHAR(1000), IN f_delimiter VARCHAR(5))
BEGIN
    DECLARE cnt INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SET cnt = LENGTH(f_string) - LENGTH(REPLACE(f_string, f_delimiter, '')) + 1;
    DROP TABLE IF EXISTS temp_split_string;
    CREATE TEMPORARY TABLE temp_split_string (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(50));
    WHILE (i < cnt) DO
        INSERT INTO temp_split_string (val)
           SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(f_string, f_delimiter, i + 1), f_delimiter, -1);
        SET i = i + 1;
    END WHILE;
    SELECT * FROM temp_split_string;
    DROP TABLE IF EXISTS temp_split_string;
END$$
DELIMITER ;

call splitString('a,b,c,d,e',',');

第二种办法：使用函数

DROP FUNCTION IF EXISTS fn_split;
DELIMITER $$
CREATE FUNCTION fn_split(s TEXT, del CHAR(1), i INT)
RETURNS VARCHAR(1024)
DETERMINISTIC -- always returns same results for same input parameters
SQL SECURITY INVOKER
BEGIN
    DECLARE n INT;
    -- get max number of items
    SET n = LENGTH(s) - LENGTH(REPLACE(s, del, '')) + 1;
    IF i > n THEN
        RETURN NULL;
    ELSE
        RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(s, del, i), del, -1);
    END IF;
END$$
DELIMITER ;


select fn_split('a,b,c,e,d',',',3);

三、TCL（王牌彩电）

TCL: 就是对mysql中的各种权限，以及账户密码等进⾏操作的语句。

1、创建用户

语法：
create user '⽤户名'@'主机名' identified by '密码';

create user 'chufaxingjin'@'localhost' identified by '123456';
create user 'chufaxingjin'@'%' identified by '123456';
创建了⼀个⽤户，其实就是在mysql数据库中的user表中，插⼊了⼀条记录⽽已。

2、赋予权限

创建好的用户没有权限：

grant 权限1,权限2 ..... on 某个数据库中的⼀些表 to '⽤户名'@'主机名'
grant insert,update,select on `sql`.* to 'chufaxingjin'@'localhost';

# 如果想创建⼀个新的超级管理员，赋予超级权限，可以使⽤如下的⽅式
all privileges 所有权限的意思
*.*   第⼀个* 所有数据库，第⼆个* 所有表
grant all privileges on *.* to 'chufaxingjin'@'localhost' identified by '123456' with grant option;

执行报错，原因是8.0之前的写法和8.0之后的写法不一样了。
-- 8.0之前版本: grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
-- 创建用户
create user 'chufaxingjin'@'localhost' identified by '123456';
-- 赋权限
grant all privileges on *.* to 'chufaxingjin'@'localhost' with grant option;

-- 撤销权限
revoke 权限名 [,权限名.....] on dbname.* from username@ip

-- 刷新权限
flush privilages;

此时复习一下第一天的那个SQL:

修改密码永不过期：
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;

//更新一下用户的密码校验规则
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

//刷新权限
FLUSH PRIVILEGES;

重置密码：
alter user 'root'@'localhost' identified by 'root';

需要学会重启mysql服务：

3、如何修改mysql允许远程登录

1）该表法

在mysql数据库中的user表中，修改数据，重启mysql服务

2）通过TCL语句修改

以前的超级管理员也有很多权限，但是不是 % 的。   %的意思是允许所有的IP访问mysql服务。
-- 创建用户
create user 'root'@'%' identified by 'root';
-- 赋权限
grant all privileges on *.* to 'root'@'%' with grant option;

flush privileges;// 刷新权限，让其⽴即起作⽤。

'root'@'localhost'
'root'@'%' 是一个用户吗？ 不是同一个用户

4、在企业中mysql如何保证数据安全？

1、数据库的端口不要设置3306

2、mysql只支持某台服务器进行连接 mysql是可以设置只允许某个IP可以访问我。

3、防火墙

4、可以购买一些防止被黑的服务。

四、小练习

CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生表';


CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程表';


CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '教师表';


CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '成绩表';


-- 插入学生数据：
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 课程表数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表数据

insert into Score values('01' , '01' , '80');
insert into Score values('01' , '02' , '90');
insert into Score values('01' , '03' , '99');
insert into Score values('02' , '01' , '70');
insert into Score values('02' , '02' , '60');
insert into Score values('02' , '03' , '80');
insert into Score values('03' , '01' , '80');
insert into Score values('03' , '02' , '80');
insert into Score values('03' , '03' , '80');
insert into Score values('04' , '01' , '50');
insert into Score values('04' , '02' , '30');
insert into Score values('04' , '03' , '20');
insert into Score values('05' , '01' , '76');
insert into Score values('05' , '02' , '87');
insert into Score values('06' , '01' , '31');
insert into Score values('06' , '03' , '34');
insert into Score values('07' , '02' , '89');
insert into Score values('07' , '03' , '98');

题目：

-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- 先找到同一个人的 01 和 02 成绩，并且 01 > 02
select * from student where s_id in (
    select s1.s_id from
  (select * from score  where c_id ='01') s1,
  (select * from score s2 where c_id = '02') s2 where s1.s_id = s2.s_id and s1.s_score > s2.s_score
 );

-- 再来一种写法
select * from student stu  join
(select s1.s_id from
  (select * from score  where c_id ='01') s1,
  (select * from score s2 where c_id = '02') s2 where s1.s_id = s2.s_id and s1.s_score > s2.s_score) ss
on stu.s_id = ss.s_id;

-- 再来一个
select * from student stu,
    score s1, score s2
    where stu.s_id = s1.s_id and stu.s_id = s2.s_id and s1.s_score > s2.s_score and s1.c_id='01'
       and s2.c_id ='02';


-- 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩– (包括有成绩的和无成绩的)

-- ans1：思路先找出平均成绩小于60分的同学，然后和学生表join在一起，还需要考虑没有考试的学生的情况
-- 思路一：没有考虑一个学生漏考一门或多门的情况
--  假如一个考试有四门，一个学生只考了三门，此时我的这个sql就不准确了。
select s1.s_id,s1.s_name,avg(s2.s_score) avgScore from student s1,score s2
            where s1.s_id = s2.s_id group by s1.s_id,s1.s_name having avgScore < 60;
-- 修改版
select s1.s_id,s1.s_name,sum(s2.s_score)/3 avgScore from student s1,score s2
            where s1.s_id = s2.s_id group by s1.s_id,s1.s_name having avgScore < 60;

-- 3 是手动写的，能不能统计出来
select count(1) from course;
-- 还需要考虑一个学生一门考试都没考，平均分是0分的情况
select s1.s_id,s1.s_name,sum(s2.s_score)/(select count(1) from course) avgScore from student s1,score s2
            where s1.s_id = s2.s_id group by s1.s_id,s1.s_name having avgScore < 60
union
select s_id,s_name,0 from student where not exists(select * from score where s_id = student.s_id);


-- 查询没学过"张三"老师授课的同学的信息
-- ans1： 张三老师讲过哪些课，这些课程中哪些学生没有考试成绩，没有考试成绩就说明学生没有学过老师的课程。
select * from student where s_id not in (
        select s.s_id from student s, score s2 where s.s_id = s2.s_id
 and s2.c_id  in (
     select c_id from teacher t , course c where t.t_id = c.t_id and t.t_name ='张三'
  )
    );

-- 查询没有学全所有课程的同学的信息
-- 思路：成绩表中，如果某个学生的成绩数量不等于学科总数，就说明这个学生某个科目没有成绩，没有成绩说明他没有学这个科目，把这些学生信息展示出来即可
select * from student where s_id not in(
    select student.s_id from student,score where student.s_id = score.s_id group by student.s_id having count(1) = (select count(1) from course)

    );


-- 查询和"01"号的同学学习的课程完全相同的其他同学的信息
--思路：可以先将01号同学的学习过的课程拼接成字符串，然后其他同学的课程也拼接成字符串，比较，如果相等说明学的课程就相等了。
select s1.s_id,s1.s_name, group_concat(s2.c_id order by s2.c_id) cids from student s1,score s2 where s1.s_id = s2.s_id group by s1.s_id,s1.s_name
 having cids = (select group_concat(s2.c_id order by s2.c_id) cids from score s2 where   s2.s_id ='01' group by s2.s_id) and s1.s_id != '01';

 -- 宝俊的写法
select *
from student
where s_id in (select s_id
               from (
                        (select group_concat(c_id) value
                         from score
                         where s_id = 01) s1
                            join (select s_id, group_concat(c_id) value
                                  from score
                                  group by s_id) s2
                        on s1.value = s2.value
                        ));

此处需要知道一个函数的用法：
group_concat的使用方法为：
group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

-- 查询各科成绩最高分、最低分和平均分：
以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率  
– 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90


-- -- 查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--  及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select
    c1.c_id,
    c1.c_name,
    max(s1.s_score),
    min(s1.s_score),
    round(sum(s1.s_score)/ (select count(1) from student),2) 平均分,
    concat(round(count(if(s1.s_score >= 60,1,null)) / (select count(1) from student)*100,2),'%') 及格率,
    concat(round(count(if(s1.s_score >= 70 and s1.s_score < 80,1,null)) / (select count(1) from student)*100,2),'%') 中等率,
    concat(round(count(if(s1.s_score >= 80 and s1.s_score < 90,1,null)) / (select count(1) from student)*100,2),'%') 优良率,
    concat(round(count(if(s1.s_score >= 90 ,1,null)) / (select count(1) from student)*100,2),'%') 优秀率
from   score s1 join course c1 on s1.c_id = c1.c_id group by c1.c_id,c1.c_name