第5章表的管理.ppt

第5章表的管理 本章内容 5 1SQLServer表概述5 2表的创建与维护5 3表中数据的维护 5 1SQLServer表概述 在SQL中 一个数据库最多可以存储20亿个表 表是数据库的实体 由行和列组成 每行最多可存储8092字节的内容 每个表最多可以有1024列 设计数据库时 需要考虑的问题 共有哪些表 每个表由哪些列组成 每列的数据类型及列宽 主键 约束与规则等 5 1SQLServer表概述 因此 本章的任务就是建立表 管理表 建立表数据类型约束表的定义 Create Drop Alter 管理表插入数据 Insert 修改数据 Update 删除数据 Delete 5 1 1数据类型简介 数据类型SQLServer支持4种基本数据类型 字符和二进制数据类型 日期时间数据类型 逻辑数据类型 数字数据类型 用于各类数据值的存储 检索和解释 5 1 1数据类型简介 1 字符和二进制数据类型 1 字符数据类型字符数据类型用于存储汉字 英文字母 数字符号和其他各种符号 char数据类型varchar数据类型text数据类型SQLServer允许使用多国语言 采用Unicode标准字符集 为此SQLServer提供多字节的字符数据类型 nchar n nvarchar n 和ntext nchar char varchar与nvarchar的区别 char是以固定长度的 使用时往往要用trim 函数把两边的空格去掉Varchar存储变长数据 但存储效率没有Char高Nchar类型和Nvarchar 为了与其他多种字符的转换 如中文 音标等 字节的存储大小是所输入字符个数的两倍 就是说它是双字节来存储数据的char varchar一般适用于英文和数字 Nchar Nvarchar适用中文和其他字符 其中N表示Unicode常量 可以解决多语言字符集之间的转换问题 nchar char varchar与nvarchar的区别 问题 namechar 10 namenchar 10 分别能存放多少个汉字 为什么 解答 namechar 10 namenchar 10 分别能存放5个10个汉字原因 char与varchar以单字节形式存放 nchar与nvarchar以双字节形式存放 几种字符类型的最大长度 Char Varchar 最多可以存储8000个字符Nchar Nvarchar 最多可以存储4000个字符text ntext 最多可以存储230 1个字符 5 1 1数据类型简介 2 二进制数据类型SQLServer二进制数据类型用于存储二进制数或字符串 SQLServer的3种有效二进制数据类型 binary n 数据类型 默认n 1 存放空间n 4个字节 取值1 8000varbinary n 数据类型 基本同上 但空间足够时取实际长度image数据类型 与next类型类似 可存1 231 1字节的二进制 通常用来存放word文档 Excel表格 图像等 5 1 1数据类型简介 2 日期时间数据类型日期时间数据类型用于存储日期和时间数据 SQLServer支持两种日期时间数据类型 datetime和smalldatetime Datetime 1753 1 1 9999 12 31Smalldatetime 1900 1 1 2079 6 6 5 1 1数据类型简介 3 逻辑数据类型SQLServer的逻辑数据类型为bit 也称为位数据类型 适用于判断真 假的场合 长度为一个字节 取值 1 0 NULL注意事项 非0当1处理 位列不允许建索引 如果一个表不多于8个位列 合起来用一个字节存储 5 1 1数据类型简介 4 数字数据类型SQLServer提供了多种方法存储数值 SQLServer的数字类型大致可分为4种基本类型 1 整数数据类型有4种整数数据类型 int smallint tinyint和bigint 用于存储不同范围的值 int 存储长度4字节smallint 存储长度2字节tinyint 存储长度1字节bigint 存储长度8字节 只要有可能 列尽量取整型 5 1 1数据类型简介 4 数字数据类型 1 整数数据类型 2 浮点数据类型浮点数据用来存储系统所能提供的最大精度保留的实数数据 由于近似的运算存在误差 因此不能用于需要固定精度的运算 如货币 float n 可精确到第15位小数 占8个字节的存储空间 n为指定的精度real n 可精确到第7位小数 占4个字节的存储空间 5 1 1数据类型简介 3 精确数值数据类型精确数值数据类型用于存储有小数点且小数点后位数确定的实数 格式 decimal p s numeric p s 其中 p指定精度 即小数点左边和右边可以存储的十进制数字的最大个数 s指定小数位数 例如 decimal 5 3 输入87 341结果如何 5 1 1数据类型简介 4 货币数据类型除了decimal和numeric类型适用于货币数据的处理外 SQLServer还专门提供了两种货币数据类型 money和smallmoney 显示货币数据值时 小数部分仅保留2位有效数字 5 1 1数据类型简介 5 其他数据类型除了以上4种基本数据类型 SQLServer还支持其他一些数据类型 1 Timestamp 时间戳数据类型 用于自动记录插入或删除操作的时间 2 sql variant 可变数据类型 该类型的变量可用来存放大部分SQLServer数据类型的值 最大长度为8016字节 不支持text ntext timestamp和sql variant类型 3 table 表类型 这是一种特殊的数据类型 存储供以后处理的结果集 4 uniqueidentifier GUID类型 GlobalUniqueIDentifier 全局惟一标识符 5 1 2空值和默认值 空值 NULL 空值不同于空白 空字符串 或数值零 通常表示未填写 未知 Unknown 不可用或将在以后添加的数据 默认值 是指表中数据的默认取值 默认值对象是数据库的对象不依附于具体的表对象 即默认值对象的作用范围是整个数据库 约束 1 非空值约束 NotNull 2 默认约束 Default 3 惟一性约束 Unique 4 主键约束 PrimaryKey 也称主关键字约束 5 外键约束 ForeignKey 也称为外部关键字约束 5 2表的创建与维护 使用SQLServer的管理平台对表进行操作 5 2表的创建与维护 创建表CREATETABLE表名 修改表ALTERTABLE表名 删除表DROPTABLE表名 5 2表的创建与维护 例5 3对计算列使用表达式 CREATETABLEsalarys 姓名varchar 10 基本工资money 奖金money 总计AS基本工资 奖金 5 2表的创建与维护 例5 4定义表autouser自动获取用户名称 CREATETABLEautouser 编号intidentity 1 1 NOTNULL 用户代码varchar 18 登录时间ASGetdate 用户名ASUser name 5 2表的创建与维护 例5 5创建临时表 CREATETABLE students 学号varchar 8 姓名varchar 10 性别varchar 2 班级varchar 10 临时表用来暂存数据 表示本地临时表 在当前数据库内使用 表示全局临时表 可在所有数据库在使用 5 3表中数据的维护 对表中数据进行维护有两种方法 使用SQLServer管理平台使用Transact SQL语句 必须熟练掌握 Insert Select Update Delete 表中数据的维护 求是指维护表中的数据 4种基本操作 添加或插入新数据检索现有数据更新现有数据删除现有数据 5 3表中数据的维护 对表中数据进行维护方法之一使用SQLServer管理平台实际操作 在数据库中打开表直接实现插入 修改 删除问题1 使用SQLServer管理平台可以实现少量的插入 但很难实现批量的插入 有条件的大量删除与修改问题2 数据库中的数据是交给用户方进行管理的 面对用户的是用户与数据库操作的接口 也就是不能在SQLServer管理平台上操作 如何实现 应用程序开发者用SQL语句实现 5 3 1插入数据 插入数据语法格式如下 INSERT INTO table name view name column list VALUES value list derived table execute statement DEFAULTVALUES 5 3 1插入数据 注意 Insert Values 一次只能插入一行如果插入的数据违反约束或规则 则插入会失败插入的值可以是指定的值集 也可以是Select语句的执行得到的一行 column list 与 value list 中顺序必须一致 value list 中的值必须是常量 变量或表达表 表达式中不能包含Select或Execute语句 5 3 1插入数据 例5 10使用简单的INSERT语句 USESalesGOINSERTSupplierVALUES S001 华科电子有限公司 施宾彬 朝阳路56号 2636565 例5 11显式指定列列表 INSERTSales dbo Supplier supplier id supplier name linkman name address telephone VALUES S001 华科电子有限公司 施宾彬 朝阳路56号 2636565 5 3 1插入数据 INSERTSales dbo Supplier Supplier id Supplier name telephone VALUES S003 韦力电子实业公司 4561681 INSERTSales dbo Supplier Supplier name telephone Supplier id VALUES 韦力电子实业公司 4561681 S003 课堂练习 1 将一个新学生元组 学号 200215128 姓名 陈冬 性别 男 所在系 IS 年龄 18岁 插入到Student表中 INSERTINTOStudent Sno Sname Ssex Sdept Sage VALUES 200215128 陈冬 男 IS 18 课堂练习 2 将学生张成民的信息插入到Student表中 INSERTINTOStudentVALUES 200215126 张成民 男 18 CS 3 插入一条选课记录 200215128 1 INSERTINTOSC Sno Cno VALUES 200215128 1 INSERTINTOSCVALUES 200215128 1 NULL 5 3 2修改数据 修改数据语法格式 UPDATE table name view name SET column name variable expression n FROM n WHERE 5 3 2修改数据 例5 14使用简单的UPDATE语句 UPDATECustomer2SETlinkman name 佚名 address NULL telephone 例5 15在UPDATE语句中使用WHERE子句 UPDATECustomer2SETtelephone 0731 telephoneWHERELEN telephone 7 5 3 2修改数据 例5 16在UPDATE语句中使用来自另一个表的信息 UPDATESell OrderSETcost Sell Order Order Num Goods Unit Price 1 Sell Order Discount FROMSell Order GoodsWHERESell Order Goods id Goods Goods id 5 3 2修改数据 例5 16在UPDATE语句中使用来自另一个表的信息 UPDATESell OrderSETcost SO Order Num G Unit Price 1 SO Discount FROMSell OrderSO GoodsGWHERESO Goods id G Goods id 5 3 2修改数据 例5 17在UPDATE语句中使用SELECT TOP语句 UPDATEGoodsSETUnit Price Unit Price 0 9FROMGoods SELECTTOP10Goods ID SUM Order Num ASTotal NumFROMSell OrderGROUPBYGoods IDORDERBYTotal NumDESC ASTotal SumWHEREGoods Goods id Total Sum Goods id 课堂练习 1 将学生200215121的年龄改为22岁 UPDATEStudentSETSage 22WHERESno 200215121 2 将所有学生的年龄增加1岁 UPDATEStudentSETSage Sage 1 5 3 3删除数据 删除数据1 DELETE语句DELETE语句可删除表或视图中的一行或多行 每一行的删除都将被记入日志 DELETE语句的语法格式如下 DELETE FROM table name view name FROM n WHERE 5 3 3删除数据 例5 18不带参数使用DELETE命令删除所有行 USESalesGODELETECustomer2本例从Customer2表中删除所有行 例5 19带WHERE子句的DELETE语句 有条件地删除行 DELETEFROMSell OrderWHERECustom ID C0003 5 3 3删除数据 例5 20在DELETE中使用联接或子查询 DELETESell OrderFROMSell OrderSOINNERJOINcustomer2CONSO customer id C customer idWHEREC addressLIKE 东方市 等同于下列命令DELETESell OrderFROMSell OrderSO customer2CWHERESO customer id C customer idANDC addressLIKE 东方市 课堂练习 1 删除学号为200215128的学生记录 2 删除所有的学生选课记录 DELETEFROMStudentWHERESno 200215128 DELETEFROMSC 课堂练习 3 删除计算机科学系所有学生的选课记录 DELETEFROMSCWHERESno SELETESnoFROMStudentWHERESdept 计算机科学系 课堂练习 3 删除计算机科学系所有学生的选课记录 DELETE