四、sql

1. case，分为简单case函数和case搜索函数，只返回第一个符合条件的值
   1. case搜索函数：select a,b,c  case when 判断条件1 then 成功结果1 else 不成功结果1  when 判断条件2  then 成功条件2 …… end as d from table ：从tale中查找列a,b,c，将符合条件的结果作为第4列d展示出来，李毅中
   2. 简单case函数：select a,b,c  case c when 条件1 then 成功结果1 else 不成功结果1  when 条件2  then 成功条件2 …… end as d from table，简单case函数编写受限，无法写判断式

      select case when age<6 then 'true'
      else 'false' end
      from table

       

2. 常用语句：where>group by>HAVING>order by
   1. 查询班里不叫张三的学生数量，且数量大于2的班级和对应的学生数量，按照数量倒叙排列：select count(id) studentCount,class from student where name != "张三" group by class Having studentCount>2 order by StudentCount desc
   2. 表1student：id,name,sex,class,created,modified  表2 sex_info:id,sex_name 表1中的sex与表2中的id是同一个字段
      1. inner join内连接，如果表中至少有一个匹配，则返回行，eg：查询学生姓名和性别名称，select student.name,sex_info.sex_name from student inner join sex_info on student.sex=sex_info.id
      2. left join左连接，即使右表无匹配，也从左表返回所有行，eg：查询id为1的同学名称和性别名称，select student.name,sex_info.sext_name from student left join sex_info on student.sex=sex_info.id where student.id=1，如果没有匹配的sex_info.sex_name则该字段值返回null
      3. right join右连接，即使左表无匹配，也从右表中返回所有行，eg：查询性别为3的同学姓名和性别名称，select student.name,sex_info.sex_name from student right join sex_info on student.sex=sex_info.id where student.id=3，如果没有匹配的student.name则字段值返回null
      4. left join+union +right join全连接，只有一个表中存在匹配，则返回，union用于和并多个select语句的结果，eg：查询性别为3或者名字为张三的同学的姓名和性别名称，select student.name,sex_info.sex_name from student left join sex_info on student.sex=sex_info.id where name=“张三” union select student.name,sex_info.sex from student right join sex_info on student.sex=sex_info.id where sex_info.id=3
   3. AVG()返回平均值、count()计数、max()返回最大值、min()返回最小值、sum()求和、ucase()将字段转为大写、lcase()将字段改为小写、len()返回长度、round()小数点位四舍五入
   4. limit （m,n）从m开始取，取n位，即取m+n位
   5. 查找入职时间倒数第三的员工： select * from employees where hire_date=(slect distinct hire_date from employees order by hire_date desc limit 2,1);
   6. 查找部门领导薪资salaries信息，部门 dept_manager，薪资 salaries：select s.*,d.dep_no from salaries s,dept_manager d where s.emp_no=d.emp_no and s.to_date='9999-01-01' and d.to_date='9999-01-01';
   7. 查询发薪水超过 16 次的员工 id 及次数 t： select emp_no,count(to_date) as t from salaries group by emp_no having t>16;
   8. 查询非部门领导员编号：select emp_no from employees where emp_no not in (select emp_no from  dept_manager);
   9. 批量插入数据：insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
   10. 删除重复数据，重复保留最小id：delete from table where id not in (select min(id) from table group by emp_no);
   11. update+replace,讲序号为 1 的数据改为 2：update table set no=replace(no,1,2)
   12. 将titles_test表名修改为titles_2017：alter table title_test rename to titles_2017
   13. 查询各 job 的平均分数，保留三位小数按平均分降序排列：select job,round(avg(score),3) as avg from grade group by job order by avg desc;
   14. 查找表所在的数据库：SELECT table_schema FROM information_schema.TABLES WHERE table_name = '表名';
3. 、慢查询：
   1. 造成因素：
      1. 数据库层面：没有索引或索引错误，隐式转换(找不到合适索引，全表扫描，比如判断条件where左右字符集类型不一致)、执行计划错误（mysql本身不足引起）、数据巨大、MDL锁(比如ddl开始时，针对同一个表的长查询还没结束，后续写操作阻塞，导致thread running飙高)、并发更新同一行、数据分布不均、表结构设计等
      2. 受到io影响：磁盘io被占用，raid卡充放电、重置等
      3. 网络层面：网络丢包、重传，网卡慢、比如大字段，网络链路变长
   2. 避免方法：估算数据量、使用索引、分析sql执行计划(在sql语句前添加explain关键字)、优化sql(对于添加的索引列考虑失效是否会引起全表扫描)、使用慢查询日志分析(show table like 'slow_query%'/'long_query_time'，查询慢查询开启状态及慢查询存储日志路径/查询超过多少s才记录)
4. mysql-关系型数据库：使用关系模型来组织数据的数据库，易理解，使用方便，易于维护，但效率低、性能差；非关系型数据库，只适合存储简单的数据
5. mysql读写分离：查询对数据服务压力大，耗时长，将查询数据分给从机完成，主机完成删改，减少主机压力，提高并发吞吐和负载能力
6. 消息队列mq作用：
   1. 将产生的数据放到mq中，需要的系统自行读取，完成解耦；避免一个请求等待时间过长(多个系统写库时间叠加)、异步处理；避免高峰期数据大量涌入数据库，写入mq中，可以慢慢拉取请求进行处理，削峰；
   2. 缺点：系统可用率低，mq挂掉影响大、系统复杂度高、一致性问题，可能出现有的处理完成，有的还没有，数据不一致
   3. kafka：高吞吐、支持大规模topic需要增加机器，延迟在ms以内，可用性高、消息0丢失、功能简单，主要支持简单的mq功能
7. 优化sql语句：尽量减少子查询、少用like、当存在大链表且数据较多并且连接语句包含or的时候，建议用union/union all代替等
8. 数据库重启：net stop mysql && net start mysql
9. drop和delete和truncate的区别:
   1. delete删除操作可回滚，truncate、drop都不可以；delete可以是table和view，truncate只能是table
   2. 表被truncate后，该表和索引占用的空间会恢复初始值；delete操作不会减少占用空间；drop语句将占用空间释放
   3. delete、truncate删除数据，drop删除整个表(结构和数据)
10. 事务特点：
    1. 原子性：事务包含的操作要么全部成功，要么全部失败回滚
    2. 一致性：事务必须使数据从一个一致性状态变成另一个一致性状态，如A、B互相转账，怎么转事务结束后sum还是一样的
    3. 隔离性：当多个用户并发访问数据库，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他事务的操作干扰，多个并发事务之间要相互隔离
    4. 持久性：一个事务一旦被提交了，那么对于数据库中的数据的修改就是永久性的
11. 索引的优缺点：
    1. 优点：
       1. 通过创造唯一性索引，确保数据唯一性
       2. 大大加快数据检索能力，加快表与表之间的连接
       3. 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
       4. 可以在查询中使用优化隐藏器，提高系统性能
    2. 缺点：
       1. 创建和维护需要时间，而且时间的量随数据量增加
       2. 索引占用物理空间
       3. 对表中的数据增删改的时候，索引也需要动态维护，降低了数据的维护速度