数据库基础创建数据库.ppt

第二章数据库的实现数据库的实现目标目标q 掌握建库的掌握建库的SQL语句语句q 掌握建表的掌握建表的SQL语句语句2回顾数据库的基础知识回顾数据库的基础知识q数据库文件的组成：数据库文件的组成：q主数据文件：主数据文件：*.mdfq次要数据文件：次要数据文件：*.ndfq日志文件：日志文件：*.ldfq数据库的其他属性：数据库的其他属性：q文件存放位置，分配的初始空间，属于哪个文件组文件存放位置，分配的初始空间，属于哪个文件组q文件增长：可以按百分比或实际大小指定增长速度文件增长：可以按百分比或实际大小指定增长速度q文件容量设置：可以指定文件增长的最大值或不受限文件容量设置：可以指定文件增长的最大值或不受限3创建数据库创建数据库 CREATE DATABASE 数据库名 ON PRIMARY(，n )LOG ON (，n)T-SQL创建数据库的语法：创建数据库的语法：主文件组，可选参数，默认4创建数据库示例创建数据库示例 1CREATE DATABASE stuDB ON PRIMARY -默认就属于PRIMARY主文件组，可省略(NAME=stuDB_data,-主数据文件的逻辑名 FILENAME=D:projectstuDB_data.mdf,-主数据文件的物理名 SIZE=5mb,-主数据文件初始大小 MAXSIZE=100mb,-主数据文件增长的最大值 FILEGROWTH=15%-主数据文件的增长率)LOG ON(NAME=stuDB_log,FILENAME=D:projectstuDB_log.ldf,SIZE=2mb,FILEGROWTH=1MB)GO 数据文件的具体描述日志文件的具体描述演示创建数据库1：一个数据文件和一个日志文件 5创建数据库示例创建数据库示例 2CREATE DATABASE employees ON (/*-主数据文件的具体描述-*/NAME=employee1,FILENAME=D:projectemployee1_Data.mdf,SIZE=10,FILEGROWTH=10%),(/*-次要数据文件的具体描述-*/NAME=employee2,FILENAME=D:projectemployee2_Data.ndf,SIZE=20,MAXSIZE=100,FILEGROWTH=1 )LOG ON (/*-日志文件1的具体描述-*/NAME=employeelog1,FILENAME=D:projectemployeelog1_Log.ldf,SIZE=10,FILEGROWTH=1 ),(/*-日志文件2的具体描述-*/NAME=employeelog2,FILENAME=D:projectemployeelog2_Log.ldf,SIZE=10,MAXSIZE=50,FILEGROWTH=1 )GO 演示创建数据库2：多个数据文件和多个日志文件 6删除数据库删除数据库q如果如果SQL Server中已存在数据库中已存在数据库stuDB，运行下列语句，运行下列语句，会出现什么问题？会出现什么问题？CREATE DATABASE stuDBON(.)LOG ON(.)GO演示：再次运行示例1如何知道服如何知道服务器上已有务器上已有的数据库的数据库7USE master -设置当前数据库为master，以便访问sysdatabases表GOIF EXISTS（SELECT*FROM sysdatabases WHERE name=stuDB）DROP DATABASE stuDBCREATE DATABASE stuDBON(.)LOG ON()GO删除数据库删除数据库q删除数据库的语法：删除数据库的语法：DROP DATABASE 数据库名 EXISTS（）语句：检测是否存在stuDB数据库如果存在stuDB数据库，则删除 8修改数据库修改数据库q修改数据库的语法修改数据库的语法ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name|ADD LOG FILE ,.n|REMOVE FILE logical_file_name|ADD FILEGROUP filegroup_name|REMOVE FILEGROUP filegroup_name|MODIFY FILE|MODIFY NAME=new_dbname|MODIFY FILEGROUP filegroup_name filegroup_property|NAME=new_filegroup_name|SET ,.n WITH|COLLATE 9修改数据库示例修改数据库示例【例】扩大主要数据文件的大小到12MB，扩大事务日志文件的大小到4MB。ALTER DATABASE stuDBMODIFY FILE(NAME=stuDB_data,SIZE=12MB)10回顾表的基础知识回顾表的基础知识 q建表的基本步骤：建表的基本步骤：q确定表中有哪些列确定表中有哪些列q确定每列的数据类型确定每列的数据类型q给表添加各种约束给表添加各种约束q创建各表之间的关系创建各表之间的关系qSQL Server中的数据类型中的数据类型 整型数据：int smallint 浮点数据：float numeric字符数据固定长度：char(6)可变长度:varchar(10)unicode编码：nchar(10)布尔数据（真/假）bit:1/0 货币数据 money日期数据 datetime11SQL Server的数据类型的数据类型分分类类备备注和注和说说明明数据数据类类型型说说明明二二进进制数据制数据类类型型存存储储非子符和文本的数据非子符和文本的数据Image可用来存可用来存储图储图像像文本数据文本数据类类型型字符数据包括任意字母、符字符数据包括任意字母、符号或数字字符的号或数字字符的组组合合Char固定固定长长度的非度的非 Unicode 字字符数据符数据Varchar可可变长变长度非度非 Unicode 数据数据Nchar固定固定长长度的度的 Unicode 数据数据Nvarchar可可变长变长度度 Unicode 数据数据Text存存储长储长文本信息文本信息Ntext存存储储可可变长变长度的度的长长文本文本日期和日期和时间时间日期和日期和时间时间在在单单引号内引号内输输入入Datetime 日期和日期和时间时间数字数据数字数据该该数据数据仅仅包含数字，包括正包含数字，包括正数、数、负负数以及分数数以及分数intsmallint整数整数floatreal数字数字货币货币数据数据类类型型用于十用于十进进制制货币值货币值MoneyBit数据数据类类型型表示是表示是/否的数据否的数据Bit存存储储布布尔尔数据数据类类型型12创建表创建表q建表的语法建表的语法CREATE TABLE 表名(字段1 数据类型 列的特征，字段2 数据类型 列的特征，.)列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。13建表示例建表示例 1USE stuDB -将当前数据库设置为stuDB GOCREATE TABLE stuInfo /*-创建学员信息表-*/(stuName VARCHAR(20)NOT NULL,-姓名，非空（必填）stuNo CHAR(6)NOT NULL,-学号，非空（必填）stuAge INT NOT NULL,-年龄，INT类型默认为4个字节 stuID NUMERIC(18,0),-身份证号 stuSeat SMALLINT IDENTITY(1,1),-座位号，自动编号 stuAddress TEXT -住址，允许为空，即可选输入)GONUMERIC(18,0)代表18位数字，小数位数为0演示：创建学员信息表stuInfoIDENTITY（起始值，递增量）14建表示例建表示例 2CREATE TABLE stuMarks(ExamNo CHAR(7)NOT NULL,-考号 stuNo CHAR(6)NOT NULL,-学号 writtenExam INT NOT NULL,-笔试成绩 LabExam INT NOT NULL -机试成绩)GO演示：创建学员成绩表 stuMarks15删除表删除表q如果当前数据库中已存在如果当前数据库中已存在stuInfo表，再次创建时系统将提表，再次创建时系统将提示出错示出错。如何解决呢？。如何解决呢？16删除表删除表q删除表的语法：删除表的语法：DROP TABLE 表名USE stuDB -将当前数据库设置为stuDB，以便在stuDB数据库中建表GOIF EXISTS（SELECT*FROM sysobjects WHERE name=stuInfo）DROP TABLE stuInfoCREATE TABLE stuInfo /*-创建学员信息表-*/(.)GO17SQL Server的约束的约束q约束的目的：确保表中数据的完整型约束的目的：确保表中数据的完整型q常用的约束类型：常用的约束类型：q主键约束（主键约束（Primary Key Constraint）：要求主键列数据唯一，）：要求主键列数据唯一，并且不允许为空并且不允许为空q唯一约束（唯一约束（Unique Constraint）：要求该列唯一，允许为空，）：要求该列唯一，允许为空，但只能出现一个空值。但只能出现一个空值。q检查约束（检查约束（Check Constraint）：某列取值范围限制、格式限）：某列取值范围限制、格式限制等，如有关年龄的约束制等，如有关年龄的约束q默认约束（默认约束（Default Constraint）：某列的默认值，如我们的男）：某列的默认值，如我们的男性学员较多，性别默认为性学员较多，性别默认为“男男”q外键约束（外键约束（Foreign Key Constraint）：用于两表间建立关系，）：用于两表间建立关系，需要指定引用主表的那列需要指定引用主表的那列18添加约束添加约束 q添加约束的语法：添加约束的语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明q约束名的取名规则推荐采用：约束类型_约束字段q主键（Primary Key）约束：如 PK_stuNoq唯一（Unique Key）约束：如 UQ_stuIDq默认（Default Key）约束：如 DF_stuAddressq检查（Check Key）约束：如 CK_stuAgeq外键(Foreign Key)约束：如 FK_stuNo 19添加约束示例添加约束示例ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo)ALTER TABLE stuInfo ADD CONSTRAINT UQ_stuID UNIQUE(stuID)ALTER TABLE stuInfo ADD CONSTRAINT DF_stuAddress DEFAULT(地址不详)FOR stuAddressALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo)REFERENCES stuInfo(stuNo)GO演示：给学员信息表stuInfo添加约束添加主键约束（stuNo作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求年龄只能在1540岁之间添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）20添加约束示例添加约束示例演示：查看约束结果21删除约束删除约束q如果错误地添加了约束，我们还可以删除约束如果错误地添加了约束，我们还可以删除约束 q删除约束的语法删除约束的语法ALTER TABLE 表名 DROP CONSTRAINT 约束名 例如：删除stuInfo表中地址默认约束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress22表的数据操作表的数据操作q向表中插入数据语法INSERT INTO table_name column_list VALUES(column_list的值)例：向表 stuInfo 增加一条记录Insert into stuInfo(stuName,stuNo)values(张大力，20100101)Insert stuInfo values(张大力,2010,20,12312,河北省)23q用UPDATE语句修改表数据UPDATE table_name SET column_name=要修改的值 WHERE 条件例：修改表stuInfo 中的张大力的年龄为22Update stuInfo set stuAge=22 where stuName=张大力24q用DELETE语句删除表中数据 DELETE FROM tablename WHERE 条件例：删除表stuInfo中的所有数据Delete from stuInfo删除表stuInfo中张大力的记录delete from stuInfo where stuName=张大力25思考思考q标识列允许为字符数据类型吗？标识列允许为字符数据类型吗？q如果标识列如果标识列A的初始值为的初始值为1，增长量为，增长量为2，则输入三行数据，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？多少开始？26关系图关系图演示关系图27SQL Server的安全模型的安全模型数据库 1数据库 2数据库 3表 1表 2表 1表 2表 1表 2增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐号登录帐号SQL Server三层安全管理28登录方式回顾登录方式回顾q登录验证有两种方式：登录验证有两种方式：qSQL身份验证：适合于非身份验证：适合于非windows平台的用户或平台的用户或Internet用户，用户，需需要提供帐户和密码要提供帐户和密码qWindows身份验证：适合于身份验证：适合于windows平台用户，不需要提供密码，平台用户，不需要提供密码，和和windows集成验证集成验证q登录帐户相应有两种：登录帐户相应有两种：SQL 帐户帐户和和Windows帐户帐户 29创建登录创建登录q添加添加 Windows登录帐户登录帐户 EXEC sp_grantlogin jbtrainingS26301 q添加添加 SQL登录帐户登录帐户 EXEC sp_addlogin zhangsan,1234演示创建登录EXEC表示调用存储过程，存储过程类似C语言的函数内置的系统管理员 帐户sa密码默认为空，建议修改密码 域名用户名用户名,密码30创建登录创建登录31创建数据库用户创建数据库用户q创建数据库用户需要调用系统存储过程创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：，其用法为：EXEC sp_grantdbaccess 登录帐户名，数据库用户名 其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301,S26301DBUser EXEC sp_grantdbaccess zhangsan,zhangsanDBUser在stuDB数据库中添加两个用户32创建数据库用户创建数据库用户33qdbo 用户q表示数据库的所有者（DB Owner）q无法删除 dbo 用户，此用户始终出现在每个数据库中 qguest 用户q适用于没有数据库用户的登录帐号访问q每个数据库可有也可删除系统内置的数据库用户34向数据库用户授权向数据库用户授权 EmployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22.delete FROM EmployeesSELECT*FROM Employees35权限的类型权限的类型 2-1q授权的语法为：授权的语法为：qGRANT 权限权限 ON 表名表名 TO 数据库用户数据库用户USE stuDBGO/*-为zhangsanDBUser分配对表stuInfo的select,insert,update权限-*/GRANT select,insert,update ON stuInfo TO zhangsanDBUser /*-为S26301DBUser分配建表的权限-*/GRANT create table TO S26301DBUser36权限的类型权限的类型 2-2没有授予delete权限37总结总结 2-1q数据库的物理实现一般包括：数据库的物理实现一般包括：q创建数据库创建数据库q创建表创建表q添加各种约束添加各种约束q创建数据库的登录帐户并授权创建数据库的登录帐户并授权q创建数据库或表时一般需要预先检测是否存在该创建数据库或表时一般需要预先检测是否存在该对象对象q数据库从数据库从master系统数据库的系统数据库的sysdatabases表中查表中查询询 q表从该数据库的系统表表从该数据库的系统表sysobjects表中查询表中查询 38总结总结 2-2q访问访问SQL Server某个数据库中的某个表，需要三某个数据库中的某个表，需要三层验证：层验证：q是否是是否是SQL Server的登录帐户的登录帐户q是否是该数据库的用户是否是该数据库的用户q是否有足够的权限访问该表是否有足够的权限访问该表39