1.创建数据库
create database bvtc_db --数据库名
on primary --在主文件组中指定文件.默认为第一个文件
(
name='bvtc_db_data', --指定主数据文件名称(逻辑名)
filename='F:/bvtc_db_data.mdf', --指定主文件的操作系统文件名称和路径.必须为安装SQL服务器的计算机上的文件夹.(物理文件名)
size=5MB, --初始容量大小
maxsize=20MB, --最大容量
filegrowth=20% --文件增长量(默认时为10%,不能超过maxsize)
)
log on --指定建立数据库的事务日志文件,文件扩展名为:ldf
(name='bvtc_db_log', -- 指定事务日志文件逻辑名称
filename='F:/bvtc_db_log.ldf',--指定物理文件名
size=10MB, --初始容量大小
filegrowth=2MB --文件增长量
)
collate Chinese_PRC_CI_AS --指定默认排序方法
GO
2.查看数据库信息
除了直接查看数据库属性外,我们以用系统存储过程式sp_helpdb:
exec sp_helpdb 'bvtc_db' --exec为执行语句,bvtc_db为数据库名
3.管理数据库
(1)打开或切换数据库: use bvtc_db
(2)修改数据库容量：可以直接在数据库属性里改，也可以用如下语句：
use bvtc_db
go
alter database bvtc_db
modify file
(
name=bvtc_db_data,--同样也可以是事务日志文件(bvtc_db_log)
size=20MB
)
(3)缩减数据库容量:
方法1:右击数据库名-所有任务-收缩数据库
方法2:
dbcc shrinkdatabase(database_name[,new_size,[,'masteroverride']])
实例:
use bvtc_db
go
exec sp_dboption 'bvtc_db','single user',TRUE --在缩减数据库前要将其设为单用户模式.
go
dbcc shrinkdatabase('bvtc_db') --此处将数据库缩减到最小容量.
go
exec sp_dboption 'bvtc_db','single user',FALSE
go
(4).设定修改数据库选项:
方法1:数据库属性,选项
方法2:
(1)查看数据库选项:exec sp_dboption 'database_name'
(2)修改数据库选项:exec sp_dboption [database_name,option_name,{true|false}]
实例:更改数据库bvtc_db为只读状态:
exec sp_dboption 'bvtc_db','read only',true
go
(5)更改数据库名称: exec sp_renamedb oldname,newname --权限仅限于sa
实例:将数据库bvtc_db改名为bvtc1_db
exec sp_dboption 'bvtc_db','read only',false
go
exec sp_renamedb 'bvtc_db','bvtc_db1',
go
(6)删除数据库:
drop database database_name1[,database_name2...]
或:exec sp_dbremove database_name --这是利用系统存储过程.
实例:将bvtc_db数据库删除:
Use master
drop database bvtc_db
go
4.数据库表的操作
(1)创建表: 在数据库student创建"学生表"
use student
go
create table 学生
(学号 char(12) constraint pk_xh primary key, --设定学号为该表的主键.
姓名 char(8) not null,--不充许为空.
性别 char(2),
出生日期 datetime,
入学时间 datetime,
班级代码 char(9) constraint fk_bjdm references 班级(班级代码), --将班级代码设为此表的外键.
专业代码 char(2),
系部代码 char(2)
)
on stugroup1 --将学生表创建在stugroup1文件组上.
go
(2)修改表:使用alter table语句可以对表的结构和约束进行修改.
实例:<1>在"学生"表中增加"家庭住址"一列,数据类型为varchar(30),不允许为空.
use student
go
alter table 学生
add 家庭住址 varchar(30) not NULL --add为增加列或约束的子句.
go
<2>在"学生"表中修改"家庭住址"一列为varchar(50),允许为空.
use student
go
alter table 学生
alter column 家庭住址 varchar(50) NULL --alter column为修改表列属性的子句.
go
<3>删除字段
use student
go
alter table 学生
drop column 家庭住址
go
(3)删除表:
use student
go
drop table 学生
go
(4)数据的添加与表的查看.
<1>向表中添加数据: insert [into] tablename(字段1,字段2,...) values(value1,value2,...)
use student
go
insert 学生 -- 表名
(学号,姓名,家庭住址) --所要插入的列名
values('001','种子张','我家') --所插入的值
如果是按顺序逐个列插入数据,则可以省略列名.
还可以将select查询结果插入到表中: insert [into] tablename1 (字段1,字段2,...) select 字段1,字段2... from tablename2
例:insert into 系部1
(系部代码,系部名称,系部主任)
select 系部代码,系部名称,系部主任
from 系部
go
(5)数据的修改:update
update table_name set Column_Name=** where ID=***
实例:根据"教学计划"表中的课程号,专业代码和专业学级修改教师任课表中的"起始周","结束周"列的值.
use student
go
update 教师任课
set 教师任课.起始周=教学计划.起始周,教师任课.结束周=教学计划.结束周
from 教学计划
where 教学计划.课程号=教师任课.课程号
and 教学计划.专业代码=教师任课.专业代码
and 教学计划.专业学级=教师任课.专业学级
(6)数据的删除:
<1>delete语句,Delete table_name 删除"表格记录"会把操作记录在日志中，可以通过事务回滚来恢复删除的数据
delete from table_name where 条件
实例:删除"课程注册"表中姓名为"种子张"的课程号为"001"的选课信息.
use student
go
delete 课程注册
from 学生
where 学生.姓名='王丽' and 课程注册.专业代码='001'
go
<2>truncate table tablename清空表格,删除"表格记录"不可恢复.
5.简单查询.
(1)select语句的主要格式.
select select_list
[into new_table_name] --指定使用结果集来创建一个新的数据表,表名为:new_table_name
from table_list
[where search_conditions]
[Group by group_by_list] --将结果集按照group_by_list列分组.
[having search_conditions]
[order by order_list[asc|desc] -- asc为升序(默认),desc为倒序.
(2)实例:
<1>查询"学生"表中全体学生的记录 *
use student
go
select * from 学生
go
<2>为结果集指定别名 as
use student
go
select 姓名,year(getdate()-year(出生日期) as 年龄 from 学生 --注意是用as ,年龄即为别名.
go
<3>选择表中不重复的记录 distinct
use student
go
select distinct 姓名
from 学生表
go
<4>限制返回行数 top
限制返回行数
选取第一条记录:select top 1* from table_name
选取前N条记录: select top N* from table_name
选取最后一条记录:select top 1* from table_name order by column_name desc
选取最后N条记录:select top N* from table_name order by column_name desc
选取任意一条记录(随机):select top 1* from table_name order by newid()
(3)查询满足条件的元组(常与where联用)
<1>查询条件: 比 较: =,<,>,>=,!=,<>,!>,!<;not+上述运算符
确定范围: between and,not between and 判断是否在范围内
确定集合: in,not in判断是否为列表中的值
字符匹配: like,not like 判断是否与指定的字符通配格式相符
空 值: is null,not is null 判断是否为空
多重条件: and,or,not 用于多重判断
实例:1.查询学生表中出生日期在1980-01-01和1982-01-01之间的学生姓名
select 姓名 from 学生 where 出生日期 between '1980-01-01' and '1982-01-01'
<2>.字符匹配.
[not] like '<匹配串>' [escape'<换码字符>']
其中,匹配串可以是一个完整的字符串,也可以是与通配符组成的字符串.
通配符有:%,_,[ ],[^]四种.%表示任意个字符如a%b表示以a开头,b结尾的任意个字符.,_表示任意一个字符,
[]表示方括号里的任意一个字符如a[bcde]表示以a头,bcde中任一个结尾的字符串.[^]表示不取方括号里的值.
实例:查询"学生"表中姓王的同学的所有信息.
select * from 学生 where 姓名 like '王%'
如果要查询的字符本身就含有%,_等就要使用escape'<换码字符>'短语对通配答进行转义了.
例:要查询delphi_2.9
where 课程名 like 'delphi/_2.9' escape'/'表示匹配字符串中紧跟在/之后的字符"_"不再是通配符.
<3>涉及空值的查询. not is null
实例:查询"班级"表中备注为空的班级信息.
select * from 班级 where 备注 is null --is不能用"="代替.
<4>对查询结果进行排序 asc升序(默认),desc降序.
实例:查询选修了课程号为001的课程的学生学号.按成绩降序排列.
select 学号,成绩 from 课程注册 where 课程号='001' order by 学号 asc,成绩 desc --有多个排序,先排前面的,相同则排后面的.
(4)对数据进行统计.
集合函数:count([distinct|all]*)统计记录个数.
count([distinct|all]<列名>)统计一列中值的个数.
sum([distinct|all]<列名> 计算一列值的总和(必须为数据类型).
avg,max,min分别求一列值中的平均数,最大值,最小值.
实例:<1>返回学生表中学生总人数.
select count(*) as 学生人数 from 学生
<2>返回一列的平均数
select avg(成绩) as 平均分 from 课程注册
<3>对结果进行分组.
group by 列名 [having 筛选条件表达式]
<4>使用compute子句
格式如下：compute 集合函数 [by 列名] --分类汇总.它显示详细记录，group by不显示。
其中，集合函数：sum(),avg(),count()等.
select * from 课程注册 order by 学号 compute sum(成绩）by 学号
(5)用查询结果生成新表 select into
select * into 课程注册副表 from 课程注册
创建空副表： select * into 学生副表 from 学生表 where 1=2
(6)合并结果集 union
将多个查询结果集合并为一个结果集
select 语句
{union select 语句},[,...n]
各个结果集列数必须相同，对应数据类型也要相同.最后结果集返回第一个select语句.
实例：查看“课程注册”表中选修了001课程或者选修了002课程的学生，也就是选择两者的并并集.
select * from 课程注册 where 课程号='001'
union
select * form 课程注册 where 课程号='002'
(7)连接查询 cross join （并集）
select 列名列表 from table_name1 cross join table_name2
实例：select * from 产品 cross join 产品销售
(8)等值与非等值连接查询
连接条件：[表名1].<列名><比较运算符>[表名２].<列名> 其中比较运算符有=,>=,<=,!=,>,<.
格式：select 列名列表 from table_name1 [inner] table_name2 on table_name1.列名=table_name2.列名 --inner指定为内连接，可以省.
(on 主键=外键）
实例：select * from 产品 inner join 产品销售 on 产品.产品编号=产品销售.产品编号 --inner join为交集，不同于cross join
自然连接：在等值连接中，把目标列中重复的属性列去掉则为自然连接.
(9)自身连接查询
实例：查询选修了两门或两门以上课程的学生学号和课程号
select a.学号，a.课程号 from 课程注册 as a join 课程注册 as b
on a.学号=b.学号 and a.课程号=b.课程号
(10)外连接查询（分为左外连接，右外连接和全外连接）
<1>.左外连接(table_name1在左边）
Select * from table_name1 left outer join table_name2 on table_name1.column_name=table_name2.column_name
<2>.右外连接(把left 改为right就行了)
<3>.全外连接(触类旁通,left 改为 full)将左表所有记录分别与右表的每一条记录进行连接组合，结果集中不满足连接条件的以null显示出来.
(11)复合连接条件查询.
实例：查询成绩在75分以上的学生的学号，姓名，选修课的课程号，课程名，专业代码，成绩，任课老师的教师编号，姓名.
select b.课程号，c.教师编号，c.姓名,a.学号,b.专业代码,b.专业学级,b.学期，b.成绩
from 学生 as a join 课程注册 as b on a.学号=b.学号 and b.成绩>75 join 教师 as c
(12)子查询 （查询块的嵌套）
select * from 产品
where 产品编号 in (select 产品编号 from 产品销售） --()内为子查询，其中in可以改为比较运算符.
(13)带有any或all运算符的子查询.
实例：查询比"1"班中所有学生年龄都要小的其它班上的学生学号与姓名
select 学号，姓名
from 学生
where 出生日期>all
(select 出生日期 from 学生 where 班级代码='1')
and 班级代码<>'1'
(14)带有exists,not exists运算符的子查询.不返回任何数据,只产生逻辑true,false.
实例：查询所有选修了'1'课程的学生姓名
select 学号，姓名
from 学生
where exists
(select * from 课程注册
where 学号=学生.学号 and 课程号='1'
)
触发器入门
创建一个简单的触发器
－－－－－－－－－－－－
触发器是一种特殊的存储过程，类似于事件函数，SQL Server" 允许为 INSERT、UPDATE、DELETE 创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列 T-SQL语句。
触发器可以在查询分析器里创建，也可以在表名上点右键->“所有任务”->“管理触发器”来创建，不过都是要写 T-SQL 语句的，只是在查询分析器里要先确定当前操作的数据库。
创建触发器用 CREATE TRIGGER
CREATE TRIGGER 触发器名称
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS
T-SQL 语句
注意：触发器名称是不加引号的。
如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
重命名触发器
……………………………………
用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename 是 SQL Server" 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。
用企业管理器重命名
在表上点右键->“所有任务”->“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触发器名称，点击“确定”。
删除触发器
…………………………
用查询分析器删除
在查询分析器中使用 drop trigger 触发器名称 来删除触发器。
也可以同时删除多个触发器：drop trigger 触发器名称,触发器名称...
注意：触发器名称是不加引号的。在删除触发器之前可以先看一下触发器是否存在：
if Exists(select name from sysobjects where name=触发器名称 and xtype='TR')
用企业管理器删除
在企业管理器中，在表上点右键->“所有任务”->“管理触发器”，选中所要删除的触发器，然后点击“删除”。
如何查看某个触发器的内容
……………………………………
用查询分析器查看
use 数据库名
go
exec sp_helptext '触发器名称'
将会以表的样式显示触发器内容。
除了触发器外，sp_helptext 还可以显示规则、默认值、未加密的存储过程、用户定义函数、视图的文本
用企业管理器查看
在表上点右键->“所有任务”->“管理触发器”，选择所要查看的触发器
如何查看当前数据库中有哪些触发器
………………………………………………
在查询分析器中运行：
use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。
sp_helptrigger
…………………………
存储过程 sp_helptrigger 用于查看触发器的属性。
sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。例：
use 数据库名
go
exec sp_helptrigger tbl
触发器更多语法
………………………………
INSTEAD OF
执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例：
create trigger f
on tbl
instead of delete
as
insert into Logs...
IF UPDATE(列名)
检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：
create trigger f
on tbl
for update
as
if update(status) or update(title)
sql_statement --更新了 status 或 title 列
inserted、deleted
这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。例：
create trigger tbl_delete
on tbl
for delete
as
declare @title varchar(200)
select @title=title from deleted
insert into Logs(logContent) values('删除了 title 为：' + title + '的记录')
说明：如果向 inserted 或 deleted 虚拟表中取字段类型为 text、image 的字段值时，所取得的值将会是 null。
触发器回滚
……………………
举个例子
我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的，如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利用回滚就可以巧妙地实现无法更改用户名。
use 数据库名
go
create trigger tr
on 表名
for update
as
if update(userName)
rollback tran
关键在最后两句，其解释为：如果更新了 userName 列，就回滚事务。
递归、嵌套触发器
……………………………………
递归触发器
递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。
间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1...
直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1...
嵌套触发器
类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。
--------------------------------------------------------------------------------
设置直接递归
默认情况下是禁止直接递归的，要设置为允许有两种方法：
T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true
EM：数据库上点右键->属性->选项。
设置间接递归、嵌套
默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：
T-SQL：exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许
EM：注册上点右键->属性->服务器设置。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
