SQL第二章

一、创建数据库
创建一个指定多个数据文件和日志文件的数据库。该数据库名称为students，有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件。数据文件逻辑名称为student1和student2，物理文件名为studentdata1.mdf和studentdata2.mdf。主文件是student1，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为studentlog1和studentlog2，物理文件名为studentlog1.ldf和studentlog2.ldf，最大尺寸均为50MB，文件增长速度为1MB。
Create database students on primary (name=student1,filename=‘f:/data/studentdata1.mdf’,size=10,maxsize=unlimited,filegrowth=10%),(name=student2,filename=‘f:/data/studentdata2.mdf’,size=20,maxsize=100,filegrowth=1) log on (name=studentlog1,filename=‘f:/data/studentlog1.ldf’,size=10,maxsize=50,filegrowth=1),(name=studentlog2,filename=‘f:/data/studentlog2.ldf’,size=10,maxsize=50,filegrowth=1)
——create database命令一条可以创建多个数据文件和多个日志文件，用逗号隔开。
二、修改数据库
Alter database students add filegroup file2
Alter database students add file (name=‘student1’,filename=‘f:/data/studentdata1.mdf’),(name=‘student2’,filename=‘f:/data/studentdata2.mdf’) to filegroup file2
三、表的设计
   a 表包括的列
   b 每列的数据类型
   c 表间关系
四、主键是哪列或哪几列组合
1、哪些列值可以为空
2、哪些列是否要使用约束、规则
3、哪些操作牵涉索引，是否要建立索引

五、表的类型
     1、系统表  前缀为sys，无论系统库还是用户库
     2、用户表 
     3、临时表  存储在tempdb系统数据库中，当不再使用时会自动删除。

六、数据类型
    A、系统数据类型
系统数据类型是SQL Server预先定义好的，可以直接使用。
1．整型数据类型
（1）bigint：8个字节
（2）int（integer）：4个字节
（3）Smallint ：2个字节（-32768~32767）
（4）Tinyint ：1个字节 （0~255）
（5）bit：只能是0或1（0|1）数据为2值的字段

2．浮点数据类型 ：
用于存储十进制小数 ，采用只入不舍的方式
（1）Real：4个字节的，最大7位精确位数。
（2）Float：可以精确到第15位小数，默认占用8个字节的存储空间。Float数据类型也可以写为float（n）的形式，n为1~15之间的整数值。当n取1~7时，系统用4个字节存储它；当n取8~15时，用8个字节存储它。
（3）Decimal和numeric：可以提供小数所需要的实际存储空间，可以用2~17个字节来存储。也可以将其写为decimal（p，s）的形式。
注意：数值类型的总位数不包括小数。
例如：decimal（10，5），表示共有10位数，其中整数4位，小数5位。
——Real和Float类型是近似数据类型，不精确，不可作比较或主键
    如果强调数据的精确，则用Decimal和numeric

3．字符数据类型
用来存储各种字母、数字符号和特殊符号。在使用时需要在其前后加上英文单引号或者双引号。
（1）Char：一个字符占用1个字节。
其定义形式为：char（n）
n的取值为1~8000。默认n的值为1。
（2）Varchar：可以存储长达8000个字符的可变长度字符串，和char类型不同varchar类型根据输入数据的实际长度而变化。
其定义形式为：varchar（n）
（3）Nchar：采用Unicode（统一字符编码标准）字符集；每个Unicode字符占2个字节
其定义形式为：nchar（n）
（4）Nvarchar：使用Unicode字符集的Varchar数据类型。
其定义形式为：nvarchar（n）

4．日期和时间数据类型
（1）Datetime：占用8个字节。
用于存储日期和时间的结合体，可以存储从公元1753年1月1日零时起~公元9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达三百分之一秒，即3.33毫秒。
当存储datetime数据类型时，默认的格式是：MM DD YYYY hh:mm A.M./P.M。当插入数据或者在其它地方使用datetime类型时，需要用单引号把它括起来。
默认January 1,1900 12:00 A.M。可以接受的输入格式如下：Jan 4 1999、JAN 4 1999、January 4 1999、Jan 1999 4、1999 4 Jan和1999 Jan 4。datetime数据类型允许使用/、-和.作为不同时间单位间的分隔符。
（2）Smalldatetime：存储从1900年1月1日~2079年6月6日内的日期。4个字节。

5．文本和图形数据类型
（1）Text：容量可以在1~231-1个字节。
在定义Text数据类型时，不需要指定数据长度，SQL Server会根据数据的长度自动为其分配空间。
（2）Ntext：采用unicode标准字符集，用于存储大容量文本数据。其理论上的容量为230-1（1，073，741，823）个字节。
（3）Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。

6．货币数据类型
（1）Money：用于存储货币值，数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808 ~922337213685477.5808，精度为货币单位的万分之一。
（2）Smallmoney：其存储范围为-214748.3468~214748.3467。
一般，当为money或smallmoney的表输入数据时，必须在有效位置前面加一个货币单位符号（如$或其它货币单位的记号）；有的和decimal类型相似。

8．二进制数据类型
（1）Binary：
其定义形式为binary（n），数据的存储长度是固定的，即n+4个字节。二进制数据类型的最大长度（即n的最大值）为8000，常用于存储图像等数据。
（2）Varbinary：
其定义形式为varbinary（n），数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。
在输入二进制常量时，需在该常量前面加一个前缀0x。

9．特殊数据类型
（1）Timestamp：也称作时间戳数据类型。是一种自动记录时间的数据类型，主要用于在数据表中记录其数据的修改时间。它提供数据库范围内的唯一值；可用来观察数据是否有修改。
（2）Uniqueidentifier：也称作唯一标识符数据类型。Uniqueidentifier用于存储一个16字节长的二进制数据类型，它是SQL Server根据计算机网络适配器地址和CPU时钟产生的全局唯一标识符代码（Globally Unique Identifier，简写为GUID）；使用系统函数newid()自动产生——RowGuid。

10．新增数据类型
（1）Bigint：占用8个字节。
（2）sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据；用时要用类型转换函数取出。
（3）table：用于存储对表或者视图处理后的结果集。

选择数据类型的指导原则
如果列的长度可变，就使用某个变长数据类型；
如果是一个不断发展的列，要按预估选择大范围的类型；
数值型，如果强调精度则选decimal；
如果存储容量超过8000字节，使用text或image；如果存储容量小于8000字节，binary或char；最好使用vchar；
货币数据使用money；
不要把float和real数据类型作为主键

自定义数据类型
1．使用企业管理器创建
2．利用系统存储过程创建
sp_addtype [@typename=] type,
[@phystype=] system_data_type
[, [@nulltype=] ‘null_type’]
[, [@owner=] ‘owner_name’]

type：指定用户定义的数据类型的名称。
system_data_type：指定相应的系统提供的数据类型的名称及定义。注意，不能使用timestamp数据类型，当所使用的系统数据类型有额外说明时，需要用引号将其括起来。
null_type：指定用户自定义数据类型的null属性，其值可以为‘null’、‘not null’或者‘nonull’。默认与系统默认的null属性相同。
owner_name：指定用户自定义数据类型的所有者。

删除用户自定义数据
1．使用企业管理器
2．利用系统存储过程
sp_droptype [@typename=] ‘type’

创建表
表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。
在SQL Server 2000中，一个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。

利用企业管理器创建表
默认值：指定列的默认值。除定义为timestamp或带IDENTITY属性的列以外的任何列。删除表时，将删除默认值定义。只有常量值（如字符串）、SQL Server内部函数（如SYSTEM_USER（））或NULL值可以用作默认值。
精度和小数位数：精度是列的总长度，包括整数部分和小数部分的长度之和，但不包括小数点；小数位数指定小数点后面的长度。

标识：指定列是否是标识列。一个表只能创建一个标识列。不能对标识列使用绑定默认值和DEFAULT约束。必须同时指定种子和增量，或者两者都不指定。默认值（1,1）。能够成为标识列的数据类型有int、smallint、tinyint、numeric和decimal等系统数据类型；如果其数据类型为numeric和decimal，不允许出现小数位数。
标识种子：指定标识列的初始值。
标识递增量：指定标识列的增量值。

RowGuid：指定列是否使用全局唯一标识符uniqueidentifier数据类型。
公式：用于指定计算列的列值表达式，可以是数学表达式，也可以是字段名间的计算式，不用引号；计算得到的字段不需要选择数据类型。
排序规则：指定列的排序规则。
  说明：在数据库中表名必须是唯一的，但是如果为表指定了不同的用户，就可以创建多个相同名称的表。

例：设计一个水果商品数据表，记录各种水果的单价、数量、金额和基本数据(水果序号是标识，ISBN为RowGuid型，金额=单价*数量，基本数据=序号*2)。

利用SQL语句创建表
CREATE TABLE
[库名.[所有者].|所有者.]
数据表名
（{<列定义>|计算列 AS 计算表达式|<表约束>} [，…n]）
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

<列定义> ::= {列名 列类型 [列宽度] }
[ COLLATE < collation_name > ]
[[DEFAULT 默认值]
|[IDENTITY [（标识种子 ,标识增量） [ NOT FOR REPLICATION ] ] ] ]
[ROWGUIDCOL]
[<列约束>] [ ...n ]

——数据类型可以参考对象浏览器的系统数据类型

<列约束> ::=[ CONSTRAINT 列约束名 ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ] }
|FOREIGN KEY [（column [,…n]）]
REFERENCES ref_table [ （ ref_column [,…n]） ] [NOT FOR REPLICATION]
| CHECK [ NOT FOR REPLICATION ]
（ logical_expression ）}

<表约束> ::=[CONSTRAINT 表约束名]
[{ PRIMARY KEY 主键列名| UNIQUE 列名}
|FOREIGN KEY （列名）]
REFERENCES ref_table [ （ 参照列名 ] [NOT FOR REPLICATION]
| CHECK（约束表达式）}

—列约束用于单列，写在该列名后
—表约束涉及多列，写在所有列下面

Create table dbo.products
(produceID int IDENTITY(1,1) NOT NULL,
 productname nvarch(40) NOT NULL,
 supplierID int NULL,
 categoryID int NULL,
 quantityperunit nvarchar(20) NULL,
 unitprice money NULL constrain df_products_unitprice DEFAULT(0),
 unitsinstock smallint NULL constrain df_products_unitinstock DEFAULT(0),
Constraint pk_products PRIMARY KEY CLUSTERED(rpoductID),
Constraint fk_products_categories FOREIGN KEY(categoryID) REFERENCES categories(categoryID) on UPDATE CASCADE,
Constraint fk_products_suppliers FOREIGN KEY(supplierID) REFERENCES suppliers(supplierID) on DELETE CASCADE,
Constraint ck_products_unitprice CHECK(unitprice>=0),
Constraint ck_unitsinstock CHECK(unitsinstock>=0)
)

创建、删除和修改约束
约束是SQL Server提供的自动保持数据库完整性的一种方法 。
列级约束：列级约束是行定义的一部分，只能够应用在一列上。
表级约束：表级约束的定义独立于列的定义，可以应用在一个表中的多列上。

主键约束 Primary key (自动产生索引)
主键能够唯一地确定表中的每一条记录，主键不能取空值。主键约束可以保证实体的完整性。
1．通过企业管理器完成添加、删除和修改主键的操作
2．使用Transact-SQL语句设置主键约束
建表时：CONSTRAINT pk_主键约束名 PRIMARY  KEY [CLUSTERED|NONCLUSTERED]（ 列名[,…n]）

建表完成后：alter table 表名 add [constraint 约束名]primary key(字段名)

——一表中只能有一个主键约束

唯一性约束(自动产生索引)unique
唯一性约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。
使用唯一性约束的字段允许为空值；
可以把唯一性约束定义在多个字段上；
唯一性约束用于强制在指定字段上创建一个唯一性索引；
默认情况下，创建的索引类型为非聚集索引(NONCLUSTERED)。 

——一表中可以设置多个唯一约束

1．通过企业管理器完成创建和修改唯一性约束的操作
2．使用Transact-SQL语句完成唯一性约束的操作
建表时：CONSTRAINT  constraint_name
  UNIQUE  [CLUSTERED|NONCLUSTERED]
（column_name[,…n]）

建表完成后：alter table 表名 add [constraint 约束名] unique(字段名)

检查约束 check
保证数据库数据的完整性。
一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关；
一个表中可以定义多个检查约束；
每个CREATE TABLE语句中每个字段只能定义一个检查约束；
在多个字段上定义检查约束，则必须将检查约束定义为表级约束；
当执行INSERT语句或者UPDATE语句时，检查约束将验证数据；
检查约束中不能包含子查询。

1．使用企业管理器创建检查约束
2．用Transact-SQL语句创建检查约束。
建表时：CONSTRAINT  constraint_name
  CHECK [NOT FOR REPLICATION]
   （logical_expression）
建表完成后：alter table 表名 add [constraint ck_表缩写名] check(条件式)
alter table 表名 add 字段名 类型 check(条件式)
——复习条件表达式制作

约束中条件式的制作
数学函数
日期函数
字符串函数
比较符和逻辑符号
>、>=、<、<=、<>（!=）、not、and、or
特定单词
Between A and B
Is [not] null
[not] in (值列表)
like

默认值约束 default
每个字段只能定义一个默认约束；
如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；
不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上；
如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。

1．使用企业管理器创建默认约束
2．使用Transact-SQL语句创建默认约束

建表时:
Create table 表名
(字段名 类型 default ‘默认值’)

建表完成后:
alter table 表名 add [constraint df约束名] default ‘默认值’for 字段名

——exec sp_helpconstraint 表名

外键约束 foreign key
外键约束主要用来维护两个表之间数据的一致性。
外键约束提供了字段参照完整性on update|on delete|on insert；
外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配；
外键约束不能自动创建索引，需要用户手动创建；
用户想要修改外键约束的数据，必须有对外键约束所引用表的SELECT权限或者REFERENCES权限；
引用同一表中的字段时，可以只使用REFERENCES子句，不使用外键foreign key子句；

一个表中最多可以有31个外键约束；
在临时表中，不能使用外键约束；
主键和外键的数据类型必须严格匹配。
1．在企业管理器中添加外键约束
2．使用Transact-SQL语句设置外键约束
建表时:
CONSTRAINT  fk_子表_主表
FOREIGN KEY （外键名[,…n]）
REFERENCES  主表名[（主键名[,…n]）] [on delete {cascade | no action}]

建表完成后:
alter table 子表名 add constraint fk_子表_主表 foreign key(外键名) references 主表(主键名)

No action指明，任何试图删除|更新由其他表的外键引用的关键字的操作都将产生错误并回滚更改。默认选项。
如果在父表中定义了cascade和更改了一行，则引用表中相应行也将随着改变。

删除约束
alter table 表名 drop constraint 约束名

禁用约束
Alter table 表名 nocheck  constraint [all|约束名]
启动约束
Alter table 表名 check constraint [all|约束名]
—只能禁用check和foreign约束，其他约束只能删除再重新添加

规则
规则是一个向后兼容的功能，用于执行一些与 CHECK 约束相同的功能。CHECK 约束是用来限制列值的首选标准方法。CHECK 约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个 CHECK 约束。CHECK 约束作为 CREATE TABLE 语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。

下例创建一个规则，执行与前面主题中的 CHECK 约束示例相同的功能。Microsoft® SQL Server™ 2000 首选的方法是 CHECK 约束。
CREATE RULE id_chk AS @id BETWEEN 0 and 10000
sp_bindrule id_chk, 'cust_sample.cust_id'
增加、删除和修改字段
利用企业管理器增加、删除和修改字段
利用Transact-SQL语言增加、删除和修改字段
添加字段
alter table 表名 add 新字段名 数据类型
添加主键字段（主键表）
alter table 表名 add primary key(字段名)
删除字段
alter table 表名 drop column 删除字段名
增加、删除和修改字段
修改字段数据类型|宽度
alter table 表名 alter column 字段名 类型(宽度)
添加默认值
alter table 表名 add default ‘默认值’for 字段名
创建约束|约束有效(子表中)
alter table 表名 check constraint 约束名
约束无效
alter table 表名 nocheck constraint 约束名

ALTER TABLE table {[ALTER COLUMN 字段名 {新数据类型 [（ 宽度 [ , scale ] ） ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ] |{ADD | DROP } ROWGUIDCOL } ] |{ADD | DROP } DEFAULE ‘默认值’
  |ADD

{[<column_definition>] | column_name AS computed_column_expression } [ ,...n ]    | [ WITH CHECK | WITH NOCHECK ] ADD      { < table_constraint > } [ ,...n ]    | DROP     { [ CONSTRAINT ] constraint_name    | COLUMN column } [ ,...n ]    | { CHECK | NOCHECK } CONSTRAINT      { ALL | constraint_name [ ,...n ] }    | { ENABLE | DISABLE } TRIGGER      { ALL | trigger_name [ ,...n ] } }
查看网格
查看网格的定义
查看网格中的数据
查看网格与其它数据库对象的依赖关系
利用系统存储过程Sp_help查看库表信息
删除表
利用企业管理器删除表
利用Transact-SQL语言删除表
    DROP TABLE table_name

更新表中数据
利用企业管理器删除表
利用Transact-SQL语言
输入记录
修改记录
删除记录
输入和插入数据
INSERT [INTO]{表名|视图名} {[（字段列表）] { VALUES
（{DEFAULT|NULL|表达式}        [ ,...n] ）| derived_table}

1.use 库名
  insert into 表名 values (所有字段的值)
2.insert into 表名 default values
3.insert into 表名(指定字段名) values(指定字段值)
更新数据
UPDATE { 表名 | 视图名}
[ FROM { < table_source > } [ ,...n ]
SET
字段名= {表达式 | DEFAULT | NULL }[ ,...n ]
 [ WHERE  条件式 > ] 
删除数据
DELETE [FROM] { 表名WITH (< table_hint_limited > [ ...n ] )
| 视图名}
[ WHERE  <条件式>   ]

Use 库名      删除符合条件的记录
   delete from 表名 where 条件式
2. delete 表名    删除所有记录
删除数据
TRUNCATE语句
其语句格式为：
TRUNCATE  表名
创建表间关系
表设计器中创建表间关系
  关系建立，但不显式
数据库关系图中创建表间关系
  关系建立，并得到保存
SQL语句
 create constrain fk_子表名_主表名 foreign key(外键名) references 主表名(主键名)
索引
索引的概念
数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。

 

索引的优点
可以大大加快数据检索速度。
通过创建唯一索引，可以保证数据记录的唯一性。
在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。
使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。
可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。

索引的分类
    a聚集索引和非聚集索引
    b复合索引
    c唯一索引

    d创建索引
   1 使用企业管理器创建索引
在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项。

使用Transact-SQL语句创建索引
利用Transact-SQL语句中的CREATE INDEX命令可以创建索引，CREATE INDEX命令既可以创建一个可改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引 。

    2使用向导创建视图
       利用索引向导创建索引
      利用索引优化向导创建索引

查看、修改和删除索引 
   使用企业管理器查看、修改和删除索引及更改索引名
   使用企业管理器查看和修改索引
   使用企业管理器更改索引名
   使用企业管理器删除索引
   使用系统存储过程查看索引信息和更改索引名称
   使用系统存储过程查看索引信息
   使用系统存储过程更改索引名称

   使用Transact-SQL语句删除索引
  当不再需要某个索引时，可以使用Transact-SQL语句中的DROP INDEX命令删除索引。DROP INDEX命令可以删除一个或者多个当前数据库中的索引。