SQLite CREATE VIEW 视图建立实例

文章作者:中山市飞娥软件工作室「Feiesoft.com」  浏览次数:4323 次  更新日期:2013-10-22

--在 SQLite 中,视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。

SQLite CREATE VIEW 视图建立实例

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

BEGIN TRANSACTION;
CREATE TABLE user(-- 会员 --
 user_id INTEGER PRIMARY KEY,--会员ID(主键)
 user_name TEXT UNIQUE,--会员姓名
 user_psw TEXT,--会员密码
 user_sex INTEGER DEFAULT 0,--会员性别
 user_phone TEXT,--会员电话
 user_addr TEXT --会员地址
);
INSERT INTO user VALUES(1001,'小赵','',0,85575390,'广州');
INSERT INTO user VALUES(1002,'小钱','',1,85575391,'深圳');
INSERT INTO user VALUES(1003,'小孙','',1,85575392,'佛山');
INSERT INTO user VALUES(1004,'小李','',1,85575393,'东莞');
INSERT INTO user VALUES(1005,'小周','',1,85575394,'中山');
INSERT INTO user VALUES(1006,'小吴','',0,85575395,'广州');
INSERT INTO user VALUES(1007,'小郑','',1,85575396,'珠海');
INSERT INTO user VALUES(1008,'小王','',1,85575397,'珠海');
INSERT INTO user VALUES(1009,'小冯','',0,85575398,'中山');
INSERT INTO user VALUES(1010,'小陈','',1,85575399,'中山');
CREATE TABLE product(-- 产品 --
 product_id INTEGER PRIMARY KEY,--产品ID(主键)
 product_name TEXT,--产品名称
 product_price DOUBLE,--产品单价
 product_num INTEGER,--产品库存量
 product_desc TEXT --产品描述
);
INSERT INTO product VALUES(8001,'剪刀',5.1,0,'剪刀说明');
INSERT INTO product VALUES(8002,'石头',1.2,0,'石头说明');
INSERT INTO product VALUES(8003,'布料',3.3,0,'布料说明');
INSERT INTO product VALUES(8004,'铅笔',1.4,0,'铅笔说明');
INSERT INTO product VALUES(8005,'可乐',6.5,0,'可乐说明');
INSERT INTO product VALUES(8006,'鸡蛋',2.6,0,'鸡蛋说明');
INSERT INTO product VALUES(8007,'耳塞',8.7,0,'耳塞说明');
INSERT INTO product VALUES(8008,'杯子',6.8,0,'杯子说明');
CREATE TABLE orders(-- 订单 --
 order_id TEXT PRIMARY KEY,--订单ID(主键)格式:YYYYMMDDNNNNC
 user_id INTEGER,--会员ID(外键)
 order_amount DOUBLE,--订单金额
 order_date DATE,--订单日期
 order_status INTEGER  DEFAULT 0 --订单状态
);
INSERT INTO orders VALUES(201310212136208338317835,1010,9.1,'2013-10-21',0);
INSERT INTO orders VALUES(201310220135227965332041,1010,6.8,'2013-10-22',0);
CREATE TABLE order_item(-- 订单项目 --
 order_item_id INTEGER PRIMARY KEY,--订单项目ID(主键)
 order_id TEXT,--订单ID(外键)
 product_id INTEGER,--产品ID(外键)
 order_item_number INTEGER,--订购数量
 order_item_status INTEGER --订单项目状态
);
INSERT INTO order_item VALUES(1,201310212136208338317835,8006,1,0);
INSERT INTO order_item VALUES(2,201310212136208338317835,8005,1,0);
INSERT INTO order_item VALUES(3,201310220135227965332041,8008,1,0);
-----------------------------------------
--
--下面建立一个统计会员个数的视图:
--
CREATE VIEW user_count AS SELECT count(user_id) FROM user;
--
--应用:SELECT * FROM user_count
--
-----------------------------------------
--下面建立一个统计零库存的视图:
CREATE VIEW product_num_zero AS SELECT * FROM product WHERE product_num = 0;
--
--应用:SELECT * FROM product_num_zero
--
-----------------------------------------
--下面建立一个统计有消费记录的会员的视图:
CREATE VIEW vip_user AS SELECT user.user_id,sum(order_amount) as total FROM user LEFT JOIN orders ON user.user_id=orders.user_id GROUP BY user.user_id HAVING count(order_id)>0 ORDER BY total DESC;
--
--应用:SELECT * FROM vip_user
--
COMMIT;