2014-12-19作业

【JSU】LJDragon's Oracle course tasks In the first semester, junior year

--1.在管理员权限下创建一个新用户，以你的名字命名，密码自定义

1. CREATE USER LJL IDENTIFIED BY LJL;
2. ALTER USER LJL IDENTIFIED BY LJL;
3. ALTER USER LJL ACCOUNT UNLOCK;
4. GRANT CONNECT,RESOURCE TO LJL;
5. GRANT CREATE VIEW TO LJL;
6. GRANT CREATE SESSION TO LJL;

--2.赋予新用户，连接，创建表，操作表等权限

1. GRANT ALL ON scott.emp TO LJL;
2. select * from dba_role_privs;
3. select * from role_sys_privs;

--3.用新的用户连接数据库，用DDL语句创建以下表(字段名，数据类型自定义,但是要符合数据需求)：

--1）客户信息表（ID,用户名，密码，邮箱，手机号码，地址）

1. CREATE TABLE customer
2.    (
3.    cus_id NUMBER(6),
4.    cus_name VARCHAR2(20),
5.    cus_password VARCHAR(50),
6.    cus_email VARCHAR(50),
7.    cus_phone NUMBER(20),
8.    cus_address VARCHAR2(50)
9.    );
10.    select * from customer;
11.    --drop table customer;

--2）产品表（ID,产品名称，产品类别，尺寸，颜色，产地，价格）

1. CREATE TABLE product
2.     (
3.     pro_id NUMBER(6),
4.     pro_name VARCHAR2(50),
5.     pro_category VARCHAR2(50),
6.     pro_size CHAR(10),
7.     pro_color CHAR(10),
8.     pro_orgin VARCHAR2(50),
9.     pro_price NUMBER(6)
10.     );
11.     select * from product;

--3）订单表（ID,订单编号，用户id，订单状态，支付状态，订单总额，创建时间）

1. CREATE TABLE orders
2. (
3. ord_id NUMBER(6),
4. cus_id NUMBER(6),
5. ord_status CHAR(2),
6. pay_status CHAR(2),
7. ord_money NUMBER(6),
8. ord_createtime DATE
9. );
10. select * from orders;

--4）订单明细表（ID，订单ID，产品ID，数量，金额，合计，创建时间，创建人）

1. CREATE TABLE orderitems
2. (
3. ordit_id NUMBER(6),
4. ord_id NUMBER(6),
5. pro_id NUMBER(6),
6. pro_num NUMBER(10),
7. pro_price NUMBER(6),
8. ordit_money NUMBER(6),
9. ordit_createtime DATE,
10. ordit_creator VARCHAR2(20)
11. );
12. select * from orderitems;

--4.给每个表加一个主键约束

1. ALTER TABLE customer
2.   ADD CONSTRAINTS pk_customer_cusid PRIMARY KEY (cus_id);
3.  
4.   ALTER TABLE product
5.   ADD CONSTRAINTS pk_product_proid PRIMARY KEY (pro_id);
6.  
7.   ALTER TABLE orders
8.   ADD CONSTRAINTS pk_orders_ordid PRIMARY KEY (ord_id);
9.  
10.   ALTER TABLE orderitems
11.   ADD CONSTRAINTS pk_orderitems_cusid PRIMARY KEY (ordit_id);

--5.给客户表加一个邮箱的检查约束(邮箱中有@)

1. ALTER TABLE customer
2. ADD CONSTRAINTS cus_email_chk CHECK (cus_email like '%@%');

--6.给产品表的价格加一个不能是负数的检查约束

1. ALTER TABLE product
2.  ADD CONSTRAINTS pro_price_chk CHECK (pro_price>0);

--7.给订单表加一个外键约束

1.     alter table orders
2.     add constraint ord_cusid_fk foreign key(cus_id)
3.     references customer (cus_id)
4.     on delete set null;--删除的时候子表外键字段置空
5. --on delete cascade;--联级删除

--8.给订单明细表加两个外键约束

1. alter table orderitems
2. add constraint ordit_cusid_fk foreign key(ord_id)
3. references orders (ord_id)
4. on delete set null;--删除的时候子表外键字段置空
5.  
6. alter table orderitems
7. add constraint ordit_proid_fk foreign key(pro_id)
8. references product (pro_id)
9. on delete set null;--删除的时候子表外键字段置空

--9.创建一个四个序列，分别对应，客户表，产品表，订单表，订单明细表的主键

1. CREATE SEQUENCE cus_seq;
2. CREATE SEQUENCE pro_seq;
3. CREATE SEQUENCE ord_seq;
4. CREATE SEQUENCE ordit_seq;

--10.插入客户表几条测试数据（主键引用上面对应的序列值）

1. INSERT INTO customer VALUES (cus_seq.nextval,'','qq@email',12345677,'中国');
2. INSERT INTO customer VALUES (cus_seq.nextval,'','qq@email',12345677,'美国');
3. INSERT INTO customer VALUES (cus_seq.nextval,'','qq@email',12345677,'加拿大');
4. INSERT INTO customer VALUES (cus_seq.nextval,'','qq@email',12345677,'英国');

--11.插入产品表几条测试数据（主键引用上面对应的序列值）

1. INSERT INTO product VALUES (pro_seq.nextval,'电脑','笔记本','14寸','黑色','中国',5000);
2. INSERT INTO product VALUES (pro_seq.nextval,'Iphone','手机','5.5寸','玫瑰金','美国',6320);
3. INSERT INTO product VALUES (pro_seq.nextval,'Ipad','平板','9寸','白色','中国',3500);
4. INSERT INTO product VALUES (pro_seq.nextval,'Itouch','手表','3.5寸','黑色','中国',250);

--12.插入订单表几条测试数据（主键引用上面对应的序列值）

1. INSERT INTO orders VALUES (ord_seq.nextval,2,'','',5000,sysdate);
2. INSERT INTO orders VALUES (ord_seq.nextval,3,'','',6320,sysdate);
3. INSERT INTO orders VALUES (ord_seq.nextval,4,'','',3500,sysdate);
4. INSERT INTO orders VALUES (ord_seq.nextval,5,'','',250,sysdate);
5.  select * from orders;

--13.插入订单明细表几条测试数据（主键引用上面对应的序列值）

1. INSERT INTO orderitems VALUES (ordit_seq.nextval,3,2,1,5000,5000,sysdate,'tom');
2. INSERT INTO orderitems VALUES (ordit_seq.nextval,4,3,1,5000,5000,sysdate,'tom');
3. INSERT INTO orderitems VALUES (ordit_seq.nextval,5,4,1,5000,5000,sysdate,'tom');
4. INSERT INTO orderitems VALUES (ordit_seq.nextval,6,5,1,5000,5000,sysdate,'tom');

--14.订单表是张业务量比较大的表，随着时间的推移，数据会越来越多，用户会经常用到用户id，和订单状态进行查选，现在需要优化查询速度，加一个索引

1. create index index_ord on orders(cus_id,ord_status);

--15.当索引都不能满足性能要求的时候，需要拆分成当前表和历史表，把今年年初之前的所有数据全部迁移到历史表中

--并在把当前表年初之前的数据删除掉

1. --这个没看懂
2.  CREATE TABLE ordersPAR
3.     (
4.     ord_id NUMBER(6),
5.     cus_id NUMBER(6),
6.     ord_status CHAR(2),
7.     pay_status CHAR(2),
8.     ord_money NUMBER(6),
9.     ord_createtime DATE
10.     )
11.     PARTITION BY RANGE (ord_createtime)
12.    (PARTITION historic VALUES LESS THAN (TO_DATE('2016-1-1', 'YYYY-MM-DD')),
13.     PARTITION presently VALUES LESS THAN (TO_DATE('2016-12-31', 'YYYY-MM-DD')))

--16.现在由于订单表分了当前表和历史表，但是用户想查两个表的数据，写一个集合语句查两个表中的数据

1. select * from historic UNION select * from presently;

--17.用最高权限的管理员账号，授予SCOTT账号可以访问你刚刚建的用户下,客户信息表所有增删改查权限

1. GRANT ALL ON LJL.customer TO scott;