《数据库的实现》PPT课件.ppt

第八章第八章数据库的实现数据库的实现数据库的实现数据库的实现11课程内容回顾课程内容回顾l需求分析阶段需求分析阶段,数据库设计的一般步骤有哪些？数据库设计的一般步骤有哪些？l如何将如何将E-R图转换成数据表图转换成数据表?l简单描述简单描述3大范式大范式22本章目标本章目标l使用使用SQL语句创建数据库和表语句创建数据库和表l使用使用SQL语句创建约束语句创建约束l创建安全帐户的创建安全帐户的SQL语句语句33数据库基础知识回顾数据库基础知识回顾l创建数据库时创建数据库时:指定数据库名称指定数据库名称主数据库文件和日志文件的名称和大小。主数据库文件和日志文件的名称和大小。l数据库文件的组成数据库文件的组成:主数据文件：主数据文件：*.mdf次要数据文件：次要数据文件：*.ndf 日志文件：日志文件：*.ldfl数据库的其他主要属性数据库的其他主要属性:文件存放位置分配的初始空间，属于哪个文件组。文件存放位置分配的初始空间，属于哪个文件组。文件的增长设置，可以按百分比或实际大小指定增长速度。文件的增长设置，可以按百分比或实际大小指定增长速度。文件容量设置，可以指定文件增长的最大值或不受限文件容量设置，可以指定文件增长的最大值或不受限 44创建数据库创建数据库CREATE DATABASE 数据库名数据库名ON PRIMARY(NAME=逻辑文件名逻辑文件名,FILENAME=物理文件名物理文件名 ,SIZE=大小大小 ,MAXSIZE=最大容量最大容量|UNLIMITED ,FILEGROWTH=增长量增长量),。nLOG ON (NAME=逻辑文件名逻辑文件名,FILENAME=物理文件名物理文件名 ,SIZE=大小大小 ,MAXSIZE=最大容量最大容量|UNLIMITED ,FILEGROWTH=增长量增长量),。n建库语法建库语法 55创建数据库示例创建数据库示例 1USE masterGOCREATE DATABASE empDB ON PRIMARY -主文件组，不能省略主文件组，不能省略/*-数据文件的具体描述数据文件的具体描述-*/(NAME=empDB_data,-主数据文件的逻辑名主数据文件的逻辑名FILENAME=D:DataempDB_data.mdf,-主数据文件的物理名主数据文件的物理名SIZE=5MB,-主数据文件的初始大小主数据文件的初始大小MAXSIZE=15MB,-主数据文件增长的最大值主数据文件增长的最大值(UNLIMITED为增长不受限制为增长不受限制)FILEGROWTH=20%)-文件的增长率文件的增长率LOG ON/*-日志文件的具体描述，各参数含义同上日志文件的具体描述，各参数含义同上-*/(NAME=empDB_log,FILENAME=D:DataempDB_log.ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO 演示创建数据库演示创建数据库1：一个数据文件和一个日志文件：一个数据文件和一个日志文件 66创建数据库示例创建数据库示例 2USE masterGOCREATE DATABASE Archive ON PRIMARY /*-主数据文件的具体描述主数据文件的具体描述-*/(NAME=Arch1,FILENAME=d:dataarchdat1.mdf,SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),/*-次要数据文件次要数据文件1的具体描述的具体描述-*/(NAME=Arch2,FILENAME=d:dataarchdat2.ndf,SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),演示创建数据库演示创建数据库2：多个数据文件和多个日志文件：多个数据文件和多个日志文件 77创建数据库示例创建数据库示例 2/*-次要数据文件次要数据文件2的具体描述的具体描述-*/(NAME=Arch3,FILENAME=d:dataarchdat3.ndf,SIZE=10MB,MAXSIZE=15,FILEGROWTH=5),LOG ON/*-日志文件日志文件1的具体描述的具体描述-*/(NAME=Archlog1,FILENAME=d:dataarchlog1.ldf,SIZE=5MB,MAXSIZE=10,FILEGROWTH=5),/*-日志文件日志文件2的具体描述的具体描述-*/(NAME=Archlog2,FILENAME=d:dataarchlog2.ldf,SIZE=5MB,MAXSIZE=10,FILEGROWTH=5)GO 演示创建数据库演示创建数据库2：多个数据文件和多个日志文件：多个数据文件和多个日志文件 88删除数据库删除数据库DROP DATABASE database_name,.。nUSE masterGO DROP DATABASE pubs99使用使用SQL语句创建表语句创建表l建表的步骤建表的步骤 确定表中有哪些列确定表中有哪些列确定每列的数据类型确定每列的数据类型给表添加各种约束给表添加各种约束创建各表之间的关系创建各表之间的关系整型整型:int,smallint小数小数:decimal,numeric字符字符:固定长度固定长度:char(6)可变长度可变长度:varchar(10)Unicode字符字符:nvarchar(10)日期日期:datetime货币货币:money布尔布尔:Bit 取值为取值为0或或11010创建表创建表 CREATE TABLE 表名表名(字段字段1 数据类型数据类型 列的特征，列的特征，字段字段2 数据类型数据类型 列的特征，列的特征，。)语法语法列的特征包括该列是否为空（列的特征包括该列是否为空（NULLNULL）、是否是标识列（自动编号）、）、是否是标识列（自动编号）、是否有默认值、是否为主键等。是否有默认值、是否为主键等。1111创建表示例一创建表示例一CREATE TABLE DEPT(deptno INT PRIMARY KEY,-主键（唯一，非空）主键（唯一，非空）dname CHAR(15)NOT NULL,-部门名称，非空部门名称，非空(必填必填)loc VARCHAR(30)-部门位置，允许为空，即可选输入部门位置，允许为空，即可选输入)演示创建部门表演示创建部门表dept1212创建表示例二创建表示例二CREATE TABLE EMP(empno INT PRIMARY KEY,-雇员编号，主键雇员编号，主键 ename VARCHAR(10)NOT NULL,-雇员姓名雇员姓名 job VARCHAR(9),-工种工种 sal DECIMAL(7,2)NOT NULL,-工资工资deptno INT NOT NULL,-雇员所在部门号，外键雇员所在部门号，外键CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)REFERENCES dept(deptno)演示创建雇员表演示创建雇员表emp1313创建表示例三创建表示例三CREATE TABLE stuInfo(stuNo INT IDENTITY(1,1)-学号，自动编号（标识列）学号，自动编号（标识列）stuName VARCHAR(20)NOT NULL,-姓名姓名stuAge INT NOT NULL,-年龄年龄stuAddress VARCHAR(30)-地址地址)演示创建学生信息表演示创建学生信息表stuInfo1414创建表示例四创建表示例四 使用使用SELECT INTO 生成表结构和数据的拷贝生成表结构和数据的拷贝:SELECT *INTO newdept FROM dept 1515删除表删除表USE empDBGO/检查数据库中是否存在该表检查数据库中是否存在该表,如果存在则删除如果存在则删除IF EXISTS(SELECT *FROM sysobjects WHERE name=dept)DROP TABLE dept语法语法:DROP TABLE 表名表名系统表系统表:存放表存放表的清单的清单演示删除表演示删除表dept1616创建和删除约束创建和删除约束l回顾约束类型回顾约束类型:主键约束（主键约束（primary key constraint）：要求主键列数据唯一，并）：要求主键列数据唯一，并且不允许为空。且不允许为空。唯一约束（唯一约束（unique constraint)：要求该列唯一，允许为空，但：要求该列唯一，允许为空，但只能出现一个空值。只能出现一个空值。检查约束（检查约束（check constraint）：某列取值的限制、格式的限制）：某列取值的限制、格式的限制等。如有关性别的检查。等。如有关性别的检查。默认约束（默认约束（default constraint）：某列输入数据时的默认值，强）：某列输入数据时的默认值，强制了域完整性。制了域完整性。外键约束（外键约束（foreign key constraint）：用于在两表间建立关系，）：用于在两表间建立关系，需要指定引用主表的哪列。需要指定引用主表的哪列。1717给表添加约束给表添加约束添加约束语法添加约束语法:约束的命名规则推荐采用：约束类型约束的命名规则推荐采用：约束类型_约束字约束字例如例如:给给dept表的表的deptno字段添加主键约束，推荐起名为：字段添加主键约束，推荐起名为：PK_deptno给给Emp表的表的ename字段添加唯一约束，可起名为：字段添加唯一约束，可起名为：UQ_enameALTER TABLE 表名表名 ADD CONSTRAINT 约束名约束名 约束类型具体的约束说明约束类型具体的约束说明1818添加约束示例添加约束示例USE empDB GO-添加主键约束添加主键约束(deptno作为主键作为主键),约束名为约束名为pk_deptnoALTER TABLE dept ADD CONSTRAINT pk_deptno PRIMARY KEY(deptno)添加唯一约束添加唯一约束,希望员工名字唯一希望员工名字唯一ALTER TABLE emp ADD CONSTRAINT UQ_ename UNIQUE(ename)添加检查约束添加检查约束,要求雇员工资少于要求雇员工资少于3000ALTER TABLE empADD CONSTRAINT ck_sal CHECK(sal=3000)添加默认值约束添加默认值约束,工资的默认值是工资的默认值是ALTER TABLE EMPADD CONSTRAITNT df_sal DEFAULT 800 FOR sal添加外键约束添加外键约束(主表主表dept和从表和从表emp建立关系，关联字段为建立关系，关联字段为deptno)ALTER TABLE empADD CONSTRAINT fk_deptno FOREIGN KEY(deptno)REFERENCES dept(deptno)1919添加约束示例添加约束示例演示：查看约束结果演示：查看约束结果2020删除约束删除约束删除约束语法删除约束语法:ALTER TABLE 表名表名 DROP CONSTRAINT 约束名约束名例如删除表例如删除表emp中的工资默认值约束中的工资默认值约束ALTER TABLE EMP DROP CONSTRAINT df_sal 2121回顾回顾SQL Server的安全模型的安全模型增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐号登录帐号SQLserver三层安全管理数据表数据表数据表数据表1 1 1 1数据表数据表数据表数据表2 2DB1DB1数据表数据表数据表数据表1 1 1 1数据表数据表数据表数据表2 2DB2DB2数据表数据表数据表数据表1 1 1 1数据表数据表数据表数据表2 2DB3DB32222登录方式回顾登录方式回顾l登录验证有两种方式：登录验证有两种方式：SQL身份验证：适合于非身份验证：适合于非windows平台的用户或平台的用户或Internet用户，用户，需要提供帐户和密码需要提供帐户和密码Windows身份验证：适合于身份验证：适合于windows平台用户，不需要提供密码，平台用户，不需要提供密码，和和windows集成验证集成验证l登录帐户相应有两种登录帐户相应有两种:：SQL 帐户帐户和和Windows帐户帐户 2323创建登录账户创建登录账户l使用使用SQL语句创建两种模式下的登录帐户语句创建两种模式下的登录帐户添加添加WINDOWS登录帐户登录帐户添加添加SQL登录帐户登录帐户 EXEC sp_grantlogin windows域名域名域帐户域帐户 EXEC EXEC sp_addlogin 帐户名帐户名，密码密码 2424创建登录账户创建登录账户l添加添加Windows登录帐户登录帐户/windows用户为用户为S01,SDXY表示域表示域 如是本机，如是本机，SDXY为计算机名为计算机名 EXEC sp_grantlogin SDXYS01l添加添加SQL登录帐户登录帐户/登录名为登录名为zhangsan,密码为密码为1234 EXEC sp_addlogin zhangsan,1234 GO 演示：创建登录帐户演示：创建登录帐户2525创建数据库用户创建数据库用户l创建了登录帐户，只能登录到创建了登录帐户，只能登录到SQL Server系统，但还不系统，但还不能访问某个数据库。如果希望访问某个数据库，必须要成能访问某个数据库。如果希望访问某个数据库，必须要成为该数据库的一个用户。为该数据库的一个用户。l创建数据库用户需要调用系统存储过程创建数据库用户需要调用系统存储过程sp_grantdbaccess l在在empDB中添加两个用户中添加两个用户 EXEC sp_grantdbaccess 登录帐户登录帐户，数据库用户数据库用户 USE empDB-S01DBUser为数据库用户名为数据库用户名EXEC sp_grantdbaccess SDXYS01，S01DBUserEXEC sp_grantdbaccess zhangsan，zhangsanDBUser 演示：创建数据库用户演示：创建数据库用户2626给数据库用户授权给数据库用户授权l当成为数据库用户后当成为数据库用户后,还不能访问表还不能访问表,需要需要DBA授予普通用授予普通用户访问表的权限户访问表的权限:insert,update,delete,selectl语法语法:GRANT 权限权限 ON 表名表名 TO 数据库用户数据库用户 USE empDBGO-为为zhangsanDBUser 分配对表分配对表emp的的select，insert，update权限权限GRANT select，insert，update ON emp TO zhangsanDBUser-为用户为用户s01DBUser 分配建表的权限分配建表的权限GRANT create table TO S01DBUser 2727总结总结 2-1l数据库的物理实现一般包括：数据库的物理实现一般包括：创建数据库创建数据库创建表创建表添加各种约束添加各种约束创建数据库的登录帐户并授权创建数据库的登录帐户并授权l创建数据库或表时一般需要预先检测是否存在该对象创建数据库或表时一般需要预先检测是否存在该对象数据库从数据库从master系统数据库的系统数据库的sysdatabases表中查询表中查询 表从该数据库的系统表表从该数据库的系统表sysobjects表中查询表中查询 2828总结总结 2-2l访问访问SQL Server某个数据库中的某个表，需要三层验证：某个数据库中的某个表，需要三层验证：是否是是否是SQL Server的登录帐户的登录帐户是否是该数据库的用户是否是该数据库的用户是否有足够的权限访问该表是否有足够的权限访问该表