综合案例3——数据表查询操作

SQL语句可以分为两部分，一部分用来创建数据库对象，另一部分用来操作这些对象，本部分内容详细介绍了操作数据库对象的数据表查询语句。通过部分内容的介绍，同学们可以了解到SQL中的查询语言功能的强大，用户可以根据需要灵活使用。此综合案例将回顾这些查询语句。

1、案例目的

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

表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表结构

字段名	数据类型	主键	外键	非空	唯一	自增	字段说明
d_no	int（11）	是	否	是	是	是	部门编号
d_name	varchar（50）	否	否	是	否	否	部门姓名
d_location	varchar（100）	否	否	否	否	否	部门地址

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

表4 dept表中的记录

d_no	d_name	d_location
10	accounting	ShangHai
20	research	BeiJing
30	sales	ShenZhen
40	operations	FuJian

2、案例操作过程

（1）登录MySQL数据库。

mysql -h localhost -u root -p

截图：

（2）创建数据库MANGER并选择使用此数据库。

create database MANGER;
use MANGER;

截图：

（3）按照表1、表2创建表employee和dept，创建成功后用desc查看表结构。

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

create table employee(
	e_no int(11) not null unique,
    e_name varchar(50) not null,
    e_gender char(2) null,
    dept_no int(11) not null,
    e_job varchar(50) not null,
    e_salary int(11) not null,
    hiredate date not null,
    primary key(e_no),
    foreign key (dept_no)references dept(d_no)
);
desc employee;

截图：

（4）将表3、表4的记录分别插入两个表中。

insert into dept (d_no,d_name,d_location)
values(10,'accounting','ShangHai'),
(20,'research','BeiJing'),
(30,'sales','ShenZhen'),
(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'),
(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');

截图：

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

select e_no,e_name,e_salary from employee;

截图：

（6）在employee表中，查询dept_no等于10和20的所有记录。

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

截图：

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

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

截图：

（8）在employee表中，查询部门编号为20的部门中的员工所有信息。

select * from employee where dept_no=20;

截图：

（9）在employee表中，查询每个部门最高工资的员工信息。

select *,max(e_salary) from employee group by dept_no;

截图：

（10）查询员工Blake所在部门编号和部门所在地。

select d_name,d_location from dept as de where de.d_no=(select dept_no from employee where e_name='Blake');

截图：

（11）使用连接查询，查询所有员工的部门和和部门信息。

select * from employee as em,dept as de where de.d_no=em.dept_no;

截图：

（12）在employee表中，计算每个部门各有多少名员工。

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

截图：

（13）在employee表中，计算不同类型职工的总工资数。

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

截图：

（14）在employee表中，计算不同部门的平均工资。

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

截图：

（15）在employee表中，查询工资低于1500的员工信息。

select * from employee where e_salary<1500;

截图：

（16）在employee表中，将查询记录先按部门编号由高到低排列，若部门相同再按员工工资由高到低排列。

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

截图：

（17）在employee表中，查询员工姓名以字母A或S开头的员工信息。

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

截图：

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

select * from employee as em where (select year(now())-(select year(em.hiredate)))>20;

截图：