MySQL基础篇知识点小结

DB：数据库（database），存储数据的仓库
我们用create database创建的就是这个玩意儿
DBMS：数据库管理系统（Database management system），数据库是通过DBMS创建和操作的容器，用于管理DB中的数据
SQL：结构化查询语言（structre query language），专门用来和数据库通信的语言
也就是我们在mysql客户端或者小海豚之类的图形界面上敲下的代码

如何开启mysql服务？
第一种：点击开始-右击计算机-管理-服务与应用程序-服务-选择mysql进行开启和关闭
第二种：在黑窗口里面，net start 定义的mysql名字，如mysql57（开启），net stop 定义的mysql名字（关闭）

如何登陆mysql？
第一种：在mysql客户端里面输入密码进行登陆
第二种：在黑窗口中输入：mysql （-hIP地址 -P端口号）（本机可省略) -u root(用户名) -p 回车 ——输入密码

DQL查询语言（执行步骤见语法）
补充：mysql中加号+的作用：作为运算符
select 100+90
两个操作数都为数值型，则做加法运算
select ‘123’+90
其中一个为字符型，试图将字符型数值转换为数值型，如果转换成果，则继续做加法运算
select ‘jack’+90
如果转换失败，则将其转换为0
select null+100
只要其中有一方为null，则结果肯定为null，null与任何数进行拼接都是null

一：DQL条件查询
语法
select 查询列表 3
from 表名 1
where 筛选条件 2

1.1 按条件表达式筛选
条件运算法：>,<,<>,<=,>=
1.2 按逻辑表达式进行筛选
逻辑运算符：and or
1.3模糊查询
like,between and/not between and,in,is null/is not null

注意事项
1.4
其中like一般和通配符搭配使用；
%代表任意多个字符，_代表任意单个字符。

1.5
使用（not）between and可以提高语句的简洁度相比于and和or；
包含临界值；
两个临界值不要调换顺序。

1.6
in列表中的值类型必须一致或兼容（能够隐式的转换）；
不支持通配。

1.7
=或<>不能用来判断null值，is null或is not null用来判断；
同时还可以配合ifnull（判断的值，假如为null则转换成的值）。

二：DQL排序查询
语法
select 查询列表 3
from 表名 1
where 筛选条件 2
order by 排序列表 asc升序（默认）/desc降序 4

注意事项
2.1
order by 后面可以接单个或多个字段，用逗号隔开；
order by后面还可以接函数。

三：常见的函数
分为单行函数和分组函数
3.1单行函数
3.1.1字符函数

length（str）：获取参数的字节个数（每个中文的字节个数按编码格式的不同也不同，一般采用utf8，为3字节）

concat（参数1，参数2，。。。）拼接字符串，将传入的参数拼接为一个字符串 upper，lower将字符串大写和小写的函数

substr（substring）（str，startindex，len）：截取从指定索引到指定字符长度的字符（mysql中字符从1开始为第一个索引）

instr（str1，str2）：返回参数2在参数1中第一次出现的索引，没有则返回0

reverse（str）：返回颠倒字符顺序的字符

left/right（str，len）：返回字符串str的最左/右len个字符

replace（str1，str2，str3）;将str1中出现的所有str2全部替换为str3

3.1.2数学函数
round（参数）：四舍五入

ceil（参数）：向上取整

floor（参数）：向下取整

truncate（参数，len）：len为小数点后保留几位

mod（参数）：取余

rand（）：获取0~1的随机数，取不到1

3.1.3日期函数
now（）：返回当前日期+时间

curdate（）：返回当前日期

curtime（）：返回当前时间

YEAR（now（））：仅仅返回当前时间的年份
MONTH（now（））：仅仅返回当前时间的月份
（日，时，分，秒依次类推）

str_to_date(参数，给定格式)：将传入的字符串，转换为指定的日期格式

date_format（参数，指定字符串格式）：将传入的日期转换为字符串

3.1.4流程控制函数
if（表达式，true执行的条件，false执行的条件）

case
1.类似于java中的switch，进行等值判断
2.类似于java中的多重if，进行区域值判断

3.2分组函数
sum（字段）：求和
max（字段）：最大值
min（字段）：最小值
avg（字段）：平均值
count（字段/*/1）：求个数

count的区别（自己的一点理解）：https://www.bilibili.com/video/av94381540/

四：分组查询
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

五：连接查询
避免笛卡尔乘积现象：当查询多个表时，并没有添加有效的连接条件。

按年代分：sql 92语法（支持内连）和sql 99语法（支持内连+外连（左外和右外）+交叉连接）

内连接：等值连接，非等值连接，自连接
外连接：左外连接，右连接，全外连接（mysql不支持）
交叉连接（笛卡尔乘积）

5.1 sql 92的等值连接和非等值连接
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1，表名2 1
where 表1和表2的连接条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

5.2 sql 92的自连接
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1，表名1 1
where 表1和表1的连接条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

5.3sql 99的等值连接和非等值连接和自连接
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1 1
inner join 表名2
on 表1和表2的连接条件
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

5.3 sql 99的三表连接
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1 1
inner join 表名2
on 表1和表2的连接条件
inner join 表名3
on 表1和表3的连接条件
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

5.4 sql 99的外连接（左外/右外）
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1 1
left（right） outer join 表名2
on 表1和表2的连接条件
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

5.5 sql 99的交叉连接
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1 1
cross join 表名2
on 表1和表2的连接条件
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6

六：子查询
出现在其他语句中的select语句，称为子查询，外部的查询语句称为主查询

分类：
按结果集的行列数
标量子查询：结果集一行一列
列子查询：一列多行
行子查询：一行多列或多行多列
表子查询：任意行列

按子查询出现的位置
select后面，仅支持标量子查询
from后面：表子查询
where和having后面：标量子查询，列子查询，行子查询
exists后面：表子查询（较特殊，先执行主查询，某个字段的值再去套子查询，来进行过滤）

注意事项
标量子查询一般搭配单行操作符使用：>,<,<=,>=,=,<>
列子查询一般搭配多行操作符使用：in，any（和子查询中某一个值进行比较），all（和子查询中所有的值进行比较）

七：分页查询
语法
select 必须是出现在group by中的字段或者分组函数 5
from 表名 1
where 筛选条件 2
group by 字段 3
having 分组后的筛选 4
order by 排序列表 asc升序（默认）/desc降序 6
limit offset,size; 7

offset:要显示条目的起始索引（起始索引从0开始）
size：要显示的条目个数

注意事项
搭配排序查询使用，可以快速获取指定的行数

八：联合查询
语法
select 查询字段 from 表名 1
union
select 查询字段 from 表名 2
应用场景
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

注意事项
要求多条查询语句的查询内容一致；
要求多条查询语句的查询内容和查询顺序一致；
union关键字默认去重，使用union all可以包含重复页

DML语言
数据操作语言
一：插入insert
语法一
insert into 表名（列名，。。。） values（值1，。。。）

注意事项
插入的值的类型要与列的类型一致或兼容；
如果列名全省略，则默认插入所有值；
如果省略部分列名，则插入指定值；
列的顺序可以调换，但列和值的个数必须一致；

语法二
insert into 表名 set 列名1=值1，列名2=值2，。。。

二：修改update
语法
update 表名 set列名1=值1，列名2=值2，。。。
where 筛选条件
（可搭配连接查询，修改多表）

三：删除delete
语法一
delete from表名 where 筛选条件 limit 条目数
（可搭配连接查询，删除多表）

语法二
truncate table 表名

delete和truncate的区别
delete可以加筛选条件，truncate不能；
truncate删除的效率更高；
如果删除的列是自增列，如果用delete删除后，再插入数据时，标识符从断点开始，而truncate删除后，在插入数据，标识符从1开始；
delete删除后有返回值（几行受影响），truncate删除后没有返回值；
delete删除后可以回滚，truncate删除后不能回滚。

DDL数据定义语言
一：库的管理
语法：
创建库：create databasse 苦命
修改库：alter database 库名 character utf8
删除库：drop database 库名

二：表的管理
创建表：create table 表名（列名 列的类型 约束，。。）；
修改表
修改表名：alter table 表名 rename to 新表名
修改列名：alter table 表名 change column 列名 新列名 类型
修改列的类型或约束：alter table 表名 modify column 列名 类型 约束
删除列：alter table 表名 drop column 列名
删除表：drop table 表名

表的复制
仅仅复制表的结构：create table 新表 like 复制的表
复制表的结构——数据：create table 新表 select 字段 from 复制的表
通过库名.指定表名，可以实现跨库复制

三：数据类型
数值型：整形，小数（定点数，浮点数）
3.1整形
字节从小到大 Tingint<Smallint<Mediumint<int<bigint

注意事项
如果不设置无符号还是有符号，则默认为有符号，如果想设置为无符号，需要添加unsigned；
如果插入的数值超过范围，默认插入的值为临界范围，会报out of range异常；
如果不设置长度会有默认的长度，长度代表显示的最大长度，如果不够则左边用0填充，搭配zerofill

3.2小数
float(M,D)<double(M,D)
DEC/DECIMAL(M,D)：最大取值范围和double相同，给定decimal的有效取值范围，由M和D决定

注意事项
M：整数部位+小数部位
D：小数部位
如果超过则插入临界值；
M，D都可以省略，decimal的默认值为10,0，而float和double的默认值随插入的数据的精度来决定；
decimal的精度较高。

3.3字符型
较短的文本：char(M)，varchar(M)
较长的文本：text

注意事项
M代表最大的字符个数；
char和varchar的区别
char代表固定长度字符，空间的耗费较高，但是效率高，而且M可以省略，默认为1；
varchar代表可变字符，空间的耗费较低，效率低，M不可以省略，必须人为的给定。

3.4日期型
date只保存日期
datetime保存日期+时间
timestamp保存日期+时间
time只保存时间
year 年

注意事项
datetime和timestamp的区别
timestamp支持的时间范围小；
timestamp与实际的时区有关，而datetime只反映插入时的当地时区；
timestamp受mysql版本和sqlmode的影响较大。

四：常见约束
not null非空约束
primary key主键约束
default默认约束
unique唯一约束
check检查约束（mysql中不起作用）
foreign key外键约束：限制两个表的关系，用保来保证该字段值必须来自于主表的关联列的值

约束创建方式
4.1创建表时添加约束
列级约束：直接在字段名和类型后面追加约束，只支持default，not null，primary key，unique
表级约束：在各字段的最下面添加约束，除了not null和default，其他的都支持

通用的语法
create table if not exists table01(
id int primary key,
name varchar(20) not null,
age int default 18,
seat int unique,
majorid int

constraint fk_table01_table_02 foreign key (majorid) references table02(id)
);

4.2修改表时添加约束
添加非空约束
alter table table01 modify column c1 int not null
添加默认约束
alter table table01 modify column c1 int default 1
添加主键
1.列级约束
alter table table01 modify column c1 int primary key
2.表级约束
alter table table01 add primary key（c1）
添加唯一
1.列级约束
alter table table01 modify column c1 int unique
2.表级约束
alter table table01 add unique（c1）
添加外键
alter table table01 add constraint fk_table01_table_02 foreign key (majorid) references table02(id)

4.3修改表时删除约束
删除非空约束
alter table table01 modify column c1 int
删除默认约束
alter table table01 modify column c1 int
删除主键
alter table table01drop primary key
删除唯一
alter table table01drop index c1
删除外键
alter table table01drop foreign key ky_table01_table02(记得删除索引)

注意事项：
主键和唯一键的对比
主键保证唯一性，非空，一个表中至多有一个，可以组合
唯一键保证唯一性，可以为空，一个表中可以由多个，可以组合

外键
要求在从表中设置外键约束；
从表的外键列的类型和主表的对应；
主表中的关联 列必须是一个key（主键，唯一）；
插入数据时，先插入主表，后插入从表；
删除数据时，先删除从表，后删除主表

或采用级联删除
语法
alter table table01 add constraint fk_table01_table02 forign key(majorid) references table02(id) on delete cascade;
如果删除主表则从表的数据一并删除

或采用级联置空
语法
alter table table01 add constraint fk_table01_table02 forign key(majorid) references table02(id) on delete set null;
如果删除主表，则从表中对应的数据为null

每个字段可以添加多个约束，空格隔开。

五：标识列
语法
在添加约束时，在后面添加auto_increment

注意事项
标示列必须为一个key；
标示列只能是数值型；
标示列可以通过set auto_increment_increment设置默认的步长；
可以手动添加起始值。

TCL事务控制语言
事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

一：事务的特点
原子性：事务是一个不可分割的整体，要么全部执行，要么全部不执行
一致性：事务必须使数据库从一个一致性的状态变成另一个一致性的状态
隔离性：一个事务的执行不能被其他事务所影响
持久性：一个事务一旦被提交，他对数据库中的数据的改变是永久性的

隐式的事务
insert，delete，update

显示的事务
语法
1.开启事务
set autocommit =0;
start transaction;
2.编写事务中的多条语句
3.结束事务
commit;提交事务
rollback;回滚

保存点
语法
savepoint 保存点名；回滚时，回滚到这里（类似于游戏中的复活存档）

二：隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题
脏读：对于两个事务t1，t2，t1读取了已经被t2更新但未提交的数据，如果t2回流，t1读取的内容就是临时的
不可重复读：对于两个事务t1,t1，t1读取了一个字段，然后t2更新，则t1再次读取时，就不同了
幻读：t1读取了一个字段，这时t2插入新的字段，t1再次读取时，就会多出几行

read uncommitted 读未提交：三种都不能避免
read committed读已提交：可避免脏读
repeatable read（mysql默认的隔离级别）可重复读：可避免脏读，不可重复读
serializable串行化：三个都可以避免

三：视图
语法
create view 视图名
as 查询语句

视图的好处
重用sql语句；
简化复杂的sql操作，不必知道他的查询细节；
保护数据，提高安全性

修改视图
create or replacce view 视图名 as 查询语句
alter view 视图名 as 查询语句

删除视图
drop view 视图名

查看视图
desc 视图名
show create view 视图名

四：变量
系统变量由系统提供，不是用户定义
自定义变量
4.1用户变量
语法
1.声明
set @变量名=值
2.赋值
set @变量名=值
select 字段 into @变量名 from 表
3.使用
select @变量名

4.2局部变量
语法
1.声明
delcare 变量名 类型
2.赋值
set 变量名=值
select 字段 into 变量名 from 表
3.使用
select 变量名

五：存储过程
语法
delimiter 结束标记
create producre 存储过程名（参数模式（空参/in/out/inout） 参数名 参数类型）
begin
存储过程体
end 结束标记

调用
call 存储过程名（参数）
删除
drop procedure 存储过程名

六：函数
有且仅有一个返回值
语法
delimiter 结束标记
create function 函数名（参数名 参数类型） returns 放回值类型
begin
函数体
return 返回值
end 结束标记

删除函数
drop function 函数名

七：流程控制结构
7.1分支结构
if，case
7.2循环结构
while，loop，repeat
循环控制
iterate（类似于continue）
leave（类似于break）

while
语法
（标签：） while 循环条件 do
循环体
end while （标签）；