MySQL 学习笔记 Part3


学习链接来自黑马程序员： https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=22&share_source=copy_web&vd_source=c76bb3d6e0326c966bf1bf32db90eb22


函数

字符串函数

数值函数

例子

生成一个六位数的随机验证码

1
2
3
-- 生成一个六位数的随机验证码

select lpad(floor(rand() * 1000000),6,"0");

日期函数


流程函数


约束

例子

1
2
3
4
5
6
7
8
9
10
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 and age <= 120 ) comment '年龄',
    status  char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

-- 插入数据
insert into user(name, age, status, gender) values ('Tom1',19,'1','男') ,('Tom2',25,'0','男');

我们并未设定id，id自动补充，为自增。

外键约束

用来让两张表之间的数据建立连接，从而保证数据的一致性和完整性。

外键删除更新行为


多表查询

一对多

一对多：建立一个外键约束即可。


多对多

多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 多对多 ----------------
create table student(
                        id int auto_increment primary key comment '主键ID',
                        name varchar(10) comment '姓名',
                        no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');


create table course(
                       id int auto_increment primary key comment '主键ID',
                       name varchar(10) comment '课程名称'
) comment '课程表';

insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');


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)
)comment '学生课程中间表';

insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);


一对一

一般用来单表拆分。

实现：在任意一方加入外键，关联另一方主键，并且设置外键唯一（UNIQUE）。


多表查询分类

  • 内连接：查询A、B交集部分数据。
  • 左外连接：查询左表所有数据，以及两张表交集数据。
  • 右外连接：查询右表所有数据，以及两张表交集数据。
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名。

内连接

1
2
3
4
5
6
7
8
9
10
11
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select emp.name, dept.name from emp, dept where emp.dept_id = dept_id;

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

-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)  --- INNER JOIN ... ON ...
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id

外连接

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 外连接演示
-- 1. 查询emp表的所有数据, 和对应的部门信息(左外连接)
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id
select emp.*, dept.name from emp left outer join dept  on emp.dept_id = dept.id;


-- 外连接演示
-- 1. 查询emp表的所有数据, 和对应的部门信息(右外连接)
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id

select emp.*, dept.name from dept right outer join emp on dept.id = emp.dept_id

自连接

需要把表看成两张表。

1
2
3
4
5
6
7
8
9
10
-- 自连接
-- 1. 查询员工 及其 所属领导的名字
-- 表结构: emp

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

-- 2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询


子查询

列子查询

行子查询

子查询返回的结果是一行，可以是多列。

例子

1
2
3
4
5
6
7
-- 行子查询
-- 1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
-- a. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列。

也就是说，把查询到的结果当作一个表，再联合查找。


事务

事务是一组操作的集合，即这些操作要么同时成功、要么同时失败。

事务操作

流程：

  • select @@autocommit; 查询提交方式，默认自动提交，为1。
  • set @@autocommit = 0; 设置提交方式为手动。
  • start transaction 或 begin; 开启事务
  • commit; 提交事务
  • rollback; 回滚事务

事务四大特性

  • 原子性（Atomicity ）：一个事务是一个不可再分割的整体，要么全部成功，要么全部失败
  • 一致性（Consistency ）：一个事务可以让数据从一种一致状态切换到另一种一致性状态
  • 隔离性（Isolution ）：一个事务不受其他事务的影响，并且多个事务彼此隔离
  • 持久性（Durability ）：一个事务一旦被提交，在数据库中的改变就是永久的，提交后就不能再回滚

事务并发存在的问题

  • 脏读
  • 不可重复读
  • 幻读

类似于计组的 读后写、写后读、 写后写 问题。


事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

1
2
3
4
5
6
7
-- 查看事务隔离级别
select @@transaction_isolation;

-- 设置事务隔离级别
set session transaction isolation level read uncommitted ;

set session transaction isolation level repeatable read ;