MySql进阶

1、多表间的关系

1.1、为什么要拆表

例如插入一个用户数据时，需要姓名、性别、电话、住址、部门等等信息，其中同部门人的部门信息就会相同，这样就会造成数据的冗余。入下图

所以可以将这两张表拆成两张表：员工表和部门表；

问题：当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。我们想要employee的dep_id中的内容只能是department表中存在的id，所以需要外键约束。

1.2、外键约束【重点】

​ 保证引用完整性 用来维护多表间关系

外键: 一张从表中的某个字段引用主表中的主键
主表： 约束别人
副表/从表： 使用别人的数据，被别人约束

语法
1、新建表时增加外键：

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

2、已有表增加外键：

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENC 主表(主键字段名);

关键字解释：
CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段

3、删除外键

ALTER TABLE 表 drop foreign key 外键名称;

练习：

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;
-- 重新为已经存在的表增加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

外键的级联

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除
场景：

语法：

alter table 表 add foreign key(列[外键]) references 表(列[主键]) [ON UPDATE CASCADE][ON DELETE  CASCADE]

外键注意事项

• 外键的这个列的类型必须和参照主表主键列的类型一致
• 参照列必须是主键

1.3、多表间的关系

多表间的关系分为三种：一对一、一对多、多对多；

1.3.1、一对一

生活中有许多一对一的例子，例如一个人只有一个身份证号，一个身份证号只属于一个人，这种就属于一对一的关系。通常为单表，或者跟一对多一样建表。

1.3.2、一对多

例如部门和员工就是一对多的关系，一个部门可以有多个员工，一个员工只属于一个部门
一对多建表原则: 在从表(多方的一方)创建1一个字段,字段作为外键指向主表(一方)的主键

1.3.3、多对多

例如：

1. 老师和学生，一个学生可以有多个老师，一个老师可以教多个学生，多对多的关系
2. 学生和课程：一个学生可以选多门课程,一门课程也可以由多个学生选择，多对多的关系
   多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
   

2、连接查询

2.1、内连接查询【重点】

内连接查询的是公共部分,满足连接条件(主外键关系)的部分

1. 隐式内连接：隐式里面是没有inner关键字的

select [字段,字段,字段][*] from a,b where 连接条件 --(a表里面的主键 = b表里面的外键) 

2. 显示内连接：显示里面是有inner关键字的

select [字段,字段,字段][*] from a [inner] join b on 连接条件 [ where 其它条件]

练习：
查询所有部门下的员工信息,如果该部门下没有员工则不展示

-- 2.1 隐式内连接方式 
SELECT * FROM dept , emp where emp.`dept_id`=dept.`id`;
-- 2.2 显示内连接方式  
SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;

2.2、外连接查询【重点】

如果要保证某张表的全部数据情况下进行连接查询，那么就要使用外连接查询了，外连接分为左外连接和右外连接。

2.2.1左外连接

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.
​可以理解为：在内连接的基础上保证左边表的数据全部显示

1. 语法

select [字段][*] from a left [outer] join b on 条件

2. 练习:查询所有部门下的员工

SELECT * FROM dept LEFT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;

2.2.2右外连接

​以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示

可以理解为：在内连接的基础上保证右边表的数据全部显示

1. 语法

select 字段 from a right [outer] join b on 条件

2. 练习:查询所有员工所对应的部门

SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.dept_id=dept.id;

3、子查询【重点】

直观解释: 一个查询里面至少包含2个select

• 一个查询语句的结果作为另一个查询语句的条件
• 有查询的嵌套，内部的查询称为子查询
• 子查询要使用括号
• 子查询结果的三种情况：
  1. 子查询的结果是一个值的时候
  2. 子查询结果是单例多行的时候
  3. 子查询的结果是多行多列

3.1、子查询的结果是一个值的时候

子查询结果只要是单个值，肯定在WHERE后面作为条件

1. 根据最高工资到员工表查询到对应的员工信息

select * from emp where salary = (select max(salary) from emp);

2. 到员工表查询小于平均工资的员工信息

select * from emp where salary < (select avg(salary) from emp);

3.2、子查询结果是单例多行的时候

子查询结果只要是单列，肯定在WHERE后面作为条件
子查询结果是单列多行，结果集类似于一个数组，父查询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

1. 查询开发部与财务部所有的员工信息

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));

3.3、子查询的结果是多行多列

子查询结果只要是多行多列，肯定在FROM后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段

1. 查询出2012年以后入职的员工信息，包括部门名称

select * from (select e.*,d.NAME d_name from emp e left join dept d on d.id = e.dept_id) p where p.join_date > '2012';

4、常用函数

为了简化操作，MySql提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数），函数可以出现在插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中。

4.1、if相关函数【重点】

4.1.1、if语句

语法：if(expr1,expr2,expr3)
说明： 如果 expr1 是TRUE，则 IF()的返回值为expr2; 否则返回值则为 expr3。if() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
练习：获取用户的姓名、性别，如果性别为1则显示1，否则显示0；要求使用if函数查询

SELECT uname, IF(sex, 1, 0) FROM t_user;

4.1.2、ifnull语句

语法：ifnull(expr1,expr2)
说明：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。ifnull()的返回值是数字或是字符串，具体情况取决于其所使用的语境。
练习：获取用户的姓名、性别，如果性别为null则显示为1；要求使用ifnull函数查询

SELECT uname, IFNULL(sex, 1) FROM t_user;

4.2、字符串函数

函数或操作符	描述
字符串连接函数
concat(str1, str2, …)	字符串连接函数，可以将多个字符串进行连接
concat_ws(separator, str1, str2, …)	可以指定间隔符将多个字符串进行连接；
字符串大小写处理函数
upper(str)	得到str的大写形式
lower(str)	得到str的小写形式
移除空格函数
trim(str)	将str两边的空白符移除
替换字符串函数
replace(str,s1,s2)	将str字符串中的s1字符串全部替换为s2
子串函数
substr()、substring()	获取子串： 1：substr(str, pos) 、substring(str, pos)； 2：substr(str, pos, len)、substring(str, pos, len)

4.3、时间日期函数

函数或操作符	描述
current_date()	获取当前日期，如 2019-10-18
current_time()	获取当前时：分：秒，如：15:36:11
now()	获取当前的日期和时间，如：2019-10-18 15:37:17
year()年、month()月、day()日	获取年月日
hour()时、minute()分、second()秒	获取时分秒

4.4、数值函数

函数或操作符	描述
abs(x)	获取数值x的绝对值
ceil(x)	向上取整，获取不小于x的整数值
floor(x)	向下取整，获取不大于x的整数值
pow(x, y)	获取x的y次幂
rand()	获取一个0-1之间的随机浮点数
round(x)	四舍五入

4.5、其他

函数或操作符	描述
uuid()	获取一个随机的字符串