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

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
Java 连接Sql sever 2008

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2