实验报告

课程名称：数据库原理及应用
实验项目名称：数据查询与更新
实验时间：2021年4月12日

实验目的:

　　(1) 观察查询结果, 体会SELECT语句实际应用；
　　(2) 要求能够在查询分析器中使用SELECT语句进行简单查询。
　　(3) 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
　　(4) 熟悉使用UPDATE/INSERT/DELETE/ALTER语句进行表操作；

实验环境:

　　MySQL 5.7.33、SQLyog-11.2.4

实验内容及过程:

在实验2的基础上，完成以下题目

1. 在查询分析器中用SQL语句完成以下题目

单表查询

找出所有供应商的姓名和所在城市。
```
SELECT SNAME,CITY FROM S
```
找出所有零件的名称、颜色、重量。
```
SELECT PNAME,COLOR,WEIGHT FROM P
```
找出使用供应商S1所供应零件的工程号码。
```
SELECT JNO FROM SPJ WHERE SNO='S1'
```
列出所有地址在北京的供应商姓名。
```
SELECT SNAME FROM S WHERE CITY='北京'
```
模糊查询，找出名字包含"螺丝"两个字且颜色为红色的零件名称。
```
SELECT PNAME FROM P
WHERE PNAME LIKE '螺丝_' AND COLOR='红'
```
统计不同城市工程项目的数量，结果显示"城市"、"工程项目数合计"。
```
SELECT CITY,COUNT(JNO) AS '工程项目书合计'
FROM J GROUP BY CITY
```

连接查询和嵌套查询

找出工程项目J2使用的各种零件的名称及其数量。
```
SELECT PNAME,QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND JNO='J2'
```

找出上海厂商供应的所有零件号码(要求用自然连接和嵌套查询两种方法)。

#自然连接
SELECT P.PNO
FROM S,P,SPJ
WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND S.CITY='上海'
GROUP BY PNO
#嵌套查询
SELECT PNO
FROM P
WHERE PNO IN(
	SELECT PNO
	FROM SPJ
	WHERE SNO IN(
		SELECT SNO
		FROM S
		WHERE CITY='上海'
	)
)

找出所有由上海供应商提供零件的工程名称(要求用自然连接和嵌套查询两种方法)。

#自然连接
SELECT JNAME
FROM S,J,SPJ
WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY='上海'
GROUP BY JNAME
#嵌套查询
SELECT JNAME
FROM J
WHERE JNO IN(
	SELECT JNO
	FROM SPJ
	WHERE SNO IN(
		SELECT SNO
		FROM S
		WHERE CITY='上海'
	)
)

统计不同地区供应商所供应的零件总数量，结果显示供应商地区、零件总量。

SELECT CITY AS '供应商地区',SUM(QTY) AS '零件总量'
FROM S,SPJ
WHERE S.SNO=SPJ.SNO
GROUP BY CITY

分类统计供应商"盛锡"，不同零件的供应量，结果显示零件名称、零件数量。

SELECT PNAME AS '零件名称', SUM(QTY) AS '零件数量'
FROM S,P,SPJ
WHERE P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO AND S.SNAME='盛锡'
GROUP BY PNAME

找出没有使用天津产的零件的工程号码。

SELECT JNO
FROM J
WHERE JNO NOT IN(
	SELECT JNO
	FROM SPJ
	WHERE SNO IN(
		SELECT SNO
		FROM S
		WHERE CITY='天津'
	)
)

查询至少用了供应商S1提供的全部零件的工程号JNO。

SELECT JNO 
FROM J
WHERE JNO IN(
	SELECT JNO
	FROM SPJ
	WHERE SNO='S1'
)

将S、SPJ表进行左外连接，查询结果中带有null值的表示什么含义？
将SPJ、P表进行右外连接，查询结果中带有null值的表示什么含义？

数据更新

把全部红色零件的颜色改成蓝色。
```
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'
```

由S5供给J4的零件P6改为由S3供应。

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6'

从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
```
DELETE FROM SPJ WHERE SNO='S2'
```

请将(S1，J6，P4，200)插入供应情况关系。

INSERT INTO SPJ(SNO,JNO,PNO,QTY)
VALUES('S1','J6','P4','200')

为S表添加供应商，供应商编号：S6,供应商名称：伟星，城市：北京，状态暂未定。
```
INSERT INTO S(SNO,SNAME,CITY,STATUS)
VALUES('S6','伟星','北京',NULL)
```
为P表添加零件，零件编号：P7,零件名称：凹轮，颜色：蓝，重量：20。
```
INSERT INTO P
VALUES('P7','凹轮','蓝',20)
```
在P表中加入属性零件产地CITY(CHAR型)。
```
ALTER TABLE P ADD CITY CHAR
```
将P表中加入属性WEIGHT改为SMALLINT型。
```
ALTER TABLE P MODIFY WEIGHT SMALLINT
```
删除刚才在P表中加入的零件产地CITY属性。
```
ALTER TABLE P DROP COLUMN CITY
```

2. 选做题

导入myemployees库，右键root@localhost，点击执行SQL脚本，选择myemployees.sql路径，点击执行，myemployees库导入完成。

myemployees库中四张表介绍

employees 员工表

字段名	数据类型	约束	含义
employee_id	int	primary key	员工编号
first_name	varchar(20)		名
last_name	varchar(25)		姓
email	varchar(25)		邮箱
phone_number	varchar(20)		电话号码
job_id	varchar(10)		工种编号
salary	double(10,2)		月薪
commission_pct	double(4,2)		奖金率
manager_id	int	foreign key	上级领导的员工编号
department_id	int	foreign key	部门编号
hiredate	datetime		入职日期

departments 部门表

字段名	数据类型	约束	含义
department_id	int	primary key	部门编号
department_name	varchar(3)		部门名称
manager_id	int		部门领导的员工编号
location_id	int	foreign key	位置编号

jobs 工种表

字段名	数据类型	约束	含义
job_id	varchar(10)	primary key	工种编号
job_title	varchar(35)		工种名称
min_salary	int		最低工资
max_salary	int		最高工资

locations 位置表

字段名	数据类型	约束	含义
location_id	int	primary key	位置编号
street_address	varchar(40)		街道
postal_code	varchar(12)		邮编
city	varchar(30)		城市
state_province	varchar(25)		州/省
country_id	varchar(2)		国家编号

练习题

(1)查询员工表中涉及到的所有的部门编号

(2)查询工资>12000的员工信息

(3)查询部门编号不是在90到110之间，或者工资高于15000的员工信息

(4)查询员工名中第三个字符为n，第五个字符为l的员工名和工资（员工名为last_name）

(5)查询员工名中第二个字符为_的员工名

(6)查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

(7)查询有奖金的员工名和奖金率

(8)选择工资不在8000到17000的员工的姓名和工资，按工资降序

(9)查询邮箱中包含e的员工信息，并先按邮箱的字节数降序(LENGTH(str)求字符长度)，再按部门号升序

(10)查询部门编号为90的员工个数，工资的最大值，最小值，平均值

(11)查询每个工种的员工平均工资

(12)查询有奖金的每个领导手下员工的平均工资

(13)查询哪个部门的员工个数 >5

(14)每个工种有奖金的员工的最高工资 >12000的工种编号和最高工资

(15)每个工种有奖金的员工的最高工资 >6000的工种编号和最高工资,按最高工资升序

(16)查询员工名、部门名（需实现两种连接方式：普通连接、内连接）

(17)查询部门个数 >3的城市名和部门个数，（添加分组+筛选）

(18)查询哪个部门的员工个数 >3的部门名和员工个数，并按个数降序（添加排序）

(19)查询员工的名字、上级的名字

(20)查询哪个部门没有员工(左外)

(21)有奖金的员工信息，并且工资较高的前10名显示出来

(22）返回公司工资最少的员工的last_name,job_id和salary

(23)返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

(24）返回其它工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id以及salary

(25)返回其它部门中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id 以及salary

(26)查询有员工的部门名(使用EXISTS)

(27)查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

(28)查询平均工资最高的 job 信息(用到LIMIT)

(29)查询平均工资高于公司平均工资的部门有哪些?

(30)查询平均工资最低的部门信息

参考代码：

#(1)查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees
#(2)查询工资>12000的员工信息
SELECT * FROM `employees` WHERE salary > 12000
#(3)查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE NOT (department_id >= 90 AND department_id <= 110) OR salary > 15000
#(4)查询员工名中第三个字符为n，第五个字符为l的员工名和工资（员工名为last_name）
SELECT `last_name`,`salary` FROM employees WHERE `last_name` LIKE "__n_l%";
#(5)查询员工名中第二个字符为_的员工名
SELECT `last_name` FROM employees WHERE `last_name` LIKE "_\_%";
#(6)查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT * FROM `employees` WHERE `job_id` IN ('IT_PROG','AD_VP','AD_PRES');
#(7)查询有奖金的员工名和奖金率
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` IS NOT NULL
#(8)选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
#(9)查询邮箱中包含e的员工信息，并先按邮箱的字节数降序(LENGTH(str)求字符长度)，再按部门号升序
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
#(10)查询部门编号为90的员工个数，工资的最大值，最小值，平均值
SELECT COUNT(*),MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
WHERE department_id = 90
#(11)查询每个工种的员工平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
#(12)查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id
#(13)查询哪个部门的员工个数>5
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
#(14)每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000
#(15)每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY MAX(salary) ASC
#(16)查询员工名、部门名 （需实现两种连接方式：普通连接、内连接）
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
#(17)查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING 部门个数>3
#(18)查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT department_name,COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>3
ORDER BY 员工个数 DESC
#(19)查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
#(20)查询哪个部门没有员工(左外)
SELECT DISTINCT(department_name)
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
#(21)有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees WHERE `commission_pct` IS NOT NULL ORDER BY `salary` DESC LIMIT 10
#(22)返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
)
#(23)返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
                SELECT job_id
                FROM employees
                WHERE employee_id=141
                )
AND salary>(
            SELECT salary
            FROM employees
            WHERE employee_id=143
            )
#(24)返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG'
#(25)返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'
#(26)查询有员工的部门名(使用EXISTS)
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE e.`department_id` = d.`department_id`
)
#(27)查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT salary,last_name,employee_id,e.department_id
FROM employees e,
(

    SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id
) av_dep
WHERE e.department_id = av_dep.department_id
AND e.salary>av_dep.ag;
#(28)查询平均工资最高的 job 信息(用到LIMIT)
SELECT j.*
FROM jobs j
WHERE j.`job_id` = (
	SELECT job_id 
	FROM employees
	GROUP BY job_id
	ORDER BY  AVG(salary) DESC
	LIMIT 1 
)
#(29)查询平均工资高于公司平均工资的部门有哪些?
SELECT AVG(salary),department_id
FROM employees e
GROUP BY  department_id
HAVING AVG(salary)>(SELECT AVG(salary) FROM employees)
#(30)查询平均工资最低的部门信息
SELECT d.*
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT  1;

实验心得：

　　通过本次实验，我充分观察了查询结果，体会了SELECT语句实际应用，现在，我能够在查询分析器中使用SELECT语句进行简单查询，能够熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法，并熟悉使用UPDATE/INSERT/DELETE/ALTER语句进行表操作，收获颇丰。

数据库原理及应用.实验3.数据查询与更新

实验报告

实验目的:

实验环境:

实验内容及过程:

1. 在查询分析器中用SQL语句完成以下题目

单表查询

连接查询和嵌套查询

数据更新

2. 选做题

employees 员工表

departments 部门表

jobs 工种表

locations 位置表

实验心得：

附录：

推荐阅读

数据库原理及应用.实验3.数据查询与更新

实验报告

实 验 目 的:

实 验 环 境:

实 验 内 容 及 过 程:

1. 在查询分析器中用SQL语句完成以下题目

单表查询

连接查询和嵌套查询

数据更新

2. 选做题

employees 员工表

departments 部门表

jobs 工种表

locations 位置表

实 验 心 得：

附 录：

推荐阅读

实验目的:

实验环境:

实验内容及过程:

实验心得：

附录：