创建数表插入数：
create table S
(SNO CHAR(6) NOT NULL
SNAME CHAR(8) NOT NULL
SSEX CHAR(2)
SAGE SMALLINT
SDEPT CHAR(10)
PRIMARY KEY (SNO)
CHECK (SSEX'男' or SSEX'女'))
create table c
(CNO char(6)not null
CNAME char(10) not null
CT SMALLINT
TNO CHAR(6)
PRIMARY KEY (CNO)
FOREIGN KEY (TNO) REFERENCES T(TNO))
create table SC
(SNO CHAR (6)
CNO CHAR (6)
SCORE SMALLINT
FOREIGN KEY(SNO) REFERENCES S(SNO)
FOREIGN KEY(CNO) REFERENCES C(CNO)
CHECK(SCORE>0 AND SCORE<100))
CREATE UNIQUE INDEX IX_S ON S(SNO)
insert into S VALUES('95001''李勇''男'20'计算机')
insert into S VALUES('95002''刘晨''女'19'信息')
insert into S VALUES('95003''王敏''女'18'数学')
insert into S VALUES('95004''张立''男'19'信息')
insert into S VALUES('95005''欧阳锋''男'19'信息')
insert into S VALUES('95006''欧阳震华''男'24'数学')
insert into S VALUES('95007''刘秋明''女'19'计算机')
INSERT INTO C VALUES('C1''数库'72'T1')
INSERT INTO C VALUES('C2''数学'80'T5')
INSERT INTO C VALUES('C3''信息系统'30'T3')
INSERT INTO C VALUES('C4''操作系统'60'T2')
INSERT INTO C VALUES('C5''数结构'80'T1')
INSERT INTO C VALUES('C6''计算机网络'50'T3')
INSERT INTO C VALUES('C7''C语言'60'T4')
INSERT INTO C VALUES('C8''DB_Design'50'T4')
INSERT INTO T VALUES('T1''李力''男''教授')
INSERT INTO T VALUES('T2''王''女''讲师')
INSERT INTO T VALUES('T3''刘伟''男''讲师')
INSERT INTO T VALUES('T4''张雪''女''教授')
INSERT INTO T VALUES('T5''张兰''女''副教授')
INSERT INTO T VALUES('T6''王''男''助教')
INSERT INTO SC VALUES('95001''C1'92)
INSERT INTO SC VALUES('95001''C2'85)
INSERT INTO SC VALUES('95001''C3'NULL)
INSERT INTO SC VALUES('95001''C4'NULL)
INSERT INTO SC VALUES('95001''C5'NULL)
INSERT INTO SC VALUES('95001''C6'NULL)
INSERT INTO SC VALUES('95001''C7'NULL)
INSERT INTO SC VALUES('95001''C8'NULL)
INSERT INTO SC VALUES('95002''C2'90)
INSERT INTO SC VALUES('95002''C3'80)
INSERT INTO SC VALUES('95003''C5'50)
INSERT INTO SC VALUES('95004''C2'66)
INSERT INTO SC VALUES('95004''C3'NULL)
INSERT INTO SC VALUES('95005''C7'NULL)
INSERT INTO SC VALUES('95007''C1'80)
INSERT INTO SC VALUES('95007''C3'48)
INSERT INTO SC VALUES('95007''C4'93)
．简单查询
查询全体学生姓名学号系
select snamesnosdept from s
查询全体学生详细记录
select * from s
查询全体学生姓名出生年份
select snameyear(getdate())sage as 'birthday' from s
查询选修课程学生学号
select sno from sc
查询年龄20岁学生姓名年龄
select snamesage from s where sage<20
查询考试成绩格学生学号
select sno from sc where score<60
查询年龄1820间学生姓名系年龄
select snamesdept sage from s where sage between 18 and 20
查询年龄2023间学生姓名系年龄
select snamesdept sage from s where sage not between 20 and 23
查询信息系数学系计算机系学生姓名性
select sname ssex from s where sdept'信息' or sdept'数学' or sdept'计算机'
查询信息系数学系计算机系学生姓名性
select sname ssex from s where sdept not in ('信息''数学''计算机')
查询学号95001学生详细情况
select * from s where sno'95001'
查询姓刘学生姓名学号性
select snamesnossex from s where sname like '刘'
查询姓欧阳命名三汉字学生姓名
select sname from s where sname like '欧阳_'
查询名字中第2字阳字学生姓名学号
select snamesno from s where sname like '_阳'
查询姓刘学生姓名
select sname from s where sname like '[^刘]'
查询DB_Design课程课程号学时
select cnoct from c where cname'DB_Design'
查询DB_开头倒数第3字符i课程详细情况
select * from c where cname like 'DB_i__'
查询缺少成绩学生学号相应课程号
select snocno from sc where score is null
查询成绩学生学号课程号(where grade is not null)
select snocno from sc where score is not null
查询选修课程号C2C4课程学生学号
select sno from sc where cno'c2' or cno'c4'
查询课程号C2C4C5课程求显示出课程号课程名（求in运算符）
select cnocname from c where cno in ('c2''c4''c5')
二连接查询
1） 查询选修C2号课程学生学号姓名
select snosname from s where sno in (select sno from sc where cno'c2')
2）查询选课学生学号姓名选课名称成绩
select ssnossnameccnamescscore from scsc
3）查询学生选课成绩情况求显示学生姓名选课名称成绩查询结果存放新数表new_table
select ssnameccnamescscore into new_table from scsc
4）查询选修课程名C语言学生学号姓名
select snosname from s where sno in (select sno from sc where cno in (select cno from c where cname'C语言'))
5）查询选修课程名数结构学生姓名课程成绩
select DISTINCT SSNOscscore from s join sc on ssnoscsno where cno in (select cno from c where cname'数结构')
6）查询计算机系学生选课成绩情况求显示学生学号姓名课程号课程名称成绩
select ssnossnameccnoccnamescscore from s join sc on ssnoscsno join c on sccnoccno where ssno in (select sno from s where sdept'计算机')
7）查询选修C2号课程成绩90分学生学号姓名
select snosname from s where sno in(select sno from sc where score>90 and cno'c2')
8）查询刘晨年龄学生姓名性年龄刘晨年龄
select snamessexsage from s where sage>(select sage from s where sname'
9）查询选修课程未参加考试学生学号姓名课程号课程名
select ssnossnameccnoccname from sc where sno in(select DISTINCT sno from sc where score is null)
10）查询选修课程考试成绩格学生学号姓名课程号课程名成绩
select ssnossnameccnoccnamescscore from s join sc on ssnoscsno join c on sccnoccno where ssno in (select sno from sc where score<60)
三函数
1 求男学生总数均年龄
select count(*) as '总数'avg(sage) as '均年龄' from s where ssex'
2 统计选修课程学生数
select DISTINCT count(*) as '数' from sc
3 计算C1号课程学生均成绩总分高分低分
select avg(score) as '均成绩'sum(score) as '总分'max(score) as '高
4 查询选修C1号课程学生高分数
select max(score) as '高分' from sc where cno'c1'
5 求课程号相应选课数
select cno as '课程号'count(sno) as '数' from sc group by cno
6 查询选修3门课程学生学号
select sno from sc group by sno having count(sno)>3
7 查询选修C3课程学生学号成绩分数降序排列
select snoscore from sc order by score DESC
8 查询全体学生情况结果系升序排列系中学生年龄降序
select * from s order by sdept ASCsage
四嵌套查询
1 求C2号课程成绩高李勇学生学号成绩
select snoscore from sc where score>(select score from sc where sno(select sno from s where sname'李勇') and cno'c2')
2 查询刘晨系学学生
select * from s where sdept in (select sdept from s where sname'刘晨')
3 查询选修课程名信息系统学生学号姓名
select snosname from s where sno in (select sno from sc where cno in(select cno from c where cname'信息系统'))
带Any all谓词子查询
4 查询系中信息系中某学生年龄学生姓名年龄
select sname sage from s where sage5 查询系中信息系学生年龄学生姓名年龄
select sname sage from s where sage
6 检索均成绩高学生学号
select top 1sno from sc group by sno order by avg(score) DESC
带Exist谓词子查询
7 检索学课程号C2课程学生学号姓名（谓词in）
select snosname from s where sno in (select sno from sc where cno'c2')
8 检索学课程号C2课程学生姓名年龄（谓词all not in）
select snosname from s where sno not in (select sno from sc where cno'c2')
9 查询选修全部课程学生姓名
select sname from s where not exists(select * from c where not exists(select * from sc where sno ssno and cnoccno))
10 查询少选修学生95002选修全部课程学生号
select snamesno from s where sno '95002' and not exists (select * from sc where sno'95002' and not exists (select * from c where ssnoscsno and sccnoccno))
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档
create table S
(SNO CHAR(6) NOT NULL
SNAME CHAR(8) NOT NULL
SSEX CHAR(2)
SAGE SMALLINT
SDEPT CHAR(10)
PRIMARY KEY (SNO)
CHECK (SSEX'男' or SSEX'女'))
create table c
(CNO char(6)not null
CNAME char(10) not null
CT SMALLINT
TNO CHAR(6)
PRIMARY KEY (CNO)
FOREIGN KEY (TNO) REFERENCES T(TNO))
create table SC
(SNO CHAR (6)
CNO CHAR (6)
SCORE SMALLINT
FOREIGN KEY(SNO) REFERENCES S(SNO)
FOREIGN KEY(CNO) REFERENCES C(CNO)
CHECK(SCORE>0 AND SCORE<100))
CREATE UNIQUE INDEX IX_S ON S(SNO)
insert into S VALUES('95001''李勇''男'20'计算机')
insert into S VALUES('95002''刘晨''女'19'信息')
insert into S VALUES('95003''王敏''女'18'数学')
insert into S VALUES('95004''张立''男'19'信息')
insert into S VALUES('95005''欧阳锋''男'19'信息')
insert into S VALUES('95006''欧阳震华''男'24'数学')
insert into S VALUES('95007''刘秋明''女'19'计算机')
INSERT INTO C VALUES('C1''数库'72'T1')
INSERT INTO C VALUES('C2''数学'80'T5')
INSERT INTO C VALUES('C3''信息系统'30'T3')
INSERT INTO C VALUES('C4''操作系统'60'T2')
INSERT INTO C VALUES('C5''数结构'80'T1')
INSERT INTO C VALUES('C6''计算机网络'50'T3')
INSERT INTO C VALUES('C7''C语言'60'T4')
INSERT INTO C VALUES('C8''DB_Design'50'T4')
INSERT INTO T VALUES('T1''李力''男''教授')
INSERT INTO T VALUES('T2''王''女''讲师')
INSERT INTO T VALUES('T3''刘伟''男''讲师')
INSERT INTO T VALUES('T4''张雪''女''教授')
INSERT INTO T VALUES('T5''张兰''女''副教授')
INSERT INTO T VALUES('T6''王''男''助教')
INSERT INTO SC VALUES('95001''C1'92)
INSERT INTO SC VALUES('95001''C2'85)
INSERT INTO SC VALUES('95001''C3'NULL)
INSERT INTO SC VALUES('95001''C4'NULL)
INSERT INTO SC VALUES('95001''C5'NULL)
INSERT INTO SC VALUES('95001''C6'NULL)
INSERT INTO SC VALUES('95001''C7'NULL)
INSERT INTO SC VALUES('95001''C8'NULL)
INSERT INTO SC VALUES('95002''C2'90)
INSERT INTO SC VALUES('95002''C3'80)
INSERT INTO SC VALUES('95003''C5'50)
INSERT INTO SC VALUES('95004''C2'66)
INSERT INTO SC VALUES('95004''C3'NULL)
INSERT INTO SC VALUES('95005''C7'NULL)
INSERT INTO SC VALUES('95007''C1'80)
INSERT INTO SC VALUES('95007''C3'48)
INSERT INTO SC VALUES('95007''C4'93)
．简单查询
查询全体学生姓名学号系
select snamesnosdept from s
查询全体学生详细记录
select * from s
查询全体学生姓名出生年份
select snameyear(getdate())sage as 'birthday' from s
查询选修课程学生学号
select sno from sc
查询年龄20岁学生姓名年龄
select snamesage from s where sage<20
查询考试成绩格学生学号
select sno from sc where score<60
查询年龄1820间学生姓名系年龄
select snamesdept sage from s where sage between 18 and 20
查询年龄2023间学生姓名系年龄
select snamesdept sage from s where sage not between 20 and 23
查询信息系数学系计算机系学生姓名性
select sname ssex from s where sdept'信息' or sdept'数学' or sdept'计算机'
查询信息系数学系计算机系学生姓名性
select sname ssex from s where sdept not in ('信息''数学''计算机')
查询学号95001学生详细情况
select * from s where sno'95001'
查询姓刘学生姓名学号性
select snamesnossex from s where sname like '刘'
查询姓欧阳命名三汉字学生姓名
select sname from s where sname like '欧阳_'
查询名字中第2字阳字学生姓名学号
select snamesno from s where sname like '_阳'
查询姓刘学生姓名
select sname from s where sname like '[^刘]'
查询DB_Design课程课程号学时
select cnoct from c where cname'DB_Design'
查询DB_开头倒数第3字符i课程详细情况
select * from c where cname like 'DB_i__'
查询缺少成绩学生学号相应课程号
select snocno from sc where score is null
查询成绩学生学号课程号(where grade is not null)
select snocno from sc where score is not null
查询选修课程号C2C4课程学生学号
select sno from sc where cno'c2' or cno'c4'
查询课程号C2C4C5课程求显示出课程号课程名（求in运算符）
select cnocname from c where cno in ('c2''c4''c5')
二连接查询
1） 查询选修C2号课程学生学号姓名
select snosname from s where sno in (select sno from sc where cno'c2')
2）查询选课学生学号姓名选课名称成绩
select ssnossnameccnamescscore from scsc
3）查询学生选课成绩情况求显示学生姓名选课名称成绩查询结果存放新数表new_table
select ssnameccnamescscore into new_table from scsc
4）查询选修课程名C语言学生学号姓名
select snosname from s where sno in (select sno from sc where cno in (select cno from c where cname'C语言'))
5）查询选修课程名数结构学生姓名课程成绩
select DISTINCT SSNOscscore from s join sc on ssnoscsno where cno in (select cno from c where cname'数结构')
6）查询计算机系学生选课成绩情况求显示学生学号姓名课程号课程名称成绩
select ssnossnameccnoccnamescscore from s join sc on ssnoscsno join c on sccnoccno where ssno in (select sno from s where sdept'计算机')
7）查询选修C2号课程成绩90分学生学号姓名
select snosname from s where sno in(select sno from sc where score>90 and cno'c2')
8）查询刘晨年龄学生姓名性年龄刘晨年龄
select snamessexsage from s where sage>(select sage from s where sname'
9）查询选修课程未参加考试学生学号姓名课程号课程名
select ssnossnameccnoccname from sc where sno in(select DISTINCT sno from sc where score is null)
10）查询选修课程考试成绩格学生学号姓名课程号课程名成绩
select ssnossnameccnoccnamescscore from s join sc on ssnoscsno join c on sccnoccno where ssno in (select sno from sc where score<60)
三函数
1 求男学生总数均年龄
select count(*) as '总数'avg(sage) as '均年龄' from s where ssex'
2 统计选修课程学生数
select DISTINCT count(*) as '数' from sc
3 计算C1号课程学生均成绩总分高分低分
select avg(score) as '均成绩'sum(score) as '总分'max(score) as '高
4 查询选修C1号课程学生高分数
select max(score) as '高分' from sc where cno'c1'
5 求课程号相应选课数
select cno as '课程号'count(sno) as '数' from sc group by cno
6 查询选修3门课程学生学号
select sno from sc group by sno having count(sno)>3
7 查询选修C3课程学生学号成绩分数降序排列
select snoscore from sc order by score DESC
8 查询全体学生情况结果系升序排列系中学生年龄降序
select * from s order by sdept ASCsage
四嵌套查询
1 求C2号课程成绩高李勇学生学号成绩
select snoscore from sc where score>(select score from sc where sno(select sno from s where sname'李勇') and cno'c2')
2 查询刘晨系学学生
select * from s where sdept in (select sdept from s where sname'刘晨')
3 查询选修课程名信息系统学生学号姓名
select snosname from s where sno in (select sno from sc where cno in(select cno from c where cname'信息系统'))
带Any all谓词子查询
4 查询系中信息系中某学生年龄学生姓名年龄
select sname sage from s where sage
select sname sage from s where sage
6 检索均成绩高学生学号
select top 1sno from sc group by sno order by avg(score) DESC
带Exist谓词子查询
7 检索学课程号C2课程学生学号姓名（谓词in）
select snosname from s where sno in (select sno from sc where cno'c2')
8 检索学课程号C2课程学生姓名年龄（谓词all not in）
select snosname from s where sno not in (select sno from sc where cno'c2')
9 查询选修全部课程学生姓名
select sname from s where not exists(select * from c where not exists(select * from sc where sno ssno and cnoccno))
10 查询少选修学生95002选修全部课程学生号
select snamesno from s where sno '95002' and not exists (select * from sc where sno'95002' and not exists (select * from c where ssnoscsno and sccnoccno))
文档香网(httpswwwxiangdangnet)户传
《香当网》用户分享的内容，不代表《香当网》观点或立场，请自行判断内容的真实性和可靠性！
该内容是文档的文本内容，更好的格式请下载文档
