学过、学全、没学过、没学全例题

建表SQL

最近遇到一些学过、没学过、学全、没学全这样的题目，总结一下各种方法。

--学生表
create table Student(
   Stu_no       char(8) ,    --学号
   Stu_name     nvarchar(10) ,--姓名
   Stu_age      tinyint ,     --年龄 
   Stu_sex       nchar(2) check(stu_sex in('男','女')), --性别
   Stu_dept      nvarchar(20),--专业
   CONSTRAINT pk_sno PRIMARY KEY (Stu_no));


--课程表
 create table  Course(
    Cou_no         char(3),      --课程号
    Cou_name       nvarchar(30),  --课程名
    Cou_pno        char(3),      --先修课
    Cou_teacher     nvarchar(10),--老师姓名
    CONSTRAINT pk_cno PRIMARY KEY (Cou_no));

--选课表 
create table SC(
    Stu_no      char(8) ,   --学号
    Cou_no      char(3),    --课程号
    Grade       decimal(5,1),--成绩 
    CONSTRAINT pk_sc PRIMARY KEY (Stu_no,Cou_no), --主健
    foreign key(Stu_no) references Student(Stu_no),--外健
    foreign key(Cou_no) references Course(Cou_no), --外健
    check ((grade is null) or (grade between 0 and 100)));

-------------------------------
INSERT into Student VALUES('20180001','全学过',20,'男','计算机');
INSERT into Student VALUES('20180002','没学过',19,'女','计算机');
INSERT into Student VALUES('20180003','没学全一',18,'男','信息');
INSERT into Student VALUES('20180004','没学全二',19,'男','信息');



INSERT into Course VALUES('a01','计算机网络','a04','李琳');
INSERT into Course VALUES('a02','数据库原理','a04','李琳');
INSERT into Course VALUES('a03','操作系统','a04','张勇');
INSERT into Course VALUES('a04','数据结构','a04','张娜');



INSERT into SC VALUES('20180001','a01',92.5);
INSERT into SC VALUES('20180001','a02',85);
INSERT into SC VALUES('20180002','a03',88);
INSERT into SC VALUES('20180002','a04',90);
INSERT into SC VALUES('20180003','a01',80);
INSERT into SC VALUES('20180003','a03',65);
INSERT into SC VALUES('20180004','a02',78);
INSERT into SC VALUES('20180004','a04',88);

1 查询学过李琳老师讲授课程的学生学号，姓名，成绩

(1)多表连接

--1.1学过---多表连接
select  student.stu_no ,stu_name,grade
from student,course,sc
where student.stu_no = sc.stu_no 
AND course.cou_no = sc.cou_no
AND course.Cou_teacher='李琳';

(2)不相关子查询 in

--1.2学过--不相关子查询（注意：投影的属性不在子查询表中，
-- 即查询的属性列，和筛选条件的属性不在一张表
select  student.stu_no ,stu_name,grade
from student,sc
where student.stu_no = sc.stu_no 
AND sc.cou_no in (select Cou_no
							  from Course
							  where course.Cou_teacher='李琳')

(3)相关子查询exists

--1.3学过----相关子查询
select  student.stu_no ,stu_name,grade
from  sc,student
where  sc.stu_no = student.stu_no
AND EXISTS
         (select * 
		 from course
		 where cou_no = sc.Cou_no   --相关子查询
		 AND cou_teacher = '李琳')

2 没学过李琳老师讲授课程的学生学号，姓名

(1)不相关子查询 not in

--2.1 没学过-- not  in

--这是一个错误的示例，排除时以选课记录为单位
-- 例如 没选全这个学生有两门选科，会留下一门不是李琳老师的选课记录。
select  student.stu_no ,stu_name,grade
from student,SC
where sc.stu_no = student.stu_no AND
sc.cou_no  not in(--sc.cou_no 以选课记录为单位
										select cou_no 
										from course
										where cou_teacher = '李琳')

-- 以学生为单位进行筛选--不相关子查询
select  student.stu_no ,stu_name,grade
from student,sc
where student.stu_no = sc.stu_no
AND student.stu_no not in(--以下是学过李琳老师课程的学号,
											--student.stu_no not in  是以学生为单位进行筛选
								select stu_no 
								from course,sc
								where course.cou_no = sc.Cou_no
								AND cou_teacher= '李琳')

(2)相关子查询 not exists

--2.2没学过 exists 相关子查询
--以学生为单位进行筛选
select  student.stu_no, stu_name
from  student     
where not exists(
						select  * 
						from sc,course
						where sc.cou_no = course.cou_no
						AND sc.stu_no = student.stu_no
						AND cou_teacher = '李琳'
						)

(3)集合的差

--2.3 没学过  集合的差
----注意筛选时以学生为单位进行
select  stu_no,stu_name
from student
except 
select student.stu_no,stu_name
from student,sc,course
where sc.stu_no = student.stu_no
AND sc.cou_no = course.cou_no
AND Cou_teacher='李琳'

3 学全李琳老师讲授课程学生的学号，姓名，成绩

（1）not exists不相关子查询

--学全
--相关子查询，能查到第一个表里的属性列，条件写在最后一个 not exists中
--至于怎么查出 course_no,cou_name,grade,如果用一个句子表达会很复杂，可以创建一个只含学全的学生表信息
--再把视图和sc、course连接
-- select * from view_all_study_students;
if exists(select  * from sys.views where name='view_all_study_students')
drop view view1
go
create  view view_all_study_students
AS
select student.stu_no ,stu_name
from  student
where not exists (select * 
						 from course
						where cou_teacher ='李琳'
						AND not exists(
												select  * 
												from sc
												where sc.stu_no = student.stu_no
												AND sc.cou_no = course.cou_no)
												)
if exists (select * from  sys.views where name='view_s_c_sc')
drop view view_s_c_sc
go
create view view_s_c_sc AS 
select view_all_study_students.Stu_no,view_all_study_students.Stu_name,cou_name,grade
from view_all_study_students,course,sc
where view_all_study_students.Stu_no= sc.Stu_no
AND sc.Cou_no= course.cou_no
go
select * from view_s_c_sc;

4 没学全李琳老师讲授课程的学生姓名、学号

（1）全部学生信息 except 学全的学生信息

略

（2）找出学生学习李琳老师的课程数目，李琳老师教授课程的数目，比较大小

--学过，没学全均为条件
select  student.stu_no 
from student
where stu_no in (select distinct stu_no --学过
						 from sc,course 
						 where sc.cou_no = course.cou_no
						 AND cou_teacher = '李琳'
						 group by stu_no 
						 having count(*)<
													(select count(*)
													from course
													where cou_teacher='李琳'))--没学全

5 SQL SERVER 使用中遇到的问题

（1） 报错将截断二进制数据或字符串

应考虑设置的字段大小是不是太小了，可以使用VARCHAR

（2）语法错误：create view必须是批处理中仅有的语句

原因： 你可能在这段代码之前还有其他的语句是同时在处理。

解决办法：1、可以在这段代码的前一行加上GO，在这段代码结束后一行加上GO

​ 2、在新窗口单独执行这段代码

(3)GO 的用法

用来提交T-SQL语句的一个标志

每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行

GO的意思 是 分批处理语句 有加这个 GO ，就执行GO 行的代码，执行后再执行接下来的代码……