SQL笔记

SQL语法
O. SQL语句不区分大小写,可以单行或多行书写，以分号结尾
O. 单行注释:#注释内容, 多行注释:*注释内容*

SQL分类:DDL、DML、DQL、DCL

DDL:用来定义数据库对象(数据库，表，字段),该部分可用软件操作,比如
        Sqlyog,Navicat,DataGrip等工具都可

DML:用来表数据增、删、改操作
 O,添加数据
   给指定字段添加数据 insert into user(id,name) values(1,'aile');
   给全部字段添加数据 insert into user values(1,'aile','男',18);
   批量添加数据           insert into user values(1,'aile','男',18),(2,'mike','男',25);
 O,修改数据                update user set name = 'aile' where id = 1;
 O,删除数据                delete from user where gender = '男';

DQL:用来查询数据库中表的记录
 O,总体结构 SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 
                    HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
 O,基础查询 select distinct address '家庭住址' from user;
 O,条件查询 select * from user where gender = '男' and age < 24;
            BETWEEN .AND . 范围之内(含最小、最大值)
            IN(.)   在in之后的列表中的值，多选一
            LIKE    占位符 模糊匹配(_匹配单个字符, %匹配任意个字符)
            IS NULL 是NULL
            AND 或 && 并且 (多个条件同时成立)
            OR 或 ||  或者 (多个条件任意一个成立)
            NOT 或 !  非 , 不是
 O,聚合函数 select avg(age) from user;
            count 统计数量
            max 最大值
            min 最小值
            avg 平均值
            sum 求和
 O,分组查询 查询年龄小于20的员工 , 并根据地址分组 , 获取员工数量大于等于3的地址
            select address, count(*) address_count from aile where age < 20 group by address having address_count >= 3;
 O,排序查询 select * from aile order by age desc;
 O,分页查询 select * from aile limit 1,10;

DCL:数据控制语言,用来管理数据库用户、控制数据库的访问权限

函数
 O,字符串函数 update aile set workno = lpad(workno, 5, '0');
             CONCAT(S1,S2,...Sn) 字符串拼接，将S1，S2，... Sn拼接成一个字符串
             LOWER(str) 将字符串str全部转为小写
             UPPER(str) 将字符串str全部转为大写
             LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
             RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
             TRIM(str) 去掉字符串头部和尾部的空格
             SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
 O,数值函数  select round(2.344,2);
             CEIL(x) 向上取整
             FLOOR(x) 向下取整
             MOD(x,y) 返回x/y的模
             RAND() 返回0~1内的随机数
             ROUND(x,y) 求参数x的四舍五入的值，保留y位小数
 O,日期函数   select name, datediff(curdate(), entrydate) as 'entrydays' from aile order by entrydays desc;
             CURDATE() 返回当前日期
             CURTIME() 返回当前时间
             NOW() 返回当前日期和时间
             YEAR(date) 获取指定date的年份
             MONTH(date) 获取指定date的月份
             DAY(date) 获取指定date的日期
             DATE_ADD(date, INTERVAL exprtype) 返回一个日期/时间值加上一个时间间隔expr后的时间值
             DATEDIFF(date1,date2) 返回起始时间date1 和 结束时间date2之间的天数
 O,流程函数
             IF(value , t , f)  
                  如果value为true，则返回t，否则返回f
             IFNULL(value1 , value2) 
                  如果value1不为空，返回value1，否则返回value2
             CASE WHEN [ val1 ] THEN [res1] ...ELSE [ default ] END
                  如果val1为true，返回res1，... 否则返回default默认值
             CASE [ expr ] WHEN [ val1 ] THEN[res1] ... ELSE [ default ] END  
                  如果expr的值等于val1，返回res1，... 否则返回default默认值

O,其他函数

convert (DECIMAL(18, 2), X) 
小数点保留两位,比如X为3.180->那么结果为3.18

convert(varchar(20),'2022-08-30')
指定格式: 2022-08-30

convert(datetime,convert(varchar(20),'2022-08-30'))
指定时间格式: 2022-08-30 00:00:00:000

convert(varchar(100), getdate(), 20)
当前时间: 2022-09-06 11:12:27

convert(varchar(20),getdate())
当前时间: 09  6 2022 11:35AM

convert(datetime,CONVERT(varchar(20),getdate()))
当前时间: 2022-09-06 11:36:00.000

DATEDIFF(MINUTE,convert(datetime,CONVERT(varchar(20),'2022-08-30')),'2022-08-30 01:20:00:000')
2022-08-30 00:00:00:000和2022-08-30 01:20:00:000间隔80分钟

case when s_score>70 then 1 else 0 end 
如果分数大于70那么1,否则0

max(case when sd.name = 'XXYY' then s.value else 0 end) as xy
取字段最大值输出

(case when b.va <= c.w1 or b.va >= c.w2 then 1 else 0 end) aaa
一个样式

row_number()over(partition by id order by date asc) as rn
根据id分区,时间升序获取rn序号的数据列表,其中rn=0就是最小date的id

charindex('a' , “dfdseaett”) > 0 
查找前字符串在后字符串中的位置，这个在第6个，查询是否存在，否则为0

多表查询--员工表emp和部门表dept,其中左外连接和右外连接是可以相互替换的,常用左外连接
 O,内连接:   查询两张表交集部分的数据
         例:查询每一个员工的姓名,及关联的部门的名称 
         select e.name, d.name from emp e join dept d on e.dept_id = d.id;

 O,左外连接: 查询表1(左表)的所有数据，也包含表1和表2交集部分的数据
         例:查询emp表的所有数据, 和对应的部门信息
         select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

 O,右外连接: 查询表2(右表)的所有数据，也包含表1和表2交集部分的数据
         例:查询dept表的所有数据, 和对应的员工信息
         select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

 O,自连接:   自己连接自己，也就是把一张表连接查询多次,必须用别名
             例:查询所有员工emp及其领导的名字 emp,如果员工没有领导,也需要查询出来
             select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;

 O,联合查询  把多次查询的结果合并起来，形成一个新的查询结果集
             例:将薪资高于10000的员工 , 和年龄小于30岁的员工全部查询出来.
         select * from emp where salary > 10000 union all select * from emp where age < 30;

 O,子查询    嵌套SELECT语句，称为嵌套查询，又称子查询
   标量子查询（子查询结果为单个值）,常用的操作符：= <> > >= < <=
             例:根据 "销售部" 部门ID, 查询员工信息
             select * from emp where dept_id = (select id from dept where name = '销售部');

   列子查询(子查询结果为一列,一个字段),常用的操作符
             IN 在指定的集合范围之内，多选一
             NOT IN 不在指定的集合范围之内
             ANY 子查询返回列表中，有任意一个满足即可
             SOME 与ANY等同，使用SOME的地方都可以使用ANY
             ALL 子查询返回列表的所有值都必须满足

             例:查询比 财务部所有人工资 都高的员工信息
              select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );
   
   行子查询(子查询结果为一行,多个字段),常用的操作符：= 、<> 、IN 、NOT IN
             例:查询与 "aile" 的薪资及直属领导 相同的员工信息
             select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'aile');

   表子查询(子查询结果为多行多列),常用的操作符：IN
             例:查询与 "aile" , "mike" 的职位和薪资相同的员工信息
             select job, salary from emp where name = 'aile' or name = 'mike';

事务 把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么成功，要么失败
 开启事务 START TRANSACTION 或 BEGIN ; 
 提交事务 COMMIT;
 回滚事务 ROLLBACK;

start transaction;
update account set money = money - 100 where name = 'aile';
测试异常信息
update account set money=money+100 where name='mike';
commit;

 索引 高效获取数据的数据结构(有序),实现快速查询,但是DML效率较低
 
 O,索引分类
 分类     含义                                                                                                     特点                                关键字
 主键索引 针对于表中主键创建的索引                                                   默认自动创建, 只能有一个        PRIMARY 
 唯一索引 避免同一个表中某数据列中的值重复                                     可以有多个                               UNIQUE
 常规索引 快速定位特定数据                                                                  可以有多个
 全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值  可以有多个                               FULLTEXT

创建普通索引    CREATE INDEX idx_name ON aile(name);
创建唯一索引    CREATE UNIQUE INDEX idx_phone ON aile(phone);
创建联合索引    CREATE INDEX idx_pro_age_sta ON aile(profession,age,status);
查看所有的索引 show index from aile;

性能分析
查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志 记录了所有执行时间超过指定参数的所有SQL语句的日志
profile详情  能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
explain 获取如何执行SELECT 语句的信息

索引失效情况
O,索引列要是运算会失效
O,字符串不加引号会失效
O,头部模糊匹配会失效
O,用or连接的字段要是某个字段没有索引,其他有索引,也会失效

SQL优化

插入优化
批量插入多条数据: Insert into tb_aile values(1,'aile'),(2,'mike');
大批量插入数据  通过load命令,使用加载文件快速导入
                          O,连接数据库     mysql –-local-infile -u root -p 
                          O,设置导入开关   set global local_infile = 1; 
                          O,load加载数据   load data local infile '/root/load_aile.log' into table tb_aile 
                                                      fields terminated by ',' lines terminated by '\n' ;

主键优化
O,降低主键的长度
O,使用AUTO_INCREMENT自增主键
O,尽量不要使用UUID做主键或者是其他自然主键
O,避免对主键的修改

order by优化
O,多字段排序时，也遵循最左前缀法则
O,尽量使用覆盖索引
O,多字段排序, 一个升序一个降序，此时需要联合索引

group by优化
O,在分组操作时,通过索引来提高效率
O,在分组操作时,索引的使用也是满足最左前缀法则的

limit优化
O,通过覆盖索引+子查询进行优化

count优化
O,count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)

update优化
O,针对索引的更改速度最快,比其他字段

视图
只有查询SQL语句,动态生成的,重复多次使用的,安全的虚拟表
创建视图  create or replace view aile_v as select id,name from aile where id >0;
查询视图  show create view aile_v;
                select * from aile_v;
修改视图  alter view aile_v as select id,name from aile where id > 0;
删除视图  drop view if exists aile_v;

存储过程--创建一次,多次调用
事先编译并存储的一段可直接复用,可传参,可返回,高效率SQL语句集合

创建  create procedure p1()
      begin
           select count(*) from aile;
      end;
调用  call p1();
查看  show create procedure p1;
删除  drop procedure if exists p1;

变量
O,系统变量 服务器提供,属于服务器层面.分为全局变量（GLOBAL）、会话变量（SESSION）
    查看系统变量 show session variables ;
    设置系统变量 set session autocommit = 1;
O,用户定义变量 当前连接,用户根据需要自己定义的变量
    赋值 set @myname = 'aile';
    使用 select @myname;
O,局部变量 根据定义局部生效的变量,访问需要DECLARE声明,在存储过程BEGIN...END块使用
    create procedure p2()
    begin
         declare stu_count int default 0;--声明
         select count(*) into stu_count from student;--赋值
         select stu_count;//使用
    end;
    
    call p2();--调用

if(条件判断)和参数(in,out,inout)

--案例1
create procedure p4(in score int, out result varchar(10))--in,输入参数,out,输出参数
begin
     if score >= 85 then
        set result = '优秀';
     elseif score >= 60 then
        set result = '及格';
     else
        set result = '不及格';
     end if;
end;

call p4(18, @result);--调用存储过程p4
select @result;--输出结果

--案例2
create procedure p5(inout score double)--inout,既是输入参数,又是输出参数
begin
     set score = score * 0.5;
end;

set @score = 198;--设置输入
call p5(@score);--调用存储过程p5
select @score;--输出结果

case(流程控制)
create procedure p6(in age int)
begin
     declare result varchar(10);
     case
         when age <=1              then
              set result = '婴儿';
         when age > 1 and age <= 3 then
              set result = '幼儿';
         when age > 3 and age <= 6 then
              set result = '小童';
         when age > 6 and age <= 12 then
              set result = '儿童';
         else
              set result := '非法参数';
     end case ;

     select concat('您输入的年龄为: ',age, ', 所属的时期为: ',result);--输出结果
end;

call p6(16);--调用存储过程p6

while(循环控制)
create procedure p7(in n int)--in,输入参数
begin
     declare total int default 0;--声明
     while n>0 do--循环条件
           set total = total + n;--赋值
           set n = n - 1;
     end while;

     select total;--使用
end;

call p7(100);

repeat(先执行再循环),可参照上
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
     declare total int default 0;
     repeat
          set total := total + n;
          set n := n - 1;
     until n <= 0
     end repeat;

     select total;
end;

call p8(100);

loop(灵活循环,退出循环,进入循环)
create procedure p10(in n int)
begin
     declare total int default 0;--声明

     sum:loop

        if n<=0 then
             leave sum;--满足条件退出循环sum
        end if;

        if n%2 = 1 then
             set n = n - 1;
             iterate sum;--满足条件,进入循环sum
        end if;

        set total = total + n;--赋值
        set n = n - 1;
     end loop sum;

     select total;--使用
end;

call p10(100);--调用存储过程p10

游标-- 一般需要异常Handler 处理

用来存储查询结果集的数据类型,用在存储过程中
声明 DECLARE 游标名称 CURSOR FOR 查询语句 ;
打开 OPEN 游标名称 ;
获取 FETCH 游标名称 INTO 变量 [, 变量 ] ;
关闭 CLOSE 游标名称 ;

Handler 用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤,异常处理

Cusor+handler+procedure案例:

create procedure p12(in uage int)
begin
     declare uname varchar(100);--声明局部变量
     declare upro varchar(100);

     declare u_cursor cursor for select name,profession from tb_user where age <=uage;--声明游标
     
     declare exit handler for not found close u_cursor;--当SQL语句执行抛出异常，将关闭游标u_cursor并退出

     drop table if exists tb_user_pro;
     create table if not exists tb_user_pro(--新建新表tb_user_pro
         id int primary key auto_increment,
         name varchar(100),
         profession varchar(100)
     );

     open u_cursor;--打开游标
     while true do
         fetch u_cursor into uname,upro;--获取游标记录,对应字段name=uname,profession=upro
         insert into tb_user_pro values (null, uname, upro);--插入数据到tb_user_pro
     end while;
     close u_cursor;--关闭游标
end;

call p12(30);--调用存储过程

触发器
在增,删,改之前或之后触发并执行定义的SQL语句集合,保证数据完整性,数据检验

案例:
通过触发器记录 tb_aile 表的数据变更日志，将变更日志插入到日志表aile_logs中, 包含增加,修改 , 删除

日志表
create table aile_logs(
   id int(11) not null auto_increment,
   operation varchar(20) not null comment '操作类型, insert/update/delete',
   operate_time datetime not null comment '操作时间',
   operate_params varchar(500) comment '操作参数',
   primary key(`id`)
)engine=innodb default charset=utf8;

插入触发器
create trigger tb_aile_insert_trigger
    after insert on tb_aile for each row
begin
    insert into 
        aile_logs(id, operation,operate_time,operate_params)
    VALUES
        (null, 'insert', now(), concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone=', NEW.phone));
end;

插入触发器测试
-- 查看
show triggers ;

-- 插入数据到tb_aile
insert into 
      tb_aile(id, name, phone) 
VALUES 
     (26,'三皇子','18809091212');

修改触发器
create trigger tb_aile_update_trigger
    after update on tb_aile for each row
begin
    insert into 
        aile_logs(id, operation, operate_time, operate_params)
    VALUES
        (null, 'update', now(), concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone' 
                                  | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=',new.phone));
end;

修改触发器测试
-- 查看
show triggers ;

-- 更新
update tb_aile set name = 'aile' where id = 12;

删除触发器
create trigger tb_aile_delete_trigger
    after delete on tb_aile for each row
begin
    insert into 
         aile_logs(id, operation, operate_time, operate_params)
    VALUES
         (null, 'delete', now(), concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=',old.phone));
end;

删除触发器测试
-- 查看
show triggers ;

-- 删除数据
delete from tb_aile where id = 12;

锁
计算机协调多个进程或线程并发访问某一资源的机制,保证数据并发访问的一致性、有效性,有全局锁,表级锁,行级锁