文章目录

一、SQL1. 什么是SQL？2. SQL通用语法3. SQL分类 二、DDL：操作数据库、表1. 操作数据库（CRUD）1. C（Create）：创建2. R（Retrieve）：查询3. U（Update）：修改4. D（Delete）：删除5. 使用数据库 2. 操作表（CRUD）1. MySQL常用数据类型2. C（Create）：创建3. R（Retrieve）：查询4. U（Update）：修改5. D（Delete）：删除 三、DML：增删改表中的数据1. 添加数据2. 删除数据3. 修改数据 四、DQL：查询表中的数据1.语法2. 基础查询3. 条件查询1. 常用运算符2. 使用示例 4. 模糊查询（like）1. 常用占位符2. 使用示例 5. 聚合函数1. 常用聚合函数2. 使用示例 6. 排序查询1. 语法2. 排序方式3. 使用示例 7.分组查询1. 语法2. 使用示例3. 重点：where 与 having 的区别 8. 分页查询1. 语法2. 使用示例 9. 内连接查询1. 隐式内连接2. 显式内连接3. 使用示例 10. 外连接查询（多表查询）1. 左外连接2. 右外连接3. 使用示例 11. 子查询（多表查询）1. 子查询的结果为单行单列2. 子查询的结果为多行单列3. 子查询的结果为多行多列4. 使用示例 五、DCL：管理用户及相关授权1. 添加用户2. 删除用户3. 查询用户4. 修改用户密码5. 查询权限6. 授予权限7. 撤销权限 重点：MySQL 中忘记了 root 用户的密码？六、约束1. 非空约束（not null）2. 唯一约束（unique）3. 主键约束（primary key）4. 外键约束（foreign key） 七、表设计1. 多表之间的关系实现1. 一对一2. 一对多/多对一3. 多对多 2. 数据库设计的范式1. 几个概念2. 第一范式（1NF）3. 第二范式（2NF）4. 第三范式（3NF） 八、事务1. 概念2. 操作3. 事务提交的两种方式4. 事务的四大特征5. 事务的隔离级别 九、数据库的备份与还原1. 备份2. 还原

一、SQL

1. 什么是SQL？

SQL（Structured Query Language）：结构化查询语言。定义了操作所有关系型数据库（如：MySQL、Oracle）的规则，不同的数据库之间会存在不一样的地方。

2. SQL通用语法

1. SQL语句可单行或单行书写，分号结尾 2. 可使用空格与缩进来增强语句的可读性 3. MySQL数据库中的SQL语句不区分大小写 4. 注释： * 单行注释：-- 注释内容**（ -- 后必须加一个空格）** * 多行注释：/* 注释内容 */

3. SQL分类

DDL（Data Definition Language）：数据定义语言。用来操作数据库、表。

DML（Data Manipulation Language）：数据操作语言。用来增删改表中的数据。

DQL（Data Query Language）：数据查询语言。用来查询表中的数据。

DCL（Data Control Language）：数据控制语句。用来管理用户及相关授权。

二、DDL：操作数据库、表

1. 操作数据库（CRUD）

1. C（Create）：创建

创建数据库 create database 数据库名称; 创建数据库并判断是否存在，不存在则创建（防止因数据库已存在，创建时报错影响后续操作） create database if not exists 数据库名称; 创建数据库并指定字符集 create database 数据库名称 character set 字符集名;

示例：创建 db 数据库，判断是否存在，并指定字符集为 gbk

create database if not exists db character set gbk;

2. R（Retrieve）：查询

查询所有数据库的名称 show databases; 查询某个数据库的创建语句（主要用来查询该数据库的字符集） show create database 数据库名称;

3. U（Update）：修改

修改数据库的字符集 alter database 数据库名称 character set 字符集名称;

4. D（Delete）：删除

删除数据库 drop database 数据库名称; 判断数据库存在，再删除数据库 drop database if exists 数据库名称;

5. 使用数据库

使用数据库 use 数据库名称; 查询当前正在使用的数据库 select database();

2. 操作表（CRUD）

1. MySQL常用数据类型

int：整数类型

double：小数类型

例：score double(5,2) 表示该数一共 5 位，小数点后有 2 位，如 999.99

date：日期类型（只包含年月日 YYYY-MM-dd）

datetime：日期类型（包含年月日时分秒 YYYY-MM-dd HH:mm:ss）

timestamp：时间戳类型（包含年月日时分秒，如果不给该类型字段赋值，或赋值为 null，则默认使用当前系统时间）

varchar：字符串类型

例：name varchar(20) 表示最多 20 个字符的字符串

2. C（Create）：创建

创建表 create table 表名( 列名1 数据类型1, 列名2 数据类型2, .... ........ 列名n 数据类型n ); 复制表 create table 表名 like 被复制的表名;

3. R（Retrieve）：查询

查询某个数据库中所有的表名 show tables; 查询表结构 desc 表名;

4. U（Update）：修改

修改表名 alter table 表名 rename to 新的表名; 修改表的字符集 alter table 表名 character set 字符集名称; 添加一列（字段） alter table 表名 add 列名 数据类型; 修改列名、数据类型 alter table 表名 change 列名 新列名 新数据类型; 仅修改数据类型 alter table 表名 modify 列名 新数据类型; 删除列 alter table 表名 drop 列名;

5. D（Delete）：删除

删除表 drop table 表名; 判断表存在，再删除表 drop table if exists 表名;

三、DML：增删改表中的数据

1. 添加数据

insert into 表名(列名1,列名2,...,列名n) values (值1,值2,...,值n);

注意：

列名与值要一一对应如果表名后不加列名，默认会给所有列添加值 insert into 表名 values (值1,值2,...,值n); 除了数字类型，其他数据类型数据都要用引号（单双皆可）引起来

2. 删除数据

delete from 表名 [where 条件]; -- []表示可选操作

注意：

如果不加 where 条件，则会删除表中所有记录（有多少条记录执行多少次删除操作，效率较低） delete from 表名; 删除表中所有记录（先删除当前表，再创建一张一模一样的空表，效率较高） truncate table 表名;

3. 修改数据

update 表名 set 列名1=值1,列名2=值2,...,列名n=值n [where 条件]; -- []表示可选操作

四、DQL：查询表中的数据

1.语法

select 字段列表 from 表名列表 where 条件列表 group by 分组条件 having 分组之后的条件 order by 排序 limit 分页限定

2. 基础查询

多个字段的查询 select 字段名1,字段名2,...,字段名n from 表名; 查询所有字段 select * from 表名; 去除重复（两条记录所有字段的值都一样才会去除） select distinct 字段名1,字段名2,...字段名n from 表名;

可以使用四则运算计算一些列的值

示例：查询学生的姓名、数学成绩、英语成绩及数学成绩与英语成绩的和

select name,math,english,math+english from student;

ifnull(表达式1,表达式2)

表达式1：需要被判断是否为 null 的字段名

表达式2：如果字段为 null 则替换为该值

示例：查询学生的姓名、数学成绩，如果数学成绩为 null 则替换为 0（成绩为 null 不合常理）

select name,ifnull(math,0) from student;

起别名（as）

示例：查询学生姓名与数学成绩（别名可不用打引号）

select name [as] 姓名,math [as] 数学 from student; -- as可省略

3. 条件查询

1. 常用运算符

>，<，=，>=，<=，!=，<>between…and…in (集合)like（模糊查询）is null，is not nulland，&&or，||not，!

2. 使用示例

查询学生表中年龄不等于 20 的记录 select * from student where age!=20; -- 方式一 select * from student where age<>20; -- 方式二 查询学生表中年龄大于等于 20，小于等于 30 的记录 select * from student where age>=20 and age<=30; -- 方式一 select * from student where age between 20 and 30; -- 方式二 查询学生表中年龄为 18，22，25 的记录 select * from student where age=18 or age=22 or age=25; -- 方式一 select * from student where age in (18,22,25); -- 方式二 查询学生表中英语成绩为 null 的记录 select * from student where english=null; -- (×) null值不能用 = 与 != 判断 select * from student where english is null; -- (√)

4. 模糊查询（like）

1. 常用占位符

_：表示单个任意字符%：表示多个任意字符

2. 使用示例

查询学生表中姓氏为马的记录 select * from student where name like '马%'; 查询学生表中姓名第二个字为云的记录 select * from student where name like '_云%'; 查询学生表中姓名为三个字的记录 select * from student where name like '___'; 查询学生表中姓名中包含云的记录 select * from student where name like '%云%';

5. 聚合函数

1. 常用聚合函数

count：计算个数max：计算最大值min：计算最小值sum：计算和avg：计算平均值

2. 使用示例

查询学生表中的纪录总数 -- 聚合函数的计算，一般会排除 null 值，所以一般计算主键 select count(id) from student; -- 方式一 select count(*) from student; -- 方式二 -- 也可使用 ifnull() 函数转换为非 null 值再参与计算 select count(ifnull(math,0)); -- 方式三 查询学生表中数学成绩的平均分 select avg(math) from student;

6. 排序查询

1. 语法

-- 多个排序条件，靠前优先级高 order by 排序字段1 排序方式1,排序字段2 排序方式2,...

2. 排序方式

asc：升序（不指定排序方式，默认为升序）desc：降序

3. 使用示例

查询学生信息，按照数学成绩升序排名，如果数学成绩一样，则按照英语成绩降序排名 select * from student order by math [asc],english desc; -- asc可省略

7.分组查询

1. 语法

group by 分组字段

2. 使用示例

对学生表按性别分组，分别查询男、女生的数学平均分、人数，要求分数低于 70 分的不参与分组，分组后组内人数要大于 2 人 select sex,avg(math),count(id) 人数 -- 给count起别名为人数 from student where math>70 group by sex having 人数>2; -- 用别名代替count(id)参与条件判断

3. 重点：where 与 having 的区别

1. where 在分组之前进行限定，如果条件不满足，则不参与分组 having 在分组之后进行限定，如果条件不满足，则结果不会被查询出来 2. where 后不可跟聚合函数 having 后可以进行聚合函数的判断 总的来说：where 是限定条件，having 是过滤结果

8. 分页查询

1. 语法

-- 开始的索引 = (当前的页码 - 1) * 每页显示的记录条数 limit 开始的索引,每页显示的记录条数

2. 使用示例

每页显示 3 条记录 select * from student limit 0,3; -- 第一页 select * from student limit 3,3; -- 第二页

9. 内连接查询

1. 隐式内连接

select 字段列表 from 表名列表 where 条件列表;

2. 显式内连接

select 字段列表 from 表名1 [inner] join 表名2 on 条件; -- inner可省略

3. 使用示例

查询所有员工信息与对应的部门信息 select * from emp,dept where emp.dept_id=dept.id; -- 隐式内连接 select * from emp join dept on emp.dept_id=dept.id; -- 显示内连接 查询员工的姓名、性别与部门的名称 select t1.name,t1.gender,t2.name -- 别名在SQL语句中的任意位置都可以使用 from emp t1, -- 起别名 dept t2 where t1.dept_id=t2.id;

10. 外连接查询（多表查询）

1. 左外连接

-- 查询左表所有数据以及两张表的交集 select 字段列表 from 表1 left [outer] join 表2 on 条件; -- outer可省略

2. 右外连接

-- 查询右表所有数据以及两张表的交集 select 字段列表 from 表1 right [outer] join 表2 on 条件; -- outer可省略

3. 使用示例

查询所有员工信息，如果员工有所属部门，则查询部门名称，如果没有，则不显示 select t1.*,t2.name from emp t1 left join dept t2 on t1.dept_id=t2.id;

11. 子查询（多表查询）

1. 子查询的结果为单行单列

子查询可以作为条件，使用运算符 >，<，=，>=，<= 判断

2. 子查询的结果为多行单列

子查询可以作为条件，使用运算符 in 判断

3. 子查询的结果为多行多列

子查询可以作为一张虚拟表

4. 使用示例

1. 查询员工工资小于平均工资的记录 -- 子查询结果为单行单列 select * from emp where emp.salary<(select avg(salary) from emp); 查询财务部与市场部所有员工信息 -- 子查询结果为多行单列 select * from emp where dept_id in (select id from dept where name in ('财务部','市场部')); 查询员工入职日期为 2020-09-05 之后的员工信息与部门信息 -- 子查询结果为多行多列 select * from dept t1, (select * from emp where emp.join_date>'2020-09-05') t2 where t1.id=t2.dept_id; -- 隐式内连接方式查询 select * from emp t1, dept t2 where t1.dept_id=t2.id and t1.join_date>'2020-09-05';

五、DCL：管理用户及相关授权

1. 添加用户

-- 用户存储在 mysql 数据库的 user 表中 create user '用户名'@'主机名' identified by '密码';

2. 删除用户

drop user '用户名'@'主机名'; -- 可用通配符 % 表示所有主机

3. 查询用户

select * from user;

4. 修改用户密码

update user set password=password('新密码') where user='用户名'; -- 方式一 set password for '用户名'@'主机名'=password('新密码'); -- 方式二

5. 查询权限

show grants for '用户名'@'主机名';

6. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; grant all on *.* to 'admin'@'%'; -- 给 admin 用户授予所有权限

7. 撤销权限

revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名';

重点：MySQL 中忘记了 root 用户的密码？

1. cmd（管理员方式启动）--> 键入 net stop mysql 回车（停止 MySQL 服务，8.x 版本的为 net stop mysql80） 2. 键入 mysqld --skip-grand-tables 回车（使用无验证方式启动 MySQL 服务） 3. 打开新的 cmd 窗口，键入 mysql 回车登录 4. 键入 use mysql 回车（使用 mysql 数据库） 5. 键入 update user set password=password('新密码') where user='root'; 回车（修改 root 用户密码） 6. 关闭所有 cmd 窗口 7. 打开任务管理器，手动结束 mysql.exe 的进程 8. 启动 mysql 服务 9. 使用新密码登录

六、约束

1. 非空约束（not null）

创建表时添加非空约束 -- 添加了非空约束的字段值不能为 null create table 表名( 字段名 数据类型 not null; ); 给已存在的表中字段添加非空约束 alter table 表名 modify 字段名 数据类型 not null; 删除非空约束 alter table 表名 modify 字段名 数据类型;

2. 唯一约束（unique）

创建表时添加唯一约束 -- 添加了非空约束的字段值不能重复（可以为 null，但最多只能有一条记录该字段为 null） create table 表名( 字段名 数据类型 unique; ); 给已存在的表中字段添加唯一约束 alter table 表名 modify 字段名 数据类型 unique; 删除唯一约束 alter table 表名 drop index 字段名;

3. 主键约束（primary key）

创建表时添加主键约束 -- 添加了主键约束的字段值非空（not null）且唯一（unique） create table 表名( 字段名 数据类型 primary key [auto_increment]; --[]设置主键自增长（相对于上一个数据自增长） ); 给已存在的表中字段添加主键约束 --[]设置主键自增长（相对于上一个数据自增长） alter table 表名 modify 字段名 数据类型 primary key [auto_increment]; 删除主键约束 alter table 表名 drop primary key;

4. 外键约束（foreign key）

创建表时添加外键约束 -- 通过添加外键约束，让表与表之间产生联系，从而保证数据的正确性 create table 表名( 字段名1 数据类型1, 字段名2 数据类型2, ...... ........ 字段名n 数据类型n, [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名) ); -- []为可选操作，如未给出自定义外键名，MySQL会给出唯一外键名 给已存在的表中字段添加主键约束 -- []为可选操作，如未给出自定义外键名，MySQL会给出唯一外键名 alter table 表名 add [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名); 删除外键约束 alter table 表名 drop foreign key 外键名; 级联操作 -- 当添加级联操作后，主表字段值发生变化时，外键关联字段会发生对应变化 -- 在添加外键约束的语句后添加级联操作 on delete cascade -- 级联删除 on update cascade -- 级联更新 -- 示例： alter table 表名 add [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名) [on delete cascade] [on update cascade]; -- []为可选操作

七、表设计

1. 多表之间的关系实现

1. 一对一

关系示例：人与身份证（一个人对应一张身份证，一张身份证对应一个人）

实现方式：在任意一方添加唯一（unique）外键指向另一方主键

2. 一对多/多对一

关系示例：员工与部门（一个员工属于一个部门，一个部门有多个员工）

实现方式：在多的一方建立外键，指向一的一方的主键

3. 多对多

关系示例：学生与老师（一个学生有多名老师，一名老师教多名学生）

实现方式：借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键（这两个字段不能同时重复，需要作为联合主键 primary key(字段1,字段2) ）

2. 数据库设计的范式

1. 几个概念

函数依赖：A -> B，如果通过 A 属性（属性组）的值，可以唯一确定 B 的值，则称 B 依赖于 A（如：学号 -> 姓名；(学号，课程名) -> 分数）完全函数依赖：A -> B，如果 A 是一个属性组，B 属性值的确定需要依赖于 A 属性组中所有的属性值，则称 B 完全依赖于 A（如：(学号，课程名) -> 分数）部分函数依赖：A -> B，如果 A 是一个属性组，B 属性值的确定只需要依赖 A 属性组中某一些值，则称 B 部分依赖于 A（如：(学号，课程名) -> 姓名）传递函数依赖：A -> B，B -> C，如果通过 A 属性（属性组）的值，可以唯一确定 B 属性（属性组）的值，再通过 B 属性（属性组）的值，可以唯一确定 C 属性的值，则称 C 传递依赖于 A （如：学号 -> 系名，系名 -> 系主任）码（候选码）：如果在一张表中，一个属性（属性组），被其他所有属性完全依赖，则称这个属性（属性组）为该表的码主属性（主码）：码属性组中的所有属性非主属性：出了码属性组之外的属性

2. 第一范式（1NF）

每一列都是不可分割的原子数据项

3. 第二范式（2NF）

在 1NF 的基础上，非码属性必须完全依赖于码（在 1NF 的基础上消除非主属性对主码的部分函数依赖）

4. 第三范式（3NF）

在 2NF 的基础上，任何非主属性不依赖于其他非主属性（在 2NF 的基础上消除传递依赖）

八、事务

1. 概念

如果一个包含多个业务步骤的业务操作，被事务管理，则这些操作，要么同时成功，要么同时失败

2. 操作

-- 在开启事务后，回滚或提交之前的操作所导致的数据变化都是临时变化，不影响真实数据（有误，则回滚；无误，则提交） -- 开启事务： start transaction; -- 回滚事务： rollback; -- 提交事务： commit;

3. 事务提交的两种方式

自动提交（MySQL 默认）：每条 DML 语句后都会自动提交一次事务手动提交（Oracle 默认）：需要先开启事务，再提交查看事务默认提交方式 select @@autocommit; -- 1，自动提交；0，手动提交 修改事务默认提交方式 set @@autocommit=0; -- 1，自动提交；0，手动提交

4. 事务的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败持久性：当事务提交或回滚后，数据库会持久化地保存数据隔离性：多个事务之间相互独立一致性：事务操作前后，数据总量不变

5. 事务的隔离级别

概念

多个事务之间有隔离性，相互独立。但多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这些问题

存在问题

脏读：一个事务，读取到另一个事务中还没有提交的数据不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，前一个事务无法查询到自己的修改

隔离级别（从前往后，安全性越来越高，效率越来越低）

read uncommitted：读未提交

产生的问题：脏读、不可重复读、幻读

read committed：读已提交（Oracle默认隔离级别）

产生的问题：不可重复读、幻读

repeatable read：可重复读（MySQL默认隔离级别）

产生的问题：幻读

serializable：串行化

可以解决所有问题

查询数据库隔离级别

select @@tx_isolation; 修改数据库隔离级别 set global transaction isolation level 级别字符串;

九、数据库的备份与还原

1. 备份

mysqldump -u用户名 -p密码 数据库名 > 保存的路径

2. 还原

1.登录数据库 2.创建数据库 3.使用数据库 4.执行文件 source 文件路径