1.分组与过滤

  1. where 过滤条件
  2. group by 字段1,字段2,...
  3. */
  4. #求各部门中最高的薪水
  5. SELECT department_id, MAX(salary)
  6. FROM employees
  7. WHERE department_id IS NOT NULL
  8. GROUP BY department_id;
  10. #求各部门中最高的薪水,按照最高薪水排序
  11. SELECT department_id, MAX(salary)
  12. FROM employees
  13. WHERE department_id IS NOT NULL
  14. GROUP BY department_id
  15. ORDER BY MAX(salary);
  17. #求各部门中各工种的平均薪水
  18. SELECT department_id,job_id,AVG(salary)
  19. FROM employees
  20. WHERE department_id IS NOT NULL
  21. GROUP BY department_id,job_id;
  23. # 求10,20,30号部门的平均薪水，并要求平均薪水>4200
  24. /*
  25. where和having的区别？
  26. 1.where后面不能出现组函数，having后面可以出现组函数
  27. 2.where在group by的前面，having在group by的后面
  28. */
  29. #方式一：效率更高
  30. SELECT department_id, AVG(salary)
  31. FROM employees
  32. WHERE department_id IN(10,20,30)
  33. GROUP BY department_id
  34. HAVING AVG(salary)>4200;
  36. #方式二：
  37. SELECT department_id, AVG(salary)
  38. FROM employees
  39. GROUP BY department_id
  40. HAVING AVG(salary)>4200 AND department_id IN(10,20,30);

GROUP BY
HAVING

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

where和having的区别？
1.where后面不能出现组函数，having后面可以出现组函数
2.where在group by的前面，having在group by的后面。
3.一般先用where过滤完分组字段再用having过滤别的字段

2.子查询

  1. /*
  3. 子查询：在一个查询语句a中可以再嵌套一个查询语句b，那么b语句叫作子查询（内查询）
  4.         a语句叫作主查询（外查询）
  6. 子查询分类 ：单行子查询  vs 多行子查询
  8. 单行子查询 ：子查询的结果只有一条
  9. 多行子查询 ：子查询的结果有多条
  11. 单行子查询使用的运算符 ：= <> > >= < <=
  12. 多行子查询使用的运算符 ：in any all
  15. 查询语句在执行时会先执行子查询再执行主查询。
  16. */
  17. #案例 ：谁的工资比 Abel 高?
  18. #方式一：①先查出Abel工资 ②再查出比Abel高的工资
  19. SELECT salary
  20. FROM employees
  21. WHERE last_name='Abel'; #11000
  23. SELECT last_name,salary
  24. FROM employees
  25. WHERE salary > 11000;
  27. #方式二：自连接
  28. SELECT e.`last_name`,e.`salary`
  29. FROM employees e JOIN employees e2
  30. ON e.`salary`>e2.`salary` AND e2.`last_name`='Abel';
  32. #方式三：子查询
  33. SELECT last_name,salary
  34. FROM employees
  35. WHERE salary > (
  36.     SELECT salary
  37.     FROM employees
  38.     WHERE last_name='Abel'
  39. )
  41. #题目：返回job_id与141号员工相同，salary比143号员工多的员工
  42. #            姓名，job_id 和工资
  43. #方式一：
  44. #1.先查出141号员工的job_id
  45. SELECT job_id
  46. FROM employees
  47. WHERE employee_id=141;#ST_CLERK
  48. #2.再查出143号员工的薪水
  49. SELECT salary
  50. FROM employees
  51. WHERE employee_id=143;#2600
  52. #3.再查出结果
  53. SELECT last_name,job_id,salary
  54. FROM employees
  55. WHERE job_id='ST_CLERK' AND salary>2600;
  57. #方式二
  58. SELECT last_name,job_id,salary
  59. FROM employees
  60. WHERE job_id=(
  61.     SELECT job_id
  62.     FROM employees
  63.     WHERE employee_id=141
  64. ) AND salary>(
  65.     SELECT salary
  66.     FROM employees
  67.     WHERE employee_id=143
  68. );
  70. #题目：返回公司工资最少的员工的last_name,job_id和salary
  71. #方式一：
  72. #1.先获取最低工资
  73. SELECT MIN(salary)
  74. FROM employees; #2100
  75. #2.再根据最低工资查出相应信息
  76. SELECT last_name,job_id,salary
  77. FROM employees
  78. WHERE salary=2100;
  80. #方式二：
  81. SELECT last_name,job_id,salary
  82. FROM employees
  83. WHERE salary=(
  84.     SELECT MIN(salary)
  85.     FROM employees
  86. );
  88. #题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
  89. SELECT department_id,MIN(salary)
  90. FROM employees
  91. WHERE department_id IS NOT NULL
  92. GROUP BY department_id
  93. HAVING MIN(salary) > (
  94.     #50号部门最低薪水
  95.     SELECT MIN(salary)
  96.     FROM employees
  97.     WHERE department_id=50
  98. );
  101. #多行子查询
  102. #题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员
  103. #              工号、姓名、job_id 以及salary
  105. SELECT employee_id,last_name,job_id,salary
  106. FROM employees
  107. WHERE salary <ANY(
  108.     #job_id为IT的薪水
  109.     SELECT DISTINCT salary
  110.     FROM employees
  111.     WHERE job_id='IT_PROG'
  112. ) AND job_id <> 'IT_PROG';
  114. #题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工
  115.  #           的员工号、姓名、job_id 以及salary
  116. SELECT employee_id,last_name,job_id,salary
  117. FROM employees
  118. WHERE salary <ALL(
  119.     #job_id为IT的薪水
  120.     SELECT DISTINCT salary
  121.     FROM employees
  122.     WHERE job_id='IT_PROG'
  123. ) AND job_id <> 'IT_PROG';

子查询：在一个查询语句a中可以再嵌套一个查询语句b，那么b语句叫作子查询（内查询）
a语句叫作主查询（外查询）

子查询分类 ：单行子查询 vs 多行子查询

单行子查询 ：子查询的结果只有一条
多行子查询 ：子查询的结果有多条

单行子查询使用的运算符 ：= <> > >= < <=
多行子查询使用的运算符 ：in（等值） any all

—All：对所有数据都满足条件，整个条件才成立，例如：5大于所有返回的id
—Any：只要有一条数据满足条件，整个条件成立，例如：3大于1，2

查询语句在执行时会先执行子查询再执行主查询。

3.库操作

  1. #笛卡尔集的错误。
  2. #导致的原因：缺少连接条件或连接条件错误
  3. SELECT e.`first_name`,d.`department_name`
  4. FROM employees e,departments d;
  8. #查看所有的库
  9. SHOW DATABASES;
  11. #选库
  12. USE 库名;
  14. #创建库：create database [if not exists] 库名 [character set '编码集']
  15. CREATE DATABASE db;
  16. #if not exists :如果库不存在则创建，存在则不创建。如果没有该字段库存在则报错。
  17. CREATE DATABASE IF NOT EXISTS db;
  18. #character set '编码集' :指定库的编码集
  19. CREATE DATABASE db2 CHARACTER SET 'gbk';
  21. #查看库的信息
  22. SHOW CREATE DATABASE db2;
  24. #修改库的编码集
  25. ALTER DATABASE db CHARACTER SET 'utf8';
  27. #删除库:drop database [if exists] 库名
  28. #if exists :库存在则删除不存在则不删，如果没有该字段库不存在则报错。
  29. DROP DATABASE IF EXISTS db;

SHOW USE
CREAT DATABASE IF NOT EXISTS
CREAT DATABASE CHARACTER SET’GBK’
SHOW CREATE DATABASE
ALTER DATABASE CHARACTER SET’GBK’
DROP DATABASE IF EXISTS

4.表操作

  1. #查看表
  2. SHOW TABLES;
  4. #查看表的信息
  5. SHOW CREATE TABLE emp2;
  7. #查看表结构
  8. DESC emp;
  10. #创建表
  11. #方式一：白手起家
  12. #注意：表的默认编码集和库的编码集相同。
  13. CREATE TABLE emp(
  14. id INT,
  15. ename VARCHAR(20) #注意：最后一个字段没有","号
  16. )
  17. #IF NOT EXISTS ：表不存在则创建，存在则不创建。如果没有该字段表存在则报错。
  18. CREATE TABLE IF NOT EXISTS emp2(
  19. id INT,
  20. ename VARCHAR(20) #注意：最后一个字段没有","号
  21. )CHARACTER SET 'utf8';
  24. #方式二：基于查询的结果创建一张新表
  25. CREATE TABLE emp3
  26. SELECT employee_id,first_name
  27. FROM myemployees.`employees`; #夸库读表 ：库名.表名
  30. #方式三：根据现有表的结构创建一张新表(没有内容)
  31. CREATE TABLE emp4 LIKE myemployees.employees;
  34. #删除表
  35. #IF EXISTS : 如果表存在则删除不存在则不删，如果没有该字段表不存在则报错。
  36. DROP TABLE IF EXISTS emp4;

SHOW TABLES
SHOW CREATE TABLE
DESC
#创建表

方式一：白手起家
注意：表的默认编码集和库的编码集相同。
CREATE TABLE emp(
id INT,
ename VARCHAR(20) #注意：最后一个字段没有”,”号
)
IF NOT EXISTS; ：表不存在则创建，存在则不创建。如果没有该字段表存在则报错。
CHARACTER SET ‘utf8’;

方式二：基于查询的结果创建一张新表
CREATE TABLE emp3
SELECT employee_id,first_name
FROM myemployees.employees; #夸库读表 ：库名.表名

方式三：根据现有表的结构创建一张新表(没有内容)
CREATE TABLE emp4 LIKE myemployees.employees;

删除表
#IF EXISTS : 如果表存在则删除不存在则不删，如果没有该字段表不存在则报错。
DROP TABLE IF EXISTS emp4;

5.表字段操作2

  1. /*
  2. 对表中的字段进行添加，修改，删除的操作
  3. alter table 表名 add/change/modify/drop .......
  4. */
  6. #添加字段
  7. ALTER TABLE emp ADD eage INT;
  9. #修改字段的名字:ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段的类型;
  10. ALTER TABLE emp CHANGE eage age INT;
  12. #修改字段的类型:很危险的一件事
  13. ALTER TABLE emp MODIFY age INT;
  15. #删除字段
  16. ALTER TABLE emp DROP age;
  18. #修改表的名字
  19. ALTER TABLE emp RENAME TO employee;
  21. #清空表中的内容
  22. TRUNCATE TABLE emp3;

ALTER TABLE ADD
ALTER TABLE CHANGE
ALTER TABLE MODIFY
ALTER TABLE DROP
ALTER TABLE RANAME TO
TRUNCATE TABLE