实验一:通过存储过程创建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();
执行结果
记录数据写完所耗费的时间
记录表对应硬盘上文件的大小
说明
- DELIMITER $$: 将分隔符更改为
$$
以允许在存储过程中使用;
。 - CREATE PROCEDURE InsertData(): 创建一个名为
InsertData
的存储过程。 - DECLARE i INT DEFAULT 1: 声明一个名为
i
的整型变量并初始化为1。 - WHILE i <= 3000000 DO: 循环执行插入操作,直到
i
达到300万。 - 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')
生成一个电子邮件地址。
- SET i = i + 1: 每次循环后将
i
的值加1。 - END WHILE: 结束循环。
- END$$: 结束存储过程。
- 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;
注意事项
- 性能:视图是一个虚拟表,每次查询视图时,数据库都会执行视图定义中的查询。因此,复杂的视图可能会影响查询性能。
- 安全性:通过视图可以限制用户访问基础表中的某些列或行,从而提高数据安全性。
- 维护:视图的定义依赖于基础表结构,如果基础表结构发生变化,可能需要相应地修改视图定义。
实验五:触发器实验
触发器是一种特殊的存储过程,当表中的数据发生某些特定事件时(如插入、更新或删除),触发器会自动执行。触发器常用于实现数据的自动审计、数据一致性检查等功能。本实验还是基于上一个实验的 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)以及操作时间。
注意事项
- 触发器类型:触发器分为
BEFORE
和AFTER
两种类型,分别表示在数据修改之前和之后执行触发器。 - 触发事件:触发器可以在
INSERT
、UPDATE
和DELETE
三种事件上触发。 - NEW 和 OLD 关键字:在
INSERT
触发器中,NEW
关键字用于引用新插入的数据。在UPDATE
触发器中,NEW
引用更新后的数据,OLD
引用更新前的数据。在DELETE
触发器中,OLD
关键字用于引用被删除的数据。 - 性能:触发器会影响数据修改操作的性能,因为每次数据修改时触发器都会被执行。
- 调试:调试触发器可能比较困难,因为触发器是在数据修改操作期间自动执行的。建议通过日志记录的方式进行调试。
实验六:自定义函数实验
自定义函数允许你创建可重用的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;
注意事项
- 函数类型:自定义函数返回一个值,并且可以在SQL语句中使用。与存储过程不同,存储过程可以返回多个结果集或没有返回值。
- 参数:自定义函数可以接受多个参数,参数类型可以是任何有效的MySQL数据类型。
- 返回类型:自定义函数必须指定返回类型。
- DETERMINISTIC 和 NON-DETERMINISTIC:
DETERMINISTIC
表示函数的返回值仅取决于输入参数,而NON-DETERMINISTIC
表示函数的返回值可能取决于输入参数以外的因素(例如,随机数、当前时间等)。 - 性能:使用自定义函数可以提高查询的可读性和重用性,但在某些情况下,频繁调用自定义函数可能会影响性能。
实验七:流程控制实验
流程控制语句用于控制存储过程或函数的执行流程,如条件语句、循环语句等。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();
注意事项
- 条件语句:
IF
、CASE
等条件语句可以根据不同的条件执行不同的代码块。 - 循环语句:
WHILE
、REPEAT
和LOOP
循环可以重复执行一组语句,直到满足某个条件。 - 异常处理:使用
DECLARE ... HANDLER
语句可以在存储过程中处理异常,确保在发生错误时可以执行特定的代码。 - 性能:在使用循环和条件语句时,需要注意性能问题,避免编写低效的代码。
实验八:带EXISTS关键字的子查询
1. 创建数据库和表
首先,创建一个新的数据库 db13
并切换到该数据库,然后创建 student
、course
和 student2course
三个表。
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. 插入数据
向 student
、course
和 student2course
表中插入一些测试数据。
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
);