2022年2022年老师布置的mysql查询作业及答案 .pdf

案例目的：根据不同条件对表进行查询操作，1：employee 表结构字段名字段说明数据类型主键外键非空唯一自增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 否否是否否2：dept 表结构字段名字段说明数据类型主键外键非空唯一自增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);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：将指定记录分别插入两个表中，执行过程如下：向 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),(1003,WARD,f,30,SALESMAN,1250,2003-05-12),名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 5 页 -(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,ANAL YST,3000,2003-05-12),(1009,KING,f,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|+-+-+-+|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 的所有记录。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|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 之间的员工信息。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|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 max(e_salary)from employee group by dept_no;#查询每个部门的最高工资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|+-+-+-+-+-+-+-+|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_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|+-+-+-+|CLARK|ACCOUNTING|ShangHai|KING|ACCOUNTING|ShangHai|SMITH|RESEARCH|BeiJing|JONES|RESEARCH|BeiJing|SCOTT|RESEARCH|BeiJing|ADAMS|RESEARCH|BeiJing|ALLEN|SALES|ShenZhen|WARD|SALES|ShenZhen|名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 5 页 -|MARTIN|SALES|ShenZhen|BLAKE|SALES|ShenZhen|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 表中，计算不同类型职工的总工资数。mysql select e_job,sum(e_salary)from employee group by e_job;+-+-+|e_job|sum(e_salary)|+-+-+|ANAL YST|3000|CLERK|2850|MANAGER|8275|PRESIDENT|5000|SALESMAN|5600|+-+-+12：在 employee 表中，计算不同部门的平均工工资。mysql 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)13：在 employee 表中，查询工资低于1500 的员工信息。mysql select*from employee where e_salary select*from employee order by dept_no desc,e_salary desc;名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 5 页 -+-+-+-+-+-+-+-+|e_no|e_name|e_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|SMITH|m|20|CLERK|800|2005-11-12|1009|KING|f|10|PRESIDENT|5000|1995-01-01|1007|CLARK|m|10|MANAGER|2450|2002-09-12|+-+-+-+-+-+-+-+12 rows in set(0.00 sec)15：在 employee 表中，查询员工姓名以字母A 或 S 开头的员工的信息。mysql select*from employee where e_name like A%or e_name like S%;+-+-+-+-+-+-+-+|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)名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 5 页 -