《数据库原理》学生表，课程表，选课表的相关内容

本文链接：https://blog.csdn.net/pythonstrat/article/details/109495701
# 学生表
create table Student(
	Sno char(9) primary key,
	Sname char(20) unique, 
	Ssex char(2),
	Sage smallint,
	Sdept char(20)   
);

# 课程表
create table Course(
	Cno char(4) primary key,
	Cname char(40) not null,
	Cpno char(4), 
	Ccredit smallint,
	foreign key(Cpno) references Course(Cno) 
);
# 表级完整性约束条件， 相当于是自关联  外键Cpno， 参照主键 Cno


# 学生选课表
create table SC(
	Sno char(9),
	Cno char(4),
	Grade smallint,
	primary key(Sno, Cno),
	foreign key(Sno) references Student(Sno),
	foreign key(Cno) references Course(Cno)
);
# 设置两个子墩 合并为主键
# 常规外键



# 插入数据
# 学生表
insert into Student values('201215121', '李勇', '男', 20, 'CS'),
			('201215122', '刘晨', '女', 19, 'CS'),
			('201215123', '王敏', '女', 18, 'MA'),
			('201215125', '张立', '男', 19, 'IS');

# 课程表
insert into Course
	values('1', '数据库', '5', 4),
	('2', '数学', null, 2),
	('3', '信息系统', '1', 4),
	('4', '操作系统', '6', 3),
	('5', '数据结构', '7', 3),
	('6', '数据处理', null, 2),
	('7', 'PASCAL语言', '6', 4);

# SC表
insert into SC
	values('201215121', '1', 92),
	('201215121', '2', 85),
	('201215121', '3', 88),
	('201215122', '2', 90),
	('201215122', '3', 80);

# 三表展示：
select * from Student;
+-----------+--------+------+------+-------+
| Son       | Sname  | Ssex | Sage | Sdept |
+-----------+--------+------+------+-------+
| 201215121 | 李勇   | 男   |   20 | CS    |
| 201215122 | 刘晨   | 女   |   19 | CS    |
| 201215123 | 王敏   | 女   |   18 | MA    |
| 201215125 | 张立   | 男   |   19 | IS    |
+-----------+--------+------+------+-------+

select * from Course;
+-----+--------------+------+---------+
| Cno | Cname        | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1   | 数据库       | 5    |       4 |
| 2   | 数学         | NULL |       2 |
| 3   | 信息系统     | 1    |       4 |
| 4   | 操作系统     | 6    |       3 |
| 5   | 数据结构     | 7    |       3 |
| 6   | 数据处理     | NULL |       2 |
| 7   | PASCAL语言   | 6    |       4 |
+-----+--------------+------+---------+

select * from SC;
+-----------+-----+-------+
| Sno       | Cno | Grade |
+-----------+-----+-------+
| 201215121 | 1   |    92 |
| 201215121 | 2   |    85 |
| 201215121 | 3   |    88 |
| 201215122 | 2   |    90 |
| 201215122 | 3   |    80 |
+-----------+-----+-------+



# 修改
alter table 表名 add foreign key （字段名）references 表名（字段名）
alter table Course add foreign key  references 表名（字段名）
ALTER TABLE Course DROP FOREIGN KEY (fk_symbol);


# 101页，3.52 查询每门课的间接先修课。(自身连接)

select first.Cno, second.Cpno 
from Course as first, Course as second
where first.Cpno=second.Cno; 
# 结果
+-----+------+
| Cno | Cpno |
+-----+------+
| 1   | 7    |
| 3   | 5    |
| 4   | NULL |
| 5   | 6    |
| 7   | NULL |
+-----+------+
5 rows in set (0.01 sec)


# 多表查询（这个题目中自关联相当于使用两张表）当不加条件的时候，就是相当于 两个表当中的元组（各条记录） 做笛卡尔积
select *  
from Course as first, Course as second;


# （内连接） 就是取交集，我感觉和 上面的例子，多表查询没区别，书上也没写这个概念
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2;

# 还以上面的题为例子：
select first.Cno, second.Cpno from Course as first inner join Course as second on first.Cpno=second.Cno;
# 结果： 和上面是一样的
+-----+------+
| Cno | Cpno |
+-----+------+
| 1   | 7    |
| 3   | 5    |
| 4   | NULL |
| 5   | 6    |
| 7   | NULL |
+-----+------+
5 rows in set (0.00 sec)


# 外连接（还分为左连接，右链接）
# 外连接,没有外键约束的条件下，可能出现左右两边都出现空值
select Student.*, SC.* from Student join SC on Student.Son = SC.Sno;

# 左连接： 已左边为标准，右边补 null， 右链接相反(具体参考课本 54,55页的理论基础)
select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade 
from Student left outer join SC on (Student.Sno=SC.Sno);

# 结果：可以看到学生的选课情况，为null的，说明该学生没有选择该门课程
+-----------+--------+------+------+-------+------+-------+
| Sno       | Sname  | Ssex | Sage | Sdept | Cno  | Grade |
+-----------+--------+------+------+-------+------+-------+
| 201215121 | 李勇   | 男   |   20 | CS    | 1    |    92 |
| 201215121 | 李勇   | 男   |   20 | CS    | 2    |    85 |
| 201215121 | 李勇   | 男   |   20 | CS    | 3    |    88 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 2    |    90 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 3    |    80 |
| 201215123 | 王敏   | 女   |   18 | MA    | NULL |  NULL |
| 201215125 | 张立   | 男   |   19 | IS    | NULL |  NULL |
+-----------+--------+------+------+-------+------+-------+
7 rows in set (0.00 sec)


# 多表连接（自身连接相当于两个表，三个表以上通常叫做多表连接）
# （103页，3.54） 查询学生学号，姓名，选修课程名及成绩
select Student.Sno, Sname, Cname, Grade
from Student, SC, Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

# 结果： 三张表进行笛卡尔积运算，之后根据等值连接取 它的子集。
+-----------+--------+--------------+-------+
| Sno       | Sname  | Cname        | Grade |
+-----------+--------+--------------+-------+
| 201215122 | 刘晨   | 数学         |    90 |
| 201215122 | 刘晨   | 信息系统     |    80 |
| 201215121 | 李勇   | 数据库       |    92 |
| 201215121 | 李勇   | 数学         |    85 |
| 201215121 | 李勇   | 信息系统     |    88 |
+-----------+--------+--------------+-------+
5 rows in set (0.01 sec)


# 嵌套查询（103页）： 一个select-from-where 语句为一个查询块。
# 将一个查询块嵌套进另一个查询块的where子句或者having短语中查询，称为嵌套查询 

# 查询选修了课程二的学生的姓名；
select Sname from Student  
where sno in (                
	select Sno from SC
	where Cno="2"
);
# 外层叫做父查询，内层叫做子查询，另外需要注意：子查询不能使用 order_by 子句， 
# order_by 只能对最终的查询结果排序。
# 当子查询是一个单值的时候，也可以使用 = 替换 in

# 结果  
+--------+
| Sname  |
+--------+
| 李勇   |
| 刘晨   |
+--------+
2 rows in set (0.02 sec)

# 另外根据经验， 嵌套查询的效率是很低的，一般不使用，还可以:

select * from Student, SC
where Student.sno = SC.Sno and  Cno="2"; 

# 结果，注意条件 Student.sno = SC.Sno and  Cno="2"， 因为是笛卡尔积，全部结果集合中挑选数据
+-----------+--------+------+------+-------+-----------+-----+-------+
| sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno | Grade |
+-----------+--------+------+------+-------+-----------+-----+-------+
| 201215121 | 李勇   | 男   |   20 | CS    | 201215121 | 2   |    85 |
| 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 | 2   |    90 |
+-----------+--------+------+------+-------+-----------+-----+-------+
2 rows in set (0.00 sec)

# 104页 有很好的例题，可以看看。 105页是一个三层嵌套查询，然后都可以用连接查询替换掉的。
# 不相关查询：子查询的查询条件不依赖于 父查询。
 
# 相关查询： 建议查看106页，好好研究一下，有执行过程解读。


# 子查询中的 谓词 ： 具体看书107,108,109.
any  # 任何一个，也就是某一个值
all  # 所有值

< any  # 小于某个值即可

# 后面的内容不再介绍了，都是类似于离散数学的，谓词逻辑，全称量词，存在量词的理论基础上的。
# 书上很清晰的例子。






























# 修改数据表字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

alter table Student change son sno char(9);