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

第二章,数据库的实现,2,目标,掌握建库的SQL语句 掌握建表的SQL语句,3,回顾数据库的基础知识,数据库文件的组成： 主数据文件：*.mdf 次要数据文件：*.ndf 日志文件：*.ldf 数据库的其他属性： 文件存放位置，分配的初始空间，属于哪个文件组 文件增长：可以按百分比或实际大小指定增长速度 文件容量设置：可以指定文件增长的最大值或不受限,4,创建数据库,CREATE DATABASE 数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n ),T-SQL创建数据库的语法：,主文件组，可选参数，默认,5,创建数据库示例 1,CREATE 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：一个数据文件和一个日志文件,6,创建数据库示例 2,CREATE 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：多个数据文件和多个日志文件,7,删除数据库,如果SQL Server中已存在数据库stuDB，运行下列语句，会出现什么问题？,CREATE DATABASE stuDB ON ( . ) LOG ON ( . ) GO,演示：再次运行示例1,如何知道服务器上已有的数据库,8,USE master -设置当前数据库为master，以便访问sysdatabases表 GO IF EXISTS（SELECT * FROM sysdatabases WHERE name =stuDB） DROP DATABASE stuDB CREATE DATABASE stuDB ON ( . ) LOG ON ( ) GO,删除数据库,删除数据库的语法：,DROP DATABASE 数据库名,EXISTS（）语句： 检测是否存在stuDB数据库 如果存在stuDB数据库，则删除,9,修改数据库,修改数据库的语法,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 ,10,修改数据库示例,【例】扩大主要数据文件的大小到12MB，扩大事务日志文件的大小到4MB 。 ALTER DATABASE stuDB MODIFY FILE (NAME= stuDB_data, SIZE=12MB),11,回顾表的基础知识,建表的基本步骤： 确定表中有哪些列 确定每列的数据类型 给表添加各种约束 创建各表之间的关系 SQL Server中的数据类型,整型数据：int smallint 浮点数据：float numeric ,字符数据 固定长度：char(6) 可变长度: varchar(10) unicode编码：nchar(10) ,布尔数据（真/假） bit: 1/ 0,货币数据 money,日期数据 datetime,12,SQL Server的数据类型,13,创建表,建表的语法,CREATE TABLE 表名 ( 字段1 数据类型 列的特征， 字段2 数据类型 列的特征， . ),列的特征： 包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。,14,建表示例 1,USE stuDB -将当前数据库设置为stuDB GO CREATE 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 -住址，允许为空，即可选输入 ) GO,NUMERIC (18,0) 代表18位数字，小数位数为0,演示：创建学员信息表stuInfo,IDENTITY（起始值，递增量）,15,建表示例 2,CREATE TABLE stuMarks ( ExamNo CHAR(7) NOT NULL, -考号 stuNo CHAR(6) NOT NULL, -学号 writtenExam INT NOT NULL, -笔试成绩 LabExam INT NOT NULL -机试成绩 ) GO,演示：创建学员成绩表 stuMarks,16,删除表,如果当前数据库中已存在stuInfo表，再次创建时系统将提示出错 。如何解决呢？,17,删除表,删除表的语法：,DROP TABLE 表名,USE stuDB -将当前数据库设置为stuDB ，以便在stuDB数据库中建表 GO IF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfo CREATE TABLE stuInfo /*-创建学员信息表-*/ ( . ) GO,18,SQL Server的约束,约束的目的：确保表中数据的完整型 常用的约束类型： 主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空 唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。 检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束 默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男” 外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列,19,添加约束,添加约束的语法：,ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明,约束名的取名规则推荐采用：约束类型_约束字段 主键（Primary Key）约束：如 PK_stuNo 唯一（Unique Key）约束：如 UQ_stuID 默认（Default Key）约束：如 DF_stuAddress 检查（Check Key）约束：如 CK_stuAge 外键(Foreign Key)约束：如 FK_stuNo,20,添加约束示例,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 stuAddress ALTER 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）,21,添加约束示例,演示：查看约束结果,22,删除约束,如果错误地添加了约束，我们还可以删除约束 删除约束的语法,ALTER TABLE 表名 DROP CONSTRAINT 约束名,例如：删除stuInfo表中地址默认约束 ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress,23,表的数据操作,向表中插入数据语法,INSERT INTO table_name column_list VALUES(column_list的值),例： 向表 stuInfo 增加一条记录 Insert into stuInfo(stuName, stuNo) values(张大力，20100101) Insert stuInfo values(张大力,2010,20,12312,河北省),24,用UPDATE语句修改表数据,UPDATE table_name SET column_name = 要修改的值 WHERE 条件,例： 修改表stuInfo 中的张大力的年龄为22 Update stuInfo set stuAge=22 where stuName=张大力,25,用DELETE语句删除表中数据,DELETE FROM tablename WHERE 条件,例： 删除表stuInfo中的所有数据 Delete from stuInfo 删除表stuInfo中张大力的记录 delete from stuInfo where stuName=张大力,26,思考,标识列允许为字符数据类型吗？ 如果标识列A的初始值为1，增长量为2，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？,27,关系图,演示关系图,28,SQL Server的安全模型,29,登录方式回顾,登录验证有两种方式： SQL身份验证：适合于非windows平台的用户或Internet用户， 需要提供帐户和密码 Windows身份验证：适合于windows平台用户，不需要提供密码，和windows集成验证 登录帐户相应有两种：SQL 帐户和Windows帐户,30,创建登录,添加 Windows登录帐户 EXEC sp_grantlogin jbtrainingS26301 添加 SQL登录帐户 EXEC sp_addlogin zhangsan, 1234,演示创建登录,EXEC表示调用存储过程， 存储过程类似C语言的函数,内置的系统管理员 帐户sa 密码默认为空，建议修改密码,域名用户名,用户名,密码,31,创建登录,32,创建数据库用户,创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：,EXEC sp_grantdbaccess 登录帐户名，数据库用户名 其中，“数据库用户“为可选参数，默认为登录帐户， 即数据库用户默认和登录帐户同名。,USE stuDB GO EXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser EXEC sp_grantdbaccess zhangsan, zhangsanDBUser,在stuDB数据库中添加两个用户,33,创建数据库用户,34,dbo 用户 表示数据库的所有者（DB Owner） 无法删除 dbo 用户，此用户始终出现在每个数据库中 guest 用户 适用于没有数据库用户的登录帐号访问 每个数据库可有也可删除,系统内置的数据库用户,35,向数据库用户授权,delete FROM Employees,SELECT * FROM Employees,36,权限的类型 2-1,授权的语法为： GRANT 权限 ON 表名 TO 数据库用户,USE stuDB GO /*-为zhangsanDBUser分配对表stuInfo的select, insert, update权限-*/ GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-为S26301DBUser分配建表的权限-*/ GRANT create table TO S26301DBUser,37,权限的类型 2-2,没有授予delete权限,38,总结 2-1,数据库的物理实现一般包括： 创建数据库 创建表 添加各种约束 创建数据库的登录帐户并授权 创建数据库或表时一般需要预先检测是否存在该对象 数据库从master系统数据库的sysdatabases表中查询 表从该数据库的系统表sysobjects表中查询,39,总结 2-2,访问SQL Server某个数据库中的某个表，需要三层验证： 是否是SQL Server的登录帐户 是否是该数据库的用户 是否有足够的权限访问该表,