sql2005数据库复习

use master go if db_id('Student') is not null drop database Student go create database Student go use Student go create table UserInfo ( userId int not null primary key identity, userName varchar(20) , ) go create table Class ( userName va

use master
go
if db_id('student') is not null
drop database student
go
create database student
go
use student
go
create table userinfo
(
userid int not null primary key identity,
username varchar(20) ,
)
go
create table class
(
username varchar(12) not null
)
go

create table UserMoney
(
moneyId int not null primary key identity,
[money] int
)
go

insert into UserMoney values(200)
insert into UserMoney values(300)
insert into UserMoney values(400)
insert into UserMoney values(500)
insert into UserMoney values(600)
insert into UserMoney values(700)
insert into UserMoney values(800)

insert into Class values('C101')
insert into Class values('C102')
insert into Class values('C103')
insert into Class values('C104')
insert into Class values('C105')

insert into userInfo values('liujie1')
insert into userInfo values('liujie2')
insert into userInfo values('liujie3')
insert into userInfo values('liujie4')
insert into userInfo values('liujie5')
insert into userInfo values('liujie6')
insert into userInfo values('liujie7')
insert into userInfo values('liujie8')
insert into userInfo values('liujie9')

select * from UserInfo

--创建索引
--判断索引是否存在
if exists (select 1 from sys.indexes where name='IX_UserId')
drop index IX_UserId on UserInfo--删除首页索引
go
--创建索引
create Index IX_UserId on UserInfo(userId)
go

--使用索引
select * from UserInfo with (index = IX_UserId)

--创建视图
--判断视图是否存在
if OBJECT_ID('v_UserInfo') is not null
drop view v_UserInfo--删除视图
go
--开始创建视图
create view v_UserInfo
as
select * from UserInfo
go
--查询视图
select * from v_UserInfo
go

--修改视图
alter view v_UserInfo
as
select * from Class
go

--查询视图
select * from v_UserInfo
go

--事务的定义。系统在执行并发操作时，最小的执行单元
--创建事务

启科网络PHP商城系统

启科网络商城系统由启科网络技术开发团队完全自主开发，使用国内最流行高效的PHP程序语言，并用小巧的MySql作为数据库服务器，并且使用Smarty引擎来分离网站程序与前端设计代码，让建立的网站可以自由制作个性化的页面。 系统使用标签作为数据调用格式，网站前台开发人员只要简单学习系统标签功能和使用方法，将标签设置在制作的HTML模板中进行对网站数据、内容、信息等的调用，即可建设出美观、个性的网站。

0

查看详情

begin transaction;
insert into UserInfo values('chaomong');
commit transaction--提交事务

select * from UserInfo
begin transaction
delete UserInfo where userName = 'chaomong'
rollback tran--回滚事务

--创建触发器for
if OBJECT_ID('tr_userMoney') is not null
drop trigger tr_userMoney
go
create trigger tr_userMoney
on UserMoney for insert
as
begin
print '添加';
end

insert into UserMoney values('101')

--添加触发器 instead of
if OBJECT_ID('tr_userMoney_1') is not null
drop trigger tr_userMoney_1
go
create trigger tr_userMoney_1
on UserMoney instead of insert
as
begin
print '添加_1';
end

insert into UserMoney values('102')

select * from UserMoney

--创建触发器for
if OBJECT_ID('tr_userMoney_2') is not null
drop trigger tr_userMoney_2
go
create trigger tr_userMoney_2
on UserMoney for Update
as
begin
begin transaction
select * from inserted
select * from deleted
commit tran
end

update UserMoney set money = money +1 where moneyId = 8

select * from UserMoney