MySQL
DDL-数据库操作(大小写都可以)
-
查询
-
查询所有数据库
SHOW DATABASES;
-
查询当前数据库
SEKECT DATABASE();
-
-
创建数据库
CREATE DATABASE[ID NOT EXISTS] 数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
-
删除
DROP DATABASE[IF EXIST]数据库名;
-
使用
USE 数据库名
-
查询当前数据库所有表
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 exists] 表名;
-
删除指定表,并重新创建该表
truncate table 表名;
DML-添加数据
-
给指定字段添加数据
insert into 表名(字段1,字段2,....)values(值1,值2,...);
-
给全部字段添加数据
insert into 表名 values(值1,值2,...);
-
批量添加数据
insert into 表名(字段1,字段2,....)values(值1,值2,...)(值1,值2,...)(值1,值2,...); insert into 表名 values(值1,值2,...)(值1,值2,...)(值1,值2,...);
-
修改数据
update 表名 set 字段名1=值1,字段名2=值2,....[where 条件]
DQL
-
查询多个字段
select 字段1,字段2,字段3...from表名 select * from 表名;
-
设置别名
select 字段1[as 别名1],字段2[as 别名2]....from 表名;
-
去除重复记录
select distinct 字段列表 from 表名;
-
条件查询
select 字段列表 from 表名 where 条件列表;
-
聚合函数
select 聚合函数(字段列表) from 表名;
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
分组查询
selevt 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];
-
排序查询
select 字段列表 from 表名 order by 字段1 排序方法1,字段2 排序方式2; 排序方式 asc:升序(默认值) desc:降序
-
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
-
执行顺序
DCL
-
查询用户
use mysql; select * from user;
-
创建用户
create user '用户名'@'主机名' idetified by '密码' %代表任意主机
-
修改用户密码
alter user '用户名'@'主机名' idetified with mysql_native_paswword BY '新密码';
-
删除用户
drop user '用户名'@'主机名';
-
权限控制
show grants for ‘用户名’@‘主机名’;
-
授予权限
grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’;
-
撤销权限
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
字符串函数
函数 | 功能 |
---|---|
concat(s1,s2,....sn) | 字符串拼接,将s1,s2,....sn拼接成一个字符串 |
lower(str) | 将字符串str全部转为小写 |
upper(str) | 将字符串str全部转为大写 |
lpad(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
rpad(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
trim(str) | 去掉字符串头部和尾部的空格 |
substring(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
数值函数
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x,y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x,y) | 求参数x的四舍五入的值,保留y为小数 |
日期函数
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 放回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取指定date的年份 |
month(date) | 获取指定date的月份 |
day(date) | 获取指定date的日期 |
date_add(date,interval expr type) | 返回一个日期/时间值加上以一个时间间隔expr后的时间值 |
datediff(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
函数 | 功能 |
---|---|
if(value,t,f) | 如果value为true,则返回t,否则返回f |
ifnull(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [val1] then [res1]...else[default] end | 如果val1为true,返回res1,...否则返回default默认值 |
case [expr] when [val1] then [res1]...else[defaule] end | 如果expr的值等于val1,返回res1,...否则返回default默认值 |
约束
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束 | 保证字段值满足某一个条件 | check |
外键约束 | 用来让两张表的数据之间建立链接,保证数据的一致性和完整性 | foreign key |
外键约束
-
添加外键
create table 表名( 字段名 数据类型, .... [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列表) )
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
-
删除外键
alter table 表名 drop foreign key 外键名称;
-
删除/更新行为
行为 说明 no action 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 restrict 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新 cascade 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录 set null 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null set default 父表有变更时,子表将外键列设置成一个默认的值
多表关系
-
隐式内连接
select 字段列表 from 表1,表2 where 条件 ...,
-
显式内连接
select 字段列表 from 表1 [inner]join 表2 on 链接条件...;
-
左外连接
select 字段列表 from 表1 lef [outer] join 表2 on 条件 ...;
-
右链接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
-
自链接
select 字段列表 from 表a 别名a join 表a 别名b on 条件...;
-
联合查询 - union,union all
select 字段列表 from 表a ... union[all] select 字段列表 from 表B ...; 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
-
子查询
select * from t1 where column1 = (select column1 from t2); 子查询外部的语句可以是insert/update/delete/select的任何一个
-
标量子查询
子查询返回的结构是单个值(数字、字符串、日期等),最简单的形式
-
列子查询
操作符 描述 in 在指定的集合范围之内,多选一 not in 不在指定的集合范围之内 any 子查询返回列表中,有任意一个满足即可 some 与any等同,使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足 -
行子查询
子查询返回的结构是一行(可以是多行)
-
事物
-
查看/设置事物提交方式
select @@autocommit; 默认auttocommit = 1 set @@autocommit = 0; 手动提交
-
提交事物
commit; 正常完成就提交
-
回滚事物
rollback; 抛出异常就回滚事物
-
开启事物
start transaction 或 begin;
-
事物四大特性
-
原子性:事物是不可分割的最小操作单元,要么全部成功,要么全部失败。
-
一致性:事物完成时,必须使所有的数据都保持一致状态。
-
隔离性:数据库系统提供的隔离机制,保证事物在不受外部并发操作影响的独立环境下运行。
-
持久性:事物一旦提交或回滚,它对数据库中的数据的改变就是永久的。
-
-
并发事物问题
问题 描述 脏读 一个事物读到另一个事物还没提交的数据 不可重复读 一个事物先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 幻读 一个事物按照条件查询数据时,没用对于的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影” -
事物隔离级别
隔离级别 脏读 不可重复读 幻读 read uncommitted √ √ √ read committed × √ √ repeatable read(默认) × × √ serializable × × × -- 查看事物隔离级别 select @@transsaction_isolation; --设置事物隔离级别 set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}
进阶篇
-
存储引擎
-
在创键表时,指定储存引擎
create table 表名( 字段1 字段1类型 [comment 字段1 注释], .... 字段n 字段n类型 [comment 字段n 注释] )ENGINE = INNODB [comment 表注释]; -- 查询建表语句 --默认存储引擎:InnoDB show create table account;
-
-
InnoDB
-
DML操作遵循ACID模型,支持事物;
-
行级锁,提高并发访问性能;
-
支持外键FOREIGN KEY约束,保证数据的完整性和正确性;
-
-
MyISAM
-
不支持事物,不支持外键
-
支持表锁,不支持行锁
-
访问速度快
-
xxx.sdi:存储表结构信息
-
xxx.MYD:存储数据
-
xxx.MYI:存储索引
-
-
Memory
-
内存存放
-
hash索引(默认)
-
xxx.sdi:存储表结构信息
特点 InnoDB MyISAM Memory 存储限制 64TB 有 有 事务安全 支持 - - 锁机制 行锁 表锁 表锁 B+tree索引 - 支持 支持 全文索引 支持(5.6版本之后) 支持 - 空间使用 高 低 N/A 内存使用 高 低 中等 批量插入速度 低 高 高 支持外键 支持 - - Hash索引 支持 - 支持 -
-
存储引擎选择
-
InnoDB:是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作出来插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
-
MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事物的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
-
MEMORY:将所有数据保持在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
-
索引
-
优缺点
优势 劣势 提高数据检索的效率,降低数据库的IO成本 索引列也要占用空间的。 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。 -
索引结构
索引结构 描述 B+Tree索引 最常见的索引类型,大部分引擎都支持B+树索引 Hash索引 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 R-tree(空间索引) 空间索引是MySAM引擎的一个特殊引类型,主要用于地理空间数据类型,通常使用较少 Full-text(全文索引) 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES -
索引分类
分类 含义 特点 关键字 主键索引 针对表中主键创建的索引 默认自动创建,只能有一个 PRIMARY 唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE 常规索引 快速定位特定数据 可以有多个 全文索引 全文索引查找的是文本中的关键词,而不是索比较索引中的值 可以有多个 FULLTEXT 在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 含义 特点 聚集索引 将数据存储与索引放到一块,索引结构的叶子节点保存了行数据 必须有,而且只有一个 二级索引 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多个 聚集索引选取规则:
-
如果存在主键,主键索引就是聚集索引。
-
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
-
如果表没有主键,或没用合适的唯一索引,则InnoDB会自动生成 一个rowid作为隐藏的聚集索引。
-
-
创建索引
create [UNIQUE | FULLTEXT] INDEX index_name(索引名字) ON table_name(字段名索引)(index_col_name,...);
-
查看索引
show index from table_name;
-
删除索引
drop index index_name on table_name;
-
SQL性能分析
-
SQL执行频率
MySQl客户端链接成功后,通过show[session|global] status 命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
show global status like 'Com_______'
-
慢查询日志
#开启MySQL慢日志查询开关 slow_query_log=1 #设置慢日志的时间为两秒,SQL语句执行时间超过两秒,就会视为慢查询,记录慢查询日志 long_query_time=2
-
profiles操作
select @@have_profiling;
-
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
set profiling=1;
-
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况 show profiles; #查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;
-
explain执行计划
#直接在select语句之前加上关键字explain/desc explain select 字段列表 from 表名 where 条件;
EXPLAIN执行计划各字段含义:
-
id select 查询的序列号,标识查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)。
-
select_type 表示SELECT的类型,常见的取值有SIMPLE(简单表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
-
type 表示连接类型,性能由好到茶的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
-
possible_key 显示可能应用在这张表上的索引,一个或多个。
-
Key 实际使用的索引,如果NULL,则没用使用索引。
-
Key_len 表示索引中使用的字节数,该值为索引字段最大可能长度,在不损失精确性的前提下,长度越短越好。
-
rows MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是精确的
-
filtered 表示返回结构的行数占许需读取行数的百分比,filtered的值越大越好。
-
-
-
索引使用
-
验证索引效率
-
在未建立索引之前,执行如下SQL语句,查看SQL的耗时。
select * from tb_sku(表名) where sn = '100000003145001';
-
针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
-
然后再次执行相同的SQL语句,再次查看SQL的耗时。
select * from tb_sku where sn='100000003145001';
-
-
最左前缀法则:如果所有了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部门失效(后面的字段索引失效)
-
-
索引使用
-
索引列运算 不要再索引列上进行运算操作,索引将失效。
explain select * from tb_user where substring(phone,10,2) = '15';
-
字符串类型字段使用时,不加引号,索引将失效。
-
模糊查询 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
-
or链接的条件 用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
-
SQL提示 是优化数据库的一个重要手段,简单来说,加上在SQL语句中加入一些人为的提示来达到优化操作的目的。use index、ignore index、force index
-
覆盖索引 尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *。
tip: using index condition :查询使用了索引,但是需要回表查询数据 using where;using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询
-
前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
create index idx_xxxx on table_name(column(n));
前缀索引长度
select count(distinct email) / count(*) from tb_user; select count(distinct substring(email,1,5))/count(*) from tb_user;
-
单列索引与联合索引 单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含多个列。
-
索引设计原则
-
针对数据量较大,且查询比较频繁的表建立索引
-
针对常作为查询条件、排序、分组操作的字段建立索引
-
尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
-
如果字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引。
-
尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
-
要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,回影响增删改的效率。
-
如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
-
-
SQL优化
-
插入数据
-
insert优化
-
批量插入
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
-
-
手动提交事物
start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;
-
主键顺序插入
主键乱序插入:8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入: 1 2 3 4 5 7 8 9 15 21 88 89
-
-
主键优化
-
数据组织方式 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的。
-
页分裂 页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据多大,会行溢出),根据主键排列。
-
页合并 当删除一行记录时,实际上记录并没有物理删除,只是记录被标识(flaged)为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
-
主键设计原则 满足业务需求的情况下,尽量降低主键的长度。 插入数据时,尽量选择顺序插入 ,选择使用AUTO_INCREMENT自增主键。 尽量不要使用UUID做主键或者是其他自然主键,如身份证号 业务操作时,避免对主键的修改。
-
-
order by优化
-
Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序都叫FileSort排序。
-
Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。
-
根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
-
尽量使用覆盖索引。
-
多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
-
如果不可避免的出现filesort,大数据排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
-
-
group by优化
-
在分组操作时,可以通过索引来提高效率。
-
分组操作时,索引的使用也是满足最左前缀法则的。
-
-
limit优化
一个常见又非常头疼的问题减少limit 2000000,10,此时需要MySQL排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
-
count优化
-
MyLSAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
-
InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
-
count的几种用法 count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。
-
用法:
-
count(主键):
-
InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null).
-
-
count(字段):
-
没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
-
有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回会给服务层,直接按行进行累加。
-
count(1):InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。
-
count(*):InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
-
-
-
按照效率排序的话:count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用count(**)。
-
-
-
update优化
-
尽量根据主键/索引字段进行数据更新
-
-
视图
-
视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要工作就落在创建这条SQL查询语句上。
-
创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL]CHECK OPTION]
-
查询
查看创建视图语句:SHOW CREATE VIEW 视图名称; 查看视图数据:SELECT * FROM 视图名称....;
-
修改
方式一:CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL]CHECK OPTION] 方式二:ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL]CHECK OPTION]
-
删除
drop view [if exists] 视图名称 [视图名称]...
-
视图的检查选项 当使用with check option子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项: CASCADED和LOCAL,默认值为CASCADED。
-
作用
-
简单:视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
-
安全:数据库可以授权,但不能授权到数据库特定的行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
-
数据独立:视图可帮助用户屏蔽真实表结构变化带来的影响。
-
存储过程
-
特点:封装,复用 可以接受参数,也可以返回数据 减少网络交互,效率提升
-
创建
create procedure 存储过程名称([参数列表]) begin --SQL语句 end;
-
调用
call 名称([参数]);
-
查看
select * from infromation_schema.rountines where routine_sehema = 'xxx'--查询指定数据库的存储过程及状态信息 show create procedure 存储过程名称;--查询某个存储过程的定义
-
删除
drop procedure [if exists] 存储过程名称;
-
注意:在命令行中,执行创建存储过程的SQL时,需要通过关键字delimiter指定SQL语句的结束符
-
变量 系统变量是MySQl服务器提供,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
-
查看系统变量
show [session | global] variables; ----查看所有系统变量 show[session | global] variables like '...'; ---可以通过like模糊匹配方式查找变量 select @@[session | global]系统变量名; -- 查看指定变量的值
-
设置系统变量
set [session | global] 系统变量名 = 值; set @@[session | global] 系统变量名 = 值;
-
注意: 如果没有指定session/global,默认是session,会话变量。 mysql服务重新启动之后,所设置的全局参数会失效,要想不失效,可以在/etc/my.cnf中配置。
-
赋值
set @var-name = expr [,@var_name = expr]...; set@var-name := expr [,@var_name = expr]...; eg: set @myname :='itcast'; set @myage := 10; select @var_name :=expr [,@var_name :=expr]...; select 字段名 into @var_name from 表名;
-
使用
select @var_name;
-
局部变量 声明
declare 变量名 变量类型[default ...];
变量类型结束数据库字段类型:int、bigint、char、varchar、date、time等。 赋值
set 变量名 = 值; set 变量名 := 值; select 字段名 into 变量名 from 表名...;
-
if 判断
if 条件1 then ... elseif 条件2 then --可选 ... else --可选 ... end if;
-
参数
类型 含义 备注 in 该类参数作为输出,也就是需要调用时传入值 默认 out 该类参数作为输出,也就是该参数可以作为返回值 inout 既可以作为输出参数,也可以作为输出参数 create procedure 存储过程名称([in/out/inout 参数名 参数类型]) begin --SQL语句 end;
-
case
case case_value when when_value1 then statement_list1 [when when_value2 then statement_list2]... [else statement_list] end case; case when search_condition1 then statement_list1 [when search_condition2 then statement_list2]... [else statement_list] end case;
-
while
while 条件 do SQL逻辑... end while;
-
repeat:有条件的循环控制语句,当满足条件的时候退出循环。
#先执行一次逻辑,然后判断逻辑是否满足,如果满足,则退出。如果不满足,则继续下一次循环 repeat SQL逻辑.. until条件 end repeat;
-
loop:实现简单的循环,如果不在SQL逻辑中增加退出循环的条件,可以用来实现简单的死循环。 leave:配合循环使用,退出循环。 iterate:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环。
[begin_label]:Loop SQL逻辑... end loop [end_label]; leave label; --退出指定标记的循环体 iterate label; -- 直接进入下一次循环 eg: creat procedure p(in n int) begin declare total int default 0; sum:loop if n<=0 then leave sum; end if; set total := total + n; set n := n - 1; end loop sum; select total; end; call p(10);
-
-
游标
-
声明游标
declare 游标名称 cursor for 查询语句;
-
打开游标
open 游标名称;
-
获取游标记录
fetch 游标名称 into 变量[,变量];
-
关闭游标
close 游标名称;
-
-
条件处理程序
NOT FOUND捕获的SQLSTATE代码的简写declare handler_actionn handler for condition_value[,condition_value]... statement; handler_action continue:继续执行当前程序 exit:终止执行当前程序 condition_value SQLSTATE sqlstate_value:状态码,如:02000 SQLWARNING:所有以01开头的SQLSTATE代码的简写 NOT FOUND:所有以02开头的SQLSTATE代码的简写 SQLEXCEPTION:所有没有被SQLWAGNING或NOT FOUND捕获的SQLSTATE代码的简写
-
存储函数
create function 存储函数名称([参数列表])
returns type[characteristic ...]
begin
--SQL语句
return...;
end;
characteristic说明:
deterministic:相同的输入参数总是产生相同的结果
no SQL :不包含SQL语句
READS SQL DATA:包含读取数据的语句,但不包含写入数据的语句。
触发器
-
创建
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,默认为当前数据库。
锁
-
全局锁 就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,已经更新操作的事物提交语句都将被阻塞。
#加全局锁 flush tables with read lock; #备份 mysqldump -uroot -p1234 db01 > D:/da01.sql; -u:用户名 -p:密码 db01:备份的数据库 > 放文件路径
特点
-
如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
-
如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
在InnoDB引擎中,我们可以在备份时加上参数 -- single-transaction 参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p123456 itcast>itcast.sql
-
-
表锁
-
表共享读锁(read lock)
-
表独占写锁(write lock)
加锁:lock tables 表名... read/write 释放锁:unlock tables / 客户端断开连接。
-
-
表级锁
-
元数据锁(meta data lock,MDL) 为了避免DML与DDL冲突,保证读写的正确性。
对应SQL 锁类型 说明 lock tables xxx read/write SHARED_READ_ONLY/SHARED_NO_READ_WRITE select\select...lock in share mode SHARED_READ 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 insert、update、delete、select...for update SHARED_WRITE 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 alter table... EXCLUSIVE 与其他的MDL都互斥 -
意向锁
-
意向共享锁(IS):由语句select...lock in share mode 添加。 与表锁共享锁(read)兼容,与表锁排它锁(write)互斥。
-
意向排他锁(IX): 由insert、update、delete、select...for update 添加。 与表锁共享锁(read)及排他锁(write)都互斥。意向锁之间不会互斥。
-
-
行级锁 InnoDB的数据是基于所以组织的,行级是通过对索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为三类:
-
行锁(Record Lock):锁定单个行记录的锁,防止其他事物对此进行update和delete。在RC、RR隔离级别下都支持。
-
间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事物在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
-
临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持
-
-
行锁 InnoDB实现了以下两种类型的行锁:
-
共享锁(S):允许一个事物去读一行,阻止其他事物获取相同数据集的排他锁。
-
排他锁(X):允许获取排他锁的事物更新数据,阻止其他事物获得相同数据集的共享锁和排他锁。
当前锁类型 \请求锁类型 S(共享锁) X(排他锁) S(共享锁) 兼容 冲突 X(排他锁) 冲突 冲突
-
-