数据库课堂代码

发表于 2026-06-24|更新于2026-05-28|教程工具

|字数总计:6398|阅读时长:29分钟|阅读量:

第四章

4.1 查看MySQL服务器中的已有数据库。

1
2
3

SHOW DATABASES;

4.2 创建名称为stusys的学生信息数据库，该数据库是本书的重要数据库。

1
2
3

CREATE DATABASE stusys;

4.3 选择stusys为当前数据库。

1
2
3

USE stusys;

4.4 修改数据库stusys的默认字符集和校对规则。

ALTER DATABASE stusys
DEFAULT CHARACTER SET gb2312 
DEFAULT COLLATE gb2312_chinese_ci;

4.5 删除数据库stusys。

1
2
3

DROP DATABASE stusys;

4.6 在学生信息数据库stusys中创建student表。

USE stusys;

CREATE TABLE student 
    (
       sno char(6) NOT NULL PRIMARY KEY,
       sname char(8) NOT NULL,
       ssex char(2) NOT NULL DEFAULT '男',
       sbirthday date NOT NULL,
       speciality char(12) NULL,
       tc tinyint NULL
    );

4.7 在stusys数据库中，使用复制方式创建student1表，表结构取自student表。

USE stusys;

CREATE TABLE student1 like student;

4.8 查看数据库stusys中所有表名。

USE stusys;

SHOW TABLES;

4.9 查看数据库stusys中student表的基本结构。

1
2
3

SHOW COLUMNS FROM student;

4.10 查看数据库stusys中student表的详细结构。

1
2
3

SHOW CREATE TABLE student\G

4.11 在数据库stusys的student1表中增加一列sid，添加到表的第1列，不为空，取值唯一并自动增加。

ALTER TABLE stusys.student1
ADD COLUMN sid int NOT NULL UNIQUE AUTO_INCREMENT FIRST;

DESC stusys.student1;

4.12 将stusys数据库的student1表的列sbirthday修改为sage，将数据类型改为tinyint，可空，默认值为18。

ALTER TABLE stusys.student1
CHANGE COLUMN sbirthday sage tinyint DEFAULT 18;

DESC stusys.student1;

4.13 删除数据库stusys的表student1中的列sid。

ALTER TABLE stusys.student
DROP COLUMN sid;

4.14 在stusys数据库中，将 student1表重命名为student2表。

ALTER TABLE stusys.student1
RENAME TO stusys.student2;

4.15 在stusys数据库中，将 student2表重命名为student3表。

1
2
3

RENAME TABLE stusys.student2 TO stusys.student3;

4.16 删除stusys数据库中的student3表。

1
2
3

DROP TABLE stusys.student3;

4.17 查看存储引擎。

1
2
3

SHOW ENGINES;

第五章

5.1 向student1表插入一条记录(‘196001’,’董明霞’,’女’,’1999-05-02’,’通信’,50)。

INSERT INTO student1 
    VALUES ('196001','董明霞','女','1999-05-02','通信',50);

5.2 向student1表插入一条记录，学号为“196002”，姓名为“李茜”，专业为“通信”，总学分48，性别为“女”，出生日期为“1998-07-25”。

INSERT INTO student1 (sno, sname, speciality, tc, ssex, sbirthday)
    VALUES('196002','李茜','通信',48,'女','1998-07-25');

5.3 向student1表插入一条记录，学号为“196004”，姓名为“周俊文”，性别为“男”、取默认值，出生日期为“1998-03-10”，专业为空值，总学分为52。

INSERT INTO student1 (sno, sname, sbirthday, tc)
    VALUES('196004','周俊文', '1998-03-10', 52);

5.4 向student表插入样本数据，共6条记录，参见附录B。

INSERT INTO student
    VALUES('191001','刘清泉','男','1998-06-21','计算机',52),
    ('191002','张慧玲','女','1999-11-07','计算机',50),
    ('191003','冯涛','男','1999-08-12','计算机',52),
    ('196001','董明霞','女','1999-05-02','通信',50),
    ('196002','李茜','女','1998-07-25','通信',48),
    ('196004','周俊文','男','1998-03-10','通信',52);

5.5 对student1表，重新插入记录(‘196002’,’李茜’,’女’,’1998-07-25’,’通信’,48)。

REPLACE INTO student1 VALUES
    ('196002','李茜','女','1998-07-25','通信',48);

5.6 向student2表插入student表的记录。

INSERT INTO student2
    SELECT * FROM student;

5.7 在student1表中，将学生周俊文的出生日期改为“1999-03-10”。

UPDATE student1
    SET sbirthday='1999-03-10'
    WHERE sname ='周俊文';

5.8 在student1表中，将所有学生的学分增加2分。

UPDATE student1
    SET tc=tc+2;

5.9 在student1表中，删除学号为196004的行。

DELETE FROM student1
    WHERE sno='196004';

5.10 在student1表中，删除所有行。

1
2
3

DELETE FROM student1;

5.11 在student表中，删除所有行。

1
2
3

TRUNCATE student;

第六章

6.1 查询student表中所有学生的学号、姓名和专业。

SELECT sno, sname, speciality
FROM student;

6.2 查询student表中所有列。

SELECT *
FROM student;

或

SELECT sno, sname, ssex, sbirthday, speciality, tc
FROM student;

6.3 查询student表中所有学生的学生的sno、sname、speciality，并将结果中各列的标题分别修改为学号, 姓名, 专业。

SELECT sno AS 学号, sname AS 姓名, speciality AS 专业
FROM student;

6.4 设student1表的表结构和样本数据与student表相同，且已创建和插入数据；在student1表中，列出学号、学分和增加4分后的学分。

SELECT sno AS 学号, tc AS 学分, tc+4 AS 增加4分后的学分
FROM student1;

6.5 查询student表中speciality列，消除结果中的重复行。

SELECT DISTINCT speciality
FROM student;

6.6 查询student表中专业为计算机或性别为女的学生。

SELECT *
FROM student
WHERE speciality='计算机' or ssex='女';

6.7 查询score表成绩为92、95的记录。

SELECT *
FROM score
WHERE grade in (92,95);

6.8 查询student表中不在1998年出生的学生情况。

SELECT *
FROM student
WHERE sbirthday NOT BETWEEN '19980101' AND '19981231';

6.9 查询已选课但未参加考试的学生情况。

SELECT * 
FROM score
WHERE grade IS null;

6.10 查询student表中姓董的学生情况。

SELECT *
FROM student
WHERE sname LIKE '董%';

6.11 查询含有“系统”或“数字”的所有课程名称。

SELECT *
FROM course
WHERE cname REGEXP '系统|数字';

6.12 求学生的总人数。

SELECT COUNT(*) AS 总人数
FROM student;

6.13 查询通信专业学生的总人数。

SELECT COUNT(*) AS 总人数
FROM student
WHERE speciality='通信';

6.14 查询1201课程总分。

SELECT SUM(grade) AS 课程1201总分
FROM score
WHERE cno='1201';

6.15 查询8001课程的最高分、最低分、平均成绩。

SELECT MAX(grade) AS 课程8001最高分, MIN(grade) AS 课程8001最低分, AVG(grade) AS 课程8001平均成绩
FROM score
WHERE cno='8001';

6.16 查询各门课程的最高分、最低分、平均成绩。

SELECT cno AS 课程号, MAX(grade) AS 最高分,MIN(grade) AS 最低分, AVG(grade) AS 平均成绩
FROM score
WHERE NOT grade IS null
GROUP BY cno;

6.17 查询平均成绩在90分以上的学生的学号和平均成绩。

SELECT sno AS 学号, AVG(grade) AS 平均成绩
FROM score
GROUP BY sno
HAVING AVG(grade)>90;

6.18 查询至少有5名学生选修且以8开头的课程号和平均分数。

SELECT cno AS 课程号, AVG (grade) AS 平均分数
FROM score
WHERE cno LIKE '8%'
GROUP BY cno
HAVING COUNT(*)>5;

6.19 将计算机专业的学生按出生时间降序排序。

SELECT *
FROM student
WHERE speciality='计算机'
ORDER BY sbirthday DESC;

6.20 查询成绩表中成绩前3位学生的学号、课程号和成绩。

SELECT sno, cno, grade
FROM score
ORDER BY grade DESC
LIMIT 0, 3;

或

SELECT sno, cno, grade
FROM score
ORDER BY grade DESC
LIMIT 3 OFFSET 0;

6.21 采用交叉连接查询教师和和讲课地点所有可能组合。

SELECT tname, location
FROM teacher, lecture;

或

SELECT tname, location
FROM teacher CROSS JOIN lecture;

6.22 查询每个学生选修课程的情况。

SELECT student.*, score.*
FROM student, score
WHERE student.sno=score.sno;

或

SELECT student.*, score.*
FROM student INNER JOIN score ON student.sno=score.sno;

6.23 查询选修了数据库系统课程且成绩在80分以上的学生情况。

SELECT a.sno, sname, cname, grade
FROM student a, score b, course c
WHERE a.sno=b.sno AND b.cno=c.cno AND cname='数据库系统' AND grade>=80;

或

SELECT a.sno, sname, cname, grade
FROM student a JOIN score b ON a.sno=b.sno JOIN course c ON b.cno=c.cno
WHERE cname='数据库系统' AND grade>=80;

6.24 对例6.22进行自然连接查询。

SELECT *
FROM student NATURAL JOIN score;

6.25 查询选修了“1201”课程的成绩高于学号为“191002”的成绩的学生姓名。

SELECT a.cno, a.sno, a.grade
FROM score a, score b
WHERE a.grade>b.grade AND a.cno='1201' AND b.cno='1201' AND b.sno='191002'
ORDER BY a.grade DESC;

或

SELECT a.cno, a.sno, a.grade
FROM score a JOIN score b ON a.grade>b.grade
WHERE a.cno='1201' AND b.cno='1201' AND b.sno='191002'
ORDER BY a.grade DESC;

6.26 采用左外连接查询教师任课情况。

SELECT tname, cno
FROM teacher LEFT JOIN lecture ON (teacher.tno=lecture.tno);

6.27 采用右外连接查询教师任课情况。

SELECT tno, cname
FROM lecture RIGHT JOIN course ON (course.cno=lecture.cno);

6.28 查询选修了课程号为8001的课程的学生情况。

SELECT * 
FROM student
WHERE sno IN
    (SELECT sno 
     FROM score 
     WHERE cno='8001' 
    );

6.29 查询选修某课程的学生人数多于4人的教师姓名。

SELECT tname AS 教师姓名  
FROM teacher
WHERE tno IN
    (SELECT tno 
     FROM lecture 
     WHERE cno IN    
        (SELECT a.cno 
         FROM course a, score b 
         WHERE a.cno=b.cno
         GROUP BY a.cno 
         HAVING COUNT(a.cno)>4
        )
    );

6.30 查询比所有通信专业学生年龄都小的学生。

SELECT *
FROM student
WHERE sbirthday >ALL
    (SELECT sbirthday 
     FROM student  
     WHERE speciality='通信'
    );

6.31 查询选修1004课程的学生姓名。

SELECT sname AS 姓名
FROM student
WHERE EXISTS 
    (SELECT *
     FROM score
     WHERE score.sno=student.sno AND cno='1004'
    );

6.32 查询性别为女及选修了课程号为4002的学生。

SELECT sno, sname, ssex
FROM student
WHERE ssex='女'
UNION
SELECT a.sno, a.sname, a.ssex 
FROM student a, score b
WHERE a.sno=b.sno AND b.cno='4002';

第七章

7.1 在stusys数据库中创建V_StudentScore视图，包括学号、姓名、性别、专业、课程号、成绩，且专业为计算机。

CREATE OR REPLACE VIEW V_StudentScore
AS
SELECT a.sno, sname, ssex, speciality, cno, grade 
FROM student a, score b
WHERE a.sno=b.sno AND speciality='计算机'
WITH CHECK OPTION;

7.2 在stusys数据库中创建V_StudentCourseScore视图，包括学号、姓名、性别、课程名、成绩，按学号升序排列，且专业为计算机。

CREATE OR REPLACE VIEW V_StudentCourseScore
AS
SELECT a.sno, sname, ssex, speciality, cname, grade
FROM student a, course b, score c
WHERE a.sno=c.sno AND b.cno=c.cno AND speciality='计算机'
ORDER BY a.sno;

7.3 分别查询V_StudentScore视图、V_StudentCourseScore视图。

SELECT *
FROM V_StudentScore;

SELECT *
FROM V_StudentCourseScore;

7.4 查询计算机专业学生的学号、姓名、性别、课程名。

SELECT sno, sname, ssex, cname 
FROM V_StudentCourseScore;

7.5 在stusys数据库中，以student为基表，创建专业为通信的可更新视图V_StudentSpecialityComm。

CREATE OR REPLACE VIEW V_StudentSpecialityComm 
AS
SELECT *
FROM student
WHERE speciality='通信';

7.6 向V_StudentSpecialityComm视图中插入一条记录：(‘196006’,’程超’,’男’,’1998-04-28’,’通信’, 50)。

INSERT INTO V_StudentSpecialityComm
VALUES('196006','程超','男','1998-04-28','通信', 50);

7.7 将V_StudentSpecialityComm视图中学号为196006的学生的总学分增加2分。

UPDATE V_StudentSpecialityComm SET tc=tc+2 
WHERE sno='196006';

7.8 删除V_StudentSpecialityComm视图中学号为196006的记录。

DELETE FROM V_StudentSpecialityComm
WHERE sno='196006';

7.9 将例7.1定义的视图V_StudentScore视图进行修改，取消专业为计算机的要求。

ALTER VIEW V_StudentScore
AS 
SELECT a.sno, sname, ssex, speciality, cno, grade
FROM student a, score b
WHERE a.sno=b.sno
WITH CHECK OPTION;

7.10 修改例7.2创建的视图V_StudentCourseScore，学号以降序排列。

ALTER VIEW V_StudentCourseScore
AS 
SELECT a.sno, sname, ssex, speciality, cname, grade
FROM student a, course b, score c
WHERE a.sno=c.sno AND b.cno=c.cno AND speciality='计算机'
ORDER BY a.sno DESC;

7.11 在stusys数据库中，将视图V_StudentCourseScore删除。

1
2
3

DROP VIEW V_StudentCourseScore;

7.12 在stusys数据库中student表的sname列上，创建一个普通索引I_studentSname。

1
2
3

CREATE INDEX I_studentSname ON student(sname);

7.13 在stusys数据库中course表的cno列上，创建一个索引I_courseCno，要求按课程号cno字段值前2个字符降序排列。

1
2
3

CREATE INDEX I_courseCno ON course(cno(2) DESC);

7.14 在stusys数据库中student表的tc列(降序)和sname列(升序)，创建一个组合索引I_studentTcSname。

1
2
3

CREATE INDEX I_studentTcSname ON student(tc DESC, sname);

7.15 在stusys数据库teacher表的tname列，创建一个唯一性索引I_teacherTname，并按降序排列。

ALTER TABLE teacher
ADD UNIQUE INDEX I_teacherTname(tname DESC);

7.16 在stusys数据库中，创建新表score1表，主键为sno和cno，同时在grade列上创建普通索引。

CREATE TABLE score1
(
    sno char (6) NOT NULL,
    cno char(4) NOT NULL,
    grade tinyint NULL,
    PRIMARY KEY(sno,cno),
    INDEX(grade)
);

7.17 查看例7.16所创建的score1表的索引。

1
2
3

SHOW INDEX FROM score1 \G;

7.18 删除已建索引I_studentTcSname。

1
2
3

DROP INDEX I_studentTcSname ON student;

7.19 删除已建索引I_teacherTname。

ALTER TABLE teacher
DROP INDEX I_teacherTname;

第八章

8.1 在stusys数据库中创建course1表，以列级完整性约束方式定义主键。

CREATE TABLE course1
(
    cno char(4) NOT NULL PRIMARY KEY,
    cname char(16) NOT NULL,
    credit tinyint NULL
);

8.2 在stusys数据库中创建course2表，以表级完整性约束方式定义主键。

CREATE TABLE course2
(
    cno char(4) NOT NULL,
    cname char(16) NOT NULL,
    credit tinyint NULL,
    PRIMARY KEY(cno)
);

8.3 在stusys数据库中创建course3表，以表级完整性约束方式定义主键，并指定主键约束名称。

CREATE TABLE course3
(
    cno char(4) NOT NULL,
    cname char(16) NOT NULL,
    credit tinyint NULL,
    CONSTRAINT PK_course3 PRIMARY KEY(cno)
);

8.4 删除例8.3创建的在course3表上的主键约束。

ALTER TABLE course3
DROP PRIMARY KEY;

8.5 重新在course3表上定义主键约束。

ALTER TABLE course3
ADD CONSTRAINT PK_course3 PRIMARY KEY(cno);

8.6 在stusys数据库中创建course4表，以列级完整性约束方式定义唯一性约束。

CREATE TABLE course4
(
    cno char(4) NOT NULL PRIMARY KEY,
    cname char(16) NOT NULL UNIQUE,
    credit tinyint NULL
);

8.7 在stusys数据库中创建course5表，以表级完整性约束方式定义唯一性约束。

CREATE TABLE course5
(
    cno char(4) NOT NULL PRIMARY KEY,
    cname char(16) NOT NULL,
    credit tinyint NULL,
    CONSTRAINT UK_course5 UNIQUE(cname)
);

8.8 删除例8.7在course5表创建的唯一性约束。

ALTER TABLE course5
DROP INDEX UK_course5;

8.9 重新在course5表上定义唯一性约束。

ALTER TABLE course5
ADD CONSTRAINT UK_course5 UNIQUE(cname);

8.10 创建score1表，在cno列以列级完整性约束方式定义外键。

CREATE TABLE score1
(
   sno char (6) NOT NULL ,
   cno char(4) NOT NULL REFERENCES course1(cno),
   grade tinyint NULL,
   PRIMARY KEY(sno,cno)
);

8.11 创建score2表，在cno列以表级完整性约束方式定义外键，并定义相应的参照动作。

CREATE TABLE score2
(
    sno char (6) NOT NULL ,
    cno char(4) NOT NULL,
    grade tinyint NULL,
    PRIMARY KEY(sno,cno),
    CONSTRAINT FK_score2 FOREIGN KEY(cno) REFERENCES course2(cno)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

8.12 删除例8.11在score2表上定义的外键约束。

ALTER TABLE score2
DROP FOREIGN KEY FK_score2;

8.13 重新在score2表上定义外键约束。

ALTER TABLE score2
ADD CONSTRAINT FK_score2 FOREIGN KEY(cno) REFERENCES course2(cno);

8.14 在stusys数据库中创建表score3，在grade列以列级完整性约束方式定义检查约束。

CREATE TABLE score3
(
    sno char (6) NOT NULL ,
    cno char(4) NOT NULL,
    grade tinyint NULL CHECK(grade>=0 AND grade<=100),
    PRIMARY KEY(sno,cno)
);

8.15 在stusys数据库中创建表score4，在grade列以表级完整性约束方式定义检查约束。

CREATE TABLE score4
(
    sno char (6) NOT NULL ,
    cno char(4) NOT NULL,
    grade tinyint NULL,
    PRIMARY KEY(sno,cno),
    CONSTRAINT CK_score4 CHECK(grade>=0 AND grade<=100)
);

8.16 删除例8.15在score4表上定义的检查约束。

ALTER TABLE score4
DROP CHECK CK_score4;

8.17 重新在score4表上定义检查约束。

ALTER TABLE score4
ADD CONSTRAINT CK_score4 CHECK(grade>=0 AND grade<=100);

第九章

9.1 使用RAND()函数求3个随机值。

1
2
3

SELECT RAND(), RAND(), RAND();

9.2 求3和4的平方根。

1
2
3

SELECT SQRT(3), SQRT(4);

9.3 求7.2和-7.2的绝对值。

1
2
3

SELECT ABS(7.2), ABS(-7.2);

9.4 求小于或等于-3.5或6.8的最大整数，大于或等于-3.5或6.8的最小整数。

1
2
3

SELECT FLOOR(-3.5), FLOOR(6.8), CEILING(-3.5), CEILING(6.8);

9.5 求8.546小数点后2位的值和四舍五入的整数值。

1
2
3

SELECT TRUNCATE(8.546, 2), ROUND(8.546);

9.6 求X的ASCII码值。

1
2
3

SELECT ASCII('X');

9.7 将ASCII码值88、89、90组合成字符串。

1
2
3

SELECT CHAR(88, 89, 90);

9.8 求joyful左侧和右侧开始的3个字符。

1
2
3

SELECT LEFT('joyful', 3), RIGHT('joyful', 3);

9.9 查询字符串”计算机网络”的长度。

1
2
3

SELECT LENGTH('计算机网络');

9.10 将”数据库原理与应用”中的”原理与应用”替换为”技术”。

1
2
3

SELECT REPLACE('数据库原理与应用','原理与应用','技术');

9.11 返回字符串joyful的从第4个字符开始的3个字符。

1
2
3

SELECT SUBSTRING('joyful',4, 3);

9.12 获取当前日期。

1
2
3

SELECT CURDATE(), CURRENT_DATE();

9.13 获取当前时间。

1
2
3

SELECT CURTIME(), CURRENT_TIME();

9.14 获取当前日期和时间。

1
2
3

SELECT NOW();

9.15 查询成绩表score，如果分数列的值大于或等于80分，则输出”良好”，否则输出”一般, 不及格或空值”。

1
2
3

SELECT sno, cno, grade, IF(grade>=80, '良好', '一般, 不及格或空值') level FROM score;

9.16 使用IFNULL()函数做条件判断。

1
2
3

SELECT IFNULL(1/0, 'NULL');

9.17 获取当前数据库的版本号。

1 2	SELECT VERSION();

第十章

10.1 修改MySQL的结束符为“//”。

DELIMITER //

DELIMITER ;

10.2 创建一个不带参数的存储过程P_str，输出“Hello MySQL!”。

DELIMITER $$
CREATE PROCEDURE P_str()
BEGIN
    SELECT 'Hello MySQL!';
END $$
DELIMITER ;

CALL P_str();

10.3 创建一个带参数的存储过程P_maxGrade，查询指定学号学生的最高分。

DELIMITER $$
CREATE PROCEDURE P_maxGrade(IN v_sno CHAR(6))
    /*创建带参数的存储过程, v_sno为输入参数*/
BEGIN
    SELECT MAX(grade) FROM score WHERE sno=v_sno;
END $$
DELIMITER ;

CALL P_maxGrade('191001');

10.4 创建存储过程P_math，如果”高等数学”课程的平均成绩大于80分，则显示”高等数学成绩良好”，否则显示”高等数学成绩一般”。

DELIMITER $$
CREATE PROCEDURE P_math(OUT v_gde char(20))
BEGIN
    DECLARE v_avg decimal(4,2);
    SELECT AVG(grade) INTO v_avg
    FROM student a, course b, score c
    WHERE a.sno=c.sno AND b.cno=c.cno AND cname='高等数学';
    IF v_avg >80 THEN
        SET v_gde='高等数学成绩良好';
    ELSE 
        SET v_gde='高等数学成绩一般'; 
    END IF;
END $$
DELIMITER ;

CALL P_math(@gde);
SELECT @gde;

10.5 创建存储过程P_title，将教师职称转变为职称类型。

DELIMITER $$
CREATE PROCEDURE P_title(IN v_tno char(6), OUT v_type char(10))
BEGIN
    DECLARE v_str char(12);
    SELECT title INTO v_str FROM teacher WHERE tno= v_tno;
    CASE v_str
        WHEN '教授' THEN SET v_type='高级职称';
        WHEN '副教授' THEN SET v_type='高级职称';
        WHEN '讲师' THEN SET v_type='中级职称';
        WHEN '助教' THEN SET v_type='初级职称';  
        ELSE SET v_type:='Nothing';
    END CASE;
END $$
DELIMITER ;

CALL P_title('100006', @type);
SELECT @type;

10.6 创建存储过程P_integerSum，计算1~100的整数和。

DELIMITER $$
CREATE PROCEDURE P_integerSum(OUT v_sum1 int)
BEGIN
    DECLARE v_n int DEFAULT 1;
    DECLARE v_s int DEFAULT 0;
    WHILE v_n<=100 DO
        SET v_s=v_s+v_n;
        SET v_n=v_n+1;
    END WHILE;
    SET v_sum1=v_s;
END $$
DELIMITER ;

CALL P_integerSum(@sum1);
SELECT @sum1;

10.7 创建存储过程P_oddSum，计算1~100的奇数和。

DELIMITER $$
CREATE PROCEDURE P_oddSum(OUT v_sum2 int)
BEGIN
    DECLARE v_n int DEFAULT 1;
    DECLARE v_s int DEFAULT 0;
    REPEAT
        IF MOD(v_n, 2)<>0 THEN 
            SET v_s=v_s+v_n;
        END IF;
        SET v_n=v_n+1;
        UNTIL v_n>100
    END REPEAT;
    SET v_sum2=v_s;
END $$
DELIMITER ;

CALL P_oddSum(@sum2);
SELECT @sum2;

10.8 创建存储过程P_factorial，计算10的阶乘。

DELIMITER $$
CREATE PROCEDURE P_factorial(OUT v_prod int)
BEGIN
    DECLARE v_n int DEFAULT 1;
    DECLARE v_p int DEFAULT 1;
    label:LOOP
        SET v_p:=v_p*v_n;
        SET v_n=v_n+1;  
        IF v_n>10 THEN
            LEAVE label;
        END IF;
    END LOOP label;
    SET v_prod=v_p;
END $$
DELIMITER ;

CALL P_factorial(@prod);
SELECT @prod;

10.9 创建一个存储过程，计算student表中行的数目。

DELIMITER $$
CREATE PROCEDURE P_tablerow(OUT v_rows int)
BEGIN
    DECLARE v_sno char(6);
    DECLARE found boolean DEFAULT TRUE;
    DECLARE CUR_student CURSOR FOR SELECT sno FROM student;
    DECLARE CONTINUE HANDLER FOR NOT found
    SET found=FALSE;
    SET v_rows=0;
    OPEN CUR_student;
    FETCH CUR_student into v_sno;
    WHILE found DO 
        SET v_rows=v_rows+1;
        FETCH CUR_student INTO v_sno;
    END WHILE;
    CLOSE CUR_student;
END $$
DELIMITER ;

CALL P_tablerow(@rows);
SELECT @rows;

10.10 创建向学生表插入一条记录的过程P_insertStudent，并调用该过程。

DELIMITER $$
CREATE PROCEDURE P_insertStudent()
BEGIN
    INSERT INTO student VALUES('191005', '王燕', '女', '1999-04-17', NULL, NULL);   
    SELECT * FROM student WHERE sno='191005';
END $$
DELIMITER ;

CALL P_insertStudent();

10.11 创建修改学生专业和总学分的过程P_updateSpecGrade，并调用该过程。

DELIMITER $$
CREATE PROCEDURE P_updateSpecGrade(IN v_sno char(6), IN v_speciality char(12), IN v_tc int)
BEGIN
    UPDATE student SET speciality=v_speciality, tc=v_tc WHERE sno=v_sno;  
    SELECT * FROM student WHERE sno='191005';
END $$
DELIMITER ;

CALL P_updateSpecGrade('191005', '计算机', '50');

10.12 创建删除学生记录的过程P_deleteStudent，并调用该过程。

DELIMITER $$
CREATE PROCEDURE P_deleteStudent(IN v_sno char(6), OUT v_msg char(8))
BEGIN
    DELETE FROM student WHERE sno=v_sno;
    SET v_msg='删除成功';  
END $$
DELIMITER ;

CALL P_deleteStudent('191005', @msg);
SELECT @msg;

10.13 删除存储过程P_insertStudent。

1
2
3

DROP PROCEDURE P_insertStudent;

10.14 创建一个存储函数F_courseName，由课程号查课程名。

DELIMITER $$
CREATE FUNCTION F_courseName(v_cno char(4))
    RETURNS char(12)
    DETERMINISTIC
BEGIN
    RETURN(SELECT cname FROM course WHERE cno=v_cno);
END $$
DELIMITER ;

10.15 调用存储函数F_courseName。

1
2
3

SELECT F_courseName('1201');

10.16 删除存储函数F_courseName。

1
2
3

DROP FUNCTION IF EXISTS F_courseName;

第十一章

11.1 在stusys 数据库的score表创建触发器T_insertScoreRecord，当向score表插入一条记录时，显示“正在插入记录”。

CREATE TRIGGER T_insertScoreRecord AFTER INSERT
    ON score FOR EACH ROW SET @str='正在插入记录';

INSERT INTO score
    VALUES('196001','1004 ',91);

SELECT @str;

11.2 删除触发器T_insertScoreRecord。

DROP TRIGGER T_insertScoreRecord;

11.3 在stusys 数据库的student表创建触发器T_inserStudentRecord，当向student表插入一条记录时，显示插入记录的学生的姓名。

CREATE TRIGGER T_inserStudentRecord AFTER INSERT
    ON student FOR EACH ROW SET @str1=NEW.sname;

INSERT INTO student
    VALUES('196007','刘莉 ','女','1999-01-14','通信',50);

SELECT @str1;

11.4 在stusys数据库的course表创建一个触发器T_updateCourseScore，当更新表course中某门课程的课程号时，同时更新score表中所有相应的课程号。

DELIMITER $$
CREATE TRIGGER T_updateCourseScore AFTER UPDATE
    ON course FOR EACH ROW
BEGIN
    UPDATE score SET cno=NEW.cno WHERE cno=OLD.cno;
END $$
DELIMITER ;

UPDATE course SET cno='4017' WHERE cno='4002';
SELECT * FROM score WHERE cno='4017';

11.5 在stusys 数据库的student表创建一个触发器T_deleteStudentScore，当删除表student中某个学生的记录时，同时将score表中与该学生有关的数据全部删除。

DELIMITER $$
CREATE TRIGGER T_deleteStudentScore AFTER DELETE
    ON student FOR EACH ROW
BEGIN
    DELETE FROM score WHERE sno=OLD.sno;
END $$
DELIMITER ;


DELETE FROM student WHERE sno='191003';
SELECT * FROM score WHERE sno='191003';

11.6 创建现在立即执行的事件E_direct，执行时创建一个表tb。

CREATE EVENT E_direct
    ON SCHEDULE AT NOW()
    DO
    CREATE TABLE tb(timeline timestamp);

SHOW TABLES;
SELECT * FROM tb;

11.7 创建事件E_insertTb，每2秒钟插入一条记录到表tb。

CREATE EVENT E_insertTb
    ON SCHEDULE EVERY 2 SECOND
    DO
    INSERT INTO tb VALUES(current_timestamp);

SELECT * FROM tb;

11.8 创建事件E_startDays，从第2天起，每天清空表tb，在2021年12月31日结束。

DELIMITER $$
CREATE EVENT E_startDays
    ON SCHEDULE EVERY 1 DAY
    STARTS CURDATE()+INTERVAL 1 DAY
    ENDS '2021-12-31'
    DO
    BEGIN
        TRUNCATE TABLE tb;
    END $$
DELIMITER ;

11.9 将事件E_startDays更名为E_firstDays。

ALTER EVENT E_startDays
    RENAME TO E_firstDays;

11.10 删除事件E_firstDays。

1
2
3

DROP EVENT E_firstDays;

第十二章

12.1 创建用户lee，口令为’1234’；创建用户zhang，口令为’5678’；创建用户sun，口令为’test’。

CREATE USER 'lee'@'localhost' IDENTIFIED BY '1234',
    'zhang'@'localhost' IDENTIFIED BY '5678',
    'sun'@'localhost' IDENTIFIED BY 'test';

12.2 删除用户zhang。

1
2
3

DROP USER 'zhang'@'localhost';

12.3 将用户sun的名字修改为qian。

1
2
3

RENAME USER 'sun'@'localhost' TO 'qian'@'localhost';

12.4 将用户qian的口令修改为’abc’。

1
2
3

SET PASSWORD FOR 'qian'@'localhost'='abc';

12.5 授予用户lee在数据库stusys的student表上对学号列和姓名列的SELECT权限。

GRANT SELECT(sno, sname)
    ON stusys.student
    TO 'lee'@'localhost';

12.6 先创建新用户hong和liu，然后授予它们在数据库stusys的student表上的SELECT和INSERT权限。

CREATE USER 'hong'@'localhost' IDENTIFIED BY '123',
    'liu'@'localhost' IDENTIFIED BY '456';

GRANT SELECT, INSERT 
    ON stusys.student
    TO 'hong'@'localhost', 'liu'@'localhost';

12.7 授予用户qian对数据库stusys执行所有数据库操作的权限。

GRANT ALL
    ON stusys.*
    TO 'qian'@'localhost';

12.8 授予已存在用户liang对所有数据库中所有表的CREATE和DROP的权限。

GRANT CREATE, DROP
    ON *.*
    TO 'liang'@'localhost';

12.9 授予已存在用户zhou对所有数据库中所有表的SELECT和UPDATE的权限。

GRANT SELECT, UPDATE
     ON *.*
     TO 'zhou'@'localhost';

12.10 授予已存在用户ben创建新用户的权限。

GRANT CREATE USER
    ON *.*
    TO 'ben'@'localhost';

12.11 查询以上用户对所有数据库的权限。

SELECT Host, User, Select_priv, Update_priv, Create_priv, Drop_priv, Create_user_priv
    FROM mysql.user;

12.12 授予已存在用户qiao在数据库stusys的student表上的SELECT和UPDATE权限，并允许将自身的权限授予其他用户。

GRANT SELECT, UPDATE
    ON stusys.student
    TO 'qiao'@'localhost'
    WITH GRANT OPTION;

12.13 收回用户qiao在数据库stusys的student表上的UPDATE权限，

REVOKE UPDATE
    ON stusys.student
    FROM 'qiao'@'localhost';

12.14 查询以上用户对student表的权限。

SELECT Host, Db, User, Table_name, Table_priv, Column_priv 
    FROM mysql.tables_priv;

第十三章

13.1 备份stusys数据库中的course表中数据，要求字段值如果是字符就用双引号标注，字段值之间用逗号隔开，每行以问号为结束标志。

SELECT * FROM course 
    INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/course.txt'
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '?';

13.2 使用mysqldump备份stusys数据库的course表到D盘mysqlbak目录下。

1
2
3

mysqldump -u root -p stusys course>D:\mysqlbak\course.sql

13.3 备份stusys数据库到D盘mysqlbak目录下。

1
2
3

mysqldump -u root -p stusys>D:\mysqlbak\stusys.sql

13.4 备份MySQL服务器上的所有数据库到D盘mysqlbak目录下。

1
2
3

mysqldump -u root -p --all-databases>D:\mysqlbak\alldata.sql

13.5 删除stusys数据库中的course表中数据后，将例13.1备份文件course.txt导入到空表course中。

DELETE FROM course;

SELECT * FROM course;

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/course.txt'
    INTO TABLE course
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '?';

SELECT * FROM course;

13.6 删除stusys数据库中各个表后，用例13.3备份文件stusys.sql将其恢复。

mysql -u root -p stusys<D:\mysqlbak\stusys.sql

第十四章

14.1 创建数据库test和表usr，在表中插入记录后，开始第1个事务，更新表的记录，提交第1个事务；开始第2个事务，更新表的记录，回滚第2个事务。

（1）查看MySQL隔离级别(隔离级别下一节介绍)

SHOW VARIABLES LIKE 'transaction_isolation';


（2）创建数据库test和表usr，在表中插入记录

CREATE DATABASE test;
USE test;

CREATE TABLE usr
    (
         usrid int,
         name varchar(12)
    );

INSERT INTO usr 
    VALUES(1,'David'),
    (2,'Mary'),
    (3,'ben'),
    (4,'Iris');

SELECT * FROM usr;

（3）开始第1个事务，更新表的记录，提交第1个事务

BEGIN WORK;

UPDATE usr SET name='Lee' WHERE usrid=1;

COMMIT;

SELECT * FROM usr;


（4）开始第2个事务，更新表的记录，回滚第2个事务

START TRANSACTION;

UPDATE usr SET name='Qian' WHERE usrid=1;

SELECT * FROM usr;

ROLLBACK;

SELECT * FROM usr;

数据库课堂代码

https://lxttz.github.io/posts/shujukuketang.html

作者

阿洛博客世界🥝

发布于

2026-06-24

更新于

2026-05-28

许可协议

CC BY-NC-SA 4.0

数据库