create database orderManage
on(
name = 'orderManage_data',
filename = 'D:\7_liming\JAVASRC\liming\orderManage_data.mdf',
size = 2mb,
maxsize = 5mb,
filegrowth = 15%)
log on(
name = 'orderManage_log',
filename = 'D:\7_liming\JAVASRC\liming\orderManage_log.ldf',
size = 1mb,
maxsize = 3mb,
filegrowth = 1mb)
use orderManage
alter database orderManage
modify file(
name = 'orderManage_data',
maxsize = 20mb)
alter database orderManage
modify file(
name = 'orderManage_log',
maxsize =15mb)
create table product(
productID int,
productName varchar(12),
productType varchar(20),
price money,
proNum int,
constraint pk_productID primary key(productID))
create table orderDetails(
orderID int,
productID int,
num smallint,
discount real)
create table rainmakers(
rmID int,
rmName varchar(8),
birthday datetime,
empdate datetime,
address varchar(30),
tel varchar(11),
departID int)
alter table product
add
产地 varchar(12),
供货商 varchar(10)
alter table product drop column 产地,供货商
alter table product
add
产地 varchar(20),
供货商 varchar(10)
alter table product drop column 产地,供货商
alter table orderDetails add constraint fk_orderID
foreign key(orderID) references product(productID)
create table departments(
departID int,
departName varchar(10),
departTel char(8) primary key)
create table Employees(
empId int,
empName varchar(8),
sex char(2),
departId int)
alter table rainmakers
add constraint ck_empdate
check(empdate BETWEEN 01/01/1940 AND 01/01/1995)
insert into product(productID,productName,productType,price,proNum) values(1001,'MP3播放器','三星YP-55VQ',780,200)
insert into product(productID,productName,productType,price,proNum) values(1002,'MP3播放器','爱国者P880',3670,150)
insert into product(productID,productName,productType,price,proNum) values(1003,'MP3播放器','松下MP-21V',550,2000)
insert into product(productID,productName,productType,price,proNum) values(2001,'手机','三星D418',3680,50)
insert into product(productID,productName,productType,price,proNum) values(2002,'手机','诺基亚6670',2580,90)
insert into product(productID,productName,productType,price,proNum) values(2003,'手机','诺基亚7260',1680,66)
insert into product(productID,productName,productType,price,proNum) values(2004,'手机','摩托罗拉A668',2350,89)
insert into product(productID,productName,productType,price,proNum) values(2005,'手机','摩托罗拉V171',580,45)
insert into product(productID,productName,productType,price,proNum) values(3001,'数码相机','索尼DSC-W1',1880,12)
insert into product(productID,productName,productType,price,proNum) values(3002,'数码相机','DCSmart23',520,58)
insert into product(productID,productName,productType,price,proNum) values(3003,'数码相机','索尼DSC-F828',4600,3)
update product set price = price*0.9 where productName = '手机'
update product set price = price*1.1 where productName = '手机' or productName = 'MP3播放器'
insert into product(productID,productName,productType,price,proNum) values(4001,'电脑','方正',3500,33)
insert into product(productID,productName,productType,price,proNum) values(4002,'电脑','联想',6800,21)
delete from product where productName = '电脑'
select * from product where price > 3000
select productName,productType from product where productName in ('MP3播放器','数码相机')
select count(*) as 记录的个数,count(orderID) as 订单的总数量,avg(num) as 平均销售数量, max(num) as 售数量的最大值,min(num) as 销售数量的最小值
from orderDetails
select orderID,sum(num) as 每笔订单的总数量 from orderDetails group by orderID
select productName,productType,num from product as p,orderDetails as o where p.productID = o.productID and orderID = 1202
select * from orderDetails where productID in (
select productID from product where productName = '数码相机')
declare @id int
select @id = min(productID) from product where productName = '手机'
select * from orderDetails where productID > @id
select * from orderDetails where ... = null
create view v_product_up as
select productName,productType,price from product where price > 2000
select * from v_product_up
create view cpdd_view as
select o.productID,o.num,p.productName,p.productType,p.price from orderDetails o,product p
where o.productID = p.productID and productName = '手机'
select * from cpdd_view
select o.productID,o.num,p.productName,p.productType,p.price
from orderDetails o,product p where o.productID = p.productID and productName = '手机' or productName = 'MP3播放器'
select * from cpdd_view
alter view cpdd_view as
select o.productID,o.num,p.productName,p.productType,p.price
from orderDetails o,product p where o.productID = p.productID and productName = '手机' or productName = 'MP3播放器'
create view yewu_view as
select rmName,birthday,empdate,tel,departName from rainmakers r,departments d where r.departID = d.departID
select * from yewu_view
create procedure cp_procedure as
select * from product where productName = '数码相机'
exec cp_procedure
create procedure cp_proc1 @parameter varchar(10) as
select * from product where productName = @parameter
exec cp_proc1 手机
create procedure cp_proc2
@productName varchar(10) = '手机',
@productType varchar(10) = '三星%'
as
select * from product where productName = @productName and productType like @productType
drop procedure cp_proc2
exec cp_proc2 @productType = '三星%'
create procedure cp_proc3
@productName varchar(10),
@para int output
as
select @para = count(*) from product
select * from product where productName = @productName
drop proc cp_proc3
declare @para int
exec cp_proc3 手机,@para output
print '记录条数：'+ convert(varchar(10),@para)
create trigger pro_trigg1
on product
for insert
as
print 'hehe:insert'
drop trigger pro_trigg1
insert into product(productID,productName,productType,price,proNum) values(4001,'电脑','方正',3500,33)
insert into product(productID,productName,productType,price,proNum) values(4002,'电脑','联想',6800,21)
delete from product where productName = '电脑'
create trigger pro_trigg2
on product
for update
as
print '不允许修改'
update product set price = price*8 where productID = 1001
create trigger pro_trigg3
on product
for delete
as
print 'hehe:delete'
delete from product where productName = '电脑'