SQLServer2012书学习七八统计数据与视图
时间：2021-07-01 10:21:17
帮助过：3人阅读
USE XK
2 GO
3 --查询班级表内容
4 SELECT * FROM class
5 GO
6 --更新成绩信息
7 UPDATE elective
SET course_score
=3 WHERE course_id
=‘11111116‘ OR course_id
=‘11111114‘
8 GO
9 --查询班级名和部门编号
10 SELECT class_name ,department_id
FROM class
11 GO
12 ----查询数据时top 和5需要分开
13 SELECT TOP 5 * FROM elective
14 GO
15 --修改课程表本科目选的人数 \
16 UPDATE course
SET student_num
=(
SELECT COUNT(course_id)
FROM elective
WHERE course.course_id
=elective.course_id )
17 GO
18 ----或者
19 DECLARE @id int
20 SET @id=11111111
21 WHILE @id<11111116
22 BEGIN
23 UPDATE course
SET student_num
=(
SELECT COUNT(course_id)
FROM elective
WHERE course_id
=@id )
WHERE course_id
=@id
24 SET @id+=1
25 END
26 GO
27 --查出选修表中选修人数最多和最少的三门课程
28
29
30 SELECT TOP 3 course_name,student_num
31 FROM course
ORDER BY student_num
ASC
32
33 SELECT TOP 3 course_name,student_num
34 FROM course
ORDER BY student_num
DESC
35 GO
36 --修改课程时间
37 SELECT teacher_name
AS ‘教师姓名‘ ,course_name
AS ‘课程名称‘
38 FROM course
39 WHERE course_time
=‘周四晚上‘
40 GO
41 --SUNSTRING 从1 截取2 截取12 0,2 截取 01 字符串index没0,相当于只截取1
42 SELECT * FROM student
43 WHERE SUBSTRING( student_name,
1,
2)
=‘王‘ OR SUBSTRING( student_name,
1,
2)
=‘小明‘
44 GO
45 --查询软件1班选修情况
46 SELECT course_name,elective.student_id,student_name,class_name,course_time
FROM student
47 JOIN class
ON class.class_id
=student.class_id
AND class.class_name
=‘软件1班‘
48 JOIN elective
ON elective.student_id
=student.student_id
49 JOIN course
ON course.course_id
=elective.course_id
50 --查询小猪的选修情况
51 SELECT course_name,elective.student_id,student_name,class_name,course_time
FROM student
52 JOIN class
ON class.class_id
=student.class_id
53 JOIN elective
ON elective.student_id
=student.student_id
54 JOIN course
ON course.course_id
=elective.course_id
WHERE student_name
=‘小猪‘
55 GO
56 --查询出没有人选择的课程
57 SELECT * FROM course
WHERE course_id
NOT IN(
SELECT DISTINCT course_id
FROM elective)
第八章 创建于管理视图
SQLServer2012书学习七八统计数据与视图
标签：code 视图 top nio set count lse min sqlserver
