SQL 语句记录

一、查询

单体查询: select a from b
多重查询: select a,b from c
全部查询: select * from b
简单的单体转换: a, b AS c

1.查询全体学生的学号和姓名

1
2
select sno,sname
from student

2.查询全体学生的详细信息

1
SELECT * FROM student;

3.查询全部学生的 “ 姓名 ” 及其 “ 出生年 ” 两列

1
SELECT Sname, (2024 - Sage) AS BirthYear FROM Student;
二、消除取值重复(distinct)

消除取值重复: select distinct a from b

4.查询选修了课程的学生学号

1
SELECT DISTINCT Sno FROM SC;
三、满足条件(WHERE)

指定条件: select a from b where c …

大小比较: select a,b from c where d </> …

确定范围: select a,b,c from d where c BETWEEN … AND …

in 和 not in 确定集合: select a,b from c where d IN (‘m’, ‘n’)

字符匹配: select … from … where … (not) like …

5.查询计算机系（ IS ）全体学生名单

1
SELECT Sname FROM Student WHERE Sdept = 'IS';

6.查询全体 20 岁以下的学生姓名和年龄

1
SELECT Sname, Sage FROM Student WHERE Sage < 20;

7.查询所有在 20 到 23 岁（含 20 和 23 ）的学生姓名、系别和年龄

1
SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;

8.查询 IS 系和 CS 系的全体学生姓名和性别

1
SELECT Sname, Ssex FROM Student WHERE Sdept IN ('IS', 'CS');

9.查询既不属于 IS 系，也不属于 MA 系的学生姓名和年龄

1
SELECT Sname, Sage FROM Student WHERE Sdept NOT IN ('IS', 'MA');

10.查询所有姓李的学生姓名和性别

1
SELECT Sname, Ssex FROM Student WHERE Sname LIKE '李%';

11.查询所有 “2002” 年入学的学生学号、姓名和系别

1
SELECT Sno, Sname, Sdept FROM Student WHERE Sno LIKE '2002%';

12.查询所有不姓 “ 刘 ” 的学生信息

1
SELECT * FROM Student WHERE Sname NOT LIKE '刘%';

13.查询名称含有 “ 数据 ” 的课程号、课程名及学分

1
SELECT Cno, Cname, Ccredit FROM Course WHERE Cname LIKE '%数据%';

14.查询没有先修课的课程号和课程名。

1
SELECT Cno, Cname FROM Course WHERE Cpno IS NULL;

15.查询所有有成绩的学生学号、课程号及成绩

1
SELECT Sno, Cno, Grade FROM SC WHERE Grade IS NOT NULL;
四、查询结果排序(order by)

select a,b from c where d … order by b …

16.查询选修了 3 号课程的学生学号和成绩，结果按成绩降序排列。

1
SELECT Sno, Grade FROM SC WHERE Cno = '3' ORDER BY Grade DESC;
五、聚集函数(count、sum、avg、max、min)

聚集函数有count 、 sum 、 avg 、 max 、 min
count（列1），功能：计算列1的个数，行数。如果加distinct ，计算列1的不同值的个数。
sum(列1)，功能是对列1的值进行求和。累加。条件是列1的属性为可累加的。
avg（列1），对列1的值进行求平均数。
Max，min，avg和sum具有共同的条件，列可计算。

17.查询学生总数

1
SELECT COUNT(*) AS TotalStudents FROM Student;

18.查询所有课程的总学分

1
SELECT SUM(Ccredit) AS TotalCredits FROM Course;

19.查询全体学生平均年龄

1
SELECT AVG(Sage) AS AverageAge FROM Student;

20.查询 1 号课程的最高分

1
SELECT MAX(Grade) AS HighestScore FROM SC WHERE Cno = '1';
六、分组统计(group by)

21.查询男女学生各有多少人

1
SELECT Ssex, COUNT(*) AS StudentCount FROM Student GROUP BY Ssex;

22.查询每个课程的课程号和平均分。

1
SELECT Cno, AVG(Grade) AS AverageGrade FROM SC GROUP BY Cno;

23.查询选修了3门课程以上（含3门）的学生学号和选修课程数。

1
SELECT Sno, COUNT(Cno) AS CourseCount FROM SC GROUP BY Sno HAVING COUNT(Cno) >= 3;

24.查询选修了2门课程以上（含2门，但不含1号课程）,学生学号和选修课程数。

1
SELECT Sno, COUNT(Cno) AS CourseCount FROM SC WHERE Cno != '1' GROUP BY Sno HAVING COUNT(Cno) >= 2;

25.查询不及格门数2门以上的学生学号。

1
2
3
4
5
SELECT Sno
FROM SC
WHERE Grade < 60
GROUP BY Sno
HAVING COUNT(*) >= 2;

26.查询有2名以上（含2名）学生选修了的课程号和选修人数。

1
SELECT Cno, COUNT(Sno) AS StudentCount FROM SC GROUP BY Cno HAVING COUNT(Sno) >= 2;
七、多表查询

1.查询计算机工程系女学生的学生学号、姓名及考试成绩。

1
2
3
4
SELECT S.Sno, S.Sname, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
WHERE S.Sdept = 'CS' AND S.Ssex = '女';

2.查询“李勇”同学所选课程的成绩。

1
2
3
4
SELECT SC.Grade
FROM SC
JOIN Student ON SC.Sno = Student.Sno
WHERE Student.Sname = '李勇';

3.查询“李新”老师所授课程的课程名称

1
2
3
4
SELECT C.Cname
FROM Course C
JOIN Teacher T ON C.Cno = T.Cno
WHERE T.Tname = '李新';

4.查询女教师所授课程的课程号及课程名称

1
2
3
4
5
SELECT C.Cno, C.Cname
FROM Course C
JOIN Teaching T ON C.Cno = T.Cno
JOIN Teacher TCH ON T.Tno = TCH.Tno
WHERE TCH.Tsex = '女';

5.查询至少选修一门课程的女学生姓名

1
2
3
SELECT S.Sname
FROM Student S
WHERE S.Ssex = '女' AND S.Sno IN (SELECT SC.Sno FROM SC);

6.查询姓“李”的学生所学的课程名称

1
2
3
4
5
SELECT DISTINCT C.Cname
FROM Course C
JOIN SC ON C.Cno = SC.Cno
JOIN Student S ON SC.Sno = S.Sno
WHERE S.Sname LIKE '李%';

7.查询选修“数学”课程且成绩在80～90分之间的学生学号及成绩

1
2
3
4
SELECT SC.Sno, SC.Grade
FROM SC
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数学' AND SC.Grade BETWEEN 80 AND 90;

8.查询课程成绩及格的男同学的学生信息及课程号与成绩

1
2
3
4
SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, SC.Cno, SC.Grade
FROM Student S
JOIN SC ON S.Sno = SC.Sno
WHERE S.Ssex = '男' AND SC.Grade >= 60;

9.查询选修“c02”课程的学生的平均年龄

1
2
3
4
SELECT AVG(S.Sage) AS AverageAge
FROM Student S
JOIN SC ON S.Sno = SC.Sno
WHERE SC.Cno = '2';

10.查询学习课程名为“数学”的学生学号和姓名

1
2
3
4
5
SELECT S.Sno, S.Sname
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE C.Cname = '数学';

11.查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩

1
2
3
4
SELECT T.Cno, SC.Sno, SC.Grade
FROM Teacher T
JOIN SC ON T.Cno = SC.Cno
WHERE T.Tname = '钱军';

12.查询在第3学期所开课程的课程名称及成绩

1
2
3
4
5
SELECT C.Cname, SC.Grade
FROM Course C
JOIN SC ON C.Cno = SC.Cno
JOIN Semester S ON C.Cno = S.Cno
WHERE S.Semester = 3;

13.查询“c02”号课程90分以下（不含90）的学生信息

1
2
3
4
SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept
FROM Student S
JOIN SC ON S.Sno = SC.Sno
WHERE SC.Cno = '2' AND SC.Grade < 90;

14.查询信息工程系成绩在90分以下（不含90）的学生姓名、性别和课程名称。

1
2
3
4
5
SELECT S.Sname, S.Ssex, C.Cname
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE S.Sdept = 'IS' AND SC.Grade < 90;

15.查询同时选修了“c03”和“c02”课程的学生姓名和成绩

1
2
3
4
5
6
7
8
9
SELECT S.Sname, SC1.Grade AS Grade_c02, SC2.Grade AS Grade_c03
FROM Student S
JOIN SC SC1 ON S.Sno = SC1.Sno AND SC1.Cno = '2'
JOIN SC SC2 ON S.Sno = SC2.Sno AND SC2.Cno = '3'
WHERE S.Sno IN (
    SELECT Sno FROM SC WHERE Cno = '2'
    INTERSECT
    SELECT Sno FROM SC WHERE Cno = '3'
);
八、嵌套查询

1.查询计算机系(CS)女学生的学生学号、姓名及考试成绩。

1
2
3
4
SELECT Student.Sno, Student.Sname, SC.Grade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE Student.Sdept = 'CS' AND Student.Ssex = '女';

2.查询“李勇”同学所选课程的成绩。

1
2
3
4
5
SELECT SC.Sno, Course.Cno, Course.Cname, SC.Grade
FROM SC
JOIN Course ON SC.Cno = Course.Cno
JOIN Student ON SC.Sno = Student.Sno
WHERE Student.Sname = '李勇';

3.查询女教师所授课程的课程号及课程名称。

1
2
3
4
SELECT Course.Cno, Course.Cname
FROM Course
JOIN Teacher ON Course.Tno = Teacher.Tno  -- 假设Teacher表有一个Tno字段，Course表通过Tno字段关联教师
WHERE Teacher.Ssex = '女';  -- 假设Teacher表有一个Ssex字段表示性别

4.查询姓“王”的学生所学的课程名称。

1
2
3
4
5
SELECT DISTINCT Course.Cname
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Student.Sname LIKE '王%';

5.查询选修“数据库”课程且成绩在80～90分之间的学生学号及成绩。

1
2
3
4
SELECT SC.Sno, SC.Grade
FROM SC
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数据库' AND SC.Grade BETWEEN 80 AND 90;

6.查询选修“C04”课程的学生的平均年龄。

1
2
3
4
SELECT AVG(Student.Sage) AS AverageAge
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE SC.Cno = '4';

7.查询学习课程名为“数学”的学生学号和姓名。

1
2
3
4
5
SELECT Student.Sno, Student.Sname
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数学';

8.查询在第3学期所开课程的课程名称及成绩。

1
2
3
4
SELECT Course.Cname, SC.Grade
FROM Course
JOIN SC ON Course.Cno = SC.Cno
WHERE Course.Semester = 3;  -- 假设Course表有一个Semester字段表示学期

9.查询与“李勇”同一个系的同学姓名。

1
2
3
4
5
6
7
SELECT Sname
FROM Student
WHERE Sdept = (
    SELECT Sdept
    FROM Student
    WHERE Sname = '李勇'
) AND Sname <> '李勇';

10.查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。

1
2
3
4
5
6
7
8
9
10
11
SELECT Sname
FROM Student
WHERE Sno > (
    SELECT Sno
    FROM Student
    WHERE Sname = '刘晨'
) AND Sage < (
    SELECT Sage
    FROM Student
    WHERE Sname = '刘晨'
);

11.查询出生日期大于所有女同学出生日期的男同学的姓名及系别。

1
2
3
4
5
6
7
SELECT Sname, Sdept
FROM Student
WHERE Ssex = '男' AND Sage > ALL (
    SELECT Sage
    FROM Student
    WHERE Ssex = '女'
);

12.查询成绩比该课程平均成绩高的学生的学号及成绩。

1
2
3
4
5
6
7
SELECT SC.Sno, SC.Grade
FROM SC
WHERE SC.Grade > (
    SELECT AVG(Grade)
    FROM SC AS SC2
    WHERE SC2.Cno = SC.Cno
);

13.查询没有选修“C02”课程的学生学号及姓名。

1
2
3
4
5
6
7
SELECT Sno, Sname
FROM Student
WHERE Sno NOT IN (
    SELECT SC.Sno
    FROM SC
    WHERE SC.Cno = '2'
);

14.查询选修了“数据库”课程的学生学号、姓名及系别。

1
2
3
4
5
SELECT Student.Sno, Student.Sname, Student.Sdept
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数据库';

15.查询“C02”号课程不及格的学生信息。

1
2
3
4
SELECT Student.Sno, Student.Sname, Student.Sdept
FROM Student
JOIN SC ON Student.Sno = SC.Sno
WHERE SC.Cno = '2' AND SC.Grade < 60;
九、数据更新

1.向Student表中插入记录(“20050203”，”张静”，”19”，”女”，”CS”)。

1
INSERT INTO Student VALUES ('20050203', '张静', '女', 19, 'CS');

2.插入学号为“20050302”、姓名为“李四”的学生信息。

1
INSERT INTO Student VALUES ('20050302', '李四', '男', 20, 'CS');

3.把计算机工程系的学生记录保存到表TS中(2种情况，TS表已存在和不存在)。

1
2
3
# 1.TS表已存在
INSERT INTO TS
SELECT * FROM Student WHERE Sdept = 'CS';
1
2
3
4
5
6
7
8
# 2.TS表不存在
-- 创建TS表，假设其结构与Student表相同
CREATE TABLE TS AS
SELECT * FROM Student WHERE 1=0; -- 使用一个不返回任何行的查询来创建表结构

-- 插入数据
INSERT INTO TS
SELECT * FROM Student WHERE Sdept = 'CS';

4.将学号为“20050202”的学生姓名改为“张华”，系别改为“CS”，专业改为“多媒体技术”。

1
2
3
4
5
6
-- 假设“专业”列名为Smajor
ALTER TABLE Student ADD Smajor VARCHAR(20);

UPDATE Student
SET Sname = '张华', Sdept = 'CS', Smajor = '多媒体技术'
WHERE Sno = '20050202';

5.将“李勇”同学的专业改为“计算机信息管理”。

1
2
3
4
5
6
-- 添加专业字段Smajor
ALTER TABLE Student ADD Smajor CHAR(20);

UPDATE Student
SET Smajor = '计算机信息管理'
WHERE Sno = '200215121';

6.将“20050201”学生选修“C03”号课程的成绩改为该课的平均成绩。

1
2
3
4
5
6
7
8
9
10
11
-- 声明一个变量来存储平均成绩
SET @avg_grade = (
    SELECT AVG(Grade)
    FROM SC
    WHERE Cno = '3'
);

-- 使用变量更新成绩
UPDATE SC
SET Grade = @avg_grade
WHERE Sno = '20050201' AND Cno = '3';

7.把成绩低于总平均成绩的女同学的成绩提高5%。

1
2
3
4
5
6
7
8
-- 计算总平均成绩
SET @total_avg_grade = (SELECT AVG(Grade) FROM SC);

-- 更新成绩低于总平均成绩的女同学的成绩
UPDATE SC
SET Grade = Grade * 1.05
WHERE Grade < @total_avg_grade
AND Sno IN (SELECT Sno FROM Student WHERE Ssex = '女');

8.把选修了“数学”课程而成绩小于90（不含）的学生的成绩全改为空值(NULL)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 第一种
UPDATE SC
SET Grade = NULL
WHERE Cno IN (
    SELECT Cno
    FROM Course
    WHERE Cname = '数字'
) AND Grade < 90;

# 第二种
-- 首先，找到“数学”课程的课程号
SET @math_cno = (
    SELECT Cno FROM Course WHERE Cname = '数学'
);

-- 然后，更新SC表中的成绩
UPDATE SC
SET Grade = NULL
WHERE Cno = @math_cno AND Grade < 90;

9.删除学号为“20050302”的学生记录。

1
DELETE FROM Student WHERE Sno = '20050302';

10.删除SC表中尚无成绩的选课记录。

1
DELETE FROM SC WHERE Grade IS NULL;

11.删除“计算机工程系”所有学生的选课记录。

1
2
3
4
DELETE FROM SC
WHERE Sno IN (
    SELECT Sno FROM Student WHERE Sdept = '计算机工程系'
);

12.把“刘晨”同学的成绩全部删除。

1
2
3
4
5
6
7
-- 首先，找到“刘晨”的学号
SET @sno = (
    SELECT Sno FROM Student WHERE Sname = '刘晨'
);

-- 然后，删除SC表中“刘晨”的所有成绩记录
DELETE FROM SC WHERE Sno = @sno;

注：检查：

如果您希望在删除之前检查“刘晨”的所有成绩记录，可以使用SELECT语句进行查询：

1
2
3
4
SELECT * FROM SC
WHERE Sno = (
 SELECT Sno FROM Student WHERE Sname = '刘晨'
);
零、元数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
drop database if EXISTS sc;

/*  1.创建数据库 */
create database sc;

/*  使用切换数据库sc */
use sc;


/*   2.创建数据表    */

/*1)创建表Student*/
create table Student   
(   
  Sno char(9) primary key,  /*Sno是主码 列级完整性约束条件 实体完整性*/  
  Sname char(10) unique,    /*Sname取唯一值，不重复*/  
  Ssex char(2),   
  Sage smallint,        /*类型为smallint*/  
  Sdept char(20)        /*所在系*/  
);              /*；要加*/  

/*2)创建表Course*/

create table Course   
(   
  Cno char(4) primary key,  /*列级完整性约束条件，Cno是主码*/  
  Cname char(20),   
  Cpno char(4),         /*Cpno的含义是先行课*/  
  Ccredit smallint,   
  foreign key (Cpno) references Course(Cno)   
  /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/    
);   

/*  3)创建表SC   */

create table SC   
(   
  Sno char(9),   
  Cno char(4),   
  Grade smallint,   
  primary key (Sno,Cno),   
    /*主码有两个属性构成，必须作为表级完整性进行定义*/  
  foreign key (Sno) references Student(Sno),   
    /*表级完整性约束条件，Sno是外码，被参照表是Student*/  
  foreign key (Cno) references Course(Cno)  
    /*表级完整性约束条件,Cno是外码,被参照表示Course*/  
);   

/***    3.插入数据      ***/

/*---1)插入Student数据     */

insert into Student values('200215121','李勇','男',20,'CS');    /*向表Student中插入一个元组*/  
insert into Student values('200215122','刘晨','女',19,'IS');    /*向表Student中插入一个元组*/  
insert into Student values('200215123','王敏','女',18,'MA');    /*向表Student中插入一个元组*/  
insert into Student values('200215125','张立','男',19,'IS');    /*向表Student中插入一个元组*/  

/* 2)插入Course数据     */
  
/*注意顺序（先行课问题）    注意添加一个空元素用 null   */
insert into Course values('2','数学',null,2);/*向表Course中插入一个元组*/  
insert into Course values('6','数据处理',null,2);/*向表Course中插入一个元组*/  
insert into Course values('4','操作系统','6',3);/*向表Course中插入一个元组*/  
insert into Course values('7','PASCAL语言','6',4);/*向表Course中插入一个元组*/  
insert into Course values('5','数据结构','7',4);/*向表Course中插入一个元组*/  
insert into Course values('1','数据库','5',4);/*向表Course中插入一个元组*/  
insert into Course values('3','信息系统','1',4);/*向表Course中插入一个元组*/  


/*---3)插入SC数据     */  
insert into SC values('200215121','1',92);/*向表SC中插入一个元组*/  
insert into SC values('200215121','2',85);/*向表SC中插入一个元组*/  
insert into SC values('200215121','3',88);/*向表SC中插入一个元组*/  
insert into SC values('200215122','2',90);/*向表SC中插入一个元组*/  
insert into SC values('200215122','3',80);/*向表SC中插入一个元组*/

用SQL语句创建学校信息管理数据库StuDB

学校信息管理数据库StuDB中有以下8个关系，根据关系完成本题SQL语句的编写：

学生信息表Student（学号Sno，学生姓名SName，学生性别SSex，学生年龄Sage，所在班级编号ClassNo，邮箱Email，家庭住址Address）

班级信息表Class（班级编号ClassNo，班级名称ClassName，班级人数Num，所属专业编号Pno）

课程信息表Course（课程编号Cno，课程名称CName，学分Credits）

选课记录表SC（课程编号Cno，学号Sno，分数Grade，选课学期Semester）

学院信息表Department（院系编号DeptNo，院系名称DeptName）

专业信息表Professional（专业编号Pno，专业名称PName，所属院系编号DeptNo）

教师信息表Teacher（教师工号Tno，教师姓名Tname，教师性别TSex，教师年龄Tage，职称Title，联系电话Phone，所属院系编号DeptNo）

教师授课信息表TC（教师工号Tno，课程编号Cno，授课课时数Cnum）

首先，我们将创建学校信息管理数据库StuDB，然后根据您提供的8个关系，我们将编写SQL语句来创建相应的表。

创建数据库StuDB
1
2
CREATE DATABASE StuDB;
USE StuDB;
创建学生信息表Student
1
2
3
4
5
6
7
8
9
CREATE TABLE Student (
    Sno VARCHAR(20) PRIMARY KEY,
    SName VARCHAR(100) NOT NULL,
    SSex CHAR(1) CHECK (SSex IN ('M', 'F')),
    Sage INT CHECK (Sage > 0),
    ClassNo VARCHAR(20),
    Email VARCHAR(100),
    Address VARCHAR(255)
);
创建班级信息表Class
1
2
3
4
5
6
7
CREATE TABLE Class (
    ClassNo VARCHAR(20) PRIMARY KEY,
    ClassName VARCHAR(100) NOT NULL,
    Num INT CHECK (Num > 0),
    Pno VARCHAR(20),
    FOREIGN KEY (Pno) REFERENCES Professional(Pno)
);
创建课程信息表Course
1
2
3
4
5
CREATE TABLE Course (
    Cno VARCHAR(20) PRIMARY KEY,
    CName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0)
);
创建选课记录表SC
1
2
3
4
5
6
7
8
9
CREATE TABLE SC (
    Cno VARCHAR(20),
    Sno VARCHAR(20),
    Grade DECIMAL(5, 2) CHECK (Grade BETWEEN 0 AND 100),
    Semester VARCHAR(20),
    PRIMARY KEY (Cno, Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno)
);
创建学院信息表Department
1
2
3
4
CREATE TABLE Department (
    DeptNo VARCHAR(20) PRIMARY KEY,
    DeptName VARCHAR(100) NOT NULL
);
创建专业信息表Professional
1
2
3
4
5
6
CREATE TABLE Professional (
    Pno VARCHAR(20) PRIMARY KEY,
    PName VARCHAR(100) NOT NULL,
    DeptNo VARCHAR(20),
    FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo)
);
创建教师信息表Teacher
1
2
3
4
5
6
7
8
9
10
CREATE TABLE Teacher (
    Tno VARCHAR(20) PRIMARY KEY,
    Tname VARCHAR(100) NOT NULL,
    TSex CHAR(1) CHECK (TSex IN ('M', 'F')),
    Tage INT CHECK (Tage > 0),
    Title VARCHAR(100),
    Phone VARCHAR(20),
    DeptNo VARCHAR(20),
    FOREIGN KEY (DeptNo) REFERENCES Department(DeptNo)
);
创建教师授课信息表TC
1
2
3
4
5
6
7
8
CREATE TABLE TC (
    Tno VARCHAR(20),
    Cno VARCHAR(20),
    Cnum INT CHECK (Cnum > 0),
    PRIMARY KEY (Tno, Cno),
    FOREIGN KEY (Tno) REFERENCES Teacher(Tno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

（1）创建选课记录表SC，要求：根据实际情况自定义各个属性的数据类型和列宽，但其中课程编号Cno与学号Sno为组合主码，课程编号Cno与课程信息表Course外码关联，学号Sno与学生信息表Student外码关联，分数Grade的取值范围是0到100分，分数Grade默认值为空

1
2
3
4
5
6
7
8
9
CREATE TABLE SC (
    Cno VARCHAR(20), -- 课程编号Cno，假设使用20个字符长度的字符串
    Sno VARCHAR(20), -- 学号Sno，假设使用20个字符长度的字符串
    Grade DECIMAL(5, 2) CHECK (Grade BETWEEN 0 AND 100), -- 分数Grade，最多包含5位数，小数点后2位，取值范围0到100
    Semester VARCHAR(20), -- 选课学期，假设使用20个字符长度的字符串
    PRIMARY KEY (Cno, Sno), -- 组合主键，由课程编号和学号组成
    FOREIGN KEY (Cno) REFERENCES Course(Cno), -- 外键关联课程信息表Course的课程编号Cno
    FOREIGN KEY (Sno) REFERENCES Student(Sno) -- 外键关联学生信息表Student的学号Sno
) DEFAULT CHARSET=utf8mb4; -- 指定默认字符集为utf8mb4，以支持更广泛的字符

（2）向基本表SC中插入数据，具体数据如表1所示

​ 表1 选课记录数据表

课程编号 学号 分数 选课学期
1 2023001 空 2023-2024-2
1 2023001 99 2023-2024-1 
1
2
3
4
-- 向SC表中插入数据
INSERT INTO SC (Cno, Sno, Grade, Semester) VALUES
('1', '2023001', NULL, '2023-2024-2'),
('1', '2023001', 99, '2023-2024-1');

（3）删除教师信息表中所有年龄超过60岁的教师信息。

1
2
DELETE FROM Teacher
WHERE Tage > 60;

（4）将所有教师的年龄增加一岁。

1
2
UPDATE Teacher
SET Tage = Tage + 1;

（5）查询姓李并且姓名为两个字（如“李健”）的学生信息，包括学号和姓名信息，查询结果根据学号降序排序。

1
2
3
4
SELECT Sno, SName
FROM Student
WHERE SName LIKE '李%' AND LENGTH(SName) = 3
ORDER BY Sno DESC;

（6）统计每门课程不及格学生人数，包括课程编号、课程名称、不及格人数等信息。

1
2
3
4
5
6
7
8
9
10
11
SELECT 
    C.Cno,
    C.CName,
    COUNT(SC.Sno) AS FailCount
FROM 
    Course C
LEFT JOIN SC ON C.Cno = SC.Cno
WHERE 
    SC.Grade < 60
GROUP BY 
    C.Cno, C.CName;

（7）查询信息工程学院所有学生信息，包含学生学号、学生姓名、所在学院名称和所属专业名称等信息。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
    S.Sno,
    S.SName,
    D.DeptName,
    P.PName
FROM 
    Student S
INNER JOIN Class C ON S.ClassNo = C.ClassNo
INNER JOIN Professional P ON C.Pno = P.Pno
INNER JOIN Department D ON P.DeptNo = D.DeptNo
WHERE 
    D.DeptName = '信息工程学院';

（8）创建可查阅物联网工程专业学生信息的视图ITE_Stu，视图包含学生学号、姓名、性别等信息。

1
2
3
4
5
6
7
8
9
10
CREATE VIEW ITE_Stu AS
SELECT 
    S.Sno,
    S.SName,
    S.SSex
FROM 
    Student S
INNER JOIN Professional P ON S.ClassNo = P.Pno
WHERE 
    P.PName = '物联网工程';

（9）利用ITE_Stu视图查询物联网工程专业所有女生的学号和姓名信息。

1
2
3
4
5
6
7
SELECT 
    Sno,
    SName
FROM 
    ITE_Stu
WHERE 
    SSex = '女';

（10）删除ITE_Stu视图。

1
DROP VIEW IF EXISTS ITE_Stu;

笔记

主要摘自 数据库系统概论(第6版) - 王珊 杜小勇 陈红 编著

数据(data)

描述事物的符号记录。是数据库中存储的基本对象。

数据的含义称为数据的语义，数据与其语义是不可分的。

数据库(database，DB)

是长期存储在计算机内有组织、可共享的大量数据的集合。(按一定的格式存放数据的仓库)

数据库管理系统

包含 数据定义功能(数据定义语言(data definition language，DDL))、数据组织、存储和管理功能、数据操纵功能(数据操纵语言(data manipulation languageDML))、数据库的事务管理和运行管理功能、数据库的建立和维护功能 的，位于用户与操作系统之间的数据管理软件。

数据库系统database system，DBS)

指由 数据库、数据库管理系统(及其应用开发工具)、应用系统和数据库管理员(database administrator，DBA)组成的存储、管理、处理和维护数据的系统

数据建模(data modeling)

把现实世界中的具体事物抽象、组织为某一数据库管理系统支持的数据模型，这个过程称为数据建模(data modeling)。

概念模型

用于信息世界的建模，是现实世界到机器世界的一个中间层次。

关系模型

建立在严格的数学概念基础之上，由一组关系组成，每个关系的数据结构是一张规范化的二维表。

  1. 关系(relation):一个关系对应通常说的一张二维表。
  2. 元组(tuple):表中的一行即为一个元组。
  3. 属性:表中的一列即为一个属性，每列的名称即为属性名。
  4. 码:又称为码键或键，是表中的某一个属性或一组属性，其值可以唯一确定一个元组。
  5. 域(domain):域表示某一属性的取值范围。
  6. 分量(component):元组中的一个属性值。
  7. 关系模式:对关系的描述，一般表示为:关系名(属性1,属性2,·,属性n)。

关系模型要求关系必须是规范化(normalization)的。关系的每一个分量必须是一个不可分的数据项。

优点：

  1. 关系模型建立在严格的数学概念基础上。
  2. 关系模型的概念单一。
  3. 关系模型的存取路径对用户隐蔽。

缺点：

  1. 存取路径对用户是隐蔽的，其查询效率往往不如层次模型和网状模型。
  2. 为了提高性能，关系数据库管理系统必须对用户的查询请求进行优化，因此增加了开发关系数据库管理系统的难度。
数据库系统的三级模式结构

模式、外模式和内模式 三级模式结构。

这三级模间提供了两级映像: 外模式/模式映像和模式/内模式映像。这两级映像保证了数据库系统中的数据能够具有较强的逻辑独立性和物理独立性