/************************************************
一、建立数据库
************************************************/
USE master;
GO
CREATE DATABASE [StuData]
ON PRIMARY
(
NAME = N'StuData',
FILENAME = N'D:\SQL work\StuData.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1MB
)
LOG ON
(
NAME = Studlog,
FILENAME = 'D:\SQL work\Stulog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20
);
GO
/************************************************
二、建表
************************************************/
USE StuData;
GO
--学生表
CREATE TABLE S
(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2) CHECK (Ssex IN ('男','女')),
Sage SMALLINT,
Sdept CHAR(20)
);
--课程表
CREATE TABLE C
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES C(Cno)
);
GO
--选课表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT CHECK (Grade >= 0 AND Grade <= 100),
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES S(Sno),
FOREIGN KEY (Cno) REFERENCES C(Cno)
);
GO
--加入列
--ALTER TABLE C ADD Cpno CHAR(4);
/************************************************
三、数据更新
************************************************/
--修改S
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95001', '李勇', '男', 20, 'CS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95002', '刘晨', '女', 19, 'IS');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95003', '王敏', '女', 18, 'MA');
INSERT INTO S(Sno, Sname, Ssex, Sage, Sdept) VALUES ('95004', '张力', '男', 19, 'IS');
--修改C
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C2', '高等数学', 2, null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C6', '数据处理', 2, null);
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C7', 'PASCAL语言',4, 'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C4', '操作系统', 3, 'c6');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C5', '数据结构', 4, 'c7');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C1', '数据库', 4, 'c5');
INSERT INTO C(Cno, Cname, Ccredit, Cpno) VALUES ('C3', '信息系统', 4, 'c1');
--修改数据
/*
UPDATE C
SET Cpno='C5'
WHERE Cno = 'C1';
*/
--修改SC
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C1', '92');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C2', '65');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C3', '88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C4', '88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C5', '88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C6', '88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95001', 'C7', '88');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95002', 'C2', '90');
INSERT INTO SC(Sno, Cno, Grade) VALUES ('95002', 'C5', '73');
/************************************************
四、查询
************************************************/
--列出所有表(测试)
SELECT *
FROM S;
SELECT *
FROM C;
SELECT *
FROM SC;
--查询所有姓刘学生的姓名、学号和性别
SELECT Sname , Sno , Ssex
FROM S
WHERE Sname LIKE '刘%' ;
--查询选修c2课程且成绩在90分及以上的所有学生的学号、姓名
--方法一
SELECT S.Sno, Sname
FROM S, SC
WHERE S.Sno = SC.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90;
--方法二
SELECT Sno,Sname
FROM S
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE SC.Cno = 'C2' AND SC.Grade >= 90);
--方法三
SELECT Sno,Sname
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno=S.Sno AND SC.Cno = 'C2' AND SC.Grade >= 90);
--查询有2门以上课程是80分以上的学生学号及其(80分以上的)课程数
SELECT Sno, COUNT(*) NUM_OF_COURSE
FROM SC
WHERE Grade >= 80
GROUP BY Sno
HAVING COUNT(*) >= 2;
--查询选修了课程名为“数据库”的学生学号和姓名
--法一
SELECT Sno, Sname
FROM S
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM C
WHERE Cname = '数据库')
);
--法二
SELECT Sno, Sname
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE Sno = S.Sno AND EXISTS
(SELECT *
FROM C
WHERE Cno = SC.Cno AND Cname = '数据库')
);
--查询其他系中比信息系某些学生年龄小的学生姓名和年龄
--法一
SELECT Sname, Sage
FROM S
WHERE Sage<ANY(SELECT Sage
FROM S
WHERE Sdept = 'IS')
AND Sdept <> 'IS';
--法二
SELECT Sname, Sage
FROM S
WHERE Sage < (SELECT MAX(Sage)
FROM S
WHERE Sdept='IS')
AND Sdept!='IS';
-- 查询没有选修c1号课程的学生姓名
--法一
SELECT Sname
FROM S
WHERE Sno NOT IN
(SELECT Sno
FROM SC
WHERE Cno = 'C1');
--法二
SELECT Sname
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = S.Sno AND Cno = 'C1');
--查询选修了全部课程的学生姓名
--法一(派生表)
SELECT Sname
FROM S
WHERE Sno NOT IN
(SELECT Sno
FROM (SELECT Sno,Cno
FROM S,C
EXCEPT
SELECT Sno,Cno
FROM SC) AS NEWT
);
--法二(存在量词)
SELECT Sname
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Cno = C.Cno AND Sno = S.Sno)
);
--查询至少选修学生95002所选全部课程的学生学号
--法一
SELECT DISTINCT Sno
FROM Sc AS S1
WHERE NOT EXISTS
(
SELECT *
FROM Sc AS S2
WHERE Sno='95002' AND NOT EXISTS
(
SELECT *
FROM Sc AS S3
WHERE Sno=S1.SNO AND Cno=S2.Cno
)
);
--法二
SELECT DISTINCT SNO
FROM SC AS S1
WHERE SNO NOT IN
(
SELECT SNO
FROM SC AS S2
WHERE SNO = '95002' AND S2.CNO NOT IN
(
SELECT CNO
FROM SC AS S3
WHERE SNO = S1.SNO AND S3.CNO = S2.CNO
)
);
--三
SELECT DISTINCT SNO
FROM SC AS SCX
WHERE Sno IN
(
SELECT Sno
FROM SC AS SCY
WHERE SNO = SCX.Sno
EXCEPT
(
SELECT CNO
FROM SC
WHERE SNO = SCX.Sno
EXCEPT
SELECT Cno
FROM SC
WHERE Sno = '95002'
)
);
ms SQL server数据库学生表查询
最新推荐文章于 2024-09-11 16:49:52 发布