数据库的实现

第二章数据库的实现回顾在需求分析阶段，设计数据库的一般步骤为？实体的映射关系有哪些？请简要说明三大范式的含义？目标掌握建库的SQL语句掌握建表的SQL语句掌握加约束的SQL语句掌握创建安全帐户的SQL语句回顾数据库的基础知识数据库文件的组成：主数据文件：*.mdf次要数据文件：*.ndf日志文件：*.ldf数据库的其他属性：文件存放位置，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文件容量设置：可以指定文件增长的最大值或不受限创建数据库CREATEDATABASE数据库名ON[PRIMARY](数据文件参数[，…n][文件组参数])[LOGON](日志文件参数[，…n])T-SQL创建数据库的语法：主文件组，可选参数，默认创建数据库示例1CREATEDATABASEstuDBONPRIMARY--默认就属于PRIMARY主文件组，可省略(NAME='stuDB_data',--主数据文件的逻辑名FILENAME='D:\project\stuDB_data.mdf',--主数据文件的物理名SIZE=5mb,--主数据文件初始大小MAXSIZE=100mb,--主数据文件增长的最大值FILEGROWTH=15%--主数据文件的增长率)LOGON(NAME='stuDB_log',FILENAME='D:\project\stuDB_log.ldf',SIZE=2mb,FILEGROWTH=1MB)GO数据文件的具体描述日志文件的具体描述演示创建数据库1：一个数据文件和一个日志文件创建数据库示例2CREATEDATABASEemployeesON(/*-主数据文件的具体描述-*/NAME='employee1',FILENAME='D:\project\employee1_Data.mdf',SIZE=10,FILEGROWTH=10%),(/*-次要数据文件的具体描述-*/NAME='employee2',FILENAME='D:\project\employee2_Data.ndf',SIZE=20,MAXSIZE=100,FILEGROWTH=1)LOGON(/*-日志文件1的具体描述-*/NAME='employeelog1',FILENAME='D:\project\employeelog1_Log.ldf',SIZE=10,FILEGROWTH=1),(/*-日志文件2的具体描述-*/NAME='employeelog2',FILENAME='D:\project\employeelog2_Log.ldf',SIZE=10,MAXSIZE=50,FILEGROWTH=1)GO演示创建数据库2：多个数据文件和多个日志文件删除数据库如果SQLServer中已存在数据库stuDB，运行下列语句，会出现什么问题？CREATEDATABASEstuDBON(....)LOGON(....)GO演示：再次运行示例1如何知道服务器上已有的数据库USEmaster--设置当前数据库为master，以便访问sysdatabases表GOIFEXISTS（SELECT*FROMsysdatabasesWHEREname='stuDB'）DROPDATABASEstuDBCREATEDATABASEstuDBON(…..)LOGON(…)GO删除数据库删除数据库的语法：DROPDATABASE数据库名EXISTS（）语句：检测是否存在stuDB数据库如果存在stuDB数据库，则删除回顾表的基础知识建表的基本步骤：确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系SQLServer中的数据类型整型数据：intsmallint浮点数据：floatnumeric……字符数据固定长度：char(6)可变长度:varchar(10)unicode编码：nchar(10)……布尔数据（真/假）bit:1/0货币数据money日期数据datetime创建表建表的语法CREATETABLE表名(字段1数据类型列的特征，字段2数据类型列的特征，...)列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。建表示例1USEstuDB--将当前数据库设置为stuDBGOCREATETABLEstuInfo/*-创建学员信息表-*/(stuNameVARCHAR(20)NOTNULL,--姓名，非空（必填）stuNoCHAR(6)NOTNULL,--学号，非空（必填）stuAgeINTNOTNULL,--年龄，INT类型默认为4个字节stuIDNUMERIC(18,0),--身份证号stuSeatSMALLINTIDENTITY(1,1),--座位号，自动编号stuAddressTEXT--住址，允许为空，即可选输入)GONUMERIC(18,0)代表18位数字，小数位数为0演示：创建学员信息表stuInfoIDENTITY（起始值，递增量）建表示例2CREATETABLEstuMarks(ExamNoCHAR(7)NOTNULL,--考号stuNoCHAR(6)NOTNULL,--学号writtenExamINTNOTNULL,--笔试成绩LabExamINTNOTNULL--机试成绩)GO演示：创建学员成绩表stuMarks删除表如果当前数据库中已存在stuInfo表，再次创建时系统将提示出错。如何解决呢？删除表删除表的语法：DROPTABLE表名USEstuDB--将当前数据库设置为stuDB，以便在stuDB数据库中建表GOIFEXISTS（SELECT*FROMsysobjectsWHEREname=’stuInfo’）DROPTABLEstuInfoCREATETABLEstuInfo/*-创建学员信息表-*/(…..)GO回顾SQLServer的约束约束的目的：确保表中数据的完整型常用的约束类型：主键约束（PrimaryKeyConstraint）：要求主键列数据唯一，并且不允许为空唯一约束（UniqueConstraint）：要求该列唯一，允许为空，但只能出现一个空值。检查约束（CheckConstraint）：某列取值范围限制、格式限制等，如有关年龄的约束默认约束（DefaultConstraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”外键约束（ForeignKeyConstraint）：用于两表间建立关系，需要指定引用主表的那列添加约束添加约束的语法：ALTERTABLE表名ADDCONSTRAINT约束名约束类型具体的约束说明约束名的取名规则推荐采用：约束类型_约束字段主键（PrimaryKey）约束：如PK_stuNo唯一（UniqueKey）约束：如UQ_stuID默认（DefaultKey）约束：如DF_stuAddress检查（CheckKey）约束：如CK_stuAge外键(ForeignKey)约束：如FK_stuNo添加约束示例ALTERTABLEstuInfoADDCONSTRAINTPK_stuNoPRIMARYKEY(stuNo)ALTERTABLEstuInfoADDCONSTRAINTUQ_stuIDUNIQUE(stuID)ALTERTABLEstuInfoADDCONSTRAINTDF_stuAddressDEFAULT('地址不详')FORstuAddressALTERTABLEstuInfoADDCONSTRAINTCK_stuAgeCHECK(stuAgeBETWEEN15AND40)ALTERTABLEstuMarksADDCONSTRAINTFK_stuNoFOREIGNKEY(stuNo)REFERENCESstuInfo(stuNo)GO演示：给学员信息表stuInfo添加约束添加主键约束（stuNo作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求年龄只能在15－40岁之间添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）添加约束示例演示：查看约束结果删除约束如果错误地添加了约束，我们还可以删除约束删除约束的语法ALTERTABLE表名DROPCONSTRAINT约束名例如：删除stuInfo表中地址默认约束ALTERTABLEstuInfoDROPCONSTRAINTDF_stuAddress回顾SQLServer的安全模型数据库1数据库2数据库3表1表2表1表2表1表2增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐号SQLServer三层安全管理登录方式回顾登录验证有两种方式：SQL身份验证：适合于非windows平台的用户或Internet用户，需要提供帐户和密码Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证登录帐户相应有两种：SQL帐户和Windows帐户创建登录添加Windows登录帐户EXECsp_grantlogin‘jbtraining\S26301‘添加SQL登录帐户EXECsp_addlogin‘zhangsan',‘1234’演示创建登录EXEC表示调用存储过程，存储过程类似C语言的函数内置的系统管理员帐户sa密码默认为空，建议修改密码域名\用户名用户名,密码创建登录创建数据库用户创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：EXECsp_grantdbaccess‘登录帐户名’，’数据库用户名’其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。USEstuDBGOEXECsp_grantdbaccess'jbtraining\S26301','S26301DBUser'EXECsp_grantdbaccess'zhangsan','zhangsanDBUser'在stuDB数据库中添加两个用户创建数据库用户dbo用户表示数据库的所有者（DBOwner）无法删除dbo用户，此用户始终出现在每个数据库中guest用户适用于没有数据库用户的登录帐号访问每个数据库可有也可删除系统内置的数据库用户向数据库用户授权EmployeesEmployeeID123LastNameDavolioFullerLeverlingFirstNameNancyAndrewJanetReportsTo22...deleteFROMEmployeesSELECT*FROMEmployees权限的类型2-1授权的语法为：GRANT权限[ON表名]TO数据库用户USEstuDBGO/*--为zhangsanDBUser分配对表stuInfo的select,insert,update权限--*/GRANTselect,insert,updateONstuInfoTOzhangsanDBUser/*--为S26301DBUser分配建表的权限--*/GRANTcreatetableTOS26301DBUser权限的类型2-2没有授予delete权限总结2-1数据库的物理实现一般包括：创建数据库创建表添加各种约束创建数据库的登录帐户并授权创建数据库或表时一般需要预先检测是否存在该对象数据库从master系统数据库的sysdatabases表中查询表从该数据库的系统表sysobjects表中查询总结2-2访问SQLServer某个数据库中的某个表，需要三层验证：是否是SQLServer的登录帐户是否是该数据库的用户是否有足够的权限访问该表