【数据库】SQL语句增删改

建立数据库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