建立数据库XSKC
USE XSKC;
CREATE TABLE student(
SNO CHAR(9) PRIMARY KEY CHECK(SNO>='000000000' AND SNO<='999999999'),
SNAME CHAR(20) UNIQUE,
SSEX CHAR(6),
SBIRTHDATE DATE,
SMAJOR VARCHAR(40)
);
CREATE TABLE Course(
CNO CHAR(5) PRIMARY KEY,
CNAME VARCHAR(20),
CCREDIT SMALLINT,
CPNO CHAR(5),
FOREIGN KEY (CPNO) REFERENCES Course(CNO)
);
CREATE TABLE SC(
SNO CHAR(9),
CNO CHAR(5),
GRADE INT CHECK(GRADE>0 AND GRADE<100),
SEMESTER CHAR(5),
TEACHINGCLASS CHAR(8),
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES student(SNO),
FOREIGN KEY(CNO) REFERENCES Course(CNO)
);
CREATE TABLE student1(
SNO CHAR(9) PRIMARY KEY CHECK(SNO>='000000000' AND SNO<='999999999'),
SNAME CHAR(20) UNIQUE,
SSEX CHAR(6),
SBIRTHDATE DATE,
SMAJOR VARCHAR(40)
);
INSERT INTO student VALUES
('20180001','李勇','男','2000-3-8','信息安全'),('20180002','刘晨','女','1999-9-1','计算机科学与技术'),
('20180003','王敏','女','2001-1-8','计算机科学与技术'),('20180004','张立','男','2000-11-1','计算机科学与技术'),
('20180005','张新奇','男','2001-6-12','信息管理与信息系统'),('20180006','赵明','男','2000-6-12','数据科学与大数据技术'),
('20180007','王佳佳','女','2001-12-7','数据科学与大数据技术');
INSERT INTO Course VALUES
('81001','程序设计基础与C语言',4,NULL),
('81007','离散数学',4,NULL),
('81002','数据结构',4,'81001'),
('81005','操作系统',4,'81001'),
('81003','数据库系统概论',4,'81002'),
('81006','Python语言',3,'81002'),
('81004','信息系统概论',4,'81003'),
('81008','大数据技术概论',4,'81003');
INSERT INTO sc VALUES
('20180001','81001',85,'20192','81001-01'),
('20180001','81002',96,'20201','81002-01'),
('20180001','81003',87,'20202','81003-01'),
('20180002','81001',80,'20192','81001-02'),
('20180002','81002',98,'20201','81002-01'),
('20180002','81003',71,'20202','81003-02'),
('20180003','81001',81,'20192','81001-01'),
('20180003','81002',76,'20201','81002-02'),
('20180004','81001',56,'20192','81001-02'),
('20180004','81002',97,'20201','81002-02'),
('20180005','81003',68,'20202','81003-01');
删除操作
删除学习“数据结构”课程的选课记录
DELETE FROM sc WHERE CNO=(
SELECT CNO FROM course WHERE CNAME='数据结构')
删除成绩不及格的选课记录
DELETE FROM sc WHERE GRADE<60
修改操作
把 “李勇”的选课成绩都提高5分
UPDATE sc SET GRADE=GRADE+5 WHERE SNO =(
SELECT SNO FROM student WHERE SNAME='李勇')
将学号为‘20180001’的学生专业修改为“计算机科学与技术”
UPDATE student
SET SMAJOR='计算机科学与技术' WHERE SNO=20180001
把SC表中所有“李勇”的选课成绩都更改为此学生的平均成绩
UPDATE SC SET GRADE=(SELECT GAVG FROM STUDENT,stugrade
WHERE SNAME='李勇' AND stugrade.sno=student.SNO)
WHERE SNO=(SELECT SNO FROM student WHERE SNAME='李勇')
增加操作
新建数据表STUGRADE,包括属性Sno(Char 9),Gavg(平均分,int),对XSKC数据库中的各数据表查询得到如上数据,并将其插入到STUGRADE中。
CREATE TABLE STUGRADE(
SNO CHAR(9),
GAVG INT
);
INSERT INTO stugrade
SELECT SNO,AVG(GRADE) FROM sc GROUP BY SNO