SQL知识复习整理

/***************************************************
****SQL数据库知识
****Author：liuqijun
****欢迎转载
****************************************************/
USE MASTER
GO
IF DB_ID(N'StudentManager') IS NOT NULL
 DROP DATABASE StudentManager
GO
 CREATE DATABASE StudentManager
GO
 
USE StudentManager
GO
IF EXISTS(SELECT  * FROM SYSOBJECTS WHERE NAME = 'Student')
 DROP TABLE Student
GO
CREATE TABLE Student --创建学生表
(
 Stu_ID int primary key identity(1,1), --学生编号:主键 自增列
 StuName varchar(20) not null          --学生姓名:不允许为空
)
GO
--插入数据--------------------------
INSERT INTO Student(StuName)VALUES (N'张三')
INSERT INTO Student(StuName)VALUES (N'李四')
INSERT INTO Student(StuName)VALUES (N'王五')
--查询数据-----------------------------
--SELECT * FROM Student

USE StudentManager
GO
IF EXISTS(SELECT  * FROM SYSOBJECTS WHERE NAME = 'Course')
 DROP TABLE Course
GO
CREATE TABLE Course --创建课程表
(
 Course_ID int primary key identity(1,1), --课程编号:主键 自增列
 Course_Name varchar(20) not null         --科目名称:不允许为空
)
GO
INSERT INTO Course(Course_Name) values(N'语文')
INSERT INTO Course(Course_Name) values(N'英语')
INSERT INTO Course(Course_Name) values(N'数学')
INSERT INTO Course(Course_Name) values(N'历史')
--SELECT * FROM Course

USE StudentManager
GO
IF EXISTS(SELECT  * FROM SYSOBJECTS WHERE NAME = 'Stu_Score')
 DROP TABLE Stu_Score
GO
CREATE TABLE Stu_Score --创建学生成绩表
(
 ID INT PRIMARY KEY IDENTITY(1,1),        --学生成绩编号 主键 唯一 自增列
 Stu_ID INT NOT NULL,                     --学生编号:不允许为空
 Course_ID INT NOT NULL,          --科目编号:不允许为空
 Score DECIMAL(5,2)                       --学生成绩:允许为空
)
GO

INSERT INTO Stu_Score
SELECT  1,2,81 UNION ALL
SELECT 1,3,52 UNION ALL
SELECT 1,4,83 UNION ALL
SELECT 2,1,50 UNION ALL
SELECT 2,2,71 UNION ALL
SELECT 2,3,72 UNION ALL
SELECT 2,4,73 UNION ALL
SELECT 3,1,90 UNION ALL
SELECT  3,2,91
go
--SELECT * FROM Stu_Score

--1、分别给学生成绩表建立与学生表、课程表的外键约束
ALTER TABLE Stu_Score
ADD CONSTRAINT FK_Student_Stu_ID FOREIGN KEY (Stu_ID)
    REFERENCES Student (Stu_ID)    ---建立与学生表的外键关系
   
    ALTER TABLE Stu_Score
ADD CONSTRAINT FK_Course_Course_ID FOREIGN KEY (Course_ID)
    REFERENCES Course (Course_ID)  ---建立与课程表的外键关系
   
--2、给课程表建立Check约束，课程名称不允许一致
ALTER TABLE Course
ADD CONSTRAINT uq_Course_Course_Name unique(Course_Name)
--SELECT * FROM Course
--insert into Course(Course_Name) values('英语') --测试约束

--3、求每个学生的单门成绩、总成绩、平均成绩
select student.stuName as '学生姓名',
    ISNULL(sum(case Course_Name when '语文' then Stu_Score.Score end),0) as '语文',
    sum(case Course_Name when '英语' then ISNULL(Stu_Score.Score,0) end) as '英语',
    sum(case Course_Name when '数学' then ISNULL(Stu_Score.Score,0) end) as '数学',
    sum(case Course_Name when '历史' then ISNULL(Stu_Score.Score,0) end) as '历史',
    sum(ISNULL(Stu_Score.Score,0)) as '总成绩',
    avg(ISNULL(Stu_Score.Score,0)) as '平均成绩'
    from Course
left join Stu_Score on Course.Course_ID=Stu_Score.Course_ID
left join Student on Student.Stu_ID=Stu_Score.Stu_ID
group by student.stuName

---查询结果如下图：

--select ISNULL(NULL,0) --思考:如何将上面查询结果集中的NULL值转换为0,此问题上述sql中已经解决

--4、将各们科目的参考人数，及格人数、总成绩、平均成绩、及格率统计出来
select a.Course_Name as '科目名称',
    Sum(a.SumStuCount) as '参考人数',
    Sum(b.Pass_StuCount) as '及格人数',
    Sum(a.SumScore)    as '总成绩',
    Sum(a.SumScore)/Sum(a.SumStuCount) as '平均成绩' --这里就不再考虑被除数为零的情况了
from (
 select Course.Course_ID,
 Course.Course_Name as Course_Name,
 count(Student.Stu_ID) as SumStuCount,
 Sum(Stu_Score.Score) as SumScore
 from Stu_Score
 left join Course on Stu_Score.Course_ID=Course.Course_ID
 left join Student on Student.Stu_ID=Stu_Score.Stu_ID
 group by Course.Course_ID,Course.Course_Name
) a
left join
(
 ---求及格人数
 select Course.Course_ID,
 count(Student.Stu_ID) as Pass_StuCount
 from Stu_Score
 left join Course on Stu_Score.Course_ID=Course.Course_ID
 left join Student on Student.Stu_ID=Stu_Score.Stu_ID
 where Stu_Score.Score>60
 group by Course.Course_ID
) b
on a.Course_ID=b.Course_ID
group by a.Course_Name

---查询结果如下图