MySQL

发表于 2018-04-20 更新于 2021-05-14 阅读次数：本文字数： 32k 阅读时长 ≈ 29 分钟

MySQL基本命令

MySQL 数据库的一个实例可以同时包含多个数据库（每条命令结束后加分号），不区分大小写

查看当前实例下包含多少个数据库：

show databases；
show CREATE DATABASE 数据库名；#查看创建库的信息

用户需要创建新的数据库：

create database [IF NOT EXISTS]数据库名；
create database 数据库名 character set GBK/UTF-8/...;#指定编码
show variables like 'character%';#查看编码

删除指定数据库：

drop database 数据库名；

进入数据库：

use 数据库名；
select database();#查看当前选择的数据库

进入数据库后，查询该数据库下包含多少个数据表：

show tables;

查看指定数据表的表结构（查看该表有多少列，每列的数据类型等信息）：

desc 表名；

MySQL语句注释：使用“#”或者“–”，多行注释使用“/* */”

MySQL数据库存储机制

MySAM：对事务支持不够好
InnoDB：系统默认机制

ENGING=MyISAM——强制使用第一种机制
ENGING=InnoDB——强制使用第二种机制

SQL语句基础

SQL 结构化查询语句

DDL语句：操作数据库对象的语句

包括创建（create）、删除（drop）、修改（alter）数据库对象
最基本的数据库对象是数据表，数据表是存储数据的逻辑单元
常见的数据库对象

对象名称	对应关键字	描述
表	table	表以行和列的形式存在：列就是字段，行就是记录
数据字典		就是系统表，存放数据库相关信息的表
约束	constraint	执行数据校验的规则，用于保证数据完整性的规则
视图	view	一个或者多个数据表数据的逻辑显示。视图并不存储数据
索引	index	用于提高查询性能，相当于书的目录
函数	function	用于完成一次特定的计算，具有一个返回值
存储过程	procedure	用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用环境
触发器	trigger	相当于一个事件监听器，当数据库发生特定事件后，触发器触发，完成相应的处理

创建表的语法

只需指定该表包含多少列，每列的数据类型信息，无需指定多少行，因为数据库的行是动态的，每条用于保存一条用户数据
语法：

create table [模式名.]表名
(
  #可以有多个列定义
  columnName datatype [default expr]，
  ...
)

每个列定义之间以英文逗号隔开，最后一个不用
列定义由列名、列类型和可选默认值组成
指定默认值用“default 值”
这种建表语句只是创建一个空表，表里没有任何数据
MySQL支持的列类型

列类型	说明
tinyint/smallint/mediumint/int(integer)/bigint	1字节/2字节/3字节/4字节/8字节整数，又可分为有无符号两种。这些整数类型区别仅仅是表数范围不同
float/double	单精度、双精度浮点数
decimal(dec)	精确小数类型，相当于float double不会产生精度丢失问题,decimal(M,D),指定数M位，其中小数点后D位
data	日期类型，不能保存时间
time	时间类型，不能保存日期
datatime	日期、时间类型
timestamp	时间戳类型
year	年类型，仅仅保存时间的年份
char	定长字符串类型
varchar	可变长度字符串类型
binary	定长二进制字符串类型，它以二进制形式保存字符串
varbinary	可变长度的二进制字符串类型，以二进制形式
tinyblob/blob/mediumblob/longblob	1字节/2字节/3字节/4字节的二进制大对象，用于存储图片、音乐等二进制数据，分别可存储：255B/64KB/16MB/4GB的大小，区分大小写
tinytext/text/mediumtext/longtext	1字节/2字节/3字节/4字节文本对象，可用于存储超长长度的字符串，分别可存储255B/64KB/16MB/4GB，不区分大小写
enmu(‘value1’,’value2’,…)	枚举类型，该列的值只能是enum中多个值的其中一个
set(‘value1’,’value2’,…)	集合类型，该列的值可以是set后括号中的其中几个

使用子查询建表语句，则可以在建表的同时插入数据

create table [模式名.]表名 [colum[,colum...]]
as subquery;

新表中的字段列表必须与子查询中字段列表数量匹配，创建新表时的字段列表可以省略（表示新表的列名与选择结果完全相同）

查看表结构的语法

describe(desc简写) 表名：显示出一个表

show create table 表名：显示更详细，包括代码

修改表结构的语法

修改表结构使用alter table，包括增加列定义、修改列定义、删除列、重命名列等操作

增加列定义

alter table 表名
add
(
   #可以有多个列定义
   column_name1 datatype [default expr] [first|after col_name],
   ...
)

如果只新增一列，可以省略圆括号，在add后紧跟一个列定义即可。

字符串值是由单引号引起的

修改列定义

alter table 表名
modify column_name datatype(修改后的类型) [default expr] [first|after col_name](指定需要将目标修改到指定位置）;

该修改语句每次只能修改一个列定义
add新增的列名是原表中不存在的，modify修改的是原表中已存在的
first 表示添加在最开始处，after xxx表示添加在xxx之后

删除列

alter table 表名
drop colum_name

重命名数据表

alter table 表名
rename to 新名

完全改变列定义 `change`

alter table 表名
change old_column new_column type [default expr][first|after col_name]

一般只有需要修改列名时才会使用change选项

删除表的语法

drop table 表名

表结构被删除，表对象不再存在；
表里所有数据也被删除；
表里所有相关索引，约束也被删除

truncate 表

作用：删除表里全部数据和结构，在重新建立一个新表（更彻底，无法找回

truncate 表名

数据库约束

约束类型：

NOT NULL：非空约束，指定某列不能为空。

UNIQUE：唯一约束，指定某列或者几列组合不能重复。

PRIMARY KEY：主键，指定该列的值可以唯一的标识该条记录。

POREING KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。

CHECK：检查，指定一个布尔表达式，用于指定对应列的值必需满足该表达式

MySQL不支持CHECK约束，可使用但没卵用

根据约束对数据列的限制，约束分为如下两类：

单列约束：每个约束只约束一列。

多列约束:每个约束可以约束多个数据列。

NOT NULL约束

它只能作为列级约束使用
非空约束不能指定名字
null值：

所有数据类型的值都可以是null；

空字符串不等于null，0也不等于null；

在数据库中null不等于null；

使用：

只要在列表定义后添加not null；

create table hehe
(
  #建立了非空约束，这意味着hehe_id不可以为null
  hehe_id int not null,
  # MySQL的非空约束不能指定名字
  hehe_name varchar(255) default 'xyz' not null,
  #下面列可以为空，默认就是可以为空
  hehe_gender varchar(2) null
);

在使用alter table 修改表时增加或者删除非空约束

#增加非空约束
alter table hehe
modify hehe_gender varchar(2) not null
#取消非空约束
modify hehe_name varchar(2) null;
#取消非空约束，并指定默认值
alter table hehe
modify hehe_name varchar(255) default 'abc' null;

UNIQUE约束

唯一约束用于保证指定列或指定列组合不允许出现重复值，但可以出现多个null
当某列创建唯一约束时，会为该列相应的创建唯一索引
如果不给唯一约束起名，该唯一约束默认与列名同名
如果需要为多列建组合约束，或者需要为唯一约束指定约束名，`只能用表级约束语法。
使用：

列级约束语法：在定义后增加unique关键字即可

#建表时创建唯一约束，使用列级约束法建立约束
create table unique_test
(
  #建立了非空约束
  test_id int not null,
  #unique是唯一约束，使用列级约束法建立唯一约束
  test_name varchar(255) unique
);a

表级约束语法：[constraint 约束名] 约束定义

即可放在create table中，也可放在alter table中使用关键字add来添加约束

create table unique_test2
(
  test_id int not null,
  test_name varchar(255),
  test_pass varchar(255),
  #使用表级约束法语句建立唯一约束
  unique (test_name),
  #使用表级约束法建立唯一约束，并指定约束名
  constraint test2_uk unique (test_pass)
  \
  #使用表级约束法建立唯一约束，指定两列组合不允许重复
  constraint test3_uk unique(test_name,test_pass)
  \
  #使用add关键字添加唯一约束
  add unique(test_name,test_pass),
);

在修改表结构时使用add关键字添加唯一约束

alter table unique_test3
add unique(test_name,test_pass);

3.在修改表时使用modify关键字，为单列采用列级约束语法来增加唯一约束

alter table unique_test3
modify test_name verchar(255) unique;

5.删除约束：drop index 约束名

alter table unique_test3
drop index text3_uk;

PRIMARY KEY约束

主键约束相当于非空约束和唯一约束
主键列的值可用于唯一的标识表中的一条记录
每一个表中最多允许有一个主键，主键是表中能唯一确定一行记录的字段或字段组合
不管用户是否为主键约束指定约束名，MySQL总是将所有的主键约束命名为PRIMARY
MySQL在主键约束所在列或列组合上建立对应的唯一索引
使用：primary key

1.列级约束法

create table primary_test
(
  #建立了主键约束
  test_id int primary key,
  test_name varchar(255)
);

2.表级约束法:[constraint 约束名] 约束定义

create table primary_test2
(
  test_id int not null,
  test_name varchar(255),
  teat_pass varchar(255),
  #指定约束名对MySQL无效
  constraint test_pk primary key (test_id)
  \
  #建立多列组合的主键约束
  primary key (test_name,test_pass)
);

alter table primary_test3
modify test_nama varchar(255) primary key;

3.删除指定表的主键约束：drop primary key

alter table primary_test3
drop primary key;
\
drop primary key (test_name,test_pass);

自增长特性：auto_increment

如果某个数据列的类型是整形，而且该列作为主键列，可指定该列具有自增长功能。
该功能通常用于设置逻辑主键列–该列的值没有任何物理意义，仅仅用于表示每行记录
一旦指定了某列具有自增长特性，则向该表插入记录时可不为该列指定值，由数据库系统自动生成

create table primary_test4
(
  #建立主键约束，使用自增长
  test_id int auto_increment primary key,
  test_name varchar(255),
  test_pass varchar(255)
);

FOREIGN KEY约束

外键约束主要用于保证一个或两个数据表之间的参考完整性，外键是构建于一个表的两个字段或者两个表的字段之间的参照关系：子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空。
当主表的记录被从表记录参照后，必须先把从表里参照记录的所有记录全部删除后，才可以删除主表的该记录或者删除主表记录时级联删除从表所有参照该记录的从表记录
从表外键参照的只能是主表主键列或者唯一键列。同一个表中可以拥有多个外键。
对于一对多的关联关系，通常在多的一端增加外键列
增加外键列的表被称为从表
使用：references

列级约束语法(MySQL建立的列级约束语法不会生效)

#为保证从表参照的主表存在，通常应该先建主表
create table teacher_table
(
  #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255)，
  primary key(teacher_id)
)；
create table student_table
(
  #为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  #指定java_teacher参照到teacher_table的teacher_id列
  java_teacher int references teacher_table(teacher_id)
);

表级约束语法：foreign key

#为保证从表参照的主表存在，通常应该先建主表
create table teacher_table1
(
  #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255)，
  primary key(teacher_id)
)；
create table student_table
(
  #为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  #指定java_teacher参照到teacher_table的teacher_id列
 foreign key (java_teacher) references teacher_table1(teacher_id)
);

使用表级约束语法可以为外键约束指定约束名，如果没有，MySQL会为该键命名为table_name_ibfk //table是从表的表名，而n从1开始的整数
如果需要建立多列组合的外键约束，必须使用表级约束法

#为保证从表参照的主表存在，通常应该先建主表
create table teacher_table3
(
  #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
  teacher_pass varchar(255),
  teacher_name varchar(255)，
  primary key(teacher_name,teacher_pass)
)；
create table student_table3
(
  #为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  java_teacher_name varchar(255),
  java_teacher_pass varchar(255),
  #指定两列的联合外键
  foreign key (java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass)
);

显示指定外键约束的名字，可使用constraint来指定名字

#为保证从表参照的主表存在，通常应该先建主表
create table teacher_table2
(
  #auto_increment:代表数据库的自动编码策略，通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255)，
  primary key(teacher_id)
)；
create table student_table2
(
  #为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  java_teacher int,
  #指定java_teacher参照到teacher_table的teacher_id列，并指定外键约束名为student_teacher_fk
 constraint student_teacher_fk foreign key (java_teacher) references teacher_table2(teacher_id)
);

删除外键约束：alter table后加 drop foreign key 约束名

#删除student_table3表上名为student_table3_ibfk_1的外键约束
alter table student_table3
drop foreign key student_tables3_ibfk_1;

增加外键约束：add foreign key

alter table student_table3
add foreign key(java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass);

自关联：参照自身

creater table foreign_test
(
  foreign_id int auto_increment primary key,
  foreign_name varchar(255),
  refer_id int,
  foreign key(refer_id) references foreign_test(foreign_id)
)

删除主表记录：

on delete cascade:删除主表记录时，把参照该主表记录的从表记录全部联级删除
on delete set null：删除主表记录时，把参照该主表记录的从表记录的外键设为null
在建立外键约束后添加

索引

索引是一个数据库对象，从属于数据表
作用：加快对表的查询
索引在数据字典中独立存在，但不能独立存在，必须属于某个表
创建索引：

自动：当在表上定义主键约束、唯一约束和外键约束时，系统会为该数据自动创建对应的索引

手动：用户可以通过create index语句来创建索引

删除索引：

自动：数据表被删除时，该表上的索引自动被删除

手动：通过drop index语句来删除指定数据表上的指定索引

一个表中可以有多个索引列，每个索引都可用于加速该列的查询速度
语法：

创建索引：

在创建表时创建索引：

creat table 表名
(
    字段1，字段2... 
    [unique]index [索引别名](列名)；//默认与列名相同
);

加unique限定索引的值必须是唯一的

在已存在的表上创建索引

create (unique)index index_name
on table_name (column[,column]...);

#提高employees基于last_name字段的查询速度
create index exm_last_name_idx
on emoployees (last_name);

#同时对多列建立索引
create index emp_last_name_idx2
on employees(first_name , last_name);

使用alter table创建索引

alter table 表名
add [unique] index [索引名] [列名]；

删除索引

drop index 索引名 on 表名

#删除了employees表上的emp_last_name_idx2索引
drop index emp_last_name_idx2
on employees

视图

视图是一个或多个数据表中数据的逻辑显示，并不能存储数据
视图只是数据表的逻辑显示，也就是一个查询结果
语法：

创建/修改视图

create or replace

#如果指定视图名不存在，则创建视图；如果指定视图名已经存在，则使用新视图替换原有视图
create or replace view 视图名
as select
subquery

create or replace view view_test[(a,b)]//创建字段别名
as
select teacher_name,teacher_pass from teacher_table;

alter view 视图名

alter view 视图名
as select 语句

使用with check option子句创建的视图不允许修改

create or replace view view_test
as
select teache_name  from teacher_table
#指定不允许修改该视图数据
with check option；

删除视图：drop view 视图名；
查看视图：

describe(desc) 视图名：查看基本信息
show table status like 视图名：查看基本属性（基本都为null）
show create view 表名：详细信息，包括建视图语句

更新视图：DML语句

DML语句：`操作数据表里的数据`

插入新数据

修改已有数据

删除不需要的数据

DML语句由insert into、update和delete from三个命令组成

insert into语句

用于向指定数据表中插入记录，每次只能插入一条

insert into table_name[(column [,column...])]
values(value [,value...]); #字符串使用单引号

表名后用括号列出所有需要插入值的列名，而values后用括号列出对应需要插入的值

如果省略表名后面的括号及括号里的列名列表，默认将为所有列插入值，则需要为每一列都指定一个值，也可以为无法确定的值的列分配null

只有在数据库中已经成功创建了数据表之后，才可以向数据表中插入记录

外键列的值必须是被参照列里已有的值，所以向从表中插入记录之前，通常应该先向主表中插入记录，否则从表记录的外键列只能为null

外键约束保证被参照的记录必须存在，但并不保证必须有被参照记录，即外键列可以为null

insert into teacher_table2(teacher_name)
values('xyz');

insert into teacher_table2
#使用null代替主键列的值
values(null,'abc');

带子查询的插入语句：可以一次插入多条记录

只要求选择出的数据和插入目的表的数据列个数相等、数据类型匹配

insert into student_table2(student_name)
#用带子查询的值来插入
select teacher_name from teacher_table2;

拓展语法：可以一次性插入多条记录，在values后使用多个括号包含多条记录，表示多条记录的多个括号之间以英文逗号（，）隔开

insert into teacher_table2
#同时插入多个值
values(null,"Yeeku"),
(null,"Sharfly");

update set语句

用于修改数据表的记录，每次可以修改多条记录，通过使用where子句限定修改哪些记录
where与if类似，没有where的句子总为true，即该表所有记录都会被修改

update table_name
set column1=value1[,column2=value2]...
[where condition];

update语句还可以一次修改多列，通过set关键字后使用column1=value1，column2=value2...来实现，修改多列的值之间用英文逗号隔开

#把teacher_table2表中所有的teacher_name列的值改为’孙悟空‘
update teacher_table2
set teacher_name='孙悟空'；

#通过where条件来指定只修改特定记录
update teacher_table2
set teacher_name='猪八戒'
where teacher_id>1;

delete from语句

用于删除指定数据表记录，使用时不需要指定列名，因为总是整行地删除
可使用where
格式语法

delete from table_name
[where condition];

#把student_table2表中的记录全部删除
delete from student_table2;

#使用where限定只删除指定记录，可以加上limit限制每次删除数量
delete from teacher_table2
where teache_id >2 limit 1;

只有先将从表中参照主表记录的所有记录全部删除后，才能删除主表记录

单表查询

select语句：用于从一个或者多个数据表中选出特定的行、列的交集
语法格式：

select coulmu1,column2...
from 数据源
[where condition]

数据源可以是表、视图等；如果没有where条件，默认选出所有行；如果想选出所有列，则可使用带星号(*)代表所有列；

select *
from teacher_table;

select语句可使用算数运算符（+、-、*、/）

不仅可以在列和常量、变量之间进行运算，也可以在两列之间进行运算。

数据列很像一个变量，但其值具有指定的范围，因此能使用变量的地方，基本上都可以使用数据列。

select teacher_id +5
from teacher_table;

#查询出teacher_table表中teacher_id*3大于4的记录
select *
from teacher_table
where teacher_id*3>4;

# 数据列实际上可当成一个变量
select 3*5，,20
from teacher_table;

MySQL使用 concat 函数来进行字符串连接运算：

-- 选择出teacher_name和'xxx'字符串连接后的结果
select concat(teacher_name,'xxx')
from teacher_table;

如果让字符串和 null 进行连接运算，将使整个算数表达式的返回值为 null

可以为数据列或者表达式起一个别名，别名紧跟数据列，中间以空格隔开，或者使用as关键字隔开

select teacher_id +5 as MY_ID
from teacher_table;

#可以用双引号实现别名中的特殊字符
select teacher_id + 5 "MY'id"
from teacher_table;

#为多列起名
select teacher_id +5 MY_id,teacher_name 老师名
from teacher_table;

#为表起别名
select teacher_id +5 
from teacher_table t;

#运算符在多列之间进行运算
select teacher_id +5 MY_ID,concat(teacher_name,teacher_id) teacher_name
from teacher_table
where teacher_id *2>3;

select 5+4
from teacher_table
where 2<9;

SQL会把常量当成一列，对于上面最后的语句，有多少条语句该常量就出现多少次

select 5+4 from dual;dual没有任何意义，仅仅相当于from后面的占位符

select 会默认把所有符合条件的记录全部选出来，即使两行记录完全一样。可以使用 distinct 关键字从查询结果中清除重复行。

select distinct student_name,java_teacher
from student_table;

distinct 紧跟 select 关键字，它的作用是去除后面字段组合的重复值，而不管对应记录在数据库里是否重复，例如：(1,’a’,’b’) 和 (2,’a’,’b’) 两条记录在数据库里是不重复的，但如果仅选择后面两列，则 distinct 会认为两条记录重复

运算符：SQL 中判断两个值是否相等的比较运算符是单等号，赋值运算符是冒号等号(:=)
特殊的比较运算符

运算符	含义
expr1 (not) between expr2 and expr3	expr1的值在两者之间
expr1 `in`(expr2,expr3,…)	expr1的值等于后面括号中的任意一个
like	查询与like后的字符串匹配的字符串
is (not) null	要求指定值等于null

select * from student_table
where student_id between 2 and 4;

#选出java_teacher<=2，student_id>=2的所有记录
select * from student_table
whrer 2 between java_teacher and student_id;

#选出student_id 为2或4的所有记录
select * from student_table
where student_id in (2,4);
/
where 2 in (student_id,java_teacher);

like 后面可以加两个通配符：下划线(_):代表一个任意字符；百分号(%):代表任意多个字符。

where student_name like '孙%'；
where student_name like '__'；//用两个下划线代表两个字符

MySQL使用\作为转义字符；标准SQL语句没有转义字符，而是使用escape关键字显示进行转义

#选出所有名字以下划线开头的学生
select * from student_table
where student_name like '\_%';

select * from student_table
where student_name like '\_%' escape '\';

where student_name is null;#选择出student_name为null的所有记录。

逻辑运算符：and、or和not

优先级：所有比较运算符>not>and>or

where student_name like '__' and(or) student_id >3;
where not student_name like '\_%';

执行查询后的查询顺序默认按照插入顺序排列，使用order by 子句，默认按升序排列；若需要强制降序排列，则需要在列名后使用desc关键字

order by column_name1 [desc] , column_name2 …

如果按多列排序，每列的asc，desc必须单独设定。如果指定了多个拍序列，只有当第一列中存在多个相同值时，第二个排序列才会起作用

#按java_teacher列降序排序，当java_teacher列的值相同时按student_name列的升序排列
select * from student_table
order by java_teacher desc， student_name;

group by分组查询

表达式：

group by 属性名 [having表达式] [with rollup]

单独使用，只会列出第一个出现的值

-- 只会筛选出第一个出现GradeName1的student的数据
select * from t_student group by gradeName;

需要注意，最新的版本的 MySQL 标准不再允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态。

所以这样的语句是不可以的:

SELECT gender, 
       last_name 
FROM   employees 
GROUP  BY gender

将 last_name 从 SELECT 中移除或将其添加到 GROUP BY 中都可以修复：

SELECT gender,
FROM   employees 
GROUP  BY gender 

SELECT gender,
last_name
FROM employees
GROUP BY gender,
last_name

但这样的修改查询出来就可能就不是想要的结果了。

针对以上情况，可以有三种方式来解决：

关闭 ONLY_FULL_GROUP_BY

可以选择关掉 MySQL 的 ONLY_FULL_GROUP_BY 模式，有两种方式，通过设置 sql_mode 来关闭。

首先查看变更前的 sql_mode：

mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过以下脚本关闭：

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

再次查询 @@sql_mode 返回中应该已经没有该模式了。

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

第二种是找到 MySQL 配置文件修改并保存。

MySQL 的配置文件名为 my.cnf，可通过以下命令查看你位置：

$ mysql --help | grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

找到后编辑并保存，重启 MySQL 后生效。

[mysqld]
-sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
+sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如果文件中没有 sql_mode 配置项可手动添加上。

因为 ONLY_FULL_GROUP_BY 更加符合 SQL 标准，所以不建议关掉。

ANY_VALUE()

还可以通过 ANY_VALUE() 来改造查询语句以避免报错。

使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。所以这样是可以的：

SELECT gender, 
-       last_name
+       ANY_VALUE(last_name) 
FROM   employees 
GROUP  BY gender

添加列间的依赖

像这个示例中:

SELECT gender, 
       last_name 
FROM   employees 
GROUP  BY gender

假如让 gender 变成不重复的主键，last_name 便与 gender 产生了一种关系，即 gender 可唯一确定 last_name。此时便可进行 GROUP BY 了。因为，之所以报错是因为在进行聚合的时候有不能确定的列参与了进来。这也是正确的使用场景。

与 group concat() 函数一起使用：会把所有值列出来

-- 会把所有属于gradeName的studentName全部组合在一起列出来
select gradeName,group concat(studentName) from t_student  group by gradeName;

与聚合函数一起使用
与 having 一起使用，限制输出结果

-- 只会输出学生名字多于三个的年级及学生名字
select gradeName,group concat(studentName) from t_student  group by gradeName having count(studentName) >3;

与 with rollup 一起使用，在最后一行增加一个叠加总和

limit 分页查询

select 字段1，字段2 ...from 表名 limit 初始位置，记录数

第一条记录从 0 开始：

-- 输出从第一条开始的五条记录；
select * from t_student limit 0,5;

数据库函数

可分为单行函数和多行函数

单行函数：对单行输入单独计算，每行得到一个计算结果返回给用户。其参数可以是变量、常量或数据列；可以接收多个参数，但只返回一个值；单行函数可以改变参数的数据类型

多行函数：多行输入整体处理，只返回一个值

MySQL单行函数的用法：

#选出teacher_table表中teacher_name列的字符长度
select char_length(teacher_name)
from teacher_table;

#求绝对值
select abs(x);

#求平方根
sqrt(x);

#求余
mod(x,y);

#计算teacher_name列字符长度的sin值
select sin(char_length(teacher_name))
from teacher_table;

#计算1.57的sin值
select sin(1.57);

#为指定日期添加一定时间
#在这种语法下interval是关键字，需要一个数值，还需要一个单位
SELECT DATA_ADD('2018-04-01',interval 2 MOMTH)；
#简单用法
select ADDDATE('2018-04-01',3);

# 获取当前日期
select CURDATE();

#获取当前时间
select curtime();

#返回日期d中的月份
select month(d);

连接函数

CONCAT(a,b):用于把两个字符串连接起来

SELECT CONCAT(firstname,lastname) from Table;

CONCAT_WS(‘(任意的分隔符)’,firstname,lastname):在两者之间显示分隔符

截断和填充函数

RTRIM/LTRIM:从一个字符串的右端或者左端删除空格
TRIM:删除想要剔除的字符，使用LEADING TAILING分别表示剔除开头的或末尾的，若没有则默认两边都剔除

SELECT TRIM(LEADING 'X' FROM 'XXXneedleXXX');//显示needleXXX
SELECT TRIM(TAILING 'X' FROM 'XXXneedleXXX');//显示XXXneedle
SELECT TRIM('X' FROM 'XXXneedleXXX');//显示needle

RPAD()/LPAD():向一个字符串中添加字符

SELECT RPAD('needle',10,'X');//添加直到字符串达到10个字符的长度

定位和位置函数

LICATE('A','B'):返回A中某个字符串在B中第一次出现的位置（从1开始，不从0开始）

子字符串函数

SUBSTRING("字符串",2,3)：获取第2个开始的三个字符
LEFT/RIGHT('MySQL',3):获取字符串左边或右边的三个字符

字符串修改函数

LCASE/UCASE():把字符串转化为小写或者大写，参数为列名便是这个列的所有值转化为小写/大写
REPEAT("A","B","C"):把A字符串中的B全部转化为C

加密函数

password(str):一般对用户的密码加密，不可解密

insert into 表名 values(值1，值2，...,password('字符'),...)；

sha()：用于对密码加密，不可解密，会把一段文字加密为唯一的40字符编码

insert into 表名 values (值1，值2，...,sha('字符'),...);

md5(str):普通加密，不可解密

insert into 表名  values (值1，值2，...,password('字符'),...)；

encode(str,pswd_str):加密函数，结果是一个二进制数，必须使用blob类型的字段来保存它，可解密

insert into 表名  values (值1，值2，...,encode('字符','xx'),...)；//'xx'为某个解密钥匙标记

decode(crypt_str,pswd_str):解密函数

select decode("被加密数据"，"与被加密参数对应的钥匙标记") from 表名；

null 处理函数

MySQL提供如下几个处理null的函数

使用：select 函数名 from 表名

ifnull(expr1,expr2):如果expr1为null，则返回expr2，否则返回expr1

nullif(expr1,expr2):如果expr1和expr2相等，则返回null，否则返回expr1

if(expr1,expr2,expr3):如果expr1为true，不等于0，且不等于null，则返回expr2，否则返会expr3

isnull(expr1):判断expr1是否为null，如果是则返回true，否则返回false

case函数：流程控制函数

select 列名 ，case 用法 from 表名；

用法1：用 value 依次与后面的 compare_value1 等相比较，如果 value 与指定的 compare_value 相等，则返回对应的 result，否则返回 else 的result

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

用法2：condition 内容是一个返回 boolean 值的条件表达式

case
when condition1 then result1
when condition2 then result2
...
else result
end

间隔分析函数

MySQL提供了三个函数用于排序时显示当前行的序号，分别为 RANK()，DENSE_RANK()和 ROW_NUMBER，其中 RANK() 在遇到重复时，显示并列排名的同时对于下一个序号会递增，而 DENSE_RANK 不会递增。

这几个函数需要结合 over 函数进行使用，over 函数的语法如下：

over（partition by columnname1 order by columnname2）

columnname1 指定分组排序的字段，columnname2 指定排序字段。

例如：在 employees 表中，有两个部门的记录：department_id ＝10 和 20，以下 sql 语句：

select department_id，rank（） over（partition by department_id order by salary) from employees

就是指在部门 10 中进行薪水的排名，在部门 20 中进行薪水排名。如果是 partition by org_id，则是在整个公司内进行排名。

函数使用的具体示例如下：

mysql> SELECT
         val,
         ROW_NUMBER() OVER w AS 'row_number',
         RANK()       OVER w AS 'rank',
         DENSE_RANK() OVER w AS 'dense_rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+

分组和组函数

组函数就是多行函数，将一组记录作为整体计算，每组记录返回一个结果
常用组函数：

avg([distinct|all]expr): 计算多行 expr 的平均值，expr 可以是变量、常量或者数据列，但其数据类型必须是数值型，如果使用distinct 表示不计算重复值，all 用与不用一样
count({*|[distinct|all]expr}): 计算多行 expr 的总条数，expr 的数据类型可以是任意类型；用（*）表示统计该表内记录的行数
max(expr)：计算多行 expr 的最大值，其中 expr 其数据类型可以是任意类型
min(expr)：计算多行 expr 的最小值
sum([distinct|all]expr)：计算多行 expr 的总和，expr 的数据类型必须是数值型

#计算的结果是20*记录的行数
select sum(20)
from student_table;

#因为expr的值是34，所以每行的值都相同
#使用distinct强制不计算重复值，所以下面计算结果为34
select sum(distinct 34)
from student_table;

#使用count统计记录行数时，null不会被计算在内
select count(student_name)
from student_table;

#对于可能出现null的列，可以用ifnull处理
select count (ifnull(student_name,0))
from student_table;

distinct 和 * 不能同时使用
组函数会把所有记录当成一组，为了对记录进行显示分组，可以在 select 语句后使用group by 子句，group by 子句后通常跟一个或多个列名，表名查询结果根据一列或多列进行分组，当一列或多列组合的值完全相同时，系统会把这些记录当成一组

#count(*)将会对每组得到一个结果
select count(*)
from student_table
#将java_teacher列值相同的记录当成一组
group by java_teacher;

如果对多列进行分组，则要求多列的值完全相同时才会被当成一组

select count(*)
from student_table
#当java_teacher、student_name两列的值完全相同时才会被当成一组
group by java_teacher , student_name;

对于很多数据库而言，分组计算时，如果查询列表中使用了组函数，或者select语句中使用了 group by 分组子句，则要求出现在 select 列表中的字段，要么使用组函数包起来，要么必须出现在 group by 子句中。
对于MySQL：如果某个数据列既没有出现在group by之后，也没有使用组函数包起来，则MySQL会输出该列的第一条记录的值。
如果需要对分组进行过滤，则应该使用 having 子句，子句后面有一个条件表达式，只有满足该表达式的分组才会被选出来
where 和 having 的区别：

不能在 where 子句中过滤组，where 子句仅用于过滤行，过滤组必须使用 having 子句
不能在 where 子句中使用组函数，having 子句才可以使用组函数

select *
from student_table
group by java_teacher
#对组进行过滤
having count(*)>2;

多表连接查询

需要选择的数据并不是来自一个表，而是来自多个数据表，这就需要使用多表查询
两种查询规范：

SQL 92规范：

等值查询

非等值查询

外连接

广义笛卡尔积

语法：

select column1, column2 ...
from table1,table2 ...
[where join_condition]

多表连接中可能出现两个或多个数据列同名的列名，则需要在这些同名列之前使用表名前缀
等值查询:where中要求两列值相等

select s.*,teacher_name
from student_name s,teacher_table t
where s.java_teacher = t.teacher_id;

非等值查询：where中要求两列值不相等，大于或小于
广义笛卡尔积：没有where子句，结果会有n x m条记录
如果要将记录进行过滤，则将过滤条件和连接条件使用and连接起来

select s.* ,teacher_name
# 指定多个数据表，并指定表别名
from student_table s，teacher_table t
# 使用where指定连接条件，并指定student_name 列不能为null
where s.java_teacher=t.teacher_id and student_name is not null;

MySQL 不支持左外连接和右外连接
自连接：对于自关联的数据表查询

# 定义一个自关联的数据表
create table emp_table
(
   emp_id int auto_increment primary key,
   emp_name varchar(255),
   namage_id int,
   foreign key(namage_id) reference emp_table(emp_id)
);
#向表中插入数据
insert into emp_table
value (null,'唐僧',null),
(null,'孙悟空'，null)，
(null,'猪八戒'，null)，
(null,'沙僧'，null);

#自定义查询
select emp.emp_id,emp.emp_name 员工名，mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manage_id=mgr.emp_id;

2.SQL 99的连接查询：多表连接查询的from后面只有一个表名

交叉连接（cross join）：效果就是SQL92中的广义笛卡儿积，无需任何连接条件

select s.*,teacher_name
#SQL 99多表连接查询的from后只有一个表名
from student_table s
#cross join 交叉连接，相当于广义笛卡儿积
cross join teacher_table t；

自然连接(natual join):自然连接表面上看起来无需指定连接条件，但自然连接是有连接条件的，以两个表中的同名列作为连接条件，如果两个表中没有同名列，则自然连接与交叉连接效果完全一样

select s.*,teacher_name
#SQL 99多连接查询的from后只有一个表名
from student_table s
natual join teacher_table t;

using子句连接：using子句可以指定一列或者多列，用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列，如果使用natual join，则会把所有同名列当成连接条件；如果使用using子句，就可显式指定使用哪些同名列作为连接条件

select s.*,teacher_name
from student_table s
#join 连接另一个表
join teacher_tale t
using(某一同名列)；

on 子句连接：每个on子句只指定一个连接条件

select s.*,teacher_name
from student_table s
#join连接另一个表
join teacher_table t
#使用on指定连接条件
on s.java_teacher = t.teacher_id;//也可以是非等值连接

左、右、全外连接：分别使用left [outer] join、right [outer] join和full [outer] join

select s.*,teacher_name
from student_table s
right/left/full join teacher_table t
on s.java_teacher >t.teacher_id;

左连接：会把左边表中所有满足连接条件的记录全部列出

右连接：会把右边表中所有满足连接条件的记录全部列出,左边没有的为null

全外连接：会把两个表中所有满足连接条件的记录全部列出

内连接：inner join，可以返回结合两个表的数据

select mismatch_topic.topic_id,mismatch_category.name
    from mismatch_topic
    inner join mismatch_category
    on (mismatch_topic.category_id = mismatch_category.category_id)
    where mismatch_topic.name = 'Horror movies;

如果要查询内容位于不同的表而列名相同，可以使用 using 代替 on

select mismatch_topic.topic_id,mismatch_category.name
    from mismatch_topic
    inner join mismatch_category
    using (category_id)
    where mismatch_topic.name = 'Horror movies;

子查询：在查询语句中嵌套另一个查询

子查询可以出现在两个位置：

出现在from语句后当成数据表，这种用法被称为行内视图，相当于一个临时视图

select *
from (select * from student_table) t # 必须给重命名
where t.java_teacher >1;

出现在where条件后作为过滤条件的值

select *
from student_table
where java_teacher >
#返回单行、单列的子查询可以被标量值使用
(select teacher_id 
from teacher_table
  where teacher_name='Yeeku');

注意点：

子查询要用括号括起来
把子查询当成数据表时（出现在from之后），可以为该子查询起别名，尤其是作为前缀来限定数据表时，必须给子查询起别名
把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符

如果子查询返回多个值，则需要使用in、any、all等关键字

(not)in:把子查询返回的多个值当成一个值列表

select *
from student_table
where student_id in
(select teacher_id
from teacher_table);

any和all可以与>、>=、<、<=、<>、=等运算符结合使用；

与any结合使用表示等于、大于等于、小于、小于等于、不等于、等于其中任意一个值；

与all结合使用分别表示等于、大于等于、小于、小于等于、不等于、等于全部值；

=any的作用与in作用相同

select *
from student_table
where student_id =
any (select_teacher_id
from teacher_table);

“<any” 只要小于值列表中的最大值即可，”>any”只要大于值列表中的最小值即可，”<all” 要求小于值列表中的最小值，”>all”要求大于值列表中的最大值。

select *
from student_table
where student_id >
all(select teacher_id
from teacher_table);

子查询可以返回多行、多列，此时where子句应该有对应的数据列，并使用圆括号将多个数据列组合起来

select *
from student_table
where (student_id,student_name)
=any(select teacher_id,teacher_name
from teacher_table);

带(not) exists关键字的子查询:若子查询内容不为空，则查询外层表，否则返回空表

select 字段…from 表1 where exists (select 字段…from 表2)；

集合运算

select语句查询的结果是一个包含多条数据的结果集，可以进行交（intersect）、并（union）和差（minus）运算

对两个结果集进行集合运算，这两个结果集必须满足如下条件：

对两个结果集所包含的数据列的数量必须相等

两个结果集所包含的数据列的数据类型也必须一一对应

union运算

select 语句 union select 语句

select * from teacher_table
union
#这个结果集的数据列必须与前一个结果集的数据列一一对应
select student_id,student_name from student_table;

union会自动把重复的内容去掉，若要不去掉重复内容，可使用 union all

2.minus运算

select 语句 minus select 语句

MySQL并不支持minus运算符，相同运算：

select student_id,student_name from student_table
where (student_id,student_name)
not in 
(select teacher_id,teacher_name from teacher_table);

3.intersect运算

select 语句 intersect select 语句

MySQL并不支持intersect语句，相同运算：

select student_id ,student_name from student_table
join 
teacher_table
on (student_id =teacher_id and student_name=teacher_name);

select student_id,student_name from student_table
join 
teacher_table
on (student_id=teacher_id and student_name=teacher_name)
where student_id<4 and teacher_name like '李%';

触发器

触发器（trigger）由事件触发某个操作，这些事件包括insert into、updata、delete from语句
创建触发器

创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row  执行语句

create trigger trigger_name after insert
on t_book for each row 
update t_booktype set bookNum=bookMun+1 where new.bookTypeId = t_booktype.id;

过渡变量：

new :只能出现在insert和update出现时，在insert时表示新插入的行数据，在update表示要替换的新数据

old:只能出现在delete和update出现时，在delete时表示被删除的数据，在update表示被更改的原来的数据行

before 和after 的区别：

before:在记录操纵之前触发，是先完成触发，在增删改，触发的语句先于监视的增删改；（insert、update）可以对new进行修改

after:在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作；不能对new进行修改

两者都不能对old进行修改

创建有多个执行语句的触发器

delimiter |
create trigger 触发器名 before|after 触发事件
  on 表名 for each row
  begin
       执行语句列表;//每句后面用分号隔开
   end
| delimiter ;

delimiter 重新定义结束符，保证完成全部操作，然后把结束符定义回分号

使用delimiter把触发器包括起来，因为MySQL执行语句后面都要加分号

delimiter |
  create trigger trig_name after delete
      on t_book for each row
      begin
        update t_bookType set bookName=bookName-1 where old.bookTypeId=t_booktype.id;
        insert into t_log values (null,now(),'在book表里删除了一条数据');
        delete from t_test where old.bookTypeId=t_test.id;
      end
| delimeter ;

查看触发器

show triggers：查看所有触发器

删除触发器

drop trigger 触发器名；

存储过程和函数

存储过程和函数是在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。
创建存储过程:create procedure

create procedure 名称([proc_parameter[,...])
     [characteristic...]routine_body

proc_premeter 表示存储过程的参数列表；每个参数由3部分组成，分别为输出输入类型、参数名称、参数类型

[in|out|inout]param_name type

其中in表示输入参数；out表示输出参数；inout表示既可以输入，也可以输出；

characteristic 参数有多个取值：

languange sql:说明routine_body部分由sql语句组成，这也是系统默认语言
[not] deterministic: 指明存储过程的执行结果是否是确定的。deterministic表示结果是确定的，每次执行存储过程，相同输入会得到相同的输出；not deterministic 表示结果是非确定的，相同的输入可能得到不同的输出，默认情况下都是非确定的
{contains SQL|NO SQL|reads sql data|modifies SQL data}：指明子程序使用sql语句的限制，默认使用contains SQL

contain sql:表示子程序包含SQL语句，但不包含读或写数据的语句

no sql：表示子程序中不包含SQL语句

read SQL data：表示子程序中包含读数据的语句

modifies sql data：表示子程序中包含写数据的语句

delimiter &&
create procedure 存储过程名( in 变量 int,out count_num int)
   read sql data
   begin
      select count(*) from 表名 where bookTypeId=变量；
    end 
    &&
delimiter ；

创建存储函数

create function 函数名([func_parameter[,..]])
    return type
    [characteristic..] routine_body

func_parameter 表示存储函数的参数列表；可以由多个参数组成，每个参数由参数名称和参数类型组成：

参数名称参数类型

return type指定返回值类型

characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的

routine_body参数是SQL代码的内容，可以使用begin…end来标志SQL代码的开始和结束

delimiter &&
create function func_book( 变量)
  return varchar(20)
  begin 
    return (select bookName from t_book where id=bookId);
  end
  &&
delimiter ;

调用存储过程和函数

1.调用存储过程：

call 存储过程名(参数)

调用存储函数：

fun_name(参数)

查看存储过程和函数

show status 语句查看存储过程和函数的状态( 只能查看属性状态)

show procedure|function status [like 存储过程名|函数名]
show create语句查看存储过程的函数的定义(能够查到定义)

show create procedure|function 存储过程名或函数名

修改存储过程和函数

alter prcedure|function 存储过程|函数名(变量)
characteristic:
 ( [contains SQL|no SQL|read SQL data|modifies SQL data|SQL security(definer|invoder])
 comment 'string'(修改内容)

sql security指明谁有权限来执行，definer表示只有定义者自己才能够执行，invoder表示调用者也可以执行，comment’string’是注释信息

删除存储过程和函数

drop procedure|function 存储名或者函数名；
变量的使用

定义变量

declare var_name[,…] type [default value]

2.为变量赋值

set var_name = expr[,var_name[,…]]

select col_name[,..] into var_name[,...]
          from table name where condition

#定义变量与使用第一种赋值方法
delimiter &&
create procedure pro_user()
      begin
         declare a,b varchar(20);
         set a='XXXX',b='xxxx';
         insert into t_user values (NULL,a,b)
       end
&&
deliiter ;

#第二种赋值
delimiter &&
create procedure pro_user()
      begin
         declare a,b varchar(20);
         select userName，password into a,b from t_user2 where id=2;
         insert into t_user values (NULL,a,b)
       end
&&
deliiter ;

游标的使用

查询语句可能查出多条记录，在存储过程和函数中必须使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

声明游标

delare cursor_name cursor for select_statement(对某个查询语句的游标）；
打开游标

open cursor_name;
使用游标

fetch cursor_name into var_name[,var_name…]
关闭游标

close cuesor_name;

delimiter &&
create procedure pro_user()
      begin
         declare a,b varchar(20);
         declare 游标名 cursor for select userName,password from t_user;
         open 游标名；
         fetch 游标名 into a,b;
         insert into t_user values (NULL,a,b);
         close 游标名；
       end
&&
deliiter ;

流程控制的使用

流程控制语句包括if、case、loop、leave、iterate、repeat和while语句

if语句

if search_condition then statement_list
   [elseif search_condition then statement_list]...
   [else statement_list]
end if;

delimiter &&
create procedure pro_user(in bookId int)
      begin
         select count(*) into @num from t_user whrer id=bookId;
         if @num>0 then updata t_user set userName='xxxx' where id=bookId;
         else
            insert into t_user values (NULL,'xxxx','xxxxx');
        end if;
       end
&&
deliiter ;

case 语句

case case_value
   when when_value then statement_list
   [when when_value then statement_list]...
   [else ststement_list]
end case;

delimiter &&
create procedure pro_user(in bookId int)
      begin
         select count(*) into @num from t_user whrer id=bookId;
         case @num
           when 1 then update t_user set user_name='xxx' where id=bookId;
           when 2 then insert into t_user values (NULL,'xxx','xxxx');
           else insert into t_user values(NULL,'xxxx','xxxx');
         end case;
       end
&&
deliiter ;

loop,leave语句

loop 语句可以使某些特定的语句重复执行，实现一个简单的循环，但是loop语句本身没有停止循环的语句，必须遇到leave语句等才能停止循环

[begin_label:]loop
       statement_list
end loop [end_label]

leave语句主要用于跳出循环：leave label

delimiter &&
create procedure pro_user(in bookId int)
      begin
         aaa: loop
           set totalNum=totalNum-1;
           if totalNum=0 then leave aaa;
           else insert into t_user values(totalNum,'xxxx','xxxxx');
           end if;
         end loop aaa;
       end
&&
deliiter ;

iterate语句

iterate 语句也是用来跳出循环的，但是iterate语句跳出本次循环，然后进行下次循环:iterate label

delimiter &&
create procedure pro_user(in bookId int)
      begin
         aaa: loop
           set totalNum=totalNum-1;
           if totalNum=0 then leave aaa;
           elseif totalNum=3 then iterate aaa;
           end if;
           insert into t_uesr values(totalNum,'xxx','xxxxx');
         end loop aaa;
       end
&&
deliiter ;

repeat语句

repeat语句是有条件的循环语句，当满足特定条件时，就会跳出循环语句

[begin_label:]repeat
       statement_list
       until search_condition
end repeat[end_list]

delimiter &&
create procedure pro_user(in bookId int)
      begin
        repeat 
            set totalNum=totalNum-1;
            insert into t_user values (totalNum,'xxxx','xxxxx');
            until totalNum=1;
        end repeat;
       end
&&
deliiter ;

6.while语句

[begin_label:]while search_condition do(别忘了)
     statement_list
end while[end_label]

delimiter &&
create procedure pro_user(in bookId int)
      begin
        while totalNum >0 do
            insert into t_user values (totalNum,'xxxx','xxxxx');
            set totalNum=totalNum-1；
        end while;
       end
&&
deliiter ;

事务

事务表示进行一系列操作，在失败的情况下能够退回原来的状态，成功时则继续往下进行

使用BEGIN表示开始一个事务
COMMIT:出现在事务中的最后，只有在所有的查询成功地执行之后才执行此命令
ROLLLBACK:当事务中的系列查询的一个或者多个失败时将使用这个命令，并且把相关的表恢复到事务之前的状态（在每个执行语句后面都加）

BEGIN;
数据库操作语句;
ROLLBACK;
...

COMMIT;

MySQL数据库导出和导入

导出：

mysqldump -u root -p 数据库名> 导出的文件名及路径

导入：（先创建数据库）

mysql>source 文件

MySQL创建用户和授权

创建：

CREATE USER '用户名' IDENTIFIED BY '密码';

授权：

GRANT ALL/SELECT/UPDATE/DELETE/UPDATE ON 数据库名.指定表（或*） TO '用户名';

撤销权限：

REVOKE ALL/... ON 数据库名 FROM '用户名'@'localhost';

删除用户：

DROP USER '用户名';