create database 教学;
use 教学;
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20));
insert into Student
values(20191708,'娟娟','女',19,'广播系');
insert into Student
values(20191766,'赵阳','男',12,'数学系');
insert into Student
values('2019002','赵亮','女',18,'数学系');
insert into Student
values('2019001','赵星','男',19,'数学系');
insert into Student
values('2019005','张林林','男',19,'计算机系');
insert into Student
values('2019006','周芷若','女',19,'计算机系');
insert into Student
values(20191701,'王位','男',22,'广播系');
CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY (Sno, Cno), /*在表级定义实体完整性*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/*在表级定义参照完整性*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/*在表级定义参照完整性*/
);
CREATE TABLE SelectCourse
(Sno CHAR(9) ,
Cno CHAR(4) ,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
/*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE
/*当更新course表中的cno时，级联更新SC表中相应的元组*/
);
delete from sc where sno='2019001';
SELECT Sname as 姓名,2021-Sage as 出生年份,'中国公民'
FROM Student;
SELECT distinct sage
FROM Student;
SELECT *
FROM Student
limit 0,2;
SELECT Sname,Sage
FROM Student
WHERE Sage=19;
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sname BETWEEN '娟娟' AND '海海';
SELECT Sname,Ssex,Sdept
FROM Student
WHERE Sdept not IN ('广播','数学','计算机');
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname not LIKE '王%';
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname not LIKE '__';
SELECT Sname,sdept,sage
FROM Student
WHERE Sdept= '计算机' AND Sage<20;
SELECT *
FROM Student
ORDER BY Sage DESC,sno;
SELECT COUNT(*)
FROM Student;
select student.*,sc.*
from Student,sc
where SC.Sno=Student.Sno ;
select Sno,Sname,Cname,Grade
from Student,Course,SC
WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno
order by Cno desc;
SELECT Cname,Ccredit
FROM course
where Cno=Cpno;
select Sname
from Student
where Sdept='计算机系' and Sage=19;
select Cno
from course
where Cno not in (
select Cno
from SC
where Sno='20191701' );
select Sname,Sage
from sc,student
where Cno=4;
select Sname,Sage
from student
where Ssex='男' and Sage > Sage and Ssex='女';
select Sname,Sage
from student
where Sage<=ALL(
select Sage
from student
where Sdept='数学系')and
Sdept<>'数学系';