视图

有一个顾客商品关系数据库，有三个基本表，表结构如下：
 
1. 请根据三个基表的信息添加测试数据
2. 请用SQL语言创建一个视图 GM_VIEW，
检索顾客的顾客号、顾客名和订购商品的商品名、金额和日期。
(金额等于数量*购买价)
3. 请为Customer表的顾客编号添加唯一索引
4. 请为OrderItem表的顾客号和商品号添加组合索引


--grant create any view to scott  添加视图权限
CREATE VIEW GM_VIEW AS 
SELECT DISTINCT C.CUSNO 顾客号 , C.CNAME 顾客名 , A.ANAME 商品名 ,O.PRICE*O.NUM 金额 , o.buytime 日期
FROM CUSTOMER C , Article A , ORDERITEM o;

select * from GM_VIEW

create index index_cusno on CUSTOMER('CUSNO');
ALTER TABLE CUSTOMER
ADD CONSTRAINT UN_CUS_NO UNIQUE (CUSNO);

-- 商品表：Article?(商品号，商品名，单价，库存量) 
--DROP TABLE IF EXISTS 'ARTICLE';
CREATE TABLE Article(
       ArtNO number(4) CONSTRAINT PK_ART_NO PRIMARY KEY,
       ANAME VARCHAR2(20) CONSTRAINT NN_ART_NAME NOT NULL,
       PRICE NUMBER(10) ,
       SOCK NUMBER(5) 
);
INSERT INTO ARTICLE VALUES(1000,' 高露洁',12,100);
INSERT INTO ARTICLE VALUES(1001,' 飘柔',52,100);
INSERT INTO ARTICLE VALUES(1002,' 德克士',92,100);

 --客户表：Customer?(顾客号，顾客名，性别，年龄，电话)
CREATE TABLE CUSTOMER(
       CUSNO NUMBER(4) CONSTRAINT PK_CUS_NO PRIMARY KEY,
       CNAME VARCHAR2(10) CONSTRAINT NN_CUS_NAME NOT NULL,
       SEX VARCHAR2(3) DEFAULT '男',
       AGE NUMBER(3) ,
       PHONE VARCHAR2(15)
);
INSERT INTO CUSTOMER VALUES(2001,'SMITH','',23,13289682623);
INSERT INTO CUSTOMER VALUES(2002,'JACK','',34,13879882623);


 -- 订单表：OrderItem?(顾客号，商品号，数量，购买价，日期)
 DROP TABLE ORDERITEM
 CREATE TABLE ORDERITEM (
        ORDERNO NUMBER(4) CONSTRAINT PK_ORDER_NO PRIMARY KEY,
        CUSNO NUMBER(4),
        ARTNO NUMBER(4),
        NUM NUMBER(4),
        PRICE NUMBER(10) ,
        BUYTIME DATE
 );

INSERT INTO ORDERITEM VALUES(9000,2001,1001,2,12,sysdate);
INSERT INTO ORDERITEM VALUES(9001,2001,1002,2,12,sysdate);
INSERT INTO ORDERITEM VALUES(9002,2002,1003,2,12,sysdate);




INSERT INTO ORDERITEM 
VALUES(4,2001,1003,2,12,TO_DATE('2014-02-14 20:47:00','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO ORDERITEM 
VALUES(5,2001,1003,2,12,TO_DATE('2018-02-14 20:47:00','yyyy-mm-dd hh24:mi:ss'));


SELECT * FROM ORDERITEM
--插入 date 值
insert into tabname(datecol) value(sysdate) ;  -- 用date值
insert into tabname(datecol) value(sysdate+1) ;  -- 用date值
insert into tabname(datecol) value(to_date('2014-02-14','yyyy-mm-dd')) ;  -- 用to_date
insert into tabname(datecol) value(to_date('2014-02-14 20:47:00','yyyy-mm-dd hh24:mi:ss')) ;  -- 用to_date
insert into tabname(datecol) value(to_date('20140214','yyyymmdd')) ;  -- 用to_date
insert into tabname(datecol) value(to_date('20140214204700','yyyymmddhh24miss')) ;  -- 用to_date