create database sheng --创建省数据库
use sheng   
create table shengming
(
 bianhao int primary key,
 shengming varchar(40) not null
)
create table chengshiming
(
  csbianhao int primary key,
  shuyusheng int foreign key references shengming(bianhao) not null,
  csming varchar(100) not null
)
insert into shengming values(1,'北京')
insert into shengming values(2,'吉林')
insert into shengming values(3,'广东')
insert into shengming values(4,'云南')
insert into shengming values(5,'西藏')
insert into chengshiming values(1,1,'北京')
insert into chengshiming values(2,2,'吉林')
insert into chengshiming values(3,2,'长春')
insert into chengshiming values(4,3,'广州')
insert into chengshiming values(5,3,'佛山')
insert into chengshiming values(6,4,'昆明')
select * from shengming
select * from chengshiming
select csming as 城市名称 from chengshiming

以下两行代码是一个嵌套查询,表名和上面不同,请注意区分

select CityName from City where ProID=(select top 1 ProID from Pro where ProName='广西'

select ProName from Pro where ProID=(select top 1 ProID from City where CityName='温州')

create database BookShop
go
use BookShop
go
create table BookCls
(
	ClsID int primary key,
	ClsName varchar(100) not null,
	CSnum int default 0,
	Smoney float default 0
)
go
insert into BookCls values(1,'教育类',0,0)
insert into BookCls values(2,'科普类',0,0)
insert into BookCls values(3,'文学类',0,0)
go
create table Book
(
	BID int not null,
	ClsID int foreign key references BookCls(ClsID) not null,
	Bname varchar(200) not null,
	Bconcern varchar(200) not null,
	author varchar(100) not null,
	BSnum int default 0,
	Bprice float,
	primary key(BID,ClsID)
)
go
insert into Book values(1,1,'高等数学','清华大学出版社','张三',0,20)
insert into Book values(2,1,'线性代数','清华大学出版社','张四',0,12)
insert into Book values(3,1,'离散数学','清华大学出版社','张五',0,32)
insert into Book values(4,1,'大学物理','清华大学出版社','张六',0,45)
insert into Book values(5,1,'大学英语','清华大学出版社','张七',0,62)

insert into Book values(1,2,'计算机基础','清华大学出版社','李三',0,30)
insert into Book values(2,2,'十万个为什么','电子工业出版社','李四',0,120)
insert into Book values(3,2,'奥秘','某某出版社','李五',0,5)
insert into Book values(4,2,'C++程序语言设计','清华大学出版社','李六',0,67)
insert into Book values(5,2,'HTML入门到精通','清华大学出版社','李七',0,98)

insert into Book values(1,3,'三国演义','某某出版社','王三',0,155)
insert into Book values(2,3,'品三国','上海文艺出版社','王四',0,23)
insert into Book values(3,3,'红楼梦','某某出版社','王五',0,180)
insert into Book values(4,3,'水浒传','某某出版社','王六',0,165)
insert into Book values(5,3,'西游记','某某出版社','王七',0,176)

update Book set BSnum=Bsnum+3 where BID=1 and ClsID=1
update BookCls set CSnum=CSnum+1 where ClsID=1
update BookCls Set Smoney=Smoney+
(select Bprice from Book where BID=1 and ClsID=1) where ClsID=1

alter table BookCls add CountNum int
select count(*) from Book group by ClsID
---------------------------------------------------------------------------

create trigger UpdateBook
on Book
for update 
as
begin
declare @BID INT
declare @ClsID INT
declare @Snum int
--declare @Dnum int
--declare @Inum int
--select @Dnum=BSnum from deleted
--select @Inum=BSnum FROM inserted
select @BID=BID, @ClsID=ClsID,
@Snum=(BSnum-(select BSnum from deleted)) FROM INSERTED
update BookCls set CSnum=CSnum+@Snum where ClsID=@ClsID
update BookCls Set Smoney=Smoney+
(@Snum*(select Bprice from inserted)) where ClsID=@ClsID
end

drop trigger UpdateBook

select * from BookCls
select * from Book

update Book set BSnum=Bsnum-5 where BID=1 and ClsID=1

update BookCls set countNum=(select count(*) 
from Book group by ClsID) where ClsID in 
(select ClsID from Book group by ClsID)
-------------------------------------------------------------------------------

create trigger InsDelBook
on Book
for insert,delete 
as
begin
declare @IClsID int
declare @DClsID int
set @IClsID=0
set @DClsID=0
select @IClsID=inserted.ClsID,@DClsID=deleted.ClsID 
from inserted,deleted
if(@IClsID=0)
begin
update BookCls set CountNum=(select count(*) from Book 
where ClsID=@DClsID) where ClsID=@DClsID
end
if(@DClsID=0)
begin
update BookCls set CountNum=(select count(*) from Book where ClsID=@IClsID)
 where ClsID=@IClsID
end
end

drop trigger InsDelBook

insert into Book values(6,1,'大学语文','某某出版社','水夜云轻',0,200)
insert into Book values(6,2,'SQL入门','某某出版社','零度冰凌',0,200)
insert into Book values(6,3,'网络淘金记','某某出版社','金满仓',0,200)

delete from Book where ClsID=2 and BID=6

select * from BookCls
-------------------------------------------------------------------------------
CREATE PROCEDURE SBook
(
	@BID int,
	@ClsID int,
    @Snum int
)
AS
update Book set BSnum=BSnum+@Snum where BID=@BID and ClsID=@ClsID
UPDATE BookCls SET CSnum =(select sum(BSnum) 
from Book where ClsID=@ClsID),Smoney=Smoney+
((select Bprice from Book where ClsID=@ClsID and BID=@BID)*@Snum) where ClsID=@ClsID
GO
return

update Book set BSnum=0 where BSnum<>0
update BookCls set CSnum=0,Smoney=0

drop PROCEDURE SBook

exec Sbook 1,2,10

EXECUTE SBook 1,3,10

select * from BookCls
select * from Book

--加   
  alter table 表名 add 新列名 数据类型(长度) 
--删   
  alter table 表名 drop column 列名   
--改
  alter table 表名 alter column 列名 数据类型(长度)