实践习题

创建一个名为"Student"学生表，包含（包含不限于这些列，可自行拓展）：学生表id、学生姓名、学号、出生日期、年级、班级、创建时间......

创建一个名为"Score"成绩表，包含（包含不限于这些列，可自行拓展）：成绩表id(必填)、学号、学科id、学科成绩......

创建一个名为"Subject"学科表，包含（包含不限于这些列，可自行拓展）：学科id(必填)、学科名称、科任老师、所属年级、所属班级......

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    student_number VARCHAR(20) UNIQUE,
    birth_date DATE,
    grade VARCHAR(10),
    class VARCHAR(10),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
SELECT * from Student;

CREATE TABLE Score (
    score_id INT PRIMARY KEY,
    student_number VARCHAR(20),
    subject_id INT,
    subject_score DECIMAL(5, 2),
    FOREIGN KEY (student_number) REFERENCES Student(student_number),
    FOREIGN KEY (subject_id) REFERENCES Subject(subject_id)
);
SELECT * from Score;

CREATE TABLE Subject (
    subject_id INT PRIMARY KEY,
    subject_name VARCHAR(50) NOT NULL,
    teacher_name VARCHAR(50),
    grade VARCHAR(10),
    class VARCHAR(10)
);
SELECT * from Subject;

对学生表创建索引，索引为学号列

CREATE INDEX idx_student_number ON Student (student_number);

对学生表、成绩表、学科表进行一些数据插入。

-- 插入学生数据

INSERT INTO Student VALUES (1, '小明', '001', '2000-01-15', '大一', '三班',CURRENT_TIMESTAMP), (2, '小红', '002', '2001-03-22', '大三', '一班',CURRENT_TIMESTAMP);

-- 插入学科数据

INSERT INTO Subject VALUES (1, '历史', '马老师', '大一', '二班'), (2, '科学', '王老师', '大二', '一班');

-- 插入成绩数据

INSERT INTO Score VALUES (1, '001', 1, 85.5), (2, '002', 2, 92.0);

修改某科目名称。

UPDATE Subject SET subject_name = '文学' WHERE subject_id = 1;

删除某个学生成绩。

DELETE from Score WHERE score_id = 2

使用distinct进行查询学生表的年级列。

SELECT DISTINCT grade FROM Student;

查询某个科目的所有数据

SELECT * FROM Subject WHERE subject_id = 1;

查询某科目成绩大于90分的所有成绩。

SELECT * FROM Score WHERE subject_score > 90;

查询姓名中含有"静"的所有学生。

SELECT * FROM Student WHERE student_name LIKE '小%';

查询出生日期为2002年的学生。

SELECT * FROM Student WHERE birth_date LIKE '2002%';

查询科目名称为"文学"或者"数学"的所有科目。

SELECT * FROM Subject WHERE subject_name IN ('文学', '数学');

按降序查询某科目成绩。

SELECT * FROM Score WHERE subject_id = 1 ORDER BY subject_score DESC;

查询某学科及格的所有学生信息。

SELECT Student.*, Score.subject_score FROM Student JOIN Score ON Student.student_number = Score.student_number WHERE Score.subject_id = 1 AND Score.subject_score >= 60;

查询语文成绩在7080、数学成绩在7585的学生信息。

-- 查询历史成绩在70到80之间的学生信息

SELECT Student.*, Score.subject_score AS History_Score FROM Student JOIN Score ON Student.student_number = Score.student_number JOIN Subject ON Score.subject_id = Subject.subject_id WHERE Subject.subject_name = '历史' AND Score.subject_score BETWEEN 70 AND 80

UNION

-- 查询科学成绩在75到85之间的学生信息

SELECT Student.*, Score.subject_score AS Science_Score FROM Student JOIN Score ON Student.student_number = Score.student_number JOIN Subject ON Score.subject_id = Subject.subject_id WHERE Subject.subject_name = '科学' AND Score.subject_score BETWEEN 75 AND 85;

查询学生各科成绩，创建相应视图，视图中需要包含可不限于学号、姓名、学科、成绩。

CREATE VIEW StudentScores AS SELECT S.student_number, S.student_name, SC.subject_id, SC.subject_score FROM Student AS S JOIN Score AS SC ON S.student_number = SC.student_number;

SELECT * FROM StudentScores;

建立一个触发器，记录成绩表的每次操作日志

CREATE TABLE ScoreLog ( log_id INT PRIMARY KEY AUTO_INCREMENT, operation_type VARCHAR(10), operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, student_number VARCHAR(20), subject_id INT, subject_score DECIMAL(5, 2) );

DELIMITER //

CREATE TRIGGER ScoreOperationLog AFTER INSERT, UPDATE, DELETE ON Score FOR EACH ROW BEGIN DECLARE operation VARCHAR(10);

IF (INSERTING) THEN
    SET operation = 'INSERT';
ELSEIF (UPDATING) THEN
    SET operation = 'UPDATE';
ELSE
    SET operation = 'DELETE';
END IF;

INSERT INTO ScoreLog (operation_type, student_number, subject_id, subject_score)
VALUES (operation, NEW.student_number, NEW.subject_id, NEW.subject_score);

END //

DELIMITER ;

常见的关系型数据库包括：

MySQL: 一个开源的关系型数据库管理系统，广泛用于Web应用程序的数据存储。
PostgreSQL: 一个强大的、开源的对象关系型数据库系统，具有高度的可扩展性和丰富的功能。
Microsoft SQL Server: 由Microsoft提供的关系型数据库管理系统，适用于Windows平台。
Oracle Database: 由Oracle Corporation提供的一系列关系型数据库管理系统，被广泛用于企业级应用程序。
SQLite: 一个轻量级的嵌入式关系型数据库引擎，适用于移动应用和小型设备。
IBM Db2: 由IBM提供的一系列关系型数据库产品，支持多种平台。
MariaDB: 一个由MySQL的创始人创建的开源关系型数据库管理系统，旨在保持MySQL的开放性。
SQL Server Express: Microsoft SQL Server的免费版本，适用于小规模应用和开发人员。

视图的优点都有哪些？

视图（View）是一个虚拟的表，它是基于 SQL 查询的结果集的可视化表示。视图不包含实际的数据，而是根据定义的查询从一个或多个基本表中检索数据。以下是视图的一些优点：

简化复杂的查询操作： 视图允许将复杂的查询逻辑抽象为一个单独的虚拟表，简化了复杂查询的编写和理解。
隐藏表结构： 视图提供了对底层表的抽象层，可以隐藏实际表的结构，仅暴露给用户或应用程序需要的数据和列，提高了数据的安全性和保密性。
简化权限管理： 通过视图，可以对用户和应用程序提供对特定数据集的访问权限，而无需授予对底层表的直接访问权限。这有助于实现最小权限原则，提高了安全性。
提高数据重用性： 视图允许将常用的查询逻辑抽象为一个视图，这样多个查询或报表可以共享同一视图，提高了查询逻辑的重用性。
简化数据更新： 如果基础表结构发生变化，只需更新视图定义而不是修改所有使用该表的查询。这降低了维护的复杂性。
支持数据聚合： 视图可以用于实现数据的聚合操作，从而简化对数据的统计和计算。
提高性能： 在某些情况下，数据库系统可以通过优化执行计划来提高视图查询的性能，特别是在使用了索引和其他查询优化技术的情况下。
简化应用程序开发： 视图可以为应用程序提供简洁的数据接口，使开发人员更容易构建和维护应用程序。

总的来说，视图是数据库设计和管理中非常有用的工具，可以提高数据管理的灵活性、安全性和性能。

存储过程和函数的区别？

存储过程（Stored Procedure）和函数（Function）是数据库中用于存储一组 SQL 语句并在需要时执行的可重用代码块，但它们有一些关键的区别：

返回值：
- 存储过程可以返回零个、一个或多个输出参数，也可以没有返回值。
- 函数始终返回一个值。可以是标量值、表值、或者是一个复杂的数据类型。
用途：
- 存储过程通常用于执行一系列的数据库操作，可能包括数据的修改、查询、事务控制等。
- 函数主要用于计算和返回一个值，通常用于在查询中进行计算或返回单一结果。
调用方式：
- 存储过程可以通过 CALL 或 EXECUTE 语句来调用，也可以作为事务中的一部分执行。
- 函数可以嵌套在 SELECT 语句、WHERE 子句等地方直接调用。
事务控制：
- 存储过程内部可以包含事务控制语句（例如 COMMIT 和 ROLLBACK）来控制事务的边界。
- 函数通常不包含事务控制语句，因为它们被设计为在一个查询中执行，而不是控制事务的边界。
修改数据：
- 存储过程可以包含 INSERT、UPDATE、DELETE 等修改数据的语句。
- 函数通常不应该包含对数据的修改，其主要目的是返回计算结果。
使用场景：
- 存储过程适用于执行复杂的业务逻辑、数据操作和事务处理。
- 函数适用于计算和返回单一值，例如在查询中进行数学运算或处理字符串。

总的来说，存储过程和函数在某些方面有相似之处，但它们的设计目的和用途不同，应根据具体的需求来选择使用哪个。

关系型数据库、数据库设计课件总结