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

综合案例3-查询操作

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

1、案例目的

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

表1 employee表结构

表2 dept表结构

表3 employee表中的记录

表4 dept表中的记录

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;

截图：

相关文章

核心教程

• 05-查询数据