下载安装

网址：https://dev.mysql.com/downloads/mysql/
- 说明1：windows系统，选安装器下载，选文件最大的
- 说明2：macos系统，选mysql-8.0.33-macos13-arm64.dmg
win：第一步选developer default(默认开发者)，点execute一键配置，然后点next下一步
connectivity这一块功能，默认3306端口，如果防火墙有开启，勾选open windows firewall ports for network access进入白名单
默认账户root，设置下密码
要求输入密码，再点checked验证一下
安装完毕后，搜索框输入mysql，出现mysql自己的命令提示符

安装navicat

图形化数据库开发工具，用来存储数据，选premium完整版
下载方式懂得都懂

修改密码

默认账号root，设置新密码建议复杂一些，在找个地方存起来
1
2
flush privileges;
alter user root@localhost identified by '新密码';

数据模型

mysql是一种关系型数据库，通过使用关系型数据库，可以很好地存储带有一定联系的数据
通过构建一个er图，我们就能很好地理清不同数据模型之间的关系和特点

规范

第一范式（1NF）

第一范式是指数据库的每一列都是不可分割的基本数据项，而下面这样的就存在可分割的情况
- 学生（姓名，电话号码）
电话号码实际上包括了家用座机电话和移动电话，因此它可以被拆分为：
- 学生（姓名，座机号码，手机号码）
满足第一范式是关系型数据库最基本的要求！

第二范式（2NF）

第二范式要求表中必须存在主键，且其他的属性必须完全依赖于主键，比如：
- 学生（学号，姓名，性别）
学号是每个学生的唯一标识，每个学生都有着不同的学号，因此此表中存在一个主键，并且每个学生的所有属性都依赖于学号，学号发生改变就代表学生发生改变，姓名和性别都会因此发生改变，所有此表满足第二范式

第三范式（3NF）

在满足第二范式的情况下，所有的属性都不传递依赖于主键，满足第三范式
- 学生借书情况（借阅编号，学生学号，书籍编号，书籍名称，书籍作者）
实际上书籍编号依赖于借阅编号，而书籍名称和书籍作者依赖于书籍编号，因此存在传递依赖的情况，我们可以将书籍信息进行单独拆分为另一张表：
- 学生借书情况（借阅编号，学生学号，书籍编号）
- 书籍（书籍编号，书籍名称，书籍作者）
这样就消除了传递依赖，从而满足第三范式

BCNF

BCNF作为第三范式的补充，假设仓库管理关系表为StorehouseManage(仓库id, 存储物品id, 管理员id, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
- (仓库id, 存储物品id) →(管理员id, 数量)
- (管理员id, 存储物品id) → (仓库id, 数量)
所以，(仓库id, 存储物品id)和(管理员id, 存储物品id)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
- (仓库id) → (管理员id)
- (管理员id) → (仓库id)
即存在关键字段决定关键字段的情况，如果修改管理员id，那么就必须逐一进行修改，所以其不符合BCNF范式

DDL定义语言

sql数据类型

字符串存储，通常用varchar
- char(n)：可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充。
- varchar(n)：也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充。
数据存储，通常用int
- smallint：用于存储小的整数，范围在 (-32768，32767)
- int：用于存储一般的整数，范围在 (-2147483648，2147483647)
- bigint：用于存储大型整数，范围在 (-9,223,372,036,854,775,808， 9,223,372,036,854,775,807)
- float：用于存储单精度小数
- double：用于存储双精度的小数
时间存储：
- date：存储日期
- time：存储时间
- year：存储年份
- datetime：用于混合存储日期+时间

创建数据库与表

创建数据库；新版本默认utf8mb4，老版本为了能够支持中文，创建时需要设定编码格式
1
2
3
create database 数据库名

create database if not exists 数据库名 default charset utf8 collate utf8_general_ci;
idea提示小写，navicat提示大写，真的是日了狗╮(╯▽╰)╭
删除数据库
1
drop database 数据库名

创建表

create table 表名(
      列名 数据类型 列级约束条件,
      列名 数据类型 列级约束条件,
      ...
      表级约束条件
)

试一试创建三张表

学生表：用于存放所有学生的数据，学生（学号，姓名，性别）
教师表：用于存放所有教师的数据，教师（教师号，姓名，性别）

授课表：用于存放教师与学生的授课信息，授课（学号，教师号）

# 创建学生与老师表
create table student(
    sid int primary key,
    name varchar(10) not null,
    sex enum('男', '女') not null default '男'
);

create table teacher(
    tid int primary key,
    name varchar(10) not null
);

# 创建授课表，添加约束条件
create table study.teach(
    tid int not null,
    sid int not null,
    constraint f_tid foreign key (tid) references study.teacher(tid),
    constraint f_sid foreign key (sid) references study.student(sid)
);

约束条件

列级约束有六种：主键primary key、外键foreign key、唯一 unique、检查 check（mysql不支持）、默认default、非空/空值not null/null

表级约束有四种：主键、外键、唯一、检查

1	[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

修改与删除表

通过add来添加一个新的列，通过drop来删除一个列，不过我们可以添加restrict或cascade，默认是restrict，表示如果此列作为其他表的约束或视图引用到此列时，将无法删除，而cascade会强制连带引用此列的约束、视图一起删除。还可以通过alter来修改此列的属性

alter table 表名[add 新列名 数据类型[列级约束条件]]
                [drop column 列名[restrict|cascade]]
                [alter column 列名 新数据类型]

# 老师表，添加性别数据
alter table teacher add sex enum('男', '女') not null default '男';

删除表；restrict和cascade跟上面效果一致

drop table 表名[restrict|cascade]

# 创建个test表，在删除试试
drop table test;

DML操纵语言

插入数据

插入单条或多条数据

insert into 表名 values(值1, 值2, 值3)
insert into 表名(列名1, 列名2) values(值1, 值2)
insert into 表名(列名1, 列名2) values(值1, 值2), (值1, 值2), (值1, 值2)

# 示例
insert into student values(202301, '小方', '女');
insert into student(sid, name) values(202302, '小强');
insert into student values(202303, '小红', '女'), (202304, '小丽', '女'), (202305, '小王', '男');

修改与删除数据

不加while会修改全部，sql语句中的等于判断是=

update 表名 set 列名=值,... where 条件

# 示例
update student set name='小刚' where sid=202301;
update student set sex='女';

删除符合条件的数据

delete from 表名 where 条件

# 示例
delete from student where sid=202301;

DQL查询语言

单表查询

只需要在一张表中去查找数据

# 查询所有的列数据
select * from 表名

# 指定查询某一列数据
select 列名[,列名] from 表名

# 只查询不重复的值
select distinct 列名 from 表名

# 会以别名显示此列
select 列名 别名 from 表名

# 示例
select * from student;
select name,sex from student;
select distinct sex from student;

限定查询目标；%03表示结尾包含03，03%表示开头包含03，%03%表示任意位置包含03

select * from 表名 where 条件

# 示例
select * from student where sex='女';
select * from student where name not in ('小强', '小王');
select * from student where sid not like '%03';
select * from student where sid like '2023%';
select * from student where sid like '%2023%';
select * from student where sid like '%04' and sex='女';
select * from student where sid like '%04' or sex='女';

常用查询条件
- 一般的比较运算符，包括=、>、<、>=、<=、!=等。
- 是否在集合中：in、not in
- 字符模糊匹配：like，not like
- 多重条件连接查询：and、or、not

排序查询

ASC表示升序，DESC表示降序，默认升序

select * from 表名 where 条件 order by 列名 asc|desc

# 示例
select * from student order by sid desc;

添加多个排序；先按sex排序，再按sid排序，asc可以不写，不写表示默认asc

select * from 表名 where 条件 order by 列名1 asc|desc, 列名2 asc|desc

# 示例
select * from student order by sex asc, sid desc;

聚集函数

聚集函数一般用作统计
- count([distinct]*)：统计所有的行数（distinct表示去重再统计，下同）
- count([distinct]列名)：统计某列的值总和
- sum([distinct]列名)：求一列的和（注意必须是数字类型的）
- avg([distinct]列名)：求一列的平均值（注意必须是数字类型）
- max([distinct]列名)：求一列的最大值
- min([distinct]列名)：求一列的最小值

聚集函数格式

1	select count(distinct 列名) from 表名 where 条件

分组和分页查询

通过group by来对查询结果进行分组，它需要结合聚合函数一起使用

select sum(*) from 表名 where 条件 group by 列名

# 按sex分组，并显示sex列表
select count(*), sex from student group by sex;

我们还可以添加having来限制分组条件

select sum(*) from 表名 where 条件 group by 列名 having 约束条件

# 按sex分组，并显示sex列表，只保留男的这一组
select count(*), sex from student group by sex having sex='男';

我们可以通过limit来限制查询的数量，只取前n个结果

select * from 表名 limit 数量

# 查询3个列表
select * from student limit 3;

我们也可以进行分页

select * from 表名 limit 起始位置,数量

# 从1后面开始查询，查询3个列表
select * from student limit 1, 3;

多表查询

查询的两个或多个表；如果两个表都有相同的属性，可以添加表名.属性指明是哪一个表的属性

select * from 表1, 表2 where 条件

# 示例
select * from student, teacher where tid=1;
select * from student, teacher where teacher.name='幽蓝';

自身连接查询

自身连接，就是将表本身和表进行笛卡尔积计算，得到结果，但是由于表名相同，因此要先起一个别名；其实自身连接查询和前面的是一样的，只是连接对象变成自己和自己了
1
select * from 表名别名1, 表名别名2
自己查自己，用途暂不明

外连接查询

通过使用inner join进行内连接，只会返回两个表满足条件的交集部分

1 2	# 查询全部老师对应的学生 select * from student inner join teach t on student.sid = t.sid;

通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已，这里就不再介绍了）
1
2
# 除了上述功能外，还能查询未分配老师的学生
select * from student left join teach t on student.sid = t.sid;

查询三张表

1 2	select * from student left join teach t on student.sid = t.sid left join teacher t2 on t.tid = t2.tid;

嵌套查询

可以将查询的结果作为另一个查询的条件；比如，查找某教师所有学生的SQL语句

select * from 表名 where 列名 = (select 列名 from 表名 where 条件)

# 示例
select * from student where sid in (
    select sid from teach where tid in (
    select tid from teacher where name='椰奶'));

DCL控制语言

创建用户

可以通过@来限制用户登录的登录IP地址，%表示匹配所有的IP地址，默认使用的就是任意IP地址
1
2
3
4
create user 用户名 identified by 密码;

# 示例
create user test identified by 'h&bZVWmQ0Iw7YaqK';
2 可以通过@来限制用户登录的登录IP地址，%表示匹配所有的IP地址，默认使用的就是任意IP地址

登录用户

首先需要添加一个环境变量，然后我们通过cmd去登陆mysql
1
login -u 用户名 -p
能否访问所有数据库
1
show databases;

用户授权

all表示授予所有权限，当数据库和表为*，表示所有的数据库和表都授权。如果在最后添加了with grant option，获得授权的用户还能把得到的权限授权给其他用户

grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]

# study库授予所有权限给test用户
grant all on study.* to test;

# 允许test用户访问study库下student表，且只允许修改student表下的name属性
grant select, update(name) on study.student to test;

收回权限

revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

# 收回test用户所有权限
revoke all on study.* from test;

视图

视图本质上是个查询结果，可以按照我们想要的样子查看数据，真正的数据存放在原来的表中，因此你在视图上进行操场，也会影响到原来的表

with check option是指创建后，如果更新视图中的数据，是否满足子查询中的条件表达式，不满足将无法插入

create view 视图名称(列名) as 子查询语句 [with check option];

# 示例
create view test as select * from student where sex='女';
create view test as select * from student where sex='女' with check option;

视图不允许更新规则
- 若视图是由两个以上基本表导出的，则此视图不允许更新
- 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作
- 若视图的字段来自集函数，则此视图不允许更新
- 若视图定义中含有GROUP BY子句，则此视图不允许更新
- 若视图定义中含有DISTINCT短语，则此视图不允许更新
- 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC：CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的
- 一个不允许更新的视图上定义的视图也不允许更新
通过drop来删除一个视图
1
drop view 视图名称;

索引

在数据量变得非常庞大时，通过创建索引，能够大大提高我们的查询效率，就像Hash表一样，它能够快速地定位元素存放的位置，我们可以通过下面的命令创建索引
1
2
3
4
5
6
7
# 创建索引
create index 索引名称 on 表名 (列名)
# 查看表中的索引
show index from student

# 示例
create index i on student(name);
删除一个索引
1
drop index 索引名称 on 表名
虽然添加索引后会使得查询效率更高，但是我们不能过度使用索引，索引为我们带来高速查询效率的同时，也会在数据更新时产生额外建立索引的开销，同时也会占用磁盘资源

触发器

触发器就像其名字一样，在某种条件下自动触发，在select/update/delete时，自动执行我们预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活；触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）

比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中

create trigger 触发器名称 [before|after] [insert|update|delete] on 表名/视图名 for each row delete from student where student.sno = new.sno

# 学生表上设置触发器t，一旦删除学生数据，授课表也会删除相同的数据
create trigger t before delete on student for each row delete from teach where old.sid = teach.sid;

for each row表示针对每一行都会生效，无论哪行进行指定操作都会执行触发器！

查看触发器，用navicat软件查看比较好
1
show triggers

删除触发器

drop trigger 触发器名称

# 示例
drop trigger t;

事务

当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！只有Innodb引擎支持事务
查看支持的引擎
1
show engines;
MySQL默认采用的是Innodb引擎，我们也可以去修改为其他的引擎

事务特性：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
一致性：事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

begin;                #开始事务
...
rollback;             #回滚事务
savepoint 回滚点;      #添加回滚点
rollback to 回滚点;    #回滚到指定回滚点
...
commit;               #提交事务
# 一旦提交，就无法再进行回滚了！

高级部分暂时跳过