Java开发网 - SQL 基础教程(原创)

Topic: SQL 基础教程(原创)

1.SQL 基础教程(原创)

Posted by: poiuytre
Posted on: 2002-12-26 10:15

/*声明*/
/*
这个例子中的许多东西是针对特定的数据库的，所以在使用的过程中，要根据所使用的
数据库进行适当的修改

在运行这个示例的时候，最好的方式是一条一条的执行SQL语句

如果要下载Interbase可以从下面的地址得到
http://www.ibphoenix.com/

这个例子应该可以在其他的数据上运行

*/

/*首先创建数据库，这里使用的是Interbase数据库，可能和其他的SQL数据库的语法有
所出入，在具体应用的时候，应该查阅具体的数据库服务器上对SQL语句的支持，下面的
许多的SQL语句都有这样的问题。*/
create database 'E:\DB\Customer.gdb' user 'younker' password 'whoami'
page_size = 2048 length = 5 pages default character set GB_2312;

/*下面创建了3个表，其中tblCustomer用来描述客户的信心，这里只是为了演示SQL语句
的相关应用，所以考虑的不是很周全，tblMerchandise描述了商场提供的货物的信心，
tblCustMerchandise表记录了一个用户在那一天购买哪一样商品的信息*/
create table tblCustomer
(
CustID integer not null primary key,
CustName char(20) not null,
CustGender char(1) default '0' not null ,
CustPhone char(20),
CustAddress varchar(100)
);

create table tblMerchandise
(
MerchandiseID integer not null primary key,
MerchandiseName varchar(50) not null,
MerchandisePrice float not null,
MerchandiseInDate date not null
);

create table tblCustMerchandise
(
CustID integer not null references tblCustomer (CustID),
MerchandiseID integer not null references tblMerchandise (MerchandiseID),
BuyCount float default 1.0 not null,
BuyDate Date not null
);

/*创建一个唯一的索引，用来保证tblCustMerchandise中数据的完整性*/
create unique index idxCustMerchandise on tblCustMerchandise
(
CustID, MerchandiseID, BuyCount, BuyDate
);

/*创建视图，这个视图的作用是存储用户在每一天购买的每种商品的花费是多少*/
create view vwCustOrder
(
CustID, CustName, MerchandiseName, BuyDate, MerchandisePrice, BuyCount, BuyMoney
)
as
select CustID, CustName, MerchandiseName, BuyDate, MerchandisePrice, BuyCount, MerchandisePrice * BuyCount
from tblCustomer, tblMerchandise, tblCustMerchandise
where tblCustomer.CustID = tblCustMerchandise.CustID
and tblMerchandise.MerchandiseID = tblCustMerchandise.MerchandiseID;

/*创建存储过程，这个存储过程的目的是查询指定的用户在某天购买商品的总的花销*/
set term !!;
create procedure procCustDayConsumption(
CustID integer, BuyDate Date)
returns (ConsumMoney float)
as
begin
select sum(BuyMoney) as ConsumMoney from vwCustOrder
where vwCustOrder.CustID = :CustID and vwCustOrder.BuyDate = :BuyDate
into :ConsumMoney;
end !!
set term ; !!

/*创建触发器，它的目的是在删除一条用户信息的时候，可以同时删除他在tblCustMerchandise
表中的相关纪录，这样维护了数据的完整性*/
set term !!;
create trigger trDeleteCustomer for tblCustomer
before delete
as
begin
delete from tblCustMerchandise where tblCustMerchandise.CustID = old.CustID;
end !!
set term ; !!

/*下面向数据库中插入试验用的数据*/
insert into tblCustomer
values (1, 'younker', '0', '13842666308', 'TaiYuanJie');

insert into tblCustomer
values (2, 'tony', '0', '130842666308', 'wangjiacun');

insert into tblCustomer
values (3, 'she', '1', '13084266608', 'unknown');

insert into tblMerchandise
values (1, 'meet', 4.65, '2002-09-01');

insert into tblMerchandise
values (2, 'tomato', 0.65, '2002-08-01');

insert into tblMerchandise
values (3, 'Potato', 0.35, '2002-08-01');

insert into tblCustMerchandise
values (1, 1, 2, "2002-8-23");

insert into tblCustMerchandise
values (1, 1, 1.5, "2002-8-24");

insert into tblCustMerchandise
values (1, 2, 1.5, "2002-8-24");

insert into tblCustMerchandise
values (1, 3, 1.5, "2002-8-23");

insert into tblCustMerchandise
values (2, 1, 1.5, "2002-8-24");

insert into tblCustMerchandise
values (2, 2, 1.8, "2002-8-22");

insert into tblCustMerchandise
values (2, 2, 1.5, "2002-8-23");

insert into tblCustMerchandise
values (3, 3, 1.5, "2002-8-23");

insert into tblCustMerchandise
values (3, 3, 1.5, "2002-8-24");

insert into tblCustMerchandise
values (3, 2, 1.5, "2002-8-24");

insert into tblCustMerchandise
values (3, 2, 1.5, "2002-8-24");

/*查询数据插入的情况*/
select * from tblCustomer;

select * from tblCustMerchandise;

/*检查视图里面的内容*/
select * from vwCustOrder;

/*执行存储过程*/
execute procedure procCustDayConsumption(1, '2002-08-23');

/*删除一条客户信息，这个过程中调用了触发器*/
delete from tblCustomer where CustID = 3;

/*查询上面的触发器之行的结果*/
select * from tblCustMerchandise;

2.Re:SQL 基础教程(原创) [Re: poiuytre]	Copy to clipboard
Posted by: george2000 Posted on: 2002-12-26 10:18 不错，不错~

3.Re:SQL 基础教程(原创) [Re: poiuytre]	Copy to clipboard
Posted by: hobohero Posted on: 2002-12-27 13:07 很好很好