实验:SQL综合练习2

实验一:通过存储过程创建300w条数据

创建表

假设表的名称为test_table,且表结构如下:

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

编写存储过程

编写一个存储过程,该过程使用循环插入300万条数据。

DELIMITER $$

CREATE PROCEDURE InsertData()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE i <= 3000000 DO
        INSERT INTO test_table (name, age, email)
        VALUES (CONCAT('Name', i), FLOOR(10 + (RAND() * 60)), CONCAT('email', i, '@example.com'));
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

执行存储过程

CALL InsertData();

执行结果

记录数据写完所耗费的时间

记录表对应硬盘上文件的大小

说明

  1. DELIMITER $$: 将分隔符更改为$$以允许在存储过程中使用
  2. CREATE PROCEDURE InsertData(): 创建一个名为InsertData的存储过程。
  3. DECLARE i INT DEFAULT 1: 声明一个名为i的整型变量并初始化为1。
  4. WHILE i <= 3000000 DO: 循环执行插入操作,直到i达到300万。
  5. INSERT INTO test_table (name, age, email): 向test_table表中插入一条记录。
  • name字段使用CONCAT('Name', i)生成一个名称。
  • age字段使用FLOOR(10 + (RAND() * 60))生成一个介于10到70之间的随机年龄。
  • email字段使用CONCAT('email', i, '@example.com')生成一个电子邮件地址。
  1. SET i = i + 1: 每次循环后将i的值加1。
  2. END WHILE: 结束循环。
  3. END$$: 结束存储过程。
  4. DELIMITER ;: 将分隔符更改回默认的

注意

插入大批量数据时,请注意以下几点:

  • 插入过程可能会花费较长时间,具体取决于您的硬件配置和MySQL配置。
  • 在实际生产环境中插入大批量数据时,建议分批插入以减少对数据库性能的影响。
  • 确保有足够的磁盘空间和适当的索引以处理大数据量。

实验二:带ALL和ANY关键字的子查询

一、实验介绍

基于一个名为 employee 的表执行子查询,以查找满足特定条件的员工数据。

创建 employee 表和插入数据

首先,创建 employee 表,并插入一些数据:

CREATE TABLE employee (
    id INT NOT NULL UNIQUE AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex ENUM('male', 'female') NOT NULL DEFAULT 'male',
    age INT(3) UNSIGNED NOT NULL DEFAULT 28,
    hire_date DATE NOT NULL,
    post VARCHAR(50),
    post_comment VARCHAR(100),
    salary DOUBLE(15,2),
    office INT,
    depart_id INT
);

INSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) VALUES
('egon', 'male', 18, '2017-03-01', 'teacher', 7300.33, 401, 1),
('alex', 'male', 78, '2015-03-02', 'teacher', 1000000.31, 401, 1),
('wupeiqi', 'male', 81, '2013-03-05', 'teacher', 8300, 401, 1),
('yuanhao', 'male', 73, '2014-07-01', 'teacher', 3500, 401, 1),
('liwenzhou', 'male', 28, '2012-11-01', 'teacher', 2100, 401, 1),
('jingliyang', 'female', 18, '2011-02-11', 'teacher', 9000, 401, 1),
('jinxin', 'male', 18, '1900-03-01', 'teacher', 30000, 401, 1),
('成龙', 'male', 48, '2010-11-11', 'teacher', 10000, 401, 1),
('歪歪', 'female', 48, '2015-03-11', 'sale', 3000.13, 402, 2),
('丫丫', 'female', 38, '2010-11-01', 'sale', 2000.35, 402, 2),
('丁丁', 'female', 18, '2011-03-12', 'sale', 1000.37, 402, 2),
('星星', 'female', 18, '2016-05-13', 'sale', 3000.29, 402, 2),
('格格', 'female', 28, '2017-01-27', 'sale', 4000.33, 402, 2),
('张野', 'male', 28, '2016-03-11', 'operation', 10000.13, 403, 3),
('程咬金', 'male', 18, '1997-03-12', 'operation', 20000, 403, 3),
('程咬银', 'female', 18, '2013-03-11', 'operation', 19000, 403, 3),
('程咬铜', 'male', 18, '2015-04-11', 'operation', 18000, 403, 3),
('程咬铁', 'female', 18, '2014-05-12', 'operation', 17000, 403, 3);

创建 department 表并插入数据:

CREATE TABLE department (
    id INT NOT NULL UNIQUE,
    name VARCHAR(20) NOT NULL
);

INSERT INTO department (id, name) VALUES
(1, '教学'),
(2, '销售'),
(3, '运营'),
(4, '技术');

显示表结构

DESC employee;

二、使用带ALL和ANY关键字的子查询进行查询

1. 查询薪资比所有部门的平均薪资都高的员工

SELECT * 
FROM employee 
WHERE salary > ALL(SELECT AVG(salary) FROM employee GROUP BY depart_id);

2. 查询薪资比所有部门的平均薪资都低的员工

SELECT * 
FROM employee 
WHERE salary < ALL(SELECT AVG(salary) FROM employee GROUP BY depart_id);

3. 查询薪资不垫底的员工: 薪资在任一部门平均线以上的员工

SELECT * 
FROM employee 
WHERE salary > ANY (SELECT AVG(salary) FROM employee GROUP BY depart_id);

4. 查询薪资不冒尖的员工: 薪资在任一部门平均线以下的员工

SELECT * 
FROM employee 
WHERE salary < ANY (SELECT AVG(salary) FROM employee GROUP BY depart_id);

实验三:链表查询和子查询对比

1. 实验介绍

在本实验中,我们将上一个实验的 employee 的表,通过子查询和链表连接两种方式,查询每个部门最新入职的员工。具体示例代码将展示如何使用这两种方法来实现。

2. 使用子查询查询每个部门最新入职的员工

SELECT t3.name, t3.post, t3.hire_date 
FROM employee AS t3 
WHERE id IN (
    SELECT (
        SELECT id 
        FROM employee AS t2 
        WHERE t2.depart_id = t1.depart_id 
        ORDER BY hire_date DESC 
        LIMIT 1
    ) 
    FROM employee AS t1 
    GROUP BY depart_id
);

3. 使用链表连接查询每个部门最新入职的员工

SELECT e.id, e.name, e.depart_id, e.hire_date
FROM employee e
INNER JOIN (
    SELECT depart_id, MAX(hire_date) AS max_hire_date
    FROM employee
    GROUP BY depart_id
) AS subquery
ON e.depart_id = subquery.depart_id AND e.hire_date = subquery.max_hire_date;

实验四:视图实验

视图是一个虚拟表,它是从数据库中一个或多个表中通过查询语句生成的。视图提供了一种简化复杂查询和提高数据安全性的方法。本实验还是基于上一个实验的 employee 的表。

1. 创建视图

以下示例展示了如何创建一个显示员工信息的视图:

CREATE VIEW employee_view AS
SELECT 
    e.id, 
    e.name, 
    e.sex, 
    e.age, 
    e.hire_date, 
    e.post, 
    e.salary, 
    d.name AS department
FROM 
    employee e
JOIN 
    department d ON e.depart_id = d.id;

2. 查询视图

创建视图后,可以像查询普通表一样查询视图:

SELECT * FROM employee_view;

3. 更新视图

视图本身是不能直接更新的,但是可以通过视图更新基础表的数据。前提是视图必须满足一定的条件,例如视图中不能包含聚合函数、子查询等。

UPDATE employee_view
SET salary = salary + 1000
WHERE name = 'egon';

4. 删除视图

如果不再需要某个视图,可以将其删除:

DROP VIEW employee_view;

5. 查看所有视图

可以查询 information_schema 数据库中的 VIEWS 表,查看当前数据库中的所有视图:

SELECT table_name
FROM information_schema.views
WHERE table_schema = 'your_database_name';

6. 查看视图定义

可以使用 SHOW CREATE VIEW 命令查看视图的定义:

SHOW CREATE VIEW employee_view;

完整示例操作

下面是创建、使用和删除视图的一系列完整操作:

-- 创建视图
CREATE VIEW employee_view AS
SELECT 
    e.id, 
    e.name, 
    e.sex, 
    e.age, 
    e.hire_date, 
    e.post, 
    e.salary, 
    d.name AS department
FROM 
    employee e
JOIN 
    department d ON e.depart_id = d.id;

-- 查询视图
SELECT * FROM employee_view;

-- 更新视图中的数据(实际更新基础表)
UPDATE employee_view
SET salary = salary + 1000
WHERE name = 'egon';

-- 查看所有视图
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'your_database_name';

-- 查看视图定义
SHOW CREATE VIEW employee_view;

-- 删除视图
DROP VIEW employee_view;

注意事项

  1. 性能:视图是一个虚拟表,每次查询视图时,数据库都会执行视图定义中的查询。因此,复杂的视图可能会影响查询性能。
  2. 安全性:通过视图可以限制用户访问基础表中的某些列或行,从而提高数据安全性。
  3. 维护:视图的定义依赖于基础表结构,如果基础表结构发生变化,可能需要相应地修改视图定义。

实验五:触发器实验

触发器是一种特殊的存储过程,当表中的数据发生某些特定事件时(如插入、更新或删除),触发器会自动执行。触发器常用于实现数据的自动审计、数据一致性检查等功能。本实验还是基于上一个实验的 employee 的表。

1. 创建 employee_log

在创建触发器之前,需要一个日志表 employee_log 来记录员工的插入操作。

CREATE TABLE employee_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(50),
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 创建触发器

以创建一个在插入新员工时记录日志的触发器:

DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, action) VALUES (NEW.id, 'INSERT');
END //

DELIMITER ;

3. 查看触发器

可以查询 information_schema 数据库中的 TRIGGERS 表,查看当前数据库中的所有触发器:

SELECT trigger_name
FROM information_schema.triggers
WHERE trigger_schema = 'your_database_name';

4. 查看触发器定义

可以使用 SHOW TRIGGERS 命令查看触发器的定义:

SHOW TRIGGERS FROM your_database_name;

5. 删除触发器

如果不再需要某个触发器,可以将其删除:

DROP TRIGGER after_employee_insert;

6. 触发器触发结果

6.1. 插入数据并触发触发器:

INSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) 
VALUES ('new_employee', 'male', 25, '2023-06-01', 'developer', 6000.00, 404, 4);

6.2. 查看日志:

SELECT * FROM employee_log;

查询结果:

在插入一条新员工记录后,employee_log 表中记录了一条对应的日志,显示该员工的ID、操作类型(INSERT)以及操作时间。

注意事项

  1. 触发器类型:触发器分为 BEFOREAFTER 两种类型,分别表示在数据修改之前和之后执行触发器。
  2. 触发事件:触发器可以在 INSERTUPDATEDELETE 三种事件上触发。
  3. NEW 和 OLD 关键字:在 INSERT 触发器中,NEW 关键字用于引用新插入的数据。在 UPDATE 触发器中,NEW 引用更新后的数据,OLD 引用更新前的数据。在 DELETE 触发器中,OLD 关键字用于引用被删除的数据。
  4. 性能:触发器会影响数据修改操作的性能,因为每次数据修改时触发器都会被执行。
  5. 调试:调试触发器可能比较困难,因为触发器是在数据修改操作期间自动执行的。建议通过日志记录的方式进行调试。

实验六:自定义函数实验

自定义函数允许你创建可重用的SQL代码块,可以在SQL语句中调用这些函数。自定义函数在MySQL中非常有用,它们可以用于封装复杂的计算或业务逻辑,使得代码更加简洁和可维护。本实验还是基于上一个实验的 employee 的表。

1. 创建自定义函数

创建一个计算员工年薪的自定义函数:

DELIMITER //

CREATE FUNCTION calculate_annual_salary(monthly_salary DOUBLE)
RETURNS DOUBLE
DETERMINISTIC
BEGIN
    RETURN monthly_salary * 12;
END //

DELIMITER ;

2. 使用自定义函数

创建函数后,可以在SQL查询中使用它:

SELECT 
    name, 
    calculate_annual_salary(salary) AS annual_salary 
FROM 
    employee;

3. 查看自定义函数

可以查询 information_schema 数据库中的 ROUTINES 表,查看当前数据库中的所有自定义函数:

SELECT routine_name
FROM information_schema.routines
WHERE routine_type = 'FUNCTION' AND routine_schema = 'your_database_name';

4. 查看自定义函数定义

可以使用 SHOW CREATE FUNCTION 命令查看自定义函数的定义:

SHOW CREATE FUNCTION calculate_annual_salary;

5. 删除自定义函数

如果不再需要某个自定义函数,可以将其删除:

DROP FUNCTION calculate_annual_salary;

注意事项

  1. 函数类型:自定义函数返回一个值,并且可以在SQL语句中使用。与存储过程不同,存储过程可以返回多个结果集或没有返回值。
  2. 参数:自定义函数可以接受多个参数,参数类型可以是任何有效的MySQL数据类型。
  3. 返回类型:自定义函数必须指定返回类型。
  4. DETERMINISTIC 和 NON-DETERMINISTICDETERMINISTIC 表示函数的返回值仅取决于输入参数,而 NON-DETERMINISTIC 表示函数的返回值可能取决于输入参数以外的因素(例如,随机数、当前时间等)。
  5. 性能:使用自定义函数可以提高查询的可读性和重用性,但在某些情况下,频繁调用自定义函数可能会影响性能。

实验七:流程控制实验

流程控制语句用于控制存储过程或函数的执行流程,如条件语句、循环语句等。MySQL 支持多种流程控制语句,使得存储过程和函数能够处理复杂的逻辑。本实验还是基于上一个实验的 employee 的表。

1. 条件语句

条件语句用于根据不同的条件执行不同的代码块。

示例:创建一个带有条件语句的存储过程
DELIMITER //

CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DOUBLE;
    
    SELECT salary INTO emp_salary 
    FROM employee 
    WHERE id = emp_id;
    
    IF emp_salary > 50000 THEN
        SELECT 'High salary' AS status;
    ELSE
        SELECT 'Low salary' AS status;
    END IF;
END //

DELIMITER ;

调用存储过程:

CALL check_salary(1);

2. 循环语句

循环语句用于重复执行一组语句,直到满足某个条件。

示例:使用 WHILE 循环创建存储过程

DELIMITER //

CREATE PROCEDURE list_employees() # 打印前10个employee
BEGIN
    DECLARE emp_id INT DEFAULT 1;
    
    WHILE emp_id <= 10 DO
        SELECT * FROM employee WHERE id = emp_id;
        SET emp_id = emp_id + 1;
    END WHILE;
END //

DELIMITER ;

调用存储过程:

CALL list_employees();

示例:使用 REPEAT 循环创建存储过程

DELIMITER //

CREATE PROCEDURE repeat_example() # 从1开始打印直到i大于5
BEGIN
    DECLARE i INT DEFAULT 1;
    
    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > 5
    END REPEAT;
END //

DELIMITER ;

调用存储过程:

CALL repeat_example();
示例:使用 LOOP 循环创建存储过程
DELIMITER //

CREATE PROCEDURE loop_example()  # 从1开始打印直到i大于5
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: LOOP
        SELECT i;
        SET i = i + 1;
        IF i > 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP loop_label;
END //

DELIMITER ;

调用存储过程:

CALL loop_example();

注意事项

  1. 条件语句IFCASE 等条件语句可以根据不同的条件执行不同的代码块。
  2. 循环语句WHILEREPEATLOOP 循环可以重复执行一组语句,直到满足某个条件。
  3. 异常处理:使用 DECLARE ... HANDLER 语句可以在存储过程中处理异常,确保在发生错误时可以执行特定的代码。
  4. 性能:在使用循环和条件语句时,需要注意性能问题,避免编写低效的代码。

实验八:带EXISTS关键字的子查询

1. 创建数据库和表

首先,创建一个新的数据库 db13 并切换到该数据库,然后创建 studentcoursestudent2course 三个表。

CREATE DATABASE db13;

USE db13;

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(16)
);

CREATE TABLE course (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(16),
    comment VARCHAR(20)
);

CREATE TABLE student2course (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sid INT,
    cid INT,
    FOREIGN KEY (sid) REFERENCES student(id),
    FOREIGN KEY (cid) REFERENCES course(id)
);

2. 插入数据

studentcoursestudent2course 表中插入一些测试数据。

INSERT INTO student(name) VALUES
('egon'),
('lili'),
('jack'),
('tom');

INSERT INTO course(name, comment) VALUES
('数据库', '数据仓库'),
('数学', '根本学不会'),
('英语', '鸟语花香');

INSERT INTO student2course(sid, cid) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 2),
(3, 2);

3. 进行查询

3.1 查询选修了所有课程的学生id、name

该查询查找那些选修了所有课程的学生。

方法1:

SELECT * 
FROM student s 
WHERE NOT EXISTS (
    SELECT * 
    FROM course c 
    WHERE NOT EXISTS (
        SELECT * 
        FROM student2course sc 
        WHERE sc.sid = s.id AND sc.cid = c.id
    )
);

方法2:

SELECT s.name 
FROM student AS s
INNER JOIN student2course AS sc ON s.id = sc.sid
GROUP BY s.name 
HAVING COUNT(sc.id) = (SELECT COUNT(id) FROM course);

3.2 查询没有选择所有课程的学生,即没有全选的学生

该查询查找那些至少有一门课程未选修的学生。

SELECT * 
FROM student s 
WHERE EXISTS (
    SELECT * 
    FROM course c 
    WHERE NOT EXISTS (
        SELECT * 
        FROM student2course sc 
        WHERE sc.sid = s.id AND sc.cid = c.id
    )
);

3.3 查询一门课也没有选的学生

该查询查找那些一门课也没有选的学生。

SELECT * 
FROM student s 
WHERE NOT EXISTS (
    SELECT * 
    FROM student2course sc 
    WHERE sc.sid = s.id
);

3.4 查询至少选修了一门课程的学生

该查询查找那些至少选修了一门课程的学生。

SELECT * 
FROM student s 
WHERE EXISTS (
    SELECT * 
    FROM student2course sc 
    WHERE sc.sid = s.id
);
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇