SQL 语句初步

出于笔者的习惯，所有的数据库、表和字段命名并不一定和书上一致。

建立数据库和表

今天我们要做的第一步就是把书上 P126 的三张表格载入我们的数据库中。

创建新数据库

1. 右键【数据库】
2. 选择【新建数据库】
3. 在【数据库名称】字段中输入 StudentManagement
4. 点击【确定】

创建表

现在我们将创建关键的三个表：Students，Courses 和 StudentAssignments，它们分别对应学生表、课程表和学生作业表。

1. 在界面的最上方，点击【新建查询】
2. 在查询窗口中，输入以下代码：

 USE StudentManagement;
 ​
 CREATE TABLE Students(
     StudentID CHAR(4) PRIMARY KEY,
     Name NVARCHAR(50) NOT NULL,
     Gender NCHAR(1) CHECK( Gender IN N('男','女')),
     MajorClass NVARCHAR(50) NOT NULL,
     BirthDate DATE NOT NULL,
     PhoneNumber VARCHAR(20)
 );
 ​
 CREATE TABLE Courses(
     CourseID CHAR(4) PRIMARY KEY,
     CourseName NVARCHAR(100) NOT NULL,
     Credits DECIMAL(2,1) NOT NULL,
     Hours INT NOT NULL,
     Instructor NVARCHAR(50) NOT NULL,
 );
 ​
 CREATE TABLE StudentAssignments(
     CourseID CHAR(4),
     StudentID CHAR(4),
     Assignment1Score INT,
     Assignment2Score INT,
     Assignment3Score INT,
     PRIMARY KEY (CourseID, StudentID),
     FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
     FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
 );

看起来有点复杂吗？让我们逐步解释这些代码，如果一行代码没有多余的知识点，我们就跳过它。

 USE StudentManagement;

这行代码指定我们要在 StudentManagement 数据库中执行接下来的操作，它确保了我们会在正确的数据库下执行工作。

 CREATE TABLE Students(

它是创建 Students 这张表的开始。CREATE TABLE 是创建新表 TABLE 的 SQL 命令。

 StudentID CHAR(4) PRIMARY KEY,

StudentID 是这一列的名称，CHAR(4) 代表这是一个固定长度为 4 的字符串。PRIMARY KEY 表示这是表的唯一标识符，即主键，它约束了：

• 每个学生都有一个唯一的 StudentID
• 不可能有两个学生拥有相同的 StudentID
• StudentID 不能为空
• 可以通过 StudentID 快速找到特定的学生记录
• 其他表（如 StudentAssignments 可以通过 StudentID 来引用特定的学生）

总而言之，主键就是确保了每一条记录都是唯一可识别的，或许可以理解成类似于身份证号一样的意思。

 Name NVARCHAR(50) NOT NULL,  

代表这一列使用的是一个可变长度（NVARCHAR）最多 50 个字符，支持 Unicode 的字符串。支持 Unicode 的原因是这里姓名是中文，这对存储中文字符是必要的。而 NOT NULL 则表示这个字段不能为空。

 Gender NCHAR(1) CHECK( Gender IN N('男','女')),

NCHAR 和 CHAR 的区别就是它支持 Unicode，因为我们输入的是中文所以需要这个。

其中 CHECK( Gender IN N('男','女')) 确保了只能输入 男 或 女。

 BirthDate DATE NOT NULL,  

这里 DATE 也是一种类型噢，代表日期。

 PhoneNumber VARCHAR(20)

这里没有 NOT NULL 说明可以为空。

 );

写完所有的内容之后，我们用其表示表创建的结束。

 Credits DECIMAL(2,1) NOT NULL,

这里 DECIMAL(2,1) 代表加上有效数字为 2 位（即包括整数部分和小数部分），1 代表小数点后有一位。

 Hours INT NOT NULL,

这里 INT 是整数类型，和我们熟悉的 C/C++ 差不多。

 PRIMARY KEY (CourseID, StudentID),

这里 PRIMARY KEY 是复合主键，也就是把两个或更多列组合起来形成一个主键的操作。

在这里，CourseID 和 StudentID 被组合起来，构成一个新的主键，这意味着它们的组合变成唯一的了。也就是说：

• 可以有多个记录有相同的 CourseID
• 也可以有多个记录有相同的 StudentID
• 但不能有两个记录同时拥有相同的 CourseID 和 StudentID

它的现实意义是：

• 一个学生可以选多门课
• 一门课可以有多个学生
• 但是一个学生在一门课中只能有一个成绩

 FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),
 FOREIGN KEY (StudentID) REFERENCES Students(StudentID)

它建立了两张表之间的联系，以第一句为例，它代表 StudentAssignments 表中的 CourseID 必须存在于 Courses 表中。这样，就约束了我们不可能在 StudentAssignments 表中添加一个不存在的课程或者学生的记录。也就是说，如果我们在前面的表中找不到这个人，我们就不能添加这个数据。

往表内写入数据

现在你已经建立了三张表，接下来就是要往表内写入数据了。按照书上的内容，我们分别运行下面的代码：

 INSERT INTO Students (StudentID, Name, Gender, MajorClass, BirthDate, PhoneNumber)  
 VALUES   
 ('0433', N'张艳', 'F', N'生物04', '1986-09-13', NULL),  
 ('0496', N'李越', 'M', N'电子04', '1984-02-23', '1381290****'),  
 ('0529', N'赵欣', 'M', N'会计05', '1984-01-27', '1350222****'),  
 ('0531', N'张志国', 'M', N'生物05', '1986-09-10', '1331256****'),  
 ('0538', N'于兰兰', 'F', N'生物05', '1984-02-20', '1331200****'),
 ('0591', N'王丽丽', 'F', N'电子05', '1984-03-20', '1332080****'),
 ('0592', N'王海强', 'M', N'电子05', '1986-11-01', NULL);

 INSERT INTO Courses (CourseID, CourseName, Credits, Hours, Instructor)  
 VALUES   
 ('K001', N'计算机图形学', 2.5, 40, N'胡晶晶'),  
 ('K002', N'计算机应用基础', 3, 48, N'任泉'),  
 ('K006', N'数据结构', 4, 64, N'马跃先'),  
 ('M001', N'政治经济学', 4, 64, N'孔繁新'),
 ('S001', N'高等数学', 3, 48, N'赵晓尘');

 INSERT INTO StudentAssignments (CourseID, StudentID, Assignment1Score, Assignment2Score, Assignment3Score)  
 VALUES   
 ('K001', '0433', 60, 75, 75),  
 ('K001', '0529', 70, 70, 60),  
 ('K001', '0531', 70, 80, 80),  
 ('K001', '0591', 80, 90, 90),  
 ('K002', '0496', 80, 80, 90),  
 ('K002', '0529', 70, 70, 85),  
 ('K002', '0531', 80, 80, 80),  
 ('K002', '0538', 65, 75, 85),
 ('K002', '0592', 75, 85, 85),
 ('K006', '0531', 80, 80, 90),
 ('K006', '0591', 80, 80, 80),
 ('M001', '0496', 70, 80, 80),
 ('M001', '0591', 65, 75, 75),
 ('S001', '0531', 80, 80, 80),
 ('S001', '0538', 60, NULL, 80);

简单查询

下面我们开始正式进入作业完成的部分，每种语句第一次出现时我都会给出解释

查询学时数大于 50 的课程信息

 SELECT * FROM Courses WHERE Hours > 50;

• SELECT *：选择所有列
• FROM Courses：从 Courses 表中
• WHERE Hours > 50：条件是 Hours 列的值大于 50

查询在 1984 年出生的学生的学号、姓名和出生日期

 SELECT StudentID, Name, BirthDate   
 FROM Students   
 WHERE YEAR(BirthDate) = 1984;

• SELECT StudentID, Name, BirthDate：选择这三列
• YEAR()：一个函数，提取日期中的年份

查询三次作业的成绩都在 78 分以上的学号、课程号

 SELECT StudentID, CourseID  
 FROM StudentAssignments  
 WHERE Assignment1Score > 78   
   AND Assignment2Score > 78   
   AND Assignment3Score > 78;

• AND：每个条件都要满足

查询 05 级的男生信息

 SELECT *   
 FROM Students  
 WHERE MajorClass LIKE '%05%' AND Gender = 'M';

• WHERE MajorClass LIKE '%05%'：MajorClass 列包含 05

查询作业成绩为空的学号和课程号

 SELECT StudentID, CourseID  
 FROM StudentAssignments  
 WHERE Assignment1Score IS NULL   
    OR Assignment2Score IS NULL   
    OR Assignment3Score IS NULL;

• OR：满足任意条件皆可
• IS NULL：检查值是否为空

查询三次作业总分排名前三的学生学号、课程号及作业总分

 SELECT TOP 3 StudentID, CourseID,   
        (Assignment1Score + Assignment2Score + Assignment3Score) AS TotalScore  
 FROM StudentAssignments  
 ORDER BY TotalScore DESC;

• SELECT TOP 3：只选择前三行
• (Assignment1Score + Assignment2Score + Assignment3Score) AS TotalScore：计算总分并命名为 TotalScore
• ORDER BY TotalScore DESC：按照 TotalScore 降序排列

查询于兰兰的选课信息，列出学号、姓名、课程名

 SELECT s.StudentID, s.Name, c.CourseName  
 FROM Students s  
 JOIN StudentAssignments sa ON s.StudentID = sa.StudentID  
 JOIN Courses c ON sa.CourseID = c.CourseID  
 WHERE s.Name = N'于兰兰';

• FROM Students s：从 Students 表开始，别名为 s
• JOIN StudentAssignments sa：连接 StudentAssignments 表，别名为 sa
• ON s.StudentID = sa.StudentID：连接条件