02.MySQL函数及约束、多表笔记

函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

示例：

select trim(' Hello '); --hello 
select substring('hello mysql',1,5); --hello
1
2

数值函数

示例：

select mode(7,4); --3
select rand();  --0-1,小数位置不限
select round(2.345,2); --2.35
--生成一个六位数的随机验证码
select lpad(round(rand()*1000000),0),6,'0');
1
2
3
4
5

日期函数

select date_add(now(),INTERVAL 70 YEAR); -- 2092-10-25 15:11:46
--查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
1
2
3

流程函数

示例：

select ifnull('ok','default'); --ok
select ifnull('','default');   -- 
select ifnull(null,'default'); --default
1
2
3

select 
	id,
	name,
	(case when math >=85 then '优秀' when math>=60 then '及格' else '不及格' end) as '数学'
from score;
1
2
3
4
5

--查询emp表的员工姓名和工作地址，如果地址为北京、上海返回一线城市，其他返回二线城市
select 
	name,
	(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
	from emp;
1
2
3
4
5

字段约束

• 概念：约束是作用于表中字段的规则，用于限制存储在表中的数据。
• 目的：保证数据库中数据的正确、有效性和完整性。
• 分类：

示例：

外键约束

添加外键约束

方式一：建表时设置约束

create table 表名(
	字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
1
2
3
4
5

方式二：建表后添加约束

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
1

删除外键约束

alter table 表名 drop foreign key 外键名称;
1

删除/更新行为

alter table 表名 add constrant 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade  on delete cascade;
1

在主外键关联中，当主表数据被删除时，CASCADE操作会自动将从表中对应外键的数据也一并删除。
CASCADE是一种级联操作，它的作用是在主表发生变化时，自动更新从表。当主表上的删除操作引发CASCADE时，从表上的相应记录会被删除，而不需要手动执行删除操作。
需要注意的是，CASCADE操作是一种非常强大的操作，需要谨慎使用。在进行级联删除时，应该特别注意数据的完整性和一致性，避免误操作导致数据丢失或不一致。

多表关系

项目开发中，在进行是数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

示例：

一对多（多对一）

• 案例：部门与员工的关系
• 关系：一个部门对应多个员工，一个员工对应一个部门
• 实现：在多的一方建立外键，指向一的一方的主键
  

多对多

• 案例：学生与课程的关系
• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
  

学生关系表SQL：

create table student_course(
	id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id),
);
1
2
3
4
5
6
7

一对一

• 案例：用户与用户详情的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

一对一跟一对多的区别主要在于：设置表时外键为唯一。

多表查询

• 概述：指从多张表中查询数据
• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积)
  

笛卡尔积：

select * from emp,dept;
1

通过内连接消除无效的笛卡尔积：

select * from emp,dept where emp.dept_id = dept.id;
1

内连接： 相当于查询A、B交集部分数据。

外连接

• 左外连接： 查询左表所有数据，以及两张表交集部分数据。

• 右外连接： 查询右表所有数据，以及两张表交集部分数据。

• 自连接： 当前表与自身的连接查询，自连接必须使用表别名
  

内连接

相当于查询两表交集部分数据。

下面例子就是员工表中的部门ID跟部门ID要一致才连接得上，通过内连接的方式才查询得出来。

隐式内连接：

select 字段列表 from 表1,表2 where 条件...;
1

显示内连接：

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
1

示例：查询每一个员工的姓名以及关联的部门名称

隐式内连接：

select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;
1

显示内连接：

select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
1

外连接

左外连接：查询左表所有数据，以及两张表交集部分数据。

select 字段列表 from 表1 left [outer] join 表2 on 条件...;
1

右外连接：查询右表所有数据，以及两张表交集部分数据。

select 字段列表 from 表1 right [outer] join 表2 on 条件...;
1

一般使用左外连接，因为它也能做右外连接做的事。

示例：查询emp表中的所有数据和对应的部门信息

select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;
1

自查询

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
1

自连接查询，可以是内连接查询也可以是外连接查询。

练习：查询员工及其所属领导的名字，因为领导也是员工，所以仍存在员工表中，使用自查询。

select a.name,b.name from emp a , emp b where a.managerid = b.id;
1

员工没有领导也要查出：

select a.name,b.name from emp a left join emp b on a.managerid = b.id;
1

联合查询

就是把多次查询的结果合并起来，形成一个新的查询结果集。

查询员工工资大于5000或年龄大于50岁的员工信息：

select * from emp where salary < 5000
union 
select * from emp where age > 50;
1
2
3

子查询

• 概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

  子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

• 根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为：VHERE之后、FROM之后、SELECT之后。

标量子查询

查询"销售部"的所有员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');
1

列子查询

子查询返回的结果集是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN、NOT IN、ANY、SOME、ALL

练习：

查询”销售部“和”市场部“的所有员工信息

select * from emp where dept_id in (select id from dept where name='销售部' or name = '市场部')
1

查询比”财务部“所有人工资都高的员工信息

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部');
1

行子查

子查询返回的结果是一行（可以是多列）常用操作符：=、<> 、IN、NOT IN

练习：查询与“张无忌”的薪资及直属领导相同的员工信息

select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌'); 
1

表子查询

子查询返回的结果是多行多列，常用操作符：IN。

练习：查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息

select * from emp where (salary,job) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥'); 
1

查询入职日期是“2006-01-01”之后的员工信息及其部门信息

select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;
1