23级数媒技专业《数据库原理与技术》期中试题

一、填空题

填空的内容用高亮标签表示出来。

(1) SQL数据定义语言，创建、修改和删除数据库对象的命令是create、( drop ) 和( alter )。

(2) SQL数据操作语言，插入、修改、删除和查询数据的命令是insert、( update )、( delete )和select。

(3) 在SQL中数据表是由行列组成的( 二维结构 )，用来( 存储和组织 )数据。

(4) 投影是根据某些条件对关系做( 列 )分割；选择运算是根据某些条件对关系做( 行 )分割。

(5) 视图是从用户使用角度关注的数据结构，包括需要用到的字段(列)和记录(行)，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图中保存着( 查询的结果 )而没有保存( 实际的数据 )。

(6) 完整性约束包括( 实体 )完整性、域完整性、( 参照 )完整性和用户定义完整性。

(7) 创建索引的主要优点是大大( 提高 )数据查询速度(条件：where 索引列 = 、分组：group by索引列、排序：order by 索引列)和通过主键约束、唯一性约束、唯一性索引、外键约束实现数据的完整性，与此同时是要付出存储索引的( 空间代价 )和维护索引的( 性能 )代价。

(8) 游标(cursor)是从查询结果记录集中，( 逐条 )地访问记录的数据访问处理机制。

(9) 事务(Transaction)是对数据库操作的一条或者多条SQL语句组成的单元，此单元中的所有操作要么( 全部完成 )完成，要么因任何一条操作不能正常完成而( 回滚 )单元中的( 执行 )操作。

(10) 触发器是一种特殊的子程序，它的执行不是由程序或手工调用执行，而是由服务器登录、数据库对象定义或数据表数据变化等( 事件 )触发( 自动 )执行。

二、设计题

（必会技能--语句、图形化操作：建库建表、增删改查、视图过程--创建使用）

创建下列数据库、2个数据表和1个视图，并插入、修改、删除和查询数据、其中xyz是学生姓名的中文姓名，写出SQL语句和结果的截图：

1. 用SQL语句创建数据库。

创建数据库。库名[学院教师库_王骞]，字符编码utf8mb4。

CREATE DATABASE 学院教师库_王骞 CHARACTER SET utf8mb4;

2. 用SQL语句创建表并插入数据(一)。

(1) 表名 院系表_王骞(院系编号 char(3)，院系名称、院系电话char(7))

要求使用：主键(院系编号)、院系名称 唯一、院系电话：以277开头的7位电话号码(3分)。

CREATE TABLE 院系表_王骞 (
    院系编号 CHAR(3) PRIMARY KEY,
    院系名称 VARCHAR(255) UNIQUE,
    院系电话 CHAR(7) CHECK (院系电话 LIKE '277%')
);

(2) 将下列院系信息添加到院系表

院系编号 院系名称 院系电话

​ 101 表演学院 2771157

​ 102 信息工程学院 2771777

​ 103 动画学院 2771778

-- 插入数据
INSERT INTO 院系表_王骞 (院系编号, 院系名称, 院系电话) VALUES
('101', '表演学院', '2771157'),
('102', '信息工程学院', '2771777'),
('103', '动画学院', '2771778');

-- 查看
SELECT * FROM `学院教师库_王骞`.`院系表_王骞` ;
图1 院系表截图

3. 创建数据表并插入数据(二)

(1) 创建数据表[教工表_王骞]代码；

教工表_王骞(工号 char(6)，姓名，性别，生日，民族，身份证号，院系编号)

要求使用：主键(工号)、外键(院系编号)、默认(民族：汉族)、非空(民族，姓名，生日)、唯一(身份证号)、检查(性别)

 -- 创建表
CREATE TABLE 教工表_王骞 (
    工号 CHAR(6) PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    性别 CHAR(1) CHECK (性别 IN ('男', '女')),
    生日 DATE NOT NULL,
    民族 VARCHAR(20) DEFAULT '汉族',
    身份证号 VARCHAR(18) UNIQUE,
    院系编号 CHAR(3),
    FOREIGN KEY (院系编号) REFERENCES 院系表_王骞(院系编号)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

(2) 将下列信息添加到[教工表_xyz]

(其中AAA B CCCC-CC-CC DD EEEEEEEEEEEEEEEEE是你的姓名、性别、生日、民族、身份证号数据)

工号 姓名，性别，生日， 民族，身份证号， 院系编号

180001 汪琼 女 2002-01-01 满族 140101200201010001 101

180002 汪瑶 女 2001-02-01 汉族 140101200102010001 101

180003 王玥 女 2000-03-01 汉族 140101200003010001 101

180004 汪扬 男 2001-04-01 汉族 140101200104010001 102

180005 AAA B CCCC-CC-CC DD EEEEEEEEEEEEEEEEE 102

-- 插入数据
INSERT INTO 教工表_王骞 VALUES
('180001', '汪琼', '女', '2002-01-01', '满族', '140101200201010001', '101'),
('180002', '汪瑶', '女', '2001-02-01', '汉族', '140101200102010001', '101'),
('180003', '王玥', '女', '2000-03-01', '汉族', '140101200003010001', '101'),
('180004', '汪扬', '男', '2001-04-01', '汉族', '140101200104010001', '102'),
('180005', '王骞', '男', '1998-05-17', '汉族', '142202199805172158', '102');
--查看
SELECT * FROM `学院教师库_王骞`.`教工表_王骞` ;
图2 教工表截图

4. 数据修改和删除的语句

(1) 修改 院系编号为101的 院系电话：2771158

(2) 删除 院系编号为103的 院系信息

(1)  修改 院系编号为101的 院系电话：2771158
UPDATE 院系表_王骞
SET 院系电话 = '2771158'
WHERE 院系编号 = '101';

(2)  删除 院系编号为103的 院系信息
DELETE FROM 院系表_王骞
WHERE 院系编号 = '103';

5. 从教工表中查询。

(1) 女教工的姓名、性别、年龄

(2) 女教工的最大年龄、最小年龄、平均年龄

(3) 本姓(自己的姓氏)的教工的姓名、性别、年龄

(1)  女教工的姓名、性别、年龄
SELECT 姓名, 性别, TIMESTAMPDIFF(YEAR, 生日, CURDATE()) AS 年龄
FROM 教工表_王骞
WHERE 性别 = '女';

(2)  女教工的最大年龄、最小年龄、平均年龄
SELECT MAX(TIMESTAMPDIFF(YEAR, 生日, CURDATE())) AS 最大年龄,
       MIN(TIMESTAMPDIFF(YEAR, 生日, CURDATE())) AS 最小年龄,
       AVG(TIMESTAMPDIFF(YEAR, 生日, CURDATE())) AS 平均年龄
FROM 教工表_王骞
WHERE 性别 = '女';

(3)  本姓(**自己的姓氏**)的教工的姓名、性别、年龄
SELECT 姓名, 性别, TIMESTAMPDIFF(YEAR, 生日, CURDATE()) AS 年龄
FROM 教工表_王骞
WHERE 姓名 LIKE '王%';
图3 本姓的教工的姓名、性别、年龄查询截图

6. 创建视图[教工表视图_xyz]代码；

教工表视图_xyz(工号, 姓名, 性别, 年龄, 生日, 民族, 身份证号, 院系编号, 院系名称, 院系电话)

CREATE VIEW 教工表视图_王骞 AS
SELECT e.工号, e.姓名, e.性别, TIMESTAMPDIFF(YEAR, e.生日, CURDATE()) AS 年龄,
       e.生日, e.民族, e.身份证号, e.院系编号, d.院系名称, d.院系电话
FROM 教工表_王骞 e
JOIN 院系表_王骞 d ON e.院系编号 = d.院系编号;

7. 从[教工表视图_xyz]中查询。

(1) 教工的姓名、性别、年龄,查询结果按生日降序排序

(2) 本姓的本性别(自己的性别)教工的姓名、性别、院系名称、院系电话 。

(1)  教工的姓名、性别、年龄,查询结果按生日降序排序

SELECT 姓名, 性别, TIMESTAMPDIFF(YEAR, 生日, CURDATE()) AS 年龄
FROM 教工表视图_王骞
ORDER BY 生日 DESC;

(2)  本姓的本性别(自己的性别)教工的姓名、性别、院系名称、院系电话 。

SELECT 姓名, 性别, 院系名称, 院系电话
FROM 教工表视图_王骞
WHERE 姓名 LIKE '王%' AND 性别 = '男';
图4 本姓的本性别教工的姓名、性别、院系电话查询截图

8. 创建[get教工信息By工号_xyz]存储过程。

(1) 查询内容：工号、姓名、性别、年龄、生日、民族、身份证号、院系编号、院系名称、院系电话

(2) 执行存储过程查询本人的信息。

(1) 查询内容：工号、姓名、性别、年龄、生日、民族、身份证号、院系编号、院系名称、院系电话
DELIMITER //

CREATE PROCEDURE get教工信息By工号_王骞(IN 输入工号 VARCHAR(20))
BEGIN
    SELECT e.工号, e.姓名, e.性别, TIMESTAMPDIFF(YEAR, e.生日, CURDATE()) AS 年龄,
           e.生日, e.民族, e.身份证号, e.院系编号, d.院系名称, d.院系电话
    FROM 教工表_王骞 e
    JOIN 院系表_王骞 d ON e.院系编号 = d.院系编号
    WHERE e.工号 = 输入工号;
END //

DELIMITER ;

(2) 执行存储过程查询本人的信息。
CALL get教工信息By工号_王骞('180005');
图5 本人信息查询截图

返回