mysql上机实验三

实验三 4学时

查询数据、实验报告（一、二、三）

一、学习目标

1. 了解基本查询语句
2. 掌握表单查询的方法
3. 掌握如何使用几何函数的查询
4. 掌握连接查询的方法
5. 掌握如何使用子查询
6. 熟悉合并查询结果
7. 熟悉如何为表和字段取别名
8. 掌握如何使用正则表达式查询
9. 掌握数据表的查询操作技巧和方法

二、实验内容

根据不同条件对表进行查询操作，掌握数据表的查询语句。Employee、dept表结构以及表中的记录如下表所示

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	否	否	是	否	否

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

步骤如下：

①创建数据表employee和dept

create database company;
use company;
 
create table dept(
d_no int(11) primary key not null unique ,
d_name varchar(50) not null ,
d_location varchar(100));

desc dept;

create table employee(
e_no int(11) primary key not null unique ,
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 ,
constraint fk foreign key(dept_no) references dept(d_no));

②将指定记录分别插入两个表中

insert into dept(d_no,d_name,d_location) values(10,'ACCOUNTING','ShangHai');
insert into dept(d_no,d_name,d_location) values(20,'RESEARCH','BeiJing');
insert into dept(d_no,d_name,d_location) values(30,'SALES','ShenZhen');
insert into dept(d_no,d_name,d_location) values(40,'OPERATIONS','FuJian');

insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1001,'SMITH','m',20,'CLERK',800,'2005-11-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1003,'WAED','f',30,'SALESMAN',1250,'2003-05-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05');
insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)
values(1012,'JAMES','f',30,'CLERK',950,'2008-06-15');

③在employee表中，查询所有记录的e_no、e_name和e_salary字段值

select e_no,e_name,e_salary
from employee;

④在employee表中，查询dept_no等于10和20的所有记录

select *
from employee
where dept_no=10 or dept_no=20;

⑤在employee表中，查询工资范围在800~2500之间的员工信息

select *
from employee
where e_salary>=800 and e_salary<=2500;

⑥在employee表中，查询部门编号为20的部门中的员工信息

select *
from employee
where dept_no=20;

⑦在employee表中，查询每个部门最高工资的员工信息

select *
from employee
where (dept_no,e_salary)in
(select dept_no,MAX(e_salary)
from employee
group by dept_no);

⑧查询员工BLAKE所在部门和部门所在地

select d_name,d_location
from dept
where d_no=
(select dept_no
from employee
where e_name='BLAKE');

⑨使用连接查询，查询所有员工的部门和部门信息

select e_name,d_no,d_name,d_location
from employee,dept
where dept_no=d_no;

⑩在employee表中，计算每个部门各有多少名员工

select dept_no,count(*)
from employee
group by dept_no;

⑪在employee表中，计算不同类型职工的总工资数

select e_job,SUM(e_salary)
from employee
group by e_job;

⑫在employee表中，计算不同部门的平均工资

select dept_no,AVG(e_salary)
from employee
group by dept_no;

⑬在employee表中，查询工资低于1500的员工信息

select *
from employee
where e_salary<1500;

⑭在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资从高到低排列

select *
from employee
order by dept_no desc,e_salary desc;

⑮在employee表中，查询员工姓名以字母‘A’或‘S’开头的员工的信息

select *
from employee
where e_name like 'A%' or e_name like 'S%';

⑯在employee表中，查询到目前为止工龄大于等于18年的员工信息

select *
from employee
where year(curdate())-year(hireDate)>=18;

三、思考题（02，04）

1. DISTINCT可以应用于所有的列吗?
2. ORDER BY可以和LIMIT混合使用吗？
3. 什么时候可以使用引号？
4. 在WHERE子句中必须使用圆括号吗?