《sql语句对数据库的基本操作.ppt》由会员分享，可在线阅读，更多相关《sql语句对数据库的基本操作.ppt（32页珍藏版）》请在金锄头文库上搜索。
1、第二章数据库的实现数据库的实现回顾回顾在需求分析阶段，设计数据库的一般步骤为在需求分析阶段，设计数据库的一般步骤为？实体的映射关系有哪些？实体的映射关系有哪些？请简要说明三大范式的含义？请简要说明三大范式的含义？目标目标 掌握建库的掌握建库的SQL语句语句 掌握建表的掌握建表的SQL语句语句 掌握加约束的掌握加约束的SQL语句语句 掌握创建安全帐户的掌握创建安全帐户的SQL语句语句回顾数据库的基础知识回顾数据库的基础知识数据库文件的组成：数据库文件的组成：主数据文件：主数据文件：*.mdf次要数据文件：次要数据文件：*.ndf日志文件：日志文件：*.ldf数据库的其他属性数据库的其他属性：文件
2、存放位置，分配的初始空间，属于哪个文件组文件存放位置，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文件增长：可以按百分比或实际大小指定增长速度文件容量设置：可以指定文件增长的最大值或不受限文件容量设置：可以指定文件增长的最大值或不受限创建数据库创建数据库 CREATE DATABASE 数据库名数据库名 ON PRIMARY ( ，n ) LOG ON ( ，n )T-SQL创建数据库的语法：创建数据库的语法：主文件组，可选参数，默认主文件组，可选参数，默认创建数据库示例创建数据库示例 1CREATE DATABASE stuDB ON PRIMARY -默认就属
3、于默认就属于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, FILEGRO
4、WTH=1MB)GO 数据文件的具数据文件的具体描述体描述日志文件的具日志文件的具体描述体描述演示创建数据库演示创建数据库1：一个数据文件和一个日志文件：一个数据文件和一个日志文件 创建数据库示例创建数据库示例 2CREATE DATABASE employees ON ( /*-主数据文件的具体描述主数据文件的具体描述-*/ NAME = employee1, FILENAME = D:projectemployee1_Data.mdf , SIZE = 10, FILEGROWTH = 10% ), ( /*-次要数据文件的具体描述次要数据文件的具体描述-*/ NAME = employe
5、e2, 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_Lo
6、g.ldf , SIZE = 10, MAXSIZE = 50, FILEGROWTH = 1 )GO 演示创建数据库演示创建数据库2：多个数据文件和多个日志文件：多个数据文件和多个日志文件 删除数据库删除数据库如果如果SQL Server中已存在数据库中已存在数据库stuDB，运行下列语句，会出现什，运行下列语句，会出现什么问题？么问题？CREATE DATABASE stuDBON (.)LOG ON(.)GO演示：再次运行示例演示：再次运行示例1USE master -设置当前数据库为设置当前数据库为master，以便访问，以便访问sysdatabases表表GOIF EXISTS（SE
7、LECT * FROM sysdatabases WHERE name =stuDB） DROP DATABASE stuDBCREATE DATABASE stuDBON ( .)LOG ON( )GO删除数据库删除数据库删除数据库的语法删除数据库的语法：DROP DATABASE 数据库名数据库名 EXISTS（）语句：检测是（）语句：检测是否存在否存在stuDB数据库如果存数据库如果存在在stuDB数据库，则删除数据库，则删除 回顾表的基础知识回顾表的基础知识 建表的基本步骤：建表的基本步骤：确定表中有哪些列确定表中有哪些列确定每列的数据类型确定每列的数据类型给表添加各种约束给表添加各种
8、约束创建各表之间的关系创建各表之间的关系SQL Server中的数据类型中的数据类型 整型数据：整型数据：int smallint 浮点数据：浮点数据：float numeric字符数据字符数据固定长度：固定长度：char(6)可变长度可变长度: varchar(10) unicode编码：编码：nchar(10) 布尔数据（真布尔数据（真/假）假） bit: 1/ 0 货币数据货币数据 money日期数据日期数据 datetime创建表创建表建表的语法建表的语法CREATE TABLE 表名表名 ( 字段字段1 数据类型数据类型 列的特征，列的特征， 字段字段2 数据类型数据类型 列的特征，
9、列的特征， .)列的特征：列的特征：包括该列是是否为空（包括该列是是否为空（NULL）、是否是标识列（自动编号）、是）、是否是标识列（自动编号）、是否有默认值、是否为主键等。否有默认值、是否为主键等。 建表示例建表示例 1USE stuDB -将当前数据库设置为将当前数据库设置为stuDB GOCREATE TABLE stuInfo /*-创建学员信息表创建学员信息表-*/( stuName VARCHAR(20) NOT NULL , -姓名，非空（必填姓名，非空（必填） stuNo CHAR(6) NOT NULL, -学号，非空（必填学号，非空（必填） stuAge INT NOT N
10、ULL, -年龄，年龄，INT类型默认为类型默认为4个字节个字节 stuID NUMERIC(18,0), -身份证号身份证号 stuSeat SMALLINT IDENTITY (1,1), -座位号，自动编号座位号，自动编号 stuAddress TEXT -住址，允许为空，即可选输入住址，允许为空，即可选输入) GONUMERIC (18,0)代表代表18位数字，位数字，小数位数为小数位数为0演示：创建学员信息表演示：创建学员信息表stuInfoIDENTITY（起始值，（起始值，递增量）递增量） 建表示例建表示例 2CREATE TABLE stuMarks( ExamNo CHAR(
11、7) NOT NULL, -考号考号 stuNo CHAR(6) NOT NULL, -学号学号 writtenExam INT NOT NULL, -笔试成绩笔试成绩 LabExam INT NOT NULL -机试成绩机试成绩)GO演示：创建学员成绩表演示：创建学员成绩表 stuMarks删除表删除表如果当前数据库中已存在如果当前数据库中已存在stuInfo表，再次创建时系统将表，再次创建时系统将提示出错提示出错 。如何解决呢？。如何解决呢？删除表删除表删除删除表的语法：表的语法：DROP TABLE 表名表名USE stuDB -将当前数据库设置为将当前数据库设置为stuDB ，以便在以
12、便在stuDB数据库中建表数据库中建表GOIF EXISTS（SELECT * FROM sysobjects WHERE name=stuInfo ） DROP TABLE stuInfoCREATE TABLE stuInfo /*-创建学员信息表创建学员信息表-*/(.) GO回顾回顾SQL Server的约束的约束约束的目的：确保表中数据的完整型约束的目的：确保表中数据的完整型常用的约束类型：常用的约束类型：主键约束（主键约束（Primary Key Constraint）：要求主键列数据唯一，）：要求主键列数据唯一，并且不允许为空并且不允许为空唯一约束（唯一约束（Unique Con
13、straint）：要求该列唯一，允许为空，）：要求该列唯一，允许为空，但只能出现一个空值。但只能出现一个空值。检查约束（检查约束（Check Constraint）：某列取值范围限制、格式限）：某列取值范围限制、格式限制等，如有关年龄的约束制等，如有关年龄的约束默认约束（默认约束（Default Constraint）：某列的默认值，如我们的男）：某列的默认值，如我们的男性学员较多，性别默认为性学员较多，性别默认为“男男”外键约束（外键约束（Foreign Key Constraint）：用于两表间建立关系，）：用于两表间建立关系，需要指定引用主表的那列需要指定引用主表的那列添加约束添加约束
14、添加约束的语法：添加约束的语法：ALTER TABLE 表名表名 ADD CONSTRAINT 约束名约束名 约束类型约束类型 具体的约束说明具体的约束说明约束名的取名规则推荐采用：约束类型约束名的取名规则推荐采用：约束类型_约束字段约束字段主键（主键（Primary Key）约束：如）约束：如 PK_stuNo唯一（唯一（Unique Key）约束：如）约束：如 UQ_stuID默认（默认（Default Key）约束：如）约束：如 DF_stuAddress检查（检查（Check Key）约束：如）约束：如 CK_stuAge外键外键(Foreign Key)约束：如约束：如 FK_stu
15、No 添加约束示例添加约束示例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
16、TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)GO演示：给学员信息表演示：给学员信息表stuInfo添加约束添加约束添加主键约束（添加主键约束（stuNo作为主键作为主键）添加唯一约束（因为每人的身份证号全国唯一添加唯一约束（因为每人的身份证号全国唯一）添加默认约束（如果地址不填，默认为添加默认约束（如果地址不填，默认为“地址不详地址不详”）添加检查添加检查check约束，要求年龄只能在约束，要求年龄只能在1540岁之间岁之间添加外键约束（主表添加外键约束（主表stuInfo
17、和从表和从表stuMarks建立关系，关联字段为建立关系，关联字段为stuNo）添加约束示例添加约束示例演示：查看约束结果演示：查看约束结果删除约束删除约束如果错误地添加了约束，我们还可以删除约束如果错误地添加了约束，我们还可以删除约束 删除约束的语法删除约束的语法ALTER TABLE 表名表名 DROP CONSTRAINT 约束名约束名 例如：删除例如：删除stuInfo表中地址默认约束表中地址默认约束ALTER TABLE stuInfo DROP CONSTRAINT DF_stuAddress回顾回顾SQL Server的安全模型的安全模型数据库数据库 1数据库数据库 2数据库数据
18、库 3表表 1表表 2表表 1表表 2表表 1表表 2增删增删改查改查增删增删改查改查增删增删改查改查增删增删改查改查增删增删改查改查增删增删改查改查数据库用户数据库用户数据库用户数据库用户数据库用户数据库用户登录帐号登录帐号SQLserver三层安全管理三层安全管理登录方式回顾登录方式回顾登录验证有两种方式：登录验证有两种方式：SQL身份验证：适合于非身份验证：适合于非windows平台的用户或平台的用户或Internet用户，用户， 需需要提供帐户和密码要提供帐户和密码Windows身份验证：适合于身份验证：适合于windows平台用户，不需要提供密码，平台用户，不需要提供密码，和和win
19、dows集成验证集成验证登录帐户相应有两种：登录帐户相应有两种：SQL 帐户帐户和和Windows帐户帐户 创建登录创建登录添加添加 Windows登录帐户登录帐户 EXEC sp_grantlogin jbtrainingS26301 添加添加 SQL登录帐户登录帐户 EXEC sp_addlogin zhangsan, 1234演示创建登录演示创建登录EXEC表示调用存储过程，表示调用存储过程，存储过程类似存储过程类似C语言的函数语言的函数内置的系统管理员内置的系统管理员 帐户帐户sa密码默认为空，建议修改密码密码默认为空，建议修改密码 域名域名用户名用户名用户名用户名,密码密码创建登录创
20、建登录创建数据库用户创建数据库用户创建数据库用户需要调用系统存储过程创建数据库用户需要调用系统存储过程sp_grantdbaccesssp_grantdbaccess，其用法为：，其用法为：EXEC sp_grantdbaccess 登录登录帐户名帐户名，数据库用户名数据库用户名 其中，其中，“数据库用户数据库用户“为可选参数，默认为登录帐户为可选参数，默认为登录帐户，即数据库用户默认和登录帐户同名即数据库用户默认和登录帐户同名。USE stuDBGOEXEC sp_grantdbaccess jbtrainingS26301, S26301DBUser EXEC sp_grantdbacce
21、ss zhangsan, zhangsanDBUser在在stuDB数据库中添加两个用户数据库中添加两个用户创建数据库用户创建数据库用户系统内置的数据库用户系统内置的数据库用户dbo 用户用户表示数据库的所有者（表示数据库的所有者（DB Owner）无法删除无法删除 dbo 用户，此用户始终出现在每个数据库中用户，此用户始终出现在每个数据库中 guest 用户用户适用于没有数据库用户的登录帐号访问适用于没有数据库用户的登录帐号访问每个数据库可有也可删除每个数据库可有也可删除向数据库用户授权向数据库用户授权 EmployeesEmployeeID123LastNameDavolioFullerL
22、everling FirstName NancyAndrewJanetReportsTo22 .delete FROM EmployeesSELECT * FROM Employees权限的类型权限的类型授权的语法为：授权的语法为：GRANT 权限权限 ON 表名表名 TO 数据库用户数据库用户USE stuDBGO/*-为为zhangsanDBUser分配对表分配对表stuInfo的的select, insert, update权限权限-*/GRANT select, insert, update ON stuInfo TO zhangsanDBUser /*-为为S26301DBUser分
23、配建表的权限分配建表的权限-*/GRANT create table TO S26301DBUser权限的类型权限的类型没有授予没有授予delete权限权限总结总结 2-1数据库的物理实现一般包括：数据库的物理实现一般包括：创建数据库创建数据库创建表创建表添加各种约束添加各种约束创建数据库的登录帐户并授权创建数据库的登录帐户并授权创建数据库或表时一般需要预先检测是否存在该对象创建数据库或表时一般需要预先检测是否存在该对象数据库从数据库从master系统数据库的系统数据库的sysdatabases表中查询表中查询 表从该数据库的系统表表从该数据库的系统表sysobjects表中查询表中查询 总结总结 2-2访问访问SQL Server某个数据库中的某个表，需要三层验证：某个数据库中的某个表，需要三层验证：是否是是否是SQL Server的登录帐户的登录帐户是否是该数据库的用户是否是该数据库的用户是否有足够的权限访问该表是否有足够的权限访问该表
