### SQL学生选课知识点解析
#### 一、E-R图
**知识点1：E-R图的概念及作用**
- **概念**：实体-联系模型（Entity-Relationship Model, E-R Model）是一种用于描述现实世界关系的概念工具，常用于数据库设计阶段。它通过图形的方式展示实体及其之间的关系。
- **作用**：
- 帮助分析人员更准确地获取用户需求；
- 便于用户的理解和交流；
- 为逻辑设计阶段提供良好的基础。
#### 二、关系模式
**知识点2：关系模式的设计**
- **关系模式**：是对关系结构的抽象表示，包括表名、各列名及其数据类型等。
- **学生表(Student)**：包含学号、姓名、性别、年龄、所属系等字段。
- **学号(Sno)**：主键，唯一标识一个学生。
- **姓名(Sname)**：唯一，用于区分不同的学生。
- **性别(Ssex)**：记录学生的性别。
- **年龄(Sage)**：记录学生的年龄。
- **所属系(Sdept)**：记录学生所属的专业或系别。
- **课程表(Course)**：包含课程号、课程名、学分等字段。
- **课程号(Cno)**：主键，唯一标识一门课程。
- **课程名(Cname)**：唯一，用于区分不同的课程。
- **学分(Ccredit)**：记录课程的学分数。
- **选课表(SC)**：包含学生学号、课程号、成绩等字段。
- **学生学号(Sno)**：与学生表关联。
- **课程号(Cno)**：与课程表关联。
- **成绩(Grade)**：学生对课程的成绩。
#### 三、实现SQL
**知识点3：创建表的基本语法**
- **创建学生表**：
```sql
CREATE TABLE Student (
Sno VARCHAR2(9) PRIMARY KEY,
Sname VARCHAR2(20) UNIQUE,
Ssex VARCHAR2(2),
Sage INTEGER,
Sdept VARCHAR2(20)
);
```
- **创建课程表**：
```sql
CREATE TABLE Course (
Cno VARCHAR(4) PRIMARY KEY,
Cpno VARCHAR(4),
Cname VARCHAR2(20),
Ccredit INTEGER,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
```
- **创建选课表**：
```sql
CREATE TABLE SC (
Sno VARCHAR2(9),
Cno VARCHAR2(4),
Grade INTEGER,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
```
#### 四、修改基本表
**知识点4：修改表结构的操作**
- **添加列**：`ALTER TABLE <表名> ADD <列名> <数据类型>;`
- 示例：为学生表添加用户密码列。
```sql
ALTER TABLE STUDENT ADD Spwd VARCHAR2(20);
```
- **增加唯一性约束**：`ALTER TABLE <表名> ADD UNIQUE (<列名>);`
- 示例：增加课程名的唯一性约束。
```sql
ALTER TABLE Course ADD UNIQUE (Cname);
```
- **修改列的数据类型**：`ALTER TABLE <表名> ALTER COLUMN <列名> <新数据类型>;`
- 示例：将用户密码列的数据类型由`VARCHAR2(20)`改为`VARCHAR2(30)`。
```sql
ALTER TABLE STUDENT ALTER COLUMN Spwd VARCHAR2(30);
```
#### 五、删除基本表
**知识点5：删除表**
- **语法**：`DROP TABLE <表名> [RESTRICT|CASCADE];`
- `RESTRICT`：如果表没有依赖关系，则可以被删除。
- `CASCADE`：即使表有依赖关系也会被删除。
- 示例：删除学生表。
```sql
DROP TABLE Student CASCADE;
```
#### 六、数据查询
**知识点6：SQL查询语句**
- **查询全体学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student;
```
- **查询学生的姓名和出生年**：
```sql
SELECT Sname, Sage FROM Student;
```
- **查询学生的姓名和所在系名，并将系名转换为小写**：
```sql
SELECT Sname, LOWER(Sdept) AS Sdept_lower FROM Student;
```
- **消除取值重复行**：
```sql
SELECT DISTINCT Sname FROM Student;
```
- **查询GIS专业学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE Sdept = 'GIS';
```
- **查询年龄小于25的学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE Sage < 25;
```
- **查询年龄介于20-25之间的学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE Sage BETWEEN 20 AND 25;
```
- **查询年龄不介于20-25之间的学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE NOT Sage BETWEEN 20 AND 25;
```
- **查询GIS和RS系学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE Sdept IN ('GIS', 'RS');
```
- **查询不在GIS和RS系学生的学号和姓名**：
```sql
SELECT Sno, Sname FROM Student WHERE Sdept NOT IN ('GIS', 'RS');
```
- **字符匹配**：
```sql
SELECT * FROM Student WHERE Sname LIKE '%牛%';
```
- **排序查询**：
```sql
SELECT * FROM Student ORDER BY Sage DESC;
```
- **聚集函数**：
- **查询学生总人数**：
```sql
SELECT COUNT(*) FROM Student;
```
- **查询GIS课程的平均成绩**：
```sql
SELECT AVG(Grade) FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 'GIS');
```
- **查询学生牛一的平均成绩**：
```sql
SELECT AVG(Grade) FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sname = '牛一');
```
- **查询各个课程的课程号与选课人数**：
```sql
SELECT Cno, COUNT(*) AS NumStudents FROM SC GROUP BY Cno;
```
- **复合查询**
- **查询选修2号课程且成绩在80分以上的学生**：
```sql
SELECT Sno FROM SC WHERE Cno = '2' AND Grade > 80;
```
- **查询每个学生的学号、姓名、选修课程名以及成绩**：
```sql
SELECT Student.Sno, Sname, Cname, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno;
```
- **查询和牛一在同一个系的学生**：
```sql
SELECT Sno, Sname FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = '牛一') AND Sname != '牛一';
```
- **查询选修了GIS课程的学生**：
```sql
SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE Cname = 'GIS';
```
#### 七、数据更新
**知识点7：更新数据**
- **插入数据**：`INSERT INTO <表名>(<列名列表>) VALUES (<值列表>);`
- **修改数据**：`UPDATE <表名> SET <列名>=<新值> WHERE <条件>;`
- **删除数据**：`DELETE FROM <表名> WHERE <条件>;`
#### 附录：视图
**知识点8：视图的创建与使用**
- **创建视图**：`CREATE VIEW <视图名> AS <查询语句>;`
- **删除视图**：`DROP VIEW <视图名>;`
- **视图的其他操作**：视图还可以进行更新操作，但受限于视图的定义。
- **视图的作用**：
- **简化查询**：对于复杂的查询，可以通过视图来简化用户的操作。
- **安全性**：可以限制用户只能通过视图访问数据，而不允许直接访问底层的数据表。
- **数据汇总**：视图可以用来汇总多个表的数据，形成一个统一的视图。
