第一章.多表查询

1.SQL书写事项

  1. (1)SQL 语言大小写不敏感。 
  2. (2)SQL 可以写在一行或者多行
  3. (3)关键字不能被缩写也不能分行
  4. (4)各子句一般要分行写。
  5. (5)使用缩进提高语句的可读性。

2.字符串

  1. 日期和字符只能在单引号中出现
  2. 每当返回一行时,字符串被输出一次

3.多表查询

  1. /*
  3. 多表查询：将多张表进行连接查出需要的数据。
  5. 多表连接分类：
  6.     1.自连接 vs 非自连接
  7.     2.内连接 vs  外连接
  8.     3.等值连接 vs 非等值连接
  10. 语法：sql92语法和sql99语法（重点）
  12. */
  13. #sql92语法
  14. #需求：查询所有的员工姓名和员工所在的部门名称
  15. SELECT first_name,department_name
  16. FROM employees,departments
  17. WHERE employees.department_id=departments.department_id;#连接条件
  19. #下面会发生笛卡尔集的错误（原因：因为缺少连接条件或连接条件错误）。
  20. SELECT first_name,department_name
  21. FROM employees,departments;
  24. #需求：查询所有的员工姓名和员工所在的部门名称
  25. #注意：多表查询时，如果查询的字段在两张表中都存在那么字段名前要加表名。
  26. #如果字段可以区分最好也加上表名。因为效率高一些。
  27. SELECT employees.first_name,departments.department_name,employees.department_id,
  28.     departments.`department_id`
  29. FROM employees,departments
  30. WHERE employees.department_id=departments.department_id;#连接条件
  32. #给表起别名
  33. SELECT e.first_name,d.department_name,e.department_id,
  34.     d.`department_id`
  35. FROM employees e,departments d
  36. WHERE e.department_id=d.department_id;#连接条件
  41. #sql99语法
  42. /*
  43. select .....
  44. from 表1 join 表2
  45. on 连接条件
  46. join 表3
  47. on 连接条件
  48. ......
  49. where 过滤条件
  50. order by ......
  51. */
  52. #需求：查询所有的员工姓名和员工所在的部门名称
  53. #非自连接（不同的表进行连接）
  54. #等值连接（连接条件是一个等号）
  55. #内连接(所有匹配的数据)
  56. SELECT e.`first_name`,d.`department_name`
  57. FROM employees e JOIN departments d  #可以写成  inner join
  58. ON e.`department_id` = d.`department_id`;
  61. #自连接
  62. #需求：获取所有的员工姓名及管理者的姓名
  63. SELECT e.`first_name` "员工姓名", m.`first_name` "管理者姓名"
  64. FROM employees e JOIN employees m
  65. ON e.`manager_id`=m.`employee_id`;
  67. #非等值连接
  68. #需求：获取所有员工的薪水等级
  69. SELECT e.`first_name`,e.`salary`,j.grade
  70. FROM employees e JOIN job_grades j
  71. ON e.salary >= j.`LOWEST_SAL` AND e.`salary` <= j.`HIGHEST_SAL`;
  73. /*
  74. 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  75. 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）
  76. 表中不满足条件的行 ，
  77. 这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 
  78. */
  79. #左外连接：
  80. #需求：获取所有的员工及员工的部门名称
  81. SELECT e.`first_name`,d.`department_name`
  82. FROM employees e LEFT JOIN departments d # 可以写成 left outer join
  83. ON e.`department_id` = d.`department_id`;
  85. #右外连接
  86. #需求：获取所有部门及部门中的员工
  87. SELECT e.`first_name`,d.`department_name`
  88. FROM employees e RIGHT JOIN departments d # 可以写成right outer join
  89. #from departments d left join employees e
  90. ON e.`department_id`=d.`department_id`;
  92. #满外连接:full join（mysql不支持）
  93. #union(去重) ：可以将两条sql语句的结果进行合并
  94. #union all(不去重)
  95. SELECT e.`first_name`,d.`department_name`
  96. FROM employees e LEFT JOIN departments d 
  97. ON e.`department_id` = d.`department_id`
  98. UNION
  99. SELECT e.`first_name`,d.`department_name`
  100. FROM employees e RIGHT JOIN departments d
  101. ON e.`department_id`=d.`department_id`;
  104. #需求：查询员工的姓名，部门名称，部门所在的城市。
  105. SELECT e.`first_name`,d.`department_name`,l.`city`
  106. FROM employees e JOIN departments d
  107. ON e.`department_id`=d.`department_id`
  108. JOIN locations l
  109. ON d.`location_id`=l.`location_id`;

$02[DML(下)] - 图1

第二章.单行函数

1.单行函数的定义

  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值

2.字符函数

  1. /*
  2. LOWER('SQL Course') ：将所有的字母变成小写
  3. UPPER('SQL Course') ：将所有的字母变成大写
  4. */
  5. SELECT LOWER('AaBc');
  6. SELECT UPPER('AaBc');
  7. SELECT LOWER(first_name),UPPER(last_name)
  8. FROM employees;
  10. /*
  11. CONCAT('Hello', 'World') : 字符串拼接
  12. SUBSTR('HelloWorld',1,5) : 截取子串
  13.     1 :指的是索引位置（索引从1开始）
  14.     5 ：指的是长度（偏移量）
  15. LENGTH('HelloWorld') ：字符串长度
  17. INSTR('HelloWorld', 'W') : W字符在当前字符串中首次出现的位置
  18.     注意：索引位置从1开始
  19. LPAD(salary,10,'*') ：向右对齐
  20.     10 : 指的是内容的长度如果不够用指定的字符补齐
  21.     '*' : 用来补内容的字符
  22. RPAD(salary, 10, '*') ：向左对齐
  23.     10 : 指的是内容的长度如果不够用指定的字符补齐
  24.     '*' : 用来补内容的字符
  25. TRIM('H' FROM 'HelloWorld') ：指定去除字符串两端的字符
  27. REPLACE('abcd','b','m') : 替换字符串所有的指定字符
  28. */
  30. SELECT REPLACE('abcaaa','a','A');
  32. SELECT TRIM('H' FROM 'HHHHHh  HHH aHHHHHH');
  34. SELECT LPAD(salary,10,' '),RPAD(salary,10,' ')
  35. FROM employees;
  40. SELECT CONCAT('hello','longge');
  41. SELECT CONCAT(first_name,last_name)
  42. FROM employees;
  44. SELECT SUBSTR('hellolong',3,3);
  46. SELECT LENGTH('hello');
  48. SELECT INSTR('heollo','o');

3.数字函数

  1. /*
  2. ROUND: 四舍五入
  3. ROUND(45.926, 2)            45.93
  5. TRUNCATE: 截断
  6. TRUNCATE(45.926)              45
  8. MOD: 求余
  9. MOD(1600, 300)                  100
  10. */
  11. SELECT  ROUND(45.926,2), #45.93
  12.     ROUND(45.926,1), #45.9
  13.     ROUND(45.926,0), #46
  14.     ROUND(45.926,-1);#50
  16. SELECT  TRUNCATE(45.926,2), #45.92
  17.     TRUNCATE(45.926,1), #45.9
  18.     TRUNCATE(45.926,0), #45
  19.     TRUNCATE(45.926,-1);#40
  22. #正负和被模数（第一个参数）的正负有关。
  23. SELECT MOD(4,3),MOD(-4,3),MOD(4,-3),MOD(-4,-3);

4.日期函数

  1. #日期函数
  2. SELECT NOW();
  4. #查看Mysql版本
  5. SELECT VERSION();

5.通用函数

  1. /*
  2. 通用函数 :ifnull(字段名，值) ： 如果字段的内容为null就返回指定的值
  3. */
  4. #需求：获取所有人的薪水（工资+奖金）
  5. SELECT salary+salary* IFNULL(commission_pct,0),commission_pct
  6. FROM employees;

6.case表达式

  1. /*
  2. case表达式：
  3. 格式1：
  4. case 字段名 
  5. when 匹配的内容1 then 返回值1
  6. when 匹配的内容2 then 返回值2
  7. when 匹配的内容3 then 返回值3
  8. ......
  9. else 返回值n
  10. end
  12. 格式2
  13. case
  14. when 表达式1  then 返回值1
  15. when 表达式2  then 返回值2
  16. else 返回值n
  17. end
  18. */
  19. /*
  20. 练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 
  21. 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 
  22. 30 号部门打印其工资的 1.3 倍数
  23. */
  24. SELECT salary,department_id,CASE department_id
  25.                 WHEN 10 THEN salary*1.1
  26.                 WHEN 20 THEN salary*1.2
  27.                 WHEN 30 THEN salary*1.3
  28.                 ELSE salary
  29.                 END AS new_salary
  30. FROM employees;    
  33. #练习 ：如果薪水大于等于1万输出"高"否则输出"低"
  34. SELECT salary,CASE 
  35.           WHEN salary>=10000 THEN "高"
  36.           ELSE "低"
  37.           END AS "h_l"
  38. FROM employees;

第三章.组函数

1.什么是分组函数?

分组函数作用于一组数据,并对一组数据返回一个值

2.组函数语法

  1. /*
  3. AVG() ：求平均值
  4. SUM() ：求和
  5. 注意：上面运算的数据类型只能是数值型
  7. MAX() ：求最大值
  8. MIN() ：求最小值
  9. COUNT() ：统计数据的个数
  10. */
  12. SELECT AVG(salary),SUM(salary),MAX(salary),MIN(salary),COUNT(salary)
  13. FROM employees;
  16. /*
  17. avg() : 
  18.     思考:求平均值时是否包含了null？没有包括null
  20. sum(),max(),min()在运算时都会忽略掉null值
  21. */
  24. SELECT COUNT(commission_pct)
  25. FROM employees;
  27. SELECT commission_pct
  28. FROM employees
  29. WHERE commission_pct IS NOT NULL;
  31. #注意：avg在做运算时不包括null
  32. SELECT SUM(commission_pct)/35,SUM(commission_pct/107),AVG(commission_pct)
  33. FROM employees;
  35. #sum,max,min在运算时都会忽略掉null值
  36. SELECT SUM(commission_pct),MAX(commission_pct),MIN(commission_pct)
  37. FROM employees;
  40. /*
  41. count(字段名) ： 不包括null在内 --字段中的内容不为null的有多少条
  42. count(*) : 统计整张表中所有数据有多少条
  43. count(数值) ： 统计整张表中所有数据有多少条比count(*)效率高
  45. */
  46. SELECT COUNT(commission_pct),COUNT(*),COUNT(1),COUNT(2)
  47. FROM employees;

第四章.分组和过滤

1.group by

  1. /*
  2. 注意：一旦select后面出现组函数，就不能再出现其它字段。
  3.     除非出现的字段也出现在group by的后面。
  4. */
  5. SELECT first_name,AVG(salary)
  6. FROM employees;
  8. /*
  9. 格式：
  10. select ......
  11. from 表名
  12. where 过滤条件
  13. group by 字段名1，字段名2.......
  14. order by ......
  15. */
  17. #需求：查询各部门中最高薪水是多少
  18. #注意：一旦select后面出现组函数，就不能再出现其它字段。
  19. #    除非出现的字段也出现在group by的后面。
  20. SELECT department_id,MAX(salary)
  21. FROM employees
  22. WHERE department_id IS NOT NULL
  23. GROUP BY department_id;
  26. #需求：查询各工种中平均薪水是多少，并按照平均薪水进行排序-降序
  27. SELECT job_id,AVG(salary) a_s
  28. FROM employees
  29. GROUP BY job_id
  30. #order by avg(salary) desc;
  31. ORDER BY a_s DESC;
  33. #需求:查询各部门中不同工种的最高薪水是多少，并按照最高薪水进行排序-降序
  34. SELECT department_id,job_id,MAX(salary)
  35. FROM employees
  36. WHERE department_id IS NOT NULL
  37. GROUP BY department_id,job_id
  38. ORDER BY MAX(salary) DESC;

2.having子句

使用having过滤分组

  • 行已经被分组
  • 使用了组函数
  • 满足Having子句中条件的分组将被显示
  1. /*
  2. having ：过滤
  3. 格式:
  4. select .......
  5. from 表名
  6. where 过滤条件
  7. group by .....
  8. having 过滤条件
  9. order by ......
  11. where和having的区别？
  12. 1.where后面不能出现组函数，having后面可以出现组函数
  13. 2.where在group by的前面，having在group by的后面
  14. */
  15. #需求：求各管理者手下员工的最高薪水大于6000的管理者有哪些，
  16. SELECT manager_id,MAX(salary)
  17. FROM employees
  18. #where max(salary)>6000 #注意:where不能跟组函数。where是在group by之前执行。
  19. GROUP BY manager_id
  20. HAVING MAX(salary)>6000
  21. ORDER BY MAX(salary) DESC;
  24. #需求：求部门号为50，90，100中各员工的平均薪水
  25. #先求出各部门的平均薪水再过滤
  26. SELECT department_id,AVG(salary)
  27. FROM employees
  28. GROUP BY department_id
  29. HAVING department_id IN(50,90,100);
  31. #先过滤出50,90,100然后再进行分组---这种方式效率最高
  32. SELECT department_id,AVG(salary)
  33. FROM employees
  34. WHERE department_id IN(50,90,100)
  35. GROUP BY department_id;

第五章.子查询

1.定义

  1. 子查询 ：在一个查询语句a中再嵌套另一个查询语b.
    b语句叫作子查询，a语句叫作外查询（主查询）
  2. 子查询分类 ： 单行子查询 vs 多行子查询
  3. 单行子查询 ：子查询的结果只有一条
    多行子查询 ：子查询的结果有多条
  4. 单行子查询使用的运算符 ：= > >= < <= <>
  5. 多行子查询使用的运算符 ： IN ANY ALL
    INT 等于列表中的任意一个
    ANY 和子查询返回的某一个值比较
    ALL 和子查询返回的所有值比较

2.使用(单行子查询)

  1. */
  2. #需求：谁的工资比 Abel 高?
  3. #第一种方式：查两次①先查出Abel的工资是多少 ②再用其它用工的薪水做对比
  4. SELECT salary
  5. FROM employees
  6. WHERE last_name='Abel'; #11000
  8. SELECT last_name,salary
  9. FROM employees
  10. WHERE salary>11000;
  12. #第二种方式：自连接
  13. SELECT e1.last_name,e1.`salary`
  14. FROM employees e1 JOIN employees e2
  15. ON e2.last_name='Abel' AND e1.`salary`>e2.salary;
  17. #第三种方式：子查询
  18. SELECT last_name,salary
  19. FROM employees
  20. WHERE salary>(
  21.     SELECT salary
  22.     FROM employees
  23.     WHERE last_name='Abel'
  24. );
  27. #需求：题目：返回job_id与141号员工相同，salary比143号员工多的员工
  28. #            姓名，job_id 和工资
  29. #1.求出141号员工的job_id
  30. SELECT job_id
  31. FROM employees
  32. WHERE employee_id=141;#ST_CLERK
  33. #2.求出143号员工的薪水
  34. SELECT salary
  35. FROM employees
  36. WHERE employee_id=143;#2600
  37. #3.求job_id为ST_CLERK薪水比2600多的员工有哪些
  38. SELECT last_name,job_id,salary
  39. FROM employees
  40. WHERE job_id='ST_CLERK' AND salary>2600;
  42. #子查询
  43. SELECT last_name,job_id,salary
  44. FROM employees
  45. WHERE job_id=(
  46.     SELECT job_id
  47.     FROM employees
  48.     WHERE employee_id=141
  49. ) AND salary>(
  50.     SELECT salary
  51.     FROM employees
  52.     WHERE employee_id=143
  53. );
  56. #需求：返回公司工资最少的员工的last_name,job_id和salary
  57. #1.求最少工资
  58. SELECT MIN(salary)
  59. FROM employees;#2100
  60. #2.查询工资为2100的员工的信息last_name,job_id和salary
  61. SELECT last_name,job_id,salary
  62. FROM employees
  63. WHERE salary=2100;
  65. #子查询
  66. SELECT last_name,job_id,salary
  67. FROM employees
  68. WHERE salary=(
  69.     SELECT MIN(salary)
  70.     FROM employees
  71. );
  73. #需求：查询最低工资大于50号部门最低工资的部门id和其最低工资
  74. SELECT department_id,MIN(salary)
  75. FROM employees
  76. WHERE department_id IS NOT NULL
  77. GROUP BY department_id
  78. HAVING MIN(salary)>(
  79.     #50号部门最低工资
  80.     SELECT MIN(salary)
  81.     FROM employees
  82.     WHERE department_id=50
  83. );
  87. SELECT commission_pct
  88. FROM employees
  89. WHERE commission_pct > (
  90.     #如果子查询结果为null那么查不出数据
  91.     SELECT commission_pct
  92.     FROM employees
  93.     WHERE employee_id=100
  94. )

3.使用(多行子查询)

  1. /*
  2. 多行子查询使用的运算符 ： IN ANY ALL
  3.     INT  等于列表中的任意一个
  4.     ANY  和子查询返回的某一个值比较
  5.     ALL  和子查询返回的所有值比较
  6. */
  7. #需求：题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
  8. #             工号、姓名、job_id 以及salary
  10. SELECT employee_id,last_name,job_id,salary
  11. FROM employees
  12. WHERE job_id<>'IT_PROG' AND salary <ANY (
  13.     #获取IT_PROG部门所有员工的薪水
  14.     SELECT DISTINCT salary
  15.     FROM employees
  16.     WHERE job_id='IT_PROG'
  17. );
  19. #需求：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
  20. #            的员工号、姓名、job_id 以及salary
  21. SELECT employee_id,last_name,job_id,salary
  22. FROM employees
  23. WHERE job_id<>'IT_PROG' AND salary <ALL (
  24.     #获取IT_PROG部门所有员工的薪水
  25.     SELECT DISTINCT salary
  26.     FROM employees
  27.     WHERE job_id='IT_PROG'
  28. );
  31. #去重：distinct
  32. SELECT DISTINCT salary
  33. FROM employees
  34. WHERE job_id='IT_PROG'