第01章_数据库概述

1.说说你了解的常见的数据库

  1. Oracle、MySQl、SQL Server、DB2、PGSQL；Redis、MongoDB、ES.....

2.谈谈你对MySQL历史、特点的理解

  1. 历史：
  2. 由瑞典的MySQL AB 公司创立，1995开发出的MySQL
  3. 2008年，MySQL被SUN公司收购
  4. 2009年，Oracle收购SUN公司，进而Oracle就获取了MySQL
  5. 2016年，MySQL8.0.0版本推出
  6. 特点：
  7. 开源的、关系型的数据库
  8. 支持千万级别数据量的存储，大型的数据库

3.说说你对DB、DBMS、SQL的理解

  1. DB：database，看做是数据库文件。 （类似于：.doc、.txt、.mp3、.avi、。。。）
  2. DBMS：数据库管理系统。（类似于word工具、wps工具、记事本工具、qq影音播放器等）
  3. MySQL数据库服务器中安装了MySQL DBMS,使用MySQL DBMS 来管理和操作DB，使用的是SQL语言。

4.你知道哪些非关系型数据库的类型呢？（了解）

  1. 键值型数据库：Redis
  2. 文档型数据库：MongoDB
  3. 搜索引擎数据库：ES、Solr
  4. 列式数据库：HBase
  5. 图形数据库：InfoGrid

5.表与表的记录之间存在哪些关联关系？

  1. ORM思想。（了解）
  2. 表与表的记录之间的关系：一对一关系、一对多关系、多对多关系、自关联 （了解）

第02章_MySQL环境搭建

1.安装好MySQL之后在windows系统中哪些位置能看到MySQL?

  1. MySQL DBMS软件的安装位置。 D:\develop_tools\MySQL\MySQL Server 8.0
  2. MySQL 数据库文件的存放位置。 C:\ProgramData\MySQL\MySQL Server 8.0\Data
  3. MySQL DBMS 的配置文件。 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
  4. MySQL的服务（要想通过客户端能够访问MySQL的服务器，必须保证服务是开启状态的）
  5. MySQL的path环境变量

2.卸载MySQL主要卸载哪几个位置的内容？

  1. 使用控制面板的软件卸载，去卸载MySQL DBMS软件的安装位置。
  2. D:\develop_tools\MySQL\MySQL Server 8.0
  3. 手动删除数据库文件。 C:\ProgramData\MySQL\MySQL Server 8.0\Data
  4. MySQL的环境变量
  5. MySQL的服务进入注册表删除。（ regedit ）
  6. 务必重启电脑

3.能够独立完成MySQL8.0、MySQL5.7版本的下载、安装、配置 （掌握）
4.MySQL5.7在配置完以后，如何修改配置文件？

  1. 为什么要修改my.ini文件？ 默认的数据库使用的字符集是latin1。我们需要修改为：utf8
  2. 修改哪些信息？
  3. 修改完以后，需要重启服务。

5.熟悉常用的数据库管理和操作的工具

  1. 方式1：windows自带的cmd
  2. 方式2：mysql数据库自带的命令行窗口
  3. 方式3：图形化管理工具：Navicat、SQLyog、dbeaver等。
  4. [mysql] #大概在63行左右，在其下添加
  5. ...
  6. default-character-set=utf8 #默认字符集
  7. [mysqld] # 大概在76行左右，在其下添加
  8. ...
  9. character-set-server=utf8
  10. collation-server=utf8_general_ci
  11. net stop mysql服务名;
  12. net start mysql服务名;

第03章_基本的SELECT语句

  1. 【题目】
  2. # 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
  3. # 2.查询employees表中去除重复的job_id以后的数据
  4. # 3.查询工资大于12000的员工姓名和工资
  5. # 4.查询员工号为176的员工的姓名和部门号
  6. # 5.显示表 departments 的结构，并查询其中的全部数据
  1. 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
  2. SELECT employee_id , last_name,salary * 12 "ANNUAL SALARY"
  3. FROM employees;
  4. SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL
  5. SALARY"
  6. FROM employees;
  1. 2.查询employees表中去除重复的job_id以后的数据
  2. SELECT DISTINCT job_id
  3. FROM employees;
  1. 3.查询工资大于12000的员工姓名和工资
  2. SELECT last_name, salary
  3. FROM employees
  4. WHERE salary > 12000;
  1. 4.查询员工号为176的员工的姓名和部门号
  2. SELECT last_name, department_id
  3. FROM employees
  4. WHERE employee_id = 176;
  1. 5.显示表 departments 的结构，并查询其中的全部数据
  2. DESC departments;
  3. SELECT * FROM departments;

第04章_运算符

  1. 【题目】
  2. # 1.选择工资不在5000到12000的员工的姓名和工资
  3. # 2.选择在20或50号部门工作的员工姓名和部门号
  4. # 3.选择公司中没有管理者的员工姓名及job_id
  5. # 4.选择公司中有奖金的员工姓名，工资和奖金级别
  6. # 5.选择员工姓名的第三个字母是a的员工姓名
  7. # 6.选择姓名中有字母a和k的员工姓名
  8. # 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
  9. # 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
  10. # 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
  1. 1.选择工资不在5000到12000的员工的姓名和工资
  2. SELECT last_name, salary
  3. FROM employees
  4. WHERE salary < 5000 OR salary > 12000;
  5. SELECT last_name, salary
  6. FROM employees
  7. WHERE salary NOT BETWEEN 5000 AND 12000;
  1. 2.选择在20或50号部门工作的员工姓名和部门号
  2. SELECT last_name, department_id
  3. FROM employees
  4. WHERE department_id = 20 OR department_id = 50;
  6. SELECT last_name, department_id
  7. FROM employees
  8. WHERE department_id IN(20, 50);
  1. 3.选择公司中没有管理者的员工姓名及job_id
  2. SELECT last_name, job_id
  3. FROM employees
  4. WHERE manager_id IS NULL;
  1.  4.选择公司中有奖金的员工姓名，工资和奖金级别
  2. SELECT last_name, salary, commission_pct
  3. FROM employees
  4. WHERE commission_pct IS NOT NULL;
  1. 5.选择员工姓名的第三个字母是a的员工姓名
  2. SELECT last_name
  3. FROM employees
  4. WHERE last_name LIKE '__a%';
  1. 6.选择姓名中有字母a和k的员工姓名
  2. SELECT last_name
  3. FROM employees
  4. WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
  1.  7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
  3. SELECT employee_id,first_name,last_name
  4. FROM employees
  5. WHERE first_name LIKE '%e';
  7. SELECT employee_id,first_name,last_name
  8. FROM employees
  9. WHERE first_name REGEXP 'e$';
  1. 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
  3. SELECT last_name,job_id
  4. FROM employees
  5. #where department_id in (80,90,100);
  6. WHERE department_id BETWEEN 80 AND 100;
  1.  9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
  3.  SELECT last_name,salary,manager_id
  4. FROM employees
  5. WHERE manager_id IN (100,101,110);

第05章_排序与分页

  1. #1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
  2. #2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
  3. #3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
  1. 答案：
  2. 1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序显示
  3. SELECT last_name,department_id,salary * 12 annual_sal
  4. FROM employees
  5. ORDER BY annual_sal DESC,last_name ASC;
  9. 2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第
  10. 21到40位置的数据
  13. SELECT last_name,salary
  14. FROM employees
  15. WHERE salary NOT BETWEEN 8000 AND 17000
  16. ORDER BY salary DESC
  17. LIMIT 20,20;
  18. 3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号
  19. 升序
  21. SELECT last_name,email,department_id
  22. FROM employees
  23. #where email like '%e%'
  24. WHERE email REGEXP '[e]'
  25. ORDER BY LENGTH(email) DESC,department_id ASC;

第06章_多表查询

多表查询-1

  1. 【题目】
  2. # 1.显示所有员工的姓名，部门号和部门名称。
  3. # 2.查询90号部门员工的job_id和90号部门的location_id
  4. # 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
  5. # 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
  6. # 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
  7. # 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
  8. employees Emp# manager Mgr#
  9. kochhar 101   king  100
  10. # 7.查询哪些部门没有员工
  11. # 8. 查询哪个城市没有部门
  12. # 9. 查询部门名为 Sales 或 IT 的员工信息

# 1.显示所有员工的姓名，部门号和部门名称。

  1. SELECT last_name, e.department_id, department_name
  2. FROM employees e
  3. LEFT OUTER JOIN departments d
  4. ON e.`department_id` = d.`department_id`;

# 2.查询90号部门员工的job_id和90号部门的location_id

  1. SELECT job_id, location_id
  2. FROM employees e, departments d
  3. WHERE e.`department_id` = d.`department_id`
  4. AND e.`department_id` = 90;
  5. or
  6. SELECT job_id, location_id
  7. FROM employees e
  8. JOIN departments d
  9. ON e.`department_id` = d.`department_id`
  10. WHERE e.`department_id` = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city

  1. SELECT last_name , department_name , d.location_id , city
  2. FROM employees e
  3. LEFT OUTER JOIN departments d
  4. ON e.`department_id` = d.`department_id`
  5. LEFT OUTER JOIN locations l
  6. ON d.`location_id` = l.`location_id`
  7. WHERE commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

  1. SELECT last_name , job_id , e.department_id , department_name
  2. FROM employees e, departments d, locations l
  3. WHERE e.`department_id` = d.`department_id`
  4. AND d.`location_id` = l.`location_id`
  5. AND city = 'Toronto';
  7. or 
  8. SELECT last_name , job_id , e.department_id , department_name
  9. FROM employees e
  10. JOIN departments d
  11. ON e.`department_id` = d.`department_id`
  12. JOIN locations l
  13. ON l.`location_id` = d.`location_id`
  14. WHERE l.`city` = 'Toronto';

5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所 在部门的部门名称为’Executive’

  1. SELECT department_name, street_address, last_name, job_id, salary
  2. FROM employees e JOIN departments d
  3. ON e.department_id = d.department_id
  4. JOIN locations l
  5. ON d.`location_id` = l.`location_id`
  6. WHERE department_name = 'Executive'

6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果 类似于下面的格式

  1. employees Emp# manager Mgr#
  2. kochhar 101 king 100
  3. SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
  4. mgr.employee_id "Mgr#"
  5. FROM employees emp
  6. LEFT OUTER JOIN employees mgr
  7. ON emp.manager_id = mgr.employee_id

7.查询哪些部门没有员工

  1. #方式1：
  2. SELECT d.department_id
  3. FROM departments d LEFT JOIN employees e
  4. ON e.department_id = d.`department_id`
  5. WHERE e.department_id IS NULL
  6. #方式2：
  7. SELECT department_id
  8. FROM departments d
  9. WHERE NOT EXISTS (
  10. SELECT *
  11. FROM employees e
  12. WHERE e.`department_id` = d.`department_id`
  13. )

8. 查询哪个城市没有部门

  1. SELECT l.location_id,l.city
  2. FROM locations l LEFT JOIN departments d
  3. ON l.`location_id` = d.`location_id`
  4. WHERE d.`location_id` IS NULL

9. 查询部门名为 Sales 或 IT 的员工信息

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e,departments d
  3. WHERE e.department_id = d.`department_id`
  4. AND d.`department_name` IN ('Sales','IT');

多表查询-2

  1. 1. 所有有门派的人员信息
  2. （ A、B两表共有）
  3. INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
  4. INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
  5. INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
  6. INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
  7. INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
  8. INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
  9. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
  10. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
  11. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
  12. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
  13. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
  14. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
  15. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
  16. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
  17. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
  18. INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
  2. 【题目】
  3. #1.所有有门派的人员信息
  4. （ A、B两表共有）
  5. #2.列出所有用户，并显示其机构信息
  6. （A的全集）
  7. #3.列出所有门派
  8. （B的全集）
  9. #4.所有不入门派的人员
  10. （A的独有）
  11. #5.所有没人入的门派
  12. （B的独有）
  13. #6.列出所有人员和机构的对照关系
  14. (AB全有)
  15. #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
  16. #left join + union(可去除重复数据)+ right join
  17. #7.列出所有没入派的人员和没人入的门派
  18. （A的独有+B的独有）

#1.所有有门派的人员信息 （ A、B两表共有）

  1. select *
  2. from t_emp a inner join t_dept b
  3. on a.deptId = b.id

#2.列出所有用户，并显示其机构信息 （A的全集）

  1. select *
  2. from t_emp a left join t_dept b
  3. on a.deptId = b.id

#3.列出所有门派 （B的全集）

  1. select *
  2. from t_dept b;

#4.所有不入门派的人员 （A的独有）

  1. select *
  2. from t_emp a left join t_dept b
  3. on a.deptId = b.id
  4. where b.id is null;

`#5.所有没人入的门派<br /> （B的独有）

  1. select *
  2. from t_dept b left join t_emp a
  3. on a.deptId = b.id
  4. where a.deptId is null;

#6.列出所有人员和机构的对照关系 (AB全有)

  1. #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
  2. #left join + union(可去除重复数据)+ right join
  4. SELECT *
  5. FROM t_emp A LEFT JOIN t_dept B
  6. ON A.deptId = B.id
  7. UNION
  8. SELECT *
  9. FROM t_emp A RIGHT JOIN t_dept B
  10. ON A.deptId = B.id

#7.列出所有没入派的人员和没人入的门派 （A的独有+B的独有）

  1. SELECT *
  2. FROM t_emp A LEFT JOIN t_dept B
  3. ON A.deptId = B.id
  4. UNION
  5. SELECT *
  6. FROM t_emp A RIGHT JOIN t_dept B
  7. ON A.deptId = B.id
  2. select *
  3. from t_emp a inner join t_dept b
  4. on a.deptId = b.id;
  5. 2. 列出所有用户，并显示其机构信息
  6. （A的全集）
  7. 3. 列出所有门派
  8. （B的全集）
  9. 4. 所有不入门派的人员
  10. （A的独有）
  11. 5. 所有没人入的门派
  12. （B的独有）
  13. 6. 列出所有人员和机构的对照关系
  14. (AB全有)
  15. 7. 列出所有没入派的人员和没人入的门派
  16. （A的独有+B的独有）
  17. select *
  18. from t_emp a left join t_dept b
  19. on a.deptId = b.id;
  20. select *
  21. from t_dept b;
  22. select *
  23. from t_emp a left join t_dept b
  24. on a.deptId = b.id
  25. where b.id is null;
  26. select *
  27. from t_dept b left join t_emp a
  28. on a.deptId = b.id
  29. where a.deptId is null;
  30. #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
  31. #left join + union(可去除重复数据)+ right join
  32. SELECT *
  33. FROM t_emp A LEFT JOIN t_dept B
  34. ON A.deptId = B.id
  35. UNION
  36. SELECT *
  37. FROM t_emp A RIGHT JOIN t_dept B
  38. ON A.deptId = B.id
  39. SELECT *
  40. FROM t_emp A LEFT JOIN t_dept B
  41. ON A.deptId = B.id
  42. WHERE B.`id` IS NULL
  43. UNION
  44. SELECT *
  45. FROM t_emp A RIGHT JOIN t_dept B
  46. ON A.deptId = B.id
  47. WHERE A.`deptId` IS NULL;

#第07章_单行函数的课后练习

  2. # 1.显示系统时间(注：日期+时间)
  3. SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() #大家只需要掌握一个函数就可以了
  4. FROM DUAL;
  6. # 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
  7. SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
  8. FROM employees;
  11. # 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
  12. SELECT last_name,LENGTH(last_name) "name_length"
  13. FROM employees
  14. #order by last_name asc;
  15. ORDER BY name_length ASC;
  18. # 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
  20. SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
  21. FROM employees;
  24. # 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
  26. SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",
  27. TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
  28. FROM employees
  29. ORDER BY worked_years DESC;
  31. # 6.查询员工姓名，hire_date , department_id，满足以下条件：
  32. #雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
  33. SELECT last_name,hire_date,department_id
  34. FROM employees
  35. WHERE department_id IN (80,90,110)
  36. AND commission_pct IS NOT NULL
  37. #and hire_date >= '1997-01-01';  #存在着隐式转换
  38. #and  date_format(hire_date,'%Y-%m-%d') >= '1997-01-01';  # 显式转换操作，格式化：日期---> 字符串
  39. #and  date_format(hire_date,'%Y') >= '1997';   # 显式转换操作，格式化
  40. AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期
  42. # 7.查询公司中入职超过10000天的员工姓名、入职时间
  43. SELECT last_name,hire_date
  44. FROM employees
  45. WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;
  48. # 8.做一个查询，产生下面的结果
  49. #<last_name> earns <salary> monthly but wants <salary*3> 
  51. SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants ',TRUNCATE(salary * 3,0)) "Dream Salary"
  52. FROM employees;
  55. # 9.使用case-when，按照下面的条件：
  56. /*job                  grade
  57. AD_PRES                  A
  58. ST_MAN                   B
  59. IT_PROG                  C
  60. SA_REP                   D
  61. ST_CLERK                 E
  63. 产生下面的结果:
  64. */
  66. SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
  67.                              WHEN 'ST_MAN' THEN 'B'
  68.                              WHEN 'IT_PROG' THEN 'C'
  69.                              WHEN 'SA_REP' THEN 'D'
  70.                              WHEN 'ST_CLERK' THEN 'E'
  71.                              END "Grade"
  72. FROM employees;
  75. SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
  76.                              WHEN 'ST_MAN' THEN 'B'
  77.                              WHEN 'IT_PROG' THEN 'C'
  78.                              WHEN 'SA_REP' THEN 'D'
  79.                              WHEN 'ST_CLERK' THEN 'E'
  80.                              ELSE "undefined" END "Grade"
  81. FROM employees;

第08章_聚合函数

  1. 【题目】
  2. #1.where子句可否使用组函数进行过滤?
  3. #2.查询公司员工工资的最大值，最小值，平均值，总和
  4. #3.查询各job_id的员工工资的最大值，最小值，平均值，总和
  5. #4.选择具有各个job_id的员工人数
  6. # 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
  7. # 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
  8. # 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
  9. # 8.查询每个工种、每个部门的部门名、工种名和最低工资

#1.where子句可否使用组函数进行过滤?

no

#2.查询公司员工工资的最大值，最小值，平均值，总和

  1. SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
  2. FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和

  1. SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
  2. FROM employees
  3. GROUP BY job_id;

#4.选择具有各个job_id的员工人数

  1. SELECT job_id, COUNT(*)
  2. FROM employees
  3. GROUP BY job_id;

5.查询员工最高工资和最低工资的差距（DIFFERENCE）

  1. SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE
  2. FROM employees;

6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

  1. SELECT manager_id, MIN(salary)
  2. FROM employees
  3. WHERE manager_id IS NOT NULL
  4. GROUP BY manager_id
  5. HAVING MIN(salary) > 6000;

7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

  1. SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
  2. FROM employees e RIGHT JOIN departments d
  3. ON e.`department_id` = d.`department_id`
  4. GROUP BY department_name, location_id
  5. ORDER BY avg_sal DESC;

8.查询每个工种、每个部门的部门名、工种名和最低工资

  1. SELECT department_name,job_id,MIN(salary)
  2. FROM departments d LEFT JOIN employees e
  3. ON e.`department_id` = d.`department_id`
  4. GROUP BY department_name,job_id

第09章_子查询

  1. 【题目】 
  3. #1.查询和Zlotkey相同部门的员工姓名和工资
  6. #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 
  8. #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary 
  11. #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 
  13. #5.查询在部门的location_id为1700的部门工作的员工的员工号
  15. #6.查询管理者是King的员工姓名和工资 
  17. #7.查询工资最低的员工信息: last_name, salary
  19. #8.查询平均工资最低的部门信息 
  21. #9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
  23. #10.查询平均工资最高的 job 信息
  25. #11.查询平均工资高于公司平均工资的部门有哪些? 
  27. #12.查询出公司中所有 manager 的详细信息 
  29. #13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
  32. #14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary 
  34. #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
  36. #16. 选择所有没有管理者的员工的last_name 
  38. #17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
  40. #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询） 
  43. #19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询） 
  45. #20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
  2. # 第09章_子查询的课后练习
  6. #1.查询和Zlotkey相同部门的员工姓名和工资
  8. SELECT last_name,salary
  9. FROM employees
  10. WHERE department_id IN (
  11.             SELECT department_id
  12.             FROM employees
  13.             WHERE last_name = 'Zlotkey'
  14.             );
  22. #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
  24. SELECT employee_id,last_name,salary
  25. FROM employees
  26. WHERE salary > (
  27.         SELECT AVG(salary)
  28.         FROM employees
  29.         );
  35. #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
  37. SELECT last_name,job_id,salary
  38. FROM employees
  39. WHERE salary > ALL(
  40.         SELECT salary
  41.         FROM employees
  42.         WHERE job_id = 'SA_MAN'
  43.         );
  46. #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
  48. SELECT employee_id,last_name
  49. FROM employees 
  50. WHERE department_id IN (
  51.             SELECT DISTINCT department_id
  52.             FROM employees
  53.             WHERE last_name LIKE '%u%'
  54.             );
  57. #5.查询在部门的location_id为1700的部门工作的员工的员工号
  59. SELECT employee_id
  60. FROM employees
  61. WHERE department_id IN (
  62.             SELECT department_id
  63.             FROM departments
  64.             WHERE location_id = 1700
  65.             );
  68. #6.查询管理者是King的员工姓名和工资
  70. SELECT last_name,salary,manager_id
  71. FROM employees
  72. WHERE manager_id IN (
  73.             SELECT employee_id
  74.             FROM employees
  75.             WHERE last_name = 'King'
  76.             );
  80. #7.查询工资最低的员工信息: last_name, salary
  82. SELECT last_name,salary
  83. FROM employees
  84. WHERE salary = (
  85.         SELECT MIN(salary)
  86.         FROM employees
  87.         );
  90. #8.查询平均工资最低的部门信息
  91. #方式1：
  92. SELECT *
  93. FROM departments
  94. WHERE department_id = (
  95.             SELECT department_id
  96.             FROM employees
  97.             GROUP BY department_id
  98.             HAVING AVG(salary ) = (
  99.                         SELECT MIN(avg_sal)
  100.                         FROM (
  101.                             SELECT AVG(salary) avg_sal
  102.                             FROM employees
  103.                             GROUP BY department_id
  104.                             ) t_dept_avg_sal
  106.                         )
  107.             );
  108. #方式2：
  110. SELECT *
  111. FROM departments
  112. WHERE department_id = (
  113.             SELECT department_id
  114.             FROM employees
  115.             GROUP BY department_id
  116.             HAVING AVG(salary ) <= ALL(
  117.                         SELECT AVG(salary)
  118.                         FROM employees
  119.                         GROUP BY department_id
  120.                         )
  121.             );
  123. #方式3： LIMIT
  125. SELECT *
  126. FROM departments
  127. WHERE department_id = (
  128.             SELECT department_id
  129.             FROM employees
  130.             GROUP BY department_id
  131.             HAVING AVG(salary ) =(
  132.                         SELECT AVG(salary) avg_sal
  133.                         FROM employees
  134.                         GROUP BY department_id
  135.                         ORDER BY avg_sal ASC
  136.                         LIMIT 1        
  137.                         )
  138.             );
  140. #方式4：
  142. SELECT d.*
  143. FROM departments d,(
  144.         SELECT department_id,AVG(salary) avg_sal
  145.         FROM employees
  146.         GROUP BY department_id
  147.         ORDER BY avg_sal ASC
  148.         LIMIT 0,1
  149.         ) t_dept_avg_sal
  150. WHERE d.`department_id` = t_dept_avg_sal.department_id
  152. #9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
  153. #方式1 
  154. SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
  155. FROM departments d
  156. WHERE department_id = (
  157.             SELECT department_id
  158.             FROM employees
  159.             GROUP BY department_id
  160.             HAVING AVG(salary ) = (
  161.                         SELECT MIN(avg_sal)
  162.                         FROM (
  163.                             SELECT AVG(salary) avg_sal
  164.                             FROM employees
  165.                             GROUP BY department_id
  166.                             ) t_dept_avg_sal
  168.                         )
  169.             );
  171. #方式2：
  173. SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
  174. FROM departments d
  175. WHERE department_id = (
  176.             SELECT department_id
  177.             FROM employees
  178.             GROUP BY department_id
  179.             HAVING AVG(salary ) <= ALL(
  180.                         SELECT AVG(salary)
  181.                         FROM employees
  182.                         GROUP BY department_id
  183.                         )
  184.             );
  186. #方式3： LIMIT
  188. SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
  189. FROM departments d
  190. WHERE department_id = (
  191.             SELECT department_id
  192.             FROM employees
  193.             GROUP BY department_id
  194.             HAVING AVG(salary ) =(
  195.                         SELECT AVG(salary) avg_sal
  196.                         FROM employees
  197.                         GROUP BY department_id
  198.                         ORDER BY avg_sal ASC
  199.                         LIMIT 1        
  200.                         )
  201.             );
  203. #方式4：
  205. SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
  206. FROM departments d,(
  207.         SELECT department_id,AVG(salary) avg_sal
  208.         FROM employees
  209.         GROUP BY department_id
  210.         ORDER BY avg_sal ASC
  211.         LIMIT 0,1
  212.         ) t_dept_avg_sal
  213. WHERE d.`department_id` = t_dept_avg_sal.department_id
  215. #10.查询平均工资最高的 job 信息
  217. #方式1：
  218. SELECT *
  219. FROM jobs
  220. WHERE job_id = (
  221.         SELECT job_id
  222.         FROM employees
  223.         GROUP BY job_id
  224.         HAVING AVG(salary) = (
  225.                     SELECT MAX(avg_sal)
  226.                     FROM (
  227.                         SELECT AVG(salary) avg_sal
  228.                         FROM employees
  229.                         GROUP BY job_id
  230.                         ) t_job_avg_sal
  231.                     )
  232.         );
  234. #方式2：
  235. SELECT *
  236. FROM jobs
  237. WHERE job_id = (
  238.         SELECT job_id
  239.         FROM employees
  240.         GROUP BY job_id
  241.         HAVING AVG(salary) >= ALL(
  242.                      SELECT AVG(salary) 
  243.                      FROM employees
  244.                      GROUP BY job_id
  245.                      )
  246.         );
  248. #方式3：
  249. SELECT *
  250. FROM jobs
  251. WHERE job_id = (
  252.         SELECT job_id
  253.         FROM employees
  254.         GROUP BY job_id
  255.         HAVING AVG(salary) =(
  256.                      SELECT AVG(salary) avg_sal
  257.                      FROM employees
  258.                      GROUP BY job_id
  259.                      ORDER BY avg_sal DESC
  260.                      LIMIT 0,1
  261.                      )
  262.         );
  264. #方式4：
  265. SELECT j.*
  266. FROM jobs j,(
  267.         SELECT job_id,AVG(salary) avg_sal
  268.         FROM employees
  269.         GROUP BY job_id
  270.         ORDER BY avg_sal DESC
  271.         LIMIT 0,1        
  272.         ) t_job_avg_sal
  273. WHERE j.job_id = t_job_avg_sal.job_id
  275. #11.查询平均工资高于公司平均工资的部门有哪些?
  277. SELECT department_id
  278. FROM employees
  279. WHERE department_id IS NOT NULL
  280. GROUP BY department_id
  281. HAVING AVG(salary) > (
  282.             SELECT AVG(salary)
  283.             FROM employees
  284.             );
  287. #12.查询出公司中所有 manager 的详细信息
  289. #方式1：自连接  xxx worked for yyy
  290. SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
  291. FROM employees emp JOIN employees mgr
  292. ON emp.manager_id = mgr.employee_id;
  294. #方式2：子查询
  296. SELECT employee_id,last_name,job_id,department_id
  297. FROM employees
  298. WHERE employee_id IN (
  299.             SELECT DISTINCT manager_id
  300.             FROM employees
  301.             );
  303. #方式3：使用EXISTS
  304. SELECT employee_id,last_name,job_id,department_id
  305. FROM employees e1
  306. WHERE EXISTS (
  307.            SELECT *
  308.            FROM employees e2
  309.            WHERE e1.`employee_id` = e2.`manager_id`
  310.          );
  313. #13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
  315. #方式1：
  316. SELECT MIN(salary)
  317. FROM employees
  318. WHERE department_id = (
  319.             SELECT department_id
  320.             FROM employees
  321.             GROUP BY department_id
  322.             HAVING MAX(salary) = (
  323.                         SELECT MIN(max_sal)
  324.                         FROM (
  325.                             SELECT MAX(salary) max_sal
  326.                             FROM employees
  327.                             GROUP BY department_id
  328.                             ) t_dept_max_sal
  329.                         )
  330.             );
  332. SELECT *
  333. FROM employees
  334. WHERE department_id = 10;
  336. #方式2：
  337. SELECT MIN(salary)
  338. FROM employees
  339. WHERE department_id = (
  340.             SELECT department_id
  341.             FROM employees
  342.             GROUP BY department_id
  343.             HAVING MAX(salary) <= ALL (
  344.                         SELECT MAX(salary)
  345.                         FROM employees
  346.                         GROUP BY department_id
  347.                         )
  348.             );
  350. #方式3：
  351. SELECT MIN(salary)
  352. FROM employees
  353. WHERE department_id = (
  354.             SELECT department_id
  355.             FROM employees
  356.             GROUP BY department_id
  357.             HAVING MAX(salary) = (
  358.                         SELECT MAX(salary) max_sal
  359.                         FROM employees
  360.                         GROUP BY department_id
  361.                         ORDER BY max_sal ASC
  362.                         LIMIT 0,1
  363.                         )
  364.             );
  366. #方式4：
  367. SELECT MIN(salary)
  368. FROM employees e,(
  369.         SELECT department_id,MAX(salary) max_sal
  370.         FROM employees
  371.         GROUP BY department_id
  372.         ORDER BY max_sal ASC
  373.         LIMIT 0,1
  374.         ) t_dept_max_sal
  375. WHERE e.department_id = t_dept_max_sal.department_id
  378. #14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
  379. #方式1：
  380. SELECT last_name, department_id, email, salary
  381. FROM employees
  382. WHERE employee_id = ANY (
  383.             SELECT DISTINCT manager_id
  384.             FROM employees
  385.             WHERE department_id = (
  386.                         SELECT department_id
  387.                         FROM employees
  388.                         GROUP BY department_id
  389.                         HAVING AVG(salary) = (
  390.                                     SELECT MAX(avg_sal)
  391.                                     FROM (
  392.                                         SELECT AVG(salary) avg_sal
  393.                                         FROM employees
  394.                                         GROUP BY department_id
  395.                                         ) t_dept_avg_sal
  396.                                     )
  397.                         )
  398.             );
  400. #方式2：
  401. SELECT last_name, department_id, email, salary
  402. FROM employees
  403. WHERE employee_id = ANY (
  404.             SELECT DISTINCT manager_id
  405.             FROM employees
  406.             WHERE department_id = (
  407.                         SELECT department_id
  408.                         FROM employees
  409.                         GROUP BY department_id
  410.                         HAVING AVG(salary) >= ALL (
  411.                                 SELECT AVG(salary) avg_sal
  412.                                 FROM employees
  413.                                 GROUP BY department_id
  414.                                 )
  415.                         )
  416.             );
  418. #方式3：
  419. SELECT last_name, department_id, email, salary
  420. FROM employees
  421. WHERE employee_id IN (
  422.             SELECT DISTINCT manager_id
  423.             FROM employees e,(
  424.                     SELECT department_id,AVG(salary) avg_sal
  425.                     FROM employees
  426.                     GROUP BY department_id
  427.                     ORDER BY avg_sal DESC
  428.                     LIMIT 0,1
  429.                     ) t_dept_avg_sal
  430.             WHERE e.`department_id` = t_dept_avg_sal.department_id
  431.             );
  434. #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
  435. #方式1：
  436. SELECT department_id
  437. FROM departments
  438. WHERE department_id NOT IN (
  439.             SELECT DISTINCT department_id
  440.             FROM employees
  441.             WHERE job_id = 'ST_CLERK'
  442.             );
  444. #方式2：
  445. SELECT department_id
  446. FROM departments d
  447. WHERE NOT EXISTS (
  448.         SELECT *
  449.         FROM employees e
  450.         WHERE d.`department_id` = e.`department_id`
  451.         AND e.`job_id` = 'ST_CLERK'
  452.         );
  456. #16. 选择所有没有管理者的员工的last_name
  458. SELECT last_name
  459. FROM employees emp
  460. WHERE NOT EXISTS (
  461.         SELECT *
  462.         FROM employees mgr
  463.         WHERE emp.`manager_id` = mgr.`employee_id`
  464.         );
  466. #17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
  467. #方式1：
  468. SELECT employee_id,last_name,hire_date,salary
  469. FROM employees
  470. WHERE manager_id IN (
  471.         SELECT employee_id
  472.         FROM employees
  473.         WHERE last_name = 'De Haan'
  474.         );
  476. #方式2：
  477. SELECT employee_id,last_name,hire_date,salary
  478. FROM employees e1
  479. WHERE EXISTS (
  480.         SELECT *
  481.         FROM employees e2
  482.         WHERE e1.`manager_id` = e2.`employee_id`
  483.         AND e2.last_name = 'De Haan'
  484.         ); 
  487. #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
  489. #方式1：使用相关子查询
  490. SELECT last_name,salary,department_id
  491. FROM employees e1
  492. WHERE salary > (
  493.         SELECT AVG(salary)
  494.         FROM employees e2
  495.         WHERE department_id = e1.`department_id`
  496.         );
  498. #方式2：在FROM中声明子查询
  499. SELECT e.last_name,e.salary,e.department_id
  500. FROM employees e,(
  501.         SELECT department_id,AVG(salary) avg_sal
  502.         FROM employees
  503.         GROUP BY department_id) t_dept_avg_sal
  504. WHERE e.department_id = t_dept_avg_sal.department_id
  505. AND e.salary > t_dept_avg_sal.avg_sal
  508. #19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
  510. SELECT department_name
  511. FROM departments d
  512. WHERE 5 < (
  513.        SELECT COUNT(*)
  514.        FROM employees e
  515.        WHERE d.department_id = e.`department_id`
  516.       );
  519. #20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
  521. SELECT * FROM locations;
  523. SELECT country_id
  524. FROM locations l
  525. WHERE 2 < (
  526.        SELECT COUNT(*)
  527.        FROM departments d
  528.        WHERE l.`location_id` = d.`location_id`
  529.      );
  531. /* 
  532. 子查询的编写技巧（或步骤）：① 从里往外写  ② 从外往里写
  534. 如何选择？
  535. ① 如果子查询相对较简单，建议从外往里写。一旦子查询结构较复杂，则建议从里往外写
  536. ② 如果是相关子查询的话，通常都是从外往里写。
  538. */

第10章_创建和管理表

练习1

题目：

  1. #1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作 
  4. #2. 创建表dept01 /*字段 类型 id INT(7) NAME VARCHAR(25) */
  6. #3. 将表departments中的数据插入新表dept02中 
  8. #4. 创建表emp01 /*字段 类型 id INT(7)
  9. first_name VARCHAR (25) last_name VARCHAR(25) dept_id INT(7) */
  12. #5. 将列last_name的长度增加到50 
  13. #6. 根据表employees创建emp02
  15. #7. 删除表emp01 
  17. #8. 将表emp02重命名为emp01 
  19. #9.在表dept02和emp01中添加新列test_column，并检查所作的操作
  21. #10.直接删除表emp01中的列 department_id

答案：

  1. # 第10章_创建和管理表的课后练习
  3. #练习1：
  4. #1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作
  6. CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';
  8. USE test01_office;
  10. #2.    创建表dept01
  11. /*
  12. 字段      类型
  13. id     INT(7)
  14. NAME     VARCHAR(25)
  15. */
  16. CREATE TABLE IF NOT EXISTS dept01(
  17. id INT(7),
  18. `name` VARCHAR(25)
  19. );
  22. #3.将表departments中的数据插入新表dept02中
  23. CREATE TABLE dept02
  24. AS
  25. SELECT *
  26. FROM atguigudb.departments;
  29. #4.    创建表emp01
  30. /*
  31. 字段            类型
  32. id        INT(7)
  33. first_name    VARCHAR (25)
  34. last_name    VARCHAR(25)
  35. dept_id        INT(7)
  36. */
  38. CREATE TABLE emp01(
  39. id INT(7),
  40. first_name VARCHAR(25),
  41. last_name VARCHAR(25),
  42. dept_id INT(7)
  43. );
  45. #5.将列last_name的长度增加到50
  46. DESC emp01;
  48. ALTER TABLE emp01
  49. MODIFY last_name VARCHAR(50);
  51. #6.根据表employees创建emp02
  52. CREATE TABLE emp02
  53. AS
  54. SELECT *
  55. FROM atguigudb.`employees`;
  57. SHOW TABLES FROM test01_office;
  59. #7.删除表emp01
  60. DROP TABLE emp01;
  63. #8.将表emp02重命名为emp01
  64. #alter table emp02 rename to emp01;
  65. RENAME TABLE emp02 TO emp01;
  67. #9.在表dept02和emp01中添加新列test_column，并检查所作的操作
  68. ALTER TABLE emp01 ADD test_column VARCHAR(10);
  70. DESC emp01;
  72. ALTER TABLE dept02 ADD test_column VARCHAR(10);
  74. DESC dept02;
  77. #10.直接删除表emp01中的列 department_id
  79. ALTER TABLE emp01
  80. DROP COLUMN department_id;
  82. #练习2：
  85. #练习3：

练习2

  1. # 1、创建数据库 test02_market
  2. # 2、创建数据表 customers

练习篇 - 图1

  1. # 3、将 c_contact 字段移动到 c_birth 字段后面
  2. # 4、将 c_name 字段数据类型改为 varchar(70)
  3. # 5、将c_contact字段改名为c_phone 
  4. # 6、增加c_gender字段到c_name后面，数据类型为char(1) 
  5. # 7、将表名改为customers_info
  6. # 8、删除字段c_city

答案：

  1. # 1、创建数据库 test02_market
  3. CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';
  5. USE test02_market;
  7. SHOW CREATE DATABASE test02_market;
  9. # 2、创建数据表 customers
  10. CREATE TABLE IF NOT EXISTS customers(
  11. c_num INT,
  12. c_name VARCHAR(50),
  13. c_contact VARCHAR(50),
  14. c_city VARCHAR(50),
  15. c_birth DATE
  16. );
  18. SHOW TABLES;
  20. # 3、将 c_contact 字段移动到 c_birth 字段后面
  21. DESC customers;
  23. ALTER TABLE customers
  24. MODIFY c_contact VARCHAR(50) AFTER c_birth;
  26. # 4、将 c_name 字段数据类型改为 varchar(70)
  27. ALTER TABLE customers
  28. MODIFY c_name VARCHAR(70) ;
  30. # 5、将c_contact字段改名为c_phone
  31. ALTER TABLE customers
  32. CHANGE c_contact c_phone VARCHAR(50);
  34. # 6、增加c_gender字段到c_name后面，数据类型为char(1)
  35. ALTER TABLE customers
  36. ADD c_gender CHAR(1) AFTER c_name;
  38. # 7、将表名改为customers_info
  39. RENAME TABLE customers
  40. TO customers_info;
  42. DESC customers_info;
  44. # 8、删除字段c_city
  45. ALTER TABLE customers_info
  46. DROP COLUMN c_city;

练习3

  1. # 1、创建数据库test03_company # 2、创建表offices

练习篇 - 图2

  1. # 3、创建表employees

练习篇 - 图3

  1. # 4、将表employees的mobile字段修改到code字段后面 
  2. # 5、将表employees的birth字段改名为birthday 
  3. # 6、修改sex字段，数据类型为char(1) 
  4. # 7、删除字段note 
  5. # 8、增加字段名favoriate_activity，数据类型为varchar(100) 
  6. # 9、将表employees的名称修改为 employees_info

答案：

  1. # 1、创建数据库test03_company
  2. CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';
  4. USE test03_company;
  6. # 2、创建表offices
  7. CREATE TABLE IF NOT EXISTS offices(
  8. officeCode INT,
  9. city VARCHAR(30),
  10. address VARCHAR(50),
  11. country VARCHAR(50),
  12. postalCode VARCHAR(25)
  13. );
  15. DESC offices;
  17. # 3、创建表employees
  18. CREATE TABLE IF NOT EXISTS employees(
  19. empNum INT,
  20. lastName VARCHAR(50),
  21. firstName VARCHAR(50),
  22. mobile VARCHAR(25),
  23. `code` INT,
  24. jobTitle VARCHAR(50),
  25. birth DATE,
  26. note VARCHAR(255),
  27. sex VARCHAR(5)
  29. );
  31. DESC employees;
  33. # 4、将表employees的mobile字段修改到code字段后面
  34. ALTER TABLE employees
  35. MODIFY mobile VARCHAR(20) AFTER `code`;
  37. # 5、将表employees的birth字段改名为birthday
  38. ALTER TABLE employees
  39. CHANGE birth birthday DATE;
  41. # 6、修改sex字段，数据类型为char(1)
  42. ALTER TABLE employees
  43. MODIFY sex CHAR(1);
  45. # 7、删除字段note
  46. ALTER TABLE employees
  47. DROP COLUMN note;
  49. # 8、增加字段名favoriate_activity，数据类型为varchar(100)
  50. ALTER TABLE employees
  51. ADD favoriate_activity VARCHAR(100);
  53. # 9、将表employees的名称修改为 employees_info
  54. RENAME TABLE employees TO employees_info;
  56. DESC employees_info;

11章_数据处理之增删改

练习1

题目：

  1. #1. 创建数据库dbtest11 
  2. CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8'; 
  5. #2. 运行以下脚本创建表my_employees
  6. USE dbtest11;
  7. CREATE TABLE my_employees( id INT(10),
  8. first_name VARCHAR(10), 
  9. last_name VARCHAR(10), 
  10. userid VARCHAR(10), 
  11. alary DOUBLE(10,2) );
  13. CREATE TABLE users( 
  14. id INT,
  15. userid VARCHAR(10), 
  16. department_id INT );
  18. #3. 显示表my_employees的结构 
  20. #4. 向my_employees表中插入下列数据 
  21. ID FIRST_NAME LAST_NAME USERID SALARY 
  22. 1  patel Ralph Rpatel  895
  23. 2  Dancs Betty Bdancs  860 
  24. 3  Biri Ben   Bbiri     1100 
  25. 4  Newman Chad Cnewman 750 
  26. 5 Ropeburn Audrey Aropebur 1550 
  29. #5. 向users表中插入数据
  30. 1 Rpatel 10 
  31. 2 Bdancs 10 
  32. 3 Bbiri 20 
  33. 4 Cnewman 30 
  34. 5 Aropebur 40
  36. #6. 将3号员工的last_name修改为“drelxer”
  38. #7. 将所有工资少于900的员工的工资修改为1000 
  39. #8. 将userid为Bbiri的user表和my_employees表的记录全部删除 
  40. #9. 删除my_employees、users表所有数据 
  41. #10. 检查所作的修正
  42. #11. 清空表my_employees

答案：

  1. #第11章_数据处理之增删改的课后练习
  2. #练习1：
  3. #1. 创建数据库dbtest11
  4. CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';
  6. #2. 运行以下脚本创建表my_employees
  7. USE dbtest11;
  9. CREATE TABLE my_employees(
  10.     id INT(10),
  11.     first_name VARCHAR(10),
  12.     last_name VARCHAR(10),
  13.     userid VARCHAR(10),
  14.     salary DOUBLE(10,2)
  15. );
  17. CREATE TABLE users(
  18.     id INT,
  19.     userid VARCHAR(10),
  20.     department_id INT
  21. );
  22. #3.显示表my_employees的结构
  24. DESC my_employees;
  26. DESC users;
  28. #4.向my_employees表中插入下列数据
  29. ID    FIRST_NAME    LAST_NAME    USERID        SALARY
  30. 1    patel        Ralph        Rpatel        895
  31. 2    Dancs        Betty        Bdancs        860
  32. 3    Biri        Ben        Bbiri        1100
  33. 4    Newman        Chad        Cnewman        750
  34. 5    Ropeburn    Audrey        Aropebur    1550
  36. INSERT INTO my_employees
  37. VALUES(1,'patel','Ralph','Rpatel',895);
  39. INSERT INTO my_employees VALUES
  40. (2,'Dancs','Betty','Bdancs',860),
  41. (3,'Biri','Ben','Bbiri',1100),
  42. (4,'Newman','Chad','Cnewman',750),
  43. (5,'Ropeburn','Audrey','Aropebur',1550);
  45. SELECT * FROM my_employees;
  47. DELETE FROM my_employees;
  49. #方式2：
  50. INSERT INTO my_employees
  51. SELECT 1,'patel','Ralph','Rpatel',895 UNION ALL
  52. SELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL
  53. SELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL
  54. SELECT 4,'Newman','Chad','Cnewman',750 UNION ALL
  55. SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
  57. #5.向users表中插入数据
  58. 1    Rpatel        10
  59. 2    Bdancs        10
  60. 3    Bbiri        20
  61. 4    Cnewman        30
  62. 5    Aropebur    40
  63. INSERT INTO users VALUES
  64. (1,'Rpatel',10),
  65. (2,'Bdancs',10),
  66. (3,'Bbiri',20),
  67. (4,'Cnewman',30),
  68. (5,'Aropebur',40)
  70. SELECT * FROM users;
  74. #6. 将3号员工的last_name修改为“drelxer”
  75. UPDATE my_employees
  76. SET last_name = 'drelxer'
  77. WHERE id = 3;
  79. #7. 将所有工资少于900的员工的工资修改为1000
  80. UPDATE my_employees
  81. SET salary = 1000
  82. WHERE salary < 900;
  84. #8. 将userid为Bbiri的users表和my_employees表的记录全部删除
  86. #方式1：
  87. DELETE FROM my_employees
  88. WHERE userid = 'Bbiri';
  90. DELETE FROM users
  91. WHERE userid = 'Bbiri';
  93. #方式2：
  95. DELETE m,u
  96. FROM my_employees m
  97. JOIN users u
  98. ON m.userid = u.userid
  99. WHERE m.userid = 'Bbiri';
  101. SELECT * FROM my_employees;
  102. SELECT * FROM users;
  104. #9. 删除my_employees、users表所有数据
  105. DELETE FROM my_employees;
  106. DELETE FROM users;
  108. #10. 检查所作的修正
  109. SELECT * FROM my_employees;
  110. SELECT * FROM users;
  112. #11. 清空表my_employees
  113. TRUNCATE TABLE my_employees;

练习2

  1. # 1. 使用现有数据库dbtest11 
  2. # 2. 创建表格pet
  1. # 3. 添加记录

练习篇 - 图4

  1. # 4. 添加字段:主人的生日owner_birth DATE类型。
  2. # 5. 将名称为Claws的猫的主人改为kevin
  3. # 6. 将没有死的狗的主人改为duck 
  4. # 7. 查询没有主人的宠物的名字； 
  5. # 8. 查询已经死了的cat的姓名，主人，以及去世时间； 
  6. # 9. 删除已经死亡的狗
  7. # 10. 查询所有宠物信息

答案：

  1. #练习2：
  2. # 1. 使用现有数据库dbtest11
  3. USE dbtest11;
  5. # 2. 创建表格pet
  6. CREATE TABLE pet(
  7. NAME VARCHAR(20),
  8. OWNER VARCHAR(20),
  9. species VARCHAR(20),
  10. sex CHAR(1),
  11. birth YEAR,
  12. death YEAR
  13. );
  15. DESC pet;
  17. # 3. 添加记录
  18. INSERT INTO pet VALUES
  19. ('Fluffy','harold','Cat','f','2003','2010'),
  20. ('Claws','gwen','Cat','m','2004',NULL),
  21. ('Buffy',NULL,'Dog','f','2009',NULL),
  22. ('Fang','benny','Dog','m','2000',NULL),
  23. ('bowser','diane','Dog','m','2003','2009'),
  24. ('Chirpy',NULL,'Bird','f','2008',NULL);
  26. SELECT *
  27. FROM pet;
  29. # 4. 添加字段:主人的生日owner_birth DATE类型。
  30. ALTER TABLE pet
  31. ADD owner_birth DATE;
  33. # 5. 将名称为Claws的猫的主人改为kevin
  34. UPDATE pet
  35. SET OWNER = 'kevin'
  36. WHERE NAME = 'Claws' AND species = 'Cat';
  38. # 6. 将没有死的狗的主人改为duck
  39. UPDATE pet
  40. SET OWNER = 'duck'
  41. WHERE death IS NULL AND species = 'Dog';
  43. # 7. 查询没有主人的宠物的名字；
  44. SELECT NAME
  45. FROM pet
  46. WHERE OWNER IS NULL;
  48. # 8. 查询已经死了的cat的姓名，主人，以及去世时间；
  49. SELECT NAME,OWNER,death
  50. FROM pet
  51. WHERE death IS NOT NULL;
  53. # 9. 删除已经死亡的狗
  54. DELETE FROM pet
  55. WHERE death IS NOT NULL 
  56. AND species = 'Dog';
  58. # 10. 查询所有宠物信息
  59. SELECT * 
  60. FROM pet;

练习3

  1. # 1. 使用已有的数据库dbtest11 
  3. # 2. 创建表employee，并添加记录

练习篇 - 图5

  1. # 3. 查询出薪资在1200~1300之间的员工信息。
  2. # 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。 
  3. # 5. 将“李四”的家庭住址改为“广东韶关” 
  4. # 6. 查询出名字中带“小”的员工

答案：

  1. #练习3：
  2. # 1. 使用已有的数据库dbtest11
  3. USE dbtest11;
  4. # 2. 创建表employee，并添加记录
  5. CREATE TABLE employee(
  6. id INT,
  7. NAME VARCHAR(15),
  8. sex CHAR(1),
  9. tel VARCHAR(25),
  10. addr VARCHAR(35),
  11. salary DOUBLE(10,2)
  13. );
  15. INSERT INTO employee VALUES
  16. (10001,'张一一','男','13456789000','山东青岛',1001.58),
  17. (10002,'刘小红','女','13454319000','河北保定',1201.21),
  18. (10003,'李四','男','0751-1234567','广东佛山',1004.11),
  19. (10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
  20. (10005,'王艳','男','020-1232133','广东广州',1405.16);
  22. SELECT * FROM employee;
  24. # 3. 查询出薪资在1200~1300之间的员工信息。
  25. SELECT *
  26. FROM employee
  27. WHERE salary BETWEEN 1200 AND 1300;
  29. # 4. 查询出姓“刘”的员工的工号，姓名，家庭住址。
  30. SELECT id,NAME,addr
  31. FROM employee
  32. WHERE NAME LIKE '刘%';
  34. # 5. 将“李四”的家庭住址改为“广东韶关”
  35. UPDATE employee
  36. SET addr = '广东韶关'
  37. WHERE NAME = '李四';
  39. # 6. 查询出名字中带“小”的员工
  40. SELECT *
  41. FROM employee
  42. WHERE NAME LIKE '%小%';

第13章_约束

基础练习：

练习1

已经存在数据库

test04_emp，两张表emp2和dept2

  1. CREATE DATABASE test04_emp;
  2. use test04_emp; 
  5. CREATE TABLE emp2( id INT, emp_name VARCHAR(15) );
  8. CREATE TABLE dept2( id INT, dept_name VARCHAR(15) );

题目：

  1. #1.向表emp2的id列中添加PRIMARY KEY约束 
  3. #2. 向表dept2的id列中添加PRIMARY KEY约束 
  5. #3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。

答案

  1. #1.向表emp2的id列中添加PRIMARY KEY约束 
  3. ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE emp2 ADD PRIMARY KEY(id); 
  6. #2. 向表dept2的id列中添加PRIMARY KEY约束 
  8. ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY; 
  9. ALTER TABLE dept2 ADD PRIMARY KEY(id);
  11. #3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。 
  13. ALTER TABLE emp2 ADD COLUMN dept_id INT; ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES dept2(id);

练习2

承接《第11章_数据处理之增删改》的综合案例。

  1. # 1、创建数据库test01_library 
  2. # 2、创建表 books，表结构如下
  1. # 3、使用ALTER语句给books按如下要求增加相应的约束

答案：

  1. # 1、2、略
  3. # 3、使用ALTER语句给books按如下要求增加相应的约束 #给id增加主键约束 
  4. ALTER TABLE books ADD PRIMARY KEY(id); #给id字段增加自增约束 
  5. ALTER TABLE books MODIFY id INT AUTO_INCREMENT; #给name等字段增加非空约束 
  6. ALTER TABLE books name VARCHAR(50) NOT NULL;
  7. ALTER TABLE books `authors` VARCHAR(100) NOT NULL; 
  8. ALTER TABLE books price FLOAT NOT NULL; 
  9. ALTER TABLE books pubdate DATE NOT NULL;
  10. ALTER TABLE books num INT NOT NULL;

练习3

题目：

  1. #1. 创建数据库test04_company 
  2. #2. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees

offices表：

练习篇 - 图6

employees表：

练习篇 - 图7

  1. #3. 将表employees的mobile字段修改到officeCode字段后面 
  3. #4. 将表employees的birth字段改名为employee_birth
  5. #5. 修改sex字段，数据类型为CHAR(1)，非空约束 
  7. #6. 删除字段note 
  9. #7. 增加字段名favoriate_activity，数据类型为VARCHAR(100)
  11. #8. 将表employees名称修改为employees_info

答案：

  1. #1. 创建数据库test04_company 
  2. CREATE DATABASE test04_company; 
  4. #2. 按照下表给出的表结构在test04_company数据库中创建两个数据表offices和employees
  5. USE test04_company;
  7. CREATE TABLE offices( 
  8. officeCode INT(10) ,
  9. city VARCHAR(50) NOT NULL,
  10. address VARCHAR(50), 
  11. country VARCHAR(50) NOT NULL, 
  12. postalCode VARCHAR(15) UNIQUE,
  13. PRIMARY KEY(officeCode) );
  15. CREATE TABLE employees( 
  16. employeeNumber INT(11) PRIMARY KEY AUTO_INCREMENT, 
  17. lastName VARCHAR(50) NOT NULL,
  18. firstName VARCHAR(50) NOT NULL,
  19. mobile VARCHAR(25) UNIQUE, officeCode INT(10) NOT NULL, 
  20. jobTitle VARCHAR(50) NOT NULL, birth DATETIME NOT NULL, 
  21. note VARCHAR(255), sex VARCHAR(5),
  22. CONSTRAINT fk_emp_ofCode FOREIGN KEY(officeCode) REFERENCES offices(officeCode) );
  24. #3. 将表employees的mobile字段修改到officeCode字段后面
  25. ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER officeCode;
  27. #4. 将表employees的birth字段改名为employee_birth
  28. ALTER TABLE employees CHANGE birth employee_birth DATETIME;
  30. #5. 修改sex字段，数据类型为CHAR(1)，非空约束 
  32. ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL; 
  34. #6. 删除字段note 
  36. ALTER TABLE employees DROP COLUMN note; 
  38. #7. 增加字段名favoriate_activity，数据类型为VARCHAR(100) 
  39. ALTER TABLE employees ADD favoriate_activity VARCHAR(100); 
  41. #8. 将表employees名称修改为employees_info
  42. ALTER TABLE employees RENAME employees_info;

拓展练习：

练习1

创建数据库test04_Market，在test04_Market中创建数据表customers。customers表结构如下所示，按以下

要求进行操作。

练习篇 - 图8

  1. （1）创建数据库test04_Market。 
  3. （2）创建数据表customers，在c_num字段上添加主键约束和自增约
  4. 束，在c_birth字段上添加非空约束。 
  6. （3）将c_contact字段插入c_birth字段后面。 
  8. （4）将c_name字段数据类型改为VARCHAR(70)。 
  10. 5）将c_contact字段改名为c_phone。 
  11. （6）增加c_gender字段，数据类型为CHAR(1)。 
  13. （7）将表名修改为customers_info。 
  15. （8）删除字段c_city。 在test04_Market中创建数据表orders。orders表结构如下所示，按以下要求进行操作。

练习篇 - 图9

  1. （1）创建数据表orders，在o_num字段上添加主键约束和自增约束，在c_id字段上添加外键约束，关联
  2. customers表中的主键c_num。 
  4. （2）删除orders表的外键约束，然后删除表customers。

练习2

  1. 创建数据表pet，并对表进行插入、更新与删除操作。pet表结构如下表所示。 （
  3. 1）首先创建数据表
  5. pet，使用不同的方法将表记录插入到pet表中。 （
  7. 2）使用UPDATE语句将名称为Fang的狗的主人改为
  9. Kevin。 （
  11. 3）将没有主人的宠物的owner字段值都改为Duck。 （
  13. 4）删除已经死亡的宠物记录。 （
  15. 5）删除所有表中的记录。

pet表结构：

练习篇 - 图10

pet表中记录：

练习篇 - 图11

练习3

  1.  1、创建数据库：test_company 
  3.  2、在此数据库下创建如下3表，数据类型，宽度，是否为空根据实际情况自己定义。
  4. A． 部门表（department） ：部门编号（depid），部门名称（depname），部门简介（deinfo）；其中
  5. 部门编号为主键。
  7. B． 雇员表（emoloyee） ：雇员编号（empid），姓名（name），性别（sex），职称（title），出生日
  8. 期（birthday），所在部门编号（depid）；其中
  9.  * 雇员编号为主键；
  10.  * 部门编号为外键，外键约束等级为（on update cascade 和on delete set null）；
  11.  * 性别默认为男
  12.  C． 工资表（salary） ：雇员编号（empid），基本工资（basesalary），职务工资（titlesalary），扣
  13. 除（deduction）。其中雇员编号为主键。
  14. 3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
  16. C． 工资表（salary） ：雇员编号（empid），基本工资（basesalary），职务工资（titlesalary），扣
  17. 除（deduction）。其中雇员编号为主键。
  18. 3、给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
  20. 4、添加数据如下：

部门表：

练习篇 - 图12

雇员表：

练习篇 - 图13

工资表：

练习篇 - 图14

  1. 5、查询出每个雇员的雇员编号，姓名，职称，所在部门名称，应发工资（基本工资+职务工资），实发
  2. 工资（基本工资+职务工资-扣除）。
  3. 6、查询销售部门的雇员姓名及其基本工资
  4. 7、查询姓“张”且年龄小于40的员工的全部信息和年龄
  5. 8、查询所有男员工的基本工资和职务工资
  6. 9、查询基本工资低于2000的员工姓名和职称、所在部门名称
  7. 10、查询员工总数
  8. 11、查询部门总数
  9. 12、查询应发工资的平均工资和最高工资、最低工资
  11. 13、按照部门统计应发工资的平均工资
  12. 14、找出部门基本工资的平均工资低于2000的 15、按照员工编号、姓名、基本工资、职务工资、扣除，并按照职务升序排列，如果职务工资相同，再
  13. 按照基本工资升序排列
  14. 16、查询员工编号、姓名，出生日期，及年龄段。其中，如果80年之前出生的，定为”老年“；80后定为”
  15. 中年“，90后定为”青壮年“ 17、查询所有的员工信息，和他所在的部门名称
  16. 18、查询所有部门信息，和该部门的员工信息
  17. 19、查询所有职位中含“工程师”的男员工的人数
  18. 20、查询每个部门的男生和女生的人数和平均基本工资
  1. #创建数据库：test_company
  3. CREATE DATABASE test_company; 
  5. #使用数据库test_company USE test_company; 
  7. #创建部门表（department）
  9. CREATE TABLE department( 
  10. depid INT PRIMARY KEY,
  11. depname VARCHAR(20) NOT NULL, 
  12. deinfo VARCHAR(50) );#创建雇员表（emoloyee） 
  15. CREATE TABLE employee(
  17. empid INT PRIMARY KEY, 
  18. `name` VARCHAR(20) NOT NULL, 
  19. sex CHAR NOT NULL DEFAULT '男', 
  20. title VARCHAR(20) NOT NULL, 
  21. birthday DATE, depid INT, 
  22. FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET NULL );
  25. #创建工资表（salary） 
  26. CREATE TABLE salary( 
  27. empid INT PRIMARY KEY,
  28. basesalary DOUBLE, 
  29. titlesalary DOUBLE, 
  30. deduction DOUBLE );
  31. #给工资表（salary）的雇员编号（empid）增加外键约束，外键约束等级为（on update cascade 和on delete cascade）
  33. ALTER TABLE salary ADD FOREIGN KEY empid REFERENCES employee(empid) ON UPDATE CASCADE ON DELETE CASCADE;
  35. #添加部门表数据 
  36. INSERT INTO department 
  37. VALUES 
  38. (111,'生产部',NULL), 
  39. (222,'销售部',NULL)
  40. (333,'人事部','人力资源管理'); 
  42. #添加雇员表
  44. INSERT INTO employee 
  45. VALUES
  46. (1001,'张三',DEFAULT,'高级工程师','1975-1-1',111), 
  47. (1002,'李四','女','助工','1985-1-1',111), 
  48. (1003,'王五','男','工程师','1978-11-11',222),
  49. (1004,'张六',DEFAULT,'工程师','1999-1-1',222);
  52. #添加工资表
  53. INSERT INTO salary 
  54. VALUES 
  55. (1001,2200,1100,200),
  56. (1002,1200,200,NULL),
  57. (1003,2900,700,200),
  58. (1004,1950,700,150); 
  59. /*查询出每个雇员的雇员编号，姓名，职称，所在部门名称， 应发工资（基本工资+职务工资）， 实发工资（基本工资+职务工资-扣除）。 */
  60. SELECT employee.empid,`name`,title,depname, basesalary+titlesalary 
  61. AS "应发工资", basesalary+titlesalary-IFNULL(deduction,0) AS "实发工资" 
  62. FROM department INNER JOIN employee INNER JOIN salary 
  63. ON department.depid = employee.depid 
  64. AND employee.empid = salary.empid; 
  66. #查询销售部门的雇员姓名及其基本工资 
  67. SELECT `name`,basesalary 
  68. FROM department INNER JOIN employee INNER JOIN salary
  69. ON department.depid = employee.depid 
  70. AND employee.empid = salary.empid WHERE department.depname = '销售部';
  72. #查询姓“张”且年龄小于40的员工的全部信息和年龄
  73. SELECT *,YEAR(CURRENT_DATE())-YEAR(birthday) 
  74. AS "年龄" FROM employee WHERE `name` LIKE '张%'
  75. AND YEAR(CURRENT_DATE())-YEAR(birthday)<40; 
  77. #查询所有男员工的基本工资和职务工资 
  78. SELECT basesalary,titlesalary 
  79. FROM employee INNER JOIN salary
  80. ON employee.empid = salary.empid
  81. WHERE employee.sex = '男';
  83. #查询基本工资低于2000的员工姓名和职称、所在部门名称 
  84. SELECT `name`,title,depname 
  85. FROM department INNER JOIN employee INNER JOIN salary 
  86. ON department.depid = employee.depid
  87. AND employee.empid = salary.empid WHERE basesalary < 2000; 
  89. #查询员工总数 
  90. SELECT COUNT(*) FROM employee; 
  91. #查询部门总数 
  92. SELECT COUNT(*) FROM department; 
  93. #查询应发工资的平均工资和最高应发工资、最低应发工资
  94. SELECT AVG(basesalary+titlesalary) AS "平均应发工资", MAX(basesalary+titlesalary) AS "最高应发工资", MIN(basesalary+titlesalary) AS "最低应发工资" FROM salary;
  96. #按照部门统计应发工资的平均工资 
  97. SELECT depid,AVG(basesalary+titlesalary) 
  98. FROM employee INNER JOIN salary 
  99. ON employee.`empid` = salary.`empid`
  100. GROUP BY employee.`depid`; 
  102. #找出部门基本工资的平均工资低于2000的
  104. SELECT depid,AVG(basesalary)
  105. FROM employee INNER JOIN salary 
  106. ON employee.`empid` = salary.`empid` 
  107. GROUP BY employee.`depid`
  108. HAVING AVG(basesalary)<2000;
  110. #按照员工编号、姓名、基本工资、职务工资、扣除，
  111. #并按照职务升序排列，如果职务工资相同，再按照基本工资升序排列
  112. SELECT emp.empid,`name`,basesalary,titlesalary,deduction 
  113. FROM employee emp INNER JOIN salary 
  114. ON emp.`empid` = salary.`empid`
  115. ORDER BY emp.`title` ASC , basesalary ASC; 
  116. #查询员工编号、姓名，出生日期，及年龄段，其中 •
  117. #如果80年之前出生的，定为”老年“；80后定为”中年“，90后定为”青壮年“
  118. SELECT empid,`name`,birthday, 
  119. CASE 
  120. WHEN YEAR(birthday)<1980 THEN '老年'
  121. WHEN YEAR(birthday)<1990 THEN '中年' 
  122. ELSE '青壮年' END "年龄段" FROM employee; 
  124. #查询所有的员工信息，和他所在的部门名称
  125. SELECT emp.*,depname 
  126. FROM employee emp LEFT JOIN department dep 
  127. ON emp.`depid` = dep.`depid`; 
  129. #查询所有部门信息，和该部门的员工信息
  130. SELECT dep.*,emp.* 
  131. FROM employee emp RIGHT JOIN department dep
  132. ON emp.`depid` = dep.`depid`; 
  134. #查询所有职位中含“工程师”的男员工的人数
  135. SELECT COUNT(*) 
  136. FROM employee 
  137. WHERE sex='男' 
  138. AND title LIKE '%工程师%'; 
  140. #查询每个部门的男生和女生的人数和平均基本工资 
  141. SELECT dep.depid,sex,COUNT(*),AVG(basesalary) 
  142. FROM department dep INNER JOIN employee INNER JOIN salary 
  143. ON dep.depid = employee.depid 
  144. AND employee.empid = salary.empid 
  145. GROUP BY dep.depid,sex;

练习4

  1.  1、创建一个数据库：test_school 2、创建如下表格
  2. 表1 Department表的定义

练习篇 - 图15

表2 Teacher表的定义

练习篇 - 图16

3、添加记录

练习篇 - 图17

练习篇 - 图18

  1. 4、用SELECT语句查询Teacher表的所有记录。
  2. 5、找出所有其家庭地址中含有“北京”的教师的教工号及部门名称，要求显示结果中各列标题用中文别名
  3. 表示。
  4. 6、获得Teacher表中工资最高的教工号和姓名。
  5. 7、找出所有收入在2500～4000之间的教工号。
  6. 8、查找在网络技术系工作的教师的姓名、性别和工资。
  1. #创建一个数据库：test_school 
  3. CREATE DATABASE test_school; 
  4. #使用数据库 USE test_school; 
  5. #创建表格
  6. -- 部门信息表Department 
  7. CREATE TABLE Department( 
  8. DepNo INT(10) PRIMARY KEY, 
  9. DepName VARCHAR(20) NOT NULL, 
  10. DepNote VARCHAR(50) );
  11. -- 创建数据表Teacher
  12. CREATE TABLE Teacher( 
  13. Number INT PRIMARY KEY, 
  14. `Name` VARCHAR(30) UNIQUE, 
  15. Sex VARCHAR(4), 
  16. Birth DATE, 
  17. DepNo INT, 
  18. Salary FLOAT, 
  19. Address VARCHAR(100), 
  20. FOREIGN KEY (DepNo) REFERENCES Department(DepNo) );
  23. -- 将表4的内容插入Department表中 
  24. INSERT INTO Department VALUES (601,'软件技术系','软件技术等专业'); 
  25. INSERT INTO Department VALUES (602,'网络技术系','多媒体技术等专业');
  26. INSERT INTO Department VALUES (603,'艺术设计系','广告艺术设计等专业'); 
  27. INSERT INTO Department VALUES (604,'管理工程系','连锁经营管理等专业');
  28. -- 将表3的内容插入Teacher表中。 
  29. INSERT INTO Teacher 
  30. VALUES
  31. (2001,'Tom','女','1970-01-10',602,4500,'四川省绵阳市'); 
  32. INSERT INTO Teacher 
  33. VALUES(2002,'Lucy','男','1983-12-18',601,2500,'北京市昌平区'); 
  34. INSERT INTO Teacher 
  35. VALUES(2003,'Mike','男','1990-06-01',604,1500,'重庆市渝中区');
  36. INSERT INTO Teacher
  37. VALUES(2004,'James','女','1980-10-20',602,3500,'四川省成都市'); 
  38. INSERT INTO Teacher 
  39. VALUES(2005,'Jack','男','1975-05-30',603,1200,'重庆市南岸区'); 
  41. #用SELECT语句查询Teacher表的所有记录。 
  42. SELECT * FROM teacher; 
  43. #找出所有其家庭地址中含有“北京”的教师的教工号及部门名称，要求显示结果中各列标题用中文表示。 SELECT number AS 教工号,Teacher.depno AS 部门名称
  44. FROM Teacher INNER JOIN Department 
  45. ON Teacher.DepNo = Department.DepNo 
  46. WHERE address LIKE '%北京%';
  47. #获得Teacher表中工资最高的教工号和姓名。
  48. SELECT number,`name` FROM teacher 
  49. WHERE salary = (SELECT MAX(salary) FROM teacher); 
  51. SELECT number,`name` FROM teacher ORDER BY salary DESC LIMIT 0,1;
  53. #找出所有收入在2500～4000之间的教工号。
  54. SELECT number FROM teacher WHERE salary BETWEEN 2500 AND 4000; 
  56. #查找在网络技术系工作的教师的姓名、性别和工资。
  57. SELECT `name`,sex,salary FROM teacher WHERE depno=(SELECT depno FROM department WHERE depname='网络技术系');
  59. SELECT `name`,sex,salary FROM teacher INNER JOIN department ON teacher.depno = department.depno WHERE depname ='网络技术系';

练习5

  1. 1、建立数据库test_student 
  2. 2、建立以下三张表，并插入记录
  3. Table:Classes

练习篇 - 图19

Table:Score

练习篇 - 图20

Table: Records

练习篇 - 图21

  1. 3、写出将张三的语文成绩修改为88的SQL语句。
  2. 4、搜索出计算机维护1班各门课程的平均成绩。
  3. 5、搜索科目有不及格的人的名单。
  4. 6、查询记录2次以上的学生的姓名和各科成绩
  1. #1、建立数据库test_student
  3. CREATE DATABASE test_student;
  4. #使用数据库 USE test_student; 
  6. #2、创建表格并添加记录 
  7. CREATE TABLE Classes(
  8. Pro_name VARCHAR(20) NOT NULL,
  9. Grade VARCHAR(10) NOT NULL, 
  10. `name` VARCHAR(10) NOT NULL,
  11. sex VARCHAR(4) NOT NULL, 
  12. seat INT(10) NOT NULL UNIQUE );
  14. CREATE TABLE Score(
  15. `name` VARCHAR(10) NOT NULL,
  16. En_score INT(10) NOT NULL, 
  17. Ma_score INT(10) NOT NULL, 
  18. Ch_score INT(10) NOT NULL );
  20. CREATE TABLE Records( 
  21. `name` VARCHAR(10) NOT NULL,
  22. record VARCHAR(10) );
  25. -- 向classes中添加数据 
  26. INSERT INTO classes VALUES('计算机网络','1班','张三','男',8);
  27. INSERT INTO classes VALUES('软件工程','2班','李四','男',12); 
  28. INSERT INTO classes VALUES('计算机维护','1班','王五','男',9); 
  29. INSERT INTO classes VALUES('计算机网络','2班','LILY','女',15); 
  30. INSERT INTO classes VALUES('软件工程','1班','小强','男',20); 
  31. INSERT INTO classes VALUES('计算机维护','1班','CoCo','女',18);
  33. -- 向score中添加数据 
  34. INSERT INTO Score VALUES('张三',65,75,98)
  35. INSERT INTO Score VALUES('李四',87,45,86); 
  36. INSERT INTO Score VALUES('王五',98,85,65); 
  37. INSERT INTO Score VALUES('LILY',75,86,87); 
  38. INSERT INTO Score VALUES('小强',85,60,58); 
  39. INSERT INTO Score VALUES('CoCo',96,87,70);
  42. -- 向records中添加数据 
  43. INSERT INTO records VALUES('小强','迟到'); 
  44. INSERT INTO records VALUES('小强','事假'); 
  45. INSERT INTO records VALUES('李四','旷课'); 
  46. INSERT INTO records VALUES('李四','旷课'); 
  47. INSERT INTO records VALUES('李四','迟到'); 
  48. INSERT INTO records VALUES('CoCo','病假'); 
  49. INSERT INTO records VALUES('LILY','事假'); 
  51. #3、写出将张三的语文成绩修改为88的SQL语句。 
  52. UPDATE score SET ch_score=88 WHERE `name`='张三'; 
  54. #4、搜索出计算机维护1班各门课程的平均成绩。 
  55. SELECT AVG(en_score),AVG(ma_score),AVG(ch_score) 
  56. FROM score
  57. WHERE `name` IN (
  58.    SELECT `name` 
  59.    FROM classes 
  60.    WHERE Pro_name='计算机维护' AND grade='1 班'); 
  62. #5、搜索科目有不及格的人的名单。
  63.    SELECT `name` 
  64.    FROM score
  65.    WHERE en_score<60 OR ma_score<60 OR ch_score<60; 
  67.    #6、查询记录2次以上的学生的姓名和各科成绩。 
  68.    SELECT * FROM score INNER JOIN (
  69.       SELECT `name`,COUNT(*) 
  70.       FROM Records GROUP BY `name` 
  71.       HAVING COUNT(*)>2) temp 
  72.       ON score.name = temp.name;

练习6

  1. 1、建立数据库：test_xuankedb 
  2. 2、建立如下三张表：
  3. 学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个字段，Sno 为关
  4. 键字。
  5. 课程表Course由课程号(Cno)、课程名(Cname)、选修课号(Cpno)、学分(Ccredit)四个字段，Cno为关键
  6. 字。
  7. 成绩表SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个字段，(SNO, CNO)为关键字。
  8. 3、向Student表增加“入学时间(Scome)”列，其数据类型为日期型。
  9. 4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
  10. 5、查询学习1号课程的学生最高分数、平均成绩。
  11. 6、查询与“李洋”在同一个系学习的学生。
  12. 7、将计算机系全体学生的成绩置零。
  13. 8、删除学生表中学号为05019的学生记录。
  14. 9、删除计算机系所有学生的成绩记录。
  1. -- 1、创建一个数据库：test_xuankedb 
  3. CREATE DATABASE test_xuankedb;
  4. -- 使用数据库 
  5. USE test_xuankedb;
  6. -- 2、创建学生表 
  7. CREATE TABLE student( 
  8. sno INT(10) PRIMARY KEY, 
  9. sname VARCHAR(10), 
  10. ssex VARCHAR(10),
  11. sage INT(10), 
  12. sdept VARCHAR(40) );
  13. -- 创建课程表 
  15. CREATE TABLE course( 
  16. cno INT(10) PRIMARY KEY, 
  17. cname VARCHAR(20), 
  18. cpno VARCHAR(40), 
  19. ccredit INT(20) );
  22. -- 创建成绩表 
  23. CREATE TABLE sg( 
  24. sno INT(10), 
  25. cno INT(10), 
  26. grade INT(3), 
  27. PRIMARY KEY(sno,cno), 
  28. CONSTRAINT stu_s_sno_fk FOREIGN KEY (sno) REFERENCES student(sno), 
  29. CONSTRAINT cou_s_sno_fk FOREIGN KEY (cno) REFERENCES course(cno) );
  31. #3、向Student表增加“入学时间(Scome)”列，其数据类型为日期型。 
  32. ALTER TABLE student ADD COLUMN scome DATE; 
  33. #4、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。
  35. SELECT sno,grade FROM sg WHERE cno=3 
  36. ORDER BY grade DESC; 
  38. #5、查询学习1号课程的学生最高分数、平均成绩。
  39. SELECT MAX(grade),AVG(grade) 
  40. FROM sg WHERE cno=1;
  41. #6、查询与“李洋”在同一个系学习的学生。 
  42. SELECT * FROM student WHERE sdept=(
  43.   SELECT sdept FROM student WHERE sname='李洋'); 
  44. #7、将计算机系全体学生的成绩置零。
  45. UPDATE sg SET grade=0 
  46.   WHERE sno IN (
  47.   SELECT sno FROM student WHERE sdept='计算机系')
  48. #8、删除学生表中学号为05019的学生记录。
  49. DELETE FROM student WHERE sno=05019; 
  50. #9、删除计算机系所有学生的成绩记录。
  51. DELETE FROM sg WHERE sno IN (SELECT sno FROM student WHERE sdept='计算机系');

练习7

  1. 1、建立数据库：test_library 2、建立如下三个表： 表一：press 出版社 属性：编号pressid(int)、名称pressname(varchar)、地址
  2. address(varchar)
  3. 表二：sort 种类 属性：编号sortno(int)、数量scount(int)
  4. 表二：book图书 属性：编号bid(int)、名称 bname(varchar)、种类bsortno(int)、出版社编号pressid(int) 3、给sort表中添加一列属性：描述describes(varchar) 4、向三个表中各插入几条数据

练习篇 - 图22

练习篇 - 图23

  1. 5、查询出版社id为100的书的全部信息
  2. 6、查询出版社为外研社的书的全部信息
  3. 7、查询图书数量（scount）大于100的种类
  4. 8、查询图书种类最多的出版社信息
  1. -- 1、建立数据库：test_library
  2. CREATE DATABASE test_library;
  3. -- 使用数据库 USE test_library;
  4. -- 2、创建出版社表 
  5. CREATE TABLE press( 
  6. pressid INT(10) PRIMARY KEY, 
  7. pressname VARCHAR(30), 
  8. address VARCHAR(50) )
  10. -- 创建一个种类表 
  11. CREATE TABLE sort( 
  12. sortno INT(10) PRIMARY KEY, 
  13. scount INT(10) );
  14. -- 创建图书表 
  15. CREATE TABLE book( 
  16. bid INT(10) PRIMARY KEY,
  17. bname VARCHAR(40), 
  18. bsortno INT(10), 
  19. pressid INT(10),
  20. CONSTRAINT p_b_pid_fk FOREIGN KEY (pressid) REFERENCES press(pressid), CONSTRAINT s_b_sno_fk FOREIGN KEY (bsortno) REFERENCES sort(sortno) );
  23. -- 3、添加一列属性
  24. ALTER TABLE sort ADD COLUMN describes VARCHAR(30);
  25. -- 4、添加数据
  26. INSERT INTO press
  27. VALUES(100,'外研社','上海'); 
  28. INSERT INTO press VALUES(101,'北大出版社','北京'); 
  29. INSERT INTO press VALUES(102,'教育出版社','北京');
  31. -- 添加数据 
  32. INSERT INTO sort(sortno,scount,describes)VALUES(11,50,'小说'); 
  33. INSERT INTO sort(sortno,scount,describes)VALUES(12,300,'科幻'); 
  34. INSERT INTO sort(sortno,scount,describes)VALUES(13,100,'神话');
  37. -- 添加数据 
  38. INSERT INTO book VALUES(1,'红与黑',11,100); 
  39. INSERT INTO book VALUES(2,'幻城',12,102); 
  40. INSERT INTO book VALUES(3,'希腊神话',13,102); 
  41. INSERT INTO book VALUES(4,'一千零一夜',13,102);
  43. #5、查询出版社id为100的书的全部信息 
  44. SELECT * FROM book WHERE pressid=100;
  46. #6、查询出版社为外研社的书的全部信息 
  48. SELECT * FROM book WHERE pressid=(
  49. SELECT pressid FROM press WHERE pressname='外研社'); 
  51. #7、查询图书数量（scount）大于100的种类 
  53. SELECT * FROM sort WHERE scount>100; 
  55. #8、查询图书种类最多的出版社信息 
  57. SELECT * FROM press 
  58. WHERE pressid=( 
  59. SELECT temp.pressid FROM (
  60.      SELECT pressid,MAX(t.c) FROM (SELECT pressid,COUNT(*) AS c FROM book GROUP BY pressid ORDER BY c DESC ) AS t) AS temp);
  62. SELECT * FROM press WHERE pressid=( SELECT pressid FROM (SELECT pressid,bsortno FROM book GROUP BY pressid,bsortno) temp GROUP BY pressid ORDER BY COUNT(*) DESC LIMIT 0,1)

练习8

  1.  1、建立数据库：test_tour 2、建立如下两个表：
  2. agency旅行社表：

练习篇 - 图24

travel旅行线路表：

练习篇 - 图25

3、添加记录

练习篇 - 图26

  1. 4、查出旅行线路最多的旅社
  2. 5、查出最热门的旅行线路(也就是查询出报名人数最多的线路)
  3. 6、查询花费少于5000的旅行线路
  4. 7、找到一次旅行花费最昂贵的旅行社名
  5. 8、查出青年旅社所有的旅行线路都玩一遍需要多少时间。
  1. #1、建立数据库：test_tour 
  3. CREATE DATABASE test_tour; 
  5. #使用数据库 USE test_tour; 
  7. #2、 CREATE TABLE agency( 
  8. id INT PRIMARY KEY NOT NULL, 
  9. NAME VARCHAR(20) NOT NULL,
  10. address VARCHAR(100) NOT NULL,
  11. areaid INT );
  13. CREATE TABLE trval(
  14. tid INT PRIMARY KEY NOT NULL, 
  15. TIME VARCHAR(50) NOT NULL, 
  16. POSITION VARCHAR(100) NOT NULL, 
  17. money FLOAT, 
  18. aid INT NOT NULL, 
  19. rcount INT, 
  20. CONSTRAINT bk_aid FOREIGN KEY trval(aid) REFERENCES agency(id) );
  23. #3、 
  24. INSERT INTO agency(id,NAME,address) VALUES (101,'青年旅行社','北京海淀');
  25. INSERT INTO agency(id,NAME,address) VALUES (102,'天天旅行社','天津海院');
  26. INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (1,'5天','八达 岭',3000,101,10);
  27. INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (2,'7天','水长 城',5000,101,14); 
  28. INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (3,'8天','水长 城',6000,102,11); 
  29. SELECT * FROM agency; SELECT * FROM trval;
  31. #4、查出旅行线路最多的旅社
  33. SELECT * FROM agency INNER JOIN (SELECT t.aid,MAX(t.c) FROM (SELECT aid,COUNT(*) AS c FROM trval GROUP BY aid) AS t)temp ON agency.id = temp.aid 
  35. #5、查出最热门的旅行线路(也就是查询出报名人数最多的线路)
  37. SELECT * FROM trval WHERE rcount=(SELECT MAX(rcount) FROM trval); 
  39. #6、查询花费少于5000的旅行线路
  40. SELECT * FROM trval WHERE money<5000;
  41. #7、找到一次旅行花费最昂贵的旅行社名 
  43. SELECT NAME FROM agency WHERE id = (SELECT aid FROM trval
  44. WHERE money =(SELECT MAX(money) FROM trval )); 
  46. #8、查出青年旅社所有的旅行线路都玩一遍需要多少时间。
  48. SELECT SUM(TIME) FROM trval WHERE aid=(SELECT id FROM agency WHERE NAME='青年旅行社');