SQL server 查询语句

1.1 索引 

--(1) 为课程表的课程名称列创建唯一性索引course_cname_ind。
create unique index course_cname_ind on Course(Cname)

--(2) 为选课表的成绩列创建一个非聚集索引，命名为SC_grade_index。
create index SC_grade_index on SC(Grade)

--(3) 为选课表的学号列和课程号列创建一个复合唯一索引，命名为SC_snocno_ind。
create unique index SC_snocno_ind on SC(Sno,Cno)
--(4) 使用系统存储过程sp_helpindex查看SC表上的索引信息
sp_helpindex SC

--(5) 使用系统存储过程sp_rename将索引SC_grade_index更名为SC_grade_ind。
sp_rename 'SC.SC_grade_index','SC_grade_ind'

--(6) 删除索引course_cname_ind。
drop index Course.course_cname_ind

1.2 基本查询 

--(1) 查询每门课程的课程编号、课程名称和学分信息，并显示“课程编号”“课程名称”“学分”列标题。
select Cno as 课程编号,Cname as 课程名称,Credit as 学分 from Course

--(2) 从学生表中检索学生的学号、姓名、性别和所在院系。只要求显示前10%的学生信息。
select top 10 percent Sno,Sname,Ssex,Sdept from Student

--(3) 查询所有选课成绩提高10%后的信息，显示字段为“学号”“课程号”“成绩”和“提高后成绩”。
 select Sno as 学号,Cno as 课程号,Grade as 成绩,Grade*1.1 as 提高后成绩 from SC

 -- (4) 使用INTO子句创建一个“助教”职称教师的名为Teacher_assistant的新表，包含教工编号、教工姓名、所在院系字段。
 select Tno,Tname,Tdept into Teacher_assistant from Teacher where Prof='助教'

 --(5) 找出所有姓“李”或“刘”的学生信息。
 select * from Student where Sname like '李%' or Sname like '刘%'

 --(6) 查询年龄在22岁以下的女学生的姓名、年龄和院系信息。
 select Sname,Sage,Sdept from Student where Sage < 22 and Ssex='女'

 -- (7) 查询学期为1、3、5的课程信息。
 select * from Course where Semester in (1,3,5)

 --(8) 查询年龄在21-23(包括21和23)的学生的姓名和年龄，并按年龄从低到高排列。
 select Sname,Sage from Student where Sage between 21 and 23 order by Sage asc

 --(9) 统计所有教师人数。
  Select COUNT(*) from Teacher

 --(10)按学分降序显示课程的名称、学分和学期，学分相同时按学期升序排列。
 select Cname,Credit,Semester from Course order by Credit desc,Semester asc

2.1 基本查询和分组查询

--1.查询课程名称中第二个字不是'据'的课程号、课程名，学分。
select Cno,Cname,Credit from Course where Cname not like '_据%'

-- 2.查询有先修课程的课程信息。
select * from Course where Cpno is not NULL

-- 3.统计既不属于'计算机系'，也不属于'数学系'的学生信息。
select * from Student where Sdept not in ('计算机系','数学系')

-- 4.统计参与选修课程的学生人数。
select count(distinct sno) from SC

-- 5.统计每个院系的学生人数，显示“院系名称”和“人数”。
select Sdept as 院系名称,COUNT(*) as 人数 from Student group by Sdept

-- 6.查询每门课程的学生平均成绩，显示"课程号"和"平均成绩"。
select cno 课程号,avg(grade) 平均成绩 from sc group by cno;

--7. 统计女同学的每一年龄组（超过1人）有多少人，显示“年龄”和“人数”。
select sage 年龄,count(*) 人数 from student where ssex = '女' group by sage having count(*) > 1;

-- 8.查询平均分高于80分的课程的编号和平均成绩。
select Cno,AVG(Grade) from SC group by Cno having AVG(Grade)>80

2.2集合查询 

--（1）查询选修了101课程或选修了201课程的学生学号。
(select Sno from SC where Cno=101)
union
(select Sno from SC where Cno=201)

-- （2）查询既选修了101课程又选修了201课程的学生学号。

(select Sno from SC where Cno=101)
intersect
(select Sno from SC where Cno=201)
-- （3）查询选修了101课程但没有选修201课程的学生学号。
(select Sno from SC where Cno=101)
except
(select Sno from SC where Cno=201)

2.3 连接查询

（1）查询每个学生的学号、姓名、选修的课程名及成绩。
select student.sno,student.sname,course.cname,sc.grade from student 
join sc on student.sno = sc.sno join course  on sc.cno = course.cno;

-- （2）检索选修了“数学”课程的学生学号和姓名（要求3个表连接，学生表在前）。
select s.sno,s.sname from student s 
join sc on s.sno = sc.sno join course c on sc.cno = c.cno where c.cname = '数学';

--（3）查询课程的先修课情况，使用别名显示“课程编号”、“课程名称”、“先修课名称”。
--（course 自连接，使用的别名c1，c2；不需要外连接）
select c1.cno 课程编号, c1.cname 课程名称, c2.cname 先修课名称 from course c1 
join course c2 on c1.cpno = c2.cno;

-- （4）检索所有学生的学号及其选修的课程名和成绩（没有参加选修课的学生学号也检索在结果中）。
select s.sno,c.cname,sc.grade from student s 
left join sc on s.sno = sc.sno left join course c on sc.cno = c.cno;

--（5）检索所有课程的课程编号、课程名称和平均成绩（没有被选修课程也在检索结果中）。
SELECT c.cno, c.cname, AVG(SC.Grade) FROM SC 
RIGHT JOIN Course c ON SC.cno = c.Cno GROUP BY c.cno,c.cname; 

3.1嵌套查询

--（1）检索选修了“信息系统”课程的学生学号和姓名。
select Sno,Sname from Student 
where Sno in(select Sno from SC where Cno=(Select Cno from Course where Cname='信息系统'))

--（2）查询选修'101'号课程且成绩在80分以上（含80分）的所有学生的学号和姓名。
select Sno,Sname from Student 
where Sno in(select Sno from SC where Cno=(Select Cno from Course where Cno='101') and Grade >= 80)

select sno,sname from student where sno in ( select sno from sc where cno = '101' and grade >= 80 );

--（3）查询非计算机系中比计算机系所有学生年龄都小的学生姓名及年龄。
select Sname,Sage from Student where 
Sdept!='计算机系' and Sage < all(select Sage from Student where Sdept = '计算机系')
--查询非计算机系中比计算机系任意一个学生年龄小的学生姓名和年龄
select Sname,Sage from Student where 
Sdept!='计算机系' and Sage < all(select Sage from Student where Sdept = '计算机系')

--（5）查询与“刘晨”在同一个系学习的学生，显示学生的所有信息（用 SELECT * 的方式）。
select * from Student where Sdept=(select Sdept from Student where Sname='刘晨')
--（6）列出选修了第2学期全部课程的学生学号和sname(exists 子查询中返回*或1)。
select s.sno,s.sname from Student s where not exists (select * from Course c where Semester=2 

and not exists (select * from sc where s.Sno=sc.Sno and c.Cno =sc.cno))
--（7）找出"吴霞"所在系的“学生人数”(使用 COUNT(*)的形式)。
select COUNT(*) from Student where Sdept=(Select Sdept from Student where Sname='吴霞')

SELECT COUNT(*) 学生人数 FROM student WHERE sdept = ( SELECT sdept FROM student WHERE sname = '吴霞' );
--（8）列出"王萍"老师所带的课程名及所在的系名。
select Cname,Tdept from Teacher,Course where Tname='王萍' 
and Course.Tno=(select Tno from Teacher where Tname='王萍')

SELECT C.cname,t.tdept FROM course C JOIN teacher t ON C.tno = t.tno
AND t.tname = '王萍'
--（9）找出所有没被学生选修的课程号（DISTINCT如果可以不用则不用）。
SELECT cno FROM course WHERE cno not in( SELECT cno FROM sc )
--（10）找出选修了课程“高等数学”的“学生人数”（只显示“学生人数”一个字段）。
 SELECT COUNT(*) 学生人数 FROM sc WHERE cno = ( SELECT cno FROM course WHERE cname = '高等数学' )
--（11）统计每一年龄选修课程的“学生人数”（DISTINCT如果可以不用则不用）。
 SELECT sage,COUNT(*) 学生人数 FROM student WHERE sno IN ( SELECT sno FROM sc) GROUP BY sage

3.2更新查询

-- （1）将“高等数学”课程的成绩提高2分(不用+=符号)。
UPDATE SC SET grade=grade+2 WHERE CNO = ( SELECT CNO FROM course WHERE cname='高等数学' )
--（2）在sc表中删除"数学系"不及格学生的修课记录(删除命令用 DELETE FROM 表名方式)。
DELETE FROM SC WHERE sno = ANY( SELECT sno FROM student WHERE sdept='数学系' ) AND grade<60;

附：创建库输入基础数据

IF EXISTS (SELECT name FROM SYS.DATABASES WHERE name='学生选课')
    DROP DATABASE 学生选课
GO
CREATE DATABASE 学生选课
GO
USE 学生选课
-- DROP TABLE SC,course,student
-- DROP TABLE course
GO

--创建教师表
CREATE TABLE Teacher(
Tno CHAR(3) NOT NULL PRIMARY KEY,
Tname VARCHAR(10) NOT NULL,
Tsex CHAR(2),
Sbirthday DATE,
Prof VARCHAR(6),
Tdept VARCHAR(10)
)
GO

--创建学生表
CREATE TABLE Student(
Sno CHAR(5) NOT NULL PRIMARY KEY,
Sname VARCHAR(10) NOT NULL,
Ssex CHAR(2),
Sage TINYINT,
Sdept VARCHAR(10)
)
GO

--创建课程表
CREATE TABLE Course(
Cno CHAR(3) NOT NULL PRIMARY KEY,
Cname VARCHAR(20) NOT NULL,
Cpno CHAR(3),
Credit TINYINT,
Semester TINYINT,
Tno CHAR(3)
)
GO

--创建选课表
CREATE TABLE SC(
Sno CHAR(5) NOT NULL,
Cno CHAR(3) NOT NULL,
Grade INT
)
GO

-- 插入教师表数据
-- SP_HELP Teacher
INSERT INTO Teacher 
VALUES ('104','李诚','男','1958/12/2','副教授','计算机系')
,('256','张旭','男','1969/3/12','讲师','数学系')
,('125','王萍','女','1972/5/5','助教','计算机系')
,('331','刘冰','女','1977/8/14','助教','信息系')
GO    
-- SELECT * FROM Teacher

-- 插入学生表数据
-- SP_HELP Student
INSERT INTO Student 
VALUES('95001','刘超华','男',22,'计算机系')
,('95002','刘晨','女',21,'信息系')
,('95003','王敏','女',20,'数学系')
,('95004','张海','男',23,'数学系')
,('95005','陈平','男',21,'数学系')
,('95006','陈斌斌','男',28,'数学系')
,('95007','刘德虎','男',24,'数学系')
,('95008','刘宝祥','男',22,'计算机系')
,('95009','吕翠花','女',26,'计算机系')
,('95010','马盛','男',23,'数学系')
,('95011','吴霞','男',22,'计算机系')
,('95012','马伟','男',22,'数学系')
,('95013','陈冬','男',18,'信息系')
,('95014','李小鹏','男',22,'计算机系')
,('95015','王娜','女',23,'信息系')
,('95016','胡萌','女',23,'计算机系')
,('95017','徐晓兰','女',21,'计算机系')
,('95018','牛川','男',22,'信息系')
,('95019','孙晓慧','女',23,'信息系')
GO
-- SELECT * FROM Student

-- 插入课程表数据
-- SP_HELP Course
INSERT INTO Course 
VALUES('101','数据库','105',5,4,'125')
,('104','操作系统','106',1,2,'104')
,('105','数据结构','107',1,3,'104')
,('106','数据处理',NULL,1,2,'331')
,('107','C语言','106',3,1,'125')
,('108','Java',NULL,3,3,'256')
,('109','网页制作',NULL,2,5,'125')
,('110','c++',NULL,3,4,'104')
,('111','网络编程',NULL,2,5,'104')
,('201','高等数学',NULL,1,1,'256')
,('301','信息系统','101',1,3,'331')
GO
-- SELECT * FROM Course WHERE Cpno IS NULL

-- 插入选课表数据
--SP_HELP SC
INSERT INTO SC 
VALUES('95001','101',87)
,('95001','201',76)
,('95001','301',79)
,('95001','104',80)
,('95001','105',81)
,('95001','106',82)
,('95001','107',67)
,('95002','101',89)
,('95002','201',81)
,('95004','101',83)
,('95004','201',56)
,('95005','101',89)
,('95006','101',54)
,('95006','201',77)
,('95010','101',56)
,('95013','301',80)
,('95013','105',90)
,('95014','201',NULL)
,('95015','201',NULL)
GO
--SELECT * FROM SC