MySQL基础之约束、多表查询、事务【基础完结】

1 SQL约束

SQL约束是用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被终止。

1.1 如何创建约束

• • 在创建表的时候进行规定（create table）
• • 修改表的时候进行规定（alter table）

1.2 约束的分类

• • 非空约束，not null，该列不能存储NULL值
• • 唯一约束，unique，保证必须有唯一值
• • 主键约束，primary key，not null和unique的组合，使用在ID上
• • 默认约束，default，没有给列赋值时的默认值，default null
• • 检查约束，check，保证列中的值符合指定的条件
• • 外键约束，foreign key

1.2.1 非空约束

not null约束强制不接受null值，即在对表中数据进行插入时，该字段不能为null。

create table person(
  id int not null,
  name varchar(255) not null,
  age int not null
);


-- 删除age上的约束
alter table person modify age int null;

-- 添加age上的约束
alter table person modify age int not null;

1.2.2 唯一约束

unique唯一约束，一个表中可以有多个唯一约束。

create table person(
  id int not null unique,
  name varchar(255) not null,
  age int not null
);

-- 删除id上的唯一约束
alter table person drop index id;

-- 添加id上的唯一约束
alter table person add unique (id);

1.2.3 主键约束

primary key 主键约束

• • 主键必须包含唯一的值
• • 主键列不能为null
• • 每个表中只有一个主键
• • MySQL中一般设置自增主键

create table person(
  id int primary key auto_increment,
  name varchar(255) not null,
  age int not null
);


-- 删除主键
alter table person drop primary key;

-- 添加主键
alter table person add primary key (id);

1.2.4 检查约束

check 约束用于限制列中的值的范围

create table person(
  id int not null,
  name varchar(255) not null,
  age int not null,
  constraint chk_id check (id > 0)
);

-- 删除检查约束
alter table person drop check chk_id;

-- 添加检查约束
alter table person add constraint chk_id check (id > 0);

1.2.5 外键约束

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

当两张表没有外键约束时，当部门表中（父表）的数据删除后，会导致员工表（子表）的数据不完整。

--添加外键语法
create table 表名(
字段名 数据类型,
字段名 数据类型,
...
[constraint] [外键名称] foreign key [外键字段名] references 主表(主表列名)
);

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

删除外键
alter table 表名 drop foreign key 外键名称;

2 多表查询

从多张表中查询数据

create table dept(
  id int primary key not null ,
  name varchar(64) not null
);

insert into dept values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'经理部'),(6,'人事部');



CREATE TABLE test.emp
(
    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    age int NOT NULL,
    selary mediumtext NOT NULL,
    entry_date date NOT NULL,
    dept_id int NOT NULL
);

INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (1, '王明', 29, '20000', '2023-07-25', 1);
INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (5, '李四', 20, '10000', '2020-07-25', 1);
INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (6, '赵四', 22, '20000', '2021-07-25', 1);
INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (2, '李红', 20, '15900', '2022-07-25', 2);
INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (3, '王强', 30, '13000', '2020-07-25', 3);
INSERT INTO test.emp (id, name, age, selary, entry_date, dept_id) VALUES (4, '张三', 20, '14000', '2023-07-18', 4);


-- 添加外键
alter table emp add constraint emp_dept_fk foreign key (dept_id) references dept(id);


-- 关联查询
select * from emp,dept where emp.dept_id = dept.id;

2.1 多表查询分类

多表查询分类：

1. 连接查询
2. 1. 内连接，相当于与查询A、B交集部分数据
   2. 外连接 左外连接，查询左表的所有数据，以及两张表交集部分数据 右外连接，查询右表的所有数据，以及两张表交集部分数据
3. 自连接，当前表与自身的连接查询，自连接必须使用表别名
4. 子查询

2.2 内连接

内连接查询的是两张表交集的部分

语法：
隐式内连接
select 字段列表 from 表1, 表2 where 条件;

显式内连接
select 字段列表 from 表1 inner join 表2 on 连接条件;

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

select * from emp inner join dept on emp.dept_id = dept.id;

2.3 外连接

外连接分为：左外连接、右外连接

-- 左外连接语法：
select 字段列表 from 表1 left join 表2 on 条件;
-- 右外连接语法：
select 字段列表 from 表1 right join 表2 on 条件;

select dept.*,emp.* from emp left join dept on emp.dept_id = dept.id;

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

2.4 自连接

-- 语法
select 字段列表 from 表A 别名A join 表B 别名B on 条件;

select * from emp as e1 join emp as e2 on e1.id = e2.id;

2.5 联合查询

联合查询，union，union all 对于union查询，就是把多次查询结果合并起来，形成一个新的查询结果集

语法：
select 字段列表 from 表A
union [all]
select 字段列表 from 表B;

-- 查询年龄大于20，和 工资大于500的员工，注意，union all的时候对数据直接合并，会有重复的
select * from emp where age > 20
union all
select * from emp where selary > 500;


-- 去掉all之后，先合并再去重，数据就不重复了
select * from emp where age > 20
union
select * from emp where selary >500;

注意：

• • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

• • union all会将全部的数据进行合并在一起，union会对合并之后的数据去重

  22.6 子查询

  SQL语句中嵌套select语句，称为嵌套查询，又称为子查询。

2.6.1 标量子查询

标量子查询，子查询返回的是单个值

语法
select * from t1 where column1 = (select column1 from t2);
-- 标量子查询，子查询返回的是单个值
select * from emp where dept_id = (select id from dept where name = '研发部');

select * from emp where entry_date > (select entry_date from emp where name = '李红');

2.6.2 列子查询

列子查询，子查询返回的结果是一列，常用的操作符有：in、not in、any、some、all

-- 查询研发部和市场部的员工信息
-- 先查研发部和市场部的部门id
select id from dept where name ='研发部' or name = '市场部';
-- 再根据部门id查询员工信息
select * from emp where dept_id in (select id from dept where name ='研发部' or name = '市场部');

-- 使用left join关联查询员工表和部门表
select * from emp left join dept on emp.dept_id = dept.id where dept.name ='研发部' or dept.name = '市场部';


-- 查询比市场部所有人员都高的员工工资
select * from emp;

select selary from emp where dept_id = (select id from dept where name='市场部');

select * from emp where selary > all ( select selary from emp where dept_id = (select id from dept where name='市场部'));


-- 我自己能想到的查询方式
-- 1、先获取市场部最高的工资
select max(selary) from emp left join dept on emp.dept_id = dept.id where dept.name = '市场部';
-- 2、在查询大于该工资的员工信息
select * from emp left join dept on emp.dept_id = dept.id where selary > 15900;

-- 比任意市场部员工工资高的员工信息
select * from emp where selary > any ( select selary from emp where dept_id = (select id from dept where name='市场部'));

2.6.3 行子查询

行子查询，子查询返回的结果是一行（可以是多列），示例待补充

2.6.4 表子查询

表子查询，子查询返回的结果是多行多列，配合in使用

select * from emp where (name,age) in (select name,age from emp where selary >1 and dept_id = 1);

3事务简介

3.1 什么是事务

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。

3.2 事务四大特性

• • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• • 持久性（Durability）：事务一旦提交或者回滚，它对数据库中的数据的改变是永久的。

3.3 并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务前后读取同一个记录，但两次读取的数据不同，称为不好重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，当在插入数据时，又发现这行数据已经存在，好像出现了幻影

3.4 事务隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted	✔️	✔️	✔️
read committed	X	✔️	✔️
repeatable read（默认）	X	X	✔️
serializable	X	X	X

serializable可以理解为用锁来解决并发问题。注意：事务隔离级别越高，数据越安全，但是性能越低。 但是性能越低。

3.5 简单使用

-- 设置当前session的事务隔离级别`set session transaction isolation level repeatable read;  
  
-- 开启事务  
start transaction;  
  
-- 对数据库的相关操作  
update ...  
  
-- 提交  
commit;  
  
-- 回滚  
rollback;

关注我，我们一起学习