*Untitled

一、基础

1 概述

1. 数据库相关概念：
   数据库 DataBase（DB）：存放数据的仓库
   数据库管理系统 DataBase Management System（DBMS）：操纵和管理数据库的大型软件
   SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

2. 启动与停止：
   启动：net start mysql80
   停止：net stop mysql80

3. 客户端连接：mysql -u root -p 密码

4. 数据模型：
   关系型数据库：
   建立在关系模型基础上，由多张相互连接的二维表组成的数据库
   MySQL数据模型：
   客户端->DBMS->多个数据库->多个二维表

2 SQL

1. SQL 通用语法
   注释：
   单行注释：–注释内容 或者 #注释内容
   多行注释：/注释内容/

2. SQL 语句分类
   DDL 数据定义语言:用来定义数据库对象
   DML 数据操作语言：用来对数据库表中数据进行增删改
   DQL 数据查询语言：用来查询数据库中表的记录
   DCL 数据控制语言：用来创建数据库用户、控制数据库的访问权限

3. DDL
   数据库操作：
   查询所有数据库：SHOW DATABASES;
   查询当前数据库：SELECT DATABASE();
   创建：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
   删除：DROP DATABASE [IF EXISTS] 数据库名;
   使用：USE 数据库名;
   表操作：查询
   查询当前数据库所有表： show tables;
   查询表结构： desc 表名;
   查询指定表的建表语句：show create table 表名;
   表操作：创建
   创建表:

   create table 表名(  
       字段1 字段1类型[comment 字段1注释],  
       字段n 字段n类型[commnet 字段n释]  
   )[comment 表注释];  

   表操作：修改
   添加字段：

alter table 表名 add 字段名 类型(长度) [commnet 注释] [约束];
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型：
alter table 表名 change 旧字段名新字段名 类型(长度) [commnent 注释] [约束];
删除字段：
alter table 表名 drop 字段名;
修改表名：
alter table 表名 rename to 新表名;
表操作：删除
删除表：
drop table [if exists] 表名;
删除指定表并重新创建该表:
truncate table 表名;
数据类型
无符号：signed；有符号：unsigned
TINYINT:1 byte
SMALLINT：2 bytes
MEDIUMINT: 3 bytes
INT/INTEGER:4 bytes
BIGINT:8 bytes
FLOAT:4 bytes
DOUBLE:8 bytes
CHAR:0-255 bytes 定长字符串（一字符也空格填充占满） 性能好
VARCHAR:0-65535 bytes 变长字符串 性能较差
DATE 3 YYYY-MM-DD 日期值
TIME 3 HH:MM:SS 日期值或持续时间
YEAR 1 YYYY 年份
DATETIME 8 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

4. DML
   给指定字段添加数据：
   insert into 表名(字段名1,字段名2,…) values(值1,值2,…);
   给全部字段添加数据：
   insert into 表名 values(值1,值2,…);
   批量添加数据：
   insert into 表名(字段名1,字段名2,…) values(值1,值2,…),(值1,值2,…),…;
   insert into 表名 values(值1,值2,…),(值1,值2,…),…;
   修改数据：
   update 表名 set 字段名1=值1,字段名2=值2,… [while 条件];
   删除数据：
   delete from 表名 [while 条件];(没有条件则删除表中所有数据)

5. DQL
   单表查询语法
   select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数;
   基本查询
   查询多个字段：
   select 字段1,字段2，字段3,… from 表名;
   select * from 表名;
   设置别名：
   select 字段1 [as 别名1],字段2 [as 别名2] … from 表名;
   去除重复记录：
   select distinct 字段列表 from 表名;
   条件查询
   语法：
   select 字段列表 from 表名 where 条件列表;
   条件：
   比较运算符： >, >=, <, <=, =, <> 或 !=,
   between … and …
   in(…)
   like 占位符（模糊匹配，_匹配单个字符，%匹配任意个字符）
   is null
   逻辑运算符：and 或 &&, or 或 ||, not 或 !
   聚合函数
   将一列数据作为一个整体，进行纵向计算
   常见聚合函数：count, min, manx, avg, sum
   语法：
   select 聚合函数(字段列表) from 表名;
   分组查询
   语法：
   select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
   where 是分组之前过滤，不满足 where 条件的不参与分组；having 是分组之后对结果进行过滤
   排序查询
   语法：
   select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式2;
   排序方式：asc（升序，默认值）；desc（降序）
   分页查询
   语法：
   select 字段列表 from 表名 limit 起始索引,查询记录数;(起始索引=（页码-1）*查询记录数)
   执行顺序
   from -> where -> group by -> having -> select -> order by -> limit

6. DCL
   管理用户
   查询用户：
   use mysql;
   select * from user;
   创建用户：
   create user ‘用户名‘@’主机名’ identified by ‘密码’;
   修改用户密码：
   alter user ‘用户名‘@’主机名’ identified with mysql_native_password by ‘新密码’;
   删除用户：
   drop user ‘用户名‘@’主机名’;
   权限控制
   MySQL中的权限：
   all,all privileges：所有权限
   select：查询数据
   insert：插入数据
   update：修改数据
   delete：删除数据
   alter：修改表
   drop：删除数据库/表/视图
   create：创建数据库/表
   查询权限：
   show grants for ‘用户名‘@’主机名’;
   授予权限：
   grant 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名’;
   撤销权限：
   remove 权限列表 on 数据库名.表名 from ‘用户名‘@’主机名’;

3 函数

函数是指一段可以直接被另一段程序调用的程序或代码

1. 字符串函数
   concat(s1,s2,…sn) 字符串拼接，将s1，s2,…四年、拼接成一个字符串
   lower(str) 将字符串 str 全部转为小写
   upper(str) 将字符串 str 全部转为大写
   lpad(str,n,pad) 左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度
   rpad(str,n,pad) 右填充
   trim(str) 去掉字符串头部和尾部的空格
   substring(str,start,len) 返回字符串 str 从 start 位置起的 len 个长度的字符串

2. 数值函数
   ceil(x) 向上取整
   floor(x) 向下取整
   mod(x,y) 返回 x/y 的模(取余)
   rand() 返回0-1的随机数
   round(x,y) 求参数 x 的四舍五入值，保留 y 位小数
   生成六位随机验证码：
   select lpad(round(rand()*1000000,0),6,’0’);

3. 日期函数：
   curdate() 返回当前日期
   curtime() 返回当前时间
   now() 返回当前日期和时间
   year(date) 获取指定 date 的年份
   month(date) 获取指定 date 的月份
   day(date) 获取指定date 的日期
   date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔 expr 后的时间值
   datediff(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数

4. 流程函数
   if(value,t,f)
   如果 value 为 true，则返回 t，否则返回 f
   ifnull(value1,value2)
   如果 value1 不为空，返回 value1，否则返回 value2
   case when [val1] then [res1]…else [default] end
   如果 val 为 true，返回 res1，否则返回 default 默认值
   case [expr] when [val1] then [res1] …else [default] end
   如果 expr 的值等于 val1，返回 res1，… 否则返回 default 默认值

4 约束

1. 概述：
   约束是作用于表上的规则，用于限制存储在中的数据（为了保证数据库中的数据的正确、有效性和完整性）

2. 分类
   非空约束：not null
   唯一约束：unique
   主键约束：primary key
   默认约束：default（未指定该字段的值，则采用默认值）
   检查约束：check（保证字段值满足一个条件）
   外键约束：foreign key
   约束关键字：
   主键：primary key
   自动增长：auto_increment
   非空：not null
   唯一：unique
   默认值：default
   满足条件：check

   create table user(
       id int primary key auto_increment comment '主键',
       name varchar(10) not null unique comment '姓名',
       age int check(age>0 && age <=120) comment '年龄',
       status char(1) default '1' comment '状态',
       gender char(1) comment '性别'
   )comment '用户表';

3. 外键约束
   语法：
   添加外键：

   create table 表名(
       字段名 数据类型,
       ...
       [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
   );

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

4. 外键删除更新行为
   no action ：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与restrict一致）
   restrict
   cascade：如果有，则也删除/更新外键所在子表中的记录
   set null：如果有则设置子表中该外键值为 null（要求外键允许 null ）
   set default：父表有更新时，子表将外键设置成一个默认的值
   语法：
   alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名) on update 行为 on delete 行为;

5 多表查询

1. 多表关系：
   一对多（多对一）：在多的一方建立外键，指向一的一方的主键
   多对多：建立第三方中间表，中间表至少包含两个外键，分别关联两方主键
   一对一：多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
   在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的

2. 多表查询
   概述
   笛卡尔积：两个集合的所有组合情况，在多表查询中，需要消除无效的笛卡尔积
   分类：
   连接查询：内连接（相当于查询交集部分数据）、外连接：左外连接（查询左边全部数据，以及两张表交集部分数据）、右外连接（查询右表所有数据，以及两张表交集部分数据）、组链接（当前表与自身的连接查询，自连接必须使用表别名）
   子查询
   内连接
   语法：
   隐式内连接：
   select 字段列表 from 表1 [别名1],表2 [别名2] where 条件…;
   显示内连接：
   select 字段列表 from 表1 [别名1] [inner] join 表2 [别名2] on 连接条件
   隐式：先做笛卡尔积再过滤；显示：先匹配链接条件再返回，显示性能更优，复杂度更低
   外连接
   语法：
   左外连接：
   select 字段列表 from 表1 left [outer] join 表2 on 条件…;
   右外连接：
   select 字段列表 from 表1 right [outer] join 表2 on 条件…;
   自连接
   语法：
   select 字段列表 from 表1 别名1 join 表1 别名2 on 条件…;
   自连接查询可以是内连接查询也可以是外连接查询
   联合查询-union,union all
   对于 union 查询，就是把多次查询的结果合并起开，形成一个新的查询结果集
   select 字段列表 fron 表a … union [all] select 字段列表 from 表b …;
   select 字段列表 fron 表a … union select 字段列表 from 表b …;(合并后去重)
   对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
   union all将全部数据直接合并在一起，union会对合并后的数据去重
   子查询
   概念：SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询
   子查询外部的语句可以是 insert、update、delete、select 的任何一个
   根据子查询结果不同，分为：
   标量子查询：子查询结果为单个值
   列子查询：子查询结果为一列
   行子查询：子查询结果为一行
   表子查询：子查询结果为多行多列
   根据子查询位置，分为：where 之后、fron 之后、select 之后
   标量子查询：

6 索引