课堂案例.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!'
----------------------------------------------------------------------------------------------------------------------