MySQL数据库基础学习笔记（二）
一、数据库表的约束
1、分类
| 约束名 | 关键字 | 含义 |
|---|---|---|
| 非空约束 | not null | 值不能为空 |
| 唯一约束 | unique | 值不能重复 |
| 主键约束 | primary key | 值非空且唯一 |
| 外间约束 | foreign key | 让表于表产生关系，从而保证数据的正确性 |
2、详细使用
非空约束
- 创建表时，添加非空约束
create table 表名(
字段名 字段类型 not null -- 对该字段添加了非空约束
);
- 创建后，如何添加非空约束
alter table 表名 modify 字段名 字段类型 not null;
- 创建后，如何删除非空约束
alter table 表名 modify 字段名 字段类型;
唯一约束
- 创建表时，添加唯一约束
create table 表名(
字段名 字段类型 unique
);
- 创建后，如何添加唯一约束
alter table 表名 modify 字段名 字段类型 unique;
- 创建后，如何删除唯一约束
alter table 表名 drop index 字段名;
主键约束
注意：
1、一张表只能有一个字段为主键。
2、主键就是表中记录的唯一标识。
3、自增长只能用在主键上
- 创建表时，添加主键约束
create table 表名(
字段名 字段类型 primary key
);
- 创建后，如何添加主键约束
alter table 表名 modify 字段名 字段类型 primary key;
- 创建后，如何删除主键约束
alter table 表名 drop primary key;
- 自动增长
如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长- 创建表时，添加主键约束，并且完成主键自增长
create table 表名( 字段名 字段类型 primary key auto_increment );- 创建后，如何添加自动增长
alter table 表名 modify 字段名 字段类型 auto_increment;- 创建后，如何删除自动增长
alter table 表名 modify 字段名 字段类型;
设置默认值
create table 表名(
字段名 字段类型 default 默认值
);
外键约束
- 简单例子
-- 部门表 和 员工表 （1 对 多的关系）
-- 部门表（主表：一方，用来约束别人的表）
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 员工表(从表：多方，被别人约束的表)
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
foreign key (dep_id) references department(id)
)
- 创建表时，添加外键约束
create table 从表名(
外键字段名 字段类型, -- 外键对应主表的主键
[constraint] [外键名称] foreign key (外键列名称)
references 主表名称(主表列名称)
);
- 创建后，如何添加外键约束
alter table 从表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
- 创建后，如何删除外键约束
alter table 表名 drop foreign key 外键名称;
- 级联操作
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
注意：只能是创建表的时候创建级联关系。
-- 员工表
create table employee4(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk2 foreign key (dep_id) references department(id) on update cascade on delete cascade
)
| 级联操作语法 | 描述 |
|---|---|
| on update cascade | 级联更新，更新主表中的主键，从表中的外键列也自动同步更新 |
| on delete cascade | 级联删除 |
二、数据库的设计
1、表与表之间的关系
表与表之间的三种关系：
- 1、一对多：最常用的关系 部门和员工
- 键表原则：在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
- 2、多对多：学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程
- 键表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
- 3、一对一：相对使用比较少，可以创建成一张表。公民表 护照表
- 建表原则：可以在任意一方添加唯一外键指向另一方的主键
2、数据规范化（三大范式）
- 设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求
- 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
| 范式 | 特点 |
|---|---|
| 1NF | 原子性：表中每列不可再拆分。 |
| 2NF | 不产生局部依赖，一张表只描述一件事情 |
| 3NF | 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。 |
三、数据库的备份和还原
-
命令行方式：
- 备份： mysqldump -u用户名 -p密码 需要备份的数据库名称 > 保存的路径
- 还原：登录-->创建-->使用（use 数据库名称）--->执行文件（ source 文件路径）
-
图形化工具
四、多表查询
- 笛卡尔积
- 有两个集合A,B 取这两个集合的所有组成情况
- 要完成多表查询，需要消除无用的数据
1、多表查询的分类
graph LR
A[多表查询] -->B[内连接]
A[多表查询] -->C[外连接]
B --> D[隐式内连接]
B --> E[显式内连接]
C --> F[左外连接]
C --> G[右外连接]
隐式内连接
看不到 join 关键字，条件使用 where 指定
- 语法
select 字段名 from 表1, 表2 where 条件;
显式内连接
使用 inner join ... on 语句, 可以省略 inner
- 语法
select 字段列表 from 表1 [inner] join 表2 on 条件;
左外连接
查询的是左表所有数据以及其交集部分
- 语法
select 字段列表 from 表1 left [outer] join 表2 on 条件;
右外连接
查询的是右表所有数据以及其交集部分
- 语法
select 字段列表 from 表1 right [outer] join 表2 on 条件;
子查询
查询中嵌套查询，称嵌套查询为子查询。
子查询的不同情况：
- 1、子查询的结果是单行单列的时候
子查询的结果是一个值的时候，可以作为条件，使用运算符去判断。 运算符： > >= < <= =
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）
- 2、子查询结果是多行单列的时候
结果集类似于一个数组，父查询使用 in 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
- 3、子查询的结果是多行多列的时候
子查询可以作为一张虚拟表参与查询
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
总结一下：
- 1、子查询结果是单列，则在 where 后面作为条件
- 2、子查询结果是多列，则在 from 后面作为表进行二次查询
五、事务
如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
1、事务提交方式
- 修改事务的默认提交方式
1、查看事务的默认提交方式:
SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
2、修改默认提交方式：
set @@autocommit = 0;
- 手动提交事务 （Oracle数据库默认是手动提交事务）
- 首先要开启事务，再提交
| 功能 | SQL语句 |
|---|---|
| 开启事务 | start transaction; |
| 提交事务 | commit; |
| 回滚事务 | rollback; |
- 手动提交事务使用过程
graph TD
A[开启事务] --> B[执行SQL语句]
B --> | 成功 | C[提交事务]
B --> | 失败 | D[回滚事务]
- 自动提交事务（mySQL默认自动提交）
- 一条DML(增删改)语句会自动提交一次事务。
2、事务的四大特征
-
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
-
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
-
- 隔离性：多个事务之间。相互独立。
-
- 一致性：事务操作前后，数据总量不变
3、事务的隔离级别（了解）
多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题
-
- 脏读：一个事务，读取到另一个事务中没有提交的数据
-
- 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
-
- 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别:
-
- read uncommitted：读未提交
- 产生的问题：脏读、不可重复读、幻读
-
- read committed：读已提交 （Oracle默认）
- 产生的问题：不可重复读、幻读
-
- repeatable read：可重复读 （MySQL默认）
- 产生的问题：幻读
-
- serializable：串行化
- 可以解决所有的问题
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
- 数据库查询隔离级别
select @@tx_isolation;
- 数据库设置隔离级别
set global transaction isolation level 级别字符串;
六、DCL (管理用户，授权)
1、管理用户
- 查询用户
1、切换到mysql数据库：use mysql;
2、查询user表：select * from user;
注：% 表示可以在任意主机使用用户登录数据库
- 添加用户
create user '用户名'@'主机名' identified by '密码';
- 删除用户
drop user '用户名'@'主机名';
- 修改用户密码(2种方式)
update user set password = password('新密码') where user = '用户名';
set password for '用户名'@'主机名' = password('新密码');
- mysql中忘记了root用户的密码？
1、管理员运行该cmd ---> net stop mysql --停止mysql服务
2、使用无验证方式启动mysql服务：mysqld --skip-grant-tables
3、打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4、use mysql
5、update user set password = password('你的新密码') where user = 'root';
6、关闭两个窗口
7、打开任务管理器，手动结束mysqld.exe 的进程
8、启动mysql服务，使用新密码登录
2、权限管理
- 查询权限
show grants for '用户名'@'主机名';
- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给用户授予所有权限，在任意数据库的任意表上
GRANT ALL ON *.* TO '用户名'@'主机名';
- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
