课题名称 综合案例 数据的查询一 授课班级 移动通信 课时 2 学时 授课地点 实训室 知识目标能力目标素质目标 1. 掌握查询所有数据的方 1. 能够熟练地查询表中的 1. 培养学生的吃苦耐劳 法 ; 所有数据 ; 克服困难的精神 ; 2. 掌握按条件查询的方法 ; 2. 能够灵活使用各种关系 2. 培养学生的团队分工与 3. 掌握对查询结果排序的方法 ; 运算符 AND 和 OR 对表中的数据进行条件查询 ; 协作能力 ; 3. 培养学生敬业乐业的工 教学目标 4. 掌握使用聚合函数对数据进行查询的方法 ; 3.. 能够熟练运用聚合函数对数据进行分析和报告 ; 作作风 4. 培养学生能够有条理地 5. 掌握分组查询的方法 ; 4. 能够熟练运用 SELECT 表达自己的思想和观点, 6. 掌握使用 LIMIT 限制查询结果数量的方法 ; 7. 掌握使用正则表达式进行查询的方法 ; 语句进行分组查询 ; 5. 能够熟练运用正则表达式对数据进行查询 ; 6. 能够熟练使用连接查询 提出问题 分析问题和解决问题, 具有观察能力 独立思考 自主创新和自我展示的能力 8. 掌握多表之间数据查询的方法 和子查询对多表之间的数据进行查询 重点 : 1. 按条件进行查询数据 ; 2. 使用聚合函数对数据进行查询的方法 ; 3. 使用 group by 进行分组查询 ; 重点难点 4. 使用正则表达式进行查询 ; 5. 使用连接查询和子查询对多表之间的数据进行查询 难点 : 1. 使用聚合函数对数据进行查询的方法 ; 2. 使用 group by 进行分组查询 ; 3. 使用正则表达式进行查询 ; 4. 使用连接查询和子查询对多表之间的数据进行查询 教学方法 任务驱动法 案例教学法 讲授法
参考资料 1. MySQL 数据库入门, 刘增杰 李坤编著, 清华大学出版社 2. MySQL 数据库任务驱动式教程, 石坤泉 汤红霞编著, 人民邮电出版社 教学过程 主要教学内容 导入新课 5 分钟 本单元我们主要学习了如何在一个表中或多个表之间进行数据的查询操 作, 现在我们就通过一个综合案例来对我们之前学过的知识进行回顾和深化 1. 案例目的 根据不同条件对表进行查询操作, 掌握数据表的查询语句 所需表 employee dept 表结构及表中的记录, 如下表所示 employee 表结构 讲授新课 30 分钟 字段名 字段说明 数据类型 主键外键非空唯一自增 e_no 员工编号 INT(11) 是 否 是 是 否 e_name 员工姓名 VARCHAR(50) 否 否 是 否 否 e_gender 员工性别 CHAR(2) 否 否 否 否 否 dept_no 部门编号 INT(11) 否 否 是 否 否 e_job 职位 VARCHAR(50) 否 否 是 否 否 e_salary 薪水 INT(11) 否 否 否 否 否 hiredate 入职日期 DATE 否 否 是 否 否 dept 表结构 字段名 字段说明 数据类型 主键外键非空唯一自增 d_no 部门编号 INT(11) 是 是 是 是 是 d_name 部门姓名 VARCHAR(50) 否 否 是 否 否 d_location 部门地址 VARCHAR(100) 否 否 否 否 否 employee 表中的记录 e_no e_name e_gender dept_no e_job e_salary hiredate 1001 SMATH m 20 CLERK 800 2005-11-2 1002 ALLEN f 30 SALESMAN 1600 2003-05-12 1003 WARD f 30 SALESMAN 1250 2003-05-12 1004 JONES m 20 MANAGER 2975 1998-05-12 1005 MARTIN m 30 SALESMAN 1250 2001-06-12
1006 BLACK f 30 MANAGER 2850 1997-02-15 1007 CLARK m 10 MANAGER 2450 2002-09-12 1008 SCOOT m 20 ANALYST 3000 2008-05-12 1009 KING f 10 PRESIDENT 5000 1995-01-01 1010 TURNER f 10 SALESMAN 1500 2007-10-12 1011 ADAMS m 20 CLERK 1100 2009-10-05 1012 JAMES f 30 CLERK 950 2008-06-15 dept 表中的记录 d_no d_name d_location 10 ACCOUNTING ShangHai 20 RESEARCH BeiJing 30 SALES ShenZhen 40 OPERATIONS FuJian 2. 操作过程 (1) 创建数据库 emp_info, 并在其中创建数据表 employee 和 dept 创建数据库 emp_info CREATE DATABASE employee_info; USE employee_info; 创建数据表 employee 和 dept CREATE TABLE dept( d_no d_name d_location INT NOT NULL PRIMARY KEY AUTO_INCREMENT, VARCHAR(50), VARCHAR(100) ); 由于 employee 表 dept_no 依赖于父表 dept 的主键 d_no, 因此需要先创建 dept 表, 然后创建 employee 表 CREATE TABLE employee( e_no e_name INT NOT NULL PRIMARY KEY, VARCHAR(100) NOT NULL,
e_gender dept_no e_job e_salary hiredate CHAR(2) NOT NULL, INT(11) NOT NULL, VARCHAR(100) NOT NULL, SMALLINT NOT NULL, DATE, CONSTRAINT dno_fk FOREIGN KEY(dept_no) REFERENCES dept(d_no) ); (2) 将指定记录分别插入两个表中 向 dept 表中插入数据,SQL 语句如下 : INSERT INTO dept VALUES (10, 'ACCOUNTING', 'ShangHai'), (20, 'RESEARCH ', 'BeiJing '),(30, 'SALES ', 'ShenZhen '), (40, 'OPERATIONS ', 'FuJian '); 向 employee 表中插入数据,SQL 语句如下 : INSERT INTO employee VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'), (1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'), (1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'), (1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'), (1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'), (1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'), (1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'), (1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2008-05-12'), (1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'), (1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'2007-10-12'), (1011, 'ADAMS', 'm',20, 'CLERK', 1100,'2009-10-05'), (1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15'); (3) 在 employee 表中, 查询所有记录的 e_no e_name 和 e_salary 字段值
SELECT e_no, e_name, e_salary FROM employee; +------+--------------+-------------+ e_no e_name e_salary +------+--------------+--------------+ 1001 SMITH 800 1002 ALLEN 1600 1003 WARD 1250 1004 JONES 2975 1005 MARTIN 1250 1006 BLAKE 2850 1007 CLARK 2450 1008 SCOTT 3000 1009 KING 5000 1010 TURNER 1500 1011 ADAMS 1100 1012 JAMES 950 +------+------------+-----------------+ 12 rows in set (0.00 sec) (4) 在 employee 表中, 查询 dept_no 等于 10 和 20 的所有记录 SELECT * FROM employee WHERE dept_no IN (10, 20); +------+---------------+------------+------------+----------------------+-------------+-----------+ e_no e_name e_gender dept_no e_job e_salary hiredate +------+---------------+------------+------------+----------------------+-------------+-----------+ 1001 SMITH m 20 CLERK 800 2005-11-12 1004 JONES m 20 MANAGER 2975 1998-05-18 1007 CLARK m 10 MANAGER 2450 2002-09-12 1008 SCOTT m 20 ANALYST 3000 2003-05-12 1009 KING f 10 PRESIDENT 5000 1995-01-01 1011 ADAMS m 20 CLERK 1100 1999-10-05 +------+---------------+------------+------------+----------------------+-------------+-----------+ 6 rows in set (0.00 sec) (5) 在 employee 表中, 查询工资范围在 800~2500 之间的员工信息 SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500; +------+-------------+----------------+------------+---------------+------------------+-----------+ e_no e_name e_gender dept_no e_job e_salary hiredate +------+--------------+-----------------+---------+-----------------+-------------------+-----------+
1001 SMITH m 20 CLERK 800 2005-11-12 1002 ALLEN f 30 SALESMAN 1600 2003-05-12 1003 WARD f 30 SALESMAN 1250 2003-05-12 1005 MARTIN m 30 SALESMAN 1250 2001-06-12 1007 CLARK m 10 MANAGER 2450 2002-09-12 1010 TURNER f 30 SALESMAN 1500 1997-10-12 1011 ADAMS m 20 CLERK 1100 1999-10-05 1012 JAMES m 30 CLERK 950 2008-06-15 +------+---------------+---------------+--------------+---------------+--------------+------------+ 8 rows in set (0.00 sec) (6) 在 employee 表中, 查询部门编号为 20 的部门中的员工信息 SELECT * FROM employee WHERE dept_no = 20; +------+-------------+-------------+----------------+-------------+----------------+------------+ e_no e_name e_gender dept_no e_job e_salary hiredate +------+-------------+------------------+------------+--------------+----------------+------------+ 1001 SMITH m 20 CLERK 800 2005-11-12 1004 JONES m 20 MANAGER 2975 1998-05-18 1008 SCOTT m 20 ANALYST 3000 2003-05-12 1011 ADAMS m 20 CLERK 1100 1999-10-05 +------+--------------+-----------------+-------------+--------------+---------------+------------+ 4 rows in set (0.00 sec) (7) 在 employee 表中, 查询每个部门最高工资的员工信息 SELECT dept_no, MAX(e_salary) FROM employee GROUP BY dept_no; +---------+-------------------+ dept_no MAX(e_salary) +---------+-------------------+ 10 5000 20 3000 30 2850 +---------+-----------------+ 3 rows in set (0.00 sec) (8) 在 employee 表中, 计算每个部门各有多少名员工 SELECT dept_no, COUNT(*) FROM employee GROUP BY dept_no;
+------------+----------------+ dept_no COUNT(*) +------------+----------------+ 10 2 20 4 30 6 +------------+----------------+ 3 rows in set (0.00 sec) (9) 在 employee 表中, 计算不同类型职工的总工资数 SELECT e_job, SUM(e_salary) FROM employee GROUP BY e_job; +---------------+-------------------------+ e_job SUM(e_salary) +-----------------+----------------------+ ANALYST 3000 CLERK 2850 MANAGER 8275 PRESIDENT 5000 SALESMAN 5600 +-------------------+-----------------------+ 5 rows in set (0.00 sec) (10) 在 employee 表中, 计算不同部门的平均工资 SELECT dept_no, AVG(e_salary) FROM employee GROUP BY dept_no; +---------+-----------------+ dept_no AVG(e_salary) +---------+----------------+ 10 3725.0000 20 1968.7500 30 1566.6667 +---------+---------------+ 3 rows in set (0.00 sec) (11) 在 employee 表中, 查询工资低于 1500 的员工信息 SELECT * FROM employee WHERE e_salary < 1500;
+------+-----------+----------------+------------+-------------------+---------------+------------+ e_no e_name e_gender dept_no e_job e_salary hiredate +------+-----------+----------------+------------+-------------------+---------------+------------+ 1001 SMITH m 20 CLERK 800 2005-11-12 1003 WARD f 30 SALESMAN 1250 2003-05-12 1005 MARTIN m 30 SALESMAN 1250 2001-06-12 1011 ADAMS m 20 CLERK 1100 1999-10-05 1012 JAMES m 30 CLERK 950 2008-06-15 +------+-----------+----------------+------------+-------------------+---------------+------------+ 5 rows in set (0.00 sec) (12) 在 employee 表中, 将查询记录先按部门编号由高到低排列, 再按员 工工资由高到低排列 SELECT e_name,dept_no, e_salary FROM employee ORDER BY dept_no DESC, e_salary DESC; +------------+--------------+-----------+ e_name dept_no e_salary +-------------+-------------+------------+ BLAKE 30 2850 ALLEN 30 1600 TURNER 30 1500 WARD 30 1250 MARTIN 30 1250 JAMES 30 950 SCOTT 20 3000 JONES 20 2975 ADAMS 20 1100 SMITH 20 800 KING 10 5000 CLARK 10 2450 +------------+--------------+--------------+ 12 rows in set (0.00 sec) (13) 在 employee 表中, 查询员工姓名以字母 A 或 S 开头的员工的 信息 SELECT * FROM employee WHERE e_name REGEXP '^[as]'; +------+------------+----------------+--------------+---------------+------------+-------------+ e_no e_name e_gender dept_no e_job e_salary hiredate +------+-------------+----------------+-------------+----------------+-------------+------------+ 1001 SMITH m 20 CLERK 800 2005-11-12 1002 ALLEN f 30 SALESMAN 1600 2003-05-12
1008 SCOTT m 20 ANALYST 3000 2003-05-12 1011 ADAMS m 20 CLERK 1100 1999-10-05 +------+--------------+-------------------+----------+------------------+-----------+--------------+ 4 rows in set (0.00 sec) (14) 在 employee 表中, 查询到目前为止, 工龄大于等于 15 年的员工信息 SELECT * FROM employee where YEAR(CURDATE()) -YEAR(hireDate) >= 15; (15) 使用连接查询, 查询所有员工的部门和部门信息 SELECT e_no, e_name, dept_no, d_name,d_location FROM employee INNER JOIN dept ON dept.d_no=employee.dept_no; +------+----------+------------------+------------------+-------------+ e_no e_name dept_no d_name d_location +------+------------+----------------+--------------------+------------+ 1001 SMITH 20 RESEARCH BeiJing 1002 ALLEN 30 SALES ShenZhen 1003 WARD 30 SALES ShenZhen 1004 JONES 20 RESEARCH BeiJing 1005 MARTIN 30 SALES ShenZhen 1006 BLAKE 30 SALES ShenZhen 1007 CLARK 10 ACCOUNTING ShangHai 1008 SCOTT 20 RESEARCH BeiJing 1009 KING 10 ACCOUNTING ShangHai 1010 TURNER 30 SALES ShenZhen 1011 ADAMS 20 RESEARCH BeiJing 1012 JAMES 30 SALES ShenZhen +------+---------------+------------+-------------------+-----------------+ 12 rows in set (0.00 sec) (16) 查询所有 2001~2005 年入职的员工信息, 查询部门编号为 20 和 30 的员 工信息并使用 UNION 合并两个查询结果, 然后按照 hiredate 字段排序 SELECT * FROM employee WHERE YEAR(hireDate)>=2001 AND YEAR(hireDate)<=2005 UNION SELECT * FROM employee WHERE dept_no in(20,30)
ORDER BY hiredate; (17) 查询员工 BLAKE 所在部门和部门所在地 SELECT d_no, d_location FROM dept WHERE d_no= (SELECT dept_no FROM employee WHERE e_name='blake'); +----------+------------+ d_no d_location +----------+------------+ 30 ShenZhen +----------+------------+ 1 row in set (0.00 sec) 学生练习 50 分钟 自己完成综合案例的所有操作 总结评价 5 分钟 教师总结本堂课所学内容, 并对学生的训练结果进行评价 作业布置 教学反思