第次课数据表的创建及其管理PPT课件.ppt

1 第七章数据对象基础 重点 第1节数据对象 表和索引 第2节创建表 第3节表的管理 第4节索引的创建与管理 2 第1节数据库对象 表视图索引存储过程触发器序列号发生器 3 表 表存储在一个或多个分区中 4 视图 虚表 由查询定义 由基本表导出 5 索引 索引 提高数据检索的性能 快速访问数据库表的特定信息 缺点 占用磁盘空间降低添加 删除和更新时的速度用法 经常查询索引列中的数据时分为 聚集索引非聚集索引 6 聚集索引 每个表只能有一个聚集索引 数据行本身只能按一个顺序排序 也就是说 记录的索引顺序与物理顺序相同 查询时 SQLServer将在索引中向下移动以查找与某个聚集索引键对应的记录 一般按照标识字段来建立聚集索引 7 非聚集索引 与数据行的物理顺序无关一个表中非聚集索引可有多个 最多可达249缺点 非聚集索引需要大量的硬盘空间和内存 非聚集索引可以提高查询的速度 但会降低插入和更新数据的速度 8 存储过程 存储过程 存储起来的过程 存什么东西 过程 过程化的SQL语句 存在哪儿 数据库服务器中优点 简化管理数据库的工作 存储过程中创建的同时在服务器上进行了编译 所以执行起来比单个SQL快 9 Createprocedureprocedure 1 firstnamenvarchar 20 asbeginselect fromswheres namelike firstname end 10 触发器 触发器 特殊的存储过程 问题1 特殊 在插入 删除或修改特定表中的数据时触发执行 问题2 存在哪儿呢 数据库中 使用SQL和PL SQL语句编写 问题3 和存储过程有何区别呢 11 存储过程 由用户 应用程序或触发器显式调用触发器 当触发事件发生时由系统触发执行 12 Createtriggertrigger 1onstudentafterinsertasprint 成功插入一条新记录 13 对象命名规则 了解 表名 XXX相关表以r 作为前缀 YYY相关表以t 作为前缀 后台独有的表以 b 作后缀 r student t courser admin b视图名 以v 作为前缀v jiaowu 14 触发器 相应的表名加上后缀 Insert触发器加 i Delete触发器加 d Update触发器加 ur test it student d存储过程名 sp 作为前缀sp Northwind test 15 注释 用英文尽可能详细 全面单行注释 两个连字符 开头多行注释 之间的部分 16 第2节如何创建表 直观可视方式T SQL方式在 表 单击右键 选择 新建表 语法格式 CREATETABLE database name owner owner table name column nameAScomputed column expression CONSTRAINTconstraint name PRIMARYKEY UNIQUE n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT column namedata type COLLATE 使用T SQL语句创建数据表 课本P157 18 列数据类型 字符型char 用于存储长度固定的字符串 当用char数据类型存储数据时 每个字符和符号占用1个字节的存储空间 其定义形式为 Char n 其中 n表示所有字符所占的存储空间 n的取值为 1 8000 若不指定n 系统默认值为1 若输入数据的字符串长度小于n 则系统自动在其后添加空格来填满设定好的空间 若输入的数据过长 将会截掉其超出部分 19 varchar 存储可变长度字符串 存储空间随输入数据的实际长度而变化 其定义形式为 varchar n 其中 n表示所有字符所占的最大空间 n的取值为 1 8000 若输入数据的字符串长度小于n 则空间为输入数据的实际长度 若长度大于n 将会截掉其超出部分 declare strchar 20 str1varchar 20 set str char字符 set str1 varchar字符 print str 1 print str1 1 text用于存储无限长的字符串 每行可达2GB 20 Unicode字符串 用于支持国际上的非英语语种 每个Unicode字符用2个字节为1个存储单位 Nchar n n的取值为 1 4000Nvarchar n n的取值为 1 4000Ntext 存储大容量文本数据 每行可达2GB 21 数值型 INT NUMERIC MONEYINT int 21亿到正21亿 4个字节SMALLINT 32768到32767 2个字节TINYINT 0 255 1个字节NUMERIC 1038到1038表示小数部分需要同时指定整数和小数部分的大小 NUMERIC 23 0 整数部分最多28位 小数位数不能高于整数位数MONEYMONEY 922万亿到正922万亿 SMALLMONEY 21万到正21万 22 Decimal和numeric 两者完全相同 定义形式 decimal p s 其中P表示可供存储的数值的总位数 默认是18 s表示小数点后的位数 默认是0 例 decimal 10 5 表示共有10位数 整数5位 小数5位 23 逻辑型 布尔型 BIT bit值类型可以取值为1 0 当直接打开表向这一列中插入数据时需要插入true或false 当用sql语句插入数据的时候该列就插入0或1当在查询分析其中用select语句读取这个表中的这一列数据时该列数据显示的是0或1 usestudentcreatetabletable 1 sdbit gousestudentinsertintotable 1values 1 24 日期型DATETIME 1753年1月1日到9999年12月31日 精确到毫秒SMALLDATETIME 1900年1月1日到2079年6月6日 精确到秒 25 什么是数据完整性呢 存储在数据库中数据的一致性和正确性 根据数据完整性措施所作用的数据库对象和范围不同 数据完整性分为 实体完整性 参照完整性 域完整性和用户定义完整性四种 为列定义约束 26 CHECK约束限制输入到一列或多列的值的范围DEFAULT约束如果没有为列指定数据 系统将默认值赋给列PRIMARYKEY约束如果某列或多列的值能惟一标识表中的每一行 这样的列称为表的主键 通过它可以强制表的实体完整性 约束的类型 27 FOREIGNKEY约束外键 ForeignKey 是用于建立和加强两个表 主表与从表 的一列或多列数据之间的链接 当数据添加 修改或删除时 通过参照完整性保证它们之间数据的一致性 定义表之间的参照完整性是先定义主表的主键 再对从表定义外键约束UNIQUE约束确保表中的两个数据行在非主键列中没有相同的列值 约束的类型 28 使用管理平台对表的约束管理 check约束的创建 查看 删除Default约束的创建 查看 删除Primarykey约束创建 查看 删除外键创建 查看 删除Unique约束的创建 查看 删除 29 NULL NOTNULL标识字段 indentity 每个表可以有1个标识字段 也只能有1个标识字段 CLUSTERED 聚集索引NONCLUSTERED 非聚集索引 30 创建表实例1 CREATETABLENewEmployees EmployeeIDintnotnull Namenvarchar 20 notnull Titlenvarchar 30 TitleOfCourtesynchar 25 BirthDaydatetime HireDaydatetime 31 创建一个主键为两个列组成的表 CREATETABLENewEmployees1 EmployeeIDintnotnull Namenvarchar 20 notnull Titlenvarchar 30 TitleOfCourtesynchar 25 BirthDaydatetime HireDaydatetime constraintasdaprimarykey EmployeeID name 32 CREATEtableProducts ProductNamenvarchar 20 NOTNULLUNIQUENONCLUSTERED ProductIDintPRIMARYKEYCLUSTERED AmountintCHECK Amount 1000andAmount 2000 Pricemoney OutDaysmalldatetime Countrynvarchar 20 DEFAULT China TotalAS Price Amount Commentnvarchar 4000 Total叫计算列 表显示时 自动生成 注意 如果表含有计算列 当执行insert语句时 不能向计算列中插入数据 如何创建一个Products表 33 课堂练习 使用T SQL创建student1表 id int型 主键 非聚集索引 非空stuname 字符型 20个 非空sex 布尔型 缺省为1 非空age tinyint型 约束1 40 可空birthday smalldatetime型 可空home 字符型 20 可空 34 CREATEtablestudent idintPRIMARYKEYNONCLUSTEREDnotnull stunamenchar 20 notnull sexbitDEFAULT 1 notnull agetinyintcheck age 1andage 40 birthdaysmalldatetime homevarchar 20 35 第五次课 CREATEtableProducts ProductNamenvarchar 20 NOTNULLUNIQUENONCLUSTERED ProductIDintCLUSTERED AmountintCHECK Amount 1000andAmount 2000 Pricemoney OutDaysmalldatetime Countrynvarchar 20 DEFAULT China TotalAS Price Amount Commentnvarchar 4000 constraintasdaprimarykey ProductID ProductName 36 创建临时表 用在存储过程或批处理中分为 局部临时表和全局临时表局部临时表 当前会话可见全局临时表 所有会话可见局部临时表 前有一个 号全局临时表 前有两个 号 37 创建一个产品临时表 CREATETABLE TempProducts ProductIDINTPRIMARYKEY Pricemoney 38 存储 临时表会存在 系统数据库 的 tempdb 临时表 中 临时表的删除 临时表在退出其作用域时由系统自动删除 当然 也可以使用Droptable语句显式删除 39 首先建立一个 xuhai 数据库 在该数据库中建立表student 40 新建 xuhai 数据库 创建student表 id int型 主键 非聚集索引 非空stuname 字符型 20个 非空sex 布尔型 缺省为1 非空age tinyint型 约束1 40 可空birthday smalldatetime型 可空home 字符型 20 可空保留sql文件 41 第3节表的管理 使用管理器直观管理使用T SQL管理功能 修改表 删除表 42 使用ALTERTABLE管理表 增加和删除字段增加和删除约束添加 修改和删除默认值修改字段数据类型 43 CREATETABLEEmployees EmployeeIDintnotnull Namenvarchar 20 notnull Titlenvarchar 30 TitleOfCourtesynchar 25 BirthDaydatetime HireDaydatetime 44 例5 在Employees表中 添加字段City 数据类型为NVARCHAR 长度为30 ALTERTABLEemployeesADDCitynvarchar 30 45 例 删除City字段 AlterTABLEEmployeesDropCOLUMNCity 46 例15 修改Employees表中的BirthDay字段 使其的类型为smalldatetime ALTERTABLEEmployeesALTERCOLUMNBirthDaysmalldatetime 47 删除表 DROPTABLEEmployees删除须谨慎 48 CHECK约束的创建 查看和删除在数据库xscj中的表 学生基本信息表 中定义学生性别列只能是 男 或 女 如果用户输入其它值 系统均提示用户输入无效 ALTERTABLEtable nameADD CONSTRAINTconstraint name CHECK logical expression DROPCONSTRAINTconstraint name 约束的创建 查看 删除 49 usexscjALTERTABLE学生基本信息表addCONSTRAINTck stud infoCHECK 性别 N 男 or性别 N 女 删除约束名为 CK stud info 的约束的语句是 ALTERTABLE学生基本信息表DROPCONSTRAINTck stud info 在数据库xscj中的表 学生基本信息表 中定义学生性别列只能是 男 或 女 如果用户输入其它值 系统均提示用户输入无效 50 例 在Employees表中给EmployeeID字段添加Check约束 使EmployeeID 100 ALTERTABLEEmployeesWITHNOCHECKADDCONSTRAINTID checkCHECK EmployeeID 100 WITHNOCHECK 避免根据现有记录验证该约束 51 例 删除刚添加到EmployeeID的Check约束 ALTERTABLEEmployeesDROPCONSTRAINTID check 52 创建和删除DEFAULT约束ALTERTABLEtable nameADD CONSTRAINTconstraint name DEFAULTconstraint expressionDROPCONSTRAINTconstraint name创建和删除PRIMARYKEY约束ALTERTABLEtable nameADD CONSTRAINTconstraint name PRIMARYKEY CLUSTERED NONCLUSTERED constraint expressionDROPCONSTRAINTconstraint name 约束的创建 查看 删除 53 约束的创建 查看 删除 为表stud info创建一个约束名为 de gender 的DEFAULT约束 要求性别 gender 的默认值为 男 其T SQL语句 ALTERTABLEstud infoADDCONSTRAINTde genderDEFAULTN 男 FORgender 删除DEFAULT约束的语句 ALTERTABLEstud infoDROPCONSTRAINTde gender 54 例 在Employees表中 给City增加DEFAULT约束为 Beijing ALTERTABLEEmployeesADDCONSTRAINTCity defDEFAULT Beijing FORCity 55 例 在Employees表中 修改City字段 删除DEFAULT约束 ALTERTABLEEmployeesDROPCONSTRAINTCity def 56 约束的创建 查看 删除 为表stud info中的学号 stud id 定义主键 其T SQL语句如下 ALTERTABLEstud infoADDCONSTRAINTPK XHPRIMARYKEYCLUSTERED stud id 删除该主键的T SQL语句 ALTERTABLEstud infoDROPCONSTRAINTPK XH 57 例 在Employees表中 给字段Name增加PRIMARYKEY约束 ALTERTABLEEmployeesADDCONSTRAINTPK NamePRIMARYKEYCLUSTERED Name 58 建立表之间的关系可以通过外键约束来实现 创建外键约束既可以由FOREIGNKEY子句完成 也可以在表设计器中完成 创建和删除FOREIGNKEY约束ALTERTABLEtable nameADD CONSTRAINTconstraint name FOREIGNKEY REFERENCESreferenced table name ref column DROPCONSTRAINT FOREIGNKEY constraint name 约束的创建 查看 删除 59 约束的创建 查看 删除 为学生成绩表stud grade与课程信息表lesson info创建外键约束 其T SQL语句如下 为lesson info表中的course id列建立主键约束ALTERTABLElesson infoADDCONSTRAINTpk course idPRIMARYKEYCLUSTERED course id 为stud grade表中的course id列建立外键约束ALTERTABLEstud gradeADDCONSTRAINTfk course idFOREIGNKEY course id REFERENCESlesson info course id 60 创建和删除UNIQUE约束ALTERTABLEtable nameADD CONSTRAINTconstraint name UNIQUEconstraint expression CLUSTERED NONCLUSTERED ALTERTABLEtable nameDROPCONSTRAINTUNIQUEconstraint name 约束的创建 查看 删除 61 约束的创建 查看 删除 针对学生基本信息表的 电话号码 创建UNIQUE约束 具体语句如下 ALTERTABLEstud infoADDCONSTRAINTUN telcodeUNIQUE telcode 删除UNIQUE约束的T SQL语句如下 ALTERTABLEstud infoDROPCONSTRAINTUN telcode 62 例 在Employee表中 给EmployeeID增加UNIQUE约束 ALTERTABLEEmployeesADDCONSTRAINTID constraintUNIQUE EmployeeID 63 例修改Employees表中的EmployID字段 使其不再具有UNIQUE约束 ALTERTABLEEmployeesDROPCONSTRAINTID Constraint 64 例 修改Employees表中的Name和EmployeeID字段 给这两个字段添加唯一性约束 ALTERTABLEEmployeesADDCONSTRAINTName ID constraintUNIQUE Name EmployeeID 65 默认值可以先定义好 在表中插入数据行时 系统自动提供事先定义的默认值 与DEFAULT约束不同的是 默认值是一种数据库对象 在数据库中只需定义一次后 就可以被一次或多次应用于任意表中的一列或多列 4 3使用默认值和规则 了解 66 创建默认值和将其绑定到表上的某列CREATEDEFAULTdefault nameASconstraint expression其中 default name 表示所建立的默认值名称 Constraint expression 指定的默认值 EXECsp bindefaultdefault name table name column name user datetype 其中 default name是CREATEDEFAULT创建默认值的名称 table name column name user datetype 为表名和列名或者绑定默认值的别名数据类型 使用默认值 67 规则这种数据库对象的作用就是当向数据表中某列插入数据时 指定该列接受数据值的范围 规则的作用与CHECK约束一样 只不过规则不固定于那个列 而是创建好以后可以随便地绑定于表中某个列上 创建和绑定规则CREATERULErule nameAScondition expression其中 rule name为规则名 condition expression定义规则的条件EXECsp bindrulerule name table name column name user datetype 使用规则 68 使用规则 在数据库student中创建一个规则 并将其绑定到表stud grade中的学生成绩 grade 列上 使得用户输入的学生成绩在 100的范围之内 否则提示输入无效 其创建规则的T SQL语句如下 USEstudentGOCREATERULEGZ GRADEAS grade1 0and grade1 100GOEXECsp bindruleGZ GRADE stud grade grade 69 修改对象名 sp rename存储过程 更改对象名sp r