第4章 索引视图子查询与备份

nullWEB技术与数据库WEB技术与数据库第4章 索引视图子查询与备份上一节回顾上一节回顾使用命令操作数据库、表 数据库增删改查操作 使用SELECT语句查询数据 使用INSERT语句插入数据 使用UPDATE语句更新数据 使用DELETE语句删除数据 函数 聚合函数 日期函数 字符串函数 T-SQL基础 逻辑控制语句 if case-end while 本章目标本章目标掌握联接查询 掌握子查询 掌握视图、索引 了解数据库的备份与恢复 了解数据导入导出*/35内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35多表联接查询多表联接查询*/27--姓名，课程名，成绩 select name,cname,cj from stuinfo inner join score on stuinfo.sno=score.cjsno inner join course on score.cjcno=course.cno--查找“没有学生考过试的”课程 select cname from course left join score on score.cjcno=course.cno where cj is null--查找“没有参加过考试”的学生 select name from score right join stuinfo on stuinfo.sno=score.cjsno where cj is nullselect * from stuinfo,score where stuinfo.sno=score.cjsnoselect * from stuinfo Inner join score on stuinfo.sno=score.cjsnoselect cname from course,score where score.cjcno*=course.cno and cj is null--查找“没有参加过考试”的学生 select name from score,stuinfo where stuinfo.sno=*score.cjsno and cj is nullALTER DATABASE stu SET COMPATIBILITY_LEVEL =80 内连接内连接左外 连接右外 连接--姓名，课程名，成绩 select * from stuinfo full join score on score.cjsno=stuinfo.sno full join course on course.cno=score.cjcno全外 连接select * from course cross join score交叉 连接内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35什么是子查询什么是子查询*/27实现方法一：采用T-SQL变量实现 --筛选比张三年龄大的学员 declare @a int select @a=age from stuinfo where name='zs' select * from stuinfo where age>@a实现方法二：采用子查询实现 select * from stuinfo where age > ( select age from stuinfo where name='zs')子查询在WHERE语句中的一般用法： SELECT … FROM 表1 WHERE 字段1 >(子查询) UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 使用子查询替换表连接使用子查询替换表连接*/27问题：查询至少有一科及格的学员名字 注：用到学员信息表和成绩表 实现方法一：采用表连接 SELECT distinct 姓名FROM sinfo INNER JOIN score ON sinfo.学号=score.学号 WHERE 成绩>60实现方法二：采用子查询 SELECT 姓名FROM sinfo WHERE 学号in(SELECT 学号FROM score WHERE 成绩>60)一般来表连接都可以用子查询替换，但有的子查询却不能用表连接替换 子查询较灵活、方便，作为增删改查筛选条件，适于操纵一个表的数据 表连接更适合于查看多表的数据注意:把in改成=可以吗?内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35典型题（难）典型题（难）查询没有参加1号课程考试的学生姓名 平均分大于85分的课程名 同时选参加了1和2号课程考试的学生 表中第4-6条记录（id自增，不连续） 所有男孩子中，岁数大于30岁*/27查询没有参加1号课程考试的学生姓名查询没有参加1号课程考试的学生姓名SELECT 姓名 FROM 学生表 WHERE (学号 NOT IN (SELECT 学号 FROM 成绩表 WHERE 成绩号 = '1'))*/27平均分大于85分的课程名平均分大于85分的课程名select cname,AVG(cj) from score inner join course on score.cjcno=course.cno group by cjcno,cname having AVG(cj)>=85*/27同时选参加了1和2号课程考试的学生同时选参加了1和2号课程考试的学生select 学号 from 成绩表 where 课程号='1' and 学号 in ( select 学号 from 成绩表 where 课程号='2' )*/27表中第4-6条记录（id自增，不连续）表中第4-6条记录（id自增，不连续）select * from (select top 6 * from 学生表 ) aa where 学号 not in( select top 3 学号 from 学生表) */27所有男孩子中，岁数大于30岁所有男孩子中，岁数大于30岁select * from ( select sno,age from stuinfo where sex='男') bb where age>30注：（给表起别名） select * from stuinfo s where s.age>10内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 索引 视图 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35什么是视图什么是视图 */38视图的概念 视图是一张虚拟表，其数据是在表的查询基础上 视图中并不存放数据，数据在原始表中 删除视图，对表没有任何影响 视图的用途 筛选表中的行，便于查询，简化SQL 防止未经许可的用户访问敏感数据 创建视图前应注意如下原则： 视图名称不与表的名称相同 视图的查询不包含 ORDER BY 子句 语法： CREATE VIEW view_name [(col_name,…)] AS SELECT statement视图视图－－创建视力，便于查询 create view v1 as select sinfo.姓名,sinfo.性别,course.课程名称,score.成绩 from score inner join sinfo on score.学号=sinfo.学号 inner join course on course.课程号=score.课程号 */38－－对视图进行插入操作 create view v2 as select 姓名,性别 from sinfo Select * from view2 Insert into view2 values (‘刘六’,1) Select * from sinfodrop view v1内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35null*/38索引索引概念： SQL Server中的数据也是按页存放,4kb一页 索引：编排数据的内部方法 索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页 索引的作用： 提高查询速度 ，加速表间连接 降低查询中GROUP BY分组和ORDER BY排序的时间 索引的分类索引的分类唯一索引：不允许两行值相同。最好非空（NOT NULL） 主键索引：定义主键将自动创建主键索引，唯一并且非空 聚集索引(Clustered)：各行物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个 非聚集索引(Non-clustered)：数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。在JOIN 或 GROUP BY 子句中较多。 每个表可以有多个，小于249个 */385.2 创建索引5.2 创建索引创建索引两种方式： 自动创建： 列PRIMARY KEY 或 UNIQUE 约束时系统自动创建索引 用 CREATE INDEX 语句 *CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…)唯一索引聚集索引或非聚集索引索引索引*/38查看学生表索引给一个表建立索引,姓名升序create nonclustered --clustered 或 uique index indexname on stuinfo(name asc) drop index stuinfo.indexname select * from stuinfo where name like '王%'sp_helpindex 学生表内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35软件测试相关－性能调优软件测试相关－性能调优适合创建索引情况： 在经常检索的列上创建索引（如经常在where子句中出现得列） 在表的主键、外键上创建索引 在经常用于表间连接的字段上建立索引 不适合创建索引情况： 在查询中几乎不涉及的列 很少有唯一值的列（即包含太多重复值得列，如性别字段） 数据类型为text、ntext或image的列 只有较少行数的表没有必要创建索引 当写的性能比查询更重要时，应少建或不建索引 批处理语句GO批处理语句GO*/38SELECT * FROM stuInfo SELECT * FROM stuMarks UPDATE stuMarks SET writtenExam=writtenExam+2 GOGO是批处理的标志，表示SQL Server将这些T-SQL语句编译为一个执行单元，提高执行效率 一般是将一些逻辑相关的业务操作语句，放置在同一批中，这完全由业务需求和代码编写者决定 内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35数据库的备份数据库的备份1．完全数据库备份 备份所有的数据，包括事务日志。 时间长,适用备份容量较小或数据库中数据的修改较少的数据库。 完整数据库备份是差异备份和事务日志备份的基准 2．差异数据库备份 备份自上次完整数据库备份以来更改的数据 适合于修改频繁的数据 3．事务日志备份 自上次备份以来数据变化的过程，即事务日志文件的信息 使用事务日志备份将数据库恢复到特定的即时点或恢复到故障点。 4．文件和文件组备份 可以将数据库的文件和文件组分别进行备份 适合于特大型数据库**备份还原数据库备份还原数据库sp_addumpdevice 'disk','stu4full','c:\stu4full.bak' backup database stu to stu4full with init sp_addumpdevice 'disk','stu4log','c:\stu4log.bak' backup log stu to stu4log with init restore database stu from stu4full with replace*/35内容进度内容进度高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35导入数据（演示）导入数据（演示）*/35小结小结高级查询 联接查询 内联接(INNER JOIN)等值连接 外联接 ——左外联接 (LEFT JOIN) 左（外）连接 ——右外联接 (RIGHT JOIN) 右(外）连接 ——完整外联接(FULL JOIN) 全（外）连接 交叉联接(CROSS JOIN) 子查询 查询典型题 视图 索引 软件测试相关－性能调优 数据库的备份与恢复 数据导入导出*/35