项目4网上书城数据库表的管理.ppt

项目四 网上书城数据库表的管理 项目知识要点 单词学习 1 Constraint 约束2 PrimaryKey 主键3 ForeignKey 外键4 Binary 二进制5 Check 检查6 Unique 唯一7 Alter 修改8 Insert 插入9 Update 更新10 Delete 删除 掌握数据表的基础知识 表的定义表是包含数据库中所有数据的数据库对象 在表中 数据成二维行列格式 每一行代表一个唯一的记录 每一列代表一个域 表4 1顾客信息表 掌握数据表的基础知识 列名列名是用来访问表中具体域的标识符 列名必须遵循下列规则 1 列名是可以含有从1到128的ASCII码字符 它的组成包括字母 下划线 符号以及数字 2 不要给列名命名为与SQL关键字相同的名字 比如SELECT IN DESC等 3 列名应该反映数据的属性 SQLServer的数据类型 思考 电话号码一般使用什么数据类型存储 性别一般使用什么数据类型存储 年龄信息一般使用什么数据类型存储 照片信息一般使用什么数据类型存储 薪水一般使用什么数据类型存储 网上书城数据表的设计与创建 表的设计步骤 1 表的命名方式 2 表中每一列的名称 数据类型及其长度 3 表中的列是否允许空值 是否唯一 是否要进行默认设置或添加用户定义约束 4 表间的关系 即确定哪些列是主键 哪些是外键 项目中的部分表 顾客信息表 Customers 项目中的部分表 订单信息表 Orders 项目中的部分表 订单详细表 OrderDetails 创建数据库表 使用SSMS向导创建表 例4 2 1 在SQLServer2005中使用 SQLServerManagementStudio 创建BookShop数据库中的Customers表 顾客信息表 详细步骤如下 1 启动SSMS 登录服务器类型为 数据库引擎 并使用Windows或SQLServer身份认证建立连接 2 在 对象资源管理器 中依次展开 数据库 节点 BookShop 节点 右键单击 表 节点 选择 新建表 命令 如图4 1所示 图4 1选择 新建表 3 打开 表设计器 窗口 在 列名 下的编辑框中输入列名 CID 然后点击 数据类型 下的下拉框 拖动下拉框的滚动条 选择 char 10 如图4 2所示 图4 2表设计器 4 在 表设计器 窗口的 列属性 选项卡中的 长度 域的编辑框中输入6 如图4 3所示 输入完毕后 再看 数据类型 下的下拉框内容由char 10 变为char 6 5 单击 表设计器 窗口的 允许空 列中的小方框 使得小方框中 去掉 就符合了 CID 列不允许为空的要求 图4 3修改字符类型长度 6 类似地 重复步骤 2 5 在 表设计器 窗口添加顾客信息表的其它7个字段 效果如图4 4所示 图4 4顾客信息表8个字段 7 设置主键 在 表设计器 窗口中选定第一个字段 CID 再执行菜单命令 表设计器 设置主键 即可按要求将字段 CID 设为主键 如图4 5所示 图4 5设置顾客信息表的主键 8 修改表名称 在 表设计器 窗口右侧 属性 面板中 名称 域的编辑框中输入表名称 Customers 如图4 6所示 表名称输入完后不是立即生效的 要在保存表后 表名称修改才能生效 图4 6修改表名称 9 保存表 单击工具栏中的 保存 按钮即可以保存表 新表创建后 在 对象资源管理器 中展开 数据库 节点中的数据库节点 BookShop 可以查看到刚才所建的表 如图4 7所示 图4 7创建好的Customers表 使用CREATETABLE语句创建表 CREATETABLE 列级完整性约束条件 列级完整性约束条件 使用CREATETABLE语句创建表 例4 2 3 为了保存顾客基本信息 需要在BookShop数据库中创建一个名为 Customers 的表 使用T SQL语句完成创建表的过程 使用CREATETABLE语句创建表 详细步骤如下 1 成功登录SQLServer服务器后 在SSMS界面中 单击 新建查询 按钮 打开一个SQLQuery窗口 2 在SQLQuery窗口输入如下命令 然后单击按钮 分析输入的T SQL语法是否有错 然后再点击 执行 按钮 即能创建好顾客信息表 如图4 13所示 使用CREATETABLE语句创建表 UseBookShop 打开数据库Go 批处理结束语句CREATETABLECustomers CIDchar 6 NOTNULLPRIMARYKEY PRIMARYKEY表示主键CTrueNamevarchar 30 NOTNULL Cpasswordvarchar 30 NOTNULL CSexchar 2 NOTNULL CAddressvarchar 50 NULL CMobilevarchar 11 NOTNULL CEmailvarchar 50 NULL CRegisterDatedatetimeNOTNULL 使用CREATETABLE语句创建表 在SQLQuery窗口输入T SQL命令 如果当前数据库中已存在Customers表 再次创建时系统将提示出错 如何解决呢 USEBookShop 将当前数据库设置为BookShop 以便在BookShop数据库中建表GOIFEXISTS SELECT FROMsysobjectsWHEREname Customers DROPTABLECustomersCREATETABLECustomers 创建顾客表 GO 标识列 表中没有合适的列作为主键怎么办 标识列 USEstuDB 将当前数据库设置为stuDBGOCREATETABLEstuInfo 创建学员信息表 stuNameVARCHAR 20 NOTNULL 姓名 非空 必填 stuNoCHAR 6 NOTNULL 学号 非空 必填 stuAgeINTNOTNULL 年龄 INT类型默认为4个字节stuSeatSMALLINTIDENTITY 1 1 座位号 自动编号stuAddressTEXT 住址 允许为空 即可选输入 GO IDENTITY 起始值 递增量 思考 标识列允许为字符数据类型吗 如果标识列A的初始值为1 增长量为2 则输入三行数据以后 再删除两行 下次再输入数据行的时候 标识值从多少开始 数据完整性 数据完整性是指数据库中数据的准确性和一致性 如果两个或更多的表由于其存储的信息而相互关联 那么只要修改了其中一个表 与之相关的所有表都要做出相应的修改 如果不这么做 存储的数据就会不再准确 也就是说 推动了数据的完整性 数据完整性主要分为四类 实体完整性 域完整性 引用完整性和用户定义完整性 数据完整性 数据存放在表中 数据完整性的问题大多是由于设计引起的 创建表的时候 就应当保证以后数据输入是正确的 错误的数据 不符合要求的数据不允许输入 创建表 保证数据的完整性 实施完整性约束 完整性包括 2 1 输入的类型是否正确 年龄必须是数字输入的格式是否正确 身份证号码必须是18位是否在允许的范围内 性别只能是 男 或者 女 是否存在重复输入 学员信息输入了两次是否符合其他特定要求 信誉值大于5的用户才能够加入会员列表 完整性包括 2 2 实体完整性 约束方法 唯一约束 主键约束 标识列 域完整性 约束方法 限制数据类型 检查约束 外键约束 默认值 非空约束 引用完整性 约束方法 外键约束 自定义完整性 约束方法 规则 存储过程 触发器 思考 学员姓名允许为空吗 家庭地址允许为空吗 电子邮件信息允许为空吗 考试成绩允许为空吗 思考 在主键列输入的数值 允许为空吗 一个表可以有多个主键吗 在一个学校数据库中 如果一个学校内允许重名的学员 但是一个班级内不允许学员重名 可以组合班级和姓名两个字段一起来作为主键吗 选择主键的原则 最少性尽量选择单个键作为主键稳定性尽量选择数值更新少的列作为主键 SQLServer的约束 约束的目的 确保表中数据的完整型常用的约束类型 主键约束 PrimaryKeyConstraint 要求主键列数据唯一 并且不允许为空唯一约束 UniqueConstraint 要求该列唯一 允许为空 但只能出现一个空值 检查约束 CheckConstraint 某列取值范围限制 格式限制等 如有关年龄的约束默认约束 DefaultConstraint 某列的默认值 如我们的男性学员较多 性别默认为 男 外键约束 ForeignKeyConstraint 用于两表间建立关系 需要指定引用主表的那列 约束名的取名规则推荐采用 约束类型 约束字段主键 PrimaryKey 约束 如PK stuNo唯一 UniqueKey 约束 如UQ stuID默认 DefaultKey 约束 如DF stuAddress检查 CheckKey 约束 如CK stuAge外键 ForeignKey 约束 如FK stuNo PRIMARYKEY约束 PRIMARYKEY约束表的一列或几列的组合的值在表中唯一地指定一行记录 这样的一列或多列称为表的主键 PRIMARYKEY 通过它可强制表的实体完整性 例4 3 1 使用PRIMARYKEY约束对数据库BookShop实施数据完整性 1 使用SQLServerManagementStudio管理PRIMARYKEY约束 1 启动SSMS 进入新建表 如果表已经建立好 则进入修改表的状态 如图4 11所示 2 单击要设置的PRIMARYKEY约束的列 例如 CID 选择工具栏上的按钮 也可以右键选择 设置主键 选项 创建主键约束 如图4 12所示 3 创建主键结束后 在对应的列名前有标志 此时 该列的 允许空 也改变为 非空 如图4 13所示 图4 11选择修改表 图4 12选择设置主键 图4 13创建PRIMARYKEY约束 使用T SQL管理PRIMARYKEY约束 CREATETABLECustomers CIDchar 6 NOTNULLConstraintPK CIDPRIMARYKEY CTrueNamevarchar 30 NOTNULL Cpasswordvarchar 30 NOTNULL CSexchar 2 NOTNULL CAddressvarchar 50 NULL CMobilevarchar 11 NOTNULL CEmailvarchar 50 NULL CRegisterDatedatetimeNOTNULL 如果在订单详情表中不设置编号 则可以将表中 订单编号 商品编号 作为订单详情的唯一标识 在创建数据表OrderDetails时 为OID和GID的组合设置PRIMARYKEY约束 完成语句如下所示 USEBookShopGOCREATETABLEOrderDetails OIDchar 14 NOTNULL GIDchar 6 NOTNULL OdPricefloatNOTNULL OdNumberintNOTNULL CONSTRAINTPK OID GIDPRIMARYKEY OID GID 图4 14组合主键 FOREIGNKEY约束FOREIGNKEY约束标识表之间的关系 用于强制参照完整性 为表中一列或者多列数据提供参照完整性 FOREIGNKEY约束也可以参照自身表中的其他列 这种参照称为自参照 主外键关系 1 演示建立数据库关系图 主外键关系 2 演示建立主 外键关系 主外键关系 3 当主表中没有对应的记录时 不能将记录添加到子表 订单表中不能出现在顾客表中不存在的客户 不能更改主表中的值而导致子表中的记录孤立 把顾客表中的客户编号改变了 订单表中的顾客编号也应当随之改变 子表存在与主表对应的记录 不能从主表中删除该行 不能把有订单的顾客删除了删除主表前 先删子表 先删订单表 后删除顾客表 创建检查约束 演示建立检查约束 使用T SQL管理CHECK约束 CREATETABLECustomers CIDchar 6 NOTNULLConstraintPK CIDPRIMARYKEY CTrueNamevarchar 30 NOTNULL Cpasswordvarchar 30 NOTNULL CSexchar 2 NOTNULLCHECK CSex 男 orCSex 女 CAddressvarchar 50 NULL CMobilevarchar 11 NOTNULL CEmailvarchar 50 NULL CRegisterDatedatetimeNOTNULL UNIQUE约束为了保证商品类别名称表Category中的名称不重复 在创建数据表Category时 为CaName设置UNIQUE约束 完成语句如下所示 CREATETABLECategory CaIDchar 2 NOTNULLPRIMARYKEY CaNamevarchar 20 NOTNULL CaDeletedbitNOTNULL CONSTRAINTUN CaNameUNIQUE CaName DEFAULT约束需要Customers表中输入数据时 为CRegisterDate提供一个默认值为当天时间 以保证非空性或简化用户输入 其完成语句如下所示 CREATETABLECustomers CIDchar 6 NOTNULLConstraintPK CIDPRIMARYKEY CTrueNamevarchar 30 NOTNULL Cpasswordvarchar 30 NOTNULL CSexchar 2 NOTNULLCHECK CSex 男 orCSex 女 CAddressvarchar 50 NULL CMobilevarchar 11 NOTNULL CEmailvarchar 50 NULL CRegisterDatedatetimeNOTNULLDEFAULT GetDate 数据表的操作 使用T SQL语句修改表结构 ALTERTABLE ALTERCOLUMN ADD DROP 1 使用ADD子句增加新列或新的完整性约束条件在Customers表中增加出生日期 CBirth 列 其数据类型为datetime类型 该操作使用T SQL语句完成如下 在Orders表中增加外键约束 CID列为外键与Customers表的主键CID对应 该操作使用T SQL语句完成如下 ALTERTABLECustomersADDCBirthdatetime ALTERTABLEOrdersADDCONSTRAINTFK Orders CustomersFOREIGNKEY CID REFERENCESCustomers CID 2 使用DROP子句删除指定的完整性约束条件 3 使用ALTERCOLUMN子句修改基本表的列定义或约束将Customers表CTrueName列的数据类型改为char型 宽度为30 且该列允许为空值 该操作使用T SQL语句完成如下 ALTERTABLECategoryDROPCONSTRAINTUN CaName ALTERTABLECustomersALTERCOLUMNCTrueNamechar 30 NULL 数据表的删除考虑到不需要BookShop数据库的购物车表ShopCar 要将该表从BookShop数据库中删除 该操作使用T SQL语句完成 完成语句如下所示 DROPTABLEShopCar 管理网上书城系统数据表的数据 例4 3 6 在SQLServer2005的SQLServerManagementStudio中完成Customers表中记录的添加 删除和修改等操作 INSERT INTO VALUES 1 插入所有列 新顾客信息录入 顾客信息如表所示 添加到Customers表中 2 插入指定列新顾客信息录入 顾客信息地址CAddress和电子邮箱CEmail尚缺 只能将该顾客的部分信息如表所示添加到Customers表中 INSERTINTOCustomersVALUES C0011 李平 123456 女 广东中山市 1351543876 liping 2009 9 6 INSERTINTOCustomers CID CTrueName Cpassword CSex CMobile CRegisterDate VALUES C0012 张先明 123456 男2009 10 6 3 插入多条记录多名顾客信息录入 顾客信息如表所示 添加到Customers表中 USEBookShopGOINSERTINTOCustomersVALUES C0013 韩志国 123456 男 NULLhanzg 2010 1 16 INSERTINTOCustomersVALUES C0014