课堂案例.txt

主要介绍了sql的基础教程

----1.创建数据库并将当前库切换为该库
create database sampledb
use sampledb
go
----创建数据库并将当前库切换为该库
create database sampledb
go
use sampledb
----2. 修改表并引用新列
----先创建表备用
create table student
(
	stu_id int primary key,
	name varchar(20),
	age int
)
go
----修改表并引用新列
alter table student add  sex char(6)
insert into student (stu_id,name,age,sex) values (1001,'mary',20,'female')
go
----3.execute的使用
execute sp_helpdb
use pubs
go
execute的使用
use pubs
sp_helpdb
go
----4.局部变量的定义与赋值示例
use northwind
go
declare @cust varchar(5)
set @cust='frank'
select customerid,companyname
from customers
where customerid = @cust
----一次声明多个局部变量(在批中声明的局部变量的有效范围)
declare @var1 int,@var2 money
set @var1=2
select @var2=30.2
go
select @var1
select @var2
declare @date_var datetime
set @date_var = '2000/12/31'
select '@date_var'=@date_var  --赋值？查询？
----可以将一个查询的结果赋值给变量
use pubs
go
declare @date_tvar datetime
set @date_tvar = (select pubdate from titles)
select '@date_tvar'=@date_tvar
----可以将一个查询的结果赋值给变量
use pubs
go
declare @date_tvar datetime
set @date_tvar = (select min(pubdate) from titles)
select '@date_tvar'=@date_tvar
----5.打印示例
print '2005-10-27 星期五 预祝大家周末愉快！ ^_^'
----6.if……else和begin……end的例子
use pubs
go
if exists (select title_id from titles where title_id='TC5555')
begin
delete titles where title_id='TC5555'
print 'TC5555 is deleted.'
end
else
print 'TC5555 not found.'  
----7.if……else嵌套的例子
declare @var int
set @var=120
if @var>50
if @var >100
print '@var > 100'
else
print '50 < @var <=100'
else
if @var <20
print '@var <20'
else
print'20 < @var <=50'
----8.如果在titles表中找到类型为business且版税为24％的书籍，就显示“get it”，否则就显示“not found”
use pubs
go
if (select royalty from titles where type='business')=24
begin
print 'get it!'
end
else
begin
print 'not found'
end
----9.用exists实现上例的目的
use pubs
go
if exists (select title_id from titles 
where type='business' and royalty=24)
begin
print 'get it!'
end
else
begin
print 'not found'
end
----10.while语句的例子
----如果玩具的平均价格低于20，就每次增加0.2元，直到平均价格大于40为止
use GlobalToyz
go
select max(价格) from 玩具
select avg(价格) from 玩具
while (select avg(价格) from  玩具)<20
begin
update 玩具 set 价格=价格+0.2
if (select avg(价格) from 玩具)>40
break
else
continue
end
print 'too expensive!'