实验三 4学时
查询数据、实验报告(一、二、三)
一、学习目标
- 了解基本查询语句
- 掌握表单查询的方法
- 掌握如何使用几何函数的查询
- 掌握连接查询的方法
- 掌握如何使用子查询
- 熟悉合并查询结果
- 熟悉如何为表和字段取别名
- 掌握如何使用正则表达式查询
- 掌握数据表的查询操作技巧和方法
二、实验内容
根据不同条件对表进行查询操作,掌握数据表的查询语句。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)
- DISTINCT可以应用于所有的列吗?
- ORDER BY可以和LIMIT混合使用吗?
- 什么时候可以使用引号?
- 在WHERE子句中必须使用圆括号吗?