关于数据库sql的一道

。系关系模式D（DNO，DNAME），其属性分别表示：系号、系名。
。学生关系模式S（SNO，SNAME， AGE，SEX，DNO），其属性分别表示：学号、姓名、年龄、性别，系号。
。教师关系模式T（TNO，TNAME，DNO），其属性分别表示：教师号、教师名、教师所在系的系号。
。课程关系模式C（CNO，CNAME，CREDIT，TNO），其属性分别表示：课程号、课程名、学分数、讲授课程教师号。
。选课关系模式SC（SNO，CNO，GRADE），其属性分别表示：学号、课程号、成绩。
请用SQL语句实现下列操作：

1． 假设T表已定义，请用CREATE TABLE 语句定义课程基本表S（要求有实体完整性和参照完整性约束）。

2． 将选课表SC中每个选修了课程号为“C2”的课程成绩加2分。

3． 将选课表SC中删除所有学号为“S1”的选课记录。

4． 查询没有选修课程的学生姓名。

5． 查询 所有“信息”系的教师编号和姓名。

1． 假设T表已定义，请用CREATE TABLE 语句定义课程基本表S（要求有实体完整性和参照完整性约束）。

CREATE TABLE C
(CNO INT(8) PRIMARY KEY,--主键
 CNAME CHAR(20) UNIQUE, --一般课程都不会重名
 CREDIT FLOAT(2) NOT NULL,--一门课不可能不标学分吧，0也算
 FOREIGN KEY(TNO) REFERENCES T(TNO)--C表的TNO是外键，参照T表的TNO
);--类型后面的数字仅代表显示的内容长度，并不代表存储的空间
  • 这里咱也不知道“课程基本表S”到底是要写哪个，就写了课程基本表（C表），没写S（学生表）表，
  • 因为S表的外键DNO对应D表的DNO，但是第一问没给这个条件

2． 将选课表SC中每个选修了课程号为“C2”的课程成绩加2分。

UPDATE SC
SET GRADE
WHERE CNO = 'C2';

3． 将选课表SC中删除所有学号为“S1”的选课记录。

DELETE
FROM SC
WHERE SNO = 'S1';

4． 查询没有选修课程的学生姓名。

SELECT SNAME
FROM S,SC
WHERE S.SNO NOT IN(
    SELECT DISTINCT SC.SNO
    FROM SC
);--先找到选课表中所有的学生学号，再看看哪个学生的学号不在选课表的学号集里面

5． 查询 所有“信息”系的教师编号和姓名。

SELECT TNO,TNAME
FROM T,D
WHERE T.DNO = D.DNO AND DNAME = '信息';

注：数据是模拟的

关系图

img

D、T、C、SC、S表的创建，主键、外键约束的添加

创建D表，Dno为主键

CREATE TABLE `D` (
`Dno`  int NOT NULL AUTO_INCREMENT COMMENT '系号' ,
`Dname`  varchar(255) NULL COMMENT '系名' ,
PRIMARY KEY (`Dno`)
)

创建T表，Tno为主键

CREATE TABLE `T` (
`Tno`  int NOT NULL AUTO_INCREMENT COMMENT '教师号' ,
`Tname`  varchar(255) NULL COMMENT '教师名' ,
`Dno`  int NULL COMMENT '系号' ,
PRIMARY KEY (`Tno`)
)

创建C表，Cno为主键

CREATE TABLE `C` (
`Cno`  int NOT NULL AUTO_INCREMENT COMMENT '课程号' ,
`Cname`  varchar(255) NULL COMMENT '课程名' ,
`Credit`  varchar(255) NULL COMMENT '学分数' ,
`Tno`  int NULL COMMENT '教师号' ,
PRIMARY KEY (`Cno`)
)

创建SC表

CREATE TABLE `SC` (
`Sno`  int NULL COMMENT '学号' ,
`Cno`  int NULL COMMENT '课程号' ,
`Crade`  int NULL COMMENT '成绩' 
)

创建S表,Sno为主键

CREATE TABLE `S` (
`Sno`  int NOT NULL AUTO_INCREMENT COMMENT '学号' ,
`Sname`  varchar(255) NULL COMMENT '姓名' ,
`Age`  int NULL COMMENT '年龄' ,
`Sex`  varchar(255) NULL COMMENT '性别' ,
`Dno`  int NULL COMMENT '系号' ,
PRIMARY KEY (`Sno`)
)

添加外键约束T_Dno，将T表的Dno设为外键，关联D表的主键Dno

ALTER TABLE `T` ADD CONSTRAINT `T_Dno` FOREIGN KEY (`Dno`) REFERENCES `D` (`Dno`)

添加外键约束C_Tno，将C表的Tno设为外键，关联T表的主键Tno

ALTER TABLE `C` ADD CONSTRAINT `C_Tno` FOREIGN KEY (`Tno`) REFERENCES `T` (`Tno`)

添加外键约束SC_Sno，将SC表的Sno设为外键，关联S表的主键Sno

ALTER TABLE `SC` ADD CONSTRAINT `SC_Sno` FOREIGN KEY (`Sno`) REFERENCES `S` (`Sno`)

添加外键约束SC_Cno，将SC表的Cno设为外键，关联C表的主键Cno

ALTER TABLE `SC` ADD CONSTRAINT `SC_Cno` FOREIGN KEY (`Cno`) REFERENCES `C` (`Cno`)

添加外键约束S_Dno，将S表的Dno设为外键，关联D表的主键Dno

ALTER TABLE `S` ADD CONSTRAINT `S_Dno` FOREIGN KEY (`Dno`) REFERENCES `D` (`Dno`)

表的数据初始化

D表

INSERT INTO `D` (`Dname`) VALUES ('信息');
INSERT INTO `D` (`Dname`) VALUES ('建筑');
INSERT INTO `D` (`Dname`) VALUES ('艺术');

img

T表

INSERT INTO `T` (`Tname`, `Dno`) VALUES ('T1', '1');
INSERT INTO `T` (`Tname`, `Dno`) VALUES ('T2', '2');
INSERT INTO `T` (`Tname`, `Dno`) VALUES ('T3', '3');
INSERT INTO `T` (`Tname`, `Dno`) VALUES ('T4', '1');

img

C表

INSERT INTO `C` (`Cname`, `Credit`, `Tno`) VALUES ('C1', '3', '2');
INSERT INTO `C` (`Cname`, `Credit`, `Tno`) VALUES ('C2', '4', '1');
INSERT INTO `C` (`Cname`, `Credit`, `Tno`) VALUES ('C3', '2', '3');
INSERT INTO `C` (`Cname`, `Credit`, `Tno`) VALUES ('C4', '4', '4');

img

S表

INSERT INTO `S` (`Sname`, `Age`, `Sex`, `Dno`) VALUES ('S1', '15', '男', '1');
INSERT INTO `S` (`Sname`, `Age`, `Sex`, `Dno`) VALUES ('S2', '16', '女', '2');
INSERT INTO `S` (`Sname`, `Age`, `Sex`, `Dno`) VALUES ('S3', '18', '男', '3');

img

SC表

INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('1', '1', '60');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('1', '2', '75');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('1', '3', '75');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('1', '4', '90');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('2', '3', '80');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('2', '1', '60');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('3', '1', '90');
INSERT INTO `SC` (`Sno`, `Cno`, `Crade`) VALUES ('3', '3', '80');

img

1． 假设T表已定义，请用CREATE TABLE 语句定义课程基本表S（要求有实体完整性和参照完整性约束）。

答案参照上方创建表的过程

2、 将选课表SC中每个选修了课程号为“C2”的课程成绩加2分。

UPDATE SC set Crade=crade+2 
where Cno in(
    select Cno from C where Cname = "C2"
)

3． 将选课表SC中删除所有学号为“S1”的选课记录。

DELETE from SC where Sno in(
    select Sno from S where Sname = "S1"
)

4． 查询没有选修课程的学生姓名。

SELECT Sname from S where Sno in(
    SELECT Sno from SC group by sno having count(*) <= 0
)

注意：我初始化的数据中无没有选修课程的学生，查询出来的是null

5． 查询 所有“信息”系的教师编号和姓名。
答案1：

select T.Tno,T.Tname from D,T 
where D.Dno=T.Dno
and D.Dname="信息"

答案2：

select Tno,Tname from T 
where Dno in(select Dno from D where Dname = "信息")