创建与管理数据库

17
一.SQL Server中的数据类型及运算的优先级 Server中的数据类型及运算的优先级
SQL Server 对数据类型使用以下优先级顺序： 对数据类型使用以下优先级顺序： 用户定义数据类型（最高） 用户定义数据类型（最高） sql_varian t 、Xml datetimeoffset datetime2 、Datetime 、Smalldatetime date、time 、float 、real 、decimal 、 money 、smallmoney 、igint 、int 、smallint 、tinyint Bit 、ntext 、text 、image 、timestamp 、uniqueidentifier nvarchar（包括 nvarchar(max)）、nchar 、 （ ） varchar（包括 varchar(max)）、char 、 （ ） varbinary（包括 varbinary(max)） （ ） binary（最低） （最低）
20百度文库
例 1 创建未指定文件的数据库
USE master; GO IF DB_ID (N'mytest') IS NOT NULL DROP DATABASE mytest; GO CREATE DATABASE mytest; GO -- Verify the database files and sizes SELECT name, size, size*1.0/128 AS [Size in MBs] FROM sys.master_files WHERE name = N'mytest'; GO
26
-- execute the CREATE DATABASE statement EXECUTE ('CREATE DATABASE Archive ON PRIMARY (NAME = Arch1, FILENAME = '''+ @data_path + 'archdat1.mdf'‘, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME = '''+ @data_path + 'archdat2.ndf'‘, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch3, FILENAME = '''+ @data_path + 'archdat3.ndf'‘, SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)
9
(2) 系统资源数据库(mssqlsystemresource) ID为32767；SQL Server 2005将所有系统对象如：系统表、系 统视图、系统存储过程等逻辑上存在于各数据库的sys架构下， 但物理上存储在系统资源数据库上，内容只读，隐藏； 不包括用户数据和用户元数据。
10
(3) Model系统数据库 是所有数据库的模板，一旦创建新的数据库其结构由model数据库决 定，Tempdb系统数据库是必须的，每次启动实例将重新创建新的 Tempdb系统数据库，其来源为model数据库，Model数据库缺 失或故障将导致无法启动。
25
例 4 指定多个数据和 事务日志文件创建数据库
USE master; GO IF DB_ID (N'Archive') IS NOT NULL DROP DATABASE Archive; GO -- Get the SQL Server data path DECLARE @data_path nvarchar(256); SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1);
14
§3.4 SQL语言简介 SQL语言简介
1. SQL的发展
1974年，由Boyce和Chamberlin提出 1975~1979，IBM San Jose Research Lab的关系数 据库管理系统原型System R实施了这种语言 SQL-86是第一个SQL标准(ANSI) SQL-89、SQL-92（SQL2）、SQL-99（SQL3） 不同厂家的DBMS产品支持的SQL一般对标准SQL 都有扩展,本课程以SQL SERVER支持的T-SQL为 背景介绍。
1. 2.
WINDOWS 身份认证 Sql server 身份认证 系统管理员用户sa 系统管理员用户
5
§3.2 逻辑数据库与物理数据库
1. 逻辑数据库 从逻辑上看， 从逻辑上看，SQL Server 数据库由存放数据的表以及支持这些数据 的存储、检索、安全性和完整性的对象所组成。 的存储、检索、安全性和完整性的对象所组成。SQL Server 的数据 库对象主要包括表(table)、视图 库对象主要包括表 、视图(view)、索引 、索引(index)、存储过程 、 (stored procedure)、触发器 、触发器(triggler)和约束 (constraint)等。 和约束 等 用户在利用T-SQL编程时，经常要引用数据库中的对象，引用名称 编程时， 数据库中的对象， 用户在利用 编程时 经常要引用数据库中的对象 两种： 有两种： (1) -- 完全限定名：server.database.schema.object 完全限定名： (2) --部分限定名：部分限定名中未指定的部分采用默认值 部分限定名： 部分限定名 server:默认为本地服务器；database：默认为当前数据库 默认为本地服务器； 默认为本地服务器 ： scheme：默认为 ：默认为dbo。 。
6
2. 物理数据库 SQL Server物理数据库指用于存储数据库对象的文件及文 物理数据库指用于存储数据库对象的文件及文 件组。 件组。 (1) 页: p113 SQL Server存储数据的最小单位。 存储数据的最小单位。 存储数据的最小单位 (2) 扩展盘区 SQL Server分配给表和索引的单位空间。 分配给表和索引的单位空间。 分配给表和索引的单位空间
第三章 创建与管理数据库
1
§3.1 SQL Server的对象层次 Server的对象层次
2
架构:是形成单个命名空间的数据库实体的集合。 架构:是形成单个命名空间的数据库实体的集合。命名 空间是一个集合，其中每个元素的名称都是唯一的。 空间是一个集合，其中每个元素的名称都是唯一的。
3
4
SQL SERVER的登录方式 SERVER的登录方式
11
(4) tempdb系统数据库 存储临时表、临时存储过程、临时游标和临时表变量，存储临时需要的 工作表； 存储数据库引擎内部需要的工作表； 创建或重建索引时若指定sort_in_tempdb参数，存储中间结果。 每次实例启动将重新创建，所以不能保存永久数据。
12
(5) msdb系统数据库 供SQL Server Agent（代理服务）使用； 数据库引擎服务不受该数据库的影响； 如果不需要执行代理服务，该数据库的存在与否无关紧要。 （6）系统表 P43 (7) 系统存储过程 P44
19
CREATE DATABASE database_name /*指定数据库名 指定数据库名*/ 指定数据库名 [ ON 子句 ] /*指定数据库文件和文件组属性 指定数据库文件和文件组属性*/ 指定数据库文件和文件组属性 [ LOG ON 子句 ] /*指定日志文件属性 指定日志文件属性*/ 指定日志文件属性
21
例 2 创建指定数据和事务日志文件的数据库
USE master; GO IF DB_ID (N'Sales') IS NOT NULL DROP DATABASE Sales; GO
22
-- Get the SQL Server data path DECLARE @data_path nvarchar(256); SET @data_path = ( SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1); --master.sys.master_files为系统视图 为系统视图 --在 expression2 中搜索 expression1 并返回其起始位置 在
18
二. 数据库的定义 P117
语法： 语法： CREATE DATABASE database_name [ ON [ PRIMARY ] [ <filespec> [ ,...n ] ] [ , <filegroup> [ ,...n ] ] [ LOG ON { <filespec> [ ,...n ] } ] ] [ COLLATE collation_name ] [ WITH <external_access_option> ]
23
-- execute the CREATE DATABASE statement EXECUTE ('CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = '''+ @data_path + 'saledat.mdf'‘, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = '''+ @data_path + 'salelog.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )’); GO
15
2. T-SQL的的语言要素 （1）数据定义语言—DDL (2) 数据操纵语言—DML (3) 数据控制语言—DCL (4) 流程控制语句 （5）其它语言元素
16
§3.5 数据定义语言（DDL） 数据定义语言（DDL）
Data Definition Language
定义数据库、数据库架构（ 定义数据库、数据库架构（schema）及 ） 数据库对象(如 基本表、视图、索引、存储过程， 数据库对象 如：基本表、视图、索引、存储过程，等) 定义各表的有关信息 完整性约束 各表在磁盘上的物理存储结构 在SQL Server中，数据库及数据库对象可有两种方式创建、 中 数据库及数据库对象可有两种方式创建、 修改和删除： 修改和删除： ；（2） （1）界面方式；（ ）命令方式 ）界面方式；（ 下面主要介绍命令方式的创建
7
(3) 数据库文件： 数据库文件： 主数据文件--.mdf 主数据文件 辅助数据文件--.ndf 辅助数据文件 日志文件--.ldf 日志文件 (4) 文件组： 文件组： 主文件组、 主文件组、用户定义文件组
8
§3.3 系统数据库与用户数据库
SQL Server2005安装后，创建了如下系统数据库： P40 安装后， 安装后 创建了如下系统数据库： mssqlsystemresource、 Master、 model、tempdb、msdb。 1. 系统数据库 (1) Master系统数据库 包含了SQL SERVER的登录账号，系统配置、数据库位置及错误信息， 对服务器正常启动十分重要，一旦发生故障将导致服务器无法正常启动， 管理员发现一旦master数据库内容发生变化，就要及时备份。
24
例3
CREATE DATABASE XSCJ ON (NAME='XSCJ_Data', FILENAME='e:\sql\data\MSSQL\Data\XSCJ.mdf', SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON (NAME='XSCJ_Log', FILENAME='e:\sql\data\MSSQL\Data\XSCJ_Log.ldf', SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB) GO
13
2. 用户数据库 由用户根据应用需要创建的数据库，例如开发学生成绩管理系统时，根 据需要创建学生信息数据库student 每个数据库在创建后， 是必须的(删都删不掉 每个数据库在创建后，有4个Schema是必须的 删都删不掉 ，这4个Sc 个 是必须的 删都删不掉)， 个 hema为：dbo，guest，sys和INFORMATION_SCHEMA， 为 ， ， 和 ，