【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。

相关文章

Linux下MySQL数据库定时备份脚本与Crontab配置教学

Linux下MySQL数据库定时备份脚本与Crontab配置教学

《Linux下MySQL数据库定时备份脚本与Crontab配置教学》在生产环境中,数据库是核心资产之一,定期备份数据库可以有效防止意外数据丢失,本文将分享一份MySQL定时备份脚本,并讲解如何通过cr... 目录备份脚本详解脚本功能说明授权与可执行权限使用 Crontab 定时执行编辑 Crontab添加定
阅读更多...
如何通过try-catch判断数据库唯一键字段是否重复

如何通过try-catch判断数据库唯一键字段是否重复

《如何通过try-catch判断数据库唯一键字段是否重复》在MyBatis+MySQL中,通过try-catch捕获唯一约束异常可避免重复数据查询,优点是减少数据库交互、提升并发安全,缺点是异常处理开... 目录1、原理2、怎么理解“异常走的是数据库错误路径，开销比普通逻辑分支稍高”？1. 普通逻辑分支 v
阅读更多...
MySQL中On duplicate key update的实现示例

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP
阅读更多...
MySQL分库分表的实践示例

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分
阅读更多...
Python与MySQL实现数据库实时同步的详细步骤

Python与MySQL实现数据库实时同步的详细步骤

《Python与MySQL实现数据库实时同步的详细步骤》在日常开发中,数据同步是一项常见的需求,本篇文章将使用Python和MySQL来实现数据库实时同步,我们将围绕数据变更捕获、数据处理和数据写入这... 目录前言摘要概述：数据同步方案1. 基本思路2. mysql Binlog 简介实现步骤与代码示例1
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
MySQL 表空却 ibd 文件过大的问题及解决方法

MySQL 表空却 ibd 文件过大的问题及解决方法

《MySQL表空却ibd文件过大的问题及解决方法》本文给大家介绍MySQL表空却ibd文件过大的问题及解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录一、问题背景：表空却 “吃满” 磁盘的怪事二、问题复现：一步步编程还原异常场景1. 准备测试源表与数据
阅读更多...
Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

Mac电脑如何通过 IntelliJ IDEA 远程连接 MySQL

《Mac电脑如何通过IntelliJIDEA远程连接MySQL》本文详解Mac通过IntelliJIDEA远程连接MySQL的步骤,本文通过图文并茂的形式给大家介绍的非常详细,感兴趣的朋友跟... 目录MAC电脑通过 IntelliJ IDEA 远程连接 mysql 的详细教程一、前缀条件确认二、打开 ID
阅读更多...
MySQL的配置文件详解及实例代码

MySQL的配置文件详解及实例代码

《MySQL的配置文件详解及实例代码》MySQL的配置文件是服务器运行的重要组成部分,用于设置服务器操作的各种参数,下面：本文主要介绍MySQL配置文件的相关资料,文中通过代码介绍的非常详细,需要... 目录前言一、配置文件结构1.[mysqld]2.[client]3.[mysql]4.[mysqldum
阅读更多...
MySQL中查询和展示LONGBLOB类型数据的技巧总结

MySQL中查询和展示LONGBLOB类型数据的技巧总结

《MySQL中查询和展示LONGBLOB类型数据的技巧总结》在MySQL中LONGBLOB是一种二进制大对象（BLOB）数据类型,用于存储大量的二进制数据,：本文主要介绍MySQL中查询和展示LO... 目录前言1. 查询 LONGBLOB 数据的大小2. 查询并展示 LONGBLOB 数据2.1 转换为十
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2