常用SQL

合集 - 数据库库(5)
1.Mysql高级-day032.Mysql高级-day023.Mysql高级-day044.Mysql高级-day01
5.常用SQL
收起

多表查询


-- 多表查询-数据准备

  

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;

posted on   pony1223  阅读(18)  评论(0)  编辑  收藏  举报

相关博文：
· Mysql高级-day02
· Mysql高级-day03
· mysql 存储过程详解 临时表 加案例
· mysql多表操作
· mysql基础
阅读排行：
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示