前言

本文内容主要是对MySQL基础学习的提升。对于刚学习MySQL的小伙伴可以先学习MySQL基础，然后再来看这篇文章。点击学习MySQL基础

一、数据完整性

作用：保证用户输入的数据保存到数据库中是正确的。确保数据的完整性就是在创建表时给表中添加约束。

完整性分类：实体完整性、域完整性、引用完整性

1.实体完整性

实体定义：即表中的一行（一条记录）代表一个实体（entity）。

实体完整性的作用：标识每一行数据都不重复。

1.1.1 约束类型

• 主键约束（primary key）
• 唯一约束（unique）
• 自动增长列（auto_increment）

1.1.1.1 主键约束

每个表中要有一个主键。特点：数据唯一，且不能为null

    -- 例：第一种添加方式： 
    CREATE TABLE student( 
        id int primary key, 
        name varchar(50) 
    );
    
    -- 第二种添加方式：此种方式优势在于，可以创建联合主键 
    CREATE TABLE student( 
        id int, name varchar(50), 
        primary key(id) 
    );
    
    CREATE TABLE student( 
        classid int, 
        stuid int, 
        name varchar(50), 
        primary key(classid,stuid) );
    
    -- 第三种添加方式：单独添加主键 
    CREATE TABLE student( 
        id int, 
        name varchar(50) 
    );
    
    ALTER TABLE student ADD PRIMARY KEY (id);

1.1.1.2 唯一约束

数据不能重复。但是null不算重复

    CREATE TABLE student( 
    Id int primary key, 
    Name varchar(50) unique 
    );

1.1.1.3 自动增长列

默认从最大值开始+1
作用：给主键添加自动增长的数值 列只能是整数类型

    CREATE TABLE student( 
    Id int primary key auto_increment, 
    Name varchar(50) 
    );
    INSERT INTO student(name) values('tom');

2.域完整性

作用：限制此单元格的数据正确，不与此列的其他单元格比较，域代表当前单元格

2.1 域完整性约束分类：

• 非空约束（not null）
• 默认值约束（default）
• check约束（mysql不支持）

数据类型：数值类型、日期类型、字符串类型

2.2 非空约束

关键字： not null

    CREATE TABLE student( 
    Id int primary key, 
    Name varchar(50) not null, 
    Sex varchar(10) );
    
    INSERT INTO student values(1,'tom',null);

2.3 默认值约束

关键字：defult

    CREATE TABLE student( 
    	Id int primary key, 
    	Name varchar(50) not null, 
    	Sex varchar(10) default '男' );
    	
    insert into student values(1,'tom','女'); 
    insert into student values(2,'jerry',default);

3.引用完整性（参照完整性）

外键约束（foreign key）

    例：CREATE TABLE stu( 
    	sid int primary key, 
    	name varchar(50) not null, 
    	sex varchar(10) default '男' 
    );
    
    create table score( 
    	id int primary key, 
    	score int, sid int , -- 外键列的数据类型一定要与主键的类型一致 
    	foreign key (sid) references stu(sid) 
    );
   
    -- 撤销外键 先选中外键所在的表,在右下角找到info选项,拉到最后,找到创建score表的信息,我们可以发现主外键关联的标识 score_ibfk_1 
    ALTER TABLE score DROP FOREIGN KEY score_ibfk_1 
    
    -- 第二种添加外键方式。 
    ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(sid);

二、多表查询

1.多表分类查询

合并结果集：UNION、UNION ALL

连接查询内连接：[INNER] JOIN ON

外连接：OUTER JOIN ON

左外连接：LEFT [OUTER] JOIN

右外连接：RIGHT [OUTER] JOIN

圈外连接（MySQL不支持）：FULL JOIN

自然连接：NATURAL JOIN

2.合并结果集

作用：合并结果集就是把俩个select语句的查询结果合并到一起

要求：被合并的俩个结果列数、类型必须一致

3.连接查询

3.1内连接

    -- 连接student和scores俩个表
    -- 被连接的俩个表需要有至少一个相同的字段，否则无法进行内连接
    SELECT * FROM 
    	student stu 
    	INNER JOIN scores sco 
    	ON stu.id=sco.id;

3.2 外连接

外连接：查询出的结果存在不满足条件的可能

3.2.1 左外连接

使用LEFT OUTER JOIN … ON

写法：SELECT FROM 表一 LEFT OUTER(可省略) JOIN 表二 ON 表一.id = 表二.id

特点：以左表为主，左表的信息会全部查出来。右边只能查出与左表中id相同的记录。其他的不显示，左表有数据的，右表没有对应数据的显示NULL

    -- 左外连接
    -- 先查询出坐标，然后再查询右表。右表满足条件的记录就显示出来，不满足条件的显示为NULL
    SELECT * FROM emp e 
    LEFT OUTER JOIN dept d 
    ON e.deptno=d.deptno;

3.2.2 右外连接

使用RIGHT OUTER JOIN … ON

写法：SELECT FROM 表一 RIGHTOUTER(可省略) JOIN 表二 ON 表一.id = 表二.id

特点：以右表为主，右表的信息会全部查出来。左边只能查出与右表中id相同的记录。其他的不显示，右表有数据的，左表没有对应数据的显示NULL

    -- 右外连接
    SELECT * FROM student stu 
    RIGHT OUTER JOIN scores sco 
    ON stu.id=sco.id

3.2.3 总结

连接可以是多张表，连接查询也可以是多张表。这种情况需要使用条件去除不需要的记录。大部分情况下使用的是主外键关系进去去除。如果俩张表进行连接查询的话，那么至少需要一个主外键条件；三张表进行连接查询话，至少需要俩个主外键条件

3.2.4 自然连接

自然连接无需显式给出主外键关系，系统自动找到这一关系。

    -- 注意:1.要有主外键,而且主外键的的名字要相同.如果没有主外键,他默认会找字段相同的所有字段进行比较,如果 没有字段相同的字段报错 
    -- 2.自然连接时,会将相同的字段合并
    -- 3.如果只写natural 默认当作内连接 
    SELECT * FROM student stu NATURAL JOIN scores sco SELECT * FROM student stu NATURAL LEFT JOIN scores sco 
    SELECT * FROM student stu NATURAL RIGHT JOIN scores sco

4.子查询

定义：一个select语句包含另外一个完整的select语句子。查询就是嵌套查询。

子查询出现的位置：

• where后：作为查询条件的一部分。
• from后：作为一张表。

注意：当子查询出现在where后作为条件时，可以使用any all 关键字

    -- 子查询示例
    SELECT * FROM emp 
    WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')

4.1 自连接

本表连接本表，需要给表起别名

    -- 自连接示例
    SELECT e1.empno , e1.ename,e1.mgr,e2.ename FROM emp e1, emp e2 
    WHERE e1.mgr = e2.empno AND e1.empno = 7369;

4.2合并结果集

合并结果集就是将两次或者多次的查询结果，合并到一起，存入一张查询结果虚拟表中。进行结果集合并的多张表，要求字段的数量是完全相同的。

语法：

union：对两次的查询结果进行合并，对最终的合并结果会进行去重处理

union all：对两次的查询结果进行合并，不会进行去重处理

三、MySQL中的函数

1.功能性函数

1.1 IFNULL

函数形式：IFNULL(field,value)

逻辑：如果第一个参数field是NULL，则返回第二个参数value的值，否则返回field本身的值。

     -- 查询结果是11
    select ifnull(null, 11);
    -- 查询结果是12
    select ifnull(12, 20); 
    -- 如果id字段的值是NULL，就返回0，否则就返回id字段的值
    select ifnull(`id`, 0); 

1.2 IF

类似Java中三目运算符

形式：IF(condition,value1,value2)

逻辑：如果第一个参数condition条件成立，返回value1的值，否则返回value2的值

    -- 10>20不成立，返回20
    select if(10>20,10,20)；
    -- 10<20成立，返回10
    select if(10<20,10,20)；
    -- 如果math字段的值大于english的值，返回math；否则返回english的值
    select if(math>english,math,english)；

1.3 CASE

实现分支结构。

case…when有俩种写法

写法1：

    -- 需求: 已知sc表中有⼀个字段名为`score`表示⼀个学⽣的成绩。通过这个成绩字段的不同范围，查询出不同
    的结果:
    -- < 0 或者 > 100, 等级为"错误成绩"
    -- [0, 60), 等级为"不及格"
    -- [60, 80), 等级为"良"
    -- [80, 100], 等级为"优"
    select `sid`, `score`,
     case
     when `score` < 0 or `score` > 100 then '错误成绩'
     when `score` < 60 then '不及格'
     when `score` < 80 then '良'
     else '优'
     end as 'level'
    from `sc`;

写法2：

    -- 需求: 已知sc表中有⼀个字段名为`subject`表示成绩的科⽬。通过这个字段的不同的值，查询出不同的描述
    信息:
    -- 如果是chinese，查询出"语⽂"
    -- 如果是math，查询出"数学"
    -- 如果是english，查询出"英语"
    -- 其他的保持原样
    select `subject`,
     case `subject`
     when 'chinese' then '语⽂'
     when 'math' then '数学'
     when 'english' then '英语'
     else `subject`
     end as '翻译'
    from `sc`

1.4 行转列

将行信息转为列信息

    -- 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 '体育'
    from `sc` group by `sname`;
    -- case实现版本
    select `sname` as '姓名',
     sum(case `subject` when '语⽂' then `score` else 0 end) as '语⽂',
     sum(case `subject` when '数学' then `score` else 0 end) as '数学',
     sum(case `subject` when '英语' then `score` else 0 end) as '英语',
     sum(case `subject` when '历史' then `score` else 0 end) as '历史',
     sum(case `subject` when '政治' then `score` else 0 end) as '政治',
     sum(case `subject` when '体育' then `score` else 0 end) as '体育'
    from `sc` group by `sname`;

1.5 exists

exists：判断一个表中的数据是否在另外一张表中查询到与之对应的数据。效率比连接查询和子查询要高。

    -- 案例: 查询有员⼯的部⻔
    select distinct dept.* from dept left join emp on dept.deptno = emp.deptno where empno is
    not null;
    select * from dept where exists(
     select 1 from emp where dept.deptno = emp.deptno
    );

2.常用系统函数

2.1 日期函数

2.2 字符串函数

2.3 常规函数

3.自定义函数

定义：mysql中的函数与存储过程类似，都是一组SQL集

与存储过程的区别：

1. 函数可以有return值，存储过程不能直接return，但是有输出参数可以输出多个返回值；
2. 函数可以嵌入到sql语句中使用，但是存储过程不能；
3. 函数一般用于实现简单的有针对性的功能（如求绝对值、返回当前时间等），存储过程用于实现复杂的功能
4. 函数的关键字是function，存储 过程的关键字是procedure

3.1代码示例

    DROP FUNCTION IF EXISTS func_compare; -- 丢掉已经存在的函数 
    DELIMITER ;; -- 自定义分隔符,这里定义的分隔符是;;,定义好后,只有遇到;;才会结束 
    -- 这里是在连接数据库 + 创建函数 
    CREATE DEFINER='root'@'localhost' FUNCTION func_compare(a INT) RETURNS VARCHAR(200) CHARSET utf8 
    BEGIN -- 函数开始 
    String temp = ""; 
    if(a>=10){temp = "大于等于10";}else{} 
    -- 这里写的是if语句 
    IF a >= 10 THEN RETURN '大于等于10'; 
    ELSE 
    RETURN '小于10'; 
    END IF; 
    END -- 函数结束 ;;
    DELIMITER ; -- 重新将分隔符定义成; 
    -- 使用函数 
    select func_compare(4) 
    结果:小于10

4.窗口函数

窗口函数又称为开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的函数。常用于计算基于组的某种值。与聚合函数区别在于：对于每个组返回多行，而聚合函数对于每个组只返回一行。简单来说窗口函数对每条详细记录开一个窗口，进行聚合统计的查询。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，同时窗口函数一般不会单独使用。窗口函数内也可以进行分组和排序。

4.1 over()函数

    -- 需求: 查询每⼀个员⼯的基本信息，及所有的员⼯的数量
    select *, count(*) over() from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及所有员⼯的⼯资和
    select *, sum(`sal`) over() from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及所有员⼯的平均奖⾦
    select *, avg(ifnull(`comm`, 0)) over() from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及所有员⼯的平均⼯龄
    select *, avg(round(datediff(now(), `hiredate`)/365, 1)) over() from `emp`;

4.2 窗口分组

通过指定的字段，对数据进行分组，此时窗口内的数据为指定分组的数据。

例如: count(*) over(partition by deptno), 此时将数据通过deptno字段进⾏了分组，窗口内的数据是与本行的deptno相同的数据。

    -- 需求: 查询每⼀个员⼯的基本信息，及其部⻔的⼈数
    select *, count(*) over(partition by `deptno`) from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及其部⻔的最⾼⼯资
    select *, max(`sal`) over(partition by `deptno`) from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及其部⻔的平均薪资
    select *, avg(ifnull(`sal`, 0)) over(partition by `deptno`) from `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及部⻔的平均⼯龄
    select *, avg(round(datediff(now(), `hiredate`)/365, 1)) over(partition by `deptno`) from
    `emp`;
    -- 需求: 查询每⼀个员⼯的基本信息，及当前岗位的⼈数
    select *, count(*) over(partition by `job`) from `emp`;

4.3 窗口数据排序

使用order by子句，实现窗口内的数据按照指定的条件进行升序或者降序排列。注意：窗口内的数据会逐行递增。

    select *, sum(`sal`) over(partition by `deptno` order by `sal` desc) from `emp`;

4.4 排名函数

对数据进行名次排序。常见排名函数有三种：row_number()、rank()、dense_rank()

row_number()：对每一行的数据进行编号，不会出现相同的名次

rank()：会出现并列的名次，同时会出现名次跳跃情况

dense_rank()：会出现并列的名次，不会出现名次跳跃情况

    -- 查询每⼀个员⼯的基本信息，及员⼯在⾃⼰部⻔内的⼯资排名
    select *, rank() over(partition by `deptno` order by `sal` desc) from `emp`;
    -- 查询每⼀个员⼯的基本信息，及员⼯在所有⼈中的⼯资排名
    select *, dense_rank() over(order by `sal` desc) from `emp`;
    -- 查询每⼀个员⼯的基本信息，及员⼯在同⼀个⼯种内的⼯资排名
    select *, rank() over(partition by `job` order by `sal` desc) from `emp`;
    -- 查询每⼀个员⼯的基本信息，及员⼯在所有⼈中的⼯龄排名
    select *, rank() over(order by `hiredate`) from `emp`;
    -- 查询每⼀个员⼯的基本信息，及员⼯在当前部⻔中的⼯龄排名
    select *, rank() over(partition by `deptno` order by `hiredate`) from `emp`;

四、MySQL中的存储过程

1.简介

MySQL从5.0开始支持procedure

存储过程简单来说就是为以后的使用而保存的一条或多条的MySQL语句的集合。可以视为批件，但是它们的作用不仅限于批处理。存储过程就是有业务逻辑和流程的集合。可以在存储过程中的创建表，更新数据，删除数据等等

通过吧处理封装在容易使用的单元中，简化复杂的操作。由于不要求反复建立一系列处理步骤，保证了数据的完整性，如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的，这样就能防止错误。需要执行的步骤越多出错的可能性就越大。防止错误保证了数据一致性。简化对变动的管理。如果表名、列名或逻辑有变化，只需要更改存储过程的代码就可以。对于使用存储过程的人员不需要知道其实现过程。

2.过程说明

    DROP PROCEDURE IF EXISTS math1; -- 丢掉存储过程
    math1 DELIMITER ; -- 重新设置分隔符为;
    CREATE PROCEDURE math1 -- 创建存储过程 math1 
    (IN a INT,IN b INT) -- 传入参数 a,b IN的意思是传入参数 OUT的意思是传出参数 INOUT是既可 以传入又可以传出 
    
    BEGIN
    	declare c int default 0; -- 定义局部变量,只能在存储过程中使用 
    	set c = 3; 
    	SET @var1 = 1; -- 设置用户变量,全局使用 
    	SET @var2 = 2; 
    	SELECT @SUM:=(a + b) AS SUM, @dif:=(a - b) AS dif; 
    END; -- 结束 
    
    DELIMITER ;-- 重新设置分隔符为; 
    CALL math1(1,2) -- 调用存储过程 
    SELECT @var1; -- 调用用户变量

3.参数说明

3.1 参数in的使用

in代表输入，表示要将参数传到存储过程的过程里面去

为了避免存储过程中分号结束语句，我们使用分隔符告诉mysql解释器，该段命令已经结束了。

    -- in的使用 示例：求1-n的和
    delimiter $ 
    create procedure p1(in n int) 
    begin 
        declare total int default 0; 
        declare num int default 0; 
        while num < n do 
        set num:=num+1; 
        set total:=total+num; 
        end while; 
        select total; 
    end$
    call p1(10)$

3.2 参数out的使用

out代表输出，将存储过程中得到的数据输出出来

注意：输出参数一定要设置初始类型，否则输出的结果都为NULL值

    -- out的使用 示例：求1-n的和
    create procedure p2(in n int,out total int) 
    begin declare num int default 0; 
        set total:=0; 
        while num < n do 
        set num:=num+1; 
        set total:=total+num; 
        end while;
    end$

总结in、out区别： in:表示输入一个值，需要一个值，就给一个值 out:往外输出一个值，输出的那个值就拿一个变量来接收输出的那个值

3.3 参数inout的使用

inout既能输入一个值又能输出一个值

    -- inout的使用 示例：传一个年龄，自动让年龄增长10岁
    create procedure p3(inout age int) 
    begin 
        set age:=age+10; 
    end$ 
    -- 注意：调用的时候，inout型的参数值既是输入类型又是输出类型，给它一个值，但值不是变量，我们需要先设置一个变量并初始化这个值，调用的时候直接传这个变量即可。 
    set @currentAge=8$ 
    call p3(@currentAge)$ 
    select @currentAge$

3.4 变量说明

MySQL的变量分俩种：系统变量和用户变量，但在实际的使用中还会有局部变量、会话变量等

3.4.1 局部变量

局部变量一般用于sql语句块中，比如存储过程的begin/end中。其作用域仅限于该语句块。在这语句块执行完毕后，局部变量就消失了。

局部变量用declare来声明，可以使用default来设置默认值。

3.4.2 用户变量

用户变量的作用域比局域变量的作用域广，可以作用于当前整个连接，但当当前的连接断开之后，所定义的用户变量都会小时。

用户变量定义：select @变量名

对用户变量赋值方式：

1. 直接用“=”号
2. 用“:=”号

区别：使用set命令对用户变量进行赋值时，俩种方式都可以使用。当使用select语句对用户变量进行赋值时，只能使用“:=”方式，因为在select语句中“=”被认为是比较操作符。

3.4.3 会话变量

服务器会为每个连接的客户端维护一系列会话变量。在客户端连接时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要额外的特殊权限，但客户端只能更改自己的会话变量，而不能更改其他客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接，当前连接断开之后，设置的会话变量会失效。

设置会话变量的三种方式：

    -- 设置会话变量有如下三种方式： 
    set session var_name = value; 
    set @@session.var_name = value; 
    set var_name = value;

查看会话变量的三种方式：

    select @@var_name; 
    select @@session.var_name; 
    show session variables like "%var%"; 
    mysql> show session variables;

3.4.4 全局变量

全局变量影响服务器的整体操作。当服务器启动时，他会将所有的全局变量初始化为默认值。这些默认值可以在选项文件中或者命令行中指定的选项进行修改。要想更改全局变量，必须具有SUPER的权限。全局变量作用于server的整个生命周期。但是重启后所有设置的全局变量会全部失效。想让全局变量在重启后继续生效的话，需要更改相应的配置文件。

    -- 要设置一个全局变量，有如下两种方式： 
    set global var_name = value; -- 注意：此处的global不能省略。根据手册，set命令设置变量时若不指定 GLOBAL、SESSION或者LOCAL，默认使用SESSION 
    set @@global.var_name = value; -- 同上 
    
    -- 要想查看一个全局变量，有如下两种方式： 
    select @@global.var_name; 
    show global variables like "%var%"; 
    mysql> show global variables;

总结

以上就是有关MySQL进阶的知识部分了，不足的地方敬请指出。