数据库基础复习ing...1年前的笔记整理

来源：互联网发布：剑网三重制版 mac 编辑：程序博客网时间：2024/05/29 17:26

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

--------------------------------第一次-----------------------------

数据库文件组成部分:

1.主要数据文件 *.mdf

2.次要数据文件 *.ndf

3.日志文件 *.ldf

任意一个数据库一定只能有一个主要数据文件

,可以有0个或者多个次要数据文件

至少有一个或者多个日志文件

数据存储在表中，

表存储在页中

页存储在文件中

DBMS数据库分为系统和用户

master:存放当前数据库软件中的一些系统信息(系统的表，系统的数据库...)

model:这是用户数据库的模式，系统可以参照模式创建用户数据库

msdb:这是sql servic 的另外一个服务

tempdb:存放一些临时的表，临时的信息

create database T705-- create database 数据库名

go--代表批处理：就是把go之前的所有代码看作一个整体，主要一起执行性能更好

--文件组：一个数据库值只能有一个主要文件组(Mdf一定在主要文件组中),主要文件组的性能最好

create database T820

on--默认可以省略primary

(

name=T810,--代表逻辑名

filename='c:/T810_data.mdf',--文件物理名称，一定要给后缀

size=10,--初始化大小

maxsize=50,--最大值文件大小默认是M

filegrowth=5%--文件增长方式

(

name=a,

filename='c:/T810a_data.ndf',

size=10,

maxsize=50,

filegrowth=5%

(

name=b,

filename='c:/T810b_data.ndf',

size=10,

maxsize=50,

filegrowth=5%

)

log on

(

name=T810log,

filename='c:/T810Log_data.ldf',

size=10,

maxsize=50,

filegrowth=5%

)

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

use T705

/*create table 表名

（

列名数据类型列的特性,

列名数据类型列的特性

）

1.表名允许为中文，但是不推荐

2.如果表名中间有空格,那么必须加上[名称1 名称2]

3.蓝色是关键字，红色是保留字(当前版本不是关键字，但以后可能是关键字,相当于关键字)

4.如果表名是保留字，那么也需要加上[保留字]

列的类型:

bit: 0,1，值是数字但含义是代表逻辑值(假，真)

numeric: 表述数字，可以精确到小数点后的位数

decimal: 表述数字，可以精确到小数点后的位数

numeric: 主要用于身份证,银行卡..

float,real表示的都是小数,注意没有double

dateTime: 可以精确到秒

char:一个字节

text：大文本,不能指定长度

char(100)和varchar(100)的区别:

char是固定长度字符,varchar是可变长度,varchar性能更好

unicode: 其实就是字符，他指的是用unicode编码保存的字符

unicode支持的字符大于以前的ASCII

二进字类型:

binary,varbinary,image

主要存储图像，影音文件

其他类型:

sql_variant系统提供类型，可以存放几乎所有类型的值

一个表可以没有主键

主键可以是任意类型

最多一个主键，但可以是组合主键

primary key(列名,列名)：组合主键的创建方式

主键一定不能为空值

主键可以不是标识列

一个表中最多一个标识列(建议把主键设置为标识列)

unique: 代表列是唯一值

unique：一定要有值，但可以是空值,但最多一个空值

default:设置列的默认值

列名类型 default（值）

在所有的特性里面只有identity,一张表最多只有一个，其他的特性都可以有多个

check(条件表达式)：设置列的值的范围

外键：有A,B两张表,假设B表中有一列a_id是普通列，但是这一列的值和A表的主键相关.

我们就把这一列叫做B相对于A表的外键，简称外键,那么A表是主表

,B表是子表

create table student

(

id int,

name varchar(50),

age int,

address varchar(50),

primary key(id)

)

create table mark

(

id int,

score int,

name varchar(50),

s_id int,

primary key(id)

)

-- alter table 表名 add constraint 约束名约束关键字条件

alter table student add constraint UN_name unique(name)

alter table student add constraint DF_age default(20) for age

--alter table 子表 add constraint 约束名约束关键字(外键) refences 主表（主键）

alter table mark add constraint FK_s_id foreign key(s_id)

references student(id)

select * from student

drop table student

create table t1

(

id int,

name varchar(50) unique,

age int check(age between 18 and 25),

address varchar(50) default('地址不详')

primary key(id)

)

insert into t1 values(1,'A001',20,default)

insert into t1 values(2,'A002',25,default)

insert into t1 values(2,'',30)

select * from t1

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

identity===>identity(1,1)

exists:检测函数

语法： exists(查询)

只要查询有值返回，那么整个检测函数就为真

如果是检测数据库是否存在:sysdatabases

必须先进入master数据库

如果是检测数据库是否存在: sysobjects

只需要进入当前数据库

sysdatabases只有一张,sysobjects有几个数据库就有几张

删除:

delete: 删除表中的数据,不会删除表的结构

truncate:删除表中的数据,不会删除表的结构

区别:

delete from 表名 where 条件

truncate 表名

delete删除数据很慢，会把信息记录到系统日志文件中去.

所有DBO可以通过日志把数据恢复

truncate删除数据很快，不写日志,他删除的数据是不能恢复的

drop:删除所有的结果

drop database 数据库

drop table 表名

drop constraint 约束名

drop index 索引名

drop view 视图名

数据库安全：

1.创建sql登录帐号:(只能登录软件，不能访问数据库)

exec sp_addlogin '登录名'，'密码'

2.创建数据库帐号：

exec sp_grantdbaccess '登录名','数据访问帐号'

注意：登录名==数据访问帐号,如果不相等会有很多麻烦的地方

3.设置权限

grant 权限(insert,select,update,delete,create table,all) on 表名

to 登录用户

注意：登录帐号是公共的，数据库帐号是和当前选中的数据库绑定的

exec sp_addlogin 'A002','123'

exec sp_grantdbaccess 'A002','A002'

select * from student

insert into student values(1,'t1',20,'地址不详')

grant delete on student to A002

if exists(select * from sysobjects where name='mark')

drop table mark

create table student

(

id int primary key,

name varchar(50),

password varchar(50)

)

create database T705

if exists(select * from sysdatabases where name='705')

drop database T705

select * from sysdatabases

--create database T705

--go

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

----------------------------第三次---------------------------------

sql: 结构化查询语言，是一种规范,是所有数据库软件都要遵守的规范.

sql 97

T-sql:可扩展的结构化查询语言.(定义变量,if,while,case,子查询，存储过程)

drop table Student

create table Student

(

stuNo varchar(50) primary key,

stuName varchar(50) not null,

stuSex varchar(50) not null,

stuAge int check(stuAge> 10 and stuAge<100),

stuSeat int identity,

stuAddress varchar(50) default('地址不详')

)

insert into Student values('s25301','张秋丽','男',18,'北京海淀')

insert into Student values('s25302','李文才','男',28,default)

insert into Student values('s25303','李斯文','女',22,'河南洛阳')

insert into Student values('s25304','欧阳俊雄','男',28,'新疆')

insert into Student values('s25318','梅超风','女',23,default)

insert into Student values('A001','张三疯','男',23,default)

select * from Student

select @@error

定义变量

declare @变量名类型

初始化:

set @变量名=值

select @变量名=列名 from 表名

变量分为：用户自定义和系统的

用户自定义: @名称

系统的: @@名称

用户自定义变量都是局部变量，系统提供的变量叫做全局变量

@@identity: 最新一条标识列的值

@@error:只能检测当前代码最上面一行的错误信息

select

declare @mystuSeat int

select @mystuSeat=stuSeat from Student where stuName='李文才'

select * from Student where stuSeat=@mystuSeat-1 or stuSeat=@mystuSeat+1

select * from Student

select 100 from Student

select 200

select @@identity

select * from Student

print 'A001'+convert(varchar(50),200)

select * from Student

while(条件)

begin

end

while(1=1)

begin

select * from Student

break

end

drop table mark

create table mark

(

ExamNo varchar(50) primary key,

stuNo varchar(50),

writtenExam int,

labExam int

)

alter table mark add constraint FK_stuNo foreign key(stuNo)

references Student(stuNo)

select * from student

select * from mark

insert into mark values('s271811','s25303',80,58)

insert into mark values('s271813','s25302',50,90)

insert into mark values('s271816','s25301',77,82)

insert into mark values('s271818','s25318',45,65)

declare @writtenExam int

declare @maxwrittenExam int

while(1=1)

begin

select @writtenExam=min(writtenExam) from mark

select @maxwrittenExam=max(writtenExam) from mark

if(@maxwrittenExam>100)

update mark set writtenExam=100 where writtenExam>100

if(@writtenExam<70)

begin

update mark set writtenExam=writtenExam+2

end

else

begin

break

end

select * from mark

case

when 条件 then 结果

else 结果

end

selectExamNo,

stuNo,

成绩=case

when writtenExam>=90 then 'A'

when writtenExam>=80 and writtenExam<90 then 'B'

when writtenExam>=70 and writtenExam<80 then 'C'

when writtenExam>=60 and writtenExam<70 then 'D'

else 'E'

end,

labExam from mark

selectExamNo as '考号',

stuNo as '学号',

writtenExam as '笔试成绩',

labExam as '机试成绩',

'平均分'=(writtenExam+labExam)/2,

'等级'=case

when (writtenExam+labExam)/2>=90 then '优'

when (writtenExam+labExam)/2>=80 and (writtenExam+labExam)/2<90 then '良'

when (writtenExam+labExam)/2>=70 and (writtenExam+labExam)/2<80 then '中等'

when (writtenExam+labExam)/2>=60 and (writtenExam+labExam)/2<70 then '合格'

else '不及格'

end

from mark

declare @avg int

while(1=1)

begin

select @avg=avg(labExam) from mark

if(@avg<85)

begin

update mark set labExam=case

when labExam <60 then labExam+5

when labExam between 60 and 69 then labExam+3

when labExam between 70 and 79 then labExam+2

when labExam between 80 and 89 then labExam+1

else labExam

end

else

break

end

select * from mark

@名称

set @@名称

select @名称

select 100

print 'A001'

print 'A002'+'A002'

print convert(varchar(50),100)+'A001'

if()

begin

end

else

while(1=1)

begin

break

end

case

when 条件 then 结果

else 结果

end

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

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

select * from student

declare @myAge int

select @myAge=stuAge from student where stuName='李斯文'

select * from student where stuAge>@myAge

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

子查询其实就是一个查询内部的查询，需要用(子查询)结构包含

子查询查询的列只能是表达式(>,<,=,<>)前面的列

子查询如果表达是>,<,=,<>那么查询返回的值只能是一个

如果子查询返回的值有多个，那么应该把表达式改为in

select * from student where stuAge not in

(select stuAge from student where stuAge >20)

select * from mark

一般来说，子查询和表连接能够完成相同的功能，但是又有区别

如果某一个功能能够用表连接来实现，那么一定可以用子查询来实现

如果某一个功能能够用子查询来实现，那么不一定可以用表连接来实现

子查询灵活，但表连接适合于多张表查询

select * from student where stuNo=

(select stuNo from mark where writtenExam=50 )

select * from student inner join mark

on student.stuNo=mark.stuNo

where mark.writtenExam=50

select stuName from student where stuNo in

(select stuNo from mark)

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

select * from student where stuNo not in(select stuNo from mark)

select * from mark

--exists(条件)注意：exists()中的条件只能是子查询语句，不能是比较运算 10>5

if exists(select * from mark where writtenExam>80)

begin

update mark set writtenExam=writtenExam+2

end

else

begin

update mark set writtenExam=writtenExam+5

end

select * from mark

if exists(select * from mark where writtenExam>=60 and labExam>=60)

begin

update mark set writtenExam=writtenExam+1,labExam=labExam+1

end

else

begin

update mark set writtenExam=writtenExam+3,labExam=labExam+3

end

if not exists(select * from mark where writtenExam>=60 and labExam>=60)

begin

update mark set writtenExam=writtenExam+3,labExam=labExam+3

end

else

begin

update mark set writtenExam=writtenExam+1,=labExam+1

end

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

select * from student

select * from mark

select '应到人数'=(select count(*) from student),

'实到人数'=(select count(*) from mark),

'缺考人数'=((select count(*) from student)-(select count(*) from mark))

selectstudent.stuName,

student.stuNo,

mark.writtenExam,

mark.labExam

into a2

from student left join mark

on student.stuNo=mark.stuNo

select * from a2

selectstudent.stuName,

student.stuNo,

writtenExam=case

when writtenExam is null then 0

else writtenExam

end ,

labExam=case

when labExam is null then 0

else labExam

end

into a4

from student left join mark

on student.stuNo=mark.stuNo

select * from a4

select stuName as '名称',

stuNo as '学号',

'笔试成绩'=case

when writtenExam =0 then '缺考'

else convert(varchar(50),writtenExam)

end ,

'机试成绩'=case

when labExam =0 then '缺考'

else convert(varchar(50),labExam)

end,

'是否通过'=case

when writtenExam>=60 and labExam>=60 then '通过'

else '不通过'

end

from a4

select '总人数'=(select count(*) from student),

'通过人数'=(select count(*) from mark where writtenExam>=60 and labExam>=60),

'通过率'=

convert(varchar(50),floor(

(

(select count(*) from mark where writtenExam>=60 and labExam>=60)*1.0/

(select count(*) from student)

)*100

)

)+'%'

select floor(0.23)

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

事务：简单来说就是把做任何一件事情的所有步骤看做一个整体，

这么整体要么全部正确执行

，如果其中某一布发生了异常，那么应该把所有数据全部还原。

这种处理数据的方式就是事务

CREATE TABLE bank

(

customerName CHAR(10), --顾客姓名

currentMoney MONEY --当前余额

)

ALTER TABLE bank

ADD CONSTRAINT CK_currentMoney

CHECK(currentMoney>=1)

INSERT INTO bank(customerName,currentMoney)

VALUES('张三',1000)

INSERT INTO bank(customerName,currentMoney)

VALUES('李四',1)

select * from bank

update bank set currentMoney=currentMoney-100 where customerName='李四'

update bank set currentMoney=currentMoney+100 where customerName='张三'

sql中默认认为每一条语句就是一个整体,它有自己的默认的事务管理策略

会把每一条语句当作一个事务

事务有4大特性(ACID)

1.原子性:其实就是把做一个事情的所有步骤当作一个整体，要不都成功，

要不都失败，这个整体中认识一条语句都不能单独存在

2.一致性：事务执行的时候要保证数据的相对一致

3.隔离性:在实际开发的时候，如果多人同时访问相同的数据,特别是有人查询，有人修改，

就会造成数据的不真实

4.持久性：事务完成后要保证数据库的修改是准确的真实的。

1100+1=1101

100+1001=1101

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

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

declare @myError int

set @myError=0

begin transaction

begin

update bank set currentMoney=currentMoney-100 where customerName='李四'

set @myError=@myError+@@error

update bank set currentMoney=currentMoney+100 where customerName='张三'

set @myError=@myError+@@error

if(@myError>0)

rollback transaction

else

commit transaction

end

1.定义代表语句错误的变量 set @变量名=0

2.begin transaction(如果不写数据库会使用默认的策略，会认为每一条语句是一个事务)

3.编写步骤,在每一步结束的时候设置变量的值 set @变量名=@变量名+@@error

4.判断

1.@变量名>0

rollback transaction

2.@变量名=0

commit transaction

declare @myError int

begin transaction

begin

update bank set currentMoney=currentMoney-100 where customerName='张三'

set @myError=@myError+@@error

update bank set currentMoney=currentMoney+100 where customerName='李四'

set @myError=@myError+@@error

if(@myError>0)

rollback transaction

else

commit transaction

end

索引的分类:

1.唯一索引

2.主键索引

3.聚集索引

4.非聚集索引

只要表中有主键，那么就一定是主键索引,主键索引其实就是唯一索引，

只要有主键索引就一定是聚集索引

一个表中最多只有一个聚集索引，一般用户创建的索引都是非聚集索引

索引：就是为了提供更优化的数据访问方案，提高访问数据的速度

数据库在查询的时候会自动判断哪种索引性能更好，用户不需要制定查询的时候

要选择的索引

注意：不是每一列都适合建索引,

一般在表中如果某一列经常被访问，但又不是主键，表中有大量数据

create NONCLUSTERED index Index_currentMoney

on bank(currentMoney)

select currentMoney from bank

select * from student

视图：其实就是临时创建的一张虚拟的表(并不是表，也没有表中的结构，但数据是真实的)

create view 视图名

select 语句

视图就是为了防止客户访问非法的数据

对视图的任何修改都会影响原始表

create view view_student

select stuNo,stuName from student

create view view_student2

select stuNo,stuage from student

select * from student

update view_student set stuName='杜甫' where stuName='张三疯'

select * from view_student2

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

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

第一章

瀑布模型:

1.可行性研究与分析

可行性分析文档:

2.需求分析

需求分析文档

3.设计

1.概要设计

画出E-R图

2.详细设计

建库建表

4.编码

5.测试

6.部署与维护

E-R图：实体关系图

长方形-->实体

椭圆形-->实体的属性

菱形--->代表的实体和实体之间的关系

范式:

第一范式：

1.一定有主键

2.表中每一列都是最小的不能再分的原子

第二范式：

1.满足第一范式：

2.表中的其他列都要和主键有关系

第三范式：

1.满足第二范式：

2.表中的其他列之间不能有关系

。。

实体之间的映射关系:

1.一对一: 公民身份证

2.一对多: 电视机 : 纯平，液晶

3.多对多: 老师：学生

第二章

DBMS:数据库管理软件

sql server 2005，2008

数据库: 其实就是数据库管理软件的一个区域

数据库包含3个文件:

1.主要数据文件名称.mdf

2.次要数据文件名称.ndf

3.事务日志文件名称.ldf

有且只有一个主要数据文件

有0个或者多个次要数据文件

有1个或者多个事务日志文件

sql server 2005 有2种数据库

1.系统

1.master:存放了当前DBMS软件所有的系统信息，表，函数，数据结果，数据类型

如果master坏了，那么整个DBMS不能启动

2.model:是一个模板，保存了用户自定义的数据库的模型

3.tempdb: 存放一些临时的文件,如果DBMS重启，那么他里面的数据全部清空,

每次启动DBMS的时候会以model做为模板创建一个新的数据库用来

临时保存数据

4.msdb:

2.用户自定义

create database 数据库名称[特殊字符]

go 批处理:go之前的所有语句是一个整体，DBMS编译器来性能更好

DBA:

create database 数据库名称

(

name='主要数据文件的逻辑名称',

filename='主要数据文件.mdf',

size=初始化大小,默认MB，

maxsize=文件最大值,

filegrowth=文件增长方式(10,10%)

(

name='主要数据文件的逻辑名称',

filename='次要数据文件.ndf',

size=初始化大小,默认MB，

maxsize=文件最大值,

filegrowth=文件增长方式(10,10%)

)

log on

(

name='主要数据文件的逻辑名称',

filename='事务日志文件.ldf',

size=初始化大小,默认MB，

maxsize=文件最大值,

filegrowth=文件增长方式(10,10%)

)

create table 表名

(

列名数据类型列的特性,

primary key(列名,列名)

)

数据类型:系统提供的类型

数值:

byte,bit(0,1),int,bigint,numeric,decimal,money

字符：

char,varchar,text

text:不能设置长度

日期型:

datetime

二进制：

image,binary,varbinary

浮点型：

float,real

列的特性:

非空 not null/null

标识列 identity(种子，曾量)

唯一约束 unique

默认值 default(‘值’) default(getdate())

检查约束 check(条件)

主键 primary key

外键 foreign key

identity:有默认的种子和增量(1,1)

只有int型才能设置标识列

一个表最多只有一个标识列，这一列可以不是主键

PK:

FK:

UQ:

DF:

CK:

ck_age

alter table 表名 add constraint 约束名 primary key(列名)

alter table 表名 add constraint 约束名 unique(列名)

alter table 表名 add constraint 约束名 default(值) for 列名

alter table 表名 add constraint 约束名 check(条件)

alter table 子表名 add constraint 约束名 foreign key(外键列名)

references 主表名(主键列名)

alter table 表名 drop 约束名

alter table 表名 disable constraint 约束名

alter table 表名 enable constraint 约束名

alter table 表名 add 列名数据类型 null

alter table 表名 drop column 列名

删除：

delete: 删除指定的数据，速度慢，会记录日志，可以把数据还原

delete from 表 where 条件

truncate: 删除全部的数据，速度快，不写日志，不可以数据还原

truncate 表名

drop:删除。删除特定的结构

检测函数: exists(子查询)

if exists(select * from sysdatabases where name='数据库')

必须要先进入 master，因为sysdatabases存在于master中，保存了DBMS中

所有已经存在的数据库的基本信息（名称，大小，位置....）

if exists(select * from sysobjects where name='表名')

sysobjects存在于每一个具体的数据库中，所有要进入当前数据库

权限管理：

1.创建登录帐号

exec sp_addlogin '名称'，'密码'

2.创建数据库帐号

exec sp_grantdbaccess '登录帐号'，'数据库帐号'

最好同名

3.设置数据库帐号的权限

grant 权限(select,insert,update,delete,create) on 表名

to 数据库帐号

删除权限

revoke 权限 from 数据库帐号

第三章

SQL:结构化查询语言,是一种标准或者说是规范.

T-SQL:可扩展的结果化查询语言(只能用于sql server)

变量的分类：

1.系统（全局）变量 @@名称

2.用户自定义（局部）变量 @名称

select @@identity

@@error

声明变量

declare @名称类型

变量初始化

1. set @名称=值

2. select @名称=列 from 表

输出变量的值

1.select @名称以网格方式返回结果

2.print @名称以文本返回值

print 10+10

print 'a'+'b'

print 'a'+10

convert(转换后的类型,值)

1T=1024G

1G=1024M

1M=1024K

1K=1024b(字节)

100G==100×1024

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

---------------------------第六章存储过程--------------------------

public void show(){

System.out.println("你好");

}

public void show(){

int num=20;

System.out.println("你好");

}

//模块化开发

public void show(int age){

System.out.println("我的年龄是: "+age);

}

public int show(){

System.out.println("你好");

}

存储过程：其实就是sql中的方法(函数)，只是一种结构

存储过程分类:

1.系统存储过程

sp_名称:

xp_名称:

执行存储过程

exec 存储过程名

2.用户自定义的存储过程

方法没有参数，方法内部也没有定义变量，方法没有返回值

public void show(){

语句快

}

create proc pc_name

begin

select stuAge from student where stuName='李斯文'

end

public void show(){

定义变量

语句快

}

create proc pc_t1

begin

declare @name varchar(50)

set @name='李斯文'

select * from student where stuName='李斯文'

end

public void show(String name,int age){

语句快

}

drop proc pr_t2

create proc pr_t2

@myName varchar(50)='A001',

@age int=20

begin

select * from student where stuName=@myName

end

public int show(String name){

语句快

}

create proc pr_t3

@myAge int output,

@myName varchar(50)

begin

select @myAge=stuAge from student where stuName=@myName

end

declare @age int

exec pr_t3 @age output,@myName='李斯文'

用户自定义存储总结:

1.没有参数没有返回值，语句快中没有定义局部变量的存储过程

相当于:public void show(){

语句块

}

语法:

create proc(关键字的简写) 存储过程名

语句块(查询)

exec 存储过程名

2.没有参数没有返回值，语句快定义局部变量的存储过程

相当于:public void show(){

int num=0;

String name="";

语句块

}

语法:

create proc(关键字的简写) 存储过程名

begin

declare @局部变量名类型

初始化

.....

end

exec 存储过程名

3.有参数没有返回值，语句快定义局部变量的存储过程

相当于:public void show(int age){

int num=0;

String name="";

语句块

}

语法:

create proc(关键字的简写) 存储过程名

@形参名类型=值，

。。。。。。。

begin

declare @局部变量名类型

初始化

.....

end

exec 存储过程名 @形参数名=值,........

特殊情况:

exec 存储过程名

exec 存储过程名 'A001'

4.有参数没有返回值，语句快定义局部变量的存储过程

相当于:public int show(int age){

int num=0;

String name="";

语句块

}

语法:

create proc(关键字的简写) 存储过程名

@返回值参数名类型 output,

@形参名类型=默认值，

。。。。。。。

begin

declare @局部变量名类型

初始化

.....

只要初始化输出参数

end

declare @局部变量类型

exec 存储过程名 @局部变量 output,@形参=值

print 输出

print @age

exec pr_t2 '李斯文',20

exec pr_t2 @age=30,@myName='李斯文'

exec pr_t2

exec pc_t1

exec pc_name

exec sp_databases

select * from mark

create proc pr_Mark_s1

begin

declare @num1 int

declare @num2 int

select @num1=avg(writtenExam),@num2=avg(labExam) from mark

if(@num1>70 and @num2>70)

begin

print '班上考试成绩优秀'

end

else

begin

print '班上考试成绩不好'

end

print '笔试平均分'+convert(varchar(50),@num1)

print '机试平均分'+convert(varchar(50),@num2)

print '本班成绩不好的学员名单是: '

select stuNo from mark where writtenExam<@num1 or labExam< @num2

end

create proc pr_Mark_s3

@myCount int output,

@myWrittenExam int=60,

@myLabExam int=60

begin

declare @num1 int

declare @num2 int

select @num1=avg(writtenExam),@num2=avg(labExam) from mark

if(@num1>70 and @num2>70)

begin

print '班上考试成绩优秀'

end

else

begin

print '班上考试成绩不好'

end

print '笔试平均分'+convert(varchar(50),@num1)

print '机试平均分'+convert(varchar(50),@num2)

print '本班成绩不好的学员名单是: '

select @myCount=count(*) from mark where writtenExam<@myWrittenExam or labExam< @myLabExam

end

create proc pr_Mark_s4

@myCount int output,

@myWrittenExam int=60,

@myLabExam int=60

begin

declare @num1 int

declare @num2 int

select @num1=avg(writtenExam),@num2=avg(labExam) from mark

if(not @myWrittenExam between 0 and 100 or not @myLabExam between 0 and 100)

begin

RAISERROR('你输入的数据不合法',17,5)

return

end

if(@num1>70 and @num2>70)

begin

print '班上考试成绩优秀'

end

else

begin

print '班上考试成绩不好'

end

print '笔试平均分'+convert(varchar(50),@num1)

print '机试平均分'+convert(varchar(50),@num2)

print '本班成绩不好的学员名单是: '

select @myCount=count(*) from mark where writtenExam<@myWrittenExam or labExam< @myLabExam

end

exec pr_Mark_s2 @myWrittenExam=50,@myLabExam=50

exec pr_Mark_s2 @myLabExam=50

declare @number int

exec pr_Mark_s4 @number output,@myWrittenExam=200,@myLabExam=500

print @number

create proc tt

select * from mark

exec pr_Mark_s3

存储过程的特点：

1.执行速度更快(重点): 存储过程会被看作是一个编译单元，

编译成功了保存在内存的缓冲区，那么执行的时候会自己访问缓冲区的数据，

所有速度快

2.允许模块化程序设计

3.提高系统安全性(重点)：可以设置访问级别

4.减少网络流通量

数据访问异常(650)

数据查询异常(670)

1.查询一个表出错(1)(20)

2.查询多表出错(1)(30)

3.数据库坏了( 7)(40)

数据创建异常(800)

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.

RECONFIGURE

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature.

RECONFIGURE

EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT

IF EXISTS(SELECT * FROM sysdatabases

WHERE name='bankDB')

DROP DATABASE bankDB

else

create DATABASE bankDB

select * from mark

exec pr_Mark_s1

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

-------------------------------触发器---------------------------------

触发器:其实就是一种结构，这个结构在特定的时候会被调用

注意：触发器不能被强制执行,只能在某些条件满足的时候被自动调用

触发器就是一个完整的包含事务的特殊存储过程,他内部的事务不需要开启，

不需要提交，但是可以自己控制回滚

触发器的分类:

insert触发器

update触发器

delete触发器

create tigger 触发器名称

on 表名

for (insert/update/delete)

语句快

注意：触发器不能有输入参数，

insert触发器:

不需要传递参数，如果需要值应该去查询inserted(这是一张系统提供临时表,表结构是和

触发器说绑定的表一样,里面存储刚刚准备添加的数据

一张表可以建多个触发器，

可以针对特定的功能建多个触发器

注意一旦动作执行的时候多个触发器就会被执行，

最好不要给同一个功能建多个触发器，容易引起死锁

update触发器:

)

drop table bank

drop table business

create table bank

(

name varchar(50),

card varchar(100) primary key,

currenMoney money

)

create table business

(

id int primary key identity,

currenDate datetime default(getdate()),

card varchar(100),

type varchar(50),

busMoney money

)

alter table business drop constraint FK_card

alter table bank add constraint CK_card check(currenMoney>=1)

alter table business add constraint FK_card foreign key(card)

references bank(card)

insert into bank values('张三','A001',1000)

insert into bank values('李四','A002',1)

select * from bank

select * from business

declare @myCard varchar(100)

declare @myMoney money

declare @type varchar(50)

declare @busMoey money

select @myCard=card from bank where name='张三'

select @myMoney=currenMoney from bank where card=@myCard

set @type='存款'

set @busMoey=500

if(@type='存款')

begin

update bank set currenMoney=currenMoney+@busMoey where card=@myCard

insert into business values( default,@myCard,'存款',@busMoey)

end

else

begin

if(@busMoey<@myMoney)

begin

update bank set currenMoney=currenMoney-@busMoey where card=@myCard

insert into business values( default,@myCard,'取款',@busMoey)

end

drop trigger tig_insert

create trigger tig_insert

on business

for insert

declare @myCard varchar(100)

declare @type varchar(50)

declare @busMoey money

declare @myMoney money

select @myCard=card,@type=type,@busMoey=busMoney from inserted

select @myMoney=currenMoney from bank where card=@myCard

print '触发器执行了'

if(@type='存款')

update bank set currenMoney=currenMoney+@busMoey where card=@myCard

else

begin

if(@busMoey<@myMoney)

update bank set currenMoney=currenMoney-@busMoey where card=@myCard

else

rollback transaction

end

create trigger tig_insert2

on business

for insert

declare @myCard varchar(100)

declare @type varchar(50)

declare @busMoey money

declare @myMoney money

select @myCard=card,@type=type,@busMoey=busMoney from inserted

select @myMoney=currenMoney from bank where card=@myCard

print '触发器执行了'

if(@type='存款')

update bank set currenMoney=currenMoney+@busMoey where card=@myCard

else

begin

if(@busMoey<@myMoney)

update bank set currenMoney=currenMoney-@busMoey where card=@myCard

else

rollback transaction

end

exec tig_insert

insert into business values( default,'A001','存款',1000)

insert into business values( default,'A001','取款',10000)

craete trigger tr_update

on business

for update

declare @myCard varchar(100)

declare @type varchar(50)

declare @busMoey money

declare @myMoney money

update business set busMoney=busMoney+2000 where id=7

create trigger delete_bank

on bank

for delete

declare @card varchar(50)

select @card=card from deleted

delete from business where card=@card

delete from bank where card='A001'

create trigger tr_update

on business

for update

declare @myCard varchar(100)

declare @type varchar(50)

declare @busMoey money

declare @myMoney money

select @myCard=card,@type=type from deleted

select @busMoey=busMoney from insertd

select @myMoney=currenMoney from bank where card=@myCard

if(@type='存款')

update bank set currenMoney=currenMoney+@busMoey where card=@myCard

else

if()

update business set busMoney=busMoney+1000 where id=1

select * from bank

select * from business

触发器开发步骤:

insert: 只需要查询inserted,是个临时表，一定只会保存当前准备添加的数据，

只要动作执行完，这个表中的数据就会被自动的清空

delete: 只要查询deleted表

update：这有2步骤，先删除，再增加.所有要先查询deleted再查询inserted

-------挺乱的