MySQL数据库3——函数与约束
一.函数
1.字符串函数
MySQL中内置了很多字符串函数，常用的几个如下：
使用方法：
SELECT 函数名(参数);
注意：MySQL中的索引值即下标都是从1开始的。
2.数值函数
常见的数值函数如下：
使用方法：
SELECT 函数名(参数);
3.日期函数
常见的日期函数如下：
注意：
datediff函数计算用的是第一个时间减去第二个时间，所以尽量让大的时间放在第一个，否则出来的结果是负数date_add中间的关键字interval必不可少，例如select date_add(now(),interval 2 day);
4.流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
注意：始终要记住函数在查询语句中的使用位置。
二.约束
1.概述
概念：约束是作用于表中字段上的规则，用来限制存储在表中的数据
目的：保证数据库中数据的正确、有效和完整
分类：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRMARY KEY |
| 默认约束 | 保存数据，如果未指定该字段值，则采用默认值 | DEFAULT |
| 检查约束 | 保证字段满足某一条件 | CHECK |
| 外键约束 | 用来让两个表的数据建立连接，保证数据一致和完整 | FOREIGN KEY |
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
2.约束演示
要完成上述表的建立：
create table worker(id int primary key auto_increment comment '主键',name varchar(10) not null unique comment '姓名',age int check(age>0 && age<=120) comment '年龄',status char(1) default '1' comment '状态',gender char(1) comment '性别'
)comment '员工表';
3.外键约束
添加外键语法：
CREATE TABLE 表名(字段名 数据类型,……[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
)；ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
这里添加外键一直报错，说一下注意事项：
- 首先外键关联的父表与子表的字段类型保持一致
- 如果表中已经有数据，请保证子表中对应字段的数据在父表对应的数据范围内。
删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
当删除外键父表中的对应字段时，如果存在对应的映射关系，删除记录则会失败。
删除/更新行为：
语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
三.多表查询
在上一节简单介绍了多表查询，现在直接详细深入一下学习。
1.多表关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所
以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多(多对一)
- 多对多
- 一对一
一对多：
案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的关系表建立外键，指向一的关系表的主键
多对多：
案例：学生与 课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表(SC)，中间表至少包含两个外键，分别关联两方主键(S#,C#)
一对一：
案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
2.概述
多表查询分类：
-
连接查询
-
内连接：相当于查询A、B交集部分数据
-
外连接：
- 左外连接:查询左表所有数据，以及两张表交集部分数据
- 右外连接:查询右表所有数据，以及两张表交集部分数据
-
自连接：当前表与自身的连接查询，自连接必须使用表别名
-
-
子查询
多表查询分为两个大类：连接查询和子查询，其中连接查询在上一节我们接触过。
3.连接查询-内连接
内连接查询的是两张表交集的部分；
隐式内连接：
SELECT 字段列表 FROM 表1,表2 WHERE 条件……;
显示外连接：
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;
注意：在多表查询时，我们经常使用起别名的方式简化操作，但起别名后，我们不能再通过原来的表名访问字段。
4.连接查询-外连接
左外连接：
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件……;
右外连接：
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件……;
左连接会包含坐标的所有数据，右连接会包含右表的所有数据，具体效果实操就知道了。
5.连接查询-自连接
语法：
SELECT 字段列表 FROM 表名 别名1 JOIN 表名 别名2 ON 条件……;
自连接的连接方式可以是内连接也可以是外连接。
6.联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A……
UNION [ALL]
SELECT 字段列表 FROM 表B……;
这里的all选项是（假设第一个查询结果和第二个查询结果有交集）是否对其进行去重，保留代表不去重，没有all代表去重。
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
7.子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
四.多表查询练习
1.案例一
-
查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id; -
查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30; -
查询拥有员工的部门ID、部门名称
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id; -
查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ; -
查询所有员工的工资等级
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;
2.案例2
-
查询 “研发部” 所有员工的信息及 工资等级
select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部'; -
查询 “研发部” 员工的平均工资
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部'; -
查询工资比 “灭绝” 高的员工信息。
-- a. 查询 "灭绝" 的薪资
select salary from emp where name = '灭绝';-- b. 查询比她工资高的员工数据
select * from emp where salary > ( select salary from emp where name = '灭绝' );-
查询比平均薪资高的员工信息
-- a. 查询员工的平均薪资 select avg(salary) from emp;-- b. 查询比平均薪资高的员工信息 select * from emp where salary > ( select avg(salary) from emp ); -
查询低于本部门平均工资的员工信息
```sql
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );
```
-
查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
-- 表: student , course , student_course -- 连接条件: student.id = student_course.studentid , course.id = student_course.courseidselect s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;
五.事务
1.事务理解
事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。
2.事务操作
查看/设置事务提交方式：
SELECT @@autocommit; -- 用来查看事务的提交方式
SET @@AUTOCOMMIT=0;
第一句用来查看事务的提交方式，如果自动提交就返回1，手动提交就返回0；第二句把提交方式设置为手动提交。
提交事务：
COMMIT;
回滚事务：
ROLLBACK
除了这一种方式以外，我们还有另一种方式进行事务操作
START TRANSACTION 或 BEGIN;
这种方式并没有修改事务的提交方式
3.事务特性-ACID
事务四大特性：
- 原子性 (
Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 - 一致性(
Consistency):事务完成时，必须使所有的数据都保持一致状态 - 隔离性(
lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行 - 持久性(
Durability): 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
4.并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影” |
5.隔离级别
√表示会出线对应的问题，×表示解决了对应的问题。
查看失去隔离级别：
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别：
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE};
- 选项：
SESSION表示仅针对当前窗口有效，GLOBAL表示针对所有窗口有效；