mysql相关-CSDN博客

本文链接：https://blog.csdn.net/wangxulei123/article/details/105889990

本文详细介绍了MySQL的基础知识，包括查询、分组、连接、事务处理、视图、变量、函数、流程控制结构和存储引擎。此外，还讨论了索引、锁机制、主从复制以及SQL性能分析，为MySQL的使用和优化提供了全面指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql基础

查询常量值

select 100;

查询表达式

slect 100%98

查询函数

select VERSION();

别名

select 100%98 as 结果;

select last_name 姓 ,first_name 名 from employees;

如果别名中有空格或者特殊字符或者关键字，可以给别名添加双引号进行规避。

去重

select distinct name form user; 使用distinct关键字进行去重设置

+号的作用

mysql中的 + 号仅仅为运算符

拼接字符串

select CONCAT(‘a’,‘b’,‘c’) as 结果；使用CONCAT()函数进行字符串拼接；

显示表结构

desc 表名；

null和任何数据进行拼接，那么结果都会成为null

IFNULL(commission_pct,0) IFNULL() 函数，如果第一个参数为null，那么返回第二个参数的值；

条件查询

select 查询列表 from 表名 where 筛选条件；

条件分类：

按照条件表达式筛选

条件运算符： > < = <> >= <=

按逻辑表达式筛选：&& || ！ and or not

模糊查询： like ,between and ,in , is null，is not null

实例：

select * from user where name like ‘%a%’

like 一般和通配符搭配使用：

通配符：% ：任意多个字符，包含0个字符

_ 任意单个字符

select name from User where name like ‘__e_a%’

将通配符当成普通字符：
```
select name from User where name like '_\_'
select name from User where name like '_$_' ESCAPE '$'
```
通过转义字符进行定义。

通过ESCAPE 进行转义字符定义。
```
select name fomr User where id between 10 an 20  包含临界值
```
```
select name , id from User where id in('1','2','3%');
```
```
select name , pct from user where pct = null;	
```
安全等于：
```
selec name pct from user where pct <=> null;
```
is nul:仅仅可以判断null值 <=> 既可以判断null值，又可以判断普通的数值，可读性较低。

排序查询

select 查询列表 from 表 where 筛选条件 order by排序列表 asc|desc
```
select * from User order by id desc;  从高到底
```
```
select * from User where id >= 90 order by date ASC;
```
```
select * ,salary*12*(1+IFNULL(pct,0)) 年薪 from User oreder by 年薪 desc; 
```
按照姓名的长度进行排序，LENGTH() 获取字符长度
```
select LENGTH(name) ,name  from user order by length(name) desc;
```
先按id排序，再按照编号排序；按多个字段排序；
```
select * from User order by id ASC,emp_id desc;
```
常见函数

函数分类：单行函数： concat，length，ifnull

分组函数：做统计使用，成为统计函数，聚合函数，组合函数。

length : select length([STR]) ; 用于获取参数值得字节个数。

concat: 拼接字符串。select concat(ex1,ex2,ex3…) from table;

upper,lower; select upper(‘john’)

substr ;截取字符串；selct substr(‘李莫愁爱上了陆展元’,6) out_put;

mysql索引从1开始

substr 如果有两个参数，则截取从指定索引处指定字符长度的字符。

instr： select instr(‘杨不悔爱上了殷六侠’,‘殷八侠’) as out_put from user;

返回子串在长串中的第一次出现的索引；如果找不到则返回0；

trim ：去空格； select length(trim(’ 张翠山 ')) as out_put;

select trim( ‘a’ from ‘aaaaaaaaaaaa张aaa翠山aaaaaaaa’);去掉前后的空格

lpad； select lpad(‘殷素素’,‘10’,’*’) as out_put ；用指定的字符实现左填充指定长度。

rpad；实现右填充。

replace；替换 select replace(‘张无忌爱上了周芷若’，‘周芷若’，‘赵敏’) as out_put;

数学函数

round 四舍五入； select round(1.65);

select round(1.567,2); 两个参数时，第二个参数表示小数保留位。

ceil 向上取整 select ceil(1.52);

floor ;向下取整；返回<=该参数的最大整数。 select floor(-9.99)

truncate；截断 select truncate(1.65,1);

mod 取余 select mod(10,3);

日期函数

now 返回当前系统日期+时间

select now();

curdate 返回当前系统日期，不包含时间； select curdate();

curtime() 返回当前时间，不包含日期； select curtime();

可以获取指定的部分，年，月，日，时，分，秒

select year(now()) 年 select mouth(now()) 月 select day(now())日

str_to_date() ; 将日期格式的字符串转换成指定格式的日期

str_to_date('9-13-'1999,%y-%m-%d)

date_format: 将日期转换成字符

select date_format(now(),’%y年%m月%d日’) as out_put

其他函数

select version()l

select database();

select user();

流程控制函数

if 函数 if else的效果

select if(10>5,‘大’,‘小’);

case函数类似于switch case效果； case 要判断的字段或表达式

case 要判断的值或者语句

when 常亮1 then 要显示的值或语句；

when 常量 2 then 小显示的值或语句2

。。。

else 要显示的值n或语句n；

end

select salay ,id,
case id
when 10 then salay*1.0
when 20 then salay*20
when 30 then salay*30
else aslay
end as 新工资 
from  user;

case 函数使用2 类似多重if
case 
when 条件1 then 执行1
when 条件2 then 执行2
...
else 要显示的值n
end

select salary ,
case 
when salary > 20000 then 'A'
when salary > 15000 then 'B'
when salary > 10000 then 'c'
else 'D'
end

分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

分类：

sum 求和，avg 平均值，max 最大值，min最小值， count 计算个数···

select sum(salary) from emp;
select avg(salary) from emp;
select min(salary) from emp;
select max(salary) from emp;
select count(salary) from emp;

参数支持类型：

sum和avg适用于数字类型 max，min ，cout适用于任何类型

所有的分组函数都忽略null值；

可以和distinct搭配使用进行去重运算；

select sum(distinct salry) from emp;

count函数详解：

select count(salary) from emp;
select count(*) from emp;
select count(1) from emp;

分组查询

select 分组函数，列(要求出现在group by后面) from 表 [筛选条件] group by 分组的列表 order by 子句；

select max(salary),job_id from emp group by job_id;
select count(*),loca_id from dep group by loca_id;
select avg(salary),dep_id from emp where email like '%a%' group by dep_id 
select max(salary),manage_id from emp where commisson_pct is not null group by manage_id;

select count(*),dep_id from emp group by dep_id having count(*)>2;
select max(salary),job_id from emp where commis_pct is not null group by job_id having MAX(salary) > 12000;
select min(salary),manager_id from emp where manager_id > 102 group by manager_id having min(salary)>5000

按表达式或函数分组：
select count(*) from emp group by length(name);
select count(*) from emp group by length(name) group by lenght(name) having count(*)>5;

按多个字段分组：
select avg(salary),dep_id,job_id from emp group by dep_id,job_id;

添加排序：
select avg(salary),dep_id,job_id from emp group by job_id,dep_id order by avg(salary) desc;

连接查询

多表查询。

内连接：等值连接，非等值连接，自连接

外连接：左外了解，右外连接，全外连接

交叉连接

select name,boyName from boys,beauty where beauty.boyfriendid = boys.id;

select name,job_id,job_title from employees,jobs where emp.jpb_id = jobs.job_id;
select e.name,j.job_id,j.job_title from employees as e,jobs as j where  e.job_id = j.job_id;
如果为表起了别名，则查询的字段不能使用原来的表明进行限定。
select name,dep_name from emp e ,dep d where e.dep_id = d.dep_id and e.comm_pct is not null;

三表连接：

select name,dep_name,city from emp e,dep d, local l where e.dep_id =d.dep_id and d.local_id =l.local_id and city like 's%';

多表等值连接的结果为多表的交集部分，n表连接，至少需要n-1个连接条件

一般需要为表起别名，可以搭配所有子句使用，比如排序，分组，筛选；

自连接：自己连接自己

select e.emp_i,name,m.emp_id,m.name from emp as e ,emp as m where e.emp_id = m.emp_id;

sql99语法

内联、外联、交叉连接

select 查询列表 from 表1 连接类型 join 表2 on 连接条件 [where 筛选条件] [group by 分组]{having 筛选条件} [order by 排序列表]

内联： inner 左外 left[outer] 右外 right [outer] 全外 full [outer]

全外：full [couter]

交叉连接：cross

一、内连接：

select name,dep_name from emp inner join dep on e.dep_id = d.dep_id;

select name,job_title from emp e inner join jobs j on e.job_id = j.job_id where e.name like '%e%';

二、子查询：

出现在其他语句中的select语句，称为子查询或内查询；

分类：按子查询出现的位置；select 后面；from后面；where或having后面；exists后面(相关子查询)；

按结果集的行列数不同：标量子查询(结果集只有一行一列)

列子查询(结果集只有一列多行)；

行子查询(结果集有一行多列)

表子查询(结果集一般为多行多列)

where 或having后面

标量子查询(单行子查询) 一般搭配> < >= <= = <>
列子查询(多行子查询) 一般搭配 in . any/some 、all
行子查询(多列多行)

select salary from emp where name ='A'
select * from emp where salary > (select salary from emp where name ='A');

select job_id from emp where emp_id = 141
select salary from emp where emp_id = 143
select name ,job_id ,salary from emp where job_id =(
	select job_id from emp where emp_id =141
) AND (select salary from emp where emp_id =143);

select name,job_id, salary from emp where salary =(select min(salary) from emp);

列子查询：

any 和子查询返回的某一个值比较

all 和自查询返回的所有值比较；

select name from emp where dep_id in(select  distinct(dep_id from dep where local_id in(1400,1700)))

exists(); 用来判断查询是否有结果；返回为boolean类型，1和0

select exists(select emp_id from emp);

分页查询：

select 查询列表 from 表【join type】 join 表2 on 连接条件 where 筛选条件 group by分组字段

having 分组后的筛选 order by 顺讯的字段 limit offset,size;

offset 要显示条目的起始索引(起始索引从0开始) size 要显示的条目个数

select * from emp limit 0,5;
select * from emp limit 10,15;
select * from emp where comm_pct is not null and order by salary desc limit 10;

联合查询： union ，将多条查询语句的结果合并成一个结果。

select * from emp where email like '%a%' UNION select * from emp where dep_id>90;

查询语句1 union 查询语句2 union …

特点：

1.要求多条查询语句的查询列数一样。

2.要求多条语句的查询列类型和顺序最好一致。

DML语言

数据操作语言：

插入：insert

修改：update

删除： delete

一、插入

insert into 表名(列明，…) values (值1，…);

insert into beauty(id,name,sex,photo) values(13,'送','男',null);

insert into beauty values(1,'a','nv',null);

insert into beauty  set id=19,name='liu',sex='nv';

二、修改

1.修改单表记录

update 表名 set 列= 新值，列2=新值。。。where 筛选条件

update user set phone = '1232132132' where name like 'a';
update user set name='张飞',usercp = 10 where id =2;

2.修改多表记录，级联更新

update 表1 别名，表2 别名

set lie=值，…

where 连接条件

and 筛选条件

update 表1 别名

inner|left|right join 表2 别名

on 连接条件

set 列 = 值,…

where 筛选条件；

update boys bo inner join beauty b on bo.id =b.boyfrient_id set b.phone ='114' where bo.boyName = '无极';
update boys bo right join beauty b on bo.id = b.boyfrient_id set b.boyfrient_id =2 where bo.id is null;

三、删除语句

delet from 表名 where 筛选条件

单表删除：

delete from beauty where phone like '%9';

多表删除：

delete from  beauty where boyfriend_id = (select id from boys where name ='张无忌');
delet b from beauty b inner join boys bo on b.boyfriend_id =bo.id where bo.boyName ='张无忌';
delete b,bo from beauty b inner join boys bo on b.boyfriend_id =bo.id where bo.boyName = 'cicici';

方式2：truncate

truncate table 表名；

truncate table boys; 清空数据

DDL 语言数据定义语言

库和表的管理

一、库的管理：

创建、修改、删除

create database if not exists books; 
alter database books  character set  gbk;
drop database if exists books;

二、表的管理

创建、修改、删除

create table 表名(
	列明 列的类型(长度) 约束，
    列明 列的类型(长度) 约束，
    列明 列的类型(长度) 约束，
    ...
    列明 列的类型(长度) 约束
)

alter table 表名 change column 列明  新列明  新列类型;
alter table book MODIFY COLUMN pubdate TIMESTAMP;   修改列类型
ALTER TABLE author ADD  COLUMN annual DOUBLE;  添加列
ALTER TABLE author RENAME TO book_author; 修改表名

ALTER TABLE author DROP COLUMN annual;  删除列

DROP TABLE IF EXISTS book_author;  删除表

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名；

表的复制：

1. 仅复制表结构
CREATE TABLE copy LIKE author;
2. 复制表结构与数据
CREATE TABLE copy2 SELECT * FROM author;
3. 复制表部分结构
CREARTE TABLE copy4 SELECT id,au_name from author where 1 =2 ;

常见的数据类型：

数值型： 整型、小数：定点数，浮点数；
字符型： 较短为本 char  varchar；较长文本 text blob较长的二进制数据
日期型：

整型：

tinyint   1个字节
smallint  2
mediumint 3
Int/integer 4
bigint    8
特点:
如果超出数值范围，会报警告。并且插入临界值； 
数值是有符号和无符号两类。可以通过unsigned关键字；
如果不设置长度，则会有默认长度。长度代表的是显示的最大宽度，如果不够则用0进行补位，并用fullsero组

小数：

float(M,D) 4字节
double(M,D) 8
定点性：
DEC(M,D)
DECIMAL(M,D)
特点：
M和D  D代表小数点保留位数  M代表小数和整数合起来的位数
MD可以省略，如果是decimal则M默认为10，D默认为0
如果是float和double则会根据数值的精度来决定精度；

字符型：

较短的文本：
char(M)    固定长度字符
carchar(M)  可变长度字符
较长的文本：
text 
blob(较大的二进制)
binary he varbinary 用户保存较短的二进制
ENUM()

create table tab_em(
	cl ENUM('a','b','c')
)

SET类型
create table tab_set(
	s1 SET('a','b','c','d')
)
可以插入多个数据
insert into tab_set values('a,b,c');

日期型：

date 4个字节  只保存日期
datetime 8  保存日期和时间 1000年---9999年
timestamp 4  保存日期和时间 和实际时区有关，更能反映实际的日期。
time 3  只保存时间
year 1    只能存年

insert into tab_date values(NOW(),NOW())

创建：create

修改：alter

删除： drop

常见约束

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性；
分类，六大约束：
     not  null；非空约束
     DEFAULT；默认约束，用于保证该字段有默认值
     PRIMARY KEY：主键约束，用于保证该字段值具有唯一性
     UNIQUE； 唯一约束，保证该字段额值具有唯一性，可以为空
     CHECK；检查约束 mysql不支持。
     FOREIGN KEY：外键约束，用于限制两个表的关系，用于保证该字段必须来自主表的关联列的值
create talbe 表名（
	字段名 字段类型  约束
）

create table stuinfo(
	id int PRIMARY KEY,
    stuName varchar NOT NULL,
    gender char CHECK(gender='男' or gender ='女')，
    seate int UNIQUE,
    age INT DEFAULT 18,
    majorid int FOREIGN KEY REFERENCES major(id)
)
create table major(
	id int primary key,
    name varchr(20)
)
desc stuinfo; 查看表结构
show index from stuinfo; 用来查看表索引

添加表级约束：

create table stuinfo(
	id int PRIMARY KEY,
    stuName varchar NOT NULL,
    gender char CHECK(gender='男' or gender ='女')，
    seate int UNIQUE,
    age INT DEFAULT 18,
    majorid int FOREIGN KEY REFERENCES major(id),
    
    CONSTRAINT pk PRIMARY  KEY(id),
    CONSTRAINT uq UNIQUE(seate),
    CONSTRAINT ck CHECK(gender ='男' or gender = '女'),
    CONSTRAINT fk_suinfo_major FOREIGN KEY(majorid) REFERENCES major(id)外键
)

修改表时添加约束

alter table stuinfo MODIFY  COLUMN stuname varchar(20)  not null;

alter stuinfo add UNIQUE/FOREIGN KEY(字段名)  PREFERENCES major(id);

修改表时删除约束：

alter table stuinfo DROP PRIMARY KEY;

ALTER TABLE stuinfo DROP INDEDX;

ALTER TABLE stuinfo DROP FOREIGN KEY  外键名；

标识列 AUTO_INCREMENT

又称为自增长列。

create table tab_identity(
	id int primary key AUTO_INCREMENT,
    name varchar(20)
)

事务

事务：一个或一组sql语句组成一个执行单元要么全部执行，要么全部不执行。

show engines；用来查看mysql支持的存储引擎；

事务 ACID属性

原子性，一致性，隔离性，持久性。

事务的创建：

隐式事务，事务没有明显的开启和结束的标记，不如insert 、update。delete语句；

显式事务：事务具有明显的开启和结束的标记。必须设置自动提交为禁用

set autocommit = 0;

start transaction;可选的

编写事务中的sql语句

语句1；

语句2；

commit; 提交事务

rollback; 回滚事务

set autocommit = 0;  关闭自动提交，并开启事务；
update account set balance = 500 where username = '张无忌'
update account set balance =1500 where username = '赵敏'
commit;
# roolback;

数据库隔离级别：

脏读：read uncommitted 出现脏读、欢度、不可重复度对于两个事务T1，T2，T1读取了已经被T2更新单还没有被提交的字段，之后弱T2回滚，T1读取的内容就是临时且无效的。

不可重复度: read committed ，对于两个事务T!，T@。T1读取了一个字段，然后T2更新了该字段，T1再次读取同一个字段，值就不同了。

幻读： repeatable read 对于两个事务T!，T2。T!从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后如果T!再次读取同一个表，就会多出几行。

串行化：serializable，性能十分低下，但是保证了数据的可靠性；

select @@tx_isolaton; 查看当前隔离级别；
set seesion transaction isolation level  xxxxx;切换隔离级别
set global seesion transaction isolation level  xxxxx; 设置系统隔离级别

savepoint: 节点名；设置保存点；

set autocommit = 0;
start transaction;
delete from account where id = 25;
savepoint a;
delete from account where id =28;
rollback to a; 回到保存点a

视图

含义：虚拟表，和普通表一样使用；并且在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果；

create view v1 
AS
select stuname,majorname from stuinfo s inner join major m on s.major  where s.majorid =1 ;

select * from v1 where stuname like '%aa'

一、创建视图：

create  view  视图名
AS
查询语句

二、视图的修改：

方式一：
create or replace view  视图名 
as
查询语句
方式二：
alter view 视图名
as 
查询语句

三、删除视图：

drop view 视图名，视图名...;

四、查看视图：

show create view 视图名;

五、视图的更新

更改视图的数据；
具备以下关键字的sql语句不允许更新：
1.分组函数、distinct，group ，by，having，union 或者union all；
2. 常量视图：
3. select 中包含子查询；
4. where 子句的子查询引用了from自居中的表
视图的更新利用update  insert  delete语句对视图进行更新；

变量

变量分为：系统变量和自定义变量

系统变量：全局变量，会话变量

自定义变量：用户变量、局部变量

一、系统变量

变量由系统提供，不是用户定义，属于服务器层面

语法：1.查看所有的系统变量；

SHOW variables;查看所有系统变量
show global|session  variables; 查看全局系统变量或者会话系统变量；

查看满足条件的部分系统变量

show global|session variables like '%char%';

查看指定的某个系统变量的值

select @@global|session.系统变量名;

为某个系统变量赋值

set global | session 系统变量名 = 值;
set @@global | session.系统变量名 = 值；

二、会话变量

仅仅针对于当前会话连接有效；

1.查看所有会话变量

 shwo session variables;

会话变量赋值

set seesion 会话变量名 = 会话变量值

三、自定义变量

针对当前会话有效，同于会话变量的作用域

声明并初始化

set  @用户变量名 = 值；
set  @用户变量名 := 值；
set  @用户变量名:= 值；

select 字段 into 变量名 from 表；

查看用户变量值

select @用户变量名；

四、局部变量

仅在定义它的begin end 中有效；

声明：

DECLARE 变量名 类型;
DECLARE 变量名 类型  DEFAULT 值;

赋值

set 局部变量名 = 值；

使用

 select 局部变量名;

五、存储过程和函数

存储过程和函数，类似于java中的方法；

含义：一组预先编译好的sql语句集合，理解成批处理语句；

创建语法

create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
...
end

参数列表包含三部分：参数模式  参数名 参数类型
IN  stuname varchar(20)

参数模式：
IN   ：该参数可以作为输入，也就是该参数需要调用方法传入值
OUT  ：该参数可以作为输出，也就是该参数可以作为返回值
INOUT ：该参数既可以作为输入又可以作为输出，也就是该参数既要传入值又可
以返回值；

如果存储过程体仅仅只有一句话，BEGIN  END可以省略
存储过程体重的每条sql语句结尾必须加分号。
存储过程的几位可以使用DELIMITER重新设置
DELIMITER 结束标记

调用语法

CALL 存储过程名(实参列表);

实例：

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	insert into admin(username,`password`) values('joh1','0000'),
	insert into admin(username,`password`) values('joh2','1000'),
	insert into admin(username,`password`) values('joh3','2000'),
	insert into admin(username,`password`) values('joh4','3000'),
	insert into admin(username,`password`) values('joh5','4000')
END$

CALL myp1()$

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	select bo.* rom boys bo right join bearuty b on bo.id =b.boyfriend_id
	where b.name = beautyName
END$

CALL myp2('刘亦菲')$

create procedure myp3(IN username varchar(20), IN password varchar(20))
BEGIN
	DECLARE result INT(20) DEFAULT '';声明并初始化变量
	
	select count(*) INTO result 赋值
	from admin
	where admin.username =username
	AND admin.password = PASSWORD;	
END $

select IF(result>0,'成功','失败');使用

CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	select bo.boyName  INTO boyName
	from boys bo inner join beauty b on bo.id = b.boyfriend_id
	where b.name =beautyName;
END $

SET @boyName;
CALL myp5('aa',@boyName);
select @boyName;

create procedure myp5(INOUT a INT,INOUT b INT)
BEGIN
	set a = a*2;
	set b = b * 2;
END;

set @m =10;
set @n = 20;
CALL myp5(@m,@n);

存储过程的删除：

DROP PROCEDURE 存储过程名;

查看存储过程信息

SHOW CREATE PROCEDURE 存储过程名；

函数

存储过程和函数的区别：

存储过程可以有0个返回，也可以有多个返回

函数只能有1个返回值；

存储过程适合批量插入、更新

函数适合做处理数据后返回一个结果；

函数创建：

CREATE FUNCTION 函数名(参数列表)  RETURNS 返回类型
BEGIN
函数体
END

参数列表包含两部分：参数名  参数类型
函数体：会有return语句，如果没有就会报错，如果return语句没有放在函数体的最后也不会报错，但是不建议；

调用语法：

select 函数名(参数列表)

create function myf1() returns int
begin
	declare c default 0;定义变量
	select count(*) INTO c # 为变量赋值
    from emp;
    return  c;
end;

select myf1();

create function myf2(empName varchar(20)) returns double
begin
	set @sal = 0;
	select salary INTO @sal
	from emp where name = empName
	return @sal
end;

selelct myf2('a');

查看函数：

show create function 函数名；

删除函数

drop function 函数名;

流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定的条件基础上，重复执行一段代码

一、分支结构

if函数；实现简单的双分支

IF(表达式，表达式2，表达式3)  如果表达式1成立，则返回表达式2的值，否则返回表达式3的值

case结构

1.类似于java中switch语句一般用于实现的等值判断
	case 变量|表达式|字段
	when 要判断的值 then 返回的值1
	when 要判断的值 then 返回的值2
	...
	else 要返回的值n
	end
2.类似于多从if语句，一般用于实现区间判断
	case 
	when 要判断的条件1 then 返回的值1
	when 要判断的条件2 then 返回的值2
	...
	else 要返回的值n
	end

create procedure test_case(IN score int)
begin 
	case 
	when score>=90 and score <= 100 then select 'A'
	when score >80 then select 'b'
	else select 'd'
	end case
end;

if结构

功能：实现多重分支

if 条件1 then 语句1；
elseif 条件2 then 语句2；
。。。
else 语句n；
end if;
应用在begin end中

create function test_if(score int) returns char
begin
	if score>= 90 and score <= 100 then return 'A'
	elseif score >80 then return 'b'
	elseif score >70 then return 'c'
	else return 'D'
	end if;
end;

循环结构

while 循环条件 do    类似java中的while循环
循环体;
end while;

loop   需要在循环体中添加结束的条件，不然就是死循环。
循环体;
end loop;

repeat    类似java中的do while
循环体;
until 结束循环的条件
end repeat

iterate：类似于java中的continue，结束本次循环，继续下一次循环。
leave：类似于java中的break，跳出循环，执行之后的语句。

create procedure pro_while1(IN insertCount INT)
begin
	declar i int default 1;
	while i< insertCount do
		insert into admin(username,`password`)values)('rose'+i,'666');
		set i=i+1;
	end while;
end$

call procedure(100)$

mysql架构

连接层：用于与mysql进行连接交互
服务层：完成核心服务功能，进行sql接口，完成缓存的查询，sql的分析和优化几部分内置函数的执行。还比如存储过程、函数。等
引擎层：存储引擎层
存储层：数据存储层，完成与文件系统的交互。

储存引擎

myisam ：不支持主外键、事务，查询性能高

innoDB ：支持主外键、事务，默认开启

索引

索引(index)是帮助mysql高效获取数据的数据结构，索引是数据结构

我们平常所说的索引，如果没有特别指明，都是指B树，并不一定是二叉树，

复合隐，前缀索引，唯一索引默认都是B+树索引，统称索引。

排好序的快速查找数据结构；

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

复合索引：一个索引包含多个列；

create index 索引名 on 表名；
show index from 表名；查看表索引；
explain sql语句，查看查询详细情况；

使用索引进行多表查询时，left join索引建立在右表，right join索引建立在左表；

优化原则：

小标驱动大表，小数据集驱动大的数据集；用in优于exists；

exists(sql语句) 返回true或者false

order by关键字优化

order by使用在索引列上进行优化，因为索引后，会自动排序

order by不要是用select * 值query需要的字段。

提高sort__buffer_size

提高max_length_for_sort_data

group by与order by基本一样；，能用where就不要用having

慢查询：

超过mysql响应时间的就叫慢查询，默认为10s；

默认slow _query_log的值为OFF

show variables like ‘%slow_query_log%’;

开启慢查询日志：

set global slow_query_log =1;数据库重启后失效；

永久修改：

slow_query_log = 1

使用profile进行sql分析

profile：是mysql提供用来分析当前会话中语句执行的资源小号情况，可用于sql的调优测量。

dev.mysql.com/doc/refman/5.5/en/show-profile.html

默认情况下，参数处于关闭状态，并保存最近15此的运行结果。

show variables like ‘profiling’;

set profiling =on; 开启profile

show profiles;查看运行结果；

show profile cpu,blok io fro query 3;

mysql锁机制

锁偏向myisam存储引擎，开销小，加锁块。

手动增加表锁；

lock table  表名字  read/write, 表2 read/write, 其他
show open tables;查看表中加过的锁
unlock tables;释放表锁

添加读锁不能修改，不能读取别表

添加写锁，不能读；

读锁会阻塞写，但是不会堵塞读，写锁则会把读和写都堵塞

表锁偏读，行锁偏写

行锁偏向于InnoDB存储引擎，开销大，加锁慢，会出现死锁。

索引失效，会导致行锁变表锁

间隙危害：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙。

当修改一个区间时，这个区间会被锁住，如果此时在修改这个区间会发生阻塞；

如何锁定一行，

begin;
select * from test where a=8 for update;锁定某一行，直到commit

show status like 'innodb_row_lock%'
查看行锁状态；

主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。您看，像在mysql数据库中，支持单项、异步赋值。在赋值过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新