1. 索引索引的种类： 聚集索引：反应的是数据存储的物理顺序，一个表只能包含一个聚集索引 非聚集索引：不反应数据存储的物理顺序，一个表可以有多个非聚集索引 1.1. 准备环境1.1.1. 创建聚集索引目前tstudent表中没有任何索引也没有主键
为tstudent表创建聚集索引
选中studentID,单击左上侧的主键按钮
为Tstuden表的studentID创建主键就同时创建了聚集索引
1.1.2. 创建组合索引为成绩表创建组合索引，因为一个学生不能为一门学科录入两次成绩，所以将成绩表中的studentID和subjectID创建组合索引
解决办法： 菜单栏----工具----选项 找到设计器（designers）,将标记处的勾去掉，单击“确定”
这样组合索引就创建成功了。
1.1.3. 用命令创建聚集索引创建一个表TS create TABLE TS( StudentID varchar(10) NOTNULL, Sname varchar(10) DEFAULT NULL, sex char(2)DEFAULTNULL, cardID varchar(20) DEFAULT NULL, Birthday datetime DEFAULT NULL, Email varchar(40) DEFAULT NULL, Class varchar(20) DEFAULT NULL, enterTime datetime DEFAULT NULL ) Go 用命令创建聚集索引 create clustered index CL_studentID on TS(studentID) 创建聚集索引不一定创建主键，如下图所示：
1.1.4. 创建唯一索引创建唯一性约束的时候就会创建唯一性索引，不能有重复值 为Tstudent表创建唯一非聚集索引 create unique non clustered index U_cardID on TStudent(cardID) 1.1.5. 创建非聚集索引---可以有重复值为Tstudent表的姓名列创建非聚集索引
使用命令查看表上的索引 Select*from sys.sysindexes whereid=(selectobject_idfromsys.all_objectswherename='Tstudent') Indid中1代表聚集索引 Indid中2代表唯一非聚集索引 Indidz中3代表非聚集索引
使用sp_help Tstudent也可以查看到相关表的信息
2. 创建视图进行数据库设计的时候，一个表有很多列，我们可以在表上创建视图，只显示指定的列。 Select语句可以作为一个视图 select Sname,sex,Classfrom dbo.TStudent whereClass='网络班' 2.1. 创建视图，筛选行和列create view netstudent as select Sname,sex,Classfrom dbo.TStudent where Class='网络班' 从视图中查找数据： select * from netstudent where sex='男' 创建视图，更改列的表头，计算列，产生计算列 select StudentID,Sname,sex,cardID,Birthday,Email,Classfromdbo.TStudent
2.2. 创建视图，更改列的表头
createviewV_Tstudent1 as selectStudentID学号,Sname姓名,sex性别,cardID身份证号码,Birthday生日,Class班级fromdbo.TStudent
select*fromV_Tstudent1
以后再去查询的时候就非常方便了。
2.3. 创建视图计算列根据出生日期计算机学生年龄，并增加一个年龄列 createview V_Tstudent as selectStudentID学号,Sname姓名,sex性别,cardID身份证号码,Birthday生日,Class班级 ,Datediff(YY,Birthday,Getdate())年龄fromdbo.TStudent
year(getdate())-year(birthday)等价Datediff(YY,Birthday,Getdate())等价于Datediff(YEAR,Birthday,Getdate())
select*fromV_Tstudent
年龄列并没有占用数据库的存储空间，这对于使用者来说就非常方便了。 2.4. 修改视图alter view V_Tstudent2 as selectStudentID学号,Sname姓名,sex性别,Birthday生日,Class班级 ,Datediff(YY,Birthday,Getdate())年龄fromdbo.TStudent
select * fromV_Tstudent2
将视图当表来用 select*fromV_Tstudent2where年龄>30
2.5. 创建视图隐藏底层数据的复杂性selecta.StudentID学号,a.sname姓名,mark成绩,subJectName学科,class班级fromdbo.TStudentajoindbo.TScoreb ona.StudentID=b.StudentIDjoindbo.TSubjectconb.subJectID=c.subJectIDwhereclass='网络班' 创建视图 createviewv_smark as selecta.StudentID学号,a.sname姓名,mark成绩,subJectName学科,class班级fromdbo.TStudentajoindbo.TScoreb ona.StudentID=b.StudentIDjoindbo.TSubjectconb.subJectID=c.subJectIDwhereclass='网络班' 查看视图 select*fromv_smark 使用视图计算列 select学号,SUM(成绩)as总分fromv_smarkgroupby学号
2.6. 从视图创建视图createviewcjzf as select学号,SUM(成绩)as总分fromv_smarkgroupby学号
select*fromcjzf
3. 存储过程存储过程是sql语句和控制语句的预编译集合，保存在数据库中，可由应用程序执行，而且允许用户声明变量，逻辑控制语句和强大的编程功能 使用存储过程的好处： 1.模块化程序设计 2.执行速度快，效率高 3.减少网络流量 4.具有很好的安全性 系统存储过程 sql-server提供了很多的系统存储过程，他们是一组预编译的T-SQL语句，系统存储过程提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式。 常用的系统存储过程 sp_database 列出服务器上的所有数据库的信息，包括数据库名和数据大小 sp_helpdb 报告有关指定数据库或所有数据库的信息 sp_renamedb 更改数据库的名称 sp_tables 返回当前环境下可查询的表和视图的信息 sp_columns 返回某个表和视图的列信息，包括列的数据类型和长度等 sp_help 查看某个数据库对象的信息：如列名，主键，约束，外键，索引等 sp_helpconstraint 查看某个表的索引 sp_stored_procedures 显示存储过程的列表 sp_password 添加或修改登录账户的密码 sp_helptext 显示默认值，未加密的存储过程，用户定义的存储过程，触发器或视图的实际文本
使用T-SQL语句调用执行存储过程的语法如下： EXEC 存储过程名 [参数值] 其中EXEC时execute的简写 3.1. 使用系统存储过程execSP_databases
使用sp_renameDB修改数据库的名字
开启xp_cmdshell execsp_configure'show advanced options',1 go reconfigure go execsp_configure'xp_cmdshell',1 go reconfigure go 在C盘创建一个bank的文件夹 execxp_cmdshell'mkdir c:\bank',no_output no_output是可选参数，设置执行DOS命令后是否输出返回信息 execxp_cmdshell'dir c:\bank\'
用户自定义的存储过程 在sql-server中，用于创建处处过程的sql语句为create procedure,所有的存储过程都存放在当前数据库中。一个完整的存储过程都包含以下三部分 1.输入参数和输出参数 2.在存储过程中执行的T-SQL语句 3.存储过程的返回值 编写一个求网络管理专业平均分数的存储过程 3.2. 创建一个自定义的存储过程--求网络管理专业的平均分
IFEXISTS(SELECT*FROMSYSOBJECTSWHEREname='usp_GetAverageResult') DROPPROCEDUREusp_GetAverageResult GO CREATEPROCEDUREusp_GetAverageResult AS DECLARE@subJectIDnvarchar(4) SELECT@subJectID=subJectIDFROMdbo.TSubjectWHEREsubJectName='网络管理' DECLARE@avgdecimal (18,2) SELECT@avg=avg(mark)fromdbo.TScorewheresubJectID=@subJectID PRINT'网络管理专业平均分是:'+CONVERT(VARCHAR(5),@avg) go execusp_GetAverageResult
4. 触发器4.1. 触发器的概念
触发器时在对表进行插入，更新或删除操作时自动执行的存储过程。触发器通常用于强制业务规则，是一种高级约束，可以定义比用check约束更为复杂的约束，可执行复杂的SQL语句，可以引用其他表中的列。 触发器是通过事件进行触发而被执行的，而存储过程可以通过存储过程名而被直接调用。 当对某一个表进行修改，诸如UPDATE,INSERT,DELETE这些操作时，sql server会自动执行触发器所定义的sql语句，从而确保对数据的处理必须符合有这些sql语句所定义的规则，由此触发器可以分为： INSERT触发器： UPDATE触发器： DELETE触发器： deleted表和inserted表 每个触发器有两个特殊的表：删除表deleted和插入表inserted。这两个表是逻辑表，并且这两个表是由系统管理的额，存储在内存中，不是存储在数据库中。因此不允许用户直接对其进行修改。 这两个表的结构与被该触发器作用的表有相同的结构。这两个表是动态驻留在内存中的，当触发器工作完成，它们也被删除。这两个表主要保存因用户操作而被影响的原数据值和新数据值。另外这两个表是只读的，用户不能向其写入内容，但可以引用表中的数据。例如可以使用select查看select * from deleted 触发器的作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性 4.2. 创建触发器createtriggerreminder ondbo.TScore forUPDATE as print'禁止修改，请联系DBA' ROLLBACKTRANSACTION GO
updatedbo.TScoresetmark=100 whereStudentID='0000000001'
select*fromdbo.TScore
|
|
|
