常用语句

show tables from databases; 查看某个库中的某个表

select database(); 查看当前所在库

desc tableName; 查看表的结构

select version(); 查看MySQL的版本

SELECT last_name as “别名” from employees; 为字段起别名，as可以省略，空格区分即可。双引号也可省略

SELECT DISTINCT id FROM users distinct–> 对查询出的数据进行去重，当对多个字段去重时 DISTINCT a,b，它会将多个字段视为一个整体，当同时满足的时候就会去重。

mysql中的加号只能作为运算符，无法拼接字符串，如果是字符型相加，会尝试将其转换成整型，转换失败则会让字符型转为数值 0，只要其中一方为null，其结果肯定为null

SELECT CONCAT(last_name," ",first_name) 姓名 FROM employees 拼接字段，并为拼接后的字段起别名为姓名

IFNULL(expr1,expr2) 判断expr1的值是否为null，为null时把null改为expr2的值

当有三个条件，可以通过（）来进行细分，如查询编号小于90或大于110或工资大于15000的

select * from employees where NOT(id>=90 AND id<=110) OR salary>15000

模糊查询，like(像)、between and(在什么之间)、in、is null、is not null。like通常和通配符搭配使用，%表示任意多个字符，包含0个字符；_下划线表示任意单个字符；

如果查询内容中有特殊字符，如下划线百分号等，可以只用反斜杠 \ 进行转译,可以在语句最后用 ESCAPE定义转译字符

between and表示区间，id between 100 and 120 取id在100到120之间,且包含100和120，临界值不要颠倒，前面是大于后面是小于，颠倒后语意会发生改变。也可以通过 NOT BETWEEN AND 来取反

in用于判断某字段的值是否属于in列表的某一项，id in(10,20,30)取id是10或20或30的数据，不能使用通配符，因为通配符是和like一起使用，in等价于等号=

is null 和 is not null用于判断字段值是否为NULL，因为=和<>不能判断null值

排序语法：select * from table [wherer 条件] order by 排序列表 [asc|desc] 默认为升序asc，可省略。也可以对表达式进行排序且起别名，select *,salary*12*(1+IFNULL(commission,0)) AS 年薪 from table [wherer 条件] order by 年薪 [asc|desc]

select * from employees order by salary asc,employees_id desc 先根据工资升序排列，当遇见工资相同时，按id降序排列，order by一般放在查询语句最后面，limit语句除外

语句的执行顺序一般是先执行form ，[有分组或者连接则先进行]，然后where筛选数据，然后才是select后要显示的字段，最后升序降序有定义则执行，因此如果有分组，select显示的是筛选之后需要显示的字段，并非原始表的字段

函数

字符函数

upper 将字符串转为大写，lower 将字符串转为小写

substr|substring(str,index,[长度]) 两者都可用，用于切割字符串，要注意的是索引是从1开始。加长度则是截取从指定索引处开始的指定字符长度的字符（即使是中文也依然只占一个字符）

instr(str,substr) 用于返回子串substr在str中第一次出现的起始索引，找不到则返回0

trim( [i FORM] str) 去掉字符串前后的空格或者指定字符串 i

LPAD(str,len ,'*') 用指定的字符实现左填充指定长度，将str的头部用星号*填充到 len 个长度，长度包括str本身的字符长度（注意是字符不是字节，字符无论中英文都是只占一个字节），但是如果len<str，它会进行截断（从右边开始截断）, rpad(str,len,'*')则是右填充

replace(str,form_str,to_str)，替换，将str中的form_str替换成to_str，如果有多个相同的字符则会全部替换

数学函数

round(X,[D]) 四舍五入，X为要计算的值，D为保留小数点后几位（以小数点X+1的位数进行四舍五入

CEIL(X) 向上取整，返回>=该参数的最小整数（如1.00则取1，1.01则取2）

FLOOR(X) 向下取整，返回<=该参数的最大整数（如1.00则取1，1.99也取1）

TRUNCATE(X,D) 表示截断，小数点后保留几位，X为原始值，D为保留的位数

MOD(m,n) 取余，a>=b,可以套用公式 a-a/b*b 进行计算，因为数据库中除法只取整，所以计算结果不一定为0

时间函数

NOW() 返回当前系统日期+时间；CURDATE() 返回当前系统日期，不包含时间；CURTIME() 返回当前系统时间，不包含日期

也可以获取指定的部分，年月日时分秒都可以单独获取，YEAR|MONTH|DAY...(时间格式)，在之后加上name,monthname 则可获取英文月份

str_to_date('9-3-2019','%m-%d-%Y') 将日期格式的字符转换成真正的日期型数据

date_format(date,'%m月/%d日 %Y年') 将日期格式date转换成字符，同时可以自定义字符的输出格式

datediff(date1,date2) 返回日期参数的相差天数，由date1减date2

流程控制函数

select if(expr1,expr2,expr3) expr1表示条件表达式，为true时返回表达式2的值，为false返回表达式3的值

case语句即可以搭配select（只能返回值），也可以单独自己使用（返回值或语句）。当显示值的时候不要放分号，一放分号就表示结束了。else可以省略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

只有then后是语句时才会加分号！！！
用法1（类似于switch）：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或  [语句1;]
when 常量2 then 要显示的值2或  [语句2;]
...
else 要显示的默认值或语句;
end


用法2（类似与多重判断，else if）,case后面不加变量:
case 
when 条件1 then 要显示的值1或  [语句1;]
when 条件2 then 要显示的值2或  [语句2;]
...
else 要显示的值n或  [语句n;]
end

如果是作为独立语句放在BEGIN END中时，then后面只能接语句，且end后面要加case;

分组函数，将多个值通过计算后获取一个值

sum(列名) 统计该列的和；avg(列名) 统计该列的平均值；min(列名) 统计该列的最低值；max(列名) 统计该列的最大值；count(列名) 统计该列的不为空null的值的个数；

sum和avg只支持数值型，min、max、count可以处理任何类型。分组函数都忽略了null值，含有null的字段数据是不会参加计算的。null加 任何值其结果都为null

分组函数可以搭配distinct（去重）使用，sum(distinct salary) 它会先对数据进行去重，然后进行计算

count(*)表示统计所有行数，因为只要该行有一个字段不为NULL就会加上那一行，主键永远不会为空

和分组函数一同查询的字段要求是group by后的字段，如果顺带查询一个普通字段，就对不上（一个对多个普通字段，无论显示什么都是没有意义的）

分组查询

以基础字段进行分组

1
2
3
4
5

SELECT MAX(salary),job_id FROM `employees` [where =] GROUP BY job_id [order =]
//以什么字段进行分组就要最后用group by进行注明以什么分组，分组的含义就是将该字段中相同的数据划分为一个组，通常用于以该字段进行划组来计算其他字段的数值。
//select后job_id字段可以省去，不加则查询出来的数据不会显示job_id一栏，它是进行过分组之后的每个组的job_id，重复的被合并为了一个。
//分组查询是不能使用*号取所有字段的，因为分组是将原始字段筛选，而显示其他字段时，不进行计算则无法知道该显示什么数据
//当描述为每个部门，每个班级等就可以用分组查询

1
2
3
4

SELECT COUNT(*) con,department_id FROM `employees`  GROUP BY department_id HAVING con>2
//查询哪个部门员工个数大于2
//对查询出来的分组数据进行筛选时不能使用where，where作用于分组之前，想要对分组之后的数据进行筛选则需要使用Having
//原始表就有的数据用where进行筛选，分组后的数据用having进行筛选

分组函数做分组查询的条件时，肯定是放在having之后（因为分组函数已经是对原始表分组后的数据进行计算过了，不属于原始数据了）。出于对性能的考虑，如果是筛选条件是分组的那个字段时，将它放在where中（也属于原始数据）

一般如果没有分组的情况下，使用where即可。having只在分组情况下使用

也可以按多个字段分组，当定义的字段全部都一致时才会分为一个组

select avg(salary),de_id,job_id from emp group by de_id,job_id

以表达式或函数分组

1

SELECT COUNT(*) FROM `employees`  GROUP BY LENGTH(last_name) HAVING COUNT(*)>5

多表查询

连接是对两表数据进行组装，如果A表的一条数据在B组中满足多个设定的where条件，那么它就会组装多个数据。每一个条件或连接都会形成一个虚拟表，然后一步步的进行筛选最后得出正确结果。无论内连还是外连都会先生成一个笛卡尔乘积的表，然后再通过条件过滤出符合的数据

内连接（查询两表的交集）

1
2
3
4

SELECT 查询字段
FROM 表1 别名 INNER JOIN 表2 别名
ON 连接条件;
//INNER可以省略

等值连接

select boyname,b.girlname form boys as b INNER JOIN girls as g ON b.id=g.boys_id 查询两个表中的数据，当两表某字段相等时显示对应行的值，一个表中有另一个没有则不会被筛选出来。最后结果肯定是 0<=数据<=两表行的乘积(m*n)，即其中的某个表的字段可能会重复显示。两表都有的字段加上前缀声明用哪个表，避免歧义 出现报错

如果为表起了别名，则查询的字段就不能用原来的表名去限定了

也可以对连接后的数据进行分组和筛选

select count(*),city from de INNER JOIN city ON de.id=city_id group by city 先对选择的表进行连接，然后分组，然后统计数量

非等值连接

即连接的符号不是等于，select salary,grade_lv from em INNER JOIN job_grade ON salary between g.'min_salary' AND g.'max_salary' ，它表示查询工资和工资等级表，并且筛选出工资在某个区间的数据，比如工资等级表有ABCD四个等级，salary表的每一条数据都会和工资等级表中的每个数据进行判断，当符合条件则将两者合成一条临时数据，然后再进行下一个字段的判断。最后出现的数据就是 每个员工的工资都会分到一个专属的等级，如2000分到D级（1000-2000）

自连接

对一张表进行连接筛选数据，比如员工表所对应的领导id也在员工表中，则需要自连接

1
2
3

select e.last_name 员工名,m.last_name 领导名 
from employees e INNER JOIN employees m 
ON e.employee_id=m.manager_id

它将两张相同的表合在一起，然后进行判断，如果判断成功则合为一条，再进行判断，最后只取出员工名和领导名

这三种连接也可以适用于超过两个表的情况

1
2
3

select last_name,department_name,city from 
employees e INNER JOIN departments d ON e.department_id=d.department_id 
INNER JOIN locations l  on d.location_id=l.location_id

表之间必须要有关联，有主外键关系。且不能把两个关联条件用AND放一起，虽然不报错，但是是有问题的。

三表及以上的连接需要考虑顺序，因为是表1和表2连成一个大表，然后on条件之后，连成的大表再和表3进行连接。且如果表1和表3没主外键关系就连接不起来，只有表1和表2，然后表2和表3

外连接（查询一个表有，另一个表没有的数据）

外连接是区分主从表的，连接之后两者根据条件匹配，匹配成功时数据会显示出来。当匹配不到时，其值则为NULL。

所以外连接查询的结果为主表中的所有记录，结果分两部分，一部分从表有数据（类似于内连接），另一部分从表数据为NULL

**当为左外连接时，left join左边的是主表；当为右外连接时，right join右边的是主表。**即左外交换顺序则可达到右外同样的效果。要查询的信息主要来自哪个表，哪个表就当主表。

1
2
3

select b.name from beauty b 
	left join boys bo on b.boyfriend_id=bo.id 
	where bo.id IS NULL

对查询结果进行判断是否为NULL来取出不是交集的值时，最好是对从键的主键进行判断，因为其他值可能正常情况下也会为NULL，只有主键永远不会为空，为空就是外连接没匹配到的那部分数据

mysql不支持全外连接，全外连接就是内连接+表1有但是表2没有的+表2有但是表1没有的 数据

交叉连接就是笛卡尔乘积现象，表1的数据乘以表2的数据，关键字是CROSS JOIN ，没顺序之分

子查询

出现在其他语句中（不仅仅是select语句，其他语句也可以使用子查询）的select语句，称为子查询或内查询；内部嵌套其他select语句的查询，称为外查询或主查询

标量子查询

标量子查询一般搭配着单行操作符使用，> < >= <= = <> ；

1
2
3

SELECT * FROM `employees`  
WHERE salary> 
	(SELECT salary FROM employees WHERE last_name='Abel')

也可以搭配分组使用

1
2
3
4
5

SELECT MIN(salary) minsalary FROM employees 
GROUP BY department_id 
HAVING minsalary>
	(SELECT MIN(salary) FROM employees where department_id=50)
//在标量子查询中（有大于等于判断），子查询的结果必须只有一行。不可以查出多行，会产生报错。因为大于等于之类的判断无法同时判断多个值（需要in）

子查询的执行是优先于主查询执行的，因为主查询的条件用到了子查询的结果

多列子查询

列子查询一般搭配着多行操作符使用，in 、any/some 、 all。

**in /not in 等于查询的条件是子查询结果列表中的任意一个；**ANY/SOME 是和子查询返回的任意某一个值比较（如果是小于任意一个，则是小于最大的那个就行，用max()更合适，可读性更高）；ALL是和子查询返回的所有值进行比较（小于所有值则是小于最小值）。

行子查询

结果集一行多列或多行多列

1
2
3
4

SELECT * FROM employees
WHERE (employees_id,salary)=(
	SELECT MIN(employees_id),MAX(salary) FROM employees
)

放select后面的子查询

1
2
3
4
5
6

查询每个部门中的人数
select d.*, 
(
	select count(*) from employees e WHERE d.department_id=e.department_id
)	个数
FROM departments d

这种方法仅仅支持标量子查询，即子查询返回的值只能是一行一列

放在from后面的子查询

将子查询充当成一张表。子查询必须起别名，不然找不到。也可以搭配内外连接，这相当于一个新的表，里面的字段就是查询出来的字段

1
2
3
4
5

//查询每个部门的平均工资的工资等级
SELECT ag_dep.*,job_grades.grade_level
FROM
(select AVG(salary) ag ,department_id from employees GROUP BY department_id) ag_dep 
INNER JOIN job_grades ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

放在exists后面（相关子查询）

exists(完整的查询语句) 用于判断后面的子查询是否有值，返回一个布尔类型，有就是 1。无论结果有多少，它只会返回一个0或者1 [单行单列]，用exists可以实现的用in也能实现

1
2
3
4

//查询有员工的部门名
select department_name FROM departments d where EXISTS(
	select * from employees e where d.department_id=e.department_id
)

查询都是一条一条执行，一条一条显示的，因此当主表查出一条数据，然后拿主表的某个字段和从表进行判断。判断失败则会不显示，放select from exists where后面都是如此，甚至其他查询也是如此。

分页查询

1
2

select 查询列表 from 表 limit offset,size;
limit是偏移量，排除从头开始共limit条数据，显示显示size条数据.

limit语句放在查询语句的最后

联合查询

当查询的结果来自多个表，且表之间没有直接连接关系，但查询的结果列数一致时,可以用联合查询将多个独立的查询的结果合并成一个结果（并集）

1
2
3
4
5

select id,name from employees wherer email like '%a%'
UNION
select uid,uname from user wherer uid>90
UNION
....

最后表名为最开始的查询字段名，即id,name；uid和uname不会进行显示。

注意：

1.要求多条查询语句的查询列数要一致

2.要求多条查询语句的查询的每一列的类型和顺序最好一致（id对uid，name对uname）

3.UNION关键字会默认去重（即表之间查询出的语句有相同的行内容），如果不想去重则需要使用 UNION ALL来包含重复项

新增数据

1
2
3
4
5

insert into 表名（列名） values(值...),(值....)
列和值要一一对应，省略列名时值要和表中列一一对应。此方法支持插入多行，

此方法也支持子查询插入(将查询出来的结果集插入到表中，不需要加values关键字)
insert into 表名（列名） select id,name,age from user

1
2
3
4

也可以通过
insert into 表名
set id=1,name="xx"
来实现新增时只给指定值进行赋值，其他值为默认值，不支持插入多行和子查询

修改数据

1
2

update 表名 set 列=新值,列=新值...  where 条件
一定要加条件，否则是将这个表中所有数据对应的列数据进行修改

update也可以进行连接来修改多个表的记录（了解即可）

1
2
3
4
5
6
7
8
9

update 表名 别名
inner|left|right join 表2 别名 on 条件
set 表1.列名=值，表2.列名=值
where 表1.列名=条件 and 表2.列名=条件

update user u inner join admin a on u.id=a.uid
set u.name='admin',a.type='success'
where u.id=1
这样就可以实现对user表中id为1的name列和admin表中uid为1的type列进行修改

删除数据

删除单行数据

1
2

delete from 表名 where 条件
不加条件则把表中数据全部删除

删除多行数据（了解即可）

1
2
3

delete 要删除的表的别名
from 表1 别名 join 表2 别名 on 连接条件
where 筛选条件

TRUNCATE TABLE 表名 清空数据，将表中数据全部删除，这个删除不允许加where，只能删除全部。

DELETE删除后自增长列的值从断点开始，而TRUNCATE自增长列重新从1开始

当涉及事务时，DELETE删除可以回滚，而TRUNCATE不能回滚，所以尽量少使用TRUNCATE

DDL对库和表的操作

CREATE DATABASE [IF NOT EXISTS] 库名 ，IF NOT EXISTS 表示如果库不存在则创建库，存在则警告，不会报错。IF EXISTS则相反意义

ALTER DATABASE 库名 SET CHARACTER SET gbk 更改库的编码

DROP DATABASE 库名 删除库

表的操作

DESC 表名 查看表结构

创建表

1
2
3
4
5

create table 表名(
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束]
)

修改表

1
2
3
4
5
6
7
8
9

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新列名的类型     //修改列名，COLUMN可省略，其他操作不能省略，建议都加上

ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 新约束    //修改列的类型或约束,不加约束则是将原有约束全删除

ALTER TABLE 表名 ADD COLUMN 列名 列类型 [first|after 字段名]   //添加新的列（可以指定在第一行或某行之后，默认是添加到最后）

ALTER TABLE 表名 DROP COLUMN 列名    //删除列

ALTER TABLE 表名 RENAME TO 新表名    //修改表名

DROP TABLE [IF EXISTS] 表名 删除表,IF EXITS仅仅适用于库和表

复制表

1
2
3
4
5

CREATE TABLE copy_table LIKE table    //仅仅复制表的结构

CREATE TABLE copy SELECT * FROM table    //复制table表的结构+数据

CREATE TABLE copy SELECT id,name FROM table where nation='中国'   //复制部分结构和部分数据，可以设置where 1=2来复制部分结构不复制数据

数据库数据类型

浮点型如果小数位超出，则会四舍五入显示最大长度的值，如类型 (2,1) 的1.26显示为1.3。而定点型则是直接截断

字符型：

enum类型：

枚举类型，要求插入的值必须属于列表中的指定的值。忽略大小写。c1 ENUM('a','b','c')

set类型：

集合类型，和ENUM类型类似，最大的区别是集合可以对于一个插入选取多个成员，而ENUM只能选一个。如 s1 SET('a','b','c')，它可以插入VALUES(‘a,b’)

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

约束

UNIQUE：唯一可为空，用于保证该字段的值具有唯一性，但可以为空（只能有一个值为null,因为两个null就重复了，违反了唯一的性质【版本不同可以会可以存在多个NULL】），primary key是唯一且不能为空

主键有且只有一个，唯一约束可以有多个

FOREIGN KEY：外键约束 ，用于限制两表的关系，保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。在创建表时使用外键约束是无效的，只有在修改时有用

CHECK检查约束在mysql中无效

SHOW INDEX FROM 表名 查看stuinfo表中的所有索引，包括主键、外键、唯一和自己创建的索引

列级约束在字段后添加即可，表级约束则是 [CONSTRAINT 约束名] 约束类型(字段名) , 外键约束有点特殊，是 [CONSTRAINT 约束名] FOREIGN KEY(此表中的字段名) REFERENCES 主表(主表的主键)，外键表名通常是fk_当前表名_主表名。CONSTRAINT是别名的关键字，但是对主键起别名在mysql中是没有效果的

外键的主表关联列必须是一个key(即必须是一个主键或唯一键【数据唯一性】)，插入数据时先插入主表再插入从表，删除数据时则先删除从表数据再删除主表数据。

如果想只删除主表而不动从表，则需要使用级联删除或者级联置空。

级联删除只需要在创建或修改约束时在后面加 ON DELETE CASCADE,设置级联删除后，当删除主表的数据时会同时删除从表中外键值为主表key字段值的数据

级联置空则是在创建或修改约束时在后面加 ON DELETE SET NULL,意思是当主表数据被删除时，从表中所对应的外键值变为NULL

1
2
3
4

ALTER TABLE 表名 ADD 约束(字段名)   //为某字段添加表级约束，列级约束只能使用MODIFY,表级约束两种方法都可以使用
ALTER TABLE 表名 MODIFY COLUMN 列名 原字段类型 新约束    //修改列的约束,不加约束则是将原有约束全删除

ALTER TABLE 表名 DROP 约束类型 约束的字段名   //删除约束，仅限于表级约束。列级约束使用MODIFY即可

列级约束语法都支持，但外键约束没有效果；表级约束默认约束(DEFAULT)和非空约束不支持，其他都支持。

AUTO_INCREMENT:自增长列约束，默认从1开始；如果想更改起始值，可以通过插入一行数据设置自增长列为你想要的起始值，如设置为10，后插入的则都会从10开始自增1。一个表最多只能有一个自增长列，且标识列的类型只能是数值型（int、float等）

事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

事务由一个或多个SQL语句组成一个单元，在这个单元中，每个语句是相互依赖的。整个单元作为一个不可分割的整体，如果单元中的某条SQL语句执行失败或发生错误，整个单元将会进行回滚。所有受到影响的数据将返回到事务开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺利执行。

**而在mysql中使用最多的存储引擎是innodb,myisam,memory。其中只有innodb支持事务。**通过 SHOW ENGINES 可以查看当前数据库支持的存储引擎

在mysql中想开启事务必须先设置自动提交功能为禁用，set autocommit=0;，这个值是控制隐式事务的自动提交(insert、update、delete都属于隐式事务)。这个命令只针对当前会话有效。关闭后又会恢复开启状态

创建事务后，更改的数据会先暂存在内存中，直到提交或回滚才会确定是否落盘；

脏读发生的情况就是读到了另一个事务在内存中的数据。不可重复读则是再次读取时读到了另一个事务提交后的字段（两次查询结果不同）。幻读则是一个事务提交后，另一个事务进行更新列操作会包含前一个事务（如更新多行符合条件的列会把另一个事务新增的符合条件的列也进行更新）；通过更改事务级别避免这些问题。

mysql的默认级别为REPEATABLE READ 可重复读。可以避免脏读和不可重复读，幻读无法避免

select @@tx_isolation; 查看隔离级别；设置事务级别可以设置为只针对当前会话有效[set后加session]，设置全局[加global]，详细命令百度即可。

delete删除的数据可以通过回滚，但是truncated删除的数据是无法回滚的，一定要注意

视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

创建视图：
create view 视图名 AS 复杂查询语句;

使用
select * from 视图名 where 条件语句

修改：
create or replace view 视图名 AS 查询语句;     如果视图存在则修改，不存在则创建
alter view 视图名 AS 查询语句;     修改视图

查看视图:
DESC 视图名;
SHOW CREATE VIEW 视图名;    查看视图的详细信息

删除视图:
DROP VIEW 视图1,视图2,...;

视图就类似于字符串拼接，将一个复杂的查询语句包装成一个视图进行重复使用，后期使用时再加各种条件筛选出数据。

视图可以实现

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性（对原始数据进行封装，与原始表相分离，查询视图返回的就只有创建视图时提供的字段，而不是所有字段，避免被人拖库等安全性问题）

视图也可以进行增删改查，语句和操作表相同，当用sql语句对视图进行更改时，会转换成对原表操作的sql语句。但如果mysql不能正确的确定被更新的基数据，则不允许更新(即视图中的某个字段在原表中并不存在，则不能进行更新)

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的

• 包含某些关键字的sql语句: 分组函数、distinct、group by、having、union、union all
• 常量视图
• select中包含子查询
• join
• 创建一个视图，其中包含查询一个不能更新的视图
• where的子查询引用了from关键字

只要是对原表的数据进行过处理的视图都是不可更新的

变量

变量分为系统变量和自定义变量；系统变量中有全局变量和会话变量；全局变量更改后针对所有会话有效，但是重启mysql后就会失效，会恢复为配置文件中的默认值

系统变量

1
2
3
4
5

SHOW GLOBAL|[SESSION] VARIABLES [LIKE '%ab%'];   查看系统 全局/会话变量,session不写默认也是查看会话变量，可以加like查看某些变量

SELECT @@[global.|session]系统变量名   查看指定的系统变量的值，加global则是查看全局的，不加或加session则为会话的

SET @@[global.|session]系统变量名 = 值		设置变量值，加不加global意义与上面一致

自定义变量

用户自定义的用户变量，只针对当前会话有效

1
2

声明并初始化(也可以为原有值进行赋值)：	SET @用户变量名=值
查看自定义变量：SELECT @用户变量名

局部变量仅仅在定义它的begin end中有效（类似于代码块），且必须放在begin end中的最前面。

1
2
3

声明： DECLARE 变量名 数据类型 [DEFAULT 值];    //局部变量必须声明且赋值，用default可以在声明时赋值
赋值： SET 变量名=值	//注意局部变量没有@
使用： SELECT 变量名

存储过程

存储过程和函数都相当于golang中的函数，用于封装一组sql语句集合。它可以提高代码的重用性，简化操作

并且第一次使用后会进行编译，而编译过后执行速度会比普通的sql语句快。且他封装后只需要连接一次数据库，平常却是有多少sql语句连接多少次；这些优点提高了存储过程的效率

创建存储过程

1
2
3
4
5

DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组合法的SQL语句);
END $

要注意的是：

参数列表包含三部分，参数模式 参数名 参数类型，如 IN stuName varchar(20)

参数模式有IN、OUT、INOUT，IN表示该参数可以作为输入，也就是该参数需要调用方传入值；OUT则是该参数可以作为返回值[sql中没有return]，INOUT就是该参数需要传入值，同时也可以返回值；参数之间用逗号隔开

如果存储过程体仅仅只有一句话，BEGIN EDN可以省略

存储过程体中的每条SQL语句的结尾要求必须加分号，但是分号又会和普通语句的分号冲突，因此需要使用 DELIMITER 结束标记 来给存储过程体的结尾分号起别名

调用存储过程

CALL 存储过程名(实参列表)创建时定义的结束标记 ,例如 CALL myp1()$

案例：创建存储过程，实现用户是否登录成功

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

//创建
DELIMITER $;
CREATE PROCEDURE login(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;
	SELECT count(*) INTO result 
	FROM admin
	WHERE admin.username=username
	AND admin.password=password;

	SELECT IF(result>0,'成功','失败');
END $

//调用
CALL login('admin','123456')$

删除存储过程

drop procedure 存储过程名 ，一次只能删除一个

查看存储过程则是 SHOW CREATE PROCEDURE 存储过程名;

函数

它和存储过程很像，区别是存储过程有0个或多个返回值，而函数有且只能有1个返回值

调用语法：SELECT 函数名(参数列表) ,执行函数中的所有语句，并且显示返回值

流程控制

if语句(case在之前的流程控制有)

1
2
3
4
5
6
7

功能：实现多重分支，只能应用在BEGIN END中

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
[ELSE 语句n;]
END IF

循环

索引

索引分为单值索引和复合索引；索引原理就是排序哈希表，就是排好序的快速查找数据结构。加速where和order by的查询

1

create index 索引名 on 表名(字段名,字段名...)     //为某字段建立索引，索引名通常取名为idx_表名_字段名;有多个字段时采用驼峰命名

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。经常性的修改和物理删除数据会导致数据不连贯，索引出现不准确的问题，因此建议使用软删除(delete_at)。且变化频繁的数据不太适合建索引，它会导致频繁更新索引

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新添加的索引列字段，都会调整因为更新所带来的键值变化后的索引信息

一张表最好不要建立超过五个索引，且通常复合索引用的多。

在创建表时，主键已经默认成为了一个唯一索引

哪些情况需要创建索引

哪种情况不需要创建索引

对差异率和重复率不高的数据(重复且平均分配的值)进行创建索引没有太多意义。索引的选择性是指索引列中不同值的数目与表中记录数的比。比如有2000条数据，1980个不同的值，它选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

EXPLAIN(查询执行计划)

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理SQL语句的。分析查询语句或表结构的性能瓶颈。语句就是explain+SQL语句

它可以获取到：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

id

它是select查询的序列号，包含一组数字，表示查询中执行select语句或操作表的顺序

它有三种情况，id相同，id不同，id相同和不同都存在的情况

select_type

table

表示这一行的数据是关于哪张表的

type

访问类型排列

从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL，一般来说，当数据量很大时，得保证查询至少达到range级别，最好能达到ref级别。

possible_keys

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际应用

key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引(查询的字段和创建的索引字段相吻合)，则该索引仅出现在key列表中。即理论上不需要使用索引，但实际运行时发现有复合索引和查询的字段相吻合，则实际使用了这个索引

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。也变相的表示为条件字段的个数，如where后的条件字段类型是char(4),那它的长度则是utf-8 3字节*4 定长 +一个允许为NULL的字节，即为13

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

即同样的查询结果下，len越小越好

ref

显示索引的哪一列被使用了，如果可能的话，最好是一个常数。说明哪些列或常量被用于查找索引列上的值(where后的判断条件值的类型，如t1=‘ac’，那ref显示的就是const,因为’ac’是一个常量)。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。即读了原表多少条数据查到目标数据(值越小越好)，不建索引时读取肯定会读全表，建立之后通过索引查询，这个rows就会减少许多

Extra

包含不适合在其他列中显示但十分重要的额外信息

Using filesort:文件排序，说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序称为 文件排序。如本身的索引是b1_b2_b3，select b1字段使用部分索引，而order by b3，跳过了b2，就会出现order无法使用索引，只能自己根据表进行排序，影响了性能

Using temporary:使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于排序order by和分组查询group by。

Using index:表示相应的select 操作使用了覆盖索引（select 后的数据列只用从索引中就能够取得[部分满足也可以]，不必读取数据行，查询列被所建的索引覆盖），避免了访问表的数据行，提高了效率；如果同时出现了using where，表明索引被用来执行索引键值查找；如果没出现using where，表明索引用来读取数据而非执行查找动作。

Using where:表明使用了where过滤

Using join buffer:使用了连接缓存

Impossible where:where子句的值总是false ，不能用来获取任何元组

要注意的是如果在where后索引字段使用 in 或者 > <等范围会导致索引失效，如果要解决这个就尽量用等号，功能无法用等号实现，就绕过这个范围判断的字段来创建索引

*尽量减少使用select ，这样只会使用到全表扫描，无法使用索引，降低性能

当两表链接时，从表一定要加索引[根据条件创建索引]，因为关联起来时主表肯定数据全部都需要，而从表是根据条件[字段和主表某字段相等]来获取交集或者差集，因此给从表加索引可以加速从表的查询速度，最大化的优化查询

永远是小表驱动大表，如书籍类目表和书籍表，以类目表为主表。因为类目表数据更少，主表一般都是所有数据都会保存，如果把书籍表当成主表，数据量更大，io操作更多，性能就无法提升上来。

索引失效的原因

1. 最佳左前缀法则：如果创建了复合索引，要遵守最佳左前缀法则。指的是where查询从索引的最左前列开始并且不跳过索引中的列。因为一跳过或者只取之后字段就会和复合索引不匹配，部分使用如果只使用第一个之后的字段也会导致索引失效。[带头大哥不能死，中间兄弟不能断]
2. 在索引列上做任何操作（计算、函数、[自动或手动的类型转换]）都会导致索引失效而转向全表扫描
3. 存储引擎不能使用索引中范围条件后右边的列**[范围之后全失效]。**即条件判断不是常量而是范围(如age>15)就会导致范围字段之后的索引失效(范围判断本身的索引字段age不会失效)
4. 尽量使用覆盖索引(只访问索引的查询[索引列和查询列一致])，减少select *
5. MySQL在使用不等于(!= or <>)时会导致无法使用索引，转而变成全表扫描
6. is null 和 is not null 也会导致索引失效
7. like以通配符开头(’%abc…’)时，MySQL的索引会失效，从而变成全表扫描ALL **[百分like加右边]，如果非要开头加通配符的话，select之后可以通过使用覆盖索引或者部分覆盖来解决索引失效的问题。**k%kk%不会导致失效，从另一个角度来看它都在右边
8. 字符串不加单引号会导致索引失效(发生了自动的类型转换，符合第2点)
9. 少用or，用它来连接时会导致索引失效

where条件后如果顺序不对，但字段对的上索引时，MySQL的优化器会自动排序。但是最好一致，避免MySQL再次翻译转换

查询优化

1.永远小表驱动大表

即小的数据集驱动大的数据集。如书籍类目表和书籍表，以类目表为主表。因为类目表数据更少，主表一般都是所有数据都会保存，如果把书籍表当成主表，数据量更大，io操作更多，性能就无法提升上来。

原理是exists会先查询外表数据，然后将其放到子查询中做条件验证 [遍历]，根据验证结果是TRUE还是FALSE还决定主查询的数据结果是否得以保留。因此B表多于A表数据时使用exists。

而in是先查询子查询的数据，再循环多次查询外表数据，因此B小于A时使用in

order by 关键字优化

• order by 尽量使用index方式排序，避免出现using filesort 问题，且不要使用select *
• 尽可能在索引列上完成排序操作，遵照索引的最佳左前缀原则。不用索引排序则会出现using filesort问题
• 如果真的有using filesort问题出现，且无法进行SQL语句优化，修改配置文件，尝试提高 sort_buffer_size或 max_length_for_sort_data

索引用于查找和排序，所以如果where后有order时，它也会使用索引，如果条件和order是顺序的，如创建了索引c1_c2_c3，条件是 where c1=x and c2=y order by c3 ，虽然explain显示只有两个索引被使用，但c3也被使用到了，只是用在排序上没显示而已。c1=x and c2>y order by c2,c3也可以使用索引，虽然c2断了，但是后续order by的c2接上了。

但如果 c1=x order by c3则会出现 using filesort 问题，因为索引断了；且 c1=x order by c3,c2也会出现相同的问题，因为order by调换位置显示的结果是不同的，无法使用优化器自动调换。而 c1=x order by c2,c1 不会出现这个问题，因为c1通过条件判断已经是个常量，优化器直接省略了它。order by c1 asc,c2 desc 由于两个排序方式不同也会导致using filesort问题。

因此order by 语句最好使用索引最左前列原则，或者where子句与order by 子句条件列组合起来满足索引最左前列也可以。

出现using filesort的情况分别有：

1. ASC DESC不一致
2. 丢失第一个索引字段
3. 丢失中间索引字段
4. order by中出现了不是索引字段中的字段
5. 第一个字段在where子句那里并且使用了大于小于等或者in，且order by排序没有重新从第一个索引字段开始

group by也同理，分组之前必排序，且无法解决using filesort时也需要更改配置文件。where高于having，能写在where限定的条件就不要去having限定了。

1. mysql有两种排序方式，文件排序(using filesort)或扫描有序索引排列(using index)
2. mysql能为排序和查询使用相同的索引，即如果排序和查询都用上索引则是最优解

慢查询日志

show variables like '%slow_query_log'; 查看慢查询日志是否开启。set global slow_query_log=1;开启

show global variables like 'long_query_time%';查看运行时间，只有在大于这个值时才会记录。set global long_query_time=3 设置运行时间阈值，设置完后马上生效，但是要重启会话才能查到新值

mysqldumpslow 日志分析工具

show profile

它是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况和生命周期各部分消耗情况。可以用于SQL的调优的测量。

show variables like 'profiling' 查看是否开启

set profiling=on; 开启此功能

show profiles; 查看最近执行的sql， show profile cpu,block io for query Query_id 根据查出来的Query_id查看某条sql的完整生命周期每一步耗费的时间和资源

常见情况：只要有其中一个就必须得优化

排查流程：

1. 收到故障消息
2. 开启慢查询日志，抓出执行慢的SQL
3. EXPLAIN分析
4. 分析没找到问题，进一步分析，使用show profile

全局查询日志

功能类似于show profile，建议使用show profile

数据库的锁

表锁

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率高，并发度最低

创建表时在创建结尾添加engine=myisam; 即使用这个引擎。

show open tables; 查看数据库有哪些表锁

因此表锁有必要设置为读锁，而写锁尽量少使用

读锁

在一个会话中对某个表加读锁后 lock table mytabs read

• 当前会话可以读这个表但不能读其他表，无法写，即只有解锁后才能进行其他操作。
• 其他会话可以读这个表和其他表，写会阻塞，直到会话解锁。

写锁

在一个会话中对某个表加读锁后 lock table mytabs write

当前会话可以读这个表和写这个表，但不能读写其他表。即只有解锁后才能进行其他表的操作。

其他会话可以读其他表，读和写这个表都会阻塞，直到会话解锁。

简而言之，针对其他对话，读锁会阻塞写，但不会阻塞读。而写锁会把读和写都阻塞。

而加了锁的那个会话，为读锁时只能读当前表，不能写当前表和读写其他表;为写锁时能读和写当前表，不能读写其他表。都只有解锁后才能操作其他表。

行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度最高

创建表时在创建结尾添加engine=innodb; 即使用这个引擎。可以不用，因为默认就是innodb引擎

innodb会对使用索引的列添加行锁，即sql语句带索引的时默认为行锁。如果没索引或者 索引失效[严重] 则会变成表锁

行锁时，当前表是处在事务中的情况。其他表可以读但不能写同一行数据(读的以前数据，避免脏读)，会阻塞直到事务commit才能写

分析用show profile即可看见lock的情况

优化建议：

1. 尽可能让所有数据检索都通过索引来完成，避免无索引或索引失效让行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度

间隙锁

因此很多框架删除功能在底层也是update delete_at(软删除)，原因就是避免间隙锁的发生