(火炬)MSSQLServer数据库案例教程

(火炬)MS SQL Server数据库案例教程 创建数据库： CREATE DATABASE TDB //数据库名称 ON ( NAME=TDB_dat,//逻辑文件名 在创建数据库完成之后语句中引用的文件名 数据库必须唯一 FILENAME='D:\mydb\TDB_dat.mdf',//操作系统在创建文件时使用的路径和文件名 SI

(火炬)MS SQL Server数据库案例教程

创建数据库：

CREATE DATABASE TDB //数据库名称

ON

(

NAME=TDB_dat,//逻辑文件名 在创建数据库完成之后语句中引用的文件名 数据库必须唯一

FILENAME='D:\mydb\TDB_dat.mdf',//操作系统在创建文件时使用的路径和文件名

SIZE=10,//指定数据文件或日志文件的初始大小（默认单位为MB）

MAXSIZE=50,// 指定数据文件或日志文件的最大大小，如果没有指定大小那么文件将磁盘曾满为止(UNLIMITED关键字指定文件大小不受限制—只受磁盘大小空间限制)

FILEGROWTH=5 //指定文件的增长曾量，文件值不能超过ＭＡＸＳＩＺＥ值的设置，０表示不增长，如果没有指定该参数，则默认值为１０％；数据文件增长方式growth [ɡruθ] n. 增长；发展；生长；种植

)

LOG ON

(

NAME=TDB_log,

FILENAME='D:\mydb\TDB_log.ldf',

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=5MB

)

删除数据库日志并收缩数据库：

1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG

2.截断事务日志：
BACKUP LOG 数据库名 WITH NO_LOG

3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成
--收缩数据库

删除数据库：

DROP DATABASE TESTDB,TDB…

1.查看Products表的结构

EXEC SP_HELP Products

2.插入数据

Insert into products (name,sex,ages) values(‘xu’,’男’,24)

如果结构不一样或者你要指定字段，用insert into data2.table2(字段1,字段2,字段) select 字段j,字段k,字段m from data1.table1

SQl 把数据库中的一张表复制到另一个数据库中：

select * into 北风贸易.dbo.Category from [Northwind].dbo.Category

或

select * into 北风贸易.Dbo.cat from [Northwind].dbo.Category

3.更新数据

Update products set productprice= productprice- productprice*0.5 where 或

Update products set productprice= productprice- productprice*0.5 where id=1

1、update 联合select批量修改sql语句：

update b set b.TagValue=a.TagValue from [Nx_TagData] as b,(select * from [Nx_TagData] where TagCode=205911

and CollectTime>='2012-11-22 00:00:00.000' and CollectTime<='2012-11-23 00:00:00.000') as a

where b.TagCode in (205915,205920,205922,206539,205908,205913,205917,205918,205809,205910,206285,206060)

and b.CollectTime=a.CollectTime

4.删除数据

Delete from Products where productname=’v8’

5.全部删除表中的数据

Delete from products 或

Truncate table products

6.给products表添加一个字段

Alter table products

Add column producttype varchar(10)

7.修改products 表 producttype字段的长度

Alter table products

Alter column producttype varchar(50)

8.将products 表删除

Drop table products

注释：drop table name[,…] 可以删除多个表

9.注释标示符

--(双连字符) /*…*/（正斜线—星号字符对）

10.创建表及primary key约束(一个表只能有一个 PRIMARY KEY 约束)

Create table t_p/*学生*/

(

P_id char(5) not null,

P_name char(8) not null,

Constraint pk_tp_id primary key (p_id)--创建主键约束 pk_tp_id为约束名

)

Create table rec/*教师*/

(

R_id char(5) not null,

R_name char(8) not null,

R_DATE datetime not null,

Constraint pk_rec_id_name primary key(R_id,name) –R_id与R_name组合约束

)

或者

Alter table rec—(增加约束)

Add Constraint pk_rec_id_name primary key(R_id,name) –R_id与R_name组合约束

提示：

（1）组合主键也可以像rec信息表那样在创建表时创建表约束，但是不能像创建t_p信息表那样创建成列级约束。

（2）以修改表的方式添加primary key 约束时，要求相应的列在创建表时必须有非空约束。

ALTER TABLE product

ADD CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED ([id] ASC)

注释：NONCLUSTERED 非聚集索引 (在CLUSTERED聚集索引前面加上NON 就变为非聚集索引)

11.default 约束

Create table rec

(

R_id char(5) not null,

R_name char(8) not null default getdate(),--该列的默认值取系统的当前的日期

R_date datetime not null

)

或

Alter table rec

Add constraint df_date defaut getdate() for R_date

12.check 约束

Create table rec

(

R_id char(5) not null,

R_name char(8) not null,

R_sex char(2) check(sex=’男’ or sex=’女’),--check 约束 值必须是 男或女 这两个任意一个值

R_DATE datetime not null

Rid char(18),

Constraint ck_rec_rid check (len(Rid)=18 or len(Rid)=15)—check约束 身份证值的长度只能为18或15这两个任意一个长度

)

或 添加check约束

Alter table rec

Add Constraint ck_rec_rid check (len(Rid)=18 or len(Rid)=15)

与

Alter table rec

Add Constraint ck_rec_sex check (sex=’男’ or sex=’女’)

13.unique 唯一约束

Create table rec

(

R_id char(5) not null,

R_name char(8) not null,

R_sex char(2) check(sex=’男’ or sex=’女’),--check 约束 值必须是 男或女 这两个任意一个值

R_DATE datetime not null

Rid char(18) unique,

)

或

Alter table rec

Add constrater un_Rid unique(pid)—限定身份证号码唯一,不会重复出现

14.foreign key 外键约束

作用是 学生表与教师表人的信息相关联 ,t_id列与R_id列定义foreign ke 约束

Create table courses

(

t_id char(5) not null foreign key references t_p(t_id),--与t_p表相关联 列级约束

R_id char(5) not null,

Grade char(16),

Class char(10),

Constraint fk_course_rec_R_id foreign key(R_id) references Rec(R_id)—与rec表相关联 表级约束

)

或

Alter table course

Add Constraint fk_course_rec_R_id foreign key(R_id) references Rec(R_id) —与rec表相关联

Alter table course

Add constraint fk_course_t_p_t_id foreign key(t_id) references t_p(t_id) --与t_p表相关联

知识点：

（1）与外键列t_id和r_id 列相对应的相关表中的列（学生表中t_id列和老师表中r_id列）必须定义为primary key约束或unique约束

（2）在建立外键时，外键列t_id和r_id列的数据类型及长度必须与相对应的相关表中的主键列（学生表中t_id列和老师表中r_id列）的数据类型及长度一致或者可以由SQL Server自动转换。

15.删除约束

删除R_id 列上名为ck_rec_rid的check约束

Alter table t_p

Drop constraint ck_rec_rid

对于创建时没有指定名称的约束，例如，服务器空间，学生信息表中sex列上创建的check约束，可以先使用如下的命令，查找到约束的名称。

Exec sp_constraint t_p或Exec sp_help constraint t_p

根据上面的语句执行后 找到想要的约束名称

再

alter table t_p

drop constraint ck_t_p_sex_1367E606

16创建索引

(1)非聚集索引—在stud表上创建名为studid_ind的聚集索引

Create clustered index studid_ind on stud(studid)

注释：一个表里只有一个聚集索引。

(2)非聚集索引—在stud表上创建名为studfullname_ind的非聚集索引

Create unique index studfullname_ind on stud(fname desc,lname) 唯一索引

Create nonclustered index studfullname_ind on stud(fname desc,lname)非聚集索引

注释:非聚集唯一索引 desc 降序 (去掉non 为聚集索引)

用“,”号隔开可以进行建立多个列的索引

17.查看stud表的索引

Select sp_helpindex stud

18.使用索引

Select * from stud (index=studid_ind) where id=’2007

19.删除索引

（1）drop index stud.studid_ind

20.修改stud表，设定studid为主键

Alter table stud

Constraint pk_studid primary key clustered(studid)

直接删除主键约束的pk_studid 索引 会报错

21.重建索引

(1)重建pk_studid索引

Dbcc dbreindex (stud,pk_studid)

注释：dbcc 重建索引命令 dbreindex 重建的标示

(2)重建pk_studid 索引，设定其填充因子占50%

Dbcc dbreindex (stud,pk_studid,50)

(3)重建studname_ind 索引

Create index studname_id on stud(fname,lname) with drop_existing

提示：

因为非聚集索引包含聚集索引，所以在去除聚集索引时，必须重建非聚集索引。如果重建聚集索引，则必须重建非聚集索引，以便使用新的索引。