数据库SQL管理和编码.doc

数据库管理引语：数据库管理(Database Manager)是有关建立、存储、修改和存取数据库中信息的技术，是指为保证数据库系统的正常运行和服务质量，有关人员须进行的技术管理工作。负责这些技术管理工作的个人或集体称为数据库管理员(DBA)。数据库管理的主要内容有：数据库的调优、数据库的重组、数据库的重构、数据库的安全管控、报错问题的分析和汇总和处理、数据库数据的日常备份. 数据库的建立：数据库的设计只是提供了数据的类型、逻辑结构、联系、约束和存储结构等有关数据的描述。这些描述称为数据模式正文：数据库管理包括数据库规划、设计、实施、运行和维护五个阶段一、数据库规划：构建用来描述学生的数据表，要求记录学生的如下属性：姓名、学号、性别、生源地、状态、考试成绩、学分、选课二、数据库设计：create schema studentcreate table student.学生(学号 char(8) primary key, 院系 as convert(tinyint,substring(学号,3,2) persisted not null foreign key references student.院系(编号), 姓名 char(10) not null, 性别 char(2) check(性别=男 or 性别=女), 生源 char(6), 状态 char(4) check(状态 in (正常,留级,休学,退学)insert into student.学生values(04014101,曹波,男,湖北,正常)insert into student.学生values(04014111,程鸣,女,湖南,正常)insert into student.学生values(04014122,吴红霞,女,浙江,正常)insert into student.学生values(04026205,张鸿飞,男,北京,正常)insert into student.学生values(04026210,张雪梅,女,贵州,休学)insert into student.学生values(04016108,林报国,男,上海,正常)insert into student.学生values(04016116,王丽,女,北京,正常)insert into student.学生values(05113102,郝海涛,男,河南,正常)insert into student.学生values(05113110,王伟,男,北京,正常)select 学生.学号,姓名,课程名称,考试成绩from student.学生 join student.选课 on 学生.学号=选课.学号join student.课程 on 选课.课程编号=课程.课程编号where 考试成绩=90select 学生.学号,姓名,课程名称,考试成绩from student.学生 cross join student.选课 cross join student.课程where 学生.学号=选课.学号and 选课.课程编号=课程.课程编号 and 考试成绩=90select 学生.学号,姓名,课程名称,考试成绩from student.课程 join student.选课 on 选课.课程编号=课程.课程编号join student.学生 on 学生.学号=选课.学号order by 学生.学号 compute avg(考试成绩),max(考试成绩),min(考试成绩) by 学生.学号select 学生.学号,姓名,课程名称,考试成绩from student.课程 join student.选课 on 选课.课程编号=课程.课程编号join student.学生 on 学生.学号=选课.学号order by 学生.学号compute avg(考试成绩),max(考试成绩),min(考试成绩) by 学生.学号compute avg(考试成绩),max(考试成绩),min(考试成绩)三、数据库实施成果：四、数据库安全配置 1使用安全的密码策略 我们把密码策略摆在所有安全配置的第一步，请注意，很多数据库账号的密码过于简单，这跟系统密码过于简单是一个道理。健壮的密码是安全的第一步，建议密码含有多种数字字母组合并9位以上。同时养成定期修改密码的好习惯，数据库管理员应该定期查看是否有不符合密码要求的账号。2使用安全的账号策略由于SQL的认证模式有Windows身份认证和混合身份认证两种。如果数据库管理员不希望操作系统管理员来通过操作系统登录来接触数据库的话，可以在账号管理中把系统账号“BUILTINAdministrators”删很多主机使用数据库应用只是用来做查询、修改等简单功能的，请根据实际需要分配账号，并赋予仅仅能够满足应用要求和需要的权限。比如，只要查询功能的，那么就使用一个简单的public账号能够select就可以了。3加强数据库日志的记录审核数据库登录事件的“失败和成功”，在实例属性中选择“安全性”，将其中的审核级别选定为全部，这样在数据库系统和操作系统日志里面，就详细记录了所有账号的登录事件。请定期查看SQL Server日志检查是否有可疑的登录事件发生，或者使用DOS命令。4管理扩展存储过程对存储过程进行大手术，并且对账号调用扩展存储过程的权限要慎重。其实在多数应用中根本用不到多少系统的存储过程，而SQL Server的这么多系统存储过程只是用来适应广大用户需求的，所以请删除不必要的存储过程，因为有些系统的存储过程能很容易地被人利用起来提升权限或进行破坏。5不要让人随便探测到您的TCP/IP端口在实例属性中选择TCP/IP协议的属性。选择隐藏 SQL Server实例。如果隐藏了SQL Server实例，则将禁止对试图枚举网络上现有的 SQL Server实例的客户端所发出的广播作出响应。这样，别人就不能探测您的TCP/IP端口了6修改TCP/IP使用的端口请在上一步配置的基础上，更改原默认的端口。在实例属性中选择网络配置中的TCP/IP协议的属性，将TCP/IP使用的默认端口变为其他端口。7拒绝来自原端口的探测由于原端口探测没有限制，能够被别人探测到一些数据库信息，而且还可能遭到DoS攻击让数据库服务器的CPU负荷增大，所以可以尽可能地隐藏您的SQL Server。8对网络连接进行IP限制SQL数据库系统本身没有提供网络连接的安全解决办法，但是Windows 2000提供了这样的安全机制。使用操作系统自己的IPSec可以实现IP数据包的安全性。请对IP连接进行限制，只保证自己的IP能够访问，也拒绝其他IP进行的端口连接，对来自网络上的安全威胁进行有效的控制。五、数据库维护：1.一致性检查:检查数据库的完整性.2.更新索引的统计数据;3.整理数据库碎片;4.重建索引;5.备份;6.检查文件的大小,并释放磁盘空间;六、监控数据库维护活动:1.备份系统数据库;2.备份用于生产的用户数据库;3.SQL SERVER Agent/SQL Main/DTC的运行情况;4.数据库大小/增长情况/磁盘自由空间的情况;5.批处理作业是否正确执行;6.DBCC作业是否正确执行;7.检查SQL 日志的错误;8.复制日志代理的运行情况;9.复制分发清除作业是否正确执行;七、数据库优化1、使用索引来更快地遍历表。缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：.有大量重复值、且经常有范围查询（between, ,=, =）和order by、group by发生的列，可考虑建立群集索引；.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。2、区分IS NULL 与 IS NOT NULL不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。3、区分IN和EXISTSEXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。4、在海量查询时尽量少用格式转换。5、当在SQL SERVER 2000中，如果存储过程只有一个参数，并且是OUTPUT类型的，必须在调用这个存储过程的时候给这个参数一个初始的值，否则会出现调用错误。6、区分ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。注意如果索引列里面有NULL值，Optimizer将无法优化。7、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。8、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重