在windows 下 启动Mysql cd到mysql\bin 目录下 在dos窗口下 启动\封闭mysql效劳
//启动mysql效劳 mysqld --console //封闭mysql效劳 mysqladmin -uroot shutdown
SQL分类
SQL 主要语句可以划分为一下3类
DDL:数据定义言语，这些语句定义差别的数据段、数据库、表、列、索引等数据库对象。常常运用语句症结字主要包括create,drop,alter等
DML:数据操纵语句，用于增添、删除、更新和查询数据库纪录，并搜检数据完整性。常常运用语句症结字主要包括 insert,delete,update和select等。
DCL数据掌握语句，用于掌握差别数据段直接的许可和接见级别的语句。这些语句定义了数据库、表、字段、用户的接见权限和平安级别。主要 的语句包括症结字grant、revoke等
DDL语句
是对数据库内部的对象举行竖立 、删除、修正等操纵言语，它和DML语句最大的辨别是DML只是对表内部数据操纵，而不触及表的定义、构造的修正，更不会触及其他对象。DDL更多地由数据库治理员（DBA）运用。
衔接mysql效劳器 mysql -uroot -p 竖立数据库test1 create database test1; 显现有哪些数据库 show databases; //mysql 自动竖立的表有 information_schema:主要存储了体系中的一些数据库信息，比方用户表信息、列信息、权限信息、字符集信息、分区信息等等 cluster：存储了体系的集群信息 mysql：存储了体系的用户权限信息。 test：体系自动竖立的测试数据库，任何用户都可以接见 挑选数据库 use test1 显现test1数据库中竖立的一切表 show tables 删除数据库 drop database test1; 竖立表 create table emp(ename varchar(10),hiredata date,sal decimal(10,2),deptno int(2)); 检察表定义 desc emp; 检察竖立表的定义 show create table emp; 删除表 drop table emp; 修正表 alter table emp modify ename varchar(20); 增添表字段 alter table emp add column age int(3); 删除表字段 alter table emp drop column age; 字段更名 alter table emp change age age1 int(4); change 和modify都可以修正表的定义，差别的是change背面须要写两次列名，不轻易，然则change的长处是可以修正列称号，则modify则不能 修正字段排序 alter table emp add birth date after ename; alter table emp modify age int(3) first; 更改表名 alter table emp rename emp1;
DML语句
是指对数据库中表纪录的操纵，主要包括表纪录的插进去（insert）、更新（update）、删除（delete）和查询（select）。
插进去纪录
insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
也可以不必指定字段称号，然则values背面的递次要和字段的分列递次一致
inset into emp('zzx1','2000-01-01','2000',1);
含可空字段、非空然则含有默许值的字段、自增字段、可以不必再insert后的字段列表内里涌现，values背面只写对应字段称号的value，没写的字段可以自动设置为null、默许值、自增的下一个数字
批量增添用逗号离隔
insert into dept values(5,'xxx'),(8,'xxx');
更新纪录
update emp set sal=4000 where ename='xxx';
删除纪录
delete from emp where ename='doney';
查询纪录
select * from emp;
*示意一切纪录，也可以用逗号离隔的字段来挑选查询
查询不反复的纪录
select distinct deptno from emp;
前提查询
用where症结字来完成，可以运用<>!=等多前提可以运用or、and等
排序和限定
desc和asc是排序症结字，desc是降序、asc是升序分列 ORDER BY 排序,默许是升序
select * from emp order by sal;
假如排序字段的值一样，则值雷同的字段依据第二个排序字段举行排序，假如只需一个排序字段，则雷同字段将会无序排序
select * from emp order by deptno,sal desc;
限定
select * from emp order by sal limit 3;
//前者是肇端偏移量，后者是显现行数
select * from emp order by sal limit 1,3;
limit 和order by 一同运用来做分页
聚合
用户做一下些汇总操纵
sum(乞降),count(*)(纪录数)，max(最大值)，min（最小值）
with rollup 是可选语法，示意是不是对分类聚合后的结果举行再汇总
having 示意对分类后的结果在举行前提的过滤。
select deptno,count(1) from emp group by deptno having count(1)>=1;
表衔接
大类上分为外衔接和内衔接
外链接 又分为左衔接和右衔接
左衔接:包括所以的左边表中的纪录以至是右侧表中没有和它婚配的纪录。
右衔接：同上
select ename,detname from emp left join dept on emp.deptno=dept.deptno; 左衔接和右衔接可以互相转换
子查询
select * from emp where deptno in(select deptno from dept); 假如子查询纪录唯一，可以运用=替代in select * from emp where deptno =(select deptno from dept limit 1);
纪录查询
将两个表的数据依据肯定的查询出来后，将结果合在一同显现
union all 是将结果鸠兼并在一同，而union是将union all后的结果在举行一次distinct，去除反复
select deptno from emp union all select deptno from dept; select demtno from emp union select deptno from dept;
? xxx 来检察
假如要检察种别 ? data types 细致的 ? int 检察语法 如 ? create table
数据范例
关于整形数据，MySql还支撑在范例称号背面的小括号设置宽度，默许设置为int(11),合营zerofill, 当数字位数不够的时刻，用字符‘0’添补 alter table t1 modify id1 int zerofill
关于小数，MySql 分为两种，浮点数和定点数。浮点数包括float和double，而定点数只需decimal，定点数在Mysql内部是以字符串情势寄存，比浮点数更准确，合实用于钱银等精度高的数据
浮点数和定点数可以用范例称号加（M，D) M是几位，D是位于小数点背面几位。
日期范例
DATE来示意年月日
DATETIME来示意年月日时分秒
TIME来示意时分秒
当前体系时候，通常常运用TIMESTAMP来示意
TIMESTAMP
竖立一个字段为TIMESTAMP范例，体系自动竖立了默许值为CURRENT_TIMESTAMP（体系日期）。同时MySql划定TIMESTAMP范例字段一列只能有一个默许值current_timestamp。假如修正会报错.
TIMESTAMP 另一个主要特点是与时区有关。当插进去时候时，先转换为当地时区后寄存，而从数据库掏出时，一样会将日期转换为当地时区后显现，如许两个时区的用户看到同一个时区可以就不一样
检察当前时区 show variables like 'time_zone'; 修正时区 set time_zone='+9.00';
DATETIME插进去的花样
YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 的字符串许可任何标点标记用来做时候部份的距离符 如92@12@31 11^30^45 YYYYMMDDHHMMSS 或YYMMDDHHMMSS的花样没有距离符的字符串
字符串范例
CHAR和VARCHAR范例
二者的主要辨别是存储体式格局差别：CHAR列的长度牢固为竖立表时声明的长度，长度可以为0-255；二VARCHAR列中的值是可变长度。同时在检索的时刻，CHAR列删除尾部的空格 ，而VARCHAR保存空格，由于CHAR是牢固长度，所以它的处置惩罚速率别VARCHAR快许多，然则其瑕玷是糟蹋内存，在运用中VARCHAR被更多的运用
create table vc (v varchar(4),c char(4))
insert into vc values('ab ','ab ');
selelct length(v),length(c) from vc
//4,2
罗列
create table vc (v varchar(4),c char(4))
insert into vc values('ab ','ab ');
selelct length(v),length(c) from vc
//4,2
set范例
set范例可以一次拔取多个成员
create table t2 (col set('a','b','c','d'));
INSERT into t2 VALUE ('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
关于（a,d，a)这个包括反复成员的鸠合只取一次 结果为’a,d'
运算符
p==/==除法猎取商 MOD==%==除法猎取余数
=和<=>辨别
不能用于null比较，后者可以
between 运用花样 a between min and max 等价于 a>=min and a<=max in的运用花样 a in(value1,value2...); like 运用花样如 a like %123%,当字符串含有123则返回1 不然返回0 REGEXP 运用花样 str REGEXP str_pat 当str字符串中含有str_pat 相婚配的字符串，则返回1
位运算
| 运算符 | 作用 |
|---|---|
| & | and |
| | | or |
| ^ | xor |
| ~ | 位异或 |
| > | 位右移 |
| << | 位左移 |
常常运用函数
字符串函数
| 函数 | 功用 |
|---|---|
| CONCAT(s1,s2,s3…) | 衔接s1到sn的字符串（任何字符串和null拼接都是null） |
| insert(str,x,y,instr) | 将字符串str从x位置最先，y字符长的子串替代为字符串instr |
| lower(str) | 将字符串str中一切字符变成小写 |
| UPPER(str) | 大写 |
| LEFT(str,x) | 返回字符串str最左边x个字符 |
| RIGHT(str,x) | 返回字符串str最右侧的x个字符 |
| LPAD(str,n,pad) | 用字符串pad对str最左边举行添补，直到长度为n个字符串长度 |
| PRPAD(str,n,pad) | 用字符串pad对str最右侧举行添补，直到长度为n个字符串长度 |
| LTRIM(str) | 去掉字符串str左边的空格 |
| RIGHT(str) | 去掉字符串str行尾的空格 |
| REPEAT(str,x) | 返回str反复x次的结果 |
| REPLACE(Str,a,b) | 用字符串b替代字符串str中一切涌现的字符串a |
| STRCMP(s1,s2) | 比较字符串s1和s2 |
| TRIM(str) | 去掉行尾和行头的空格 |
| SUBSTRING(str,x,y) | 返回字符串str x位置起y字符串长度的字串 |
数字函数
| 函数 | 功用 |
|---|---|
| ABS(X) | 返回x的绝对值 |
| CEIL(X) | 返回大于x的最小整数值 |
| FLOOR(X) | 返回小于x的最大整数值 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0-1内的随机值 |
| ROUND(x,y) | 返回参数x的四舍五入的有y位小数的值 |
| TRUNCATE(x,y) | 返回数值x截断为y位小树的结果 |
日期和时候函数
| 函数 | 功用 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时候 |
| NOW() | 返回当前的日期和时候 |
| UNIX_TIMESTAMP(date) | 返回date的unix时候戳 |
| FROM_UNIXTIME | 返回UNIX时候戳的日期值 |
| WEEK(date) | 返回日期date为一年中的第几周 |
| YEAR(date) | 返回日期date的年份 |
| HOUR(time) | 返回time的小时价 |
| MINUTE(time) | 返回time的分钟值 |
| MONTHNAME(date) | 返回date的月份名 |
| DATE_FROMATE(date,fmt) | 返回按字符串fmt花样化日期date值 |
| DATE_ADD(date,interval expr type) | 返回一个日期或时候值加上一个时候距离的时候值 |
| DATEDIFF(expr,expr2) | 返回肇端时候expr和完毕时候expr2之间的天数 |
流程函数
| 函数 | 功用 |
|---|---|
| IF(value,t f) | 假如value是真 返回 t；不然返回f |
| IFNULL(value1,value2) | 假如value1不为空，返回value1，担任返回value2 |
| CASE WHEN[value1] THEN[value2]…ELSE[default] END | 假如value1是真，返回result1不然返回defalut |
| case [expr] WHEN[value1] THEN[value2]…ELSE[default] END | 假如expr即是value1，返回result1不然返回defalut |
实例
create table salary(userid int ,salary decimal(9,2)); insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null); select * from salary select if(salary>2000,'high','low') from salary; select ifnull(salary,0) from salary; select case when salary <=2000 then 'low' else 'high' end from salary; select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
其他函数
| 函数 | 功用 |
|---|---|
| DATABASE() | 返回确实数据库库名 |
| VERSION() | 返回当前数据库版本 |
| USER() | 返回当前登录用户名 |
| INET_ATON(IP) | 返回ip地点的数字示意 |
| INET_NTOA(num) | 返回数字代表的ip地点 |
| PASSWORD(str) | 返回字符串str加密版本 |
| MD5() | 返回字符串的md5值 |
MySql引擎
MySql支撑的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，个中InnoDB和BDB供应事宜平安表，用户可以挑选差别的数据存储引擎来进步运用的效力
竖立表假如不指定存储引擎，体系默许运用默许存储引擎，MySql5.5之前的默许引擎是MyISAM，5.5今后改成InnoDB。假如要修正默许的存储引擎，可以在参数文件中设置default-table-type.
create table salary(userid int ,salary decimal(9,2)); insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null); select * from salary select if(salary>2000,'high','low') from salary; select ifnull(salary,0) from salary; select case when salary <=2000 then 'low' else 'high' end from salary; select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
MyISAM
MyISAM 不支撑事宜、也不 不支撑外键，其长处是速率快，对事宜完整性没有请求。以SELECT和INSERT为主的运用基本上都就可以运用这个表
InnoDB
InnoDB存储引擎供应了具有提交、回滚和崩溃恢复能力的事宜平安。然则对照MyISAM的存储引擎，InnoDB写的处置惩罚效力差一些，而且会占用更多的磁盘空间以保存数据和索引。
create table autoincre_demo (i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3')
假如插进去空或许0，则现实插进去的将是自动增进后的值。
可以经由历程以下语句强迫设置自动增添列的初始值，默许从1最先，然则该强迫的默许值是保存到内存中，假如数据库从起，这个强迫的默许值会丧失，就须要数据库启动后从新设置
ALTER TABLE *** auto_increment =n
MEMORY
memory 存储引擎运用存在于内存中的内容来竖立表，每一个MEMORY表现实对应一个磁盘文件，花样是.fm,MEMORY表的接见非常快，由于它的数据是放在内存中，而且默许运用HASH索引，然则一旦效劳封闭，表中的数据就会
alter table t2 engine=memory; show TABLE status like 't2' 给memory表竖立索引。可以指定hash索引照样btree索引 create index mem_hash using hash on tab_memory(city_id);
在启动MySql效劳的时刻运用–init-file选项，把INSERT INTO … SELECT或LOAD DATA INFILE如许的语句放入这个文件中，就可以在效劳启动时从持久稳固的数据源装载表
效劳器须要充足的内存来保持同一时候运用的MEMORY表，当不须要MEMORY表的内容，要开释MEMORY表的内存，实行DELETE FROM或 TRUNCATE TABLE 或许是DROP TABLE
每一个MEMORY表中可以安排的数据量的大小，受max_heap_table_size体系变量的束缚，初始值是16mb，可以依据须要加大、
MEMORY范例的存储引擎主要用在那些内容变化不寻常的表，或作为统计操纵的中心结果表，便于高效的对中心结果举行剖析并取得终究的统计结果。
TokuDB
TokuDB是第三方的存储引擎，是一个高机能、支撑事宜处置惩罚的MySql和MariaDB的存储引擎，具有高扩展性、高压缩、高效力的写入机能，支撑大多数在线的DDL操纵
TokuDB 迥殊实用的场景
日记数据，由于日记数据平常插进去频仍且贮存量大
历史数据，平常不会有在写的操纵，可以应用TokuDB的高压缩特征举行存储
在线DDL频仍的场景
几种常常运用存储引擎的实用环境
MyISAM:假如运用是以读操纵和插进去操纵为主，只需很少的更新和删除操纵，而且对事宜的完整性，并发性请求不高，那末挑选这个引擎非常适宜
Innodb：用于事宜的处置惩罚，支撑外键。假如运用对事宜的完整性较高的请求，在并发前提下请求数据的一致性，数据除了插进去和查询外，还包括许多的更新和删除操纵，那末Innodb存储引擎比较适宜
MEMORY：将一切数据都存在RAM中，假如须要疾速定位纪录和其他相似数据的环境下，可以供应极快的接见，缺点在于对表大小的限定，太大的表没法缓存在内存中，其次是要确保表的数据是可恢复的.
MERGE:用于将一系列同等MyISAM表以逻辑体式格局组合在一同，并作为一个对象援用它们。MERGE表的长处在于可以打破单个MyISAM表大小的限定，而且经由历程将差别的表散布在多个磁盘上，可以有用的改良MERGE表的接见效力
Text与BLOB
假如保存少许字符串会挑选CHAR和VARCHAR 然则保存较大文本时，挑选text或blob，二者主要差别是blob能用来保存二进制数据如图片；而text只能保存字符数据
BLOB与TEXT引发的机能题目，迥殊是在实行大批的删除数据时，删除操纵会留下很大的朴陋，今后填入这些朴陋的纪录在插进去的机能上会有影响，竖立按期运用OPTIMIZE TABLE对这类表举行碎屏整顿
optimize table t
运用合成的索引来供应大文本字段的查询机能
合成索引就是依据大文本字段的内容竖立一个散列值，并把值存储在零丁的数据列中，接下来就是经由历程检索散列值找到数据行，然则只能做到准确婚配不能运用局限搜刮。可以运用MD5，SHA1，CRC32 等生成散列值，运用准确婚配，在肯定水平上削减了I/O，进步了查询效力。假如散列算法生成的字符串带有尾部空格，就不要存储在CHAR或VARCHAR列中，它会受尾部空格的影响
假如须要对BLOB或CLOB字段举行隐约查询，MySQL进步前缀索引，也就是只为字段的前n列竖立索引 desc select * from t where context like 'beijing%' \G;
注重事项
在不必要的时刻防备检索大型的BLOB或TEXT：如SELECT * 查询，只管从相符前提的数据行中检索BLOB或TEXT指
把BLOB或TEXT列星散到零丁表中：在某些环境下，假如把这些数据列移动到第二张数据表中，可以把原数据表中的数据列转换为牢固长度的数据行花样，削减主表的碎片，可以取得牢固长度数据行的机能上风。还可以在运转SELECT * 查询的时刻不会经由历程网络传输大批的BLOB或TEXT指
设想索引的准绳
搜刮的索引列，不肯定是所要挑选的列。最适宜索引的列是涌如今where字句中的列，或衔接字句中指定的列，而不是涌如今select症结字后的列表中的列
运用唯一索引.斟酌到某列中的值散布，索引的列基本越大，索引的结果越好。入寄存出生日期的列具有各部雷同的值，很轻易辨别，然则纪录性别的列，只含有男和女对此类举行索引没有多大优点
运用短检索。假如对字符串举行检索，应当指定一个前缀长度。比方：一个CHAR(200)列，假如前10个或20个字符内，多数值是唯一的，那末就不要对全部列举行检索。对前10个或20个字符举行检索可以节约大批索引空间，是查询更快。
应用最左前缀。在竖立一个n列索引时，现实是竖立了MySQL可应用的n个索引。多列索引可起几个索引的作用，由于可应用索引最左边的列级来婚配。
不要过分索引。每一个索引都是占用分外的磁盘空间，并下降写操纵的机能。在修正表内容的时刻，索引必需举行响应的更新，有时刻须要重构。假如有一个索引很少被用到，那末会不必要的减缓表的修正速率。别的，mysql在生成一个实行计划时，要斟酌各个索引，这也要花费时候。竖立过剩的索引给查询优化带来了更多的事情
关于Innodb，纪录默许会依据肯定的递次排序，假如有明白的定义主键，则依据主键排序递次保存。
存储历程和函数
什么是存储历程和函数
存储历程和函数是事前经由编译并存储在数据库中的一段SQL语句的鸠合，挪用存储历程和函数
可以简化运用开发人员的许多事情，削减数据在数据库和运用效劳器之间的传输，关于供应数据处置惩罚的效力是有优点的。
存储历程很函数的辨别在于函数必需有返回值，而存储历程没有，贮存历程的参数可以运用IN，OUT，INOUT范例，而函数的参数只能是IN范例的。假如有函数从其他范例的数据库迁徙到MySQL，那末就可以因而须要将函数改形成存储历程。
存储历程和函数的相干操纵
在对贮存历程和函数操纵时，须要起首确认用户是不是具有响应的权限。比方，竖立存储历程或许函数须要CREATE ROUTINE权限，修正或许删除存储历程或许函数须要ALTER ROUTINE权限，实行历程或许函数须要EXECUTE权限
竖立一个新的历程 film_in_stock,该历程用来搜检 film_id和store_id对应的inventory是不是满足请求，而且返回满足的inventory_id 以及满足请求的纪录数
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int) READS sql data begin select inventory_id from inventory where film_id =p_film_id and store_id=p_store_id and inventory_in_stock(inventory_id); SELECT found_rows() into p_film_count; end $$
平常在竖立历程和函数之前，都邑经由历程DELIMITE $$敕令将语句的完毕符从'；'修正成其他标记，这里运用‘$$’，如许在历程和函数中的';'就不会被MySql，诠释成语句的完毕而毛病。在存储历程或许函数竖立完成 经由历程‘DELIMITER;'敕令在将完毕符改回成';'
挪用历程
CALL film_in_stock(2,2,@a);
存储历程的优点在于处置惩罚逻辑都封装在数据库端，挪用者不须要相识中心的处置惩罚逻辑，一旦逻辑转变，只须要修正存储历程，对挪用者的顺序没有影响
删除存储历程或许函数
一次只能删除一个存储历程或许函数，删除须要ALTER ROUTINE权限
drop procedure film_in_stock;
检察存储历程或许函数状况
show procedure status like 'film_in_stock';
检察存储历程的函数定义
show create procedure film_in_stock
变量运用
存储历程和函数中可以运用变量，在MySql 5.1版本中，变量不辨别大小写
变量的定义
经由历程DECLARE可以定义一个局部变量，该变量的作用局限只能在BEGIN...END中，可以用在嵌套块中
定义一个DATE范例的变量
DECLARE last_month_start date;
变量赋值 可以直接赋值，或许经由历程查询赋值。直接赋值运用set，可以赋常量或许赋表达式
set var_name=expr [,var_name=expr]... set last_month_start=date_sub(current_date(),interval month); select col_name[,...] into var_name[,...] table_expr;
定义前提和处置惩罚
delimiter $$ create procedure actor_insert() begin declare continue handler for sqlstate '23000' set @x2=1; set @x=1; insert into actor(actor_id,first_name,last_name) values(201,'test','201'); set @x=2; insert into actor(actor_id,first_name,last_name) values(1,'test','1'); set @x=3; end ; $$
挪用处置惩罚函数时碰到主键重的毛病会依据定义的处置惩罚体式格局去处置惩罚，由于定义的是CONTINUE 会继承实行下面的语句
还支撑EXIT示意停止
光标运用
声明光标
declare cursor_name cursor for select_statement
open光标
open cursor_name
fetch光标
fetch cursor_name into var_name[,var_name]...
close光标
close cursor_name
delimiter $$
create procedure payment_stat()
begin
declare i_staff_id int;
declare d_amount decimal(5,2);
declare cur_payment cursor for select staff_id,amount from payment;
declare exit handler for not found close cur_payment;
set @x1=0;
set @x2=0;
open cur_payment;
REPEAT
FETCH cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
close cur_payment;
end;
$$
变量，前提，处置惩罚顺序，光标都是经由历程DECLARE定义的，她们之间是有先后递次请求的。变量和前提必需在最前面声明，然后才是光标的声明，末了才可所以处置惩罚顺序的声明
掌握语句
case
when i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
loop 和leave连系
create procedure actor_insert()
begin
set @x=0;
ins:loop
set @x=@x+1;
if @x=100 then
leave ins;
end if;
insert into actor(first_name,last_name) values('Test','201');
end loop ins;
end;
$$
inerate 语句作用是跳过当前轮回的剩下语句，直接进入下一轮轮回
create procedure actor_insert()
begin
set @x=0;
ins:loop
set @x=@x+1;
if @x=10 then
leave ins;
elseif mod(@x,2)=0 then
iterate ins;
end if;
insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x);
end loop ins;
end;
$$
repeat 语句 有前提的轮回掌握语句，当满足前提的时刻退出轮回
repeat
fetch cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
while
delimiter $$
create procedure loop_demo()
begin
set @x=1,@x1=1;
repeat
set @x=@x+1；
until @x>0 end repeat;
while @x<1 do
set @x=@x+1;
end while;
end;
$$
//竖立事宜调理器
CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECOND
DO
INSERT INTO dept(deptno,deptname)
VALUES(3,'3');
//检察当地调理器状况
show variables like '%scheduler%';
//翻开调理器
set global event_scheduler=1;
//检察背景历程
show processlist;
//竖立一个新的定时器 定时清空表，防备表变大，这类触发器非常适宜去按期清空暂时表或许日记表
create event trunc_test
on schedule every 1 minute
do truncate table test;
禁用调理器或许删除
alter event test_event_1 disable;
drop event test_event_1;
| 事宜调理器 | 申明 |
|---|---|
| 上风 | MySQL事宜调理器布置在数据库内部由DBA或专人一致庇护和治理，防备将一些数据库相干的定时使命布置到操纵体系层，削减操纵体系治理员发生误操纵的风险，对后续的治理和庇护也非常有益。比方，后续举行数据库迁徙时无需再迁徙操纵体系层的定时使命，数据库迁徙自身已包括了调理事宜的迁徙 |
| 运用场景 | 事宜调理器实用于按期网络统计信息，按期清算历史数据，按期数据库搜检（比方，自动监控和复兴slave失利历程） |
| 注重事项 | 在忙碌且请求机能的数据库效劳器上要郑重布置和启用调理去；过于庞杂的处置惩罚更适宜顺序完成；开启和封闭事宜调理器须要具有超等用户权限 |
事宜掌握和锁定语句
MySQL支撑对MyISAM和MEMORY存储引擎的表举行表级锁定，对InnoDB存储引擎的表举行行集锁定。默许状况下是自动取得。
LOCK TABLES 可以用于锁定当前线程取得的表，假如表被其他线程锁定，当前线程一向守候到可以猎取现有锁定为止。
UNLOCK TABLES 可以开释当前线程取得的任何锁定，当前线程实行另一个LOCK TABLES时，或当与效劳器的衔接被封闭时，一切由当前线程锁定的表被隐式地解锁。
| session_1 | session_2 |
|---|---|
| 猎取表film_text 的read锁定 lock table fim_text read | |
| 当前seesion可以查询纪录 select * from fim_text | 其他seesion也可以查询select * from fim_text |
| 其他session更新锁定表会守候锁 update fim_text …. 处于守候状况 | |
| 开释锁 unlock tables | 守候 |
| sesion猎取锁，更新胜利 |
事宜掌握
mysql 经由历程set autocommit ,start transaction ,commit ,rollback等语句支撑当地事宜。默许状况下，mysql是自动提交(autocommit)的，假如须要明白的commit和rollback来提交和回滚事宜，那末就须要经由历程明白的事宜掌握敕令来最先事宜，这是和Oracle的事宜治理显著差别的处所。
start transaction 或 begin语句可以最先一项新的事宜
commit 和rollback用来提交或许回滚事宜。
chain和release字句离别用来定义在事宜提交或回滚今后的操纵，chain会马上启动一个新事宜，而且和适才的事宜具有雷同的断绝级别，release则会断开和客户端的衔接。
set autocommit可以修正当前衔接的提交体式格局，假如设置了set autocommit=0，则设置今后的一切事宜读须要经由历程明白的敕令举行提交或许回滚。
假如只是对某些语句须要举行事宜掌握，则运用start transaction语句最先一个事宜比较轻易，如许事宜完毕今后可以自动回到自动提交的体式格局，假如愿望一切的事宜都不是自动提交的，那末经由历程修正autocommit来掌握事宜比较轻易。
start transation和commit and chain
| session_1 | session_2 |
|---|---|
| 从表actor查询 select * from actor 没有数据 | 从表actor查询 select * from actor 没有数据 |
| 启动一个事宜start transaction; insert into actor… | |
| 查询actor select * from actor 依然为空 | |
| commit | |
| 再次查询 就有 了 select * from actor… |
| session_1 | session_2 |
|---|---|
| 自动提交 inset into actor… | |
| 可以从表中查询到刚插进去 select *from actor | |
| 从新用star transaction 启动一个事宜 start transaction; insert into actor…;用commit and chain敕令提交 commit and chain;此时启动一个新的事宜， insert into… | |
| 刚插进去的数据查不到 select * from actor… | |
| 用commit 提交 commit; | |
| 刚插进去的可以查询到 |
假如在锁表的时期，用start transaction 敕令最先一个新的事宜，会形成一个unlock tables被实行
| session_1 | session_2 |
|---|---|
| 查询一个actor_id=201,结果为空 select * from actor where actor_id=201; | 从内外查询 同理结果为空 |
| 对表加写锁 lock table actor write | |
| 对表actor 的读操纵被壅塞 select * from actor where actor_id=201 | |
| 插进去数据 insert into actor(actor_id,..)values(201,..) | 守候 |
| 回滚纪录 rollback | 守候 |
| 用start transaction敕令从新启动一个事宜 | 守候 |
| 最先一个事宜，表锁被开释，可以查询；select …where actor_id=201 | |
| 查到数据 |
因而，在同一个事宜中，最好不运用差别的存储引擎，不然rollback时须要对非事宜范例的表举行迥殊的处置惩罚。由于，commit ,rollback 只能对事宜范例的表举行提交和回滚。
平常状况下，只对提交的事宜纪录到二进制的日记中，然则假如一个事宜中包括非事宜范例的表，那末回滚操纵也会被纪录到二进制日记中，以确保非事宜范例表的更新可以被复制到从数据库(slave)中。
在事宜中可以经由历程定义savepoint，指定回滚事宜部份，然则不能指定提交事宜的一个部份。关于庞杂的运用，可以定义多个差别的savepoint,满足差别的前提时，回滚差别的savepoint,须要注重的是，假如定义了雷同名字的savepoint，则背面定义的savepoint会掩盖之前的定义。关于不须要运用的savepoint，可以经由历程release savepoint敕令删除。
事宜回滚
| session_1 | session_2 |
|---|---|
| 从表中查询first_name=’Simon’的纪录为空 select * from….where first_name=’simon’ | 从表中查询first_name=’Simon’的纪录为空 select * from….where first_name=’simon’ |
| 启动一个事宜插进去 一条数据 start transaction; inset ….values(‘simon’…) | |
| 查询到刚插进去的数据 select * from…where first_name=’simon’ | 没法从actor查到session1刚插进去的纪录 select * from … where first_name=’simon’ |
| 有数据 | 无数据 |
| 定义一个 savepoint，称号为test savepoint test; insert into …values(…,tom) | |
| 查询到两条数据select *… | 依然查询不到数据 select * … |
| 回滚到适才定义的savepoint rollback to savepoint test | |
| 从表actor查询到一条数据 第二天被回滚 select * from …. | 依然查询不到数据 |
| 提交commit | |
| 查询的到 | 查询的到 |
散布式事宜的运用
MySql从5.0.3最先支撑散布式事宜，当前散布式事宜只支撑InnoDb存储引擎。一个散布式事宜会触及多个行为，这些行为自身是事宜性。一切行为都必需一同胜利完成，或许一同被回滚
在mysql中，运用散布式事宜的运用顺序触及一个或多个资本治理器和一个事宜治理器。
资本治理器(rm)用于供应通向事宜资本的门路。数据库效劳器是一种资本治理器，该治理器必需可以提交或回滚由rm治理的事宜。如：多台mysql数据库作为多台资本治理器或许几台mysql效劳器和几台oracle效劳器作为资本治理器。
事宜治理器（tm）用于谐和作为一个散布式事宜一部份的事宜。tm与治理每一个事宜的rm s举行通讯。在散布式事宜中，各个单个事宜均是散布式事宜的“分支事宜”。散布式事宜和各个分支经由历程一种定名要领举行标示。
实行散布式的历程分为两阶段提交，发生时候有散布式事宜的各个分支须要举行的行为已被实行今后
在第一阶段，一切分支呗准备好，即它们被TM示知要准备提交。平常，这意味着用于治理分支的每一个RM会纪录关于被稳固保存的分支的行为。分支指导是不是它们可以这么做，这些结果被用于第二阶段
在第二阶段，TM示知Rms是不是要提交或回滚，假如在准备分支时，一切的分支指导它们将可以提交，则一切的分支被示知要提交。假如在准备时，有任何分支指导它将不能提交，则一切分支呗示知回滚。
语法
xa start xid 用于启动一个带给定xid值的xa事宜。每一个xa事宜必需有一个唯一的xid值，因而该值当前不能被其他xa事宜运用
xa grtid[,beual[,formatId]] grtid 是一个散布式事宜比较符，雷同的散布式事宜应当运用雷同的gtrid，如许可以明白晓得XA事宜属于哪一个散布式事宜
bequal 是一个分支限定符，默许值是空值。关于一个散布式事宜中的每一个分支事宜，bqual指是唯一的
formatId是一个数值，要用来标志 由gtrid和bqual值运用的花样，默许是1
xa end xid[suspend [for migrate]] xa prepare xid
使事宜进入prepare 状况，也就是两阶段提交的第一个提交阶段
xa commit xid[one phase] xa rollback xid
用来提交和回滚细致的分支事宜
xa recover 返回当前数据库中处于PREPARE状况的分支事宜的细致信息
散布式的症结在于怎样确保散布式事宜的完整性，以及在某个分支涌现题目时的毛病处理，xa的相干敕令就是供应给运用怎样在多个自力的数据库之间举行散布式事宜的治理，包括启动一个分支事宜、使事宜进入准备阶段以及事宜的现实提交回滚操纵等，
例子
| session_1 in DB1 | session_2 in DB2 |
|---|---|
| 在数据库DB1 启动一个散布式的一个分支事宜,xid 的gtrid 为 “test”,bqual为”db1”: xa start ‘test’,’db1’;分支事宜插进去一个数据 insert into actor(…)values(…) 对分支事宜1举行第一阶段提交，进入prepare状况 ：xa end ‘test’,’db1’; xa prepare ‘test’,’db1’ | 在数据库DB2 启动散布式事宜 “test”的别的一个分支事宜，xid的gtrid为”test”.bqual为”db2”; xa start ‘test’,’db2’: 分支事宜2在表film_actor 更新数据 末了 xa end ‘test’,’db2’ xa prepare ‘test’,’db2’ |
| xa recover 检察当前分支事宜状况 | xa recover 检察当前分支事宜状况 |
| 两个事宜进入准备提交状况，假如之前碰到任何毛病，都应当回滚到一切分支，以确保事宜的准确 | |
| xa commit ‘test’,’db1’ | xa commit ‘test’,’db2’ |
假如分支事宜在实行到prepare状况是，数据库非常，且不能再支撑启动，须要备份和binlog来复兴数据，
SQL Mode
在MySql中，SQLMode常常运用来处理下面几类题目
经由历程设置SQL Mode，可以完成差别严厉水平的数据校验，有用的保证数据准确性。
经由历程设置SQL Mode，为ANSI形式，来保证大多数SQL相符规范的Sql语法，如许运用在差别数据库之间举行迁徙时，则不须要对营业SQL举行较大的修正
在差别数据库之间举行数据迁徙之前，经由历程设置SQL Mode可以使MySQL上的数据更轻易地迁徙到目的数据库中
检察 SQL Mode敕令
select @@sql_mode
插进去一个出国现实定义值的大小varchar(10)
insert into value('123400000000000000000000000000000');
//检察warning内容
show warnings
select * from t 这里对插进去的数据举行截取前10位
设置SQL Mode为 严厉形式
set session sql_mode='STRICT_TRANS_TABLES'
再次插进去insert into value('123400000000000000000000000000000'); 直接给出ERROR，而不是warning
SQL Mode罕见功用
校验日期是合法性
set seesion sql_mode='ANSI'
insert into t values('2007-04-31')
结果是 插进去值变成'0000-00-00 00:00:00' 而且体系给出warning 而在TRADITIONAL形式下，直接提醒日期不法，谢绝插进去，同时Mode（x,0）也会报错
qidon NO_BACKSLASH_ESCAPES形式，使反斜杠成为平常字符，在导入数据时，假如数据含有反斜杠字符,你们启动NO_BACKSLASH_ESCAPES形式，保证数据的准确性
启动PIPES_AS_CONCAT。将||视为字符串衔接符，在Oracle等数据库中，||被视为字符串的衔接操纵符，所以在其他数据库中含有||操纵符的sql在MySql将没法实行，为相识决这个题目mysql供应了PIPES_AS_CONCAT形式、
MySql分区
MySql从5.1版本最先支撑分区，分区是指依据肯定的划定规矩，数据库把一个表分解成多个更小的，更轻易治理的部份。就接见数据库的运用而言，逻辑上只需一个表或一个索引，然则现实上这个表可以由数10个物理分区对象构成，每一个分区都是一个自力的对象，可以单独处置惩罚，可以作为表的一部份举行处置惩罚。分区对运用而言是完整通明的，不影响运用的营业逻辑
长处
和单个磁盘或许文件体系分区比拟，可以存储更多数据
优化查询。在where子句中包括分区前提，可以只扫描必要的一个或多个分区来进步查询效力；同时在触及SUM（）和COUNT（）这类聚合函数的查询时，可以轻易的在每一个分区上并行处置惩罚，终究只须要汇总一切分区的结果
关于已由期或许不须要保存的数据，可以经由历程删除与这些数据有关的分区来疾速删除数据
跨多个磁盘来疏散数据查询，以取得更大的查询吞吐量
分区有利于治理非常大的表，它采纳 分而治之的逻辑，分区引入分区键的观点，分区键用于依据某个区间键，特定值列表或许HASH函数实行数据的群集，让数据依据划定规矩散布在差别的分区中，让一个大对象变成一些小对象
show VARIABLES like '%partition%' 检察是不是支撑分区
Mysql支撑大部份存储引发如MyISAM，INNODb,Memory等存储引擎，竖立分区，在5.1版本中，同一个分区表的所以分区必需运用同一个存储引擎；在同要给表上，不能对一个分区运用MyISAM引擎和Innodb引擎，然则在同一个MySQL效劳器效劳器上，以至同一个数据库中，关于差别的分区表运用差别的存储引擎
分区范例
range分区：基于一个给定一连区间局限，把数据分配到差别的分区。
LIST分区：相似RANGE分区，辨别在LIST分区是基于罗列出的值列表分区，RANGE是基于给定的一连区间局限分区
HASH分区：基于给定的分区个数，把数据分配到差别的分区
KEY分区：相似HASH分区
在5.1版本中，RANGE分区，LIST分区，HASH分区请求分区键都是int范例，key分区，可以运用其他范例（除了BLOB和TEXT类除外）作为分区键
分区表的主键/唯一键必需包括分区键，不能运用主键/唯一键，要么分区表的主键/唯一键都必需包括分区键，分区的名字是不辨别大小写的
range分区
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(store_id)(
PARTITION p0 VALUES less than (10),
PARTITION p1 VALUES less than (20),
PARTITION p2 VALUES less than (30)
);
//上述的分区计划将storid,1-9分到p0区，10-19分到p1区，等假如插进去大于30，会涌现毛病，由于没有划定规矩庇护大于30的
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以
//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);
增添分区
alter table emp add partition(partition p3 values less than maxvalue);
maxvalue示意最大的可以的整数值
mysql 支撑在values less than 语句中到场表达式
比方以日期作为分区
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(year(separated ))(
PARTITION p0 VALUES less than (1995),
PARTITION p1 VALUES less than (2000),
PARTITION p2 VALUES less than (2005)
);
MySQl 5.5改进了range分区给你，经由历程支撑非整数分区，竖立日期分区就不须要经由历程函数举行转换
partition by range(separated )(
PARTITION p0 VALUES less than ('1996-01-01'),
PARTITION p1 VALUES less than ('2001-01-01'),
PARTITION p2 VALUES less than ('2006-01-01')
);
rang分区的功用实用一下状况
- 当须要删除逾期的数据，只须要简朴的alter table emp drop partition p0 来删除p0 分区中的数据。关于具有上百万条纪录的表来讲，删除分区要比运转一个delete语句有用的多
- 常常运转包括分区键的查询，MyySql可以很快地肯定只需某一个或许某些分区须要扫描，由于其他分区不可以包括有相符该where字句的任何纪录。比方检索id大于25的纪录数，MySql只须要扫描p2分区即可
explain partition select count(1) from emp where store_id>=25
List分区
List分区是竖立离散的值列表通知数据库特定的值属于哪一个分区，LIST分区在许多方面相似于RANGE分区，辨别在于LIST分区是从属于一个罗列列表，RANGE分区是从属于一个一连区间值的鸠合
create table expenses（ expense_date date not null, category int, amount decimal(10,3) ）partition by list (category)( partition p0 values in(3,5), partition p1 values in(1,10), partition p2 values in(4,9), partition p3 values in(2), partition p4 values in(6) )
LIST分区不存在相似于VALUES LESS THAN MAXVALUE如许的值 在MYSQL5.5支撑非整数分
Columns分区
Column分区是5.5引入的分区范例，引入Columns分区处理了MySQL5.5版本之前RANGE和LIST分区值值支撑整数分区，从而致使须要分外的函数盘算取得整数值或许经由历程分外的转换表来转换为整数在分区的题目
Column分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支撑整数，日期时候，字符串三大数据范例
关于Range分区和List分区，Colums分区的亮点除了支撑数据范例增添以外，还支撑多列分区
create table rc3(a int,b int) parition by range columns(a,b)( parition p01 values less than(0,10), parition p01 values less than(10,10), parition p01 values less than(10,20), parition p01 values less than(maxvalue,maxvalue) )
Hash分区
hash分区主如果分数热门读，确保数据在预先肯定个数的分区中只管均匀散布。对一个表实行HASH分区时，Mysql会对分区间运用一个散列函数，以肯定数在n个分区中的谁人分区中。
mysql支撑两种hash分区，通例的hash分区和线性hash分区，通例的hash运用取模算法，线性hash分区运用的一个线性的2的幂的运算轨则
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by hash(store_id)partitions 4;
这里竖立了一个通例的hash 运用 partition by hash（expr）个中expr是某列值或一个整数值的表达式返回值。 partition num 对分区范例，分区键，分区个数举行定义，上述基于store_id列hash分区，表被分为4个分区
我们可以盘算出它被保存在哪一个分区中假定，假定纪录的分区编号为N，那末N=MOD（expr，num），比方emp表中有4个分区，插进去一个store_id为234的 mod（234,4）=2,倍保存在第二个分区
表达式‘expr’可所以MySQL中有用的任何函数或许是其他表达式，只需它们返回一个既非常数也非随机数的整数。每当插进去更新删除一行数据，这个表达式就须要盘算一次，意味着非常庞杂的表达式可以会引发机能题目
通例的HASH分区经由历程去模的体式格局去讲数据均匀散布在每一个分区上，让每一个分区治理的数据都削减，进步了查询的效力；但是当我们须要增添分区或许兼并分区的时刻，题目就涌现了，假定本来是5个通例hash分区，如今须要新增一个通例hash分区，本来的去模算法是mod（expr,5)依据余数0-4散布在5个分区上，如今新增一个分区，取模算法变成mod（expr，6）依据余数0-5分区在6个分区中，本来5个分区的数据大部份都须要经由历程从新盘算从新分区，通例的hash在分区治理上带来的价值太大了。不适宜天真更改分区的需求，Mysql供应了线性hash分区
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by linear hash(store_id)partitions 4; 盘算编号为n的分区 起首找到下一个大于即是num的2的幂，这个值设为v，v的盘算公司 v=power(2,ceiling(log(2,num))) =power(2,ceiling(log(2,4))) =power(2,ceiling(2)) =power(2,2) =4 其次设置n=f（column_list)&(v-1),如今盘算store_id=234对应的n值 n=f(column_list)&(4-1) =234&(4-1) =2 当n》=num设置n=n&（v-1) 关于store_id=234由于n=2《4，所以直接判断这个会被寄存到第二分区
线性hash分区的长处在于，在分区庇护上（包括增添，删除，兼并，拆分分区）时，Mysql可以处置惩罚得越发敏捷；瑕玷是对照通例hash分区的时刻，线性hash散布不太平衡
key分区
依据key分区举行分区非常相似于依据hash举行分区，只不过hash分区许可运用用户自定义的表达式，而KEY分区不可运用用户自定义的表达式，须要运用MySQl效劳器供应的hash函数；同时hash分区只支撑整数分区，而key分区支撑除了blob
or text范例外其他范例的列作为分区键
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by key (job)partitions 4; 假如不晓得分区键，默许为主键，没有主键会挑选非空唯一键作为分区键
子分区
子分区是分区表对每一个分区的再次支解。又被称为复合分区，mysql5.1最先支撑对已经由历程range或许list分区了的表在举行子分区
create table ts(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased))subpartitions 2(partition p0 values less than (1900),partition p0 values less than (2000),partition p0 values less than (maxvalue))
在分区中的null值
在mysql不制止分区键上运用null，分区键多是一个字段或许一个用户定义的表达式，平常状况下，mysql的分区把null当作零值，或许一个最小值处置惩罚
分区治理
删除分区 alter table emp_date drop partition p2; 增添分区 alter table emp_date add partition(partition p5 value less than(2025)) 拆分p3分区，分为p2和p3分区 分区 alter table emp_date reorganize partition p3 into(partition p2 values less than(2005),parition p3 values less than (2015)); 兼并分区 alter table emp_date reogranize partition p1,p2,p3 into(partition p1 values less than (2015)) 从新定义list分区时，只能从新定义相邻的分区，不能跳过list分区举行从新定义
hash&key治理
不能以range和list分区表删除分区的体式格局，而是跳过alter table coalesce partition 来兼并或分区 以本来4个分区为例 alter table emp coalesce partition 2 //削减分区到2个 alter table emp coalesc partition 8 //不能增添分区 要增添分区 alter table emp add partition partitions 8;
相干引荐：
关于mysql 基本知识的总结
PHP 和 MySQL 基本教程（一）
PHP 和 MySQL 基本教程（二）
PHP 和 MySQL 基本教程（三）
PHP 和 MySQL 基本教程（四）
mysql手册教程：http://www.ki4.cn/course/37.html
mysql视频教程：http://www.ki4.cn/course/list/51.html
以上就是mysql基本知识（mysql新手教程）的细致内容，更多请关注ki4网别的相干文章！
