mysql_mysql创建表sql命令

基础MYSQL学习

点击进入学习视频

DDL

库操作

• 查询所有数据库—show databases;
• 查询当前数据库—select database();
• 创建—create database[if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
• 删除—drop database [if not exists] 数据库名
• 使用—use 数据库名

表操作

• 创建—create table 表名(

  字段1 字段1类型 [comment] 注释,

  …

  )[comment 表注释];

• 查询—查询当前数据库所有表—show tables;

​ 查询表结构—desc 表名;

​ 查询指定表的建表语句—show create table 表名;

• 修改

  —添加字段—alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

​ —修改数据类型—alter table 表名 modify 字段名 新数据类型(长度);

​ —修改字段名和字段类型—alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

​ —删除字段—alter table 表名 drop 字段名;

​ —修改表名—alter table 表名 rename to 新表名;

• 删除

  —删除表—drop table [if exits] 表名;

​ —删除指定表，并重新创建该表，表中数据全被删除，表结构还在—truncate table 表名;

DML

添加数据

• insert into 表名 (字段1，字段2，…) values (值1，值2，…) —给指定字段添加数据

  insert into employee(id,name) values(1,'张三');
  1

• insert into 表名 values (值1，值2，…) —给全部字段添加数据

• insert into 表名 (字段1，字段2，…) values (值1，值2，…),(值1，值2，…);

  insert into 表名 values (值1，值2，…),(值1，值2，…),(值1，值2，…); —批量添加数据

  • 注意：
  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
  • 字符串和日期型数据应该包含在引号中
  • 插入的数据大小，应该在字段的规定范围内

修改数据

• update 表名 set 字段1=值1,字段2=值2,…[where 一条件]

• 注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的数据。

  update employee set name ='wzhvv',gender='男' where id=1;
  1

删除数据

• delete from 表名 [where 条件]
• 注意：delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
• delete语句不能删除某一个字段的值(可以使用update)

DQL

基本查询

• 查询多个字段

  —select 字段1，字段2…from 表名;

  —select * from 表名;

• 设置别名

​ —select 字段1[as 别名1]，字段2[as 别名2]…from 表名;

• 去除重复记录

  —select distinct 字段列表 from 表名;

• 条件查询

​ —select 字段列表 from 表名 where 条件列表;

<>  或  !=     //不等于
between and    //在某个范围之内（含最小，最大值）
in(...)        //在in之后的列表中的值，多选一，满足其一即可
例：select name from emp where id in(id=1,id=2);
like '占位符'     //模糊匹配（'-'匹配单个字符，'%'匹配任意个字符）
isnull         //是null
1
2
3
4
5
6

• 聚合函数

​ —select 聚合函数（字段列表）from 表名; //null值不参与所有聚合函数运算

count      //统计数量
avg        //平均值
....
select avg(e.salary) from emp.e,dept d where e.dept_id=d.id and d.name='研发部';
1
2
3
4

• 分组查询

​ —select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where和having区别
    执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。
    判断条件不同：where不能对聚合函数进行判断，而having可以。
    注意：
       执行顺序：where>聚合函数>having
       分组之后：查询的字段一般为聚合函数和分组字段，查询其他字段无意义。
    select workaddress,count(*) from emp where age<=45 group by workaddress having count(*)>=3;
    注意：使用聚合函数时，也使用了其他数据，其他数据要放在group by后面
1
2
3
4
5
6
7
8

• 排序查询

​ —select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

asc    //升序（默认值）
desc   //降序
    注意：
        如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
        select name,age from emp where age <=35 order by age asc ,entrydate desc;
1
2
3
4
5

• 分页查询

​ —select 字段列表 from 表名 limit 起始索引，查询记录数;

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
    select * from emp where gender='男' and between 20 and 40 order by age,entrydate desc limit 5;
1
2

DCL

管理用户

• 查询用户

use mysql;
select * from user;
1
2

• 创建用户

create user '用户名' @ '主机名' identified by '密码';   //主机名可以用%通配->任意主机都可访问
1

• 修改用户密码

alter user '用户名' @ '主机名' identified with mysql_native_password by '新密码';
1

• 删除用户

drop user '用户名' @ '主机名';
1

查询权限

• 权限表

  

• 查询权限

show grants for '用户名' @ '主机名';
1

• 授予权限

grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';
1

• 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
1

• 注意：多个权限之间，使用逗号隔开
• 授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

select 函数(参数)
1

字符串函数

• concat(s1,s2…sn) //字符串拼接，s1,s2…sn拼接成一个字符串

  select concat('hello','mysql');    //hellomysql
  1

• lower(str) //将字符串str全部转为小写

• upper(str) //将字符串str全部转为大写

• lpad(str,n,pad) //左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

  select lpad('01',5,'-');      //---01
  update emp set workno=lapd(workno,5,'0');
  1
  2

• rpad(str,n,pad) //右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

• trim(str) //去掉字符串头部和尾部的空格

  select trim('  hello  mysqkl  ');       //hello mysql
  1

• substring(str,start,len) //返回从字符串str从start位置起的len个长度的字符串，索引从1开始

  select substring('hello mysql',1,7);     //hello m
  1

数值函数

• ceil(x) //向上取整

• floor(x) //向下取整

• mod(x,y) //返回x/y的模，即x%y的值

• rand() //返回0-1的随机数

• round(x,y) //求参数x的四舍五入的值，保留y位小数

  select lpad(round(rand()*1000000,0),6,'0');
  1

日期函数

• curdate() //返回当前日期

• curtime() //返回当前时间

• now() //返回当前日期和时间

• YEAR(date) //获取指定date的年份

• MONTH(date) //获取指定date的月份

• DAY(date) //获取指定date的日期

• date_add(date,INTERVAL expr type) //返回一个日期/时间值加上一个时间间隔expr和type类型后的时间值

  select date_add(now(),INTERVAL 70 DAY);
  1

• datediff(date1,date2) //返回起始时间date1 和date2之间的天数，结果为date1-date2；

  select datediff('2021-09-01','2018-09-01');
  select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
  1
  2

流程函数

• if(value,t,f) //如果value为true，返回t，否则返回f
• ifnull(value1,value2) //如果value1不为null，返回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默认值

select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
1
2
3
4

约束

概述

• 约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的

• 保证数据库中数据的正确，有效性和完整性。

分类

非空约束 --- 限制该字段的数据不能为null --- not null
唯一约束 --- 保证该字段的所有数据都是唯一，不重复的 --- unique
主键约束 --- 主键是一行数据的唯一标识，要求非空且唯一 --- primary key
默认约束 --- 保存数据时，如果未指定该字段的值，则采用默认值 --- default
检查约束 --- 保证字段值满足某一个条件 --- check
外键约束 --- 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 --- foreign key
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
1
2
3
4
5
6
7

外键约束

• 添加外键

create table 表名{
字段名 数据类型,
....
[constraint(约束)] [外键名称] foreign key (外键字段名) references(引用) 主表(主表列名)
};     //创建表时添加
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);   //表结构创好时添加
例：alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
1
2
3
4
5
6
7

• 删除外键

alter table 表名 drop foreign key 外键名称;
1

• 删除/更新行为

not action --- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(同restrich)
restrich --- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(同not action)
cascade --- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
set null --- 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取null)
set default --- 父表有变更时，子表将外键列设置成一个默认的值(lnnodb不支持)
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade(可变) on delete cascade(可变);
1
2
3
4
5
6

多表查询

多表关系

一对一 --- 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的
一对多 --- 在多的一方建立外键，指向一的一方的主键
多对多 --- 建立第三张中间表，中间表至少包含两个外键，分别关联两方外键
1
2
3

多表查询

• 内连接

隐式内连接：select 字段列表 from 表1,表2 where 条件...;
显式内链接：select 字段列表 from 表1 [inner](可删) join 表2 on 连接条件...;
隐式内连接 语法更简单，显示内连接 效率更高，两个在查询结果上来说是相同的
例：select e.name,e.age,e.job,d,name from emp e inner join dept d on e.dept_id=d.id where e.age<30;
1
2
3
4

• 外连接

左外连接：select 字段列表 from 表1 left [outer](可删) join 表2 on 条件...;
右外连接：select 字段列表 from 表1 right [outer](可删) join 表2 on 条件...;
1
2

• 自连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
自连接查询，可以是内连接查询，也可以是外连接查询。
注意：必须使用表别名
1
2
3

• 联合查询

select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
对于联合查询的多张表的列数必须保持一致，字段类型也必须保持一致
union all会将全部的数据直接合并到一起，union会对合并后的数据去重
1
2
3
4
5

• 子查询/嵌套查询

  根据子查询位置，分为：where之后，from之后，select之后

  根据子查询结果不同，分为：

1. 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用操作符：in , not in , any , some , all
in --- 在指定的集合范围内，多选一
not in --- 不在指定的集合范围内
all --- 子查询返回列表中，在任意一个满足即可
some --- 与any等同，使用some的地方都可以用any
all --- 子查询返回列表的所有值都必须满足
1
2
3
4
5
6
7

2. 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
常用的操作符：= , <> , in , not in
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');
1
2
3

3. 表字查询

子查询返回结果是多行多列，这种子查询称为表子查询
常用的操作符：in
select * from emp where (salary,job) in(select salary,job from emp where name='鹿杖客' or name='宋远桥');
select e.*,d.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept d on e.dept_id=d.id;
1
2
3
4

事务

事务操作

• 查看/设置事务提交方式

select @@autocommit;
set @@sutocommit=0;
1
2

• 提交事务

commit;
1

• 回滚事务

rollback;
1

• 开启事务

start transaction 或 begin
1

提交事务和回滚事务同上

事务四大特性

• 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
• 隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 脏读：一个事务读到另外一个事务还没有提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

事务隔离级别

存储引擎

存储引擎简介

• 在创表时，指定存储引擎

create table 表名{
....
}engine=innodb;
1
2
3

• 查看当前数据库支持的存储引擎

show engines;
1

存储引擎特点

• InnoDB

• MyISAM

• Memory

• 比较

存储引擎选择

索引

概述：

索引是帮助mysql高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

• 优势：提高数据检索的效率，降低数据库的IO成本。

  ​ 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

• 劣势：索引列也是要占用空间的。

​ 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT，UPDATE，DELETE时，效率降低。

索引结构

Btree索引

B+tree索引

Hash索引

B+tree索引优势

索引分类

语法

• 创建索引

create [unique|fulltext] index index_name on table_name(index_col_name...)
例：create index ind_user_name on tb_user(name)
   create unique index tb_user_phone on tb_user(phone)
1
2
3

• 查看索引

show index from table_name;
1

• 删除索引

drop index index_name on table_name;
例：drop index tb_user_email on tb_user;
1
2

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert,update,delete,select的访问频次

show global status like 'COM_______'; //(7个下划线)
1

慢查询日志

1. 查看慢查询日志开关开启情况

show variables like 'show_query_log';
1

2. 

profile

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

select @@have_profiling;  select @@profiling;(查询是否打开)
1

默认profling是关闭的，可以通过set语句在session/global级别开启profling:

set profiling=1;
1

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

#查看每一条SQL的耗时基本情况
show profiles;
 Query_ID | Duration   | Query                                     |
+----------+------------+-------------------------------------------+
|        1 | 0.00043350 | select @@profiling                        |
|        2 | 0.00080550 | select * from tb_user where id=1          |
|        3 | 0.00092550 | select * from tb_user where name='白起' 
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
例：show profile for query 3;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
1
2
3
4
5
6
7
8
9
10
11
12

explain

explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中如何连接和连接的顺序

语法：

#直接在select语句之前加上关键字explain/desc
explain select 字段列表 from 表名 where 条件;
1
2

typr：
NULL：查询时不访问任何表。如：select 'A';
const：主键或唯一索引
ref：非唯一索引
1
2
3
4

使用规则

• 验证索引效率

在未建立索引之前，执行如下SQL语句，查看SQL的耗时
select * from tb_sku where sn='10000003145001';
针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
然后再次执行相同的SQL语句，再次查看SQL的耗时
select * from tb_sku where sn='10000003145001';
1
2
3
4
5
6

• 最左前缀法则

如果索引了多列(联合索引)，要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效(后面的字段索引失效)

explain select * from tb_user where profession='软件工程' and age=31 and status='0'; //key_len=54
explain select * from tb_user where profession='软件工程' and age=31;  //key_len=49
explain select * from tb_user where age=31 and status='0'; //type=all,索引失效
explain select * from tb_user where profession='软件工程' and status='0'; //key_len=47,status失效
explain select * from tb_user where age=31 and status='0' and profession='软件工程';//key_len=54
索引中最左边元素必须存在，与所放位置无关
1
2
3
4
5
6

• 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

explain select * from tb_user where profession='软件工程' and age>30 and status='0';//key_len=49
explain select * from tb_user where profession='软件工程' and age>=30 and status='0';//key_len=54
1
2

• 索引列运算

不要在索引列上进行运算操作，索引将失效

explain select * from tb_user where substring(phone,10,2)='15';
1

• 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

explain select * from tb_user where profession='软件工程' and age=31 and status=0;
explain select * from tb_user where phone=17799990015;(字符串不加引号，隐式类型转换)
1
2

• 模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
1
2
3

• or连接条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id=10 or age=23;
explain select * from tb_user where phone='17799990015' or age=23;
1
2

由于age没有索引，所以即使id，phone有索引，索引也会失效。所以需要针对于age也要建立索引。

• 数据分布影响

如果MYSQL评估使用索引比全表更慢，则不使用索引。

select * from tb_user where phone>='17799990005';
select * from tb_user where phone>='17799990015';
1
2

• SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index:(建议使用，可能不遵循)
explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
ignore index:(不用)
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
force index:(强制)
explain select * from tb_user force index(idx_user_pro) where profession='软件工程';
1
2
3
4
5
6

• 覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部找到)，减少select *

explain select id,profession from tb_user where profession="软件工程" and age=31 and status='0';
explain select id,profession,age,status from tb_user where profession="软件工程" and age=31 and status='0';

explain select id,profession,age,status,name from tb_user where profession="软件工程" and age=31 and status='0';
explain select * from tb_user where profession="软件工程" and age=31 and status='0';
1
2
3
4
5

知识小贴士：

using index condition：查找使用了索引，但是需要回表查询数据

using where;using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

• 前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：
create index idx_xxxx on table_name(column(n));
前缀长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email)/count(*) from tb_user;   1
select count(distinct substring(email,1,5))/count(*) from tb_user;  0.9513，变为4时为0.9123
1
2
3
4
5
6

SQL优化

插入数据

insert优化

1. 批量插入

insert into tb_user values(1,'TOM'),(2,'CAT'),...;
1

2. 手动提交事务

start transaction;
insert into tb_user values(1,'TOM'),(2,'CAT'),...;
...
commit;
1
2
3
4

3. 主键顺序插入

主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89
1
2

大批量插入数据

• 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

#客户端连接服务端时，加上参数 --local-infile
mysql--local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行local指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
1
2
3
4
5
6

视图

• 介绍

视图(view)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

• 创建

create [or replace](可省略) view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option]
1

• 查询

查看创建视图语句：show create view 视图名称;
查看视图数据：select * from 视图名称...;
1
2

• 修改

方式一：create [or replace](不可省略) view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option]
方式二：alter view 视图名称[(列名列表)] as select 语句 [with[cascaded | local] check option]
1
2

• 删除

drop view [if exists] 视图名称 [视图名称]...;
1

• 视图的检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：

cascaded 和 local，默认值为cascaded。

CASCADED:

• 视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或窗口函数(sum(),min(),max(),count()等)
2. distinct
3. group by
4. having
5. union或者union all

• 作用

1. 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

2. 安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3. 数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响

存储过程

基本语法

• 介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
1
2

• 特点

封装，复用
可以接收参数，也可以返回数据
减少网络交互，效率提升
1
2
3

• 创建

create procedure 存储过程名称([参数列表])
begin
     --SQL语句
end;     
1
2
3
4

• 调用

call 名称([参数]);
1

• 查看

select * from information_schema.routines where routine_schema='xxx';--查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称;--查询某个存储过程的定义
1
2

• 删除

drop procedure [id exists] 存储过程名称;
1

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

变量

系统变量

系统变量是MYSQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(global)，会话变量(session)

• 查看系统变量

show [session | global] variables;   --查看所有系统变量
show [session | global] variables like'...';  --可以通过like模糊匹配的方式查找变量
show @@[session | global] 系统变量名;  --查看指定变量的值
1
2
3

• 设置系统变量

set [session | global] 系统变量名=值;
set @@[session | global] 系统变量名=值;
1
2

注意：

如果没有指定session/global，默认是session，会话变量

mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不要提前声明，在用的时候**直接用"@变量名"**使用就可以。其作用域为当前连接

• 赋值

set @var_name=expr[,@var_name=expr]...;
set @var_name:=expr[,@var_name=expr]...;
set @myname='wzhvv';
set @myage:=10;
set @mygender:='男',@myhobby:='mysql';
1
2
3
4
5

select @var_name=expr[,@var_name=expr]...;
select 字段名 into @var_name from 表名;
select @mycocor:='red';
select count(*) into @mycount from tb_user;
1
2
3
4

• 使用

select @var_name;
select @myname,@myage,@mygender,@myhobby;
1
2

注意：

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的begin…end块

• 声明

declare 变量名 变量类型 [default...];
1

变量类型就是数据库字段类型：int，begint，char，varchar，date，time等

• 赋值

set 变量名=值;
set 变量名:=值;
set 字段名 into 变量名 from 表名...;
1
2
3

流程结构

if判断

语法：

if 条件1 then
....
elseif 条件2 then
....
else
....
end if;
1
2
3
4
5
6
7

参数(in,out,input)

用法：

create procedure 存储过程([in/out/inout 参数名 参数类型])
begin
   --SQL语句
end;
1
2
3
4

case

语法一：

case case_value
      when when_value1 then statement_list1
      [when when_value2 then statement_list2]...
      [else statement_list]
end case;     
1
2
3
4
5

语法二：

case
    when search_condition1 then statement_list1
    [when search_condition2 then statement_list2]...
    [else statement_list]
end case;
1
2
3
4
5

while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
     SQL逻辑...
end while;   
1
2
3
4

repeat(相当于do…while)

repeat是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat 
     SQL逻辑...
     until 条件
end repeat;     
1
2
3
4
5

loop

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合以下两个语句使用：

• leave：配合循环使用，退出循环
• iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

[begin_lable:] loop
       SQL逻辑...
end loop [end_lable];     
1
2
3

leave lable;   --退出指定标记的循环体
iterate lable; --直接进入下一次循环
1
2

游标-cursor

游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明，open，fetch和close，其语法分别如下。

• 声明游标

declare 游标名称 cursor for 查询语句;
1

• 打开游标

open 游标名称;
1

• 获取游标记录

fetch 游标名称 into 变量[，变量];
1

• 关闭游标

close 游标名称;
1

条件处理程序(handler)

条件处理程序(handle)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

declare handler_action handler for condition_value [,condition_value]...statement;
handler_action
        continue:继续执行当前程序
        exit:终止执行当前程序
condition_value
        sqlstate sqlstate_value:状态码，如02000
        sqlwarning:所有以01开头的sqlstate代码的简写
        not found:所有以02开头的sqlstate代码的简写
        sqlexeception:所有没有被sqlwarning或not found捕获的sqlstate代码的简写
1
2
3
4
5
6
7
8
9

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。具体语法如下：

create function 存储函数名称([参数列表])
returns type [characteristic...]
begin
    --SQL语句
    return ...;
end;

characteristic说明:
-deterministic:相同的输入参数总是产生相同的结果
-no sql:不包含sql语句
-reads sql data:包含读取数据的语句，但不包含写入数据的语句
1
2
3
4
5
6
7
8
9
10
11

触发器

触发器是与表相关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

语法

创建：
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row --行级触发器
begin
    trigger_stmt;
end;
查看
show triggers;
删除
drop trigger [schema_name] trigger_name; --如果没有指定schema_name,默认为当前数据库
1
2
3
4
5
6
7
8
9
10
11

锁

概述：

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU，RAM，I/O)的争用以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
1

MySQL中的锁，按照锁的粒度分，分为以下三类：

1. 全局锁：锁定数据库中的所有表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

数据库随记

MySQL查询语句的执行顺序

先找到要查询表格或连接要查询的表格，因此FROM才是第一步；

接下来是进行条件筛选，所以是WHERE紧随其后；

然后如果遇到表格有分组的需要，则需要先GROUP BY；

分组时如果也存在筛选条件，这里就要用HAVING进行分组筛选；

这些执行过后才是查询操作SELECT；

SELECT的时候如果遇到重复数据，就需要去重，即使用DISTINCT;

接下来如果要对查询后的数据进行排序，会用到ORDER BY；

最后如果要指定返回的查询数据范围、条数则要用LIMIT/OFFSET函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15