SQLServer_第2章 数据库和表的创建

第2章 数据库和表的创建

2.1 数据库

2.1.1逻辑数据库

数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。

SQLServer2000的数据库对象主要包括：表、视图、索引、存储过程、触发器、约束和默认值等对象。

1. 对象的名称

（1）完全限定名（对象的全名）

服务器名.数据库名.所有者名.对象名  server.database.owner.object

（2）部分限定名 省略的部分取默认值或当前值

如：server.database..object

       server..owner.object

       database.owner.object

       server…object

       owner.object

       object

（3）命名规则

遵循T-SQL常规标识符命名规则，最长为30个字符，区分大小写

2.1.2 物理数据库

存储逻辑对象的实体，主要是文件和文件组，还有页和盘区等概念。

SQLServer2000的数据是以页为最基本的存储单位，大小是8KB，16个扇区。盘区是为表和索引分配存储空间的基本单元。一个盘区是大小为8个相邻的页（64KB），当表和索引的长度小于64KB时，和其他对象存储在共享的混合盘区中，一旦超过了64KB，就被存储在自己的统一盘区中。

1.       物理数据库中的文件（默认扩展名）

主数据文件（mdb）：有且只有1个

辅助数据文件（ndb）：可选的。可以没有，也可以有多个

日志文件       （ldf）：至少要有1个

2.       文件组

为了便于管理和分配数据而将文件组织在一起，通常可以为1个磁盘驱动器创建一个文件组，然后将特定的表、索引等与该文件组相关联，那么对这些表的存储、查询、更新等操作都在该文件组中。使用文件组可以提高表中的数据查询性能。

（1）主文件组。包含主数据文件和没有指定给其他文件组的文件；

（2）用户自定义文件组。FileGroup关键字

注意：    1个文件只能属于一个文件组；

              只有数据文件才能作为文件组成员，日志文件不能作为文件组的成员；

2.1.3     重要的4个系统数据库

（1）master：包含了SQLServer中的登录账号、系统配置、数据库位置以及错误信息等用于控制用户数据库和SQLServer的运行。

（2）model：为新创建的数据库提供模板

（3）msdb：为Agent调度信息和作业记录提供存储空间

（4）tempdb：为临时表和临时存储过程提供存储空间。所有与系统连接的用户的临时表和临时存储过程都存储于该数据库中。

2.2 数据库的创建、修改和删除

2.2.1 界面方式：企业管理器或向导

1. 在创建数据库的时候，需要考虑：

数据库的名称、所有者、主数据文件和辅助数据文件的大小/增量等、日志文件的大小/增量、文件组以及数据文件和文件组的分配关系。

2. 数据库可以修改的项目有：

       数据文件和日志文件的数量、大小及其增长方式；文件组的增加和删除，以及文件和文件组的分配关系。

       在删除某文件组时，要首先清空它的文件。主文件组的名称不能修改，更不能删除。

2.2.2 命令方式

1. create database 创建新数据库

例1：只有1个主数据文件和日志文件

/*使用create database命令示例*/

create database XSCJ1

on

(name='xscj1_data'

,filename='D:/sql2000/MSSQL/Data/xscj1_data.mdf'

,size=5MB

,maxsize=50MB

,filegrowth=10%

)

log on

(name='xscj1_log'

,filename='D:/sql2000/MSSQL/Data/xscj1_log.ldf'

,size=2MB

,maxsize=5MB

,filegrowth=1MB

)

GO

例2：有多个数据文件和日志文件

/*create database命令示例2: 有多个数据文件和日志文件*/

create database test2

on

primary

(

name='test2_data1'

,filename='D:/sql2000/MSSQL/Data/test2_data1.mdf'

,size=5Mb

,maxsize=50Mb

,filegrowth=10Mb

),

(

name='test2_data2'

,filename='d:/sql2000/mssql/data/test2_data2.ndf'

,size=5Mb

,maxsize=20Mb

,filegrowth=10%

),

(

name='test2_data3'

,filename='d:/sql2000/mssql/data/test2_data3.ndf'

,size=5Mb

,maxsize=20Mb

,filegrowth=10%

)

log on

(

name='test2_log1'

,filename='d:/sql2000/mssql/data/test2_log1.ldf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

),

(

name='test2_log2'

,filename='d:/sql2000/mssql/data/test2_log2.ldf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

)

GO

例3：有多个文件组的数据库

/*Create database 示例3：创建一个具有3个文件组的数据库 */

create database test3

on

primary /*主数据文件在默认文件组中*/

(

name='test3_data1'

,filename='d:/sql2000/mssql/data/test3_data1.mdf'

,size=5Mb

,maxsize=50Mb

,filegrowth=100%

),

(

name='test3_data2'

,filename='d:/sql2000/mssql/data/test3_data2.ndf'

,size=5Mb

,maxsize=10Mb

,filegrowth=2Mb

),

filegroup test3_group1

(

name='test3_data3'

,filename='d:/sql2000/mssql/data/test3_data3.ndf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

),

(

name='test3_data4'

,filename='d:/sql2000/mssql/data/test3_data4.ndf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

),

filegroup test3_group2

(

name='test3_data5'

,filename='d:/sql2000/mssql/data/test3_data5.ndf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

),

(

name='test3_data6'

,filename='d:/sql2000/mssql/data/test3_data6.ndf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

)

log on

(name='test3_log'

,filename='d:/sql2000/mssql/data/test3_log.ldf'

,size=1Mb

,maxsize=5Mb

,filegrowth=1Mb

)

GO

 

create database命令基本的格式如下：

create database DatabaseName

on

primary

(name=’’

,filename=’’

,size=

,maxsize=

,filegrowth=

),

(

),

filegroup FilegroupName1…n

(

)

Log on

(

),

 (

)

GO

2．Alter database   

1次只能修改某1个属性值,能修改的项目有:

1)        Add file … to filegroup filegroup_name

2)        Add log file ….

3)        Add filegroup filegroup_name

4)        Remove file logical_file_name

5)        Remove filegroup filegroup_name

6)        Modify file …

7)        Modify name=new_dbname

例1：alter database example 1

/*alter database example 1*/

/*change the maxsize of the master database-file to unlimited*/

alter database xscj

modify file

(name='xscj_data1'

,maxsize=unlimited

)

/*change the filegrowth of the master database-file */

Alter database xscj

Modify file

(name='xscj_data1'

,filegrowth=5MB

)

/* add the bak database-file*/

alter database xscj

add file

(name='xscj_data_bak'

,filename='d:/sql2000/mssql/data/xscj_data_bak.ndf'

,size=5Mb

,maxsize=50Mb

,filegrowth=5%

)

/* add filegroup*/

alter database xscj

add filegroup filegroup1

/*add two others database-files to the filegroup1 */

alter database xscj

add file

(name='xscj_data2'

,filename='d:/sql2000/mssql/data/xscj_data2.ndf'

,size=5Mb

,maxsize=50Mb

,filegrowth=5%

),

(name='xscj_data3'

,filename='d:/sql2000/mssql/data/xscj_data3.ndf'

,size=5Mb

,maxsize=50Mb

,filegrowth=5%

)

to filegroup filegroup1

/*add a log file*/

alter database xscj

add LOG file

(name='xscj_log2'

,filename='d:/sql2000/mssql/data/xscj_log2.ldf'

,size=5MB

,maxsize=20MB

,filegrowth=2MB

)

例2：

/*delete the bak database-file from xscj db*/

alter database xscj

 remove file xscj_data_bak

/*remove the filegroup1 and the files in it*/

alter database xscj

 remove file xscj_data2

go

alter database xscj

 remove file xscj_data3

go

alter database xscj

 remove filegroup filegroup1

go

 

/*delete the log files*/

alter database xscj

 remove  file xscj_log2   /* log file is wrong!!!*/

go

/*change the database name*/

alter database xscj

 modify name=xscjtemp  /*这里没有单引号*/

go

3.    删除数据库

/*drop the database*/

drop database xscjtemp

go

 

2.3 表的创建、修改和删除

2.3.1  SQL Server支持的数据类型

数据类型

符号标识

说明

整数型

bigint int smallint tinyint

8bytes 4bytes 2bytes 1byte

精确数值型

Decimal numeric

Decimal和numeric一样，但是decimal不能用于带有identity关键字的列，声明它们的方式是 decimal(p[,s])|numeric(p[,s])。其中，p是精度，s是小数位数。

其存储位数根据精度而定，如：

精度      字节

1－9       5

10－19     9

20－28    13

29－38     27

浮点型

Float real

4bytes / 8bytes  4bytes

货币型

Money  smallmoney

8bytes  4bytes

位型

Bit

该列不允许是null值

字符型

Char  varchar

Var  变长存储

Unicode型

Nchar nvarchar

 

文本型

Text ntext

 

二进制型

Binary  varbinary

 

日期时间型

Datetime smalldatetime

8bytes  4bytes

时间戳型

Timestamp

8bytes

图像型

Image

实际存储的是可变长的二进制数据

其他

Cursor sql-variant  table  uniqueidentifier

 

 

2.3.2 创建表

表可分为两种：表达实体的表和表达实体之间联系的表。

创建表的实质就是定义表的结构和约束。要确定的内容有表名、字段名、数据类型和长度、字段默认值、约束、能否为空、关键字、外键、索引等。

可以使用企业管理器和向导来新建一个表。

2.3.3 命令方式创建表

1．Create table

例1：

/* the simple example for the  “create table” command*/

use xscj

create table xs

(

stu_number char(6) not null

,stu_name char(8) not null

,stu_major char(10) null

,stu_sex bit not null

,stu_birth smalldatetime not null

,stu_total_score tinyint null

,stu_memo ntext null

)

go

create table 语句的完整格式：

create table table_name  /*指定表名*/

(

{<column_definitin>  /*列的定义*/

| column_name as computed_columu_expression /*定义计算列*/

|<table_constraint>}/*指定表的约束*/

)

[On {filegroup|default}] /*指定存储表的文件组*/

[textimage_on {filegroup|default}] /*指定存储text ntext image 类型数据的文件组*/

其中，列定义有：

Column_name datatype,null|not null,default expression,identity [seed,incremnet] [not for replication]]

Rowguidcol  /*全局标识列*/

2．Alter table

Alter table table_name

Alter column

Add column

Drop column

例1：

use xscj

/*add a new column named scholarship_grade*/

alter table xs

add

       stu_scholarship_grade tinyint null

go

 

/*alter some of the columns in the "xs" table*/

alter table xs

       alter column stu_name char(10)

       alter column stu_birth datetime

go

 

/*delete the column*/

use xscj

alter table xs

    drop column stu_scholarship_grade

go

3. drop table

drop table table_name

例1：

/*delete the table compeletly*/

use xscj

drop table xs

练习：

/*create a db named cpxs*/

create database cpxs

on

(

name='cpxs_data'

,filename='d:/sql2000/mssql/data/cpxs_data.mdf'

,size=10Mb

,maxsize=100Mb

,filegrowth=10%

)

log on

(

name='cpxs_log'

,filename='d:/sql2000/mssql/data/cpxs_log.ldf'

,size=2Mb

,maxsize=5Mb

,filegrowth=1Mb

)

Go

/*change the way of increasement to the cpxs db */

alter database cpxs

 modify file

(

name='cpxs_data'

,filegrowth=5Mb

)

 

/*add three table into  the cpxs db*/

use cpxs

create table product

(

product_id char(6) not null

,product_name char(10) not null

,product_price money not null

,product_store int not null

)

GO

/* add a column into the table product*/

alter table product

add

  product_introduce ntext null

go

 

create table sales

(

guest_id char(6) not null

,guest_name char(10) not null

,guest_area char(10) null

,guest_manager char(10) null

,guest_phone char(15) null

)

Go

 

create table trade /*product_sales*/

(

trade_time smalldatetime not null

,trade_product_id char(6) not null

,trade_guest_id char(6) not null

,trade_num int not null

,trade_mount money not null

)

练习2：

/*创建学生成绩数据库的T－sql语句*/

create database xscj

on

(

name='xscj_data'

,filename='d:/sql2000/mssql/data/xscj_data.mdf'

,size=5Mb

,maxsize=50Mb

,filegrowth=5Mb

)

log on

(

name='xscj_log'

,filename='d:/sql2000/mssql/data/xscj_log.ldf'

,size=1Mb

,maxsize=5mb

,filegrowth=1Mb

)

 

/*添加相关的表*/

use xscj

create table xs

(

stu_id char(6)  not null

  constraint stu_id_pk primary key

,stu_name char(8) not null

,stu_major char(10) null

,stu_sex bit not null default 1 /**/

,stu_birth smalldatetime not null

,stu_total_score tinyint null

,stu_memo ntext null

)

go

 

create table kc

(

kc_id char(3)  not null

  constraint kc_id_pk primary key

,kc_name char(16) not null

,kc_semester tinyint default 1  not null check(kc_semester>=1 and kc_semester<=8)

,kc_study_hours tinyint not null

,kc_credit tinyint null

)

go

 

create table xs_kc

(

stu_id char(6)  not null

,kc_id  char(3)  not null

,score tinyint null

,credit tinyint null

)

go

alter table xs_kc

 add constraint xs_kc_pk

 primary key clustered(stu_id,kc_id)

go

关于表的定义，还有很多重要的部分，如约束、主键、外键、触发器等等，在后面的章节会继续总结。