以下是针对学生选课系统练习题中所有建表语句的详细解释涵盖DDL、DML、DCL、触发器与存储过程等核心知识点。1. DDL数据定义语言建表语句详解题目 1创建 Student 表CREATE TABLE Student ( sid VARCHAR(10) PRIMARY KEY, sname VARCHAR(50) NOT NULL, gender CHAR(2) DEFAULT 男, age INT CHECK (age 15 AND age 30), dept VARCHAR(20) );CREATE TABLE核心命令用于创建新表 。sid VARCHAR(10) PRIMARY KEY定义学号字段VARCHAR(10)表示可变长度字符串最大10个字符PRIMARY KEY指定该字段为主键确保唯一且非空 。sname VARCHAR(50) NOT NULL学生姓名字段NOT NULL约束保证该字段必须有值 。gender CHAR(2) DEFAULT 男性别字段CHAR(2)表示固定长度2个字符DEFAULT 男设置默认值为“男” 。age INT CHECK (age 15 AND age 30)年龄字段INT为整数类型CHECK约束限制年龄必须在15到30之间 。dept VARCHAR(20)系别字段存储学生所属系。题目 2修改 Course 表结构ALTER TABLE Course ADD COLUMN classroom VARCHAR(20);ALTER TABLE用于修改现有表结构 。ADD COLUMN添加新列。classroom VARCHAR(20)新增教室字段类型为可变长度字符串。题目 3为 Score 表添加检查约束ALTER TABLE Score ADD CONSTRAINT chk_grade CHECK (grade 0 AND grade 100);ADD CONSTRAINT添加约束。chk_grade约束名称便于后续管理。CHECK (grade 0 AND grade 100)检查约束确保成绩在0到100之间 。题目 4为 Student 表创建索引CREATE INDEX idx_student_sname ON Student(sname);CREATE INDEX创建索引以提高查询速度。idx_student_sname索引名称。ON Student(sname)在 Student 表的 sname 字段上创建非聚集索引。题目 5创建视图 V_Dept_Avg_ScoreCREATE VIEW V_Dept_Avg_Score AS SELECT d.dept_name, AVG(sc.grade) AS avg_grade FROM Department d JOIN Student s ON d.dept_id s.dept JOIN Score sc ON s.sid sc.sid GROUP BY d.dept_name;CREATE VIEW创建虚拟表视图基于查询结果。AS后接定义视图的 SELECT 语句。JOIN连接 Department、Student、Score 三张表。AVG(sc.grade)计算平均成绩。GROUP BY d.dept_name按系名分组统计。2. DML数据操纵语言语句详解题目 6向 Department 表插入数据INSERT INTO Department (dept_id, dept_name, location) VALUES (D01, 计算机系, A栋), (D02, 数学系, B栋), (D03, 物理系, C栋);INSERT INTO向表中插入新记录。(dept_id, dept_name, location)指定要插入数据的列。VALUES后接要插入的具体值多行数据用逗号分隔。题目 7更新计算机系学生年龄UPDATE Student SET age age 1 WHERE dept D01;UPDATE更新表中现有记录。SET age age 1将年龄字段值加1。WHERE dept D01条件子句仅更新系号为 D01计算机系的学生。题目 8删除不及格成绩记录DELETE FROM Score WHERE grade 60;DELETE FROM从表中删除记录。WHERE grade 60条件子句仅删除成绩小于60的记录。题目 9多表连接查询SELECT s.sname, sc.grade FROM Student s JOIN Score sc ON s.sid sc.sid JOIN Course c ON sc.cid c.cid WHERE c.cname 数据库原理 ORDER BY sc.grade DESC;JOIN内连接关联 Student、Score、Course 表。ON指定连接条件。WHERE c.cname 数据库原理筛选课程名为“数据库原理”的记录。ORDER BY sc.grade DESC按成绩降序排列。题目 10分组统计学生人数SELECT dept, COUNT(*) AS student_count FROM Student GROUP BY dept HAVING COUNT(*) 10;GROUP BY dept按系分组。COUNT(*)统计每组记录数。HAVING COUNT(*) 10过滤分组仅显示学生数大于10的系。3. 复杂查询语句详解题目 11相关子查询SELECT sc.sid, sc.grade FROM Score sc JOIN Course c ON sc.cid c.cid WHERE c.cname 操作系统 AND sc.grade ( SELECT AVG(grade) FROM Score WHERE cid sc.cid );主查询找出选修“操作系统”的学生。子查询SELECT AVG(grade) FROM Score WHERE cid sc.cid计算当前学生所选课程的平均分sc.cid是外部引用。AND sc.grade (...)筛选出成绩低于该课程平均分的学生。题目 12EXISTS 子查询SELECT s.sname FROM Student s WHERE NOT EXISTS ( SELECT cid FROM Score sc1 WHERE sc1.sid S001 EXCEPT SELECT cid FROM Score sc2 WHERE sc2.sid s.sid );NOT EXISTS当子查询结果为空时返回真。子查询使用EXCEPT或MINUS找出 S001 学生选了但当前学生s.sid没选的课程。如果这个差集为空说明当前学生选了 S001 的所有课程。题目 13ANY/ALL 用法SELECT * FROM Student WHERE dept D01 AND age ALL ( SELECT age FROM Student WHERE dept D01 ); ALL (...)要求年龄大于子查询返回的所有值。子查询SELECT age FROM Student WHERE dept D01获取计算机系所有学生的年龄。主查询找出非计算机系 (dept D01) 且年龄大于计算机系所有学生年龄的学生。题目 14CASE WHEN 用法SELECT sid, cid, grade, CASE WHEN grade BETWEEN 90 AND 100 THEN A WHEN grade BETWEEN 80 AND 89 THEN B WHEN grade BETWEEN 70 AND 79 THEN C ELSE D END AS grade_level FROM Score;CASE WHEN ... THEN ... ELSE ... END条件判断根据成绩范围返回等级。BETWEEN ... AND ...范围判断。AS grade_level为计算列指定别名。题目 15窗口函数排名SELECT cid, sid, grade, DENSE_RANK() OVER (PARTITION BY cid ORDER BY grade DESC) AS course_rank FROM Score;DENSE_RANK()窗口函数计算排名允许并列且不跳过名次。OVER (PARTITION BY cid ORDER BY grade DESC)按课程分区在每个课程内按成绩降序排名。4. DCL数据控制语言语句详解题目 16权限授予CREATE USER app_userlocalhost IDENTIFIED BY password; GRANT SELECT ON Student TO app_userlocalhost; GRANT SELECT ON Course TO app_userlocalhost; GRANT INSERT, UPDATE ON Score TO app_userlocalhost;CREATE USER创建新用户。GRANT授予权限。SELECT ON Student授予对 Student 表的查询权限。INSERT, UPDATE ON Score授予对 Score 表的插入和更新权限。题目 17角色管理CREATE ROLE read_only_role; GRANT SELECT ON *.* TO read_only_role; CREATE USER guest_userlocalhost IDENTIFIED BY password; GRANT read_only_role TO guest_userlocalhost;CREATE ROLE创建角色。GRANT SELECT ON *.*授予角色对所有数据库所有表的查询权限。GRANT ... TO ...将角色授予用户。5. 触发器与存储过程详解题目 18BEFORE INSERT 触发器DELIMITER // CREATE TRIGGER trg_check_grade BEFORE INSERT ON Score FOR EACH ROW BEGIN IF NEW.grade 0 THEN SET NEW.grade 0; ELSEIF NEW.grade 100 THEN SET NEW.grade 100; END IF; END // DELIMITER ;DELIMITER //更改语句分隔符避免与存储过程中的分号冲突。CREATE TRIGGER创建触发器。BEFORE INSERT在插入操作之前触发。FOR EACH ROW行级触发器。NEW.grade引用要插入的新行的 grade 值。IF ... THEN ... ELSEIF ... END IF条件判断修正成绩范围。题目 19带参数的存储过程DELIMITER // CREATE PROCEDURE sp_GetStudentByDept(IN dept_name VARCHAR(50)) BEGIN SELECT s.* FROM Student s JOIN Department d ON s.dept d.dept_id WHERE d.dept_name dept_name; END // DELIMITER ;CREATE PROCEDURE创建存储过程。IN dept_name VARCHAR(50)定义输入参数。BEGIN ... END存储过程体。内部为多表连接查询根据系名过滤学生。题目 20带事务的存储过程DELIMITER // CREATE PROCEDURE sp_TransferStudent(IN p_sid VARCHAR(10), IN p_new_dept VARCHAR(10)) BEGIN DECLARE dept_exists INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 事务回滚转系失败 AS result; END; START TRANSACTION; SELECT COUNT(*) INTO dept_exists FROM Department WHERE dept_id p_new_dept; IF dept_exists 0 THEN ROLLBACK; SELECT 新系号不存在转系失败 AS result; ELSE UPDATE Student SET dept p_new_dept WHERE sid p_sid; COMMIT; SELECT 学生转系成功 AS result; END IF; END // DELIMITER ;DECLARE声明局部变量。DECLARE EXIT HANDLER FOR SQLEXCEPTION异常处理发生错误时执行ROLLBACK。START TRANSACTION开始事务。SELECT ... INTO将查询结果赋值给变量。IF ... THEN ... ELSE ... END IF条件判断。UPDATE更新学生系别。COMMIT提交事务确认更改。ROLLBACK回滚事务撤销所有未提交的更改。参考来源mysql 建表语句示例_MySQL Create Table语句和示例Hive建表语句详解及创建表时的分隔符使用MySQL 建表语句详解Hive基础04、Hive建表语句详解oracle和mysql通用建表语句_一个完整的Oracle建表的例子