1.内连接

  1. /*
  2. 连接条件的分类：
  3.     1.自连接 vs 非自连接
  4.     2.等值连接 vs 非等值连接
  5.     3.内连接 vs 外连接
  7. 多表查询的语法：sql92语法与sql99语法（主要使用sql99）
  9. */
  10. #查询员工姓名及部门名称
  11. SELECT first_name, department_name
  12. FROM employees,departments   #sql92
  13. WHERE employees.`department_id` = departments.`department_id`;
  15. #查询员工的姓名，部门名称和部门id
  16. SELECT first_name, department_name,employees.`department_id`
  17. FROM employees,departments   #sql92
  18. WHERE employees.`department_id` = departments.`department_id`;
  19. #注意：多表查询最好都在字段前面加表名，即提升效率，也减少错误
  20. #多表查询时会在每张表都查询该字段，不加表名效率低下
  22. #给表起别名,form先执行不是顺序执行
  23. SELECT e.first_name, d.department_name,e.`department_id`
  24. FROM employees e,departments d   #sql92
  25. WHERE e.`department_id` = d.`department_id`;
  27. /*
  28. select...
  29. from 表名1 join 表名2
  30. on 连接条件
  31. join 表名3
  32. on 连接条件
  33. ...
  34. order by ...
  35. */ 
  36. #查询员工姓名及部门名称(sql99语法)
  37. SELECT e.`first_name`,d.`department_name`
  38. FROM employees e JOIN departments d
  39. ON e.`department_id`=d.`department_id`;
  40. #连接条件：
  41.     #非自连接（两张不同的表）
  42.     #等值连接（连接条件符号为=）
  43.     #内连接（只查找两张表中的匹配内容）

JOIN ON

2.各种连接形式UNION

  1. #自连接
  2. #查询员工及员工管理者的姓名
  3. SELECT e1.first_name 员工姓名, e2.`first_name` 管理者姓名
  4. FROM employees e1 JOIN employees e2
  5. ON e1.`manager_id` = e2.`employee_id`;
  7. #非等值连接
  8. #查询员工薪水的等级
  9. SELECT e.`salary`,j.`GRADE`
  10. FROM employees e JOIN job_grades j
  11. ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;
  13. #左外连接
  14. #查询所有的员工姓名及部门名称。
  15. SELECT e.`first_name` ,d.`department_name`
  16. FROM employees e LEFT JOIN departments d 
  17. ON e.`department_id` = d.`department_id`;
  19. #右外连接
  20. SELECT e.`first_name`,d.`department_name`
  21. FROM employees e RIGHT JOIN departments d
  22. ON e.`department_id` = d.`department_id`;
  24. #满外连接 - full join(mySQL不支持）
  25. #union：可以将两个查询结果合并在一起（去重后）
  26. SELECT e.`first_name`,d.`department_name`
  27. FROM employees e LEFT JOIN departments d
  28. ON e.`department_id` = d.`department_id`;
  29. UNION
  30. SELECT e.`first_name`,d.`department_name`
  31. FROM employees e RIGHT JOIN departments d
  32. ON e.`department_id` = d.`department_id`;
  34. #查询员工姓名，部门名称，城市名(三表连接)
  35. SELECT
  36. FROM employees e JOIN departments d
  37. ON e.`department_id` = d.`department_id`
  38. JOIN locations l
  39. ON d.`location_id` = l.`location_id`;

LEFT JOIN
RIGHT JOIN
FULL JOIN(MYSQL支持)
UNION
UNION ALL(不去重)

3.去重与飘号

  1. #去重：distinct
  2. #查询有员工的部门号
  3. SELECT DISTINCT department_id
  4. FROM employees
  5. WHERE department_id IS NOT NULL;
  7. #``:如果字段名与关键字相同，那么字段名需要使用``括起来
  8. SELECT `select`;

DISTINCT

4.单行函数

一出一

  1. /*
  2. LOWER('SQL Course'):将所有内容变成小写
  3. UPPER('SQL Course'):将所有内容变成大写
  4. */
  6. SELECT LOWER('aAbBcC');
  7. SELECT UPPER('aAbBcC');
  9. /*
  10. CONCAT():字符串拼接
  11. SUBSTR():截取子串。开始位置;长度  ,初始位置为1
  12. LENGTH()：字符串长度
  13. INSTR('helloworld','w'):w首次出现的位置 
  14. LPAD(salary,10,'*'):向右对齐
  15.     10：长度不够10用*补齐
  16. RPAD():向左补齐
  17. TRIM(''FROM'  aaaa aaaa  '):去除两边指定的字符
  18. REPLACE('aaaaa','a','A'):将字符串中指定字符进行替换
  19. */
  20. SELECT CONCAT('hello','world');
  21. SELECT CONCAT(first_name,last_name)
  22. FROM employees;
  24. SELECT SUBSTR('hellolong',1,3);
  26. SELECT first_name, LENGTH(first_name)
  27. FROM employees;
  29. SELECT INSTR('abcdefg','c');
  31. SELECT LPAD(salary,10,'*'),RPAD(salary,10,'*')
  32. FROM employees;
  34. SELECT TRIM(' ' FROM 'aa bb cc'); #aa bb cc
  36. SELECT REPLACE ('abcabcaaa','a','A'); #AbcAbcAAA
  38. /*
  39. ROUND:四舍五入
  40. ROUND(45.926,2)    45.93
  42. TRUNCATE:截断
  43. TRUNCATE(45.926)   45
  45. MOD:求余
  46. MOD(1600,300)      100  
  47. */
  49. SELECT ROUND(45.926,2),ROUND(45.926,1),
  50. ROUND(45.926,0),ROUND(45.926,-1); #50
  52. SELECT TRUNCATE(45.926,2),TRUNCATE(45.926,1),
  53. TRUNCATE(45.926,0),TRUNCATE(45.926,-1) #40
  55. #注意：结果的正负与第一个参数（被模数）的正负有关
  56. SELECT MOD(4,3),MOD(-4,-3),MOD(-4,3),MOD(4,-3);
  58. /*
  59. 函数NOW()
  60. VERSION()
  61. */
  63. SELECT NOW();
  64. SELECT VERSION(); #显示当前版本

LOWER(‘SQL Course’) ：将所有内容变成小写
UPPER(‘SQL Course’) ：将所有内容变成大写
CONCAT(‘Hello’, ‘World’) : 字符串拼接
SUBSTR(‘HelloWorld’,1,5) : 截取子串。1指的是 索引位置（从1开始）。5指的是长度
LENGTH(‘HelloWorld’) ：子符串长度
INSTR(‘HelloWorld’, ‘W’) :W首次出现的位置
LPAD(salary,10,’‘) : 向右对齐
如果长度不够10用补齐
RPAD(salary, 10, ‘‘) ：向左对齐
如果长度不够10用补齐
TRIM(‘H’ FROM ‘HelloWorld’):去除指定的字符
REPLACE(‘abcd’,’b’,’m’) ：将字符串中的指定的字符进行替换
ROUND: 四舍五入
ROUND(45.926, 2) 45.93

TRUNCATE: 截断
TRUNCATE(45.926) 45

MOD: 求余
MOD(1600, 300) 100
#注意：结果的正负和第一个参数的正负有关（被模数）
NOW()
VERSION()

5.通用函数

  1. /*
  2. ifnull(字段，默认值) ：如果字段的内容为null那么就返回默认值
  3. */
  4. SELECT IFNULL(1,10),IFNULL(NULL,10);
  6. #案例：查询所有人的工资（salary+奖金）。
  7. SELECT first_name,salary+salary*IFNULL(commission_pct,0) 工资
  8. FROM employees;
  12. /*
  13. case-when-then-else-end
  15. 格式：
  16. case 字段名
  17. when 值1 then 返回值1
  18. when 值2 then 返回值2
  19. when 值3 then 返回值3
  20. .......
  21. else 返回值n
  22. end
  24. 格式二
  25. case 
  26. when 判断式1 then 返回值1
  27. when 判断式2 then 返回值2
  28. when 判断式3 then 返回值3
  29. .......
  30. else 返回值n
  31. end
  34. */
  35. #案例：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 
  36. #则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 
  37. #30 号部门打印其工资的 1.3 倍数
  38. SELECT first_name,salary,department_id,CASE department_id
  39.                        WHEN 10 THEN salary*1.1
  40.                        WHEN 20 THEN salary*1.2
  41.                        WHEN 30 THEN salary*1.3
  42.                        ELSE salary
  43.                        END new_salary
  44. FROM employees
  45. WHERE department_id IN(10,20,30);

ifnull(字段，默认值) ：如果字段的内容为null那么就返回默认值

case-when-then-else-end

格式：
case 字段名
when 值1 then 返回值1
when 值2 then 返回值2
when 值3 then 返回值3
…….
else 返回值n
end

格式二
case
when 判断式1 then 返回值1
when 判断式2 then 返回值2
when 判断式3 then 返回值3
…….
else 返回值n
end

6.组函数（多行函数）

多出一

  1. /*
  2. AVG() ：平均值 
  3. SUM() ：求和
  4. 注意：数据只能是数值类型
  6. MAX() ：最大值
  7. MIN() : 最小值
  10. */
  11. SELECT MAX(first_name)
  12. FROM employees;
  14. SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
  15. FROM employees;
  17. /*
  18. COUNT() ：求数量
  20. count(字段名)：该字段数据不为null的有多少条。
  21. count(*)：该表中的数据有多少条
  22. count(数值)：该表中的数据有多少条，count(数值)效率高于count(*)
  23. */
  24. SELECT COUNT(commission_pct),COUNT(*),COUNT(1)
  25. FROM employees;
  27. SELECT commission_pct
  28. FROM employees
  29. WHERE commission_pct IS NOT NULL;
  32. /*
  33. 注意1：
  34. 思考：求平均值(avg)是否包括null?不包括null
  36. */
  37. SELECT AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35
  38. FROM employees;
  40. /*
  41. 注意2：一旦select后面出现组函数（聚合函数，多行函数）就不能再出现其它字段。
  42.     除非该字段也出现在group by的后面。
  43. */
  44. SELECT first_name,AVG(salary)
  45. FROM employees;

AVG() ：平均值
SUM() ：求和
注意：数据只能是数值类型
MAX() ：最大值
MIN() : 最小值
COUNT() ：求数量
count(字段名)：该字段数据不为null的有多少条。
count()：该表中的数据有多少条
count(数值)：该表中的数据有多少条，count(数值)效率高于count()

注意1：
思考：求平均值(avg)是否包括null?不包括null
不包括

注意2：一旦select后面出现组函数（聚合函数，多行函数）就不能再出现其它字段。
除非该字段也出现在group by的后面。

注意3：注意2中如果group by后面跟的是主键或者不为空唯一索引时，可以在select后随意添加其他元素。

  1. SELECT emp_no,salary,(
  2.     select COUNT(DISTINCT(s2.salary))
  3.     from salaries s2
  4.     where s2.salary >= s1.salary
  5. ) `t_rank`
  6. from salaries s1
  7. order by s1.salary DESC,s1.emp_no