SQL Server详细教程

一、初识SQL Server

1、数据文件与文件组、日志文件

数据文件
SQL Server 数据库具有以下3种类型的文件。
数据文件：用来存放数据，
（1）主数据文件
　　一个数据库必须有且只有一个主数据文件，其扩展名为.mdf
（2）次数据文件
　　一个数据库可以没有也可以有多个次数据文件，其扩展名为.ndf
（3）事务日志文件（.ldf ）
日志文件为数据库的恢复提供日志信息。每个数据库至少包含一个日志文件，也可以有多个。日志文件的扩展名建议为.ldf。

数据库文件组
出于分配和管理的目的，可以将数据库文件分成不同的文件组。每个文件组有一个组名。文件组分主文件组和次文件组。
（1）主文件组
主文件组包含主数据文件和任何没有明确指派给其它文件组的其它文件。
（2）用户定义文件组(次文件组)
用户定义文件组是在 create database 或 alter database 语句中，使用 FILEGROUP 关键字指定的文件组。
一个文件只能属于一个文件组，一个文件组也只能被一个数据库使用。

事务日志
事务日志是一个与数据库文件分开的文件。它存储对数据库进行的所有更改，并记录全部插入、更新、删除、提交、回退和数据库模式变化。
事务日志是任何数据库的关键组成部分，是备份和恢复的重要组件，如果系统出现故障，它将成为最新数据的重要来源。
在默认的情况下，所有数据库都使用事务日志。事务日志的使用是可选的，但是，除非您因特殊原因而不使用，否则您应始终使用它。运行带有事务日志的数据库可提供更强的故障保护功能、更好的性能以及数据复制功能。

数据库快照

2、系统数据库

SQL Server 包含master、model、msdb和tempdb4个系统数据库
（1）Master数据库
系统最重要的数据库，记录了所有系统信息。如所有的
登录信息、系统设置信息、SQL Server的初始化信息和其它
系统数据库及用户数据库的相关信息。
（2）Model数据库
是模板数据库，为新建立的数据库提供模板和原型。
（3）Tempdb数据库
是一个临时数据库，它为所有的临时表、临时存储过程及其它临时操作提供存储空间。
（4）Msdb数据库
是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。

3、掌握建立、修改和删除数据库的方法

建立数据库
（1）使用对象资源管理器建立数据库
（2）使用T-SQL命令建立数据库
建立数据库的最简方法

CREATE    DATABASE   database_name

建立数据库的完整语法

CREATE   DATABASE   database_name
[ ON  [ PRIMARY ]  
    [<filespec> [，…n] ]
]
[ LOG ON {
    <filespec> [，…n] } ]

其中filespec在实际应用中用下面相应代码替换：
<filespec>::=
（[ NAME=logical_file_name，]
  FILENAME=‘os_file_name’
  [，SIZE=size]
  [，MAXSIZE={
   max_size|UNLIMITED}]
  [，FILEGROWTH=growth_increment] ）

例子：

CREATE  DATABASE  score3
ON  PRIMARY
( NAME=score3_data,
FILENAME='D:\sql\score3_data.mdf’,
SIZE=5MB,
MAXSIZE=20MB,
FILEGROWTH=10%
),
( NAME=score3_data1,
FILENAME=’ D:\sql\score3_data1.ndf’,
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
) LOG ON
( NAME=score3_log,
FILENAME=’ D:\sql\score3_log.ldf’,
SIZE=2MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB )

修改和删除数据库
使用对象资源管理器修改数据库
使用T-SQL命令修改数据库

alter database   database_name
{
   ADD FILE<filespec>[,…n]
|ADD LOG FILE <filespec> [,…n]
|ADD  FILEGROUP  group_name
|REMOVE  FILE  logical_file_name 
|REMOVE FILEGROUP group_name
|MODIFY  FILE <filespec>
|MODIFY  NAME=new_databasename
|MODIFY FILEGROUP  group_name
	 NAME=new_groupname  } 

Database_name:要修改的数据库的名称。
ADD FILE<filespec>[,…n]:添加数据文件。
ADD LOG FILE <filespec> [,…n]：添加日志文件。
ADD  FILEGROUP  group_name：添加文件组。
REMOVE  FILE  logical_file_name：删除文件，是物理删除。
REMOVE FILEGROUP group_name:删除文件组。
MODIFY  FILE <filespec>：修改数据库文件。
MODIFY  NAME=new_databasename：重命名数据库。

注意点：
修改文件属性时,不需指定物理文件名。
文件大小不能小于初始容量。
删除文件组前必须保证该文件组为空，若其中有文件则应先删除 。

二、创建和管理数据表

1、掌握系统数据类型

SQL Server的数据类型是SQL Server预先定义好的，可以直接使
用。SQL Server提供以下系统数据类型。
（1）bit
状态类型，可以取值为1或0。
（2）整型
（1）bigint（长整型）：8个字节
（2）int（基本整型）：4个字节
（3）Smallint （短整型）：2个字节
（4）Tinyint（微整型） ：1个字节 ，表示无符号整数，其取值范围0~255.
（3）精确数值型
包括decimal和numeric两种类型，该数值型数据由整数部分和小数部分组成，存储数值的范围为-10e38+1~10e38-1。
格式：
decimal(p,[s])或者numeric(p,[s])
其中：p（有效位数，小数点左右两侧位数之和）
s（小数位数），s默认值为0，0<=s<=p
例：decimal(10,6)
表示数中共有10位数，基中整数占4位，小数占6位。
decimal和numeric功能等价
（4）近似数据类型
用科学计数法来表示浮点数的数据类型。浮点数为近似值，包括re
al和float两种类型。两者的区别和能表示的数值范围如表所示。

数据类型	数据范围	占用存储空间
real	-3.40E+38~3.40E+38	4个字节
float	-1.79E+308~1.79E+308	8个字节

real：精确到7位小数
float：可以精确到第15位小数，默认占用8个字节的存储空间。Float数据类型也可以写为float（n）的形式，n为1到15之间的整数值。当n取1到7时，系统用4个字节存储它；当n取8到15时，用8个字节存储它。
（5）字符数据类型
字符型数据是指由字母、数字和其他一些特殊符号（如$，@）构成的字符串。在引用字符串时要用单引号括起来。字符型包括char、varchar、nchar和nvarchar四种类型。

1)char(n)
存放固定长度的n个字符数据。若输入字符长度不足n时，则用空格补足。1≤n≤8000。

2)varchar(n)
存放可变长度的n个字符数据。若输入字符长度不足n时，则按实际输入长度存储。1≤n≤8000。

3）nchar[(n)] ：可存储1~4000个定长Unicode字符串，字符串长度在创建时指定；如未指定，默认为nchar(1)。每个字符占用2bytes存储空间。

4）nvarchar[(n)] ：可存储最大值为4000个字符可变长Unicode字符串。可变长Unicode字符串的最大长度在创建时指定，如nvarchar(50)，每个字符占用2bytes存储空间。

（6）货币型（低版本的类型）
用十进制数来表示货币值。使用货币型数据时必须在数据前加上货币符号（$），当货币值为负数时，在符号和数据之间加负号，如：$123，$-231。
包括money和smallmoney两种类型。

数据类型	数据范围	占用存储空间
money	-2E+63~2E+63-1	8个字节
smallmoney	-2E+31~2E+31-1	4个字节

（7）文本型
当存储的字符数目大于8000时使用文本型，文本型包括text和ntext两种。
text：用来存储ASCLL编码字符数据，最多可以存储231-1（约20亿）个字符。在定义Text数据类型时，不需要指定数据长度.

ntext：用来存储Unicode编码字符型数据，最多可能存储230 -1（约10亿）个字符，其存储长度为实际字符个数的两倍，因为Unicode字符用双字节表示。

（8）日期型
旧类型：包括smalldatetime和datetime。
日期型数据以字符的形式表示，即要用单引号括起来。

数据类型	日期范围	占用存储空间
smalldatetime	1900年1月1日—2079年6月6日	4个字节
datetime	1753年1月1日—9999年12月31日	8个字节

（9）table数据类型
table数据类型是一种特殊的数据类型，用于存储结果集以进行后续处理。

（10）二进制数据类型
binary[(n)]：存放n字节固定长度的二进制数据。1≤n≤8000。
varbinary[(n)]：存放n字节可变长度的二进制数据，
1≤n≤8000。

（11）自定义数据类型
使用sp_addtype创建用户自定义数据类型的语法如下：

sp_addtype[@typename=]type，
[@phystype=]system_data_type
[，[@nulltype=]'null_type']

参数说明：
①[@typename=]type：
指定创建的用户自定义数据类型的名称。
用户自定义数据类型名称必须遵循标识符的命名规则，而且在数据库中唯一。
②[@phystype=]system_data_type：
指定用户自定义数据类型所依赖的系统数据类型。
③[@nulltype=]‘null_type’：指定用户自定义数据类型的可空属性，
即用户自定义数据类型处理空值的方式。取值为“NULL”，“NOT NULL”或“NONULL”。

例 在score数据库中，创建名为“sex_type”自定义数据类型，依赖字符型数据，宽度为2，不允许为空。

USE score
EXEC  sp_addtype  sex_type,'char(5)', 'not null'

可以像使用系统数据类型一样使用自定义数据类型。

例 在score数据库的student表中的sex字段的数据类型修改为sex_type。

USE  score
ALTER  TABLE  student
ALTER  COLUMN  sex  sex_type

删除自定义数据类型
使用sp_droptype删除用户定义数据类型的语法如下：

Sp_droptype {
   ‘类型名’}

例 把score数据库中student表的sex字段的数据类型修改为char(2)，然后再删除自定义数据类型sex_type。

USE  score
ALTER  TABLE  student
ALTER  COLUMN  sex  char(2)
GO
EXEC  sp_droptype  sex_type

注意：当自定义数据类型被使用状态下不能被删除

2、建立、修改和删除表

（1）建立表
使用对象资源管理器建立表
使用T-SQL语句建立表

CREATE  TABLE <table_name> 
( 列名 数据类型 ［NOT NULL ／NULL] ［IDENTITY（初始值，步长值）］ [DEFAULT<默认值> ]

  [，…n]
［，UNIQUE (列名[，…n])］ 
［，PRIMARY  KEY(列名[，…n])］ 
［，FOREIGN  KEY(列名)  REFERENCES  table_name [(列名)]
［，CHECK(条件)］) 

说明
列名：用户自定义属性的名称，应遵守标识符的命名规则。
数据类型：用来指定该列存放何种类型的数据。
NOT NULL | NULL：指定该列是否允许存放空值。
IDENTITY（初始值，步长值）：用来指定标识列及其初始值和步长值。
UNIQUE：指定唯一性约束。
PRIMARY KEY：建立主键约束；
FOREIGN KEY：建立外键约束，括号中所指定的列即为外键； REFERENCES用来指定外键所参照的表，表名后面的列名用来指定外键所参照的列。
DEFAULT：为指定的列定义一个默认值，当该列没有录入数据时，则用默认值代替。
CHECK：定义检查约束，使用指定条件对存入表中的数据进行检查，以确定其合法性，提高数据的安全性。

例 在score数据库中建立一个名为teach的教师任教表，
并定义主键约束和外键约束。

Use score 
Go

CREATE TABLE teach
( teacher_id char(4)  NOT NULL,
course_id char(5) NOT NULL,
primary key(teacher_id,course_id),
FOREIGN KEY(teacher_id)  REFERENCES teacher(teacher_id),
FOREIGN KEY(course_id)  REFERENCES  course(course_id)
)

例 在score数据库中建立一个名为course的课程信息表，
并定义主键约束和课程名称字段的唯一性约束。

USE  score
GO
CREATE  TABLE　course
( course_id   char(5)  PRIMARY  KEY,
course_name  char(20) ,
period  int,
UNIQUE(course_name)
)

（2）修改表
使用对象资源管理器修改表
使用ALTER TABLE 语句修改表结构

ALTER  TABLE  table_name
{
     
   ALTER COLUMN  字段名　数据类型　[NULL|NOT NULL]
   |ADD   字段名　 数据类型　[NULL|NOT NULL]
   |ADD  CONSTRAINT 约束名　约束类型
   |DROP COLUMN 　字段名  [，…n]
   |DROP  CONSTRAINT 约束名
}

参数说明：
（1）ALTER TABLE：表明是要修改表
（2）table_name：被修改的表名
（3）ALTER COLUMN 列名：表明要更改的字段
（4）ADD 列名：添加新的字段
（5）DROP COLUMN 列名：删除指定的列
（6）DROP CONSTRAINT子句：用来删除指定的约束
（7）ADD CONSTRAINT：为表添加约束。各类型约束格式如下：
1）添加主键约束
ADD CONSTRAINT 约束名 PRIMARY KEY(列名[，…n])
2）添加外键约束
ADD CONSTRAINT约束名FOREIGN KEY(列名) REFERENCES table_name(列名)
3）添加默认值约束
ADD CONSTRAINT 约束名 DEFAULT <默认值> FOR <列名>
4）添加唯一性约束
ADD CONSTRAINT 约束名 UNIQUE(列名[，…n])
5）添加检查约束
ADD CONSTRAINT 约束名 CHECK(检查条件)

例 为score数据库中的course表添加课程类型字段，字段名为
c_type。

USE score
GO
ALTER  TABLE  course
ADD   c_type  char(12)

例 为score数据库中的class表添加主键约束，指定class_id字段为主键。

USE  score
GO
ALTER  TABLE  class
ADD  CONSTRAINT  PK_id 　PRIMARY  KEY(class_id)

（3）删除表
使用对象资源管理器删除表
使用T-SQL语句删除表
语法格式如下：

DROP  TABLE   table_name

参数说明：
DROP TABLE：指明要删除的对象是表。
table_name：指定要删除表的名称。

3、插入、修改和删除数据

（1）插入记录
使用对象资源管理器添加记录
使用INSERT语句添加记录
语法：

INSERT   [ INTO]  <表名>[(<字段名1>[，<字段名2>…])]
VALUES（<常量1>[,<常量2>]…
语法说明：
(1 )[(<字段名1>[，<字段名2>…])] ：要插入字段值的字段名
     省略即表示所有列都要插入数据。
(2) （<常量1>[,<常量2>]…）：要插入的字段值，与上面字段名一一对应。

例 使用INSERT语句往class表中录入记录。
方法一：

INSERT  class(class_id , class_name, dept_id)
VALUES(‘20170101', '17计算机软件1班','01')

方法二：

INSERT  class
VALUES(‘20170102', '17计算机软件2班','01')

说明：
①INSERT语句一次只能插入一条记录，如果想插入多条，需用逗号隔开。
②INTO 可以省略。
③当往表中所有列都录入数据并且指定数据的顺序与表结构中列的顺序一致时，则可以省略列名列表，如方法二；也可以指明所有列，如方法一。若只往指定的表中部分列录入数据，那么列名列表不能省略。

（2）修改记录
使用对象资源管理器添加记录
使用update语句修改记录
语法：

UPDATE  <表名>
SET <字段名>=<表达式>[，......n]
[WHERE　<条件>]

功能：
修改指定表中满足WHERE子句条件的记录。其中SET子句给出<表达式>的值用于取代相应的属性列值，如果WHERE省略，表示修改所有记录。

例 将姓名为张丹丹的address修改为广东广州

UPDATE  student
SET  address=’广东广州’
WHERE  student_name=’张丹丹’

（3）删除记录
使用对象资源管理器删除记录
使用DELETE 语句删除记录
语法：

DELETE  [FROM]<表名>［WHERE<条件>］

功能：删除表中满足条件的记录，省略WHERE表示删除所有记录。

三、数据查询

1、了解SQL语言的基本功能

SQL的全称为Structured Query Language(结构化查询语言)。它是数据库系统的通用语言，利用它，用户可以用几乎同样的语句在不同的数据库系统上执行同样的操作。
SQL语言是一个综合的，功能强大又简单易学的语言，它集数据查询、数据操纵、数据定义和数据控制于一体。SQL语言按照功能可以分为4大类。
（1）数据查询语言（Data Query Language，DQL）
按照指定的组合、条件表达式或排序检索已存在的数据库中的数据，但并不改变数据库中数据。命令动词有SELECT。
（2）数据定义语言（DDL：Data Definition Language）
创建、修改或删除数据库中各种对象，包括表、视图、索
引等。命令动词有CREATE、DROP、ALTER。

操 作 对 象		操 作 方 式
命令	创 建	修改	删除
表	CREATE TABLE	ALTER TABLE	DROP TABLE
视图	CREATE VIEW		DROP VIEW
索引	CREATE INDEX		DROP INDEX

（3）数据操纵语言（Data Manipulation Language，DML）
对已经存在的数据库进行记录的插入、删除、修改等操作。命令动词有INSERT、UPADATE、DELETE。

（4）数据控制语言（Data Control Language，DCL）
用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视。命令动词有GRANT、REMOVE等。

2、掌握各种查询操作

数据查询，也叫数据检索。是指针对数据库中的数据按指定的条件和特定的组合进行对数据表的检索。
（1）SELECT查询
基本格式是：

SELECT  <字段列表>
         FROM   〈表名〉
         [WHERE  〈查询条件〉]

含义是：根据WHERE子句的查询条件，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的字段次序，筛选出记录中的指定字段值。若不设置查询条件，则表示被查询的表中所有记录都满足条件。

完整格式是：

SELECT  [ALL|DISTINCT]<字段列表>
  [INTO  新表名]
FROM <表名列表>
[WHERE <查询条件>]
[GROUP BY <字段名>[HAVING <条件表达式>]]
[ORDER BY <字段名>[ASC|DESC]]

参数说明：
（1）ALL|DISTINCT
其中ALL表示查询满足条件的所有行；DISTINCT表示在查询的结果集中，消除重复的记录。
（2）<字段列表>：由被查询的表中的字段或表达式组成，指明要查询的字段信息。
（3）INTO 新表名
表示在查询的时候同时建立一个新的表，新表中存放的数据来源于查询的结果。
（4）FROM <表名列表>
指出针对那些表进行查询操作，可以是单个表，也可以是多个表，表名与表名之间用逗号隔开。
（5）WHERE <查询条件>
用于指定查询的条件。该项是可选项，即可以不设置查询条件，但也可以设置一个或多个查询条件。
（6）GROUP BY <字段名>
对查询的结果按照指定的字段进行分组。
（7）HAVING <条件表达式>：
对分组后的查询结果再次设置筛选条件，最后的结果集中只包含满足条件的分组。必须与GROUP BY子句一起使用。
（8）ORDER BY <字段名>[ASC|DESC]
对查询的结果按照指定的字段进行排序，其中[ASC|DESC]用来指明排序的方式。ASC为升序；DESC为降序。

整个SELECT语句的含义：

根据WHERE子句的筛选条件，从FROM子句指定的表中找出满足条件记录，再按SELECT语句中指定的字段次序，筛选出记录中的字段值构造一个显示结果表。
如果有GROUP BY子句，则将结果按group by后面的“字段名”的值进行分组，该字段中值相等的元组为一个组；
如果GROUP BY子句带有短语HAVING，则只有满足短语指定条件的分组才会输出。
如果有ORDER BY子句，则结果表要按照order by后面的<