SQL Server数据库设计和高级查询SQL AdvanceV教案.pptx

《SQL Server数据库设计和高级查询SQL AdvanceV教案.pptx》由会员分享，可在线阅读，更多相关《SQL Server数据库设计和高级查询SQL AdvanceV教案.pptx（33页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、会计学1SQL Server数据库设计和高级查询数据库设计和高级查询SQL AdvanceV回顾回顾n n在需求分析阶段，设计数据库的一般步骤为？n n实体的映射关系有哪些？n n请简要说明三大范式的含义？第1页/共33页目标目标n n 掌握建库的SQL语句n n 掌握建表的SQL语句n n 掌握加约束的SQL语句n n 掌握创建安全帐户的SQL语句第2页/共33页回顾数据库的回顾数据库的基础知识基础知识数据库文件的组成：主数据文件：*.mdf次要数据文件：*.ndf日志文件：*.ldf数据库的其他属性：文件存放位置，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文

2、件容量设置：可以指定文件增长的最大值或不受限第3页/共33页创建数创建数据库据库 CREATE DATABASE 数据库名 ON PRIMARY(，n )LOG ON (，n)T-SQL创建数据库的语法：主文件组，可选参数，默认第4页/共33页创建数据创建数据库示例库示例 1CREATE DATABASE stuDB ON PRIMARY -默认就属于PRIMARY主文件组，可省略(NAME=stuDB_data,-主数据文件的逻辑名 FILENAME=D:projectstuDB_data.mdf,-主数据文件的物理名 SIZE=5mb,-主数据文件初始大小 MAXSIZE=100mb,-主

3、数据文件增长的最大值 FILEGROWTH=15%-主数据文件的增长率)LOG ON(NAME=stuDB_log,FILENAME=D:projectstuDB_log.ldf,SIZE=2mb,FILEGROWTH=1MB)GO 数据文件的具体描述日志文件的具体描述演示创建数据库1：一个数据文件和一个日志文件 第5页/共33页创建数据创建数据库示例库示例 2CREATE DATABASE employees ON (/*-主数据文件的具体描述-*/NAME=employee1,FILENAME=D:projectemployee1_Data.mdf,SIZE=10,FILEGROWTH=1

4、0%),(/*-次要数据文件的具体描述-*/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,MAXSIZ

5、E=50,FILEGROWTH=1 )GO 演示创建数据库2：多个数据文件和多个日志文件 第6页/共33页删除删除数据数据库库n n如果如果SQL ServerSQL Server中已存在数据库中已存在数据库stuDBstuDB，运行下列语句，会出现什么，运行下列语句，会出现什么问题？问题？CREATE DATABASE stuDBON(.)LOG ON(.)GO演示：再次运行示例1第7页/共33页USE master -设置当前数据库为master，以便访问sysdatabases 表GOIF EXISTS（SELECT*FROM sysdatabases WHERE name=stuDB）

6、DROP DATABASE stuDBCREATE DATABASE stuDBON(.)LOG ON()GO删除数删除数据库据库n n删除数据库的语法：DROP DATABASE 数据库名 EXISTS（）语句：检测是否存在stuDB数据库如果存在stuDB数据库，则删除 第8页/共33页回顾表的回顾表的基础知识基础知识 建表的基本步骤：确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系SQL Server中的数据类型 整型数据：int smallint 浮点数据：float numeric字符数据固定长度：char(6)可变长度:varchar(10)unicode编码：

7、nchar(10)布尔数据（真/假）bit:1/0 货币数据 money日期数据 datetime第9页/共33页创建创建表表n n建表的语法CREATE TABLE 表名(字段1 数据类型 列的特征，字段2 数据类型 列的特征，.)列的特征：包括该列是是否为空（NULL）、是否是标识列（自动编号）、是否有默认值、是否为主键等。第10页/共33页建表建表示例示例 1USE stuDB -将当前数据库设置为stuDB GOCREATE TABLE stuInfo /*-创建学员信息表-*/(stuName VARCHAR(20)NOT NULL,-姓名，非空（必填）stuNo CHAR(6)NO

8、T NULL,-学号，非空（必填）stuAge INT NOT NULL,-年龄，INT类型默认为4个字节 stuID NUMERIC(18,0),-身份证号 stuSeat SMALLINT IDENTITY(1,1),-座位号，自动编号 stuAddress TEXT -住址，允许为空，即可选输入)GONUMERIC(18,0)代表18位数字，小数位数为0演示：创建学员信息表stuInfoIDENTITY（起始值，递增量）第11页/共33页建表示建表示例例 2CREATE TABLE stuMarks(ExamNo CHAR(7)NOT NULL,-考号 stuNo CHAR(6)NOT

9、NULL,-学号 writtenExam INT NOT NULL,-笔试成绩 LabExam INT NOT NULL -机试成绩)GO演示：创建学员成绩表 stuMarks第12页/共33页删除表删除表n n如果当前数据库中已存在stuInfo表，再次创建时系统将提示出错。如何解决呢？第13页/共33页删除删除表表n n删除表的语法：DROP TABLE 表名USE stuDB -将当前数据库设置为stuDB，以便在stuDB数据库中建表GOIF EXISTS（SELECT*FROM sysobjects WHERE name=stuInfo）DROP TABLE stuInfoCREAT

10、E TABLE stuInfo /*-创建学员信息表-*/(.)GO第14页/共33页回顾回顾SQL Server的约束的约束n n约束的目的：确保表中数据的完整型n n常用的约束类型：n n主键约束（主键约束（Primary Key Primary Key ConstraintConstraint）：要求主键列数据唯）：要求主键列数据唯一，并且不允许为空一，并且不允许为空n n唯一约束（唯一约束（Unique ConstraintUnique Constraint）：）：要求该列唯一，允许为空，但只要求该列唯一，允许为空，但只能出现一个空值。能出现一个空值。n n检查约束（检查约束（Chec

11、k ConstraintCheck Constraint）：）：某列取值范围限制、格式限制等，某列取值范围限制、格式限制等，如有关年龄的约束如有关年龄的约束n n默认约束（默认约束（Default ConstraintDefault Constraint）：）：某列的默认值，如我们的男性学某列的默认值，如我们的男性学员较多，性别默认为员较多，性别默认为“男男”n n外键约束（外键约束（Foreign Key Foreign Key ConstraintConstraint）：用于两表间建立关）：用于两表间建立关系，需要指定引用主表的那列系，需要指定引用主表的那列第15页/共33页添加约添加约束

12、束 n n添加约束的语法：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明约束名的取名规则推荐采用：约束类型_约束字段主键（Primary Key）约束：如 PK_stuNo唯一（Unique Key）约束：如 UQ_stuID默认（Default Key）约束：如 DF_stuAddress检查（Check Key）约束：如 CK_stuAge外键(Foreign Key)约束：如 FK_stuNo 第16页/共33页添加约束添加约束示例示例ALTER TABLE stuInfo ADD CONSTRAINT PK_stuNo PRIMARY KE

13、Y(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(s

14、tuNo)GO演示：给学员信息表stuInfo 添加约束添加主键约束（stuNo作为主键）添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为“地址不详”）添加检查check约束，要求年龄只能在1540岁之间添加外键约束（主表stuInfo 和从表stuMarks建立关系，关联字段为stuNo）第17页/共33页添加约添加约束示例束示例演示：查看约束结果第18页/共33页删除约删除约束束n n如果错误地添加了约束，我们还可以删除约束 n n删除约束的语法ALTER TABLE 表名 DROP CONSTRAINT 约束名 例如：删除stuInfo 表中地址默认约束ALT

15、ER TABLE stuInfo DROP CONSTRAINT DF_stuAddress第19页/共33页回顾回顾SQL Server的安全的安全模型模型数据库 1数据库 2数据库 3表 1表 2表 1表 2表 1表 2增删改查增删改查增删改查增删改查增删改查增删改查数据库用户数据库用户数据库用户登录帐号SQLserver三层安全管理第20页/共33页登录方登录方式回顾式回顾n n登录验证有两种方式：n nSQLSQL身份验证：适合于非身份验证：适合于非windowswindows平台的用户或平台的用户或InternetInternet用户，用户，需要需要提供帐户和密码提供帐户和密码n n

16、WindowsWindows身份验证：适合于身份验证：适合于windowswindows平台用户，不需要提供密平台用户，不需要提供密码，和码，和windowswindows集成验证集成验证n n登录帐户相应有两种：SQL 帐户和Windows帐户 第21页/共33页创建登创建登录录n n添加 Windows登录帐户 EXEC sp_grantlogin jbtrainingS26301EXEC sp_grantlogin jbtrainingS26301 n n添加 SQL登录帐户 EXEC sp_addlogin zhangsan,1234EXEC sp_addlogin zhangsan,

17、1234演示创建登录EXEC表示调用存储过程，存储过程类似C语言的函数内置的系统管理员 帐户sa密码默认为空，建议修改密码 域名用户名用户名,密码第22页/共33页创建创建登录登录第23页/共33页创建数创建数据库用据库用户户n n创建数据库用户需要调用系统存储过程sp_grantdbaccess，其用法为：EXEC sp_grantdbaccess 登录帐户名，数据库用户名 其中，“数据库用户“为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名。USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301,S26301DBUser EXEC sp

18、_grantdbaccess zhangsan,zhangsanDBUser在stuDB数据库中添加两个用户第24页/共33页创建数据创建数据库用户库用户第25页/共33页系统内置的数据库用户n ndbo 用户n n表示数据库的所有者（表示数据库的所有者（DB DB OwnerOwner）n n无法删除无法删除 dbo dbo 用户，此用户始终用户，此用户始终出现在每个数据库中出现在每个数据库中 n nguest 用户n n适用于没有数据库用户的登录帐适用于没有数据库用户的登录帐号访问号访问n n每个数据库可有也可删除每个数据库可有也可删除第26页/共33页向数据库用向数据库用户授权户授权 E

19、mployeesEmployeeID123LastNameDavolioFullerLeverling FirstName NancyAndrewJanetReportsTo22.delete FROM EmployeesSELECT*FROM Employees第27页/共33页权限的类权限的类型型n n授权的语法为：n nGRANT 权限 ON 表名 TO 数据库用户USE stuDBGO/*-为zhangsanDBUser 分配对表stuInfo 的select,insert,update权限-*/GRANT select,insert,update ON stuInfo TO zhan

20、gsanDBUser /*-为S26301DBUser 分配建表的权限-*/GRANT create table TO S26301DBUser第28页/共33页权限的类型权限的类型没有授予delete 权限第29页/共33页总结总结 2-1n n数据库的物理实现一般包括：n n创建数据库创建数据库n n创建表创建表n n添加各种约束添加各种约束n n创建数据库的登录帐户并授权创建数据库的登录帐户并授权n n创建数据库或表时一般需要预先检测是否存在该对象n n数据库从数据库从mastermaster系统数据库的系统数据库的sysdatabasessysdatabases表中查询表中查询 n n表从该数据库的系统表表从该数据库的系统表sysobjectssysobjects表中查询表中查询 第30页/共33页总结总结 2-2n n访问SQL Server某个数据库中的某个表，需要三层验证：n n是否是是否是SQL ServerSQL Server的登录帐户的登录帐户n n是否是该数据库的用户是否是该数据库的用户n n是否有足够的权限访问该表是否有足够的权限访问该表第31页/共33页第32页/共33页