老师布置的mysql查询作业及答案

1、 案例目的： 根据不同条件对表进行查询操作， 1：employee表结构 字段名 字段说明 数据类型 主键 外键 非空 唯一 自增 e_no 员工编号 ） 11int（ 是 否 是 是 否 e_name 员工姓名 varchar(50) 否 否 是 否 否 e_gender 员工性别 char(2) 否 否 否 否 否 dept_no 部门编号 int(11) 否 是 是 否 否 e_job 职位 varchar(50) 否 否 是 否 否 e_salary 薪水 int(11) 否 否 是 否 否 hireDate 入职日期 date 否 否 是 否 否 2：dept表结构 字段名 字段说明

2、 数据类型 主键 外键 非空 唯一 自增 dept_no 部门编号 ）int（11 是 否 是 是 是 d_name 部门名称 varchar(50) 否 否 是 否 否 d_location 部门地址 varchar(100) 否 否 否 否 否 1：创建数据表employee和dept create database chaxun; use chaxun; create table dept( dept_no int(11) primary key auto_increment, d_name varchar(50) not null, d_location varchar(100) );

3、 create table employee( e_no int(11) primary key, e_name varchar(50) not null, e_gender char(2) , dept_no int(11) not null, e_job varchar(50) not null, e_salary int(11) not null, hireDate date not null ); Alter table employee add constraint emp_dept foreign key (dept_no) references dept(dept_no); 2：

4、将指定记录分别插入两个表中，执行过程如下： 向dept表中插入数据 INSERT INTO dept VALUES (10, ACCOUNTING, ShangHai), (20, RESEARCH , BeiJing ), (30, SALES , ShenZhen ), (40, OPERATIONS , FuJian ); 向employee表中插入数据 INSERT INTO employee VALUES (1001, SMITH, m,20, CLERK,800,2005-11-12), (1002, ALLEN, f,30, SALESMAN, 1600,2003-05-12),

5、 (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,2003-05-12), (1009, KING, f,

6、10, PRESIDENT, 5000,1995-01-01), (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); 3：在employee表中，查询所有记录的e_no、e_name和e_salary字段值 mysql select e_no,e_name,e_salary from employee; +-+-+-+ | e_no | e_name | e_salary

7、| +-+-+-+ | 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 | +-+-

8、+-+ 12 rows in set (0.00 sec) 4：在employee表中，查询dept_no等于10和20的所有记录。 mysql select * from employee where dept_no between 10 and 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 |

9、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 se

10、c) 5：在employee表中，查询工资范围在800到2500之间的员工信息。 mysql select * from employee where e_salary800 and e_salary 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 | | 10

11、04 | 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 max(e_salary) from employee group by dept_no; # 查询每个部门的最高工资

12、 Select * from employee where e_salary in (select max(e_salary) from employee group by dept_no); mysql Select * from employee where e_salary in (select max(e_salary) from emp loyee group by dept_no); +-+-+-+-+-+-+-+ | e_no | e_name | e_gender | dept_no | e_job | e_salary | hireDate | +-+-+-+-+-+-+-+

13、 | 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 | | 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 | | 1009 | KING | f | 10 | PRESIDENT | 5000 | 1995-01-01 | +-+-+-+-+-+-+-+ 8：查询员工BLAKE所在部门和部门所在地。 mysql select e_name,d_name,d_location from employee as e ,dept as d where e.dept_no=d.dept_

14、no and e_name=BLAKE; +-+-+-+ | e_name | d_name | d_location | +-+-+-+ | BLAKE | SALES | ShenZhen | +-+-+-+ 1 row in set (0.00 sec) 9：使用连接查询，查询所有员工的部门和部门信息。 mysql select e_name,d_name,d_location from employee as e ,dept as d where e.dept_no=d.dept_no; +-+-+-+ | e_name | d_name | d_location | +-+-+-+

15、| CLARK | ACCOUNTING | ShangHai | | KING | ACCOUNTING | ShangHai | | SMITH | RESEARCH | BeiJing | | JONES | RESEARCH | BeiJing | | SCOTT | RESEARCH | BeiJing | | ADAMS | RESEARCH | BeiJing | | ALLEN | SALES | ShenZhen | | | ShenZhen | SALES | WARD | MARTIN | SALES | ShenZhen | | BLAKE | SALES | Shen

16、Zhen | | TURNER | SALES | ShenZhen | | JAMES | SALES | ShenZhen | +-+-+-+ 10：在employee表中，计算每个部门各有多少名员工。 mysql select dept_no, count(e_no) from employee group by dept_no; +-+-+ | dept_no | count(e_no) | +-+-+ | 10 | 2 | | 20 | 4 | | 30 | 6 | +-+-+ 3 rows in set (0.00 sec) 11：在employee表中，计算不同类型职工的总工资数

17、。 mysql 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 | +-+-+ 12：在employee表中，计算不同部门的平均工工资。 mysql select dept_no,avg(e_salary) from employee group by dept_no;

18、 +-+-+ | dept_no | avg(e_salary) | +-+-+ | 10 | 3725.0000 | | 20 | 1968.7500 | | 30 | 1566.6667 | +-+-+ 3 rows in set (0.00 sec) 13：在employee表中，查询工资低于1500的员工信息。 mysql select * from employee where e_salary select * from employee order by dept_no desc,e_salary desc; +-+-+-+-+-+-+-+ | e_no | e_name | e

19、_gender | dept_no | e_job | e_salary | hireDate | +-+-+-+-+-+-+-+ | 1006 | BLAKE | f | 30 | MANAGER | 2850 | 1997-02-15 | | 1002 | ALLEN | f | 30 | SALESMAN | 1600 | 2003-05-12 | | 1010 | TURNER | f | 30 | SALESMAN | 1500 | 1997-10-12 | | 1003 | WARD | f | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | m | 30 | SALESMAN | 1250 | 2001-06-12 | | 1012 | JAMES | m | 30 | CLERK | 950 | 2008-06-15 | | 1008 | SCOTT | m | 20 | ANALYST | 3000 | 2003-05-12 | | 1004 | JONES | m | 20 | MANAGER | 2975 | 1998-05-18 | | 1011 | ADAMS | m | 20 | CLERK | 1100 | 1999-10-05 | | 1001 | SM