SQLServer2012书学习七八九 统计数据、视图、索引

第七章  查询与统计数据

　　目标： 了解SELECT 语句的构成、通配符和聚合算法的使用方法、SELECT语句中各子句使用方法、子查询和连接查询的使用方法、运用SELECT语句解决实际的查询问题

　　1、SELECT语句的完整语法包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY子句。在查询时还可以使用UNION,EXCEPT将结果集合并或比较，最终合成一个结果集。 

　　　　  select * into new table from table where () group by () order by () asc desc （升序，降序）

　　2、通配符

　　　　%表示任意多个字符，_表示任意一个字符，[]表示方括号中列出的任意一个字符，[^]表示不属于方括号中的任意一个字符

　　　　LIKE '%AB'    返回以AB结尾的任意一个字符    LIKE 'AB%' 返回以AB结束的任意一个字符 　　LIKE '[AB]%'    返回以A或者B开头的任意一个字符　　LIKE 'A[^a]%' 返回以A开头，不以a为第二个字符的任意字符串

　　　　[%] 表示纯粹的%而不表示通配符， LIKE '5[%]'   表示匹配5%的字符

　　3　　聚合函数

　　　　聚合函数也称为统计函数，是对一组值进行计算并且返回一个数值，通畅与GROUP BY 一起使用。除了COUNT外，其他都会忽略NULL值

　　　　　　COUNT(ALL  |  DISTINCT)   求列数目，ALL 去除为空的列，   DISTINCT 去除为空和重复的列

　　　　　　SUN(ALL  |   DISTINCT)  求和

　　　　　　MAX(ALL  |   DISTINCT)   求最大值

　　　　　　MIN(ALL  |   DISTINCT)   求最小值

　　　　　　AVG(ALL  |   DISTINCT)    求平均值

 代码：

USE XK
GO
--查询班级表内容
SELECT * FROM class
GO
--更新成绩信息
UPDATE elective SET course_score=3 WHERE course_id='11111116'    OR course_id='11111114'
GO
--查询班级名和部门编号
SELECT class_name ,department_id FROM class
GO
----查询数据时top 和5需要分开 
SELECT TOP 5 *  FROM elective
GO
--修改课程表本科目选的人数  
UPDATE course SET student_num=(SELECT COUNT(course_id) FROM elective WHERE course.course_id=elective.course_id )  
GO
----或者
DECLARE @id int
SET @id=11111111
WHILE @id<11111116
    BEGIN
        UPDATE course SET student_num=(SELECT COUNT(course_id) FROM elective WHERE course_id=@id ) WHERE course_id=@id
        SET @id+=1
    END
GO
--查出选修表中选修人数最多和最少的三门课程  
 
 
SELECT  TOP 3    course_name,student_num  
FROM course   ORDER BY student_num ASC
 
SELECT TOP 3   course_name,student_num  
    FROM course  ORDER BY student_num DESC
GO
 --修改课程时间
SELECT  teacher_name AS '教师姓名' ,course_name AS '课程名称'
FROM course 
WHERE course_time='周四晚上'
GO
--SUNSTRING 从1 截取2   截取12    0,2  截取 01   字符串index没0,相当于只截取1
SELECT * FROM student 
WHERE SUBSTRING( student_name,1,2)='王' OR SUBSTRING( student_name,1,2)='小明'
GO
--查询软件1班选修情况
SELECT  course_name,elective.student_id,student_name,class_name,course_time FROM student
JOIN class ON class.class_id=student.class_id AND class.class_name='软件1班'
JOIN elective ON elective.student_id =student.student_id
JOIN course ON course.course_id=elective.course_id
--查询小猪的选修情况
SELECT  course_name,elective.student_id,student_name,class_name,course_time FROM student 
JOIN class ON class.class_id=student.class_id  
JOIN elective ON elective.student_id =student.student_id
JOIN course ON course.course_id=elective.course_id WHERE student_name='小猪'
GO
--查询出没有人选择的课程
SELECT * FROM course WHERE course_id NOT IN(SELECT DISTINCT course_id FROM elective)

第八章　　创建与管理视图

　　　　目标：　　理解视图的概念，了解视图的作用，创建视图， 管理视图（修改视图，查看视图信息，重命名，删除） 使用视图

　　　　1、　　视图概述

　　　　　　　　视图是数据库系统中的一个数据库对象，由查询语句构成，从一个或几个基本表中导出的虚拟表。数据库只保存视图的定义，其中的数据是在引用视图时有DBMS系统根据定义自动生成的。视图给用户提供了一种从不同角度使用数据库中数据的重要机制。

　　　　　　　　1 让用户只着重于需要的数据

　　　　　　　　2、增强数据库的安全性

　　　　　　　　3、简化数据库操作

　　　　　　　　当基本表发生变化时，视图的数据也可以保持不变，即内模式，数据库的存储发生变化时，外模式的数据可以不发生改变

　　　　2、　　视图的应用

　　　　　　　　视图包含　　标准视图，索引视图，分区视图三类

　　　　　　　　　1、显示基本表的部分行数据

　　　　　　　　　2、显示基本表的部分列数据

　　　　　　　　　3、复杂查询，由两个基表和若干视图组成的查询

第九章　　创建与管理索引

　　　　目标： 理解索引的作用，了解索引的使用时机。　　根据需要创建索引　　管理索引，重命名，删除，维护索引

　　　　　　1 索引时基于数据表或视图建立，是一个单独的存储在磁盘上的数据库结构，可以加快从表或视图的检索行的速度。

　　　　　　2 在SQL Server中建立索引是在创建表时由系统自动创建或者由用户根据查询进行创建。

　　　　　　3 索引的使用是系统根据查询的需要自动选择调用，不需要用户参与

　　　　1　　索引简介

　　　　　　　　索引类似于书的目录，根据索引进行查找而不需要遍历每一行进行查找。　　当数据较少时无需使用索引，数据量较大并且经常需要搜寻的列需要使用索引。

　　　　2　　索引分类

　　　　　　　　聚集索引，有主键表自带，无主键可以创建聚集索引，要求物理存储与索引顺序相同

　　　　　　　　非聚集索引  只对索引进行逻辑排序，建立索引保存索引列的逻辑排序位置　　在表中最多可以建立250个非聚集索引或者1+249

　　　　　　　　非聚集索引实现方法　　1 PRIMARY KEY和 UNIQUE约束　　独立于约束的索引　　索引视图的非聚集索引　　还有唯一索引。包含索引。索引视图。全文索引。XML索引等　　

　　　　3　　索引的使用时机

　　　　　　　　一方面可以提高查询速度，另一方面，过多的创建索引会占据大量的存储空间

　　　　　　　　　　优点　　1　　创建唯一索引，可以保证数据表每一行数据的唯一性。

　　　　　　　　　　　　　　2　　加快数据的查询速度。

　　　　　　　　　　　　　　3　　实现数据的参照完整性加快表与表之间的连接，

　　　　　　　　　　　　　　4　　实用分组和排序子句显著减少分组和排序的时间　　　　

　　　　　　　　　　缺点　　1　　创建索引和维护索引消耗时间，数据量增加，消耗的时间更多　　

　　　　　　　　　　　　　　2　　索引占据磁盘空间

　　　　　　　　　　　　　　3　　对数据表进行插入删除和修改操作时，索引需要动态的维护，降低了数据的维护速度

　　　　　　　　　　适合建立

　　　　　　　　　　　　　　1　　经常被查询搜索的列

　　　　　　　　　　　　　　2　　ORDER BY 子句使用的列

　　　　　　　　　　　　　　3　　外键或者主键的列

　　　　　　　　　　　　　　4　　列值唯一不重复的列

　　　　　　　　　　不适合

　　　　　　　　　　　　　　1　　查询时很少被引用的列

　　　　　　　　　　　　　　2　　表中有大量重复值的列　

　　　　　　　　　　　　　　3　　数据类型为bit,tex,image的列

代码

　　

USE XK
GO
--创建视图
CREATE VIEW v_course
AS 
    SELECT course_name AS '课程名称',teacher_name AS '教师姓名',course_time AS '上课时间',student_num AS '学生人数'
    FROM course
GO
SELECT * FROM v_course
GO
--添加更改视图  查出学生id 课程名称   学生姓名  查出选课视图选课的学生姓名，课程
ALTER VIEW v_elective
AS 
    SELECT TOP 300 student.student_id AS '学生id', course_name AS '课程名称',student_name AS '学生姓名' FROM student
    LEFT JOIN elective ON student.student_id=elective.student_id
    LEFT JOIN course ON elective.course_id=course.course_id
    GROUP BY course.course_name ,student_name,student.student_id
    ORDER BY course.course_name
GO
SELECT * FROM v_elective
GO
--统计每个部门开设课程的门数

ALTER VIEW v_course
AS 
    SELECT TOP 100 department.department_name AS '部门名称', COUNT(course_id) AS '课程数' FROM course
    RIGHT JOIN department ON department.department_id=course.department_id
    GROUP BY department_name
    ORDER BY '课程数' DESC
GO
SELECT * FROM v_course
GO
----更改索引名称

exec sp_rename 'course.IX_course','UN_course','index'
----显示关闭使用索引信息，一大串。。。。
SET SHOWPLAN_ALL ON 
GO
SELECT * FROM course WHERE course_name = '小明'
GO
SET SHOWPLAN_ALL OFF
GO
--更新统计信息
UPDATE STATISTICS student IX_student
GO
----扫描表 碎片整理
DBCC SHOWCONTIG(student,IX_student)
GO
DBCC INDEXDEFRAG(XK,student,IX_student)
GO