（2）MySQL进阶

 1.约束

MySQL 约束用于限制表中数据的规则，确保数据的准确性和可靠性。以下是 MySQL 中主要的约束类型：

约束	说明	作用与特点
PRIMARY KEY	主键约束	唯一标识表中的每一行 不允许 NULL 值 一个表只能有一个主键
FOREIGN KEY	外键约束	建立两个表之间的关系 确保引用完整性
DEFAULT	默认约束	当没有指定值时设置默认值
UNIQUE	唯一约束	确保列中的所有值都不同 允许 NULL 值（但只能有一个 NULL）
NOT NULL	非空约束	强制列不接受 NULL 值

约束通常在创建表时制定

1.1 主键约束

--建表时添加主键约束：
create table 表名(
    字段名 字段类型 PRIMARY KEY，
    字段名 字段类型);

create teble 表名(
    列名,数据类型,
    [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

--删除主键约束
alter table 表名 drop primary key;

--建表后单独添加主键约束
alter table 表名 add primary key(字段名);

--主键自增 AUTO_INCREMENT
--主键默认从1开始自动增长;i++
create table 表名(
    字段名 字段类型 PRIMARY KEY AUTO_INCREMENT，
    字段名 字段类型);
--插入时省略所有字段时,赋值主键时要给null作为占位符
insert into st values (NULL,'qiqqi',26);

--修改自动增长的开始值，增加的就是新的开始值了AUTO_INCREMENT=？

1.2 唯一、非空、默认值约束

唯一约束的作用：让字段的值唯一，不能重复

非空约束的总用：让字段的值不为null

默认值约束的作用：如果这个字段不设置值，就是用默认值。

--唯一约束
create table 表名(
    字段名 字段类型 UNIQUE，
    字段名 字段类型);

--非空约束
create table 表名(
    字段名 字段类型 NOT NULL，
    字段名 字段类型);

--默认值约束
create table 表名(
    字段名 字段类型 DEFAULT 值，
    字段名 字段类型);

1.3 外键约束

一张表中的某个字段引用其他表的主键，这个字典就称为外键

主表：将数据给别人用的表

副表: 使用别人数据的表

--外键语法
create table 表名(
    字段名 字段类型,
    字段名 字段类型,
    --添加外键约束
    [constraint 外键约束名] foreign key(外键字段名) references 主表(主键字段名)
);

--示例
create table employee (
    id int primary key auto_increment,
    name varchat(10),
    age int,
    dep_id int,
    constraint fk_dep_id foreign key(dep_id) references department(id)；

--删除外键约束
alter table 表名 drop foreign key 外键约束名;
--已有表增加外键约束
alter table 从表 add [constraint 外键约束名称] foreign key(外键字段名) references 主表(主键字段名称);

2.数据库设计

数据库设计的主要步骤：

1. 需求分析

• 确定数据库的目的和范围

• 识别所有用户需求

2. 概念设计

• 创建实体关系模型(ER模型)

• 识别实体、属性和关系

• 确定主键和外键

• 规范化数据(通常到第三范式)

3. 逻辑设计

• 将概念模型转换为关系模型

• 定义表结构、字段和数据类型

• 建立表间关系

• 应用规范化规则

4. 物理设计

• 选择具体的DBMS(如MySQL、PostgreSQL等)

• 定义索引、视图、存储过程等

• 考虑性能优化策略

• 设计安全机制和访问控制

3.多表

3.1表关系

一对一，一对多与多对多。

3.2 事务

事务是数据库管理系统中的一个重要概念，它保证了一组数据库操作要么全部成功执行，要么全部不执行，从而维护数据库的一致性。

事务的四大特性 (ACID)

1. ​​原子性 (Atomicity)​​

   事务是不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚

2. ​​一致性 (Consistency)​​

   事务执行前后，数据库从一个一致状态转变为另一个一致状态

3. ​​隔离性 (Isolation)​​

   多个事务并发执行时，一个事务的执行不应影响其他事务

4. ​​持久性 (Durability)​​

   事务一旦提交，其结果就是永久性的，即使系统故障也不会丢失

举个例子说明事务的重要性，以大壮给小美转账500元为例，我们需要对大壮的余额-500，而小美的余额+500的数据操作，在这个操作过程中，如果大壮余额扣除的操作成功了，而对小美的余额增加操作时失败，这样我们转账的操作就会出现问题，大壮平白无故的少了500元，但小美并没有得到500元；事务就很好的解决了这个问题，这两个操作要么都成功，转账成功，要么都失败，转账失败。

--开启事务
start transaction;
--或者  begin;
begin;

--提交事务
commit;

--回滚事务
rollback;

3.3 多表查询

表连接查询隐式内连接

--查询孙悟空员工的信息，包括所在部门名称

select * from tb_emp,tb_dept;

直接查询两个表会出现笛卡尔积效应，表一的每一个数据都会和表二的每条数据对应。

--去除笛卡尔积
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

--在加上查询员工名字为孙悟空
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id and tb_emp.NAME = '孙悟空';

显示内连接

获取满足条件的数据(白龙马没有部门数据，将会查不到）

--显示内连接语法
select 字段名称 from 表1 [inner] join 表2 on 条件;

select * from tb_emp inner join tb_dept 
on tb_emp.dept_id = tb_dept.id 
where tb_emp.NAME = '孙悟空';

左外连接右外连接

--左外连接语法 使用left outer join ... on, outer可以省略
select 字段名称 from 表1 left [outer] join 表2 on 条件;

select * from tb_emp left outer join tb_dept 
on tb_emp.dept_id = tb_dept.id 

--右外连接语法 使用right outer join ... on, outer可以省略
select 字段名称 from 表1 right [outer] join 表2 on 条件;

select * from tb_emp right outer join tb_dept 
on tb_emp.dept_id = tb_dept.id; 

子查询

一个查询语句的结果作为另一个查询语句的一部分

--子查询语法
--子查询要放在（）中
select 查询字段 from 表 where 条件;


--子查询结果的三种情况

--子查询结果是单行单列
--查询工资最高的员工是谁
select * from tb_emp where salary = (select max(salary) from tb_emp);

--子查询结果是多行单列，结果类似于一个数组，在where后面作为条件，父查询使用IN/ANY/ALL运算符
seelct 字段名 from 表 where 字段 in (子查询)
--查询工资大于5000的员工，来自于那些部门的名字
select NAME from tb_dept where id in (select dept_id from tb_emp where salary > 5000);

--子查询结果是多行多列，在from后面作为虚拟表
select 查询字段 from (子查询) 表别名 where 条件;
--查询出2011年后入职的员工信息，包括部门名称
select * from (select * fron tb_emp where join_date >= '2011-01-01') e 
left outer join tb_dept
on e.dept_id = tb_dept.id;