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

(火炬)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)

补充: SQL数据库中把一张表从一个数据库中插入到另外一个数据库的一张表里 如果两个表结构完全一样的，用insert into data2.table2 select * from data1.table1

如果结构不一样或者你要指定字段，用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

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

提示：

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