多表查询
-- 多表查询-数据准备
use mydb3;
-- 创建部门表
create table if not exists dept3(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp3(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');
-- 1.交叉连接查询
/*
1. 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
2. 笛卡尔集可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
3. 假如A表有m行数据，B表有n行数据，则返回m*n行数据
4. 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
格式：select * from 表1,表2,表3….;
*/
-- 内连接查询
/*
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
*/
-- 查询每个部门的所属员工
-- 隐式内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 a,emp3 b where a.deptno = b.dept_id;
-- 显式内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 a join emp3 b on a.deptno = b.dept_id;
-- 查询研发部门的所属员工
-- 隐式内连接
select * from dept3 a ,emp3 b where a.deptno = b.dept_id and name = '研发部';
-- 显式内连接
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name = '研发部';
-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部') ;
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in('研发部' ,'销售部') ;
-- 查询每个部门的员工数,并升序排序
select
a.name,a.deptno,count(1)
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno,name;
-- 查询人数大于等于3的部门，并按照人数降序排序
select
a.deptno,
a.name,
count(1) as total_cnt
from dept3 a
join emp3 b on a.deptno = b.dept_id
group by
a.deptno,a.name
having
total_cnt >= 3
order by
total_cnt desc;
-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';
-- 查看binlog日志的格式
show variables like 'binlog_format';
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in 'binlog.000010';
select * from mydb1.emp2;
select count(*) from mydb1.emp2;
update mydb1.emp2 set salary = 8000;
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
-- 开启查询日志
set global general_log=1;
select * from mydb1.emp2;
select * from mydb6_view.emp;
select count(*) from mydb1.emp2;
select count(*) from mydb6_view.emp;
update mydb1.emp2 set salary = 9000;
-- 慢日志查询
-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';
-- 开启慢查询日志
set global slow_query_log = 1;
-- 查看慢查询的超时时间
show variables like 'long_query_time%';
select sleep(12);
存储过程
-- 存储过程---------------
create database mysql7_procedure;
use mysql7_procedure;
-- 1:创建存储过程
/*
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
sql语句
end 自定义的结束符合
delimiter ;
*/
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01();
call proc01();
-- 变量定义
/*
语法1： 声明变量 declare var_name type [default var_value];
语法2：
select col_name [...] into var_name[,...]
from table_name wehre condition
*/
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量
set var_name01 = 'zhangsan'; -- 给变量赋值
select var_name01; -- 输出变量的值
end $$
delimiter ;
call proc02();
/*
select col_name [...] into var_name[,...]
from table_name wehre condition
其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
*/
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ; -- 声明/定义变量
select ename into my_ename from emp where empno = 1001; -- 给变量赋值
select my_ename; -- 输出变量的值
end $$
delimiter ;
call proc03();
-- 用户变量
delimiter $$
create procedure proc04()
begin
set @var_nam01 = 'beijing';
select @var_nam01;
end $$
delimiter ;
call proc04();
select @var_nam01; -- 也可以使用用户变量
use mysql7_procedure;
-- 系统变量
-- 全局变量
use mysql7_procedure
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 33000;
select @@global.sort_buffer_size;
-- 会话变量
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 60000 ;
select @@session.sort_buffer_size;
-- ---------传入参数：in---------------------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int )
begin
select * from emp where emp.empno = empno;
end $$
delimiter ;
call proc06(1001);
call proc06(1002);
-- ------------------------------------
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50), in param_sal decimal(7,2))
begin
select * from dept a, emp b where a.deptno = b.deptno and a.dname = param_dname and b.sal > param_sal;
end $$
delimiter ;
call proc07('学工部',20000);
call proc07('销售部',10000);
-- ---------传出参数：out---------------------------------
use mysql7_procedure;
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in in_empno int, out out_ename varchar(50))
begin
select ename into out_ename from emp where empno = in_empno;
end $$
delimiter ;
call proc08(1002, @o_ename);
select @o_ename;
-- ------------------------------------------
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in in_empno int, out out_ename varchar(50), out out_sal decimal(7,2))
begin
select ename, sal into out_ename , out_sal
from emp
where empno = in_empno;
end $$
delimiter ;
call proc09(1002, @o_ename,@o_sal);
select @o_ename;
select @o_sal;
-- ------------------------------------
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50), in param_sal decimal(7,2))
begin
select * from dept a, emp b where a.deptno = b.deptno and a.dname = param_dname and b.sal > param_sal;
end $$
delimiter ;
call proc07('学工部',20000);
call proc07('销售部',10000);
use mysql7_procedure;
-- 传入一个数字，传出这个数字的10倍值
delimiter $$
create procedure proc10(inout num int)
begin
set num = num * 10;
end $$
delimiter ;
set @inout_num = 3;
call proc10(@inout_num);
select @inout_num;
-- 传入员工名，拼接部门号，传入薪资，求出年薪
-- 关羽 ----> 30_关羽
delimiter $$
create procedure proc11(inout inout_ename varchar(50), inout inout_sal int)
begin
select concat_ws('_',deptno,ename) into inout_ename from emp where emp.ename = inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc11(@inout_ename,@inout_sal);
select @inout_ename ;
select @inout_sal;
-- 存储过程-if
-- 案例1
-- 输入学生的成绩，来判断成绩的级别：
/*
score < 60 :不及格
score >= 60 , score <80 :及格
score >= 80 , score < 90 :良好
score >= 90 , score <= 100 :优秀
score > 100 :成绩错误
*/
delimiter $$
create procedure proc_12_if(in score int)
begin
if score < 60
then
select '不及格';
elseif 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 ;
call proc_12_if(120)
-- 输入员工的名字，判断工资的情况。
/*
sal < 10000 :试用薪资
sal >= 10000 and sal < 20000 :转正薪资
sal >= 20000 :元老薪资
*/
delimiter $$
create procedure proc_13_if(in in_ename varchar(20))
begin
declare var_sal decimal(7,2);
declare result varchar(20);
select sal into var_sal from emp where ename = in_ename;
if var_sal < 10000
then
set result = '试用薪资';
elseif var_sal < 20000
then
set result = '转正薪资' ;
else
set result = '元老薪资' ;
end if;
select result;
end $$
delimiter ;
-- 调用
call proc_13_if('关羽');
call proc_13_if('程普');
-- 流程控制语句：case
/*
支付方式:
1 微信支付
2 支付宝支付
3 银行卡支付
4 其他方式支付
*/
-- 格式1
delimiter $$
create procedure proc14_case(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 proc14_case(2);
call proc14_case(4);
-- 格式2
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when score < 60
then
select '不及格';
when score < 80
then
select '及格' ;
when score >= 80 and score < 90
then
select '良好';
when score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end case;
end $$
delimiter ;
call proc_15_case(88);
-- 存储过程-循环-while
use mysql7_procedure;
-- 创建测试表
create table user (
uid int primary key,
username varchar(50),
password varchar(50)
);
/*
【标签:】while 循环条件 do
循环体;
end while【 标签】;
*/
-- 需求：向表中添加指定条数的数据
-- -------存储过程-循环-while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1;
label:while i <= insertCount do
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
end while label;
end $$
delimiter ;
call proc16_while(10);
-- -------存储过程-循环控制-while + leave
-- leave：直接跳出while循环
truncate table user;
delimiter $$
create procedure proc17_while_leave(in insertCount int) -- 10
begin
declare i int default 1;
label:while i <= insertCount do -- 10
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
if i = 5 then
leave label;
end if;
set i = i + 1;
end while label;
select '循环结束';
end $$
delimiter ;
call proc17_while_leave(10);
-- -------存储过程-while+iterate
-- iterate:跳过本次循环的剩余代码，进入下一次循环
create table user2 (
uid int ,
username varchar(50),
password varchar(50)
);
use mysql7_procedure;
truncate table user2;
delimiter $$
create procedure proc17_while_iterate(in insertCount int) -- 10
begin
declare i int default 0;
label:while i < insertCount do -- 10
set i = i + 1;
if i = 5 then
iterate label;
end if;
insert into user2(uid, username, password) values(i,concat('user-',i),'123456');
end while label;
select '循环结束';
end $$
delimiter ;
call proc17_while_iterate(10); -- 1 2 3 4 6 7 8 9 10
-- -------存储过程-循环控制-repeat
use mysql7_procedure;
truncate table user;
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
until i > insertCount
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc18_repeat(100);
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值、
-- 关闭游标
-- -------存储过程-循环控制-loop
/*
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
*/
use mysql7_procedure;
truncate table user;
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i int default 1;
label: loop
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1 ;
if i > insertCount
then
leave label;
end if ;
end loop label;
end $$
delimiter ;
call proc19_loop(100);
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值、
-- 关闭游标
use mysql7_procedure;
drop procedure if exists proc19_cursor;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a, emp b
where a.deptno = b.deptno and a.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;
-- xxxxx
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc19_cursor('销售部');
-- 游标 + 句柄
/*
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
*/
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a, emp b
where a.deptno = b.deptno and a.dname = in_dname;
-- 定义句柄：定义异常的处理方式
/*
1:异常处理完之后程序该怎么执行
continue :继续执行剩余代码
exit : 直接终止程序
undo: 不支持
2: 触发条件
条件码:
1329
条件名：
SQLWARNING
NOT FOUND
SQLEXCEPTION
3:异常触发之后执行什么代码
设置flag的值 ---》 0
*/
declare continue handler for 1329 set flag = 0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
-- 判断flag，如果flag的值为1，则执行，否则不执行
if flag = 1 then
select var_empno, var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- xxxxx
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc21_cursor_handler('教研部');
drop table user_2021_11_03;
-- -----------------------------------------
-- 练习
/*
创建下个月的每天对应的表user_2021_12_01、user_2022_12_02、...
需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！
*/
-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
create database mydb18_proc_demo;
use mydb18_proc_demo;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
declare next_year int; -- 下一个月的年份
declare next_month int; -- 下一个月的月份
declare next_month_day int;-- 下一个月最后一天的日期
declare next_month_str varchar(2); -- 下一个月的月份字符串
declare next_month_day_str varchar(2);-- 下一个月的日字符串
-- 处理每天的表名
declare table_name_str varchar(10);
declare t_index int default 1;
-- declare create_table_sql varchar(200);
-- 获取下个月的年份
set next_year = year(date_add(now(),INTERVAL 1 month)); -- 2021
-- 获取下个月是几月
set next_month = month(date_add(now(),INTERVAL 1 month)); -- 11
-- 下个月最后一天是几号
set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); -- 30
if next_month < 10
then set next_month_str = concat('0',next_month); -- 1 ---》 01
else
set next_month_str = concat('',next_month); -- 12
end if;
while t_index <= next_month_day do
if (t_index < 10)
then set next_month_day_str = concat('0',t_index);
else
set next_month_day_str = concat('',t_index);
end if;
-- 2021_11_01
set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
-- 拼接create sql语句
set @create_table_sql = concat(
'create table user_',
table_name_str,
'(`uid` INT ,`uname` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
-- FROM后面不能使用局部变量！
prepare create_table_stmt FROM @create_table_sql;
execute create_table_stmt;
DEALLOCATE prepare create_table_stmt;
set t_index = t_index + 1;
end while;
end $$
delimiter ;
call proc22_demo();
select year(date_add(now(),INTERVAL 1 month))
select dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
事务SQL
create database if not exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(
id int primary key, -- 账户id
name varchar(20), -- 账户名
money double -- 金额
);
-- 插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0;
-- 模拟账户转账
-- 开启事务
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
-- 回滚事务
rollback;
select * from account;
-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
-- 这种隔离级别会引起脏读，A事务读取到B事务没有提交的数据
-- 设置read committed
set session transaction isolation level read committed;
-- 这种隔离级别会引起不可重复读，A事务在没有提交事务之前，可看到数据不一致
-- 设置repeatable read （MySQ默认的）
set session transaction isolation level repeatable read;
-- 这种隔离级别会引起幻读，A事务在提交之前和提交之后看到的数据不一致
-- 设置serializable
set session transaction isolation level serializable;
-- 这种隔离级别比较安全，但是效率低，A事务操作表时，表会被锁起，B事务不能操作。
-- SQL的优化
insert into account values(3,'wangwu',1000);
-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';
-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like 'Com_______';
-- 查看针对InnoDB引擎的统计信息
show status like 'Innodb_rows_%';
-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log = 1;
-- 查看慢日志记录SQL的最低阈值时间,默认如果SQL的执行时间>=10秒，则算慢查询，则会将该操作记录到慢日志中去
show variables like '%long_query_time%';
select sleep(12);
select sleep(10);
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time = 5;
-- 通过show processlist查看当前客户端连接服务器的线程执行状态信息
show processlist;
select sleep(50);
-- ----------------explain执行计划-------------------------
create database mydb13_optimize;
use mydb13_optimize;
-- 1、查询执行计划
explain select * from user where uid = 1;
-- 2、查询执行计划
explain select * from user where uname = '张飞';
-- 2.1、id 相同表示加载表的顺序是从上到下
explain select * from user u, user_role ur, role r where u.uid = ur.uid and ur.rid = r.rid ;
-- 2.2、 id 不同id值越大，优先级越高，越先被执行。
explain select * from role where rid = (select rid from user_role where uid = (select uid from user where uname = '张飞'))
-- 2.3/
explain select * from role r ,
(select * from user_role ur where ur.uid = (select uid from user where uname = '张飞')) t where r.rid = t.rid ;
索引SQL
-- 分布函数- CUME_DIST
/*
用途：分组内小于、等于当前rank值的行数 / 分组内总行数
应用场景：查询小于等于当前薪资（salary）的比例
*/
use mydb4;
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;
/*
rn1:
3 / 12 = 1/4 = 0.25
5 / 12 = 0.4166666666666667
rn2:
1 / 6 = 0.1666666666666667
3 / 6 = 0.5
*/
-- percent_rank
/*
用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
应用场景：不常用
*/
select
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn,
percent_rank() over(partition by dname order by salary desc) as rn2
from employee;
/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/
-- 前后函数-LAG和LEAD
/*
用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
应用场景：查询前1名同学的成绩和当前同学成绩的差值
*/
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
/*
用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
*/
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)、NTILE(n)
/*
NTH_VALUE(expr,n)
用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
*/
select
dname,
ename,
salary,
hiredate,
nth_value(salary,2) over(partition by dname order by hiredate ) as second_salary,
nth_value(salary,3) over(partition by dname order by hiredate ) as third_salary
from employee;
-- NTILE
/*
用途：将分区中的有序数据分为n个等级，记录等级数
应用场景：将每个部门员工按照入职日期分成3组
*/
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(3) over(partition by dname order by hiredate ) as nt
from employee
)t
where t.nt = 1;
create database mydb5;
use mydb5;
-- 方式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) -- 给name列创建索引
);
select * from student where name = '张三';
-- 方式2-直接创建
-- create index indexname on tablename(columnname);
create index index_gender on student(gender);
-- 方式3-修改表结构(添加索引)
-- alter table tablename add index indexname(columnname)
alter table student add index index_age(age);
-- 1、查看数据库所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
-- 2、查看表中所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
-- 3、查看表中所有索引
-- show index from table_name;
show index from student;
-- 删除索引
/*
drop index 索引名 on 表名
-- 或
alter table 表名 drop index 索引名
*/
drop index index_gender on student;
alter table student drop index index_age;
-- 索引的操作-创建索引-单列索引-唯一索引
/*
唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
*/
-- 方式1-创建表的时候直接指定
create table student2(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名)
create table student2(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double
);
create unique index index_card_id on student2(card_id);
-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num);
-- 操作-删除索引
drop index index_card_id on student2;
alter table student2 drop index index_phone_num;
-- 主键索引
show index from student2;
-- 组合索引
use mydb5;
-- 创建索引的基本语法-- 普通索引
-- create index indexname on table_name(column1(length),column2(length));
create index index_phone_name on student(phone_num,name);
-- 操作-删除索引
drop index index_phone_name on student;
-- 创建索引的基本语法-- 唯一索引
create unique index index_phone_name on student(phone_num,name);
/*
1 a
1 b
2 a
1 a 不行
*/
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';
/*
三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段 才能够进行匹配。
而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。
*/
*/
show variables like '%ft%';
use mydb5;
-- 创建表的适合添加全文索引
create table t_article (
id int primary key auto_increment ,
title varchar(255) ,
content varchar(1000) ,
writing_date date -- ,
-- fulltext (content) -- 创建全文检索
);
insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02');
insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');
-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content)
-- 添加全文索引
create fulltext index index_content on t_article(content);
-- 使用全文索引
select * from t_article where match(content) against('yo'); -- 没有结果
select * from t_article where match(content) against('you'); -- 有结果
select * from t_article where content like '%you%';
create table shop_info (
id int primary key auto_increment comment 'id',
shop_name varchar(64) not null comment '门店名称',
geom_point geometry not null comment '经纬度',
spatial key geom_index(geom_point)
);
约束SQL
-- 1：通过修改表结构添加主键
/*
create table 表名(
...
);
alter table <表名> add primary key（字段列表);
*/
use mydb1;
-- 添加单列主键
create table emp4(
eid int,
name varchar(20),
deptId int,
salary double
);
alter table emp4 add primary key(eid);
-- 添加多列主键
create table emp5(
eid int,
name varchar(20),
deptId int,
salary double
);
alter table emp5 add primary key(name, deptId);
-- 删除主键
-- 1.删除单列主键
alter table emp1 drop primary key;
-- 2.删除多列主键
alter table emp5 drop primary key;
use mydb1;
-- 自增正约束
create table t_user1 (
id int primary key auto_increment,
name varchar(20)
);
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');
delete from t_user1; -- delete删除数据之后，自增长还是在最后一个值基础上加1
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');
truncate t_user1; -- truncate删除之后，自增长从1开始
insert into t_user1 values(NULL,'张三');
insert into t_user1(name) values('李四');
-- 指定自增长的初始值
-- 方式一：创建表时指定
create table t_user2 (
id int primary key auto_increment,
name varchar(20)
)auto_increment = 100;
insert into t_user2 values(NULL,'张三');
insert into t_user2 values(NULL,'张三');
delete from t_user2;
insert into t_user2 values(NULL,'张三');
insert into t_user2 values(NULL,'张三');
truncate t_user2; -- truncate删除之后，自增长从1开始
insert into t_user2 values(NULL,'张三');
insert into t_user2 values(NULL,'张三');
-- 方式二：创建表之后指定
create table t_user3 (
id int primary key auto_increment,
name varchar(20)
);
alter table t_user3 auto_increment = 200;
insert into t_user3 values(NULL,'张三');
insert into t_user3 values(NULL,'张三');
-- 非空约束
/*
MySQL 非空约束（NOT NULL）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。
*/
-- 格式
/*
方式1：<字段名><数据类型> not null;
方式2：alter table 表名 modify 字段 类型 not null;
*/
use mydb1;
-- 1. 创建非空约束-方式1，创建表时指定
create table mydb1.t_user6 (
id int ,
name varchar(20) not null, -- 指定非空约束
address varchar(20) not null -- 指定非空约束
);
insert into t_user6(id) values(1001); -- 不可以
insert into t_user6(id,name,address) values(1001,NULL,NULL); -- 不可以
insert into t_user6(id,name,address) values(1001,'NULL','NULL'); -- 可以（字符串：NULL）
insert into t_user6(id,name,address) values(1001,'',''); -- 可以（空串）
-- 2.创建非空约束-方式2，创建表之后指定
create table t_user7 (
id int ,
name varchar(20) , -- 指定非空约束
address varchar(20) -- 指定非空约束
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;
desc t_user7;
-- 3.删除非空约束
-- alter table 表名 modify 字段 类型
alter table t_user7 modify name varchar(20) ;
alter table t_user7 modify address varchar(20) ;
-- 唯一约束
/*
唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。
*/
-- 语法：
/*
方式1：<字段名> <数据类型> unique
方式2：alter table 表名 modify 字段 类型 not null;
*/
use mydb1;
-- 1. 添加唯一约束-方式1-创建表时指定
create table t_user8 (
id int ,
name varchar(20) ,
phone_number varchar(20) unique -- 指定唯一约束
);
insert into t_user8 values(1001,'张三',138);
insert into t_user8 values(1002,'张三2',139);
insert into t_user8 values(1003,'张三3',NULL);
insert into t_user8 values(1004,'张三4',NULL); -- 在MySQL中NULL和任何值都不相同 甚至和自己都不相同
-- 2. 添加唯一约束-方式1-创建表之后指定
-- 格式：alter table 表名 add constraint 约束名 unique(列);
create table t_user9 (
id int ,
name varchar(20) ,
phone_number varchar(20) -- 指定唯一约束
);
alter table t_user9 add constraint unique_pn unique(phone_number);
insert into t_user9 values(1001,'张三',138);
insert into t_user9 values(1002,'张三2',138);
-- 3. 删除唯一约束
-- 格式：alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_pn;
-- 默认约束
-- 1.创建默认约束
/*
方式1： <字段名> <数据类型> default <默认值>;
方式2: alter table 表名 modify 列名 类型 default 默认值;
*/
use mydb1;
-- 方式1-创建表时指定
create table t_user10 (
id int ,
name varchar(20) ,
address varchar(20) default '北京' -- 指定默认约束
);
insert into t_user10(id,name,address) values(1001,'张三','上海');
insert into t_user10 values(1002,'李四',NULL);
-- 方式2-创建表之后指定
-- alter table 表名 modify 列名 类型 default 默认值;
create table t_user11 (
id int ,
name varchar(20) ,
address varchar(20)
);
alter table t_user11 modify address varchar(20) default '深圳';
insert into t_user11(id,name) values(1001,'张三');
-- 2.删除默认约束
-- alter table <表名> change column <字段名> <类型> default null;
alter table t_user11 modify address varchar(20) default null;
insert into t_user11(id,name) values(1002,'李四');
-- 零填充约束(zerofill)
-- 1. 添加约束
create table t_user12 (
id int zerofill , -- 零填充约束
name varchar(20)
);
insert into t_user12 values(123, '张三');
insert into t_user12 values(1, '李四');
insert into t_user12 values(2, '王五');
-- 2.删除约束
alter table t_user12 modify id int;
-- 总结
-- 1：通过修改表结构添加主键约束
create table emp4(
eid int primary key,
name varchar(20),
deptId int,
salary double
);
-- 2:添加自增正约束
create table t_user1 (
id int primary key auto_increment,
name varchar(20)
);
-- 3：创建非空约束
create table mydb1.t_user6 (
id int ,
name varchar(20) not null, -- 指定非空约束
address varchar(20) not null -- 指定非空约束
);
-- 4：创建唯一约束
create table t_user8 (
id int ,
name varchar(20) ,
phone_number varchar(20) unique -- 指定唯一约束
);
-- 5:创建默认约束
create table t_user10 (
id int ,
name varchar(20) ,
address varchar(20) default '北京' -- 指定默认约束
);
JDBC操作
drop database if exists mydb16_jdbc;
create database if not exists mydb16_jdbc;
use mydb16_jdbc;
create table if not exists student(
sid int primary key auto_increment,
sname varchar(20),
age int
);
insert into student values(NULL,'宋江',30),(NULL,'武松',28),(NULL,'林冲',26);
select * from student;
-- 防止SQL注入
create table if not exists user(
uid int primary key auto_increment,
username varchar(20),
password varchar(20)
);
insert into user values(NULL,'zhangsan','123456'),(NULL,'lisi','888888');
-- SQL注入
drop table if exists user;
create table user(
uid int primary key auto_increment,
username varchar(20),
password varchar(20)
);
insert into user values(NULL, 'zhangsan','123456'),(NULL,'lisi','888888');
SQL优化
use mydb13_optimize;
CREATE TABLE tb_user
(
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(45) NOT NULL,
password varchar(96) NOT NULL,
name varchar(45) NOT NULL,
birthday datetime DEFAULT NULL,
sex char(1) DEFAULT NULL,
email varchar(45) DEFAULT NULL,
phone varchar(45) DEFAULT NULL,
qq varchar(32) DEFAULT NULL,
status varchar(32) NOT NULL COMMENT '用户状态',
create_time datetime NOT NULL,
update_time datetime DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_user_username (username)
) ;
-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
-- 3、加载数据
-- 结论：当通过load向表加载数据时，尽量保证文件中的主键有序，这样可以提高执行效率
/*
脚本文件介绍 :
sql1.log ----> 主键有序
sql2.log ----> 主键无序
*/
-- 主键有序 -22.617s
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
truncate table tb_user;
-- 主键无序-81.739s
load data local infile 'D:\\sql_data\\sql2.log' into table tb_user fields terminated by ',' lines terminated by '\n';
-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
SET UNIQUE_CHECKS=1;
select * from tb_user
-- 优化order by语句
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
-- 创建组合索引
create index idx_emp_age_salary on emp(age,salary);
-- 排序,order by
explain select * from emp order by age; -- Using filesort
explain select * from emp order by age,salary; -- Using filesort
explain select id from emp order by age; -- Using index
explain select id,age from emp order by age; -- Using index
explain select id,age,salary,name from emp order by age; -- Using filesort
-- order by后边的多个排序字段要求尽量排序方式相同
explain select id,age from emp order by age asc, salary desc; -- Using index; Using filesort
explain select id,age from emp order by age desc, salary desc; -- Backward index scan; Using index
-- order by后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from emp order by salary,age; -- Using index; Using filesort
show variables like 'max_length_for_sort_data'; -- 4096
show variables like 'sort_buffer_size';
-- 优化limit
select count(*) from tb_user;
select * from tb_user limit 0,10;
explain select * from tb_user limit 900000,10; -- 0.684
explain select * from tb_user a, (select id from tb_user order by id limit 900000,10) b where a.id = b.id; -- 0.486
explain select * from tb_user where id > 900000 limit 10;
drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;
