• 索引+sql练习优化

    目录

    优势劣势

    什么时候用索引

    Expain性能分析

     分析字段：

    插入100w数据如何做到最快

     1.创建函数（随机产生编号以及随机名字）

     2.创建存储过程插入数据（利用上面两个函数得到随机编号以及名字）

    流程：

     3.创建索引

    用图表示多字段索引的执行

     练习

     索引是如何找到数据的

    注意事项

     小总结

    一些建议

    关联查询

    关联查询实例 

    关联查询小结论：

    关联优化测试

    索引对于分组查询的影响

    排序分组优化

     还一个很优化的点（覆盖索引）：

    作业（sql练习+优化）

     

    优势劣势

    通过索引提高数据检索效率，降低IO成本 ，但是用了索引也会降低更新的效率，每次修改都会导致我们的索引文件里面的信息发送变化，并且内存消耗up

     索引也可以理解为一张表，里面的索引字段指向实体表记录

    什么时候用索引

     频繁作为查询条件的、关联查询的、分组排序的、唯一索引的

    Expain性能分析

    作用：

    使用Explain关键字可以模拟优化器（就是之前那个Optimizer优化器）执行SQL查询语句，得到mysql是如何处理sql语句的，进行分析（查看执行计划）

    使用：

     分析字段：

    id表示一趟独立的查询，一个sql的躺数越少越好

     select_type

     type

    最关键三：range、index、all

     key和key_len和rows

     key_len如何计算索引长度

     EXTRA

    目的：减少全表扫描，增加性能

     子查询（SUBQUERY）：

     范围查询（DEPENDENT SUBQUERY）

      不可用缓存查询：

     sql未命中，sql不一样——>当出现变量时，sql就肯定不一样了；

      All问题：

      All table scan，将全表进行遍历找到匹配的行；

      index索引

     出现index是sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行排序分组；

     范围查询：

     group by先进行排序后进行分组

     用了索引之后发现速度发生百倍优化（reset cache）

     两个表关联，关联字段要建索引

    where条件后的字段用了索引

     统计数量count（*）：MyISAM的话，他统计了表中数据的数量，InnoDB没有统计会真的去打开表进行扫描

    EXPLAIN SELECT * FROM t_dept

    字段解析 ：

     id指的是执行顺序，type指的是黄色红色黑色那些预警，key_len指的是where条件字段长度（长度越长，越容易命中），rows值的行数（物理扫描的行数，越少越好速度越快），Extra额外字段一般看group by，other by，关联查询； 

    插入100w数据如何做到最快

    1.我们可以将100w条插入语句进行拼接，让他变为一条语句，速度肯定快一些

    2.我们可以取消mysql的自动提交，因为100w条数据提交100w次和提交1次

    肯定不一样；

    3.使用多线程

    mysql的主从复制

    主机从机用一个binlog，里面有共有函数

     1.创建函数（随机产生编号以及随机名字）

     

     2.创建存储过程插入数据（利用上面两个函数得到随机编号以及名字）

    1. CREATE TABLE `dept` (
    2.  `id` INT(11) NOT NULL AUTO_INCREMENT,
    3.  `deptName` VARCHAR(30) DEFAULT NULL,
    4.  `address` VARCHAR(40) DEFAULT NULL,
    5.  ceo INT NULL ,
    6.  PRIMARY KEY (`id`)
    7. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    8.  
    9.  
    10. CREATE TABLE `emp` (
    11.  `id` INT(11) NOT NULL AUTO_INCREMENT,
    12.  `empno` INT NOT NULL ,
    13.  `name` VARCHAR(20) DEFAULT NULL,
    14.  `age` INT(3) DEFAULT NULL,
    15.  `deptId` INT(11) DEFAULT NULL,
    16.  PRIMARY KEY (`id`)
    17.  #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
    18. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    19.  
    20. #开启自定义函数配置的开启
    21. SET GLOBAL log_bin_trust_function_creators=1;
    22.  
    23.  
    24.  
    25.  
    26.  #随机生成字符串函数
    27. DELIMITER $$
    28. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    29. BEGIN    
    30. DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    31.  DECLARE return_str VARCHAR(255) DEFAULT '';
    32.  DECLARE i INT DEFAULT 0;
    33.  WHILE i < n DO  
    34.  SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
    35.  SET i = i + 1;
    36.  END WHILE;
    37.  RETURN return_str;
    38. END $$
    39.  
    40. USE mydb
    41.  
    42. #用于随机产生多少到多少的编号
    43. DELIMITER $$
    44. CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
    45. BEGIN   
    46.  DECLARE i INT DEFAULT 0;  
    47.  SET i = FLOOR(from_num +RAND()*(to_num -from_num+1))   ;
    48. RETURN i;  
    49.  END$$ 
    50.  
    51. #假如要删除
    52. #drop function rand_num;
    53.  
    54.  
    55. #假如要删除
    56. #drop function rand_string;
    57.  
    58.  
    59. #插入五十万条数据
    60. DELIMITER $$
    61. CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
    62. BEGIN  
    63. DECLARE i INT DEFAULT 0;   
    64. #set autocommit =0 把autocommit设置成0，关闭自动提交  
    65.  SET autocommit = 0;    
    66.  REPEAT  
    67.  SET i = i + 1;  
    68.  INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   , rand_num(30,50),rand_num(1,10000));  
    69.  UNTIL i = max_num  
    70.  END REPEAT;  
    71.  COMMIT;  
    72.  END$$ 
    73.  
    74. #删除
    75. # DELIMITER ;
    76. # drop PROCEDURE insert_emp;
    77.  
    78.  
    79.  
    80. #执行存储过程，往dept表添加随机数据
    81. DELIMITER $$
    82. CREATE PROCEDURE `insert_dept`(  max_num INT )
    83. BEGIN  
    84. DECLARE i INT DEFAULT 0;   
    85.  SET autocommit = 0;    
    86.  REPEAT  
    87.  SET i = i + 1;  
    88.  INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
    89.  UNTIL i = max_num  
    90.  END REPEAT;  
    91.  COMMIT;  
    92.  END$$
    93.  
    94. #删除
    95. # DELIMITER ;
    96. # drop PROCEDURE insert_dept;
    97.  
    98.  
    99. #插入1w条数据
    100. #执行存储过程，往dept表添加1万条数据
    101. DELIMITER ;
    102. CALL insert_dept(10000); 
    103.  
    104.  
    105. #执行存储过程，往emp表添加50万条数据
    106. DELIMITER ;
    107. CALL insert_emp(100000,500000); 
    108.  
    109. SELECT COUNT(*) FROM emp;
    110. SELECT COUNT(*) FROM dept;

     像这么多数据，我们进行查询就需要用到索引了，我们要执行下一个sql就要删除当前sql的索引

    流程：

    需要mysql承认你取出来的字符串

    1. 查看表中索引

    SHOW INDEX FROM t_emp;

    2.我们的索引也是一张表，在information数据库中，名字为STATISTICS

     查看一下索引表，主键索引不能删除

     3.创建索引

    单字段下

    先查询一下，发现explain估算49w多行

    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`age`=30;


    CREATE INDEX idx_age ON emp(age);

     创建完索引后，就只有4w行了，速度up

    0.03->0.007

    多字段下 

    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd';  

    CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

    速度由0.082->忽略不计

    用图表示多字段索引的执行

    最佳左前缀：从左边到右边按顺序执行，不然会断开

    1. 如果系统经常出现的sql如下：
    2.   EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   
    3. 或者
    4. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   
    5.  那原来的idx_age_deptid_name 还能否正常使用？

    第二个不能正常执行，没有遵循从左到右的顺序执行 ，需要环环命中——>同时也体现出全值匹配，字段与索引一定要对应

     练习

    1. #执行删除
    2. CALL proc_drop_index("mydb","emp");
    3.  
    4. #在索引表中查询指定索引 （非主键）
    5. SELECT index_name FROM information_schema.`STATISTICS` WHERE TABLE_NAME='t_emp' 
    6. AND TABLE_SCHEMA='mydb' AND INDEX_NAME <>'PRIMARY' AND SEQ_IN_INDEX=1;
    7.  
    8.  
    9. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`age`=30; #0.03 0.007
    10. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4
    11. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd';  
    12.  
    13. #创建索引
    14. CREATE INDEX idx_age ON emp(age);
    15. CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
    16.  
    17. SHOW INDEX FROM emp; 
    18.  
    19. #模糊查询
    20. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` LIKE 'abc%';#0.8->0.016
    21.  
    22. #这种会导致索引失效，别用函数
    23. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE  LEFT(emp.`name`,3)='abc';#0.8
    24.  
    25. #创建索引
    26. CREATE INDEX idx_name ON emp(NAME);
    27.  
    28. #执行删除
    29. CALL proc_drop_index("mydb","emp");
    30. CALL proc_drop_index("mydb","dept");
    31.  
    32. #范围查询，范围右边的字段索引是失效的（这种右边是根据索引来判断的）
    33.  EXPLAIN SELECT SQL_NO_CACHE * FROM emp #0.5->0.052,type:range
    34.  WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; #最佳效率0.004
    35.  
    36. #创建索引
    37. CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
    38. CREATE INDEX idx_age_deptid_name ON emp(age,NAME,deptid);
    39.  
    40.  
    41. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` <> 'abc';
    42.  
    43. #创建索引,对于上述失效！=会导致索引失效
    44. CREATE INDEX idx_name ON emp(NAME);
    45.  
    46. EXPLAIN SELECT * FROM emp WHERE age IS NULL #0.2->0.001
    47.   
    48. #这种为not都会失效  
    49. EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL;#0.04->0.009,索引失效，type为ALL
    50.  
    51. #创建索引
    52. CREATE INDEX idx_age ON emp(age);
    53.  
    54. #我们的索引结构平衡树是按照a-z，如果首字母都不能确定，索引就会失效
    55. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.`name` LIKE '%abc%';#0.329->0.274
    56.  
    57. #创建索引
    58. CREATE INDEX idx_name ON emp(NAME);	
    59.  
    60.  
    61. #执行删除
    62. CALL proc_drop_index("mydb","emp");
    63. CALL proc_drop_index("mydb","dept");
    64.  
    65.  
    66. #类型转换也会造成索引失效
    67. #有几个字段就建立几个字段的索引

     索引是如何找到数据的

     (24条消息) (3)MySQL是如何通过【索引】找到一条【真实的数据】_孤鸿寄语LWQ的博客-CSDN博客_mysql如何通过索引查询

    注意事项

    注意sql编写，防止索引失效 

     小总结

     1. 记得最佳左前法则

     2.不能使用函数：abs，max，<>....

     3.不能用like前缀%xxx通配符

     4.is not null不行

     5.类型转换也不行

    一些建议

    1.当选择组合索引时，我们可以将过滤性较好的字段放在索引字段靠前位置，因为这样筛选出来到下一个树，可能节点就相对变少了，甚至为1，效率更高；

    2.当选择组合索引，要尽量包含where后更多字段

    3.尽量避免索引失效问题

    关联查询

    场景：

    当两个表没有关联字段，然后进行关联查询，会两个表都进行全局扫描，从而出现笛卡尔积

    如何关联：

    当在关联查询场景下，我们关联查询->首先会扫描驱动表的第一行数据，然后根据第一行数据对被驱动表进行扫描，找到的数据合为一行为总的一行数据；

    结论：

    1.驱动表是一定会被全局扫描的，而被驱动表不一定，所以我们索引建在被驱动表上，驱动表因为一定全部被扫所以建不建索引是无所谓的；

    2.为了提升效率，我们数据量相对较小的最好作为驱动表 

    3.另外，inner join和left join不太一样，在inner join下mysql会自动选择那个为被驱动表，而不是它们的相对位置，是根据小结果集的作为驱动表；

    关联查询实例 

    1. 关联查询一下两个表 ->extra出现两表之间没有关联字段，查很慢，出现笛卡尔
    2. EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.`card`; 
    3.  
    4. #创建索引，这里我们一般都是给被驱动表建立索引
    5. CREATE INDEX Y ON book(card);
    6.  
    7. #执行删除
    8. CALL proc_drop_index("mydb","book");
    9. DROP INDEX Y ON book;
    10. DROP INDEX X ON class;
    11.  
    12. #创建索引
    13. ALTER TABLE class ADD INDEX X (card);
    14.  
    15. #inner join,mysql自己选择哪个是被驱动表——>根据索引进行判断，谁有索引就谁是被驱动
    16.  
    17.  
    18. #小数据为驱动表，扫描时间短以此提高效率
    19.  
    20. #求所有dept对应的CEO名称
    21.  
    22. #1.这里会发现是一趟查询，mysql5.7的一个优化，c表为驱动表，我们这个被驱动表为ab虚拟表
    23. #而虚拟表又不能创建索引，所以说会浪费一次优化机会，5.7更新所在之处
    24. EXPLAIN SELECT c.`name`,ab.name ceoname FROM t_emp c LEFT JOIN
    25. (SELECT b.`id`,a.`name` FROM t_emp a INNER JOIN t_dept b ON a.`id`=b.`CEO`)ab
    26. ON c.`deptld`=ab.id;
    27.  
    28.  
    29. #得到dept的掌门
    30. SELECT b.`id`,a.`name` FROM t_emp a INNER JOIN t_dept b ON a.`id`=b.`CEO`
    31.  
    32. #2.先查询名字和CEO
    33. SELECT ab.name,c.`name` ceoname FROM
    34. (SELECT a.`name`,b.`CEO` FROM t_emp a LEFT JOIN t_dept b ON a.`deptld`=b.`id`) ab 
    35. LEFT JOIN t_emp c ON ab.ceo=c.`id`;
    36.  
    37.  
    38. #3.最快，直接关联，不用子查询一次性两次外连接得到帮派派主（第一次得到用户那边信息，第二次根据CEO进行筛选）
    39. EXPLAIN SELECT a.`name`,c.`name` ceoname FROM t_emp a 
    40. LEFT JOIN t_dept b ON a.`deptld`=b.`id`
    41. LEFT JOIN t_emp c ON b.`CEO`=c.`id`;

    关联查询小结论：

    1.我们需要保证被驱动表join的字段以及被索引

    2.left join时，选择小表为驱动

    3.inner join：小结果集作为驱动表

    4.子查询尽量不要放在驱动表，因为5.7前虚拟表是不能用索引的，会导致效率降低

    5.能直接关联就直接关联

    关联优化测试

    这里明显有not导致索引不能使用，优化->使用left join进行关联查询，然后根据条件过滤即可 

    1. #至少两个非掌门成员的门派(先掌门编号，然后根据掌门编号在t_emp中查)
    2. SELECT * FROM t_emp a WHERE a.id NOT IN
    3. (SELECT b.CEO FROM t_dept b WHERE b.CEO IS NOT NULL);
    4.  
    5. #优化(先得到所有掌门（left join），然后条键过滤)
    6. SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.id = b.CEO
    7. WHERE b.id IS NULL;

    索引对于分组查询的影响

    (24条消息) 【mysql知识点整理】--- order by 、group by 出现Using filesort原因详解_nrsc的博客-CSDN博客_filesort 原因

    总结：

    1.分组查询order by后的字段能否使用索引->取决于后面是否接了过滤条件，如果接了则索引生效

    2.order by后面的顺序是很重要的，顺序不一样结果不一样，所以并不会被优化

    3.如果字段都升序或者降序是不影响结果的，如果不一致就会影响从而出现using filesort

    1. #执行删除
    2. CALL proc_drop_index("mydb","emp");
    3. CALL proc_drop_index("mydb","dept");
    4.  
    5.  
    6. #分组查询
    7. CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
    8. CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,empno); 
    9.  
    10.  
    11. #以下是否能使用到索引，能否去掉using filesort ，（order by想用索引必须要过滤条件）
    12. EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; 
    13.  
    14. EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10; 
    15.  
    16.  
    17.  #无过滤 不索引
    18.  
    19.  
    20. EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY deptid;
    21.  
    22. EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY   deptid,NAME; 
    23.  
    24. EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  deptid,empno;
    25.  
    26. #deptid字段需要再name前面，这顺序并不会被优化，因为结果会被影响，order by后面字段顺序是很重要的 
    27. EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  NAME,deptid;
    28.  
    29. EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
    30.  
    31.  
    32.  
    33.  
    34. #顺序错，必排序，可以都是降序或者升序，这样的话，顺序改一下还是不影响结果的
    35.  
    36.  
    37. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid DESC, NAME DESC ;
    38.  
    39.      
    40.  
    41. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid ASC, NAME DESC ;
    42.  
    43. #方向反 必排序，一升一降不能索引：Using filesort

    排序分组优化

    mysql会对索引的选择进行优化，选择一个自认为最快的索引

    如果不在字段不在索引列上，filesort有两种算法

    1.双路排序：就是两次扫描磁盘，先把所有内容扫一遍放到磁盘，扫完后再对磁盘中的内容进行排序（也就是读取行指针和order by列），对磁盘两次扫描，IO很耗时

    2.单路排序：从磁盘上读取查询的索引列，然后按照order by进行查询，他把每次读取的内容放到内存中，减少了IO的损耗

     group by与other by的区别就是：

    group by可以没有限制条件就是用索引，other by无过滤不索引

     还一个很优化的点（覆盖索引）：

    之前不是出现模糊查询like %xx、is not、other by xxx这些都会导致索引失效嘛，我们可以对查询的内容进行限制，不再是select * ，而是字段限制了，这样可以根据字段来匹配索引，以此提高效率；

    1. CREATE INDEX idx_id_age_deptid ON emp(id,age,deptid);
    2.  
    3. #可以对查询内容上索引
    4. EXPLAIN SELECT SQL_NO_CACHE NAME FROM emp WHERE age IS NOT NULL;
    5.  
    6. EXPLAIN SELECT * FROM emp WHERE NAME LIKE '%abc';
    7.  
    8. EXPLAIN SELECT SQL_NO_CACHE id,age,deptid FROM emp WHERE NAME LIKE '%abc';
    9.  
    10. #查看索引
    11. SHOW INDEX FROM emp;

    作业（sql练习+优化）

    注意：Group by的字段设置之后，前面select的字段只能包含Group by的内容和函数内容，否则会报错

    1. EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;#0.729
    2.  
    3. #执行删除
    4. CALL proc_drop_index("mydb","emp");
    5. CALL proc_drop_index("mydb","dept");
    6.  
    7. #建立索引,优先第一个idx_age_empno的索引，mysql会优选择最优的索引
    8. CREATE INDEX idx_age_empno ON emp(age,empno);
    9. CREATE INDEX idx_age_name ON emp (age,NAME);
    10. CREATE INDEX idx_age ON emp(age);
    11. CREATE INDEX idx_name ON emp(NAME);
    12. CREATE INDEX idx_id_age_deptid ON emp(id,age,deptid);
    13.  
    14. #可以对查询内容上索引
    15. EXPLAIN SELECT SQL_NO_CACHE NAME FROM emp WHERE age IS NOT NULL;
    16.  
    17. EXPLAIN SELECT * FROM emp WHERE NAME LIKE '%abc';
    18.  
    19. EXPLAIN SELECT SQL_NO_CACHE id,age,deptid FROM emp WHERE NAME LIKE '%abc';
    20.  
    21. #查看索引
    22. SHOW INDEX FROM emp;
    23.  
    24.  
    25. #1.求小弟比门派派主年龄小的信息
    26. SELECT a.name FROM t_emp a 
    27. LEFT JOIN t_dept b ON a.`deptld`=b.id
    28. LEFT JOIN t_emp c ON b.CEO=c.`id`
    29. WHERE c.`age`<a.`age`;
    30.  
    31. #优化
    32. EXPLAIN SELECT SQL_NO_CACHE a.`name`,a.`age`,c.`name`,c.`age` FROM emp a 
    33. LEFT JOIN dept b ON a.`deptId`=b.`id`
    34. LEFT JOIN emp c ON b.`ceo`=c.`id`
    35. WHERE c.`age`<a.`age`;
    36.  
    37.  
    38. #2.列出年龄比帮派平均年龄小的人员(先得到部门分组的人员信息)
    39. SELECT  c.`name`,c.`age` FROM t_emp c INNER JOIN
    40. (SELECT a.`deptld`,AVG(a.`age`) avgage FROM t_emp a WHERE a.`deptld` IS NOT NULL
    41. GROUP BY a.`deptld`)aa
    42. ON c.`deptld`=aa.deptld
    43. WHERE c.`age`<aa.avgage;
    44.  
    45.  
    46. #优化,将这个子查询作为驱动表
    47. #因为子查询5.7版本前是不能有索引的，所以我们把它作为驱动表，这样非驱动表就可以上索引
    48. EXPLAIN SELECT SQL_NO_CACHE c.`name`,c.`age`,aa.avgage FROM emp c 
    49. INNER JOIN
    50. (SELECT a.deptId,AVG(a.age)avgage FROM emp a WHERE a.deptId IS NOT NULL
    51. GROUP BY a.deptId)aa
    52. ON c.`deptId`=aa.deptId
    53. WHERE c.`age`<aa.avgage;
    54.  
    55.  
    56. #创建索引
    57. CREATE INDEX idx_deptId ON emp(deptId);
    58. CREATE INDEX idx_deptId_age ON emp(deptId,age);
    59.  
    60.  
    61. #3.列出至少有2个年龄>40岁的成员的门派 (select得到数量，肯定是group by了)
    62. #(先按照门派分组 ->然后进行筛选得到>40岁的员工数量 —>最后进行判断)
    63. SELECT b.`deptName`,b.`id`,COUNT(*) FROM t_emp a
    64. INNER JOIN t_dept b ON a.`deptld`=b.`id`
    65. WHERE a.`age`>40
    66. GROUP BY b.`deptName`,b.`id`
    67. HAVING COUNT(*)>=2;
    68.  
    69.  
    70. #优化
    71. EXPLAIN SELECT SQL_NO_CACHE b.`deptName`,b.`id`,COUNT(*) FROM dept b 
    72. STRAIGHT_JOIN emp a ON a.`deptId`=b.`id`
    73. WHERE a.`age`>40
    74. GROUP BY b.`deptName`,b.`id`
    75. HAVING COUNT(*)>=2;
    76.  
    77. #建立索引
    78. CREATE INDEX idx_deptName ON dept(deptName);
    79. CREATE INDEX idx_deptId_age ON emp(deptId,age);
    80.  
    81.  
    82. #4.非掌门人员（先查询所有有帮派的帮派，然后提取CEO，然后not in）
    83. SELECT * FROM t_emp a WHERE a.`id` NOT IN
    84. (SELECT b.`CEO` FROM t_dept b WHERE b.`CEO` IS NOT NULL)
    85. ;
    86.  
    87. SELECT * FROM t_emp 
    88. SELECT * FROM t_dept 
    89.  
    90. #优化:(1.得到非掌门的门派成员信息，采用左外关联查询，门派表没有匹配的就会null，所以要b.id is null)
    91. SELECT * FROM t_emp a 
    92. LEFT JOIN t_dept b ON a.`id`=b.`CEO`
    93. WHERE b.`id` IS NULL;
    94.  
    95. SELECT c.deptName,c.id,COUNT(*) 
    96. FROM t_emp a INNER JOIN t_dept c ON a.`deptld`=c.id
    97. LEFT JOIN t_dept b ON a.`id`=b.`CEO`
    98. WHERE b.`id` IS NULL
    99. GROUP BY c.deptName,c.id
    100. HAVING COUNT(*)>=2
    101. ;
    102.  
    103. #5.得到所有在帮派的成员->得到在籍帮派的名字和id->在关联一次，去除掌门，留下非掌门的信息(利用左外的性质)
    104. #要得到数量，所以分组
    105. EXPLAIN SELECT SQL_NO_CACHE c.`deptName`,c.`id`,COUNT(*) FROM emp a 
    106. INNER JOIN dept c ON a.`deptId`=c.`id`
    107. LEFT JOIN dept b ON a.`id`=b.`CEO`
    108. WHERE b.`id` IS NULL
    109. GROUP BY c.`deptName`,c.`id`
    110. HAVING COUNT(*)>=2;
    111.  
    112.  
    113. #创建索引
    114. CREATE INDEX idx_deptName ON dept(deptName);
    115. CREATE INDEX idx_deptId ON emp(deptId);
    116. CREATE INDEX idx_CEO ON dept(CEO);
    117.  
    118.  
    119. #6.列出所有人员，并且增加一列备注是否为掌门，如果是备注就是——>case when 判断
    120. #然后利用左外性质b的id->is null then xxx进行判断
    121. SELECT a.`name`,a.`age`,b.`deptName`,(CASE WHEN b.id IS NULL THEN '否' ELSE '是' END)'是否为掌门'
    122. FROM t_emp a 
    123. LEFT JOIN t_dept b ON a.`id`=b.`CEO`; 
    124.  
    125.  
    126. #7.列出所有门派，并且备注，平均年龄>50就是老鸟，否则为菜鸟
    127. SELECT b.`deptName`,b.`id`,IF(AVG(a.`age`)>50,'老鸟','菜鸟')'老鸟or菜鸟' 
    128. FROM t_emp a
    129. INNER JOIN t_dept b ON a.`deptld`=b.`id`
    130. GROUP BY b.`deptName`,b.`id`;
    131.  
    132.  
    133. #8.显示每个门派年龄最大的人(->先得到最大年龄根据门派进行分组->然后关联一下人物表得到最大年龄的人)
    134. SELECT c.`name`,c.`age`,aa.maxage FROM t_emp c INNER JOIN 
    135. (SELECT a.`deptld`,MAX(a.`age`)maxage 
    136. FROM t_emp a WHERE a.`deptld` IS NOT NULL
    137. GROUP BY a.`deptld`)aa
    138. ON c.`deptId`=aa.deptld AND c.`age`=aa.maxage;
    139.  
    140.  
    141. #9.求每个门派第三大年龄的人(w写错了)
    142. SELECT a.`age`,a.`name`,a.`deptld` FROM t_emp a ORDER BY a.`age` DESC 
    143. LEFT JOIN(
    144. SELECT a.`id` FROM t_emp a INNER JOIN t_dept b ON a.`deptld`=b.id
    145. )aa ON a.`deptld`=aa.id;#这里就分组错了，原计划分组后再limit完成
    146.

  • 相关阅读:
    Linux内存地址映射-8086分段分页与缺页异常
    怎样合并PDF文件更快速？PDF合并工具有什么？
    R语言---PCA/tSNE/UMAP降维计算
    安防监控产品经营商城小程序的作用是什么
    力扣刷题 day41：10-11
    批处理程序修改网卡配置
    视频监控/视频汇聚/安防视频监控平台EasyCVR如何将默认快照的raw格式改为jpg/base64格式？
    Java中的正则表达式
    MacOS上的Pip和Python升级指南
    数字孪生技术栈的应用场景的优点
  • 原文地址：https://blog.csdn.net/weixin_57128596/article/details/125455737