首页 > 代码库 > Oracle_Q&A_04
Oracle_Q&A_04
2014-12-19作业
--1.在管理员权限下创建一个新用户,以你的名字命名,密码自定义
- CREATE USER LJL IDENTIFIED BY LJL;
- ALTER USER LJL IDENTIFIED BY LJL;
- ALTER USER LJL ACCOUNT UNLOCK;
- GRANT CONNECT,RESOURCE TO LJL;
- GRANT CREATE VIEW TO LJL;
- GRANT CREATE SESSION TO LJL;
--2.赋予新用户,连接,创建表,操作表等权限
- GRANT ALL ON scott.emp TO LJL;
- select * from dba_role_privs;
- select * from role_sys_privs;
--3.用新的用户连接数据库,用DDL语句创建以下表(字段名,数据类型自定义,但是要符合数据需求):
--1)客户信息表(ID,用户名,密码,邮箱,手机号码,地址)
- CREATE TABLE customer
- ???(
- ???cus_id NUMBER(6),
- ???cus_name VARCHAR2(20),
- ???cus_password VARCHAR(50),
- ???cus_email VARCHAR(50),
- ???cus_phone NUMBER(20),
- ???cus_address VARCHAR2(50)
- ???);
- ???select * from customer;
- ???--drop table customer;
--2)产品表(ID,产品名称,产品类别,尺寸,颜色,产地,价格)
- CREATE TABLE product
- ????(
- ????pro_id NUMBER(6),
- ????pro_name VARCHAR2(50),
- ????pro_category VARCHAR2(50),
- ????pro_size CHAR(10),
- ????pro_color CHAR(10),
- ????pro_orgin VARCHAR2(50),
- ????pro_price NUMBER(6)
- ????);
- ????select * from product;
--3)订单表(ID,订单编号,用户id,订单状态,支付状态,订单总额,创建时间)
- CREATE TABLE orders
- (
- ord_id NUMBER(6),
- cus_id NUMBER(6),
- ord_status CHAR(2),
- pay_status CHAR(2),
- ord_money NUMBER(6),
- ord_createtime DATE
- );
- select * from orders;
--4)订单明细表(ID,订单ID,产品ID,数量,金额,合计,创建时间,创建人)
- CREATE TABLE orderitems
- (
- ordit_id NUMBER(6),
- ord_id NUMBER(6),
- pro_id NUMBER(6),
- pro_num NUMBER(10),
- pro_price NUMBER(6),
- ordit_money NUMBER(6),
- ordit_createtime DATE,
- ordit_creator VARCHAR2(20)
- );
- select * from orderitems;
--4.给每个表加一个主键约束
- ALTER TABLE customer
- ??ADD CONSTRAINTS pk_customer_cusid PRIMARY KEY (cus_id);
- ?
- ??ALTER TABLE product
- ??ADD CONSTRAINTS pk_product_proid PRIMARY KEY (pro_id);
- ?
- ??ALTER TABLE orders
- ??ADD CONSTRAINTS pk_orders_ordid PRIMARY KEY (ord_id);
- ?
- ??ALTER TABLE orderitems
- ??ADD CONSTRAINTS pk_orderitems_cusid PRIMARY KEY (ordit_id);
--5.给客户表加一个邮箱的检查约束(邮箱中有@)
- ALTER TABLE customer
- ADD CONSTRAINTS cus_email_chk CHECK (cus_email like ‘%@%‘);
?
--6.给产品表的价格加一个不能是负数的检查约束
- ALTER TABLE product
- ?ADD CONSTRAINTS pro_price_chk CHECK (pro_price>0);
--7.给订单表加一个外键约束
- ????alter table orders
- ????add constraint ord_cusid_fk foreign key(cus_id)
- ????references customer (cus_id)
- ????on delete set null;--删除的时候子表外键字段置空
- --on delete cascade;--联级删除
--8.给订单明细表加两个外键约束
- alter table orderitems
- add constraint ordit_cusid_fk foreign key(ord_id)
- references orders (ord_id)
- on delete set null;--删除的时候子表外键字段置空
- ?
- alter table orderitems
- add constraint ordit_proid_fk foreign key(pro_id)
- references product (pro_id)
- on delete set null;--删除的时候子表外键字段置空
--9.创建一个四个序列,分别对应,客户表,产品表,订单表,订单明细表的主键
- CREATE SEQUENCE cus_seq;
- CREATE SEQUENCE pro_seq;
- CREATE SEQUENCE ord_seq;
- CREATE SEQUENCE ordit_seq;
--10.插入客户表几条测试数据(主键引用上面对应的序列值)
- INSERT INTO customer VALUES (cus_seq.nextval,‘tom‘,‘1234‘,‘qq@email‘,12345677,‘中国‘);
- INSERT INTO customer VALUES (cus_seq.nextval,‘Alen‘,‘1234‘,‘qq@email‘,12345677,‘美国‘);
- INSERT INTO customer VALUES (cus_seq.nextval,‘Jack‘,‘1234‘,‘qq@email‘,12345677,‘加拿大‘);
- INSERT INTO customer VALUES (cus_seq.nextval,‘Mack‘,‘1234‘,‘qq@email‘,12345677,‘英国‘);
--11.插入产品表几条测试数据(主键引用上面对应的序列值)
- INSERT INTO product VALUES (pro_seq.nextval,‘电脑‘,‘笔记本‘,‘14寸‘,‘黑色‘,‘中国‘,5000);
- INSERT INTO product VALUES (pro_seq.nextval,‘Iphone‘,‘手机‘,‘5.5寸‘,‘玫瑰金‘,‘美国‘,6320);
- INSERT INTO product VALUES (pro_seq.nextval,‘Ipad‘,‘平板‘,‘9寸‘,‘白色‘,‘中国‘,3500);
- INSERT INTO product VALUES (pro_seq.nextval,‘Itouch‘,‘手表‘,‘3.5寸‘,‘黑色‘,‘中国‘,250);
--12.插入订单表几条测试数据(主键引用上面对应的序列值)
- INSERT INTO orders VALUES (ord_seq.nextval,2,‘1‘,‘0‘,5000,sysdate);
- INSERT INTO orders VALUES (ord_seq.nextval,3,‘2‘,‘1‘,6320,sysdate);
- INSERT INTO orders VALUES (ord_seq.nextval,4,‘0‘,‘3‘,3500,sysdate);
- INSERT INTO orders VALUES (ord_seq.nextval,5,‘3‘,‘2‘,250,sysdate);
- ?select * from orders;
--13.插入订单明细表几条测试数据(主键引用上面对应的序列值)
- INSERT INTO orderitems VALUES (ordit_seq.nextval,3,2,1,5000,5000,sysdate,‘tom‘);
- INSERT INTO orderitems VALUES (ordit_seq.nextval,4,3,1,5000,5000,sysdate,‘tom‘);
- INSERT INTO orderitems VALUES (ordit_seq.nextval,5,4,1,5000,5000,sysdate,‘tom‘);
- INSERT INTO orderitems VALUES (ordit_seq.nextval,6,5,1,5000,5000,sysdate,‘tom‘);
--14.订单表是张业务量比较大的表,随着时间的推移,数据会越来越多,用户会经常用到用户id,和订单状态进行查选,现在需要优化查询速度,加一个索引
- create index index_ord on orders(cus_id,ord_status);
--15.当索引都不能满足性能要求的时候,需要拆分成当前表和历史表,把今年年初之前的所有数据全部迁移到历史表中
--并在把当前表年初之前的数据删除掉
- --这个没看懂
- ?CREATE TABLE ordersPAR
- ????(
- ????ord_id NUMBER(6),
- ????cus_id NUMBER(6),
- ????ord_status CHAR(2),
- ????pay_status CHAR(2),
- ????ord_money NUMBER(6),
- ????ord_createtime DATE
- ????)
- ????PARTITION BY RANGE (ord_createtime)
- ???(PARTITION historic VALUES LESS THAN (TO_DATE(‘2016-1-1‘, ‘YYYY-MM-DD‘)),
- ????PARTITION presently VALUES LESS THAN (TO_DATE(‘2016-12-31‘, ‘YYYY-MM-DD‘)))
--16.现在由于订单表分了当前表和历史表,但是用户想查两个表的数据,写一个集合语句查两个表中的数据
- select * from historic UNION select * from presently;
--17.用最高权限的管理员账号,授予SCOTT账号可以访问你刚刚建的用户下,客户信息表所有增删改查权限
- GRANT ALL ON LJL.customer TO scott;
Reference
LJDragon的作业
Oracle_Q&A_04