MySQL
DDL（data definition language），数据定义语言
常用操作
1.show databases;
2.create database if not exists mydb1 charset=utf8;
3.use mydb1;
4.drop database if exists mydb1;
5.alter database mydb1 character set utf8;
6.修改表添加列：alter table student add dept varchar(20);
7.修改列名和类型：alter table student change dept department varchar(20);
8.删除列：alter table student drop department;
9.修改表名：rename table student to stu;
DML数据操作语言
1.插入insert
2.删除delete
delete from student where sid = 1004;
先删表再创建新表：truncate table student;或truncate student;
3.更新update
update student set address='重庆',score=100 where id = 1004;
约束
1.主键约束primary key
主键约束是一列或者多个列的组合，其值能唯一地标识表中的每一行，方便再RDBMS中尽快的找到某一行
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
每个表最多只允许一个主键
主键约束的关键字是：primary key
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引
创建主键方式一：
create table emp1(
eid int primary key,
name varchar(20),
deptID int,
salary duble
);
创建主键方式二：
create table emp2(
eid int,
name varchar(20),
deptID int,
salary duble,
constraint pk1 primary key(eid)
);
创建联合主键：
create table emp3(
name varchar(20),
deptID int,
salary double,
constraint pk2 primary key(name,deptID)
);
通过修改表结构的方式添加主键：
alter table emp4 add primary key(eid);
删除主键：
alter table emp4 drop primary key;
2.自增长约束auto_increment
在MySQL中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键会自动以相同的步长进行增长
创建自增长约束：
create table t_user1(
id int primary key auto_increment,
name varchar(20)
);
指定自增长的初始值：
create table t_user1(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;
alter table t_user1 auto_increment=100;
delete和truncate在删除后自增列的变化
delete数据之后自增长从断点开始
truncate数据之后自增长从默认起始值开始
非空约束not null
指字段的值不能为空
alter table t_user modify name varchar(25) not null;
删除非空约束：alter table t_user modify name varchar(25);
唯一性约束unique
create table t_user(
id int,
name varchar(20),
phone_number varchar(20) unique
);
在MySQL中，NULL和任何值都不相等，和自己也不相等
alter table t_user add constraint unique_pn unique(phone_number);
删除唯一约束：alter table t_user drop index unique_pn;
默认约束default
create table t_user(
id int,
name varchar(20),
address varchar(50) default '北京'
);
alter table t_user modify address varchar(50) default '深圳';
零填充约束zerofill
数值类型，长度不够10的时候，用0填充
create table t_user(
id int zerofill,
name varchar(20)
);
删除零填充约束：alter table t_user modify id int;
外键约束foreign key
分页查询
从第4条开始显示，显示5条
select * from product limit 3,5;
第n页，每页显示60个
(n-1)*60,60
把查询结果导入另一张表
insert into table2(field1,field2,...) select value1,value2,... from table1;
正则表达式regexp
-- 判断商品名以'海'开头的
SELECT * FROM product WHERE pname regexp('^海');
-- 以'水'结尾的商品
SELECT * FROM product WHERE pname regexp('水$');
外键约束
外键约束foreign key时表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表，外键所在的表就是从表。
外键用来建立主表和从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜4种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子、西瓜，其它的水果都不能购买。
建表时添加：constraint 外键名 foreign key (字段名) references 主表名 (主键列);
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2(deptno);
删除外键
alter table 表名 drop foreign key 外键约束名;
交叉连接查询
交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
外连接查询
外连接分为左外连接（left outer join）、右外连接（right outer join）、满外连接（full outer join）
oracle里面有full join，可mysql对full join支持的不好。我们可以用union来达到目的
select * from A left join B on 条件;
select * from A right join B on 条件;
子查询
指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式
多表联合查询的关键字
1.select .. from .. where c > all(查询语句);
2.select .. from .. where c > any(查询语句);
some和any作用一样
3.select .. from .. where c > in(查询语句);
4.select .. from .. where exists(查询语句);
-- 查询年龄大于'1003'部门所有年龄的员工信息
SELECT * FROM emp3
WHERE
age > ALL(select age from emp3 where dept_id = '1003');
-- 查询不属于任何一个部门的员工信息
SELECT * FROM emp3
WHERE
dept_id != ALL(SELECT deptno FROM dept3);
-- 查询年龄大于'1003'部门任意一个员工年龄的员工信息
SELECT * FROM emp3
WHERE
age > ANY(SELECT age from emp3 WHERE dept_id = '1003');
-- 查询研发部和销售部的员工信息，包含员工号、员工名字
SELECT * FROM emp3
WHERE
dept_id in (SELECT deptno from dept3 WHERE name in ('研发部','销售部'));
-- 查询公司是否有大于60岁的员工，有则输出
SELECT * FROM emp3 a
WHERE
EXISTS(SELECT * FROM emp3 b WHERE a.age > 60);
自连接查询
-- 查询每个三国人物及他的上级信息
SELECT
a.ename 员工,b.ename 上级
FROM
sanguo a
INNER JOIN
sanguo b
ON
a.manager_id = b.eid;
group_concat()函数
-- 把所有员工的名字合并成一行，按部门分组，要求名字按薪资降序排
SELECT department,GROUP_CONCAT(emp_name ORDER BY salary DESC SEPARATOR ';') FROM emp
GROUP BY department;
数学函数
绝对值
SELECT abs(字段) from 表;
向上取整
SELECT ceil(1.1); -- 返回2
向下取整
SELECT floor(1.9); -- 返回1
返回列表的最大值
SELECT greatest(字段1,字段2,字段3) from 表;
返回列表的最小值
SELECT least(字段1,字段2,字段3) from 表;
求余数
SELECT mod(5,2); -- 返回1
求次方
SELECT pow(4,3); -- 返回64
返回一个0~1之间的随机数
SELECT rand();
返回0~100之间的随机数
SELECT rand() * 100;
把小数四舍五入取整
SELECT round(3.5415); -- 返回4
四舍五入保留小数位
SELECT round(3.5415,3); -- 返回3.542
把小数直接截取到指定位数
SELECT TRUNCATE(3.1415,3); -- 返回3.141
字符串函数
返回字符串中字符的个数
SELECT char_length('你好吗'); -- 返回3
返回字符串中字节的个数
SELECT length('你好吗'); -- 返回9
字符串合并
SELECT concat('hello','world'); -- 返回helloworld
字符串合并，指定分隔符
SELECT concat_ws('-','hello','world'); -- 返回hello-world
返回第一个字符串在列表中的位置，返回第一次出现的位置
SELECT field('ccc', 'aaa','bbb','ccc'); -- 返回3
SELECT field('bbb', 'aaa','bbb','ccc'); -- 返回2
去除字符串左边空格
SELECT ltrim(' aaaa');
去除字符串右边空格
SELECT rtrim('aaaa ');
去除两边空格
select trim(' aaaa ');
字符串截取
SELECT mid('helloword',2,3); -- 从第2个字符开始截取，截取长度为3
获取字符串A在字符串B中第一次出现的位置
SELECT position('abc' in 'helloabcworld'); -- 返回6
字符串替换
SELECT replace('helloaaaworld','aaa','bbb');
字符串反转
SELECT reverse('hello');
返回字符串的后几个字符串
SELECT right('hello',3)
字符串比较
SELECT strcmp('hello', 'world'); -- 一个字符一个字符比
字符串截取
SELECT substr('hello',2,3); -- 从第2个字符开始截取，截取长度为3
把小写转大写
SELECT upper('hello World');
把大写转小写
SELECT lower('HELLO World');
日期函数
返回时间戳
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP('2021-12-21 08:08:08');
把时间戳转为日期
SELECT FROM_UNIXTIME(1640045288,'%Y-%m-%d %H:%i:%s');
获取当前的年月日
SELECT CURDATE();
SELECT CURRENT_DATE();
获取当前的时分秒
SELECT CURTIME();
SELECT CURRENT_TIME();
返回当前的年月日 时分秒
SELECT CURRENT_TIMESTAMP();
从日期字符串中提取年月日
SELECT DATE('2022-12-12 12:34:56');
获取两个日期相隔多少天
SELECT DATEDIFF('2022-01-18','2021-12-18');
计算时间差值
SELECT TIMEDIFF('12:12:34','10:18:56');
把日期按照指定的格式显示
SELECT DATE_FORMAT('2021-1-1 1:1:1','%Y-%m-%d %H:%i:%s');
把字符串转成日期
SELECT STR_TO_DATE('2022-12-24 17:34:48','%Y-%m-%d');
SELECT STR_TO_DATE('August 10 2017','%M%d%Y');
从日期向前跳转
SELECT DATE_SUB('2022-10-01',interval 2 day);
日期向后跳转
SELECT DATE_ADD('2022-10-01',interval 2 day);
SELECT DATE_ADD('2022-10-01',interval 2 month);
从日期中获取值
SELECT EXTRACT(hour from '2021-12-13 11:12:13');
SELECT EXTRACT(year from '2021-12-13 11:12:13');
SELECT EXTRACT(month from '2021-12-13 11:12:13');
返回给定日期的那一月份的最后一天
SELECT LAST_DAY('2022-01-18');
获取指定年月和天数的对应日期
SELECT MAKEDATE('2022',53); -- 2022年的第53天是几号
根据日期获取年月日 时分秒
SELECT year('2022-12-24 17:34:48');
SELECT month('2022-12-24 17:34:48');
SELECT minute('2022-12-24 17:34:48');
SELECT QUARTER('2022-12-24 17:34:48'); -- 获取季度
获取日期信息
SELECT MONTHNAME('2022-12-24 17:34:48');
SELECT DAYNAME('2022-12-24 17:34:48');
SELECT DAYOFMONTH('2022-12-24 17:34:48');
SELECT DAYOFWEEK('2022-12-24 17:34:48'); -- 周日是1，周一是2
SELECT DAYOFYEAR('2022-12-24 17:34:48');
SELECT week('2022-12-24 17:34:48'); -- 算出一年的第几周
SELECT weekday('2022-12-24 17:34:48'); -- 日期是星期几，0表示星期一
SELECT yearweek('2022-12-24 17:34:48'); -- 日期是几几年第几周
SELECT now(); -- 返回当前日期和时间
控制流函数
如果表达式成立，返回v1，否则返回v2
if(表达式,v1,v2)
判断工资等级
SELECT *,IF(salary>6000,'优秀','及格') 等级 FROM emp;
判断v1是否为null，如果是null，则用v2代替
ifnull(v1,v2)
判断字段是不是null
isnull(5);
判断两个值是否相同，相同返回null，不相同返回第一个
SELECT nullif(12,12);
SELECT nullif(5,9);
case...when语句
例子，新增列：支付方式
方式1
SELECT
*,
case payType
when 1 then '微信支付'
when 2 then '支付宝支付'
when 3 then '银行卡支付'
else '其他支付'
end '支付方式'
FROM
orders;
方式2
SELECT
*,
case
when payType=1 then '微信支付'
when payType=2 then '支付宝支付'
when payType=3 then '银行卡支付'
else '其他支付'
end payTypeStr
FROM
orders;
窗口函数mysql8.0新增的功能
1.序号函数，有3个，row_number(),rank(),dense_rank()，可以实现分组排序，并添加序号
格式：
row_number() | rank() | dense_rank() over (
partition by ...
order by ...
)
-- 对每个部门的员工按照薪资排序，并给出排名，如果出现重复薪资rank会隔断
SELECT
dname,ename,salary,
row_number() over(partition by dname order by salary desc) rn
FROM
employee;
SELECT
dname,ename,salary,
rank() over( partition by dname order by salary desc) rn
FROM
employee;
SELECT
dname,ename,salary,
dense_rank() over(partition by dname order by salary desc) rn
FROM
employee;
-- 求出每个部门薪资排在前3名的员工
SELECT
*
FROM
(SELECT
dname,
ename,
salary,
dense_rank() over ( PARTITION BY dname ORDER BY salary DESC ) rn
FROM
employee) t
WHERE
t.rn <= 3;
-- 全局排序
SELECT
dname,
ename,
salary,
dense_rank() over (ORDER BY salary DESC ) rn
FROM
employee;
2.开窗聚合函数，sum,avg,min,max,count
-- 累加
SELECT
dname,ename,salary,
sum(salary) over (partition by dname order by hiredate) c1
FROM
employee;
-- 从向上3行加到当前行
SELECT
dname,ename,salary,
sum(salary) over (partition by dname order by hiredate rows between 3 preceding and current row) c1
FROM
employee;
-- 从向上3行加到当前行，外加当前行的下一行
SELECT
dname,ename,salary,
sum(salary) over (partition by dname order by hiredate rows between 3 preceding and 1 following) c1
FROM
employee;
-- 从当前行加到最后
SELECT
dname,ename,salary,
sum(salary) over (partition by dname order by hiredate rows between current row and unbounded following) c1
FROM
employee;
3.分布函数，cume_dist和percent_rank
-- cume_dist小于等于当前rank值的行数 / 总行数
SELECT
dname,ename,salary,
cume_dist() over (order by salary) as rn1,
cume_dist() over (partition by dname order by salary) as rn2
FROM
employee;
-- percent_rank每行按照公式(rank-1) / (分组后的总行数 - 1)，不常用
SELECT
dname,ename,salary,
rank() over (partition by dname order by salary desc) as rn1,
percent_rank() over (partition by dname order by salary desc) as rn2
FROM
employee;
4.前后函数lag和lead
返回位于当前行的前n行（lag(expr,n)）或后n行（lead(expr,n)）的expr值
-- 把相邻的日期放同一行
SELECT
dname,ename,salary,hiredate,
lag(hiredate,1,'2000-01-01') over (partition by dname order by hiredate) as time1,
lag(hiredate,2) over (partition by dname order by hiredate) as time2
FROM
employee;
SELECT
dname,ename,salary,hiredate,
lead(hiredate,1,'2000-01-01') over (partition by dname order by hiredate) as time1,
lead(hiredate,2) over (partition by dname order by hiredate) as time2
FROM
employee;
-- 头尾函数,first_value和last_value
从开头到目前为止第一个和最后一个
SELECT
dname,ename,salary,hiredate,
first_value(salary) over (partition by dname order by hiredate) as first,
last_value(salary) over (partition by dname order by hiredate) as last
FROM
employee;
-- 其他函数
nth_value(expr,n)
返回窗口种第n个expr的值，expr可以是表达式，也可以是列名
-- 查询每个部门截止目前薪资排在第二和第三的员工信息
SELECT
dname,ename,salary,hiredate,
nth_value(salary,2) over (partition by dname order by hiredate) as secode_sal,
nth_value(salary,3) over (partition by dname order by hiredate) as third_sal
FROM
employee;
ntile(n)
把分区中的有序数据分为n个等级，记录等级数
SELECT
dname,ename,salary,hiredate,
ntile(3) over (partition by dname order by hiredate) as nt
FROM
employee;
-- 取出每个部门的第一组员工
SELECT
*
FROM
(SELECT
dname,ename,salary,hiredate,
ntile(4) over (partition by dname order by hiredate) as nt
FROM
employee) t
WHERE
t.nt = 1;
视图view
视图是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变
视图的作用：
1.简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使用复杂的查询易于理解和使用
2.安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图。如社会保险基金表，可以用视图只显示姓名、地址，而不显示社会保险号和工资数等，可以对不同的用户设定不同的视图
创建视图
create [or replace] view view_name as select statement;
查询有多少视图
show full tables;
修改视图
alter view 视图名 as select语句;
更新视图
如果视图包含下述结构中的任何一种，那么它就不能更新
聚合函数
distinct
group by
having
union或union all
位于选择列表中的子查询
join
from子句中的不可更新视图
where子句中的子查询，引用from子句中的表
重命名视图
rename table 视图名 to 新视图名;
删除视图
drop view if exists 视图名;
存储过程
存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，累死JAVA中的方法
存储过程就是数据库SQL语言层面的代码封装和重用
特性
1.有输入输出参数，可以声明变量，有if/else,case,while等控制语句
2.模块化，封装，代码复用
3.速度快，只有首次执行需要经过编译和优化，后续被调用可以直接执行
语法
delimiter $$
create procedure 存储名()
begin
sql语句;
end $$
delimiter;
调用存储过程
call 存储命();
案例
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter;
-- 调用存储过程
call proc01();
存储过程的局部变量
在begin/end块中有效
语法
declare var_name type [default var_value];
set var_name = 'zhangsan';
select var_name;
案例
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa';
set var_name01 = 'zhangsan';
select var_name01;
end $$
delimiter;
call proc02();
还可以使用select...into语句为变量赋值
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20);
select ename into my_ename from emp where empno=7788;
select my_ename;
end $$
delimiter;
call proc03();
用户变量，当前会话（连接）有效，在begin/end外部也可以使用用户变量
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'beijing';
select @var_name01;
end $$
delimiter;
call proc04();
select @var_name01;
系统变量-全局变量
语法
@@global.var_name
查看全局变量
show global variables;
查看某全局变量
select @@global.auto_increment_increment;
修改全局变量
set @@global.sort_buffer_size = 40000;
会话变量，当前连接有效
查看会话变量
show session variables;
查看某全局变量
select @@session.auto_increment_increment;
修改全局变量
set @@session.sort_buffer_size = 40000;
存储过程传参in
delimiter $$
create procedure proc05(in param_empno int)
begin
select * from emp where empno=param_empno;
end $$
delimiter;
call proc05(7839);
call proc05(7788);
多个变量
delimiter $$
create procedure proc06(in param_dname varchar(14),in param_sal double(7,2))
begin
select * from dept d join emp e on d.deptno=e.deptno
where d.dname=param_dname and e.sal > param_sal;
end $$
delimiter;
call proc06('SALES',2000);
存储过程传参out
out表示从存储过程内部传值给调用者
-- 返回一个变量
delimiter $$
create procedure proc07(in in_empno int,out out_ename varchar(10))
begin
select ename into out_ename from emp where empno=in_empno;
end $$
delimiter;
call proc07(7788,@o_ename);
SELECT @o_ename;
-- 返回2个变量
delimiter $$
create procedure proc08(in in_empno int,out out_ename varchar(10),out out_sal double(7,2))
begin
select ename,sal into out_ename,out_sal from emp where empno=in_empno;
end $$
delimiter;
call proc08(7788,@o_ename,@o_sal);
SELECT @o_ename;
SELECT @o_sal;
存储过程传参inout
-- 传入一个数字，传出这个数字的10倍值
delimiter $$
create procedure proc09(inout num int)
begin
set num = num * 10;
end $$
delimiter;
set @inout_num = 3;
call proc09(@inout_num);
select @inout_num;
-- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc10(inout param_ename varchar(10),inout param_sal double(7,2))
begin
select concat_ws('_',deptno,ename) into param_ename from emp where ename=param_ename;
set param_sal = param_sal * 12;
end $$
delimiter;
set @param_ename = 'JONES';
set @param_sal = 3000;
call proc10(@param_ename,@param_sal);
SELECT @param_ename;
SELECT @param_sal;
存储过程-流程控制-判断
/*输入学生的成绩，判断成绩的级别：
score < 60：不及格
score>=60,score<80：及格
score>=80,score<90：良好
score>=90,score<=100：优秀
score>100：成绩错误
*/
delimiter $$
create procedure proc11(in score int)
begin
if score < 60 then select '不及格';
elseif score >= 60 and score < 80 then select '及格';
elseif score >= 80 and score < 90 then select '良好';
elseif score >= 90 and score <= 100 then select '优秀';
else select '成绩错误';
end if;
end $$
delimiter;
set @score = 65;
call proc11(@score);
-- 案例2
/*输入员工名字，判断工资情况
sal < 1000：试用薪资
sal >=1000,sal<2000：转正薪资
sal >=2000：元老薪资
*/
delimiter $$
create procedure proc12(in in_ename varchar(10))
begin
declare var_sal double(7,2);
select sal into var_sal from emp where emp.ename = in_ename;
if var_sal < 1000 then select '试用薪资';
elseif var_sal >=1000 and var_sal < 2000 then select '转正薪资';
else select '员工工资';
end if;
end $$
delimiter;
set @var_name = 'ADAMS';
call proc12(@var_name);
存储过程-流程控制-case
案例
/*
支付方式：
1 微信支付
2 支付宝支付
3 银行卡支付
4 其他方式支付
*/
delimiter $$
create procedure proc13(in pay_type int)
begin
case pay_type
when 1 then select '微信支付';
when 2 then select '支付宝支付';
when 3 then select '银行卡支付';
else select '其他方式支付';
end case;
end $$
delimiter;
call proc13(5);
存储过程-流程控制-循环
循环分类
1.while
2.repeat
3.loop
循环控制
leave类似于break，跳出，结束当前所在的循环
iterate类似于continue，继续，结束本次循环，继续下一次
循环-while
-- 向表中添加指定条数的数据
delimiter $$
create procedure proc14_while(in insertCount int)
begin
declare i int default 1;
while i <= insertCount do
insert into user(uid,username,password) values(i,concat_ws('_','user',i),'123');
set i = i + 1;
end while;
end $$
delimiter;
call proc14_while(10);
循环-while+leave
-- 向表中添加指定条数的数据，插入5行时跳出循环
delimiter $$
create procedure proc15_while(in insertCount int)
begin
declare i int default 1;
label:while i <= insertCount do
insert into user(uid,username,password) values(i,concat_ws('_','user',i),'123');
if i = 5 then
leave label;
end if;
set i = i + 1;
end while label;
end $$
delimiter;
call proc15_while(10);
循环控制-while+iterate
-- 跳过本次循环的剩余代码，进入下一次循环
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 0;
label:while i < insertCount do
set i = i + 1;
if i = 5 then
iterate label;
end if;
insert into user(uid,username,password) values(i,concat_ws('_','user',i),'123');
end while label;
end $$
delimiter;
call proc16_while(10);
循环控制-repeat
delimiter $$
create procedure proc17_repeat(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid,username,password) values(i,concat_ws('_','user',i),'123');
set i = i + 1;
until i > insertCount
end repeat label;
end $$
delimiter;
call proc17_repeat(10);
循环控制-loop
delimiter $$
create procedure proc18_loop(in insertCount int)
begin
declare i int default 1;
label:loop
insert into user(uid,username,password) values(i,concat_ws('_','user',i),'123');
set i = i + 1;
if i > insertCount then
leave label;
end if;
end loop label;
end $$
delimiter;
call proc18_loop(10);
游标cursor
用来存储查询结果集的数据类型
声明
declare cursor_name cursor for select_statement
打开
open cursor_name
取值
fetch cursor_name into var_name
关闭
close cursor_name
-- 输入一个部门名，查询该部门员工的编号、名字、薪资，把查询结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_dname varchar(14))
begin
declare var_empno int;
declare var_ename varchar(10);
declare var_sal double(7,2);
-- 声明游标
declare my_cursor cursor for
select e.empno,e.ename,e.sal from dept d join emp e on d.deptno=e.deptno where d.dname=in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;
-- 关闭游标
close my_cursor;
end $$
delimiter;
call proc19_cursor('SALES')
索引
索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大的一部分时间
索引类似一本书的目录，比如要查找'student'这个单词，可以先找到s开头的页，然后向后查找
索引的分类
1.按照数据结构分
Hash索引
B+Tree索引
2.按功能分类
单列索引：普通索引、唯一索引、主键索引
组合索引
全文索引
空间索引
单列索引
一个索引只包含单个列，但一个表中可以有多个单列索引
普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点
方式1：创建表的时候加索引
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
index index_name(name)
);
方式2：直接创建索引
create index index_gender on student(gender);
方式3：修改表结构添加索引
alter table student add index index_age(age);
查看表中的索引
show index from 表名;
删除索引
方式1：
drop index 索引名 on 表名;
方式2：
alter table 表名 drop index 索引名;
唯一索引
索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一
主键索引
每张表一般都有自己的主键，创建表时，MySQL会自动在主键列上创建一个索引，这就是主键索引。主键具有唯一性并且不允许为NULL
组合索引
create index 索引名 on 表名(列1,列2);
组合索引最左原则
比如
select * from student where name = '张三'; -- 用不到组合索引
select * from student where phone_num = '15100046637' -- 用到组合索引
select * from student where phone_num = '15100046637' and name = '张三'; -- 用到组合索引
select * from student where name = '张三' and phone_num = '15100046637'; -- 用到组合索引
全文索引fulltext（了解）
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询
使用全文索引时，要先看一下自己的MySQL版本、存储引擎和数据类型是否支持全文索引
char,varchar,text才支持全文索引
select * from t where match(content) against('you');
Hash算法
优点：通过字段的值计算的hash值，定位数据非常快
缺点：不能进行范围查询，因为散列表中的值是无序的，无法进行大小的比较
二叉树
特性：分为左子树、右子树和根节点，左子树比根节点值要小，右子树比根节点值要大
缺点：
1.插入操作需要旋转
2.支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10
3.如果存放几百条数据的情况下，树高度越高，查询效率越慢
B+TREE树
叶子节点的值不会丢
数据放在连续的位置，方便磁盘扇区扫描，减少磁头来回动的次数
MyISAM
叶子节点的data域存放的是数据记录的地址
InnoDB
叶子节点的data域存放的是数据，相比MyISAM效率要高一些，但是比较占硬盘空间
索引优点
1.大大加快数据的查询速度
2.使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
3.创建唯一索引，能够保证数据库表中每一行数据的唯一性
4.在实现数据的参考完整性方面，可以加速表和表之间的连接
索引的缺点
1.创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
2.索引需要占据磁盘空间
3.对数据表中的数据进行增加、修改、删除，索引也要动态的维护，降低了维护的速度
创建索引的原则
1.更新频繁的列不应设置索引
2.数据量小的表不要使用索引
3.重复数据多的字段不应设为索引（比如性别，一般来说，重复的数据超过百分之15就不该建索引）
4.首先应该考虑对where和order by涉及的列上建索引
存储引擎
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎
可以使用show engines;查询数据库支持的引擎，默认引擎是InnoDB支持事务、行级锁定和外键
查看当前数据库支持的存储引擎
show engines;
查看当前的默认存储引擎
show variables like '%storage_engine%';
查看某个表用了什么引擎
show create table 表名;
创建新表时指定存储引擎
create table (...) engine=MyISAM;
修改表的引擎
alter table 表名 engine=INNODB;
修改MySQL默认存储引擎
1.关闭MySQL拂去
2.找到MySQL安装目录下的my.ini文件
3.找到default-storage-engine=INNODB改为目标引擎
4.启动MySQL服务
事务（transaction）
在MySQL中的事务是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理DDL、DML、DCL，比如insert，update，delete语句，默认是自动提交的
开始事务
begin;
提交事务
commit;
回滚事务
rollback;
禁止自动提交
set autocommit=0;
开启自动提交
set autocommit=1;
事务的特性
原子性：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完成，要么全部不做
一致性：系统从一个正确的状态，迁移倒另一个正确的状态
隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不可见
持久性：事务一旦提交，则其结果是永久性的
事务的隔离级别
1.读未提交（read uncommitted）
一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证，会造成脏读
2.读已提交（read committed）
一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读
3.可重复读（repeatable read）
就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。MySQL默认的隔离级别是repeatable read
4.序列化（serializable）
是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可避免脏读、不可重复读、幻读，但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用
查看隔离级别
show variables like '%isolation%';
设置read uncommitted
set session transaction isolation level read uncommitted;
设置read committed，A事务在没提交事务期间，读取倒B事务操作的数据是不同的
set session transaction isolation level read committed;
设置repeatable read，A事务在提交之前和提交之后看到的数据不一致
set session transaction isolation level repeatable read;
设置序列化，A事务操作表时，表会被锁起，B事务不能操作
set session transaction isolation level serializable;
锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）
在数据库中，除传统的计算资源（CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素
表锁
操作时，会锁定整个表
行锁
操作时，会锁定当前操作行
读锁
针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁
当前操作没有完成之前，它会阻断其他写锁和读锁
MyISAM存储引擎只支持表锁
MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新（update,delete,insert）操作前，会自动给涉及的表加写锁
加读锁
lock table 表名 read;
加写锁
lock table 表名 write;
释放锁
unlock tables;
InnoDB行级锁
共享锁（S）
select * from xxx where ... lock in share mode;
排他锁（X）
select * from xxx where ... for update;
MySQL日志
帮助数据库管理员追踪数据库曾经发生的各种事件
日志分类
错误日志
二进制日志
查询日志
慢查询日志
错误日志
查看日志位置指令
show variables like '%log_error%';
查看是否开启了binlog日志
show variables like '%log_bin%';
查看所有binlog日志
show binlog events;
查看最新的日子
show master status;
查询指定的binlog日志
show binlog events in 'DESKTOP-OSFHTMP-bin.000101';
查看日志从指定位置开始显示
show binlog events in 'DESKTOP-OSFHTMP-bin.000101' from 666;
限制查看个数
show binlog events in 'DESKTOP-OSFHTMP-bin.000101' from 666 limit 2;
清空所有日志
reset master;
查询日志
检查查询日志是否开启
show variables like '%general_log%';
开启查询日志（临时）
set global general_log=1;
慢查询日志
检查慢查询日志是否开启
show variables like '%slow_query_log%';
开启慢查询日志
set global slow_query_log=1;
查看慢查询日志超时时间
show variables like '%long_query_time%';
修改慢查询日志时间
set global long_query_time=5;
MySQL的优化
从设计上优化
从查询上优化
从索引上优化
从存储上优化
查看当前会话SQL执行类型的统计信息（Com后面7个下划线）
show session status like 'Com_______';
查看上次服务器启动至今的SQL执行类型的统计信息（Com后面7个下划线）
show global status like 'Com_______';
查询针对InnoDB引擎的统计信息
show status like '%Innodb_rows_%';
定位低效率执行
show processlist;
explain分析执行计划
EXPLAIN SELECT * FROM emp WHERE sal > 2000;
explain之type字段
system>const>eq_ref>ref>range>index>ALL
只有主键索引和唯一索引：const
左表有主键列，而且左表的每一行和右表的每一行刚好匹配：eq_ref
普通索引：ref
范围查询：range
把索引列的数据都扫描：index
全表扫描：ALL
浙公网安备 33010602011771号