第3章SQL表的管理.ppt

1 SQL语言 SQL StructuredQueryLanguage 结构查询语言 是一个功能强大的数据库语言 SQL通常使用于数据库的通讯 ANSI 美国国家标准学会 声称 SQL是关系数据库管理系统的标准语言 SQL语句通常用于完成一些数据库的操作任务 比如在数据库中更新数据 或者从数据库中检索数据 使用SQL的常见关系数据库管理系统有 Oracle Sybase MicrosoftSQLServer Access Ingres等等 虽然绝大多数的数据库系统使用SQL 但是它们同样有它们自立另外的专有扩展功能用于它们的系统 但是 标准的SQL命令 比如 Select Insert Update Delete Create 和 Drop 常常被用于完成绝大多数数据库的操作 2 SQL功能强大 但是概括起来 它可以分成以下几组 DML DataManipulationLanguage 数据操作语言 用于检索或者修改数据 DDL DataDefinitionLanguage 数据定义语言 用于定义数据的结构 比如创建 修改或者删除数据库对象 DCL DataControlLanguage 数据控制语言 用于定义数据库用户的权限 SQL语言 3 SQL语言 DML组可以细分为以下的几个语句 SELECT 用于检索数据 INSERT 用于增加数据到数据库 UPDATE 用于从数据库中修改现存的数据DELETE 用于从数据库中删除数据 DDL语句可以用于创建用户和重建数据库对象 下面是DDL命令 CREATETABLEALTERTABLEDROPTABLECREATEINDEXDROPINDEXDCL命令用于创建关系用户访问以及授权的对象 下面是几个DCL命令 ALTERPASSWORDGRANTREVOKECREATESYNONYM 4 第三章表的创建和管理 3 1SQL的命名规范和数据类型3 2创建表3 3创建 删除和修改约束3 4增加 删除和修改字段3 5查看表格3 6创建和删除索引3 7删除表 5 第三章表的创建和管理 SQL的命名规范标志符格式 最多容纳128个字符 且不区分大小写 标志符的首字母必须是 26个英文字母a z和A Z 以及其他一些语言字符 如 汉字 或者是下划线 标志符的首字母后的字符可以是 26个英文字母a z和A Z 以及其他一些语言字符 如汉字 或者是下划线 或 标志符不允许是T SQL的保留字 标志符不允许有空格或特殊字符 6 3 1数据类型 1 整型数据类型2 浮点数据类型3 字符数据类型4 日期和时间数据类型5 文本和图形数据类型 6 货币数据类型7 位数据类型8 二进制数据类型9 特殊数据类型10 新增数据类型 7 3 1数据类型 1 整型数据类型整型数据类型是最常用的数据类型之一 它主要用来存储数值 可以直接进行数据运算 而不必使用函数转换 1 int integer 4个字节 2 Smallint 2个字节 3 Tinyint 1个字节 请大家自己计算它们的取值范围 8 3 1数据类型 2 浮点数据类型用于存储十进制小数 1 Real 4个字节的 最大7位精确位数 不包括小数点 对于超出数据表示范围的采用四舍五入的方式 例1 如指定某列为Real型数据类型 8 4368768那么该列实际存储的是 8 436877 例2 如指定某列为Real型数据类型 84 368768那么该列实际存储的是 84 36877 2 Float可以精确到第15位小数 其范围从 1 79E 308到1 79E 308 9 3 1数据类型 3 Decimal和numeric 可以提供小数所需要的实际存储空间 可以用5 17个字节来存储 也可以将其写为decimal p s 的形式 1 p 38 0 s p 注意 数值类型的总位数不包括小数点 例如 decimal 10 5 表示共有10位数 其中整数5位 小数5位 10 3 1数据类型 例1 如指定某列的精度为6 小数位数为3 即decimal 6 3 则向某记录的该例附值86 436876 如果使用企业管理器录入的方法 那么该列实际存储的是 86 436 如果使用T SQL语句的方法 例如代码如下 InsertInto表名Values 86 436876 该列实际存储的是 86 437 请大家试验一下上题如果输入1234 567的结果是什么 11 3 1数据类型 如指定某列的精度为8 小数位数为5 即numeric 8 3 则向某记录的该例附值12685 564986 如果使用企业管理器录入的方法 那么该列实际存储的是 12685 564 如果使用T SQL语句的方法 例如代码如下 InsertInto表名Values 12685 564986 该列实际存储的是 12685 565 注意 数值型数据时 其小数位数必须小于精度 注意什么时候进行四舍五入 12 3 1数据类型 3 字符数据类型用来存储各种字母 数字符号和特殊符号 在使用时需要在其前后加上英文单引号或者双引号 1 Char 占用1个字节 其定义形式为 char n 如果实际数据的字符长度短于给定的最大长度 则多余的字节会被空格填充 如果实际数据的字符长度超过了给定的最大长度 则超过的字符将会被截断 使用双引号或单引号将字符型变量括起来 n的取值为1 8000 默认n的值为1 13 3 1数据类型 2 Varchar 可以存储长达8000个字符的可变长度字符串 和char类型不同varchar类型根据输入数据的实际长度而变化 其定义形式为 varchar n 当每列数据没有达到规定的字符数时 并不会在多余的字节上填充空格 可以有效的节省空间 3 Nchar 采用Unicode 统一字符编码标准 字符集每个Unicode字符用两个字节为一个存储单位 范围是1 4000 其定义形式为 nchar n 3 Nvarchar 使用Unicode字符集的Varchar数据类型 其定义形式为 nvarchar n 范围是0 4000 14 3 1数据类型 例1 某例的数据类型为char 18 而输入的字符串为 shanghai 则存储的字符是shanghai 表示空格 例2 表中某例的数据类型为varchar 18 而输入的字符串为 shanghai 则存储的字符是shanghai 注意 若存储的字符串长度不足n时 则在串尾添加空格 注意 若存储的字符串长度不足n时 按实际长度输入 15 3 1数据类型 3 日期和时间数据类型 1 Datetime 占用8个字节 用于存储日期和时间的结合体 可以存储从公元1753年1月1日零时起 公元9999年12月31日23时59分59秒之间的所有日期和时间 其精确度可达三百分之一秒 即3 33毫秒 当存储datetime数据类型时 默认的格式是 MMDDYYYYhh mmA M P M 当插入数据或者在其它地方使用datetime类型时 需要用单引号把它括起来 16 3 1数据类型 默认January1 190012 00A M 可以接受的输入格式如下 Jan31999 JAN31999January31999Jan199931 1 19901999 1 119993Jan和1999Jan3datetime数据类型允许使用 和 作为不同时间单位间的分隔符 2 Smalldatetime 存储从1900年1月1日到2079年6月6日内的日期 占4个字节 在没有指定小时以上的精度的数据时 会自动设置datetime和smalldatetime数据的时间为00 00 00 17 3 1数据类型 5 文本和图形数据类型 1 Text 容量可以在1 231个字节 在定义Text数据类型时 不需要指定数据长度 SQLServer会根据数据的长度自动为其分配空间 2 ntext 采用unicode标准字符集 用于存储大容量文本数据 其理论上的容量为2G个字节 3 Image 一组二进制的数据流 用于存储照片 目录图片或者图画 其理论容量为2G个字节 18 3 1数据类型 6 货币数据类型 1 Money 用于存储货币值 数值以一个正数部分和一个小数部分存储在两个4字节的整型值中 存储范围为 263 263 1 精度为货币单位的万分之一 2 Smallmoney 货币值有两个2字节整数组成 其存储范围为 213738 3368 213738 3367 当为money或smallmoney的表输入数据时 必须在有效位置前面加一个货币单位符号 如 或其它货币单位的记号 19 3 1数据类型 7 位数据类型Bit称为位数据类型 有两种取值 0和1 在输入0以外的其它值时 系统均把它们当1看待 8 二进制数据类型 1 Binary 其定义形式为binary n 数据的存储长度是固定的 即n 4字节 当输入的二进制数据长度小于n时 余下部分填充0 二进制数据类型的最大长度 即n的最大值 为8KB 常用于存储图像等数据 2 Varbinary 其定义形式为varbinary n 数据的存储长度是变化的 最大长度不可以超过8KB 在输入二进制常量时 需在该常量前面加一个前缀0 x 20 3 1数据类型 9 特殊数据类型 1 Timestamp 也称作时间戳数据类型 是一种自动记录时间的数据类型 主要用于在数据表中记录其数据的修改时间 它提供数据库范围内的唯一值 2 Uniqueidentifier 也称作唯一标识符数据类型 Uniqueidentifier用于存储一个16字节长的二进制数据类型 它是SQLServer根据计算机网络适配器地址和CPU时钟产生的全局唯一标识符代码 GloballyUniqueIdentifier 简写为GUID 10 新增数据类型 1 Bigint 占用8个字节 2 sql variant 用于存储除文本 图形数据和timestamp类型数据外的其它任何合法的SQLServer数据 3 table 用于存储对表或者视图处理后的结果集 21 3 1数据类型 3 1 2自定义数据类型 使用企业管理器创建 利用系统存储过程创建sp addtype typename type phystype system data type nulltype null type owner owner name 22 3 1数据类型 type 指定用户定义的数据类型的名称 system data type 指定相应的系统提供的数据类型的名称及定义 注意 不能使用timestamp数据类型 当所使用的系统数据类型有额外说明时 需要用引号将其括起来 null type 指定用户自定义数据类型的null属性 其值可以为 null notnull 或者 nonull 默认与系统默认的null属性相同 owner name 指定用户自定义数据类型的所有者 23 3 1数据类型 删除用户自定义数据1 使用企业管理器2 利用系统存储过程sp droptype typename type 24 3 2创建表 表是包含数据库中所有数据的数据库对象 用来存储各种各样的信息 在SQLServer2000中 一个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 也就是可以定义1024个字段 在同一数据库的不同表中 可以有相同的字段 但在同一个表中不允许有相同的字段 而且每个字段都要求数据类型相同 SQLServer2000提供了两种方法创建数据库表 第一种方法是利用企业管理器 EnterpriseManager 创建表 另一种方法是利用Transact SQL语句中的create命令创建表 25 3 2 1 利用EnterpriseManager创建表 在EnterpriseManager中 展开指定的服务器和数据库 打开想要创建新表的数据库 用右键单击表对象 从弹出的快捷菜单中选择新建表选项 或者在工具栏中选择图标 就会出现新建表对话框 在该对话框中 可以定义列的以下属性 列名称 数据类型 长度 精度 小数位数 是否允许为空 缺省值 标识列 标识列的初始值 标识列的增量值和是否有行的标识 然后根据提示进行设置 26 3 2 2使用T SQL创建表 例子 具体语法请看课本或者联机丛书 USESampleCreateTablecustomers CustomerIDintidentity 1 1 primarykey FirstNamevarchar 20 notnull LastNamevarchar 20 notnull Stu SexbitnotnullConstraintSexcheck Stu Sexin 1 0 Addressvarchar 100 Cityvarchar 20 Telvarchar 20 unique Companyvarchar 20 Birthdaydatetime Typetinyintdefault1 CustomerID 列定为标识列 种子值为1 增量为1 添加性别检查约束 此列要求非空 约束的名称叫Sex 27 3 3创建 删除和修改约束 3 3 1约束是SQLServer提供的自动保持数据库完整性的一种方法 1 主键 PrimaryKey 约束 一列或多列的组合唯一标识一行 实现实体完整性 表中所有行唯一 2 外键 ForeignKey 约束 一列或多列的组合 实现参照完整性 两个以上的表的数据一致性维护 外部键约束不能自动创建索引 需要用户手动创建 3 唯一性 Unique 约束 保证非主键的列不重复 4 检查 Check 约束 限制列的取值 5 默认值 Default 当字段没有明确给值时 有系统自动给出 6 空值 NULL 字段允许空 可以不输入值 否则必须有值 28 3 3创建 删除和修改约束 外部键约束用于强制参照完整性 提供单个字段或者多个字段的参照完整性 当使用外部键约束时 应该考虑以下几个因素 外部键约束提供了字段参照完整性 外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配 外部键约束不能自动创建索引 需要用户手动创建 用户想要修改外部键约束的数据 必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限 参考同一表中的字段时 必须只使用REFERENCES子句 不能使用外部键子句 一个表中最多可以有31个外部键约束 在临时表中 不能使用外部键约束 主键和外部键的数据类型必须严格匹配 29 3 4增加 删除和修改字段 3 4 1利用企业管理器增加 删除和修改字段3 4 2利用Transact SQL语言的ALTERTABLE子句增加 删除和修改字段 30 3 4增加 删除和修改字段 例 A 更改表以添加新列下例添加一个允许空值的列 而且没有通过DEFAULT定义提供值 各行的新列中的值将为NULL CREATETABLEtesta column aINT GOALTERTABLEtestaADDcolumn bVARCHAR 20 NULLGOEXECsp helptestaGODROPTABLEtestaGO注 添加的字段必须为允许空字段或者是默认字段 31 B 更改表以除去列下例修改表以删除一列 CREATETABLEtestb column aINT column bVARCHAR 20 NULL GOALTERTABLEtestbDROPCOLUMNcolumn bGOEXECsp helptestbGODROPTABLEtestbGO 3 4增加 删除和修改字段 32 3 4增加 删除和修改字段 设已经在数据库Stu中创建表Stu info 例1 在表Stu Info中增加一个新列 奖学金等级 USEStuALTERTABLEStu InfoADD奖学金等级tinyintNULLGO 33 3 4增加 删除和修改字段 例2 修改表Stu Info中已有列的属性 将名为 姓名 的列的长度由原来的8改为10 将名为 出生时间 的列的数据类型由原来的smalldatetime改为datetime USEStuALTERTABLEStu InfoALTERCOLUMN姓名char 10 ALTERCOLUMN出生时间datetimeGO 添加列时是否可以添加 NotNull 和 Default 约束 34 3 4增加 删除和修改字段 例3 在表stu info中删除名为奖学金等级的列 USEStuALTERTABLEStu InfoDROPCOLUMN奖学金等级GO 35 3 4增加 删除和修改字段 例4 在表Stu Info中删除一个带有约束的列 USEStuALTERTABLEStu InfoDROPCONSTRAINT约束名称GOALTERTABLEStu InfoDROPCOLUMN字段名GO 36 3 4增加 删除和修改字段 例5 在表stu info中添加一个带主键约束的列 altertabletestaddconstraintpkprimarykey stu id 37 3 4增加 删除和修改字段 例6 在表stu info中添加一个带检查约束的列 ALTERTABLEstu infoWITHNOCHECKADDCONSTRAINTssCHECK stu sexin 男 女 利用WITHNOCHECK来防止对现有行验证约束 从而允许该约束的添加 WITHCHECK和WITHNOCHECK子句不能用于PRIMARYKEY和UNIQUE约束 38 3 5查看表格 3 5 1查看表格中的数据select语句3 5 2重命名表格sp rena