【MySQL】深圳大学数据库实验二

2024-09-05 21:52
文章标签 mysql 数据库 实验 database 深圳大学

本文主要是介绍【MySQL】深圳大学数据库实验二，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

目录

一、实验目的

二、实验要求

三、实验设备

四、建议的实验步骤

4.1 EXERCISES 5 GROUP BY & HAVING

GROUP BY 的用法

HAVING 的用法

综合示例

小结

4.2 EXERCISES 6 SUB QUERIES.

1. 标量子查询（Scalar Subquery）

2. 行子查询（Row Subquery）

3. 表子查询（Table Subquery）

4. 相关子查询（Correlated Subquery）

5. 非相关子查询（Non-Correlated Subquery）

6. 存在子查询（EXISTS Subquery）

7. IN 子查询（IN Subquery）

8. ANY 和 ALL 子查询

小结

4.3 EXERCISES 7 Data Manipulation

1. 创建表（CREATE TABLE）

2. 修改表（ALTER TABLE）

3. 删除表（DROP TABLE）

5. 添加索引（CREATE INDEX）

6. 删除索引（DROP INDEX）

7. 添加主键（ALTER TABLE ... ADD PRIMARY KEY）

8. 删除主键（ALTER TABLE ... DROP PRIMARY KEY）

小结

五、实验总结

一、实验目的

  1. 了解DBMS系统的功能、软件组成；

  2. 掌握利用SQL语句定义、操纵数据库的方法。

二、实验要求

  1. 在课外安装相关软件并浏览软件自带的帮助文件和功能菜单，了解DBMS的功能、结构；

  2. 创建一个有两个关系表的数据库；（建议采用MYSQL）

  3. 数据库、关系表定义；

  4. 学习定义关系表的约束(主键、外键、自定义)；

  5. 了解SQL的数据定义功能；

  6. 了解SQL的操纵功能；

  7. 掌握典型的SQL语句的功能；

  8. 了解视图的概念；

三、实验设备

        计算机、数据库管理系统如MYSQL等软件。

四、建议的实验步骤

4.1 EXERCISES 5 GROUP BY & HAVING

GROUP BY 和 HAVING 是 SQL 中用于处理分组和过滤的关键字。它们通常一起使用，以便在分组数据后进行过滤。以下是它们的用法说明和示例：

GROUP BY 的用法

GROUP BY 用于将查询结果按一个或多个列进行分组。它通常与聚合函数一起使用，如 COUNT()、SUM()、AVG()、MAX() 和 MIN()，以计算每个分组的汇总信息。

SELECT column1, aggregation_function(column2)
FROM table_name
GROUP BY column1;

示例：

假设你有一个名为 sales 的表，包含以下字段：salesperson 和 amount，表示销售人员和销售金额。你可以按销售人员分组，并计算每个销售人员的总销售额：

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;

HAVING 的用法

HAVING 用于过滤 GROUP BY 产生的分组结果。它的作用类似于 WHERE，但 WHERE 只能过滤原始数据，而 HAVING 是用于过滤聚合后的数据。

SELECT column1, aggregation_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregation_function(column2) condition;

示例：

在上面的 sales 表的基础上，如果你只想显示总销售额大于 5000 的销售人员，可以使用 HAVING 来进行过滤：

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING SUM(amount) > 5000;

综合示例

假设你有一个名为 orders 的表，包含以下字段：customer_id、order_date 和 total_amount。你想要找出每位客户的总订单金额，并且只显示总金额大于 1000 的客户。你可以先按 customer_id 分组，然后计算每个客户的总订单金额，最后使用 HAVING 来过滤总金额大于 1000 的客户。

SELECT customer_id, SUM(total_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

练习： 

-- ====================== EXERCISES	5	GROUP BY & HAVING =======================
-- 1.List the department number and average salary of each department.
select DEPTNO, avg(SAL)
from emp2022150212
group by DEPTNO;-- 2.Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group's average annual salary.
select DEPTNO, JOB, avg(SAL)
from emp2022150212
group by DEPTNO, JOB;-- 3.Issue the same query as above except list the department name rather than the department number.
select d.DNAME, JOB, avg(SAL)
from emp2022150212 e,dept2022150212 d
where e.DEPTNO = d.DEPTNO
group by e.DEPTNO, e.JOB;-- 4.List the average annual salary for all job groups having more than 2 employees in the group.
select JOB, avg(SAL)
from emp2022150212
group by JOb
having count(*) > 2;-- 5.Find all departments with an average commission greater than 25% of average salary.
select DEPTNO
from emp2022150212
group by DEPTNO
having avg(COMM) > avg(SAL) * 0.25;-- 6.Find each department's average annual salary for all its employees except the managers and the president.
select DEPTNO, avg(SAL)
from emp2022150212
where JOB != 'Manager'and JOB != 'President'
group by DEPTNO;

小结

  • GROUP BY: 用于将结果集按指定列分组，并通常与聚合函数一起使用。
  • HAVING: 用于过滤分组后的结果集，基于聚合函数的计算结果。

4.2 EXERCISES 6 SUB QUERIES.

在 MySQL 中，子查询（或称为嵌套查询）是一种将一个查询的结果作为另一个查询的输入的方法。子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中使用。根据子查询的位置和用途，子查询可以分为不同的类型。以下是 MySQL 中子查询的分类及其用法：

1. 标量子查询（Scalar Subquery）

标量子查询返回单个值（单行单列）。这种子查询通常用于 SELECT 语句中，作为一个计算表达式的一部分。

示例：

查找每个员工的薪水加上公司中最高薪水的子查询：

SELECT name, salary, (SELECT MAX(salary) FROM employees) AS highest_salary
FROM employees;

2. 行子查询（Row Subquery）

行子查询返回单行多列的数据。这种子查询可以与其他子查询或主查询中的列进行比较。

示例：

查找 employees 表中薪水等于某个特定薪水水平的员工：

SELECT name, salary
FROM employees
WHERE (salary, position) = (SELECT salary, position FROM employees WHERE id = 1);

3. 表子查询（Table Subquery）

表子查询返回多行多列的数据。它可以用作 FROM 子句中的一个虚拟表或视图。

示例：

找到薪水高于部门平均薪水的员工：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

4. 相关子查询（Correlated Subquery）

相关子查询的每次执行都依赖于外部查询的当前行。换句话说，相关子查询中的某些列来自主查询。

示例：

查找每个部门中薪水高于该部门平均薪水的员工：

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

5. 非相关子查询（Non-Correlated Subquery）

非相关子查询在执行时不依赖于外部查询。它是独立的，并且可以在不依赖于主查询的情况下被执行。

示例：

查找薪水高于所有员工的平均薪水的员工：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

6. 存在子查询（EXISTS Subquery）

存在子查询用于测试是否存在满足条件的行。它通常与 EXISTS 关键字一起使用。如果子查询返回至少一行结果，EXISTS 返回 TRUE。

示例：

查找有订单记录的客户：

SELECT customer_id
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE customers.customer_id = orders.customer_id);

7. IN 子查询（IN Subquery）

IN 子查询用于测试某个值是否存在于子查询的结果集中。它通常与 IN 关键字一起使用。

示例：

查找来自特定城市的客户：

SELECT customer_id, customer_name
FROM customers
WHERE city IN (SELECT city FROM branch WHERE branch_name = 'Main Branch');

8. ANY 和 ALL 子查询

ANY 和 ALL 关键字用于与子查询的结果集进行比较。ANY 用于与子查询中的任何值进行比较，而 ALL 用于与所有值进行比较。

示例（ANY）：

查找薪水高于部门中任何一个员工的薪水：

SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 1);

示例（ALL）：

查找薪水高于部门中所有员工的薪水:

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 1);

 练习：

-- ====================== EXERCISES	6	SUB QUERIES. =======================
-- 1.List the name and job of employees who have the same job as Jones.
select ENAME, JOB
from emp2022150212
where JOB = (select JOB from emp2022150212 where ENAME = 'Jones');-- 2.Find all the employees in Department 10 that have a job that is the same as anyone in department 30.
select *
from emp2022150212
where DEPTNO = 10and JOb in (select JOB from emp2022150212 where DEPTNO = 30);-- 3.List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.
select ENAME, JOB, DEPTNO
from emp2022150212
where JOB = (select JOB from emp2022150212 where ENAME = 'Jones')or SAL >= (select SAL from emp2022150212 where ENAME = 'Ford');-- 4.Find all employees in department 10 that have a job that is the same as anyone in the Sales department
select *
from emp2022150212
where DEPTNO = 10and JOB in (select e.JOBfrom emp2022150212 e,dept2022150212 dwhere e.DEPTNO = d.DEPTNOand d.DNAME = 'Sales');-- 5.Find the employees located in Liverpool who have the same job as Allen.  Return the results in alphabetical order by employee name.
select e.*
from emp2022150212 e,dept2022150212 d
where e.DEPTNO = d.DEPTNOand d.LOC = 'Liverpool'and JOB = (select JOB from emp2022150212 where ENAME = 'Allen')
order by e.ENAME asc;-- 6.Find all the employees that earn more than the average salary of employees in their department.
select *
from emp2022150212 e,(select DEPTNO, avg(SAL) as avg_sal from emp2022150212 group by DEPTNO) d
where e.DEPTNO = d.DEPTNOand e.SAL > d.avg_sal;-- 7.Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.
select *
from emp2022150212
where SAL > (select SAL from emp2022150212 where ENAME = 'JONES');

小结

  • 标量子查询: 返回单个值。
  • 行子查询: 返回单行多列的数据。
  • 表子查询: 返回多行多列的数据，通常用作虚拟表。
  • 相关子查询: 每次执行依赖于外部查询的当前行。
  • 非相关子查询: 独立执行，不依赖于外部查询。
  • 存在子查询: 测试是否存在满足条件的行。
  • IN 子查询: 测试某个值是否在子查询的结果集中。
  • ANY 和 ALL 子查询: 用于与子查询的结果集进行比较。

4.3 EXERCISES 7 Data Manipulation

1. 创建表（CREATE TABLE）

创建一个新表时，你需要定义表的结构，包括列的名称、数据类型以及其他属性（如主键、默认值等）。

语法：

CREATE TABLE table_name (column1 data_type [constraints],column2 data_type [constraints],...[table_constraints]
);

2. 修改表（ALTER TABLE）

修改表结构可以包括添加、删除或修改列，添加或删除索引，修改表的约束等。

添加列：

ALTER TABLE table_name
ADD column_name data_type [constraints];

删除列：

ALTER TABLE table_name
DROP COLUMN column_name;

修改列的数据类型或属性：

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type [constraints];

重命名列：

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name data_type [constraints];

重命名表：

ALTER TABLE old_table_name
RENAME TO new_table_name;

3. 删除表（DROP TABLE）

删除表会将表结构以及所有数据完全删除。请谨慎使用此操作。

语法：

DROP TABLE table_name;

4. 复制表（CREATE TABLE ... SELECT）

复制一个表的结构和数据可以使用 CREATE TABLE ... SELECT 语法。如果只需要复制结构而不包含数据，可以使用 WHERE 子句来实现。

语法（复制表结构和数据）：

CREATE TABLE new_table AS
SELECT * FROM existing_table;

语法（仅复制表结构）：

CREATE TABLE new_table LIKE existing_table;

5. 添加索引（CREATE INDEX）

索引可以提高查询效率。你可以在一个或多个列上创建索引。

语法：

CREATE INDEX index_name
ON table_name (column1, column2, ...);

6. 删除索引（DROP INDEX）

删除索引不会影响表的其他结构，只会移除索引。

语法：

DROP INDEX index_name ON table_name;

7. 添加主键（ALTER TABLE ... ADD PRIMARY KEY）

在表中添加主键约束可以确保唯一性并提高查询效率。

语法：

ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

8. 删除主键（ALTER TABLE ... DROP PRIMARY KEY）

语法：

ALTER TABLE table_name
DROP PRIMARY KEY;

练习：

-- ====================== EXERCISES	7	Data Manipulation =======================
-- 1.Create a new table called loans with columns named LNO NUMERIC (3), EMPNO NUMERIC (4), TYPE CHAR(1), AMNT NUMERIC (8,2)
create table loans
(LNO   tinyint unsigned,ENPNO mediumint unsigned,TYPE  varchar(1),AMNT  decimal(8, 2)
);-- 2.Insert the following data
--      LNO		EMPNO		TYPE		AMNT
--		23		7499			M		20000.00
--		42		7499			C		 2000.00
--		65		7844			M		 3564.00
insert into loans
values (23, 7499, 'M', 20000.00),(42, 7499, 'C', 2000.00),(65, 7844, 'M', 3564.00);-- 3.Check that you have created 3 new records in Loans
select *
from loans;-- 4.The Loans table must be altered to include another column OUTST NUMERIC(8,2)
alter table loansadd OUTST decimal(8, 2);-- 5.Add 10% interest to all M type loans
update loans
set AMNT = AMNT * (1.1)
where TYPE = 'M';-- 6.Remove all loans less than  £3000.00
delete
from loans
where AMNT < 3000;-- 7.Change the name of loans table to accounts
rename table loans to accounts;-- 8.Change the name of column LNO to LOANNO
alter table accountschange LNO LOANNO tinyint unsigned;-- 9.Create a view for use by personnel in department 30 showing employee name, number, job and hiredate
create view my_view as
select ENAME, EMPNO, JOB, HIREDATE
from emp2022150212
where DEPTNO = 30;-- 10.Use the view to show employees in department 30 having jobs which are not salesman
create view my_view2 as
select *
from emp2022150212
where DEPTNO = 30and JOB != 'Salesman';-- 11.Create a view which shows summary information for each department.
create view my_view3 as
select *
from dept2022150212;

小结

  • 创建表: 使用 CREATE TABLE 语句定义表结构。
  • 修改表: 使用 ALTER TABLE 语句进行结构修改（添加、删除、修改列等）。
  • 删除表: 使用 DROP TABLE 语句删除表。
  • 复制表: 使用 CREATE TABLE ... SELECT 或 CREATE TABLE LIKE 语法复制表结构和数据。
  • 添加索引: 使用 CREATE INDEX 提高查询效率。
  • 删除索引: 使用 DROP INDEX 移除索引。
  • 添加主键: 使用 ALTER TABLE ... ADD PRIMARY KEY 添加主键约束。
  • 删除主键: 使用 ALTER TABLE ... DROP PRIMARY KEY 删除主键约束。

五、实验总结

通过本次实验，我们掌握了 GROUP BY 和 HAVING 的用法，能够在分组后进行数据的过滤。我们学会了各种子查询的分类及其实际应用，并能够根据需要使用不同类型的子查询来解决问题。最后，我们还学习了如何进行表结构的操作，包括创建、修改、删除表，以及操作索引和主键。这些技能对于数据库管理和优化查询性能至关重要。

这篇关于【MySQL】深圳大学数据库实验二的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1140169。

相关文章

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

《MySQL多表连接操作方法(INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN)》多表连接是一种将两个或多个表中的数据组合在一起的SQL操作,通过连接,... 目录一、 什么是多表连接？二、 mysql 支持的连接类型三、 多表连接的语法四、实战示例 数据准备五、连接的性
阅读更多...
MySQL中的分组和多表连接详解

MySQL中的分组和多表连接详解

《MySQL中的分组和多表连接详解》：本文主要介绍MySQL中的分组和多表连接的相关操作,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录mysql中的分组和多表连接一、MySQL的分组（group javascriptby ）二、多表连接（表连接会产生大量的数据垃圾）MySQL中的
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...
SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...
mysql中的group by高级用法

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使
阅读更多...
Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2