牛客网MySql 51~60

牛客网MySql 51~60

51.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt

• 巧用 length() 函数
  length()函数可以获取字符串的长度
  replace（String a，String b, String c）:把a中的b用c代替

SELECT 
length( '10,A,B') - length(REPLACE( '10,A,B',',',''));

52. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

• Right函数：它能返回从最右边开始指定长度的字符串。
• LEFT函数：返回从最左边开始的指定长度字符串。

SELECT e.FIRST_NAME 
FROM employees e
ORDER BY RIGHT (e.FIRST_NAME,2);

• substr函数：

  substr(X,Y,Z) X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

SELECT e.FIRST_NAME 
FROM employees e
ORDER BY SUBSTR(e.FIRST_NAME,-2); 

SELECT e.FIRST_NAME 
FROM employees e
ORDER BY SUBSTR(e.FIRST_NAME,LENGTH(e.FIRST_NAME)-1); 

53. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

• 聚合函数group_concat

  聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号

SELECT d.DEPT_NO , GROUP_CONCAT(d.EMP_NO,'') 
FROM dept_emp d
GROUP BY d.DEPT_NO ;

54. 去除一个最大值，一个最小值求平均

• 子查询+NOT IN

SELECT AVG(s1.SALARY)
FROM salaries s1
WHERE s1.TO_DATE ='9999-01-01'
AND s1.SALARY NOT IN ( 
SELECT MAX(smax.SALARY)
FROM (
SELECT smax.SALARY 
FROM salaries smax
WHERE smax.TO_DATE ='9999-01-01') AS smax)
AND s1.SALARY NOT IN (
SELECT MIN(smin.SALARY)
FROM (
SELECT smin.SALARY 
FROM salaries smin
WHERE smin.TO_DATE ='9999-01-01') AS smin);

• 聚合函数 （sum-max-min）/count-2

SELECT (SUM(s.SALARY) -MAX(s.SALARY)-MIN(s.SALARY))/(COUNT(s.EMP_NO)-2) AS avg_salary
FROM salaries s
WHERE s.TO_DATE ='9999-01-01';

55. 分页查询employees表，每5行一页，返回第2页的数据

• LIMIT函数：

  LIMIT 语句结构： LIMIT X,Y
  Y ：返回几条记录
  X：从第几条记录开始返回（第一条记录序号为0，默认为0）
  比如：
  每页5条 第一页 limit 0，5（01234）
  每页5条 第二页 limit 5，5（56789）
  每页5条 第三页 limit 10，5（56789）

select * from employees
limit 5,5;

56. 使用含有关键字exists查找未分配具体部门的员工的所有信息

• Exists的用法：

  exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录;反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false。

• 什么时候用EXISTS，什么时候用IN？

  主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：
  当主表比从表大时，IN查询的效率较高；
  当从表比主表大时，EXISTS查询的效率较高；
  原因如下：
  in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次
  exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

SELECT *
FROM employees
WHERE NOT EXISTS 
(SELECT emp_no
FROM dept_emp
WHERE employees.emp_no = dept_emp.emp_no);

SELECT * 
FROM employees
WHERE EXISTS
(SELECT emp_no 
FROM dept_emp 
WHERE dept_emp.emp_no != employees.emp_no)

SELECT *
FROM employees
WHERE emp_no NOT IN 
(SELECT emp_no
FROM dept_emp);