SQL SERVER（三）

一、创建与管理数据库

1、认识数据库

数据库：数据库相当于一个容器，在容器里装有表、视图、存储过程、数据库触发器等数据库对象。

表是数据库的基本单位，用来存储数据。表由表结构和行所组成，表结构给出表由那些列组成，以及每列的数据类型和存储类型的长度，行用来存储实体的实例，每一行就是一个实例。

2、系统数据库和实例数据库

①主数据库master ：主数据库记录所有服务器特定的配置信息，包括授权用户、数据库库、系统配置设置以及远程服务器。

②模板数据库model :模板数据库是新创建数据库的一种模板或原型。每当创建数据库时，模板数据库的内容就被复制到新的数据库中。模板数据库必须始终存在于SQL SERVER系统中。

③msdb数据库 ：msdb数据库支持SQL SERVER AGENT。msdb数据库包括任务调度、异常处理、警报管理、以及SQL EXECUTIVE SERVICE需要的系统操作员信息。

④临时数据库tempdb ：临时数据库是用作临时存储区域的一个临时的数据库。服务器用它自动的解决大的或嵌套的查询，或者用它来返回结果给用户之前分类的数据。

系统表：系统表用来存储SQL SERVER的配置、安全和数据库对象信息，SQL SERVER在系统表的借助下管理每个数据库。每一个数据库都有自己的系统表，主数据库中的系统表包含了SQL SERVER的信息，其他数据库中的系统表包含了含有数据库的信息。

文件：文件用来存储数据库的文件，通常有主数据文件，次数据文件和事务日志文件。（一个数据库必须要有一个主数据文件，后缀为.mdf文件；一个数据库至少有一个事务日志文件用来记录数据库中发生的所有修改；次数据文件可以提高查询性能）

文件组：文件组是文件的逻辑集合，用来存储数据文件和数据库对象。SQL SERVER自动创建一个名为PRIMARY的主文件组，主文件组上存有系统表。SQL SERVER 默认将数据文件、数据库对象存放于主文件组上。

3、创建数据库

（1）使用CREATE DATABASE创建数据库

USE master
GO

--创建数据库
CREATE DATABASE Xk
ON
PRIMARY
(NAME = Xk,
 FILENAME = 'C:\Xk.mdf',
 SIZE = 10MB,
 MAXSIZE = 20MB,
 FILEGROWTH = 2MB)
LOG ON
(NAME = Xk_log,
 FILENAME = 'C:\Xk_log.ldf',
 SIZE = 5MB,
 MAXSIZE = 30MB,
 FILEGROWTH = 2MB)
GO

（2）查看数据库

sp_helpdb
EXEC sp_helpdb master

（3）修改数据库

--扩充数据库
--1、新增文件组
USE Xk
GO
ALTER DATABASE Xk
ADD FILEGROUP Xk1
GO

USE Xk
GO
SP_HELPFILEGROUP
GO

--扩充数据库或日志文件的容量
USE master
GO
ALTER DATABASE Xk
ADD FILE
(NAME = 'Xk_data1',
 FILENAME = 'D:\Xk_data1.ndf',
 SIZE = 5MB,
 MAXSIZE = 50MB,
 FILEGROWTH = 3MB),
(NAME = 'Xk_data2',
 FILENAME = 'D:\Xk_data2.ndf',
 SIZE = 5MB,
 MAXSIZE = 50MB,
 FILEGROWTH = 3MB)
TO FILEGROUP Xk1
GO

ALTER DATABASE Xk
ADD LOG FILE
(NAME = 'Xk1_log1',
 FILENAME = 'D:\Xk1_log1.ldf',
 SIZE = 6MB,
 MAXSIZE = 50MB,
 FILEGROWTH = 3MB)
GO

SP_HELPDB Xk

--2、修改大小
USE Xk
GO
ALTER DATABASE Xk
MODIFY FILE(NAME = Xk,
			SIZE = 100MB)
GO
ALTER DATABASE Xk
MODIFY FILE(NAME = Xk_log,
			SIZE = 120MB)
GO


--缩小数据库和数据文件
--删除数据文件方法
ALTER DATABASE Xk
REMOVE FILE Xk_data1
GO
ALTER DATABASE Xk
REMOVE FILE Xk1_log1
GO

sp_helpdb Xk

--收缩数据文件
USE Xk
GO
DBCC SHRINKFILE(Xk,10)
GO
SP_HELPDB Xk

（4）修改或查看数据库选项

--显示文件组
USE Xk
GO
SP_HELPFILEGROUP

--当前数据库下所有的数据对象
SP_HELP

--修改数据库属性
USE Xk
GO
SP_DBOPTION
GO

--将数据库修改为只读模式
USE Xk
GO
SP_DBOPTION 'Xk','READ ONLY','OFF'
GO

USE Xk
GO
SP_DBOPTION 'Xk','SINGLE USER','TRUE'
GO

（5）重命名数据库

--重命名数据库
sp_renamedb 'Xk','CC'
GO

（6）删除数据库

--删除数据库
USE MASTER
GO
DROP DATABASE CC
GO

二 、创建与管理数据库

1、详细设计数据表

（1）表及设计
定义：表是一种用于存储数据的数据库对象。表中的数据以行和列的方式组织。表中的每一行表示一条唯一的记录，每一列表示记录的一个属性。表中列名必须唯一，但是数据库中的不同表内的列名可以相同。
在完成数据库设计并且已经创建数据库之后，需要在数据库中创建数据表。

数据表的设计要考虑的点：
①要定义的表名
②将表存放于那个文件组中，系统默认将表存放在PRIMARY主文件组上，如果将表创建在其他文件组上，需先创建文件组。
③每列的名称、数据类型和最大存储长度，列值是否允许为空。
④那一列或者那些列的组合需要定义为主键，哪一列需要定义为外键、唯一键或标识符。
⑤那些列需要定义存储数据的有效值范围，或在不输入数据时由系统自动给出默认值。
⑥需要按照哪些列查询数据。

（2）空值（NULL）
空值特点：空值不等于0、空格或零长度的字符串，NULL表示没有输入，通常表明值是未知的或者未定义的。
使用方法：
①测试某列是否未空值，可以在WHERE字句中使用IS NULL或者 IS NOT NULL语句。
②在查询窗口中查看查询结果时，空值在结果集内显示为NULL。
③如果包含空值列，则某些计算（平均值等）可能得不到预期的结果，所以在执行计算前根据需要消除空值。
④任何两个空值都不想等，比较两个空值或将空值与任何其他数据相比均返回未知。

2、创建数据表
（1）使用SQL Server Management Studio创建数据库
（2）使用 Transcact-SQL 语句创建数据表

USE Xk
GO
--创建班级信息表Class
CREATE TABLE Class
(ClassNo nvarchar(8) NOT NULL,
 ClassName nvarchar(20) NOT NULL,
 DepartNo nvarchar(2) NOT NULL)
GO

--创建学生信息表Student
CREATE TABLE Student
(StuNo nvarchar(8) NOT NULL,
 StuName nvarchar(10) NOT NULL,
 Pwd nvarchar(8) NOT NULL,
 ClassNo nvarchar(8) NOT NULL)
GO

--创建学生选课表StuCou
CREATE TABLE StuCou
(StuNo nvarchar(8) NOT NULL,
 CouNo nvarchar(3) NOT NULL,
 WillOrder smallint NOT NULL,
 State nvarchar(2) NOT NULL,
 RandomNum nvarchar(50) NULL)
GO

3、显示表结构

--语法：sp_help table_name
--例子：sp_help Class

4、修改数据表结构
（1）新增、删除列

--给Student表新增两列：生日列birthday，数据类型为日期型，允许为空；备注列bz,数据类型为nvarchar，长度20，允许为空。
USE Xk
GO
ALTER TABLE Student
ADD birthday datetime null,bz nvarchar(20) null
GO

--删除列
USE Xk
GO
ALTER TABLE Student
DROP COLUMN birthday
GO

（2）修改列定义

--将Student表的备注列bz,将列定义的长度修改为30。
USE Xk
GO
ALTER TABLE Student
ALTER COLUMN bz nvarchar(30) null
GO

（3）重命名列、数据表

--将Student表的备注列bz名字修改为StuBz
USE Xk
GO
sp_rename 'Student.bz','StuBz',COLUMN
GO

--将Depart表重命名为Department。
USE Xk
GO
EXEC sp_rename 'Depart','Department'
GO

5、删除数据表

--删除数据表
USE Xk
GO
DROP TABLE Department
GO

6、知识拓展（临时表和表变量）
（1）临时表
①本地临时表：本地临时表名称以“#”开始，它们仅对当前的用户连接是可见的，且当用户断开与SQL SERVER实例链接时被删除。如果没有使用DROP TABLE语句显示删除临时表，那么本地临时表在退出其作用域时将由系统自动删除。当存储过程结束时，将自动删除在存储过程中创建的本地临时表。
②全局临时表：全局临时表的名称以“##”开始，他们对任何用户都是可见的，且当所有引用该表的用户断开与SQL SERVER连接时会删除全局临时表。如果创建名为Student的表，则只要在数据中有使用该表的安全权限的用户就可以使用该表。如果名为#Student的本地临时表，则只有创建者能对该表执行操作，且在断开连接时删除删除该表。如果创建名为##Student的全局临时表，则数据表中的任何用户均可对该表执行操作。SQL SERVER在所有使用该全局临时表的用户断开连接后删除该表。

--先定义临时表，再使用。
USE Xk
GO

--创建临时表#t1
CREATE TABLE #t1
( CouNo nvarchar(3),CouName nvarchar(30))

--向临时表输入数据的另一种方式
INSERT INTO #t1
VALUES('000','临时课程')

--向临时表输入数据的另外一种方式
INSERT #t1
SELECT CouNo,CouName
FROM Course
GO


--直接将结果集的数据输入到临时表
USE Xk
GO
SELECT CouNo,CouName INTO #t2
FROM Course
GO

(2)表变量：表变量是一种特殊的数据类型，可用于函数、存储过程和批处理中，用来存储结果集，供后续使用。该数据类型主要用于临时存储一组数据，这些行数据将作为表值函数的结果集返回。
表变量的行为类似于局部变量，有明确定义的作用域，该作用域为声明变量的函数、存储过程或批处理。与其他变量一样，再定义表变量的函数，存储过程或批处理结束后，将自动清除表变量。
作用：
①在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
②涉及表变量的事物只在表变量更新期间存在，这样就减少了表变量对锁定和记录资源的需求。
③在其作用域内，表变量可像常规表那样使用。该变量可应用于SELECT\INSERT\UPDATE和DELETE语句中用到表或表的表达式的地方。
④不支持在表变量之间进行赋值操作。
⑤由于表变量作用域有限，而且不是永久数据库的一部分，因此不受事物回滚的影响。

表变量的使用方法

USE Xk
GO
--定义表变量
DECLARE @t
TABLE(CouNo nvarchar(3),CouName nvarchar(30))
--向表变量输入数据的一种方式
INSERT INTO @t VALUE('000','临时课程')
--向表变量输入数据的另外一种方式
INSERT @t SELECT CouNo,CouName
FROM Course
GO