[原] SQLITE 一个商业的小例子.

华芸智森 2011-10-27 02:10:11
加精 




/*****************************************************************/

/* 下面的例子是 SQLITE	在商业程序中的小例子                     */

/*					陈建华                   */

/*不能按 F5 一起运行,要一段一段括起来运行,下面的 GO 只是个人习惯.*/

/*************************************************************/



GO



DROP TABLE IF EXISTS BASE_Employee;



GO



/*员工表*/

CREATE TABLE BASE_Employee(

  IntEmployeeAuID	      Int          not null, /*员工系统编号*/

  VarEmployeeID	        VarChar(32)  not null, /*员工编号*/

  VarEmployeeName	      VarChar(64)  not null, /*中文名*/

  VarHouseTel	          VarChar(32)  not null, /*家庭电话*/

  BitSex	              smallint     not null, /*性别	*/

  DTEBirthday	          datetime         null, /*出生日期*/

PRIMARY KEY (IntEmployeeAuID))



GO



/*插入测试数据*/

INSERT INTO BASE_Employee VALUES(1,'002001','张三','010-056354',1,'1978-09-23');

INSERT INTO BASE_Employee VALUES(2,'002002','李四','010-325678',1,'1972-04-02');

INSERT INTO BASE_Employee VALUES(3,'002003','老五','010-765879',1,'1971-06-21');



GO



DROP TABLE IF EXISTS BASE_Goods;



GO



/*商品表*/

CREATE TABLE BASE_Goods(

  IntGoodsAuID	      Int          not null, /*商品系统编号*/

  VarGoodsName	      VarChar(64)  not null, /*商品名称*/

  FltPrice	          Float        not null, /*价格*/

PRIMARY KEY (IntGoodsAuID))



GO



/*插入测试数据*/

INSERT INTO BASE_Goods VALUES(1,'金星21寸CTR彩电',1300);

INSERT INTO BASE_Goods VALUES(2,'神舟电脑 A560P-I7 D3',4600);

INSERT INTO BASE_Goods VALUES(3,'红双喜香烟',90);



GO



DROP TABLE IF EXISTS BUS_Order;



GO



/*销售单*/

CREATE TABLE BUS_Order(

  VarBillID	                  VarChar(32)          not null, /*单号*/

  DTEBillDate                 DateTime             not null, /*单据日期*/

  IntEmployeeAuID             Int                  not null, /*经手人*/

  VarRemark                   VarChar(255)             null,  /*备注*/

PRIMARY KEY (VarBillID))



GO



/*插入测试数据*/

INSERT INTO BUS_Order VALUES('PO-201111250001','2011-11-25',1,'');

INSERT INTO BUS_Order VALUES('PO-201111250002','2011-11-25',2,'');

INSERT INTO BUS_Order VALUES('PO-201111250003','2011-11-25',3,'');

INSERT INTO BUS_Order VALUES('PO-201111260001','2011-11-26',1,'');

INSERT INTO BUS_Order VALUES('PO-201111260002','2011-11-26',2,'');

INSERT INTO BUS_Order VALUES('PO-201111260003','2011-11-26',3,'');

INSERT INTO BUS_Order VALUES('PO-201111270001','2011-11-27',1,'');

INSERT INTO BUS_Order VALUES('PO-201111270002','2011-11-27',2,'');

INSERT INTO BUS_Order VALUES('PO-201111270003','2011-11-27',2,'');



GO



DROP TABLE IF EXISTS BUS_OrderItem;



GO



/*销售单明细*/

CREATE TABLE BUS_OrderItem(

  VarBillID	                  VarChar(32)          not null, /*单号*/

  IntGoodsAuID                Int                  not null, /*商品编号*/

  FltPrice                    Float                not null, /*单价*/  

  IntQuantity                 Int                      null, /*数量*/

  FltTotalMoney               Float                    null, /*金额*/

PRIMARY KEY (VarBillID,IntGoodsAuID))



GO



/*插入测试数据*/

INSERT INTO BUS_OrderItem VALUES('PO-201111250001',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111250001',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111250002',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111250002',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111250003',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111250003',2,4600,1,4600);





INSERT INTO BUS_OrderItem VALUES('PO-201111260001',1,1300,1,1300);

INSERT INTO BUS_OrderItem VALUES('PO-201111260001',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111260002',3,90,10,900);

INSERT INTO BUS_OrderItem VALUES('PO-201111260002',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111260003',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111260003',3,90,10,900);



INSERT INTO BUS_OrderItem VALUES('PO-201111270001',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111270001',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111270002',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111270002',2,4600,1,4600);



INSERT INTO BUS_OrderItem VALUES('PO-201111270003',1,1300,2,2600);

INSERT INTO BUS_OrderItem VALUES('PO-201111270003',2,4600,1,4600);



GO



/*********************************************************************/

/*********************************************************************/

/*********************************************************************/



/*显示销售单,把员工带出.*/

SELECT T1.*,T2.VarEmployeeName 

FROM BUS_Order AS T1 LEFT JOIN BASE_Employee AS T2 ON T1.IntEmployeeAuID=T2.IntEmployeeAuID



/*执行结果.*/

RecNo VarBillID       DTEBillDate             IntEmployeeAuID VarRemark VarEmployeeName 

----- --------------- ----------------------- --------------- --------- --------------- 

    1 PO-201111250001 2011-11-25 00:00:00.000               1           张三              

    2 PO-201111250002 2011-11-25 00:00:00.000               2           李四              

    3 PO-201111250003 2011-11-25 00:00:00.000               3           老五              

    4 PO-201111260001 2011-11-26 00:00:00.000               1           张三              

    5 PO-201111260002 2011-11-26 00:00:00.000               2           李四              

    6 PO-201111260003 2011-11-26 00:00:00.000               3           老五              

    7 PO-201111270001 2011-11-27 00:00:00.000               1           张三              

    8 PO-201111270002 2011-11-27 00:00:00.000               2           李四              

    9 PO-201111270003 2011-11-27 00:00:00.000               2           李四 



GO



/*显示明细*/

SELECT T1.*,T2.VarGoodsName

FROM BUS_OrderItem AS T1 LEFT JOIN BASE_Goods AS T2 ON T1.IntGoodsAuID=T2.IntGoodsAuID



GO



/*统计 2011 年销售金额,并按年,月,商品编号 分组显示*/

SELECT  GT1.Year

       ,GT1.Month

       ,GT1.IntGoodsAuID       

       ,GT2.VarGoodsName

       ,SUM(GT1.FltTotalMoney) AS FltTotalMoneySUM 

FROM (

     SELECT 

        strftime('%Y',T1.DTEBillDate) as Year

       ,strftime('%m',T1.DTEBillDate) as Month       

       ,T2.IntGoodsAuID  

       ,T2.FltTotalMoney   

     FROM BUS_Order AS T1 LEFT JOIN BUS_OrderItem AS T2 ON T1.[VarBillID]=T2.VarBillID

     WHERE T1.DTEBillDate>='2011-01-01' AND T1.DTEBillDate<'2012-1-1 00:00:00' ) AS GT1 

LEFT JOIN BASE_Goods AS GT2 ON GT1.[IntGoodsAuID]=GT2.IntGoodsAuID

GROUP BY GT1.Year,GT1.Month,GT1.IntGoodsAuID,GT2.VarGoodsName;



/*执行结果*/

RecNo GT1.Year GT1.Month GT1.IntGoodsAuID GT2.VarGoodsName FltTotalMoneySUM 

----- -------- --------- ---------------- ---------------- ---------------- 

    1 2011     11                       1 金星21寸CTR彩电       19500            

    2 2011     11                       2 神舟电脑 A560P-I7 D3 36800            

    3 2011     11                       3 红双喜香烟            1800  



GO



/*统计2011年销售金额最高的2款产品*/

SELECT  GT1.IntGoodsAuID

       ,GT2.VarGoodsName

       ,GT1.FltTotalMoneySUM

FROM (

     SELECT  T2.IntGoodsAuID  

            ,SUM(T2.FltTotalMoney) AS FltTotalMoneySUM

     FROM BUS_Order AS T1 LEFT JOIN BUS_OrderItem AS T2 ON T1.[VarBillID]=T2.VarBillID

     WHERE T1.DTEBillDate>='2011-01-01' AND T1.DTEBillDate<'2012-1-1 00:00:00'

     GROUP BY T2.IntGoodsAuID

     ) AS GT1 LEFT JOIN BASE_Goods AS GT2 ON GT1.[IntGoodsAuID]=GT2.IntGoodsAuID

ORDER BY FltTotalMoneySUM DESC LIMIT 0,2;



/*执行结果*/

RecNo IntGoodsAuID VarGoodsName     FltTotalMoneySUM 

----- ------------ ---------------- ---------------- 

    1            2 神舟电脑 A560P-I7 D3 36800            

    2            1 金星21寸CTR彩电       19500            



GO



/*统计2011年各商品的贡献度*/



SELECT  GT1.IntGoodsAuID

       ,GT2.VarGoodsName

       ,GT1.FltTotalMoneySUM/(SELECT SUM(TA2.FltTotalMoney) FROM BUS_Order AS Ta1 LEFT JOIN BUS_OrderItem AS Ta2 ON Ta1.[VarBillID]=Ta2.VarBillID WHERE TA1.DTEBillDate>='2011-01-01' AND TA1.DTEBillDate<'2012-1-1 00:00:00') FltProductContribution

FROM (

     SELECT  T2.IntGoodsAuID  

            ,SUM(T2.FltTotalMoney) AS FltTotalMoneySUM         

     FROM BUS_Order AS T1 LEFT JOIN BUS_OrderItem AS T2 ON T1.[VarBillID]=T2.VarBillID

     WHERE T1.DTEBillDate>='2011-01-01' AND T1.DTEBillDate<'2012-1-1 00:00:00'

     GROUP BY T2.IntGoodsAuID

     ) AS GT1 LEFT JOIN BASE_Goods AS GT2 ON GT1.[IntGoodsAuID]=GT2.IntGoodsAuID     

GROUP BY GT1.IntGoodsAuID 

ORDER BY FltTotalMoneySUM;



/*执行结果*/

RecNo IntGoodsAuID VarGoodsName     FltProductContribution 

----- ------------ ---------------- ---------------------- 

    1            3 红双喜香烟            0.0309810671256454     

    2            1 金星21寸CTR彩电       0.335628227194492      

    3            2 神舟电脑 A560P-I7 D3 0.633390705679862      







GO



-- 用中间表保存结果统计.



DROP TABLE IF EXISTS Tempms;



CREATE TEMP TABLE Tempms(

  FltTotalMoneyAll Float NULL

);



INSERT INTO Tempms SELECT SUM(TA2.FltTotalMoney) FROM BUS_Order AS Ta1 LEFT JOIN BUS_OrderItem AS Ta2 ON Ta1.[VarBillID]=Ta2.VarBillID WHERE TA1.DTEBillDate>='2011-01-01' AND TA1.DTEBillDate<'2012-1-1 00:00:00';



SELECT  GT1.IntGoodsAuID

       ,GT2.VarGoodsName

       ,GT1.FltTotalMoneySUM/( SELECT FltTotalMoneyAll FROM Tempms ) AS FltProductContribution

FROM (

     SELECT  T2.IntGoodsAuID  

            ,SUM(T2.FltTotalMoney) AS FltTotalMoneySUM         

     FROM BUS_Order AS T1 LEFT JOIN BUS_OrderItem AS T2 ON T1.[VarBillID]=T2.VarBillID

     WHERE T1.DTEBillDate>='2011-01-01' AND T1.DTEBillDate<'2012-1-1 00:00:00'

     GROUP BY T2.IntGoodsAuID

     ) AS GT1 LEFT JOIN BASE_Goods AS GT2 ON GT1.[IntGoodsAuID]=GT2.IntGoodsAuID     

GROUP BY GT1.IntGoodsAuID 

ORDER BY FltTotalMoneySUM;



DROP TABLE IF EXISTS Tempms;
...全文
1161 33 打赏 收藏 转发到动态 举报
写回复
用AI写文章
33 条回复
切换为时间正序
请发表友善的回复…
发表回复
hyl3976 2012-05-30
  • 打赏
  • 举报
 回复
「已注销」 2012-01-19
  • 打赏
  • 举报
 回复
楼主标题党。
1 商业应用，没有说并发问题，没有说事物，没有说文件锁。
2 数据表字段过于简单，也许是为了初学者，但基本的create_time,creator之类的并没有。习惯不好。
3 数据类型，商业应用一般尽量减少浮点类型的使用，多使用整型(比如单位为分)。
4 数据表、字段命名一般不使用大写，而以下划线分割。(当然这个不是唯一，但首字母大写+下划线，这是不存在的规范)。命名中不要带数据类型，这样不便于以后的修改。
5 电子商务应用或者说数据仓库，一般只有2,3个数据类型，int，varchar，(text)。不指定各数据格式，由UI指定。
6 自增主键，流水号是一个好习惯。
7 insert中指定字段是一个好习惯。
8 select中的left join 应尽量替换成inner join。就像 for(int i=0; i!=N; ++i)与for(int i=0; i<N; ++i)的性质类似。如果出现inner join和left join获得结果不一致，那么说明程序不完整或数据错误。应该修改程序或者修复数据，而不是程序中适应错误。
9 慎用子查询，慎用临时表。
10 商用数据库一般增加有限冗余字段以减少实时的统计数据。
11 sql的优化，要用explain之类的去分析（如果不熟悉sqlite下的explain结果，可以用你熟悉的数据库去分析）sqlite对标准的支持还算可以。

今天无聊打酱油，发发牢骚。
叶子 2012-01-12
  • 打赏
  • 举报
 回复
谢谢分享，路过学习！
melos 2011-12-03
  • 打赏
  • 举报
 回复
先做个记号，谢谢楼主分享！
lzfstruggle 2011-11-11
  • 打赏
  • 举报
 回复
学习学习。。
这不是鸭头 2011-11-02
  • 打赏
  • 举报
 回复
不错，学习...
ysyabc 2011-11-01
  • 打赏
  • 举报
 回复
正在学习sql,感谢分享！
zhu6100441 2011-11-01
  • 打赏
  • 举报
 回复
sqlite的资料好少，感谢楼主分享。
coco915 2011-11-01
  • 打赏
  • 举报
 回复
不错 学习
sy_shu 2011-11-01
  • 打赏
  • 举报
 回复
学习了！
izard999 2011-10-31
  • 打赏
  • 举报
 回复
学习一下，谢谢！
天才在左疯子在右 2011-10-31
  • 打赏
  • 举报
 回复
学习一下
gggkcb 2011-10-30
  • 打赏
  • 举报
 回复
不错 感谢楼主分享
vanxining 2011-10-29
  • 打赏
  • 举报
 回复
这个真的是仔细研究啊~
Sududdao 2011-10-29
  • 打赏
  • 举报
 回复
谢谢，太有用了
leechiyang 2011-10-28
  • 打赏
  • 举报
 回复
不错 感谢楼主分享
lulusophia 2011-10-28
  • 打赏
  • 举报
 回复
刚刚看sqlite,学习了！感激楼主！
nettman 2011-10-28
  • 打赏
  • 举报
 回复
感谢楼主分享，学习！
BestTim 2011-10-28
  • 打赏
  • 举报
 回复
不错不错。没有深入研究过sqlite，原来也可以用些高级的语法。学习了
northcan 2011-10-28
  • 打赏
  • 举报
 回复
正在WinCE嵌入式系统上的应用项目中使用Sqlite
非常小巧精美的数据库
感谢楼主分享
加载更多回复（4）
新版Android开发教程.rar
Android 是一个专门针对移动设备的软件集，它包括一个操作系统，中间件和一些重要的应用程序。 Beta 版 的 Android SDK 提供了在 Android 平台上使用 JaVa 语言进行 Android 应用开发必须的工具和 API 接口。 特性 ...
android开发资料大全
Android自定义View研究-- 一个小Demo Android调用相册拍照实现系统控件缩放切割图片 Android SQLite的实例汇总大全 两分钟彻底让你明白Android Activity生命周期(图文)! Android 图形系统剖析 Android 立体效果图片...
SQLite数据库逆向分析
SQLite数据库被加密了，怎么办？... 掌握这个技能，只要它是SQLite数据库，只要它可以被“专有”的程序访问，无论它采取了多么强大的加密方式。在你的面前，它只是一个普通的SQLite数据库而已，里面的数据任你摆布！
asp.net知识库
.NET关于string转换的一个小Bug Regular Expressions 完整的在.net后台执行javascript脚本集合 ASP.NET 中的正则表达式 常用的匹配正则表达式和实例 经典正则表达式 delegate vs. event 我是谁？[C#] 表达式计算引擎...
[原创]c/c++在vc6，vs（visual studio2010），codeblocks，wxDev c++中应用sqlite3 API笔记
目前较新的版本是3.7.6.3，在sqlite的源码中(sqlite-amalgamation-3070603)包含4个文件：shell.c, sqlite3.c,sqlite3.h,sqlite3ext.h。已编译好的sqlite包含2个文件：sqlite3.dll和sqlite3.def。如果只是为了使用...
其他数据库

2,209

社区成员

9,519

社区内容

发帖
与我相关
我的任务
社区描述
其他数据库开发 其他数据库
社区管理员
  • 其他数据库社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章