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

实验报告

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

实 验 目 的:

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

实 验 环 境:

　　MySQL 5.7.33、SQLyog-11.2.4

实 验 内 容 及 过 程:

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

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

单表查询

  1. 找出所有供应商的姓名和所在城市。

    SELECT SNAME,CITY FROM S

  2. 找出所有零件的名称、颜色、重量。

    SELECT PNAME,COLOR,WEIGHT FROM P

  3. 找出使用供应商S1所供应零件的工程号码。

    SELECT JNO FROM SPJ WHERE SNO='S1'

  4. 列出所有地址在北京的供应商姓名。

    SELECT SNAME FROM S WHERE CITY='北京'

  5. 模糊查询，找出名字包含"螺丝"两个字且颜色为红色的零件名称。

    SELECT PNAME FROM P
WHERE PNAME LIKE '螺丝_' AND COLOR='红'

  6. 统计不同城市工程项目的数量，结果显示"城市"、"工程项目数合计"。

    SELECT CITY,COUNT(JNO) AS '工程项目书合计'
FROM J GROUP BY CITY

连接查询和嵌套查询

  1. 找出工程项目J2使用的各种零件的名称及其数量。

    SELECT PNAME,QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND JNO='J2'

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

    #自然连接
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='上海'
	)
)

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

    #自然连接
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='上海'
	)
)

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

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

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

    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

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

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

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

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

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

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

数据更新

  1. 把全部红色零件的颜色改成蓝色。

    UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'

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

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

  3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

    DELETE FROM SPJ WHERE SNO='S2'

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

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

  5. 为S表添加供应商，供应商编号：S6,供应商名称：伟星，城市：北京，状态暂未定。

    INSERT INTO S(SNO,SNAME,CITY,STATUS)
VALUES('S6','伟星','北京',NULL)

  6. 为P表添加零件，零件编号：P7,零件名称：凹轮，颜色：蓝，重量：20。

    INSERT INTO P
VALUES('P7','凹轮','蓝',20)

  7. 在P表中加入属性零件产地CITY(CHAR型)。

    ALTER TABLE P ADD CITY CHAR

  8. 将P表中加入属性WEIGHT改为SMALLINT型。

    ALTER TABLE P MODIFY WEIGHT SMALLINT

  9. 删除刚才在P表中加入的零件产地CITY属性。

    ALTER TABLE P DROP COLUMN CITY

2. 选做题

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

  2. 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) 国家编号

  3. 练习题

    (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语句进行表操作，收获颇丰。

附 录：

posted @ 2021-04-12 00:00  海边星  阅读(2841)  评论(0)  编辑  收藏  举报