sql server 2005学习笔记[6~9课]

 

【第六课】

本节课学习使用sql语句来操作数据库，具体内容有创建数据库，创建表，添加约束和创建登录帐户等。
sql 语句回顾
1.添加数据
   Insert [into] 表名(字段1,字段2,…) values (值1,值2…)
2.修改数据
Update 表名 set 字段1=值1，字段2=值2，..where = （条件）
3.查询数据
Select 字段1，字段2… from 表名 where (条件) order by 字段名
4.删除数据
Delete from 表名 where (条件)
一 用sql 语句创建和删除数据库
1 创建数据库
   语法：create database 数据库名
on primary
         (
          <数据文件参数>[,...n][<文件组参数>]
)
           [log on]
(
           {<日志文件参数>[,...n]}
)
文件的具体参数的语法如下。
（[name=逻辑文件名,]）
filename=物理文件名
[,size=大小]
[,maxsize={最大容量|unlimited}]
[,filegrowth=增长量])[,...n]

例句：
create database test
on primary
(name='test_data',
filename='d:\test_data.mdb',
size=3mb,
maxsize=unlimited,
filegrowth=3)
log on
(name='test_dblog',
filename='d:\test_dblog.ldf',
size=1mb,
filegrowth=1mb)

2 删除数据库
语法：drop database 数据库名

二 使用sql 语句创建和删除表
回顾下建表的步骤
确定表中有哪些列，确定每列的数据类型，给表添加各种约束，创建各表之间的关系。
1创建表
语法：create table 表名
       （字段1 数据类型 列的特征，
字段2 数据类型 列的特征，
…）
列的特征包括是否为空，是否是标识列，是否有默认值，是否为主键等。
例句：
create table userinfo
(id int identity (1,1),
       username varchar(10) not null,
       password varchar(16) not null,
       sex varchar(5) not null,
       email varchar(20),
       address varchar(50),
)

2 删除表
语法：Drop table 表名

三 使用sql语句创建和删除约束
回顾一下常用的约束
   主键约束（primary key constraint）:要求主键列数据唯一，并且不允许为空。
   唯一约束(unique constraint):要求该列唯一，允许为空，但只能出现一个空值。
   检查约束(check constraint):该列取值范围限制、格式限制等。
   默认约束（default constraint）:该列的默认值。
   外键约束（foreign key constraint）:用于在两表之间建立关系，需要指定引用主表的哪些列
1 添加约束
语法：alter table 表名
      Add constraint 约束名 约束类型 具体的约束说明
例句：
--主键约束
alter table userinfo
add constraint pk_id primary key (id)
--唯一约束
alter table userinfo
add constraint uq_username unique (username)
--默认约束
alter table userinfo
add constraint df_address default ('地址不详') for address
--检查约束
alter table userinfo
add constraint ck_email check(email like )
--外键约束
alter table username
add constraint fk_id
foreign key(id) references userinfo(id)

2 删除约束
语法：alter table 表名
         Drop constraint 约束名
四 使用sql 语句创建登录
   1创建登录帐户
     添加windows登录帐户需要调用sql server 内置的系统存储过程sp_grantlogin,
语法：exec sp_grantlogin ‘windows 域名\域帐户’
添加sql 登录帐户需要调用系统存储过程sp_addlogin,
语法：exec sp_addlogin '帐户', '密码'
2 创建数据库用户
需要调用系统存储过程sp_grantdbaccess
语法：exec sp_grantdbaccess '登录帐户','数据库用户'
3给数据库用户授权
语法：grant 权限[on 表名] to 数据库用户

【第七课】

本节课学习简单子查询以及exists和if子查询的用法
一 简单子查询
子查询就是把一条查询语句嵌入到另外一条查询语句当中，这条查询语句就成为子查询。他所嵌入到的那条查询语句为父查询。
例句一：
use test
Declare @stuNo int
Select @stuNo=stuno from stuinfo where stuname='小白'
Select * from stuinfo where stuno>@stuno
go
例句二:
use test
select * from stuinfo
      where stuno>(select stuno from stuinfo where stuname='小白')
go
例句三：
select stuname from stuinfo inner join scores
on scores.stuno=stuinfo.stuno where score=88
go
例句四：
select stuname from stuinfo
where stuno=(select stuno from scores where score=88)
go

二 in 和not in 查询
使用比较运算符不允许返回多条记录，这个时候我们就使用in 查询
例句一:
select stuname from stuinfo
where stuno in (select stuno from scores where score>85)
go

例句二:
select stuname from stuinfo
where stuno in (select stuno from scores)
go

例句三:
select stuname from stuinfo
where stuno not in (select stuno from scores)
go

三exists 和 not exists 子查询
如果子查询的结果非空，则exists返回真(true)，否则返回假(false)
select * from scores
if exists(select * from scores where score<80)
begin
update scores set score=score+5
select * from scores
end
else
begin
   update scores set score=score-2
   select * from scores
end
go

【第八课】

本节课学习存储过程，介绍常见的系统存储过程，以及如何创建并调用用户自定义存储过程。

一 什么是存储过程
存储过程(procedure)是SQL语句和控制流语句的预编译集合。存储过程存储在数据库内，可由应

用程序通过一个调用执行，而且允许用户声明变量，逻辑控制语句以及其他强大的编程功能。存

储过程可分为系统存储过程以及用户自定义存储过程。
存储过程的优点，允许模块化程序设计，允许更快地执行，减少网络流量。

二 常见的系统存储过程
系统存储过程是一组预编译的sql 语句，提供了管理数据库和更新表的机制，并充当从系统表中

检索信息的快捷方式。
常见的系统存储过程。
系统存储过程 说明
sp_databases 列出服务器上所有数据库
sp_helpdb 报告有关数据库或者所有数据库的信息
sp_renamedb 更改数据库的名称
sp_tables 返回当前环境下可查询的对象列表
sp_columns 返回某个表列的信息
sp_help 返回某个表的所有信息
sp_helpconstraint 返回某个表的约束
sp_helpindex 返回某个表的所有索引
sp_stored_procedures 列出当前环境中的所有存储过程
sp_password 修改登录帐户的密码
xp_cmdshell 运行DOS命令

三 用户自定义的存储过程
除了使用系统存储过程，用户还可以创建自己的存储过程。创建存储过程的语句为：create

procedure
1 创建不带参数的存储过程
创建存储过程语法：
create proc[edure] 存储过程名
[{@参数 数据类型}[=默认值][output],
........
{@参数N 数据类型}[=默认值][output]]
as sql语句

例句：
if exists(select * from sysobjects where name='proc_stu')
drop procedure proc_stu
go
create procedure proc_stu
as
declare @avgage float;
select @avgAge=avg(stuage) from stuinfo
if (@avgage>20)
   begin
    print '平均年龄大于岁,年龄最大的为'
    select top 1 stuname from stuinfo order by stuage desc
   end
else
   begin
    print '平均年龄不到岁,年龄最小的为'
    select top 1 stuname from stuinfo order by stuage
   end

2 创建带输入参数的存储过程
if exists(select * from sysobjects where name='proc_stu2')
drop procedure proc_stu2
go
create procedure proc_stu2
@age int
as
declare @avgage float;
select @avgAge=avg(stuage) from stuinfo
if (@avgage>@age)
   begin
    print '平均年龄大于'+convert(varchar,@age)+'岁,年龄最大的为'
    select top 1 stuname from stuinfo order by stuage desc
   end
else
   begin
    print '平均年龄不到'+convert(varchar,@age)+'岁,年龄最小的为'
    select top 1 stuname from stuinfo order by stuage
   end
exec proc_stu2 22

【第九课】

本节课学习触发器及其作用，以及如何创建触发器

一 触发器介绍

触发器属于一种特殊的存储过程，可以在其中包含复杂的SQL语句，触发器与存储过程的区别在于触发器能够自动执行并且不含有参数，触发器可以划分为INSERT触发器，UPDATE触发器，DELETE触发器。

优点：触发器可以自动调用，当发生了对数据库所做的任何修改时，与之相关的触发器就会立刻被激活，可以完成数据库中相关表之间的级联修改，可以对需要存储的数据加以限制并且能够实现比CHECK约束更为复杂的功能。

二创建触发器

语法：create trigger 触发器名称
        on {表名}
         [with encryption]
      for|after|instead of [delete][,][update][,][insert]
       as
        sql 语句[...n]
在执行触发器时，系统会自动创建两张临时表inserted,deleted,这两张表的结构与触发器所依赖的表类似，用于保存在用户操作过程中被插入或被删除的数据。在执行结束后两个临时表会自动被系统删除。

insert 触发器

use test
go
if exists(select * from sys.triggers where name = 'insert_sco')
drop trigger insert_sco
go
create trigger insert_sco on scores
instead of insert
as
    if exists (select * from inserted where stuno in (select stuno from stuinfo))
       print'添加成功'
    else
    begin
       print'添加失败'
       rollback transaction
end

Delete触发器：
use test
go
if exists(select * from sys.triggers where name = 'del_info')
drop trigger del_info
go
create trigger del_info
on stuinfo
after delete
as
delete from scores where stuno in(select stuno from deleted)
select * from deleted
go

2修改触发器

语法：alter trigger 触发器名称
   on<表名>
[with encryption]
    for|after|instead of [delete][,][insert][,][update]
as
sql 语句[...n]
例句：
alter trigger del_info
on stuinfo
after update
as
update scores set stuno=(select stuno from inserted) where stuno=(select stuno from deleted)
select * from inserted
select * from deleted

3 开启/禁用触发器

Enable/DISABLE TRIGGER <触发器名称>
ON <表名>

4删除触发器

DROP TRIGGER 触发器名称[,触发器名称]