首页 > 代码库 > Oracle_Q&A_04

Oracle_Q&A_04

2024-08-29 01:38:53   220人阅读

2014-12-19作业

--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,‘tom‘,‘1234‘,‘qq@email‘,12345677,‘中国‘);
  2. INSERT INTO customer VALUES (cus_seq.nextval,‘Alen‘,‘1234‘,‘qq@email‘,12345677,‘美国‘);
  3. INSERT INTO customer VALUES (cus_seq.nextval,‘Jack‘,‘1234‘,‘qq@email‘,12345677,‘加拿大‘);
  4. INSERT INTO customer VALUES (cus_seq.nextval,‘Mack‘,‘1234‘,‘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,‘1‘,‘0‘,5000,sysdate);
  2. INSERT INTO orders VALUES (ord_seq.nextval,3,‘2‘,‘1‘,6320,sysdate);
  3. INSERT INTO orders VALUES (ord_seq.nextval,4,‘0‘,‘3‘,3500,sysdate);
  4. INSERT INTO orders VALUES (ord_seq.nextval,5,‘3‘,‘2‘,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;

Reference

LJDragon的作业

Oracle_Q&A_04


联系
我们