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

(火炬)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 [ɡrəuθ] 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<='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

提示：

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

 

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。 如：查找日期范围……聚集索引比较适合

 

对于聚集索引，人们往往有一些错误的认识。其中，最常见的错误有：

l 聚集索引会降低insert操作的速度，因为必须要向后移动一半的数据来为新插入的行腾出空间。这种认识是错误的，因为可以利用填充因子控制填充的百分比，从而在索引页上为新插入的数据保留空间。如果索引页填满了，SQL Server将会进行页拆分，在这种情况下只有第一个页才会受到影响。

l 在使用标识列的主键上创建聚集索引是一种好的设计方法，它可以使对表的操作达到最快速度。这种认识是错误的，它浪费了创建其它更有效的聚集索引的机会。并且，使用这种方法会把每个新插入的记录行都存储到表尾部的同一个的数据页中，这将导致数据库的热点和锁争用。笔者曾经见过采用这种方法设计的数据库，对于每一个新订单，客户服务人员都不得不等待数分钟来加以确认。

l 聚集索引是具有魔力的。如果哪个查询的速度不够快，那么就在该列上创建聚集索引，对于表的操作速度一定会得到提高。这种认识也是错误的，聚集索引只是比非聚集索引稍稍快了那么一点点。因为在每个表上只能创建一个聚集索引，所以它也是一种宝贵的性能资源，只有在那些经常作为条件查询一组记录行的列上才应该建立聚集索引。

在创建聚集索引之前，应先了解数据是如何被访问的。可考虑将聚集索引用于：

l 包含大量非重复值的列。

l 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

l 被连续访问的列。

l 返回大型结果集的查询。

l 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

l OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。

非聚集索引

非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

典型的桌面数据库使用的是非聚集索引。在这类索引中，索引键值是有序的，而每个索引节点所指向的数据行是无序的。一个SQL Server表最多可以拥有249个非聚集索引。

非聚集索引与聚集索引一样有 B-树结构，但是有两个重大差别：

l 数据行不按非聚集索引键的顺序排序和存储。

l 非聚集索引的叶层不包含数据页。

相反，叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器，这些行定位器指向有该键值的数据行（如果索引不唯一，则可能是多行）。非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。在 SQL Server中，非聚集索引中的行定位器有式：

l 如果表是堆集（没有聚集索引），行定位器就是指向行的指针。该指针用文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID。

l 如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

由于非聚集索引将聚集索引键作为其行指针存储，因此使聚集索引键尽可能小很重要。如果表还有非聚集索引，请不要选择大的列作为聚集索引的键。

在创建非聚集索引之前，应先了解您的数据是如何被访问的。可考虑将非聚集索引用于：

l 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。

l 不返回大型结果集的查询。

l 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。

l 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。

l 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

22.创建触发器

卷烟库存表（smoke_t_stock） 卷烟销售表（smoke_t_sale）日志表(smoke_log) –sale销售

(1)在查询分析器里创建“卷烟库存表”上的insert触发器

Create trigger t_insert_smoke_t_stock---------------------stock库存

On smoke_t_stock

For insert

As

--执行下列语句，保证业务规则1

Insert into smoke_log(logcontent) value(‘添加卷烟库存信息爱’)

Go

 (2)delete触发器

Create trigger t_delete_smoke_t_stock---------------------stock库存

On smoke_t_stock

For delete

As

--执行下列语句，保证业务规则1

Insert into smoke_log(logcontent) value(‘删除卷烟库存信息爱’)

Go

(3)update触发器

Create trigger t_update_smoke_t_stock---------------------stock库存

On smoke_t_stock

For update

As

--执行下列语句，保证业务规则1

Insert into smoke_log(logcontent) value(‘更新卷烟库存信息爱’)

Go

(4)smoke_t_sale表上的insert触发器

Create trigger t_insert_smoke_t_sale

On smoke_t_sale

For insert

As

Update smoke_t_sale set saletotalprice=saleprice*salenum

Where smokeproductname=(select smokeproductname from inserted)

Go

----插入数据

Insert into smoke_t_sale values(‘红塔山人为峰’,’保全购货商’,20,250,0)

插入后查询结果显示 saletotalprice列由0为更新为5000.0000

 

(5)在smoke_t_stock表上的delete触发器

Create trigger t_delete_smoke_t_sale

On smoke_t_stock

For delete

As

Delete smoke_t_sale where smokeproductname=(select smokeproductname from deleted)

Go

---- smoke_t_stock表删除数据的同时与smoke_t_sale相关的表也同时删除相同条件的数据

--------------------------------------------------------------------------------------------------------------------------------

其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

对表的操作	Inserted逻辑表	Deleted逻辑表
增加记录（insert）	存放增加的记录	无
删除记录（delete）	无	存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。

 

23.存储过程

 在数据库中创建存储过程，SQL语句如下：

 USE [数据库名称]

 Create pro| procedure updatesalesMan  -- updatesalesMan存储名称

  @chineseName varchar(10),@englishName varchar(60),

  @telephone varchar(20) ,@mobilephone(20),……

AS

Begin

     Update salesman set chineseName=@@chineseName,

englishName=@englishName, telephone=@ telephone,

 mobilephone=@ mobilephone,……

where salesmanID=@ salesmanID

end

go

-------------------调用存储过程----------------

cm.ExecuteNonQuery(updatesalesman,commandType.storedprocedure,parames)

-----------------存储过程的调用-----------------

//存储过程方法的调用（举例）C#代码里操作

 Public datatable prosalesman(string chineseName String englishName ,string telephone ,string mobilephone,string salesmanID……)

{

Datatable dt=new Datatable();

Sqlparameter[] parames=new Sqlparameter[4];

Parames[0]=new sqlparameter(“@ chineseName”, chineseName);

Parames[1]=new sqlparameter(“@ englishName”, englishName);

……

Parames[3]=new sqlparameter(“@ salesmanID”, salesmanID);

dt=sqlhelper.ExecuteDataTable(“updatesalesman”,commandType.storedprocedure,parames);

return dt;

}

24.Transaction 事务处理（SQL）

在sQL server 数据库查询器里

Begin

Delete from [我的类别] where 类别编号=2 // 在这里 我的类别 代表表名称

If((select count(*) from [我的产品] where 类别编号=2)>3)

Begin

 Rollback transaction //事物 回滚

End

Else

 Begin

  Delete from [我的产品] where 产品编号=2

  Commite transaction //提交事物

End

---------------------------在。Net程序里（实例）-------------------

打开连接

Conn.open();

//声明一个事物

SqlTransaction trans=conn.beginTransaction();

Sqlcommand cmd=new sqlcommand();

Cmd.connection=conn;

//将事物指定给命令对象

Cmd.transaction=trans;

Try{

//指定命令文本

Cmd.commandText=”delete from 我的类别 where 类别编号=@ID”;

cmd.parameters.addwithvalue(“@ID”,categryID);

cmd.ExecuteNonQuery();

cmd.commandText=”select count(*) from 我的产品 where 类别编号=@ID”;

//如果属于该类别的产品数量大于3则回滚

If(convert.ToInt32(cmd.ExecuteScalar())>3)

 {

    Trans.Rollback();

}

Else{   //继续删除产品

//指定命令文本

cmd.commandText=”delete from 我的产品 where 类别编号=@ID”;

cmd.ExecuteNonQuery();

trans.commit();//提交事物

}

}

Catch(sqlexeption ex){

 Trans.rollback();//回滚事务

 Throw ex;

}

Catch(exeption ex){

Trans.rollback();//回滚事务

 Throw ex;

}

 

25./*-----------存储过程传参 方法的调用 及分页-------------*/

Public datatable prosalesman(int pageindex,int pagesize, out int rowcount)

{

Datatable dt=new Datatable();

Rowcount=0;

Sqlparameter[] parames=new Sqlparameter[3];

Parames[0]=new sqlparameter(“@ pageindex”, pageindex);

Parames[1]=new sqlparameter(“@ pagesize”, pagesize);

Parames[2]=new sqlparameter(“@ rowcount”, rowcount);

Parames[2].Direction=parameterDirection.output;

dt=sqlhelper.ExecuteDataTable(“mypage”,commandType.storedprocedure,parames);

Rowcount=convert.ToInt32(parames[2].value);

Return dt;

}