【数据库】Mysql精简回顾复习

一、概念

• 数据库（DB）：数据存储的仓库
• 数据库管理系统（DBMS）：操纵和管理数据库的大型软件
• SQL：操作关系型数据库的编程语言，是一套标准
• 关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

二、DDL数据库操作

1.查询

查询所有数据库            show databases();

查询当前数据库            select database();

2.创建

create database if not exists+数据库名+default charset(字符集) + collate(排序规则);

3.删除

 drop database if exists + 数据库名;

4.使用

use + 数据库名;

三、DDL表操作--查询

1.查询当前数据库所有表

show tables;

2.查询表结构

desc + 表名;

3.查询指定表的建表语句

show create table + 表名;

四、DDL表操作--创建

create table + 表名（

字段1 + 字段1类型 + comment + 字段1注释;

...

字段n + 字段n类型 + comment + 字段n注释;

）comment 表注释;

五、DDL表操作--修改

1.添加字段

alter+table+表名+add+字段名+类型（长度）+comment+注释; 

2.修改数据类型

alter+table+表名+modify+字段名+新数据类型（长度）;

3.修改字段名和字段类型

alter+table+表名+change+旧字段名+新字段名+类型（长度）+comment+注释;

4.删除字段

alter+table+表名+drop+字段名;

5.修改表名

alter+table+表名+rename to + 新表名;

6.删除表

drop+table(if  exists)+表名;

7.删除指定表，并重新创建该表

truncate + table + 表名;

六、DML--添加数据

1.给指定字段添加数据

insert into+表名（字段名1，字段名2）+ values(值1，值2);

2.给全部字段添加数据

insert into+表名+ values(值1，值2);

注意：插入时字符串和日期型数据应该包含在引号中

七、DML--修改数据

update + 表名 + set + 字段名1 = 值1，字段名2 = 值2，... ，where + 条件;

八、DML--删除数据

delete from + 表名 + where + 条件;

九、DQL--基本查询

1.查询多个字段

select 字段1，字段2，字段3 ... from 表名;

select * from + 表名;

2.设置别名

select 字段1 as 别名1 from 表名;

3.去重

select distinct 字段列表 + from + 表名 ;

十、DQL--条件查询

select + 字段列表 + from + 表名 + where + 条件列表(like + 占位符);

十一、DQL--聚集函数

注意：聚集函数只能用于select子句和group by中的having子句

• count
• max
• min
• avg
• sum

select + 聚集函数（字段列表）+ from + 表名;

十二、DQL--分组查询

select + 字段列表 + from + 表名 + where条件 + group by分组字段名 + having 分组后过滤条件;

//查询平均成绩大于等于90分的学生学号和平均成绩Select Sno,AVG(Grade)
from SC group by Sno
having AVG(Grade) >= 90;

十三、DQL--排序查询

• asc  升序（默认）
• desc 降序

select + 字段列表 + from + 表名 + order by + 字段1  排序方式1，字段2  排序方式2;

十四、DQL--分页查询

select + 字段列表 + from + 表名 + limit + 起始索引，查询记录数;

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数

十五、DCL--管理用户

1.查询用户

use mysql;

select * from user;

2.创建用户

create user '用户名'  @  ‘主机名’  identified by '密码';

3.修改用户密码

alter user '用户名'  @  ‘主机名’  identified with mysql_native_password by '新密码';

4.删除用户

drop user '用户名'  @  ‘主机名’

十六、DCL--权限控制

1.查询权限

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

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名' @ ‘主机名’ ；

3.撤销权限

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

十七、外键约束

1.添加外键

create table 表名（

字段名 数据类型，

...

[constraint]  [外键名称] foreign key (外键字段名) references 主表（主表列名）

）;

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

2.删除外键

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

3.删除/更新行为

no action / restrict

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新

cascade

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则强制删除/更新外键在子表中的记录

set null

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null

set default

父表有变更时，子表将外键列设置成一个默认的值

十八、多表查询--笛卡尔积

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

十九、连接查询--内连接

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

1.隐式内连接

select 字段列表 from 表1，表2  where 条件 ... ;

例：select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

2.显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件;

例：select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

二十、连接查询--外连接

1.左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件... ;

相当于表A的所有数据以及包含表A和表B交集部分的数据

2.右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件 ... ;

相当于表B的所有数据以及包含表A和表B交集部分的数据

二十一、连接查询--自连接

可以是内连接，也可以是外连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...；

二十二、联合查询--union,union all

就是将多次查询的结果合并起来，形成一个新的查询结果集

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

select 字段列表 from 表A ...

union [all]

select 字段列表 from 表B ... ;

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

二十三、子查询

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

1. 标量子查询：返回的结果是单个值
2. 列子查询：返回的结果是一列（可以是多行）
3. 行子查询：返回的结果是一行（可以是多列）
4. 表子查询：返回的结果是多行多列

二十四、带有exists谓词的子查询

其不返回任何数据，只产生逻辑真值“true”，或逻辑假值“false”

//例：查询所有选修了1号课程的学生姓名select Sname
from Student
where exists(
select *
from SC
where Sno = Student.Sno  AND  Cno = '1'
);

//例：查询选修了全部课程的学生姓名select Sname 
from Student
where not exists(
select *
from Course
where not exists
(select *
from SC
where Sno = Student.Sno  AND  Cno = Course.Cno
)
);

二十五、事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体，一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

1.事务操作（其一）

1.查看/设置事务的提交方式

select @@autocommit;

set @@autocommit = 0;

2.提交事务

commit;

3.回滚事务

rollback;

2.事务操作（其二）

1.开启事务

start transaction    /   begin

2.提交事务

commit;

3.回滚事务

rollback;

3.事务四大特性

• 原子性：事务是不可分割的最小单元
• 一致性：事务完成时，必须使所有的数据都保持一致状态
• 隔离性：保证事务在不受外部并发操作影响的独立环境下运行
• 持久性：事务一旦提交或回滚，它对数据的改变是永久的

二十六、索引

是帮助数据库高效获取数据的数据结构

1.结构

默认为B+ Tree结构组织的索引

B+ Tree（多路平衡搜索树）

1. 每一个节点，都可以存储多个key（有n个key，就有n个指针）
2. 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据
3. 叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

2.语法

（1）创建索引

create [unique] index 索引名 on 表名 （字段名，...）;

（2）查看索引

show index from 表名;

（3）删除索引

drop index 索引名 on 表名;

注意：

在建表时，主键字段会自动创建主键索引

添加唯一约束时，数据库实际上会添加唯一索引

---