一 SQL#
全称为Structured query Language：结构化查询语言。每种数据库语句，都有属于自己的方言，但基本的命令不变。
mysql 的语句不区分大小写，但是建议
关键字写为大写
- DDL：操作数据库、表
- DML：增删改表的数据
- DQL：查询表的数据
- DCL：授权
二 数据类型#
数据类型整体分为三大类，数值类型，字符串类型，日期类型
1 数值类型：
| 类型 | 大小 (字节) | 说明 |
|---|---|---|
| int/integer | 4 | 数字 |
| bigint | 8 | 数字 |
| smallint | 2 | 数字 |
| tinyint | 1 | 布尔值 |
| double | 8 | 近似存储 |
| double(M,D) | 8 | 近似存储 |
| decimal(M,D) | 8 | 精确存储 |
这里 M 表示最长有几位，D 为小数点后几位
2 字符串类型：
| 类型 | 大小 (字节) | |
|---|---|---|
| char | 0-155 | 定长字符串，如果太短会补充空格。 优点：查询速度夸，缺点：浪费空间 |
| varchar | 0-65535 | 变长字符串 优点：节省空间，缺点：稍微慢点 |
| blob | 0-65535 | 存储二进制数据 |
| mediumblob | 16M | 存储二进制数据 |
| longblob | 4G | 存储二进制数据 |
| text | 0-65535 | 长文本 |
| mediumtext | 16M | 长文本 |
| longtext | 4G | 长文本 |
MYSQL 中 == 每条记录 == 最大字节为 64k (数字，字符串), 文本，二进制文件不包含在内
这个每条记录表示的是一行数据，比如 id+name+age+address 合起来
3 日期类型：
| 类型 | 长度 (字节) | 格式 | 用途 |
|---|---|---|---|
| date | 3 | YYYY-MM-DD | 日期值 |
| time | 3 | HH:MM | 时间值 |
| year | 1 | YYYY | 年 |
| datetime | 8 | YYYY-MM-DD HH:MM | 时间日期 |
| timestamp | 4 | YYYYMMDDHHMMSS | 时间戳 |
三 DDL 语法#
对数据库，以及表的操作
1 操作数据库#
- 创建数据库：
# 创建数据库
create database 数据库名称;
# 如果数据库没有则进行创建，有则不创建
create database if not exists 数据库名称;
# 创建并设置字符集
create database 数据库名称 character set 字符集名称;
- 查询数据库：
show databases;
# 显示并创建
show create database 数据库名称;
- 修改数据库的字符集：
# 修改数据库字符集
alter database 数据库名称 character set 字符集名称;
- 删除数据库
drop database 数据库名称;
# 如果存在则删除
drop database if exists 数据库名称;
- 查询当前正在使用的数据库名称：
select database();
- 使用数据库：
use 数据库名称;
2 操作表#
- 创建表
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
列名2 数据类型2,
......
列名n 数据类型n # 最后一列后面没有分号
);
- 复制表：
create table 新表名 like 表名;
- 查询所有的表：
# 查询所有表
show tables;
# 查询表结构
desc 表名;
- 修改表：
# 修改表名称
alter table 表名 rename to 新表名;
# 修改表的字符集
alter table 表名 character set 字符集名;
# 新增一列
alter table 表名 add 列名 数据类型;
# 修改列
alter table 表名 change 列名 新列名 新数据类型; [[改名字和类型]]
alter table 表名 modify 列名 新数据类型 [[只改列的数据类型]]
- 删除：
# 删除列
alter table 表名 drop 列名;
# 删除表
drop table 表名;
[[删除表前判断]]
drop table if exists 表名;
四 DML 语法#
对表中内容的操作，是我么最常用的语句
1 添加数据#
添加数据：
# 全部列都添加
insert into stu(列名1,列名2....列名n) values (数据1,数据2...数据n);
# 如果全度添加数据，可以不写列名
insert into stu values (数据1,数据2...数据n);
# 如果不需要增加哪一行则可以不书写
# 如果想设置默认值可写为default
2 删除数据#
# 删除数据
delete from 表名 where 条件;
# 清空表
delete from 表名;
# 直接删除表，然后创建一个同名的表，速度更快
truncate table 表名;
3 修改数据#
# 修改数据
update 表名 set 列名1 = 值1,列名2 = 值2, .... where 条件
# 不加where的条件即表示，修改表中所有的指定的数据
update 表名 set 列名1 = 值1,列名2 = 值2, ....
五 DQL 语法#
查询表中的数据，书写顺序如下：
select 字段列表
from 表名列表
where 条件列表
group by 分组条件
having 对排序后的数据进行筛选
order by 进行排序的列
limit 进行分页，limit是mysql的方言
1 基本查询#
# 查询全部
select * from 表名;
# 查询指定的列
select 列名1,列名2... from 表名;
# 查询并去重
select distinct 列名1,列名2... from 表名;
# 两列进行计算
select 列1+列2.... from 表名;
# 上面如果有null值则无法进行正常计算，因此可以使用if null 关键字
select math + if null(english,0) from 表名;
# 查询起别名
select 列1 as 别名, 列2 as 别名..... from 表名;
[[省略as也可以]]
select 列1 别名, 列2 别名....... from 表名;
2 条件查询#
即语句后面加上where字段，然后书写查询的条件
| 比较运算符 | 说明 |
|---|---|
| >、<、<=、>=、=、<> | <>表示不等于，在 mysql 中也可以使用!= |
| between...and | 在一个范围之内，例如：between 100 and 200 即表示：[100，200] |
| in (集合) | 集合表示多个值，使用逗号分隔 |
| like ' 张 %' | 占位符：_表示单个任意字符；%表示多个任意字符；模糊查询 |
| is NULL | 查询某一列为 null 的值，注意：不能写 = null |
| is not NULL | 查询不为 NUll 的值 |
| 逻辑运算符 | 说明 |
|---|---|
| and 或 && | 与，SQL 中建议写前者，后者并不通用 |
| **or 或 | |
| not 或 ！ | 非 |
3 排序查询#
即在order by后加排序
| 关键字 | 说明 |
|---|---|
| 不指定，默认升序 | |
| ASC | 升序 |
| DESC | 降序 |
语句：
多条件排序，就是当第一个条件一样时，进行第二排序
select * from student order by 数学成绩, 英语成绩;
4 聚合函数#
聚合函数：将一列数据作为一个整体，进行纵向的计算，即对列进行计算
| 关键字 | 说明 |
|---|---|
| count（列名）： | 计算个数 |
| max（列名）： | 计算最大值 |
| min（列名）： | 计算最小值 |
| sum（列名）： | 计算和 |
| avg（列名）： | 计算平均值 |
注意：聚合函数会排除null值，如果统计个数，建议不用有空的列
使用：
计算全部人数：
select count(name) from student;
5 分组查询#
即在group by后加分组
如果进行分组，则查询select出来的结果肯定是个整体，因此不能具体到某一列什么的，所以，只能写：分组本身的字段，聚合函数
比如需要查男生，与女生各自的数学平均分，以及各自的人数：
select sex,avg(math) ,count(id) from studnet group by sex;
如果需要在分组后限制则需要having进行筛选
比如需要，筛选出查询后，人数大于 3 人的组：
select sex,avg(math) ,count(id) from studnet group by sex having count(id)>3;
6 分页查询#
分页查询：如果查询的结果非常多，我们想分页进行展示，这就是分页查询，在limit字段书写条件
limit 开始的索引,每页查询的条数
如果需要查询前三个人，且第一页显示 3 个
select * from student limit 0,3; [[第1页]]
select * from student limit 3,3; [[第2页]]
select * from student limit 6,3; [[第3页]]
*因此我们看到一个规律：开始的索引 =（页数 - 1）每页显示的条数
7 多表查询#
其语法与单表查询，基本一致，但是意义不同，首先要介绍下笛卡尔积
- 笛卡尔积：就是几个东西的所有的组合情况，在数据库中如果查询多个表，就行将多个表的数据进行求笛卡尔积，也就是把所有的组合情况打印出来
假设有 A，B 两张表，A 有 x 个数据，B 有 y 个数据，那么查询出来就会有x*y个数据
select * from A, B;
注意：直接进行这样查询，会有很多无用的数据，因此下面介绍如何消除无用的笛卡尔积
1 内连接查询
- 隐式内连接：直接在
where条件语句指定，两表的关联字段相等即可
select * from 表1,表2 where 表1.id = 表2.id;
- 显式内连接：
select 查询字段 from 表名1 inner join 表名2 on 表1.id = 表2.id;
select 查询字段 from 表名1 join 表名2 on 表1.id = 表2.id; [[inner可以省略]]
2 外连接查询
- 左外连接：查询左表的所有数据，以及其交集部分（一般用左外连接）
select 查询字段 from 表名1 lift join 表名2 on 条件;
- 右外连接：查询右表的所有数据，以及其交集部分
select 查询字段 from 表名1 right join 表名2 on 条件;
3 子查询
概念：即查询的嵌套，称嵌套的查询是外面的查询的子查询
- 1 子查询的结果是单行单列的
使用运算符进行判断的都是这种，比如这里，查询最高成绩的结果肯定是单个
# 查询成绩最高的人的信息
-- 1 成绩最高是多少
select max (salary) from emp; -- 90分
-- 2 查询成绩为最高的人的信息
select * from emp where salary = 90
# 其实可以嵌套来写
select * from emp where salary =(select max (salary) from emp);
- 2 子查询是多行单列的
使用运算符in来进行接收
# 查询 所有2班与3班的学生信息
-- 1 查询2班与3班对应的班级编号
select id
from dept
where name= 1班 or name = 2班; -- 1 ,2
-- 2 查询班级编号对应的学生信息,or或者in
select *
from emp
where dept_id = 1 or dept_id = 2;
select *
from emp
where dept_id in (1,2);
# 可以进行嵌套的写
select *
from emp
where dept_id
in(select id from dept where name= 1班 or name = 2班);
- 3 子查询是多行多列的
多行多列就相当于一个虚拟的表
# 查询 所有的学生 入学日期是2020年1月1日之后的信息和 班级信息
# 普通查询
select *
from emp t1, dept t2
where t1.id = t2.id and t1.date>2020-1-1;
# 嵌套查询
select *
from dept t1, (select * from emp where emp.date >2020-1-1) t2
where t1.id = t2.id
六 DCL 管理用户#
1 用户管理#
1 添加用户
create user 用户名 identified by 密码
create user 用户名 @ 主机名 identified by 密码
# 主机名表示此用户只能在此主机登录
# 如果不写默认就是如下的形式，什么机器都能登录
create user '用户名' @ '%' identified by 密码
2 修改用户密码
# 方式一
update user set password = password('新密码') where user = '用户名';
# 方式二
set password for '用户名'@ '主机名' = password('新密码');
3 忘记 root 用户密码
# cmd 执行如下命令，关闭mysql服务
net stop mysql
# 使用无验证方式启动mysql的服务
mysqld --skip -grant -tables
# 打开新的窗口,输入如下命令，即可无验证进入，然后修改密码
mysql
4 删除用户
drop user '用户名' @ '主机名'
drop user '用户名'
5 查询用户
在mysql下载好的时候，系统自带的一个数据库叫mysql，其中有张表叫user，里面就存放了用户的信息，那么查询就是
use mysql;
select * from user;
2 权限管理#
1 查询权限
show grants for '用户名' @ '主机名';
show grants for '用户名' @ '%';
2 授予权限
# 一个一个权限授予
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';
# 权限列表：select，update，delete,inseat
# 一次授予全部权限
grant all on *.* to '用户名' @ '主机名';
3 撤销权限
# 一个一个权限撤销
revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
# 一次全部撤销
revoke all on *.* from '用户名' @ '主机名';
七 约束#
顾名思义规范存储的数。能存什么，不能存什么，有如下三大类：
1 实体完整性约束：
| 约束 | 说明 |
|---|---|
| primary key | 主键约束 |
| unique | 唯一约束，唯一，但可为 null |
| auto_increment | 自增，配合主键约束使用 |
2 域完整性约束
| 约束 | 说明 |
|---|---|
| not null | 非空 |
| default | 默认值约束 |
| 类型 | 类型约束 |
3 引用完整性约束：
也称外键约束，优点能保证数据库完整性，缺点速度慢，性能低。语法：
# 创建表时候增加
constraint foreign key (外键字段) references 另一张表名称(另一张表的主键)
# 创建表后添加外键
alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
4 删除约束：
- 删除非空约束：
# 改名字，类型以及约束
alter table 表名 change 列名 新列名 新数据类型 新的约束;
# 只改列的数据类型
alter table 表名 modify 列名 新数据类型 新的约束
# 即重新指定列的属性，去掉了约束
alter table stu modify id int;
- 删除唯一约束：
alter table stu drop index 列名;
- 删除主键约束：
alter table stu drop primary key;
八 范式#
范式：范式就是规范，即设计数据库需要遵循的规范
1 范式的分类
| 范式 | 说明 |
|---|---|
| 第一范式（1NF） | 每一列都是不可分割的原子数据项 |
| 第二范式（2NF） | 在 1NF 的基础上，消除非主属性对主码的部分函数依赖 |
| 第三范式（3NF） | 在 2NF 的基础上，消除传递依赖 |
| 第四范式（4NF） | 在 3NF 的基础上， |
| 第五范式（5NF） | 在 4NF 的基础上， |
| 第六范式（6NF） | 在 5NF 的基础上， |
2 函数依赖
- 完全函数依赖：可以通过前面的确定唯一的后面的
A-->B或者(A,B)-->C - 部分函数依赖：
(A,B)-->C但是A-->C，因此，是部分函数依赖 - 传递函数依赖：
A-->B并且B-->C因此A-->C这就是传递依赖 - 码：如果在一个表中，一个属性，或者属性组被其他所有属性完全依赖，则个属性就称为该表的码
- 主属性：码属性组中的所有属性
- 非主属性：除过码属性组的其他属性
九 数据库的备份与还原#
将数据库保存为 sql 文件，以及导入 sql 文件
# 备份：
mysql dump -u用户名 -p密码 数据库名称 > D:\\xxx.sql;
# 还原,需要有个数据库，
create database xxx;
use xxx;
source D:\\xxx.sql
十 事务#
事务就是把这些操作整合为一个整体，要么都执行，要么都不执行，如果事务期间出现异常哪呢就会回滚，回到事务开始的地方，如果没有问题，就提交事务。
每当执行一个 sql 语句的时候，就会默认开启一个事务，而我们自己开启事务的原因就是打破这个默认的单行语句事务，让多行语句成为原子操作。
开启事务
| 解释 | 命令 |
|---|---|
| 开启事务 | start transaction，begin，set uncommit = 0 这三种方式 |
| 回滚 | rollback |
| 提交 | commit |
查看，设置是否自动提交：
# 查看是否为自动提交
select @@autocommit ;
# 如果结果为1-----自动提交，mysql默认
# 如果结果为0-----手动提交，oracle默认
# 设置自动提交
set @@autocommit;
事务的四大特征
1 原子性：事务是不可分割的最小单位，即是一个整体，要么同时成功，要么同时失败，即事务是整体进行运行
2 一致性：事务的一致性，事务操作前后，事务的总量是不变的，比如我们转账，转账前后金额的总量是不变的
3 隔离性：多个事务之间，相互独立，但是有一些的相互影响，需要事务的隔离级别进行限制
2 持久性：当事务提交或者回滚后，数据会持久的进行保存
事务的隔离级别
多个事务之间是隔离的，是相互独立的，但是多个事务操作同一批数据会引发一些问题，设置不同的隔离级别进行解决这些问题:
1. 脏读：一个事务，读取到另一个事务没有提交的数据
2. 不可重复度（虚读）：在同一个事务中，两次读取到的数据不同，因为在读的时候，有人修改了数据。
3. 幻读：一个事务操作（DML：增删改）数据表中的所有记录，另一个事务添加了一条数据，则第一个事务无法查看到自己操作的结果，仿佛出现了幻觉，但 mysql 做了处理，不会出现幻读。
隔离级别
| 隔离级别 | 说明 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| read uncommited | 读未提交 | √ | √ | √ |
| read commited | 读已提交 (Oracle 默认) | √ | √ | |
| repeatable read | 可重复读 (MySql 默认) | √ | ||
| serializable | 串行化 |
隔离级别从小到大，安全性越来越高，但是效率越来越低
查询隔离级别
select @@tx_isolation
# 结果如下：mysql默认
# +-----------------+
# | @@tx_isolation |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+
设置隔离级别
# 全局设置
set global transaction isolation level 级别字符串;
# s
set session transaction isolation level 级别字符串;
十二 视图#
虚拟表，但和真实表一样
优点：简单，安全性高，用户只能看到视图内的数据
缺点：
- 视图数据来源于数据库，如果数据库数据变化则跟着变化
- 没有优化查询性能
- 如果视图有如下内容则不会更新
- 聚合函数的结果
- distinct 去重的结果
- group by 分组的结果
- having 筛选的结果
- union，union all 合并的结果
创建视图：
create view 自己起视图名 as 数据库查询语句
# 如果有此名称的视图则替换，可以作为修改语句
create or replace view 自己起视图名 as 数据库查询语句
# 修改视图
alter view 视图名 as 查询语句;
# 删除视图，视图的删除不会影响到原表
drop view 视图名;
对视图的操作也可以更新到数据库，但是一般不这样做。比如，视图的数据来源是一张表的全部内容，而像连接查询的结果作为视图的数据源，则不可以进行修改，很容易理解。