use master--設(shè)置當(dāng)前數(shù)據(jù)庫為master,，以便訪問sysdatabases表

go

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

Drop database stuDB

go

create database stuDB

on primary--默認(rèn)就屬于primary主文件組,，可省略

(

name='stuDB_data',--主數(shù)據(jù)文件的邏輯名

filename='D:\我的文檔\SQL Server Management Studio\Projects\stuDB_data.mdf',--主數(shù)據(jù)文件的物理名

size=5mb,--主數(shù)據(jù)文件的初始大小

maxsize=100mb,--主數(shù)據(jù)文件增長的最大值

filegrowth=15%--主數(shù)據(jù)文件的增長率

)

log on

(

name='stuDB_log',

filename='D:\我的文檔\SQL Server Management Studio\Projects\stuDB_log.ldf',

size=2mb,

filegrowth=1mb

)

create table tablename

(

列名　數(shù)據(jù)類型　約束（一般為非空約束）

)

use stuDB

go

create table class

(

clsId int identity not null,

clsName varchar(20) not null

)

go

create table student

(

stuId int not null,

clsId int not null,

stuName varchar(20) not null,

stuAge int not null,

stuAddress varchar(20) not null

context varchar(20) not null

)

go

alter table tablename

add constraint 約束名　約束類型　具體的約束說明

alter table class

add constraint PK_clsId primary key (clsId)

alter table student

add constraint PK_sutId primary key(stuId)

alter table student

add constraint FK_clsId foreign key(clsId) references class(clsId)

alter table student

add constraint CK_stuAge check(stuAge between 18 and 35)

alter table student

add constraint DF_stuAddress default('地址不詳') for stuAddress

alter table student

add constraint UQ_stuName unique(stuName)

go

create table exam

(

examId int identity primary key,

stuId int foreign key references student(stuId),

exam int not null check(exam between 0 and 100)

)

go

alter table student

add context varchar(20)

alter table student

alter column context varchar(20)

alter table student

drop column context

insert into class(clsName) values('一班')

insert into class(clsName) values('二班')

insert into class(clsName) values('三班')

go

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(1,'zhangsan1',18,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(2,'zhangsan2',19,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(3,'zhangsan3',20,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(1,'zhangsan4',21,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(1,'zhangsan5',22,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(1,'zhangsan6',23,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(1,'zhangsan7',24,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(2,'zhangsan8',25,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(2,'zhangsan9',26,default,'good')

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(2,'zhangsan10',27,default,null)

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(2,'zhangsan11',28,default,null)

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(3,'zhangsan12',35,default,null)

insert into student(clsId,stuName,stuAge,stuAddress,context)

values(3,'zhangsan13',30,default,null)

go

select * into newtable from class

select * from newtable

go

insert into newtable(clsId,clsName)

select stuId,context from student

go

insert into class(clsName)

select 'aa' union

select 'aa1' union

select 'aa2' union

select 'aa'

go

select count(context),count(*) from student

select sum(stuAge) from student

select avg(stuAge) from student

select max(stuAge) from student

select min(stuAge) from student

select clsId,avg(stuAge) from student

group by clsId

order by avg(stuAge) desc

select clsId,avg(stuAge) from student

group by clsId

select clsId,avg(stuAge) from student

where stuAge >=20

group by clsId

having avg(stuAge) >= 22

order by avg(stuAge) desc

select * from student

where stuName like '%1_'

select top 5 * from student

select top 50 percent * from student

select distinct context from student

select * from student

where stuName='zhangsan1' or stuName='zhangsan2' or stuName='zhangsan3'

等價于

select * from student

where stuName in ('zhangsan1','zhangsan2','zhangsan3')

/*

一頁顯示幾條　pageSize

當(dāng)前是第幾頁　nowPage

*/

select top pageSize * from 表名

where 主鍵名

not in

(select top (nowPage-1)*pageSize 主鍵名from 表名 order by 排序規(guī)則)

order by 排序規(guī)則

select top 3 * from student

where stuId not in

(select top 6 stuId from student order by stuId)

order by stuId

select stu.stuName,cls.clsName from student as stu

inner join class as cls

on (cls.clsId = stu.clsId)

select cls.clsName,stu.stuName,exam.exam from

exam

inner join student stu

on (stu.stuId = exam.stuId)

inner join class cls

on (cls.clsId = stu.clsId)

select * from

student stu--左表

left join exam--右表

on (stu.stuId = exam.stuId)

select stu.* from

student stu

left join exam

on (stu.stuId = exam.stuId)

where exam.examId is null

select * from

exam--左表

right join student stu--右表

on (stu.stuId = exam.stuId)

select * from

exam--左表

right join student stu--右表

on (stu.stuId = exam.stuId)

left join class cls--右表

on (stu.clsId = cls.clsId)

select * from student,class

declare @age int

set @age = 12

select @age as '年齡'

print '年齡=' + cast(@age as varchar(2))

declare @age int

set @age = 12

select @age as '年齡'

print '年齡=' + convert(varchar(2),@age)

declare @age int

select @age=stuAge

from student

where stuId=13

print '年齡=' + convert(varchar(2),@age)

declare @stuId int

select @stuId = stuId from student where stuName='zhangsan7'

select * from student where stuId in (@stuId-1,@stuId+1)

select @@version

select @@identity

select @@error

select @@language

select @@rowcount

select @@servername

select @@trancount

select @@max_connections

declare @avgExam int

select @avgExam = avg(exam)

from exam

print '平均分為：' + cast(@avgExam as varchar(2))

if (@avgExam >= 70)

begin

print '成績優(yōu)秀'

select top 3 * from exam order by exam asc

end

else

begin

print '成績不好'

select top 3 * from exam order by exam desc

end

select stuId,exam,'等級'=

case

when exam <60 then 'E'

when exam between 60 and 69 then 'D'

when exam between 70 and 79 then 'C'

when exam between 80 and 89 then 'B'

else 'A'

end

from exam

declare @minExam int

declare @index int

set @index = 1

select @minExam = min(exam) from exam

while(@minExam >= 60)

begin

print '開始第' + cast(@index as varchar(2)) + '次減分'

update exam set exam = exam - 3

select @minExam = min(exam) from exam

print '第' + cast(@index as varchar(2)) + '次減分后為：' + cast(@minExam as varchar(2))

set @index = @index + 1

end

declare @avgExam int

declare @index int

set @index = 1

select @avgExam = exam from exam

while(@avgExam <= 85)

begin

print '開始第' + cast(@index as varchar(2)) + '次加分'

update exam set exam =

case

when exam >= 90 then exam

when exam between 80 and 89 then exam + 1

when exam between 70 and 79 then exam + 2

when exam between 60 and 69 then exam + 3

else exam + 4

end

select @avgExam = avg(exam) from exam

print '第' + cast(@index as varchar(2)) + '次加分后為：' + cast(@avgExam as varchar(2))

set @index = @index + 1

end

create view stuview1

as

select stuId as sid,stuName as sname from student

go

select * from stuview1

create view examview

as

select cls.clsName,stu.stuName,exam.exam from

exam

inner join student stu

on (stu.stuId = exam.stuId)

inner join class cls

on (cls.clsId = stu.clsId)

select * from examview

create view examview2

as

select a.stuId,b.stuName,a.exam from fuxidb.dbo.exam as a

inner join testdb.dbo.student as b

on (a.stuId = b.stuId)

go

declare @errorcount int

set @errorcount = 0

begin transaction

update back set currentMoney = currentMondy + 100 where cid = 1

select * from bank

set @errorcount = @errorcount + @@error

update bank set currentMoney = currentMondy - 100 where cid = 2

set @errorcount = @errorcount + @@error

if(@errorcount <> 0)

begin

print '沒有異常,，提交'

commit transaction

end

else

begin

print '發(fā)生異常，回滾'

rollback transaction

end

package com.anllin.network;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TransactionTEst

{

public static void main(String[] args) throws Exception

{

String sql1= "update back set currentMoney = currentMondy + 100 where cid = 1";

String sql2= "update bank set currentMoney = currentMondy - 100 where cid = 2";

Connection con = null;

Statement sta = null;

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=testdb","sa","");

System.out.println("autoCommit = " + con.getAutoCommit() );

con.setAutoCommit(false);

sta = con.createStatement();

sta.executeUpdate(sql1);

sta.executeQuery(sql2);

con.commit();

}

catch (Exception e)

{

con.rollback();

e.printStackTrace();

}

finally

{

sta.close();

con.close();

}

}

}

create [unique/clustered/nonclustered]

index indexName

on tableName(columnName)

[with fillfactor=x]

if exists (select name from sysindexes where name = 'IX_name')

drop index bank.IX_name

create nonclustered index IX_name

on bank(customerName)

where fillfactor=30

go

select * from bank with(index=IX_name)

where customerName = 'zhangsan'

create proc[edure] procName

@param[1] dataType = value,

...

@param[n] dataType = defualt value [output]

as

T-SQL編程,，定義變量,，流程控制等

create proc test

@a int=100,

@b int output

as

print '傳遞來的a = ' + cast(@a as varchar(4))

print '傳遞來的b = ' + cast(@b as varchar(4))

set @a = @a +10

set @b = @b + 10

print '修改后的a = ' + cast(@a as varchar(4))

print '修改后的b = ' + cast(@b as varchar(4))

go

declare @c int

declare @d int

set @c = 100

set @d = 100

execute test @a=@c,@b=@d output

print @c

print @d

go

create proc sp_transfer

@inId int,

@outId int,

@money int,

@inMoney int output,

@outMoney int output

as

update back set currentMoney = currentMondy + @money where cid = @inId

update back set currentMoney = currentMondy + @money where cid = @outId

select @inMoney = currentMoney from bank where cid = @inId

select @outMoney = currentMoney from bank where cid = @outId

go

declare @a int

declare @b int

exec sp_transfer @inId=2,@outId=1,@money=100,@inMoney=@a output,@outMoney=@b output

print @a

print @b

package com.anllin.proc;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

public class TestProc

{

public static void main(String[] args) throws Exception

{

int outId = 1;

int inId = 2;

int money = 100;

Connection con = null;

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

con = DriverManager.getConnection(

"jdbc:sqlserver://localhost:1433;databaseName=testdb",

"sa", "");

CallableStatement csta = con.prepareCall("{call sp_transfer()}");

csta.setInt(1,inId);

csta.setInt(2,outId);

csta.setInt(3,money);

csta.registerOutParameter(4,Types.INTEGER);

csta.registerOutParameter(5,Types.INTEGER);

csta.execute();

System.out.println(csta.getInt(4));

System.out.println(csta.getInt(5));

}

catch (Exception e)

{

e.printStackTrace();

}

finally

{

con.close();

}

}

}

create table bank

(

cid int identity primary key,

customerName char(10),

currentMoney money

)

go

alter table bank

add constraint CK_currentMoney

check(currentMoney >= 1)

go

insert into bank(customerName,currentMoney)

values('zhangsan',1000)

insert into bank(customerName,currentMoney)

values('lisi',1)

go

create table transInfo

(

tid int identity primary key,

cid int not null,

tdate datetime not null,

transType varchar(20) not null,

transMoney money not null

)

go

create trigger transInfoDeleteTrigger

on transInfo

for delete

as

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

select * into transInfoBackup from deleted

else

insert into transInfoBackup select * from deleted

go

delete from transInfo where tid = 1

go

select * from transInfoBackup

go

create trigger backTrigger1

on bank

for insert

as

print '新增觸發(fā)器觸發(fā)了,，看看插入的數(shù)據(jù)'

select * from inserted

go

insert into bank(customerName,currentMoney)

values('wangwu',1000)

go

create trigger backTrigger2

on bank

for update

as

select * from deleted

print '新增觸發(fā)器觸發(fā)了,，看看插入的數(shù)據(jù)'

select * from inserted

go

update bank set currentMoney = currentMoney + 150

go

修改操作

Inserted表

Deleted表

增加（insert）記錄

存放新增的記錄

----------

刪除（delete）記錄

-----------

存放被刪除的記錄

修改（update）記錄

存放更新后的記錄

存放更新前的記錄

create trigger triggerName

on tableName

[with encryption]--表示加密觸發(fā)器定義的SQL文本

for [delete,insert,update]--指定觸發(fā)器類型

as

T-SQL語句

go

create trigger transInfoTrigger

on transInfo

for insert

as

declare @type varchar(20)

declare @money money

declare @cardId int

select @type=transType,@money=transMoney,@cardId=cid from inserted

if(@type = '存')

update bank set currentMoney = currentMoney + @money where cid = @cardId

else

update bank set currentMoney = currentMoney - @money where cid = @cardId

go

insert into transInfo(cid,tdate,transType,transMoney)

values(1,getdate(),'存',100)

go

select * from bank

select * from transInfo

create trigger transInfoDeleteTrigger

on transInfo

for delete

as

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

select * into transInfoBackup from deleted

else

insert into transInfoBackup select * from deleted

go

delete from transInfo where tid = 1

go

select * from transInfoBackup

go

alter trigger bankUpdateTrigger

on bank

for update

as

declare @oldMoney money

declare @newMoney money

declare @amount money

select @newMoney=currentMoney from inserted

select @oldMoney=currentMoney from deleted

set @amount = @oldMoney - @newMoney

if(@amount>=20000 or @amount <=-20000)

begin

print '交易錯誤,，金額最多為萬'

select * from bank

rollback transaction

end

go

insert into transInfo(cid,tdate,transType,transMoney)

values(1,getdate(),'存',10000)

go

select * from bank

create trigger TG_updateTransInfo

on transInfo

for update

as

if update(tdate)

begin

print '交易失敗...'

raiserror ('安全警告：交易日期不能修改，由系統(tǒng)自動產(chǎn)生',16,1)

rollback transaction

end

go

update transInfo set tdate = getdate() where tid = 1