Mysql知识点

DBMS:数据库管理系统

SQL：结构化查询语言

数据库最小单元是table

row 行，信息

colum:列，字段

---

基础查询

show databases; #查看数据库

use 数据库名; #进入选择的数据库

select database(); #显示当前所在数据库名

desc 列表名； #显示列表结构

show tables; #展示当前数据库中表格

select version(); #查看数据库版本

---

DQL 数据查询语言基础

条件语句基础查询语句

select 字段名 from 表名; #查询一个字段

select 字段名1，字段名2，... from 表名; #查询多个字段

列起别名

select empname,salary as empsal from table1;

select empname,salary empsal from table1; #也可以不用as，直接空格

#从表格中选择empname和salary列，并且把salaey列改名为empsal(只是更改了显示的内容，库中的原表并没有被修改）

列参与数学运算

select empname,salary*12 as '年薪' from table1;

#从表中选择empname和salary*12并取名为年薪

运算符

=

select empname,salary from table1 where salary = 1000;

#从表中查询工资等于1000的员工信息

<= 和 <

#select empname,salary from table1 where salary <= 1000;

#从表中查询工资小小于等于1000的员工信息

>= 和 >

#同上

between ... and ...

select empname,salary from table1 where salary between 1000 and 2000;

#从表中查询工资范围在1000到2000的员工（包括1000和2000）

is 和 is not

select empname,salary from table1 where salary is null;

select empname,salary from table1 where salary is not null;

#查询没有工资的员工姓名（对于null 只能用 is来判断，不能用=）

in 和 not in (in相当于多个or,not in 主要用于is或not in中）

select empname,salary from table1 where salary in(1000,2000);

#从表中查询工资为1000或2000的员工信息（这里括号内代表两个数字，不代表区间范围）

and 和 or （and优先级高于or)

select empname,salary from table where empname = 'boris' and salary = 1000 or 2000 ;

select empname,salary from table where empname = 'boris' and (salary = 1000 or 2000);

#注意两个查询语句语法的差别。查询目的是从表中查询名字为boris，并且工资为1000或2000的人。第一个查询语句中有错误，因为and优先级高于or，所以用第二种形式，用括号括起来，可以实现从表中查询姓名为boris，工资为1000或者2000的员工信息

like (模糊查询）

%代表匹配多个字符

_代表匹配一个字符

select empname from table1 where name like 'a%'； #从表中查询第一个字母以a开头的员工的名字

select empname from table1 where name like '_a%"； #从表中查询以第二个字母以a开头的员工的名字

select empname from table1 where name like '%a%'； #从表中查询名字中带有a的员工的名字

select empname from table1 where name like '%__a*'； #从表中查询名字第三个字母是a的员工的名字

---

字段排序

select empname,salary from table1 order by salary; #默认为升序

select empname,salary from table1 order by salary desc; #降序

#从列表中查询empname和salary，并以salary进行排序

select empname,salary from table1 order by salary,empname asc;

#从列表中查询empname和salary，并先以salary进行排序，如果有empname重复的，对重复的empname按照升序进行排列

---

DDL

DDL（数据定义语言）是用来定义数据结构，完成数据库对象的创建（CREATE）、修改（ALTER）、删除（DROP）

创建数据库

语法：create database 数据库名 character set 字符集名 [collate 校对集名] ；

注意点：

1、创建数据库时，默认字符集为latin1，如果之后的数据库中要存储汉字，则需要在创建数据库时指定汉字字符集，如：utf8

2、每一个字符集都有多个校对集，也有对应的默认校对集。如果要指定默认校对集外的其他校对集，则需要使用collate 来指定。

查看当前数据库的字符集；

show variables like '%character%' ; （查看character_set_database 对应的值）

查看mysql所支持的所有字符集：

show character set ；

查看mysql所支持的所有校对集：

show collations ；

---

修改数据库

alter database 数据库名 character set 字符集名 ；

alter database 数据库名 collation 字符校对集 ；

删除数据库

drop database 数据库名 ；

创建数据表

语法：create table 表名（属性名 数据类型 [约束] , 属性名 数据类型 [约束],……）[engine=表类型] [charset=字符集];

注意点：

1、创表时定义每个属性都必须指定对应的数据类型；

2、属性可以执行约束，也可以不指定，或者将属性定义完以后，再指定约束。

3、属性之间用 , 逗号隔开，括号中的最后一行末尾没有逗号

4、创表时，默认使用的表类型为InnoDB,如果要使用其他表类型，需要关键字 engine 来指定。

5、创表时，数据表默认使用当前数据库的字符集。如果要使用其他字符集，则需要关键字 charset来指定。

查看当前数据库下所有的表：

show tables ;

查看某张表的数据结构：

desc 表名 ；

查看某张表的创表语句：

show create table 表名 ；

---

数据类型

1、整型 ：int 、bigint

2、浮点型 ：float(m,d) 、double(m,d)

3、字符串型: char(n) 、varchar(n)

4、日期时间型 : date 、time 、datetime

---

约束

1、主键约束 primary key

通过数据表中的一个或多个属性能够标识表中的唯一一行记录，这一个或多个属性称为该表的主键

特点：

a、主键属性的值不能重复

b、主键属性的值不能为空（多属性组时，组中的每个属性都不能为空）

c、一张表中只能有一个主键（可以是一个属性，也可以是多个属性组成的属性组）

d、主键会默认创建一个索引来加快查询速度

定义主键：

1）、列级约束定义:定义属性时，指定某个属性为主键(只能定义单属性为主键，不能定义属性组)：

create table 表名（属性 数据类型 primary key , 属性 数据类型 [约束] ,……）;

2）、表级约束定义：定义完所有属性后，指定某个或某几个属性为主键（属性组时，每个属性之间用逗号隔开）：

create table 表名（属性1 数据类型 ，属性2 数据类型，…… ，primary key(属性/属性组)）；

2、唯一约束 unique

确保不是主键的属性不会出现重复数据

特点：

a、唯一约束的属性值不能重复

b、唯一约束的属性值可以为空

c、一张中可以有多个唯一约束

d、唯一约束也会创建一个唯一索引

定义语法：

1）：列级约束定义:定义属性时，指定某个属性为唯一约束：

create table 表名（属性 数据类型 unique , 属性 数据类型 [约束] ,……）;

2）：表级约束定义：定义完所有属性后，指定某个属性为唯一约束：

create table 表名（属性 数据类型 ，…… , unique key(属性) [,unique key(属性2)]） ；

3、外键约束 foreign key

当表A中的某个属性 能参照到 表B中的主键或者唯一键时，可以设定该属性为表A的外键。同时称表A为从表，表B为主表

特点：

a、外键关联的属性必须是主表的主键或者唯一键。

b、外键属性中的值，必须是主表关联属性中存在

c、当主表关联属性中的值发生变化时，从表的外键属性的值为发生响应的关联变化。

d、设定外键的属性要与主表关联属性的数据类型一致。

f、一张表中可以设置多个外键属性

定义语法：

1）：表级约束定义：

create table 表名（属性 数据类型，……，foreign key (属性) references 主表名（关联属性） on update cascade on delete cascade）;

foreign key ：指定外键属性名

references ：指定参照表名（即主表名）和关联的主键或者唯一键的属性名

on update 、on delete : 指定主表主键属性的删除和更新 ，不指定则主表的主键不能删除和更新

cascade ：表示从表会随着主表一起变化 ； set null ：表示主表变化时，从表对应设置为空值。 no action :表示主表变化时，从表不变

4、非空约束 not null

确保属性列中的数据不能为空，如果没有明确指定非空约束，则默认允许为空

定义语法：

列级约束定义：

create table 表名（属性 数据类型 not null ,……）;

5、默认值约束 default

给设定默认值约束的属性一个默认值，当向表中插入数据时，如果有明确的插入数据，则使用插入的数据。如果没有明确的向该属性插入值，则使用默认值。

定义语法：

列级约束定义：

create table 表名（属性 数据类型 default 值 ,……）;

同一张表中的属性可以定义多个约束，用空格隔开

---

修改表

1、修改表结构

1）：新增属性

语法：alter table 表 名 add 属性名 数据类型 [约束] ; (如果有约束，只能是列级约束)

2）：修改属性(要修改属性名，只能使用change)

语法：alter table 表名 modify 属性名 数据类型 [约束] ；

alter table 表名 change 原属性名 新属性名 数据类型 [约束] ;

注意：此方法可以对列级约束进行删改，如果没有则添加约束。不能操作外键约束

3）：删除属性

语法：alter table 表名 drop 属性名 ；

2、修改约束

1）：新增约束

语法：alter table 表名 add constraint [约束名] 约束类型（属性）；

例：alter table teacher add constraint primary key (tid);

alter table student add constraint unique (name) ;

alter table teacher add constraint fk_01 foreign key (name) references student(name) ;

2）：删除约束

语法：alter table 表名 drop 约束类型 [约束名]；

例： alter table teacher drop primary key;

alter table student drop index name; （删除唯一约束时，要按照索引名字删除唯一索引（name为唯一索引名），一般索引名字就是对应属性名字）

alter table teacher drop foreign key fk_01; （删除外键时，要指定外键名，然后还要删除和删除唯一键一样删除外键的索引）

注意：用此方法只能对表级约束进行操作

3、修改表字符集

语法：alter table 表名 convert to charset 字符集名 ；

4、修改表名

1）：alter table 表名 rename to 新表名；

2）：rename table 原表名 to 新表名；

---

DML

数据操纵语句，主要是对数据表的数据进行增（insert into）删 （delete）改（update）

数据新增/插入数据

基础语法(向表中某些属性插入数据)：insert into 表名 (属性1,属性2 , ……) values (值1，值2，……) ；

注意点：

1、插入数据的值要与表后列举的属性一一对应；

2、值要满足对应属性的数据类型和约束（字符串、时间要用引号括起来）；

特殊语法（向表中所有属性插入数据）：insert into 表名 values（值1，值2，……）;

注意点：

1、插入数据的值要与表中的所有属性一一对应；（用desc 表名 查看表结构和属性的顺序）

2、值要满足对应属性的数据类型和约束（字符串、时间要用引号括起来）；

特殊用法2（将一张表的数据查询出来后，插入新的表中）：

insert into 表A（属性a1,属性a2，……） select 属性b1,属性b2，…… from 表B where子句 ；

注意点：

1、从B表中查询出的属性要与A表中要插入的属性一一对应；

2、查询出的值，要满足A表对应属性的数据类型和约束；

3、不需要关键字VALUES；

数据更新/修改

update 表名 set 属性=新值，属性2=新值，…… where子句 ；

注意点：

必须要加where子句，如果不加 则会修改全表数据。

删除数据

delete from 表名 where子句 ；

注意点：

必须要加where子句，如果不加 则会删除全表数据，可回滚。

truncate 表名 ；

直接清空全表数据，与delete from 表名 不加where子句是一个效果。但是比delete 效率要快，不可回滚。

---

DQL

DQL（数据查询语言）主要用于进行数据表中数据的查询

基础语法：select 属性1，属性2，…… from 表名 ；

注意：当要查询表中所有数据时，可以使用 * 来代替所有的属性。

算术运算

对select的查询结果基础上，进行算术运算

例：查询每个员工的姓名、工资和年薪

给员工每人涨薪500元，查询出所有员工涨薪后的工资

限定运算

在查询之前可以使用where子句对查询进行限定，限定后查询出满足条件的结果。

例：查询3号部门中工资上万的员工的信息

在where子句中可以有算术运算、比较运算、逻辑运算：

例：查询年薪大于15万的员工的信息

普通比较运算： = 、> 、>=、 < 、<= 、!= （<>）

例：查询3号部门以外的所有员工的信息

特殊比较运算：

属性 between n1 and n2 ：属性在n1到n2之间，包含n1,n2 （n2必须大于n1的值）

例：查询3月到7月入职的员工的信息

属性 in (列表) ： 属性分别匹配列表中的每一个值

例：查询3号和4号部门的所有员工的信息

like : 模糊匹配 （% ：若干个任意字符 _ ：一个任意字符）

例：查询所有员工中名字中包含'明'字的员工信息

查询员工中名字中第二个字为'明'字的员工的信息

is null :匹配空值

例：查询奖金为空的员工的信息

逻辑运算

and 或&& ：连接两个同时成立的条件

or 或 || ：连接两个条件，只要有一个成立即可返回对应的值

not 或 ！ ：返回连接条件的相反之（如果使用！需要将后边的条件括起来）

例：查询3号部门以外的所有员工的信息

运算优先级：

算术运算（先乘除后加减） 》比较运算 》逻辑运算（先not 再 and 最后 or ）

例：查询3和2号部门涨薪1000元后，工资大于8000元且不是4号职位的员工的信息

数据去重

distinct

使用DISTINCT关键字能够将查询到的结果集中重复的记录去除

例：查询所有员工的部门号和职位号

设置别名

as （可以省略）

1、给属性取别名（主要用于别名的友好展示和查询后对属性的调用）

例：查询员工的姓名，编号，入职日期(友好展示别名)

2、给表取别名（主要用于多表查询时，用别名来引用表中的属性）

分组

group by

将查询的结果按照某个属性进行分组，然后对分组后的值进行统计（一般分组GROUP BY子句和聚合函数一起使用）

例：查询每个部门的人数

查询3,4号部门中，男女员工的平均工资

查询所有员工中每个部门的最高工资和最低工资

注意：SELECT语句后只能使用聚合函数和GROUP BY子句后出现的属性（mysql中select后可以出现其他属性，但是没有意义）

多属性分组：

group by 属性1,属性2，……： 按照属性1分组后，在每个组的内部，按照属性2再次进行分组，……

例：查询每个部门每个职位的人数

进一步限定/筛选/过滤：having

对分组统计后的结果进行进一步限定。

与where的区别：

-WHERE子句在查询前限定查询条件

-HAVING子句在分组后进行进一步限定

例：查询每个部门的总工资，返回总工资大于3万的部门和对应的总工资

排序

order by

对查询结果按照某个属性进行排序，默认升序（ASC），也可以指定降序（DESC）

例：查询所有员工的信息，按照工资进行降序排序

查询在3月到10月入职的员工信息，按照工资进行降序排序

多属性排序：

order by 属性1 排序方式 ,属性2 排序方式，…… ： 先按照属性1进行排序，如果属性1有相同值时，再按照属性2 进行排序

例：查询所有员工信息，按照部门号降序排序，每个部门内部按照工资升序排序

限定结果/分页展示

limit

对查询的最终结果限定返回的行数

1、limit n ：限定返回从第一行开始的 n行数据 。

例：查询公司员工中工资最高的3个人的信息

2、limit n,m ：限定返回从n+1行开始的m行数据。

例：查询公司员工中工资排名6到8位的员工的信息 （n+1=6, 8-n=m）：n=5,m=3

---

函数

单行函数

对查询出的每一行数据进行单独处理，并返回对应的结果

字符串函数

length(x): 返回字符串x的长度（mysql中汉字为3个长度，字母数字为1个长度）

char_length(x):返回字符串x的长度（汉字字母数字都为1个长度）

例：查询所有员工信息，返回名字超过2个字的员工的信息

查询每个员工的姓名的字符串长度

replace（x，char1,char2） :将字符串x中的char1片段替换成char2

例：查询每个员工的编号，姓名，并将编号中的00换成**展示

left (x,n) ：从字符串x的左边开始截取n个长度

right (x,n）:从字符串x的右边开始截取n个长度

substr (x，n, m) :从字符串x 的n 位置起始往后截取m个长度 ，包括第n位（mysql中字符串的下标索引从1开始。末尾往前为-1开始）

例：查询员工信息，将员工信息中编号的6/7位替换成*

concat (x1,x2,x3,……) :将字符串x1,x2,x3,……拼接成一个字符串

例：查询每个员工的姓氏并显示成xx员工

时间函数

curdate():返回当前日期

curtime()：返回当前时间

now():返回当前日期时间

last_day(x) ：返回日期x所在月的最后一天的日期

例：查询所有员工入职当月的最后一天的日期

查询在当月倒数第二天入职的员工

date_add(x , interval n day/month/year ) :返回日期 x 间隔n（天/月/年）后的日期 （n为负数，则向前间隔）

例：查询在当月倒数第二天入职的员工

datediff(x,y) :返回日期x与y 之间相差的天数（返回一个整数值，为x-y的值）

例：查询每个员工入职的天数

date_format(x , fmt) : 返回将日期x格式化成fmt字符串后输出的内容

年：%Y：四位数字的年份 月：%m：两位数字的月份 日 ：%d ：两位数字的天数

时：%H：24小时制的时 分：%i ：两位数字的分 秒 ：%s ：两位数字的秒

year(x)：返回日期x的年份

month(x)：返回日期x的月份

day(x)：返回日期X的天数

数学函数

abs(x):返回数值x的绝对值

mod(x,y) :返回x除以y的余数

pow(x,y)：返回x的y次方

round（x,d）：返回x取d位小数后的值（四舍五入）（当d为负数时，向小数点左边取舍值）

truncate（x,d）：返回x取d位小数后的值（不四舍五入）（当d为负数时，向小数点左边取舍值）

greatest（x1,x2,……）:返回参数列表中的最大值

与max()函数区别：

max（）在同一列中比较，而greaest（）在同一行中比较

least（x1,x2,……）:返回参数列表中的最小值

控制流函数

ifnull(x,y) : 当x为空值时，返回y ；当x不为空时，返回x

多用于查询时的空值转换（空值进行数学运算时，结果为空）

在oracle数据库中，使用函数NVL()函数来实现相同的功能

nullif(x,y) : 当x=y时，返回空值 ；x!=y时，返回x

if(expr ,x ,y) :当表达式expr成立 则返回x ；不成立则返回y

例：查询每个员工的姓氏，根据性别进行不同输出（XX帅哥，XX美女）

case

when expr1 then x1 当条件expr1成立则返回x1

when expr2 then x2 当条件expr2成立则返回x2

……

else x 否则返回x

end

case 属性

when x1 then y1 当属性=x1时，返回y1

when x2 then y2 当属性=x2时，返回y2

……

else y 否则返回y

end

例：计划给不同部门的员工涨薪

（1号部门涨薪1000,2号部门涨薪800,3号部门涨薪500,4号部门涨薪2000）

查询出所有员工涨薪后的薪资

---

聚合函数

也叫多行函数，将查询的多个结果值，通过函数计算后返回一个结果

1、count() ：计数，返回查询结果的行数（空值不参与计数）

注意：count()函数为按照指定属性进行计数，但是如果没有明确说明按照某个属性计数时，通常采用表的主键属性来计数。count(*)会计算包含空值行

例：查询3号部门的员工人数

2、sum() ：求和，按照指定属性进行求和

例：查询所有员工的工资总和

3、avg()：求平均值 ，按照指定属性求平均值

例：查询所有女员工的平均工资

4、max()：求最大值

5、min() ：求最小值

例：查询所有员工中最高工资和最低工资

6、group_concat(x separator '指定字符')：将查询结果拼接成一个值进行返回，默认逗号隔开（如果要使用其他符号分割，使用关键separator 指定分隔符），如果不指定字符，则默认以逗号拼接 。

例：查询3号部门所有员工的姓名，并返回成一个值(使用$ 符号分隔)

---

多表查询

多表连接查询（笛卡尔集）

内连接

连接两张相关联的表，只展示两张表中匹配到的数据，没匹配到的就不展示

基础语法：select 属性 from 表1 inner join 表2 on 关联属性 where子句 ；

select 属性 from 表1 ，表2 where 关联属性 and 限定条件 ；

等值连接：使用等号连接的关联属性

例：查询工作地在重庆的所有员工的姓名，编号，入职日期，部门名称

查询所有职员的工作地点

查询每个部门的部门名称，总工资 返回总工资大于30000的部门，按照工资降序排序

自然连接 NATURAL JOIN：

select 属性 from 表1 natural join 表2 where限定；

必须要满足以下两个条件：1、两张表必须有相同的属性名

2、相同属性名下的属性的数据能够实现隐式转换

不等值连接：就是在进行连接查询时，表与表之间没有相同的属性进行匹配关联(或者有相同但是不能用)，但是表之间某个或某些项直接存在着一定的关系,则可以使用不等值连接来匹配出我们要获取的数据

例：查询每个员工的工资等级

外连接

连接两张相关联的表，分为主表和副表，主表展示所有内容，副表展示匹配到内容，没有与主表匹配的以空值展示

左连接：left join 连接两张表 ，left左边为主表，右表为副表，on后写关联属性

右连接：right join 连接两张表 ,right右边为主表，左边为副表，on后写关联属性

自连接：多表连接查询的一种使用方式，而不是连接方式

将一张表通过取不同的别名，将它看做两张不同的表，然后进行连接查询。

例：查询每个员工领导的姓名

---

子查询

1、单行子查询

子查询返回的结果为一行一列

单行子查询使用单行比较运算符（=、>、>=、<、<=、!=）

例：查询所有职员的员工信息

查询工作地在重庆的所有员工信息

2、多行子查询

子查询返回的结果为多行一列

多行子查询要使用多行比较运算符（in、all、any）

例：查询工作地在成都的所有员工信息 #先看两张表要查数据的关联性，利用关联属性做子查询关键词

查询所有部门经理的工作所在地

>all:大于最大值

<all：小于最小值

>any：大于最小值

<any：小于最大值

例：查询工资大于所有职员工资的员工信息

3、多行多列子查询

子查询返回的结果为多行多列，使用多行比较运算符（in），且多行属性要用括号括起来

通常还可将多行多列子查询视为一张表，放在from后边，注意要给子查询表取别名

例：查询员工工资大于部门平均工资的员工信息

子查询注意事项：

①多条件判断时可以考虑用子查询简化sql语句

②用子查询语句时，往往先考虑输出的结果考虑问题（看是否输出结果能先查出来，或者多表连接后查出来），然后可将查询结果（单行单列数据用> < =...，多行单列数据用in any all)当作where子句中的定位值，或者多行多列数据看作一张新表起别名和其余可关联表相链接，最后通过相关属性进行条件定位，最后展示查询信息。

③注意查询出数据多行单列时对(in any all)的灵活使用

---

合并结果集

在MySQL中使用UNION或者UNION ALL语句可以将多个SELECT语句的结果集进行合并

union :合并结果集并消除重复行

union all :合并结果集不消除重复行

注意点：

1、所有select语句查询的属性的个数要一致 ；

2、所有select语句查询的属性要有相似的数据类型 ；

3、SELECT语句中使用ORDER BY、LIMIT子句时，需要用括号将每个select单独括起来；

---

sql语句执行逻辑

一条完整的sql语句书写顺序

select->from->join on->where->group by->having->order->limit

执行顺序

1、from子句识别查询表的数据；

2、join on/union用于连接多表数据；

3、where子句基于指定的条件对记录进行筛选；

4、group by 子句将数据划分成多个组别，如按性别男、女分组；

5、有聚合函数时，要使用聚集函数进行数据计算；

6、Having子句筛选满足第二条件的数据；

7、执行select语句进行字段筛选

8、对数据进行排序；

9、执行limit进行结果限定。

---

数据库对象

索引

针对创建了索引的属性列进行特定顺序的排序，类似于书的目录一样，可以加快查询速度。

优点：加快查询的效率

缺点：1、会降低表的增删改的速度。2、创建索引会占据一定的磁盘空间（在数据表中并不是索引建的越多越好）

需要注意的是：

1、创建主键或者唯一约束时，会自动创建索引。

2、表中定义多个索引时，系统只会使用一个索引。如果有主键，则优先使用主键索引。没有主键时，会选择最严格的限制来选择索引。

创建索引：

create index 索引名 on 表名（属性）;

也可以在创表时，创建索引：

create table 表名（属性 数据类型 [约束],……, index 索引名 （属性））;

还可以使用ALTER语句更新数据表时，新增索引

alter table 表名 add index 索引名 （属性 ）；

查看表中的索引：

show index from 表名 ；

删除索引：

drop index 索引名 on 表名 ；

视图

可以把视图理解为保存查询结果的虚拟表，并不真实存在，但是可以像真实表一样去使用。

创建语法：

create view 视图名 as select 语句 ；

视图的作用：

1、增强数据的安全性。只给用户展示想要展示的数据，对其他的数据可以进行隐藏。

2、提高查询的效率。将查询结果直接保存在视图中，使用视图时，不需要重新去从原始表中获取数据。

3、简化sql 。

4、可以通过视图来更新原始表中的数据，但是有限制：

如果视图中的属性来自于两个以上的数据表，则此视图不允许更新

如果视图中的属性来自于函数结果，则此视图不允许更新

如果视图定义中含有GROUP BY子句，则此视图不允许更新

如果视图定义中含有DISTINCT语句，则此视图不允许更新

一个不允许更新的视图上定义的视图也不允许更新

修改视图：

alter view 视图名 as select语句 ;

create or replace view 视图名 as select语句 ；

删除视图：

drop view 视图名 ；

查看数据库中所有的视图：

show table status where comment ='view' ；

视图的结构查看，视图中数据查看操作和操作表一样

触发器

当触发器所在的表上出现指定事件时，将调用该触发器的执行程序

触发器的优点：

自动被事件触发，不需要主动调用

能够通过触发器的使用，确保数据的完整性

创建语法：

create trigger 触发器名 触发时机 触发事件 on 表名

for each row

begin

程序体语句 ；

end；

触发时机：after 、before

触发事件：insert 、update、delete

注意事项：

1、一张表中只能建立一种类型的触发器，也就是说一张表最多只能创建6个触发器。

2、MySQL限制触发器程序体中不能使用返回结果集的语句，例如SELECT

3、触发器的程序体中不能包含对触发器所在表做的操作

4、触发器不能修改，只能删除重建

5、程序体中的语句，每句使用分号 ；结尾

在触发器里，new.属性 old.属性

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

自定义函数

在mysql中当自带函数不能完成特定业务功能时，需要自定义函数来实现

查看是否开启了函数功能：

show variables like '%func%' ； （查看变量log_bin_trust_function_creators 的值 off为关闭，on为开启）

开启函数功能：

set global log_bin_trust_function_creators =1 ；

创建语法：

create function 函数名（参数名 数据类型，……）

returns 返回值的数据类型

begin

函数程序体语句；

end ；

注意事项：

1、函数有且只有一个返回值，且定义函数时一定要指定该返回值的数据类型

2、函数可以有参数或者多个参数（用逗号隔开），也可以没有参数，但是必须写括号

3、函数不能修改，只能删除重建

4、程序体中的语句，每句使用分号 ；结尾

5、如果程序体中返回值为查询语句的结果，需要用括号将该查询语句括起来。

调用函数：

select 函数名（实参值） ；

查询数据库中所有的函数信息：

show function status ;

查看某个函数的信息：

show create function 函数名 ；

删除函数：

drop function 函数名 ；

存储过程

是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户可以通过存储过程的名称来调用并执行它

创建语法：

create procedure 存储过程名 （参数类型 参数名 数据类型 ，……）

begin

存储程序体；

end;

注意事项：

1、函数可以有参数或者多个参数（用逗号隔开），也可以没有参数，但是必须写括号。

2、参数必须指定参数类型（in 入参、out出参、inout出入参）。

3、存储过程可以没有返回值，也可以返回多个值。

4、存储过程不能修改，只能删除重建

5、程序体中的语句，每句使用分号 ；结尾

存储过程调用：

使用call 存储过程名（实参）； 如果存储过程有出参，在储存过程体中必须给出参赋值，不然没有意义，之后还需要使用 @变量名 （用户变量）来接收出参的值，然后使用select 进行调用；

有出参的情况：

先call procedure名(实参，@变量名1，@变量名2...)进行接受参数值，然后

select @变量名1，@变量名2，...；进行打印

无出参情况直接打印：

call procedure名 （实参）

查询数据库中所有的存储过程信息：

show procedure status ;

查看某个存储过程的信息：

show create procedure 存储名；

删除存储过程：

drop procedure 存储名 ；

事务处理

事务处理机制

事务是一种机制，是一种操作序列，包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行，事务是一个不可分割的工作逻辑单元

事务机制主要用于维护数据库的完整性、一致性

首先开启事务，使用命令：START TRANSACTION

执行SQL语句，执行过程中建立保存节点，使用命令：SAVEPOINT 节点名称

判断SQL语句是否执行成功

如果全部成功则提交事务，使用命令：COMMIT

如果出现异常则不提交事务，执行回滚操作，使用命令：ROLLBACK或ROLLBACK TO 保存节点

---

事务特点

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)应该具有的四个特性：

原子性（Atomicity）：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）：指在事务开始之前和事务结束以后，数据库的完整性约束和业务逻辑没有被破坏

隔离性（Isolation）：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

持久性（Durability）：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

SQL 隔离等级

1、read uncommited ：读到未提交数据（解决了更新丢失，但还是可能会出现脏读）

2、read committed：读已提交数据（解决了更新丢失和脏读问题）

3、repeatable read：可重重读（mysql 默认 解决了更新丢失、脏读、不可重复读、但是还会出现幻读）

4、serializable ：串行事物（解决了更新丢失、脏读、不可重复读、幻读(虚读)）

---

mysql锁

注意：MySQL的存储引擎是从MyISAM到InnoDB，锁从表锁到行锁。其中，InnoDB支持事务，InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。如果对非索引加行锁而进行批量修改数据脚本的时候，行锁升级为表锁，会让其他修改事务均处于等待状态。

mysql 锁机制的目的是为了解决事务的隔离性问题，目的是保证数据库数据完整和一致性。

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别。

行锁

数据库表中有一个主键索引和一个普通索引，Sql语句基于索引查询，命中两条记录。此时行锁就锁定两条记录，当其他事务访问数据库同一张表时，被锁定的记录不能被访问，只有等当前事务的锁释放之后，其他事务才可访问，但是数据其他的记录都可以访问到。

劣势：开销大；加锁慢；会出现死锁

优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

InnoDB和MyISAM的最大不同点有两个：一，InnoDB支持事务(transaction)；二，默认采用行级锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)；我们先简单了解一下事务知识。

间隙锁

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做”间隙(GAP)”。InnoDB也会对这个”间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

行锁优化

1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。

2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。

3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。

4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

---

表锁

表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。

优势：开销小；加锁快；无死锁

劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低

加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：

共享读锁：lock table tableName read;

独占写锁：lock table tableName write;

批量解锁：unlock tables;

---

控制流语句

定义变量

在mysql数据库中，可以使用关键字declare 来定义变量，该变量只作用于 begin 和end之间的程序体中；

定义语法：

declare 变量名 数据类型 [default 值] ；

变量的赋值

1、select 属性 into 变量 1 from 表名 where子句 ；

2、set 变量名=值 ；

判断语句

if判断：

IF语句主要用于条件判定，根据条件成立与否，后续执行不同的命令

语法：

if 条件1 then sql 1; 当条件1成立时，执行sql1语句

elseif 条件2 then sql2 ; 当条件2成立时，执行sql2语句 （条件1不成立）

……

else sqln ； 否则执行sqln （其他条件都不成立时）

end if ；

---

case判断：

CASE语句为多分枝语句结果，从WHEN后面查找与CASE后的变量相等的值，如果找到，执行该分支语句，否则执行ELSE语句

语法：

case 变量

when 值1 then sql1; 如果变量=值1则执行sql1

when 值2 then sql2; 如果变量=值2则执行sql1

……

else sqln; 否则执行sqln

end case;

循环语句

while循环：

判断条件成立与否，成立则执行循环体语句，不成立时则退出循环

语法：

while 条件 do （do后边没有分号 ；）

循环体语句；

end while ；

repeat循环：

无论如何都要先执行一次循环体语句，然后再条件判断，不成立则执行循环，直到条件成立时退出循环

语法：

repeat

循环体语句；

until 条件 （until后无分号）

end repeat ；

loop循环：

loop本身就是一个简单的无限循环语句；

但是当loop循环遇到 iterate 语句时 可以停止当前一轮循环，直接进入下一轮循环；

当loop循环遇到 leave 语句时，停止整个循环。

语法：

star_lable: loop

循环体语句；

end loop ；

例：

CREATE PROCEDURE pro03(in x INT)

BEGIN

lable:LOOP

SET x=x+1;

IF x = 6 THEN ITERATE lable; # ITERATE语句 不在执行后续的循环体语句，直接进入下一轮循环

ELSEIF x=10 THEN LEAVE lable; #leave语句 直接退出整个循环，后边的语句也不再执行。

END IF;

SELECT x,'OK';

END LOOP;

END;

---

语句优化的部分方法

1.使用连接（JOIN）来代替子查询

2.where子句中避免使用函数，会进行全表扫描

3.避免使用select * from ...

4.避免使用count(*)

5.避免使用 like模糊查询

6.避免where字句中使用 in 、not in、 !=、 <>， 会进行全表扫描

.......

其他

linux导入sql文件：

source + 文件绝对路径

链接mysql

mysql -h ip地址 -P 端口号 -p密码