Oracle表SQL操作

实验内容

一、创建表语句

二、修改表语句

三、DML语句练习

四、删除表语句

前置条件：
在Oracle权限安全管理中创建了以自己姓名为名的本人用户，并使用system用户完成了BOOKS和CUSTOMERS表的创建，在system.books表中已经插入数据('20240101','Oracle数据库管理与开发','李真',48)。
注意：Oracle中的DML语句默认需要COMMIT提交事务之后才会保存到硬盘数据中，因此如果system.books表中的数据消失，请重新插入该数据之后，使用COMMIT;语句完成事务提交。

一、创建表语句

先登录system用户为本人用户(本文本人用户用***代替)进行CREATE TABLE权限授权。

如果缺少SELECT权限，还要登录sys账户为本人用户进行授权。

参考：

SQL> alter user *** quota unlimited on BOOKTBS1;
用户已更改。
SQL> GRANT SELECT ON system.books TO ***;

以下操作使用本人用户完成

1.基于复制表创建的语句，在本人用户下复制SYSTEM.BOOKS表在表空间BOOKTBS1中创建一张BOOKS表，并复制其中的数据。

SOL> create table BOOKs tablespace BOOKTBS1 as select * from SYSTEM.BOOKS.
表已创建。

2.基于复制表创建的语句，在本人用户下复制SYSTEM.CUSTOMER表在表空间BOOKTBS1中创建一张CUSTOMERS表，只保留表结构，不包含其中的数据（查询数据时指定查不到数据的条件）。

SQL> create table CUSTOMERS tablespace BOOKTBS1

as select * from SYSTEM.CUSTOMERS where 1=2;

3.在BOOKTBS1表空间下创建如下几张表格，注意选择列级或者表级约束定义语句以创建表格中的约束。

SQL> ALTER TABLE CUSTOMERS ADD PRIMARY KEY (CUSTOMER_ID);

表已更改。

SQL> CREATE TABLE ORDERS
2 (ORDER_ID NUMBER(4) PRIMARY KEY,
3 CUSTOMER_ID NUMBER(4),
4 ORDERDATE DATE NOT NULL,
5 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID));

表已创建。

SQL> CREATE TABLE ORDERITEM
2 (ORDER_ID NUMBER(4),
3 ITEM_ID NUMBER(4),
4 ISBN VARCHAR2(50) NOT NULL,
5 QUANTITY NUMBER(4),
6 PRIMARY KEY(ORDER_ID,ITEM_ID),
7 FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID));

表已创建。

4.查看BOOKS表和ORDERITEM表的结构和其中的约束。使用desc命令查看表结构；查询数据字典user_constraints查看表中的约束名称和约束类型。

SQL> DESC ORDERSITEEM;
SQL> DESC BOOKS;
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'ORDERITEM';
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'BOOKS';

二、修改表语句

1.删除表BOOKS中的cost列。

SQL> ALTER TABLE BOOKS DROP COLUMN COST;
表已更改。

2.向表BOOKS中重新添加COST列，字段类型为NUMBER(6,2)。

SQL> ALTER TABLE BOOKS ADD COST NUMBER(6,2);
表已更改。

3.向表BOOKS中添加category类别列，字段类型为VARCHAR2(10)。

SQL> ALTER TABLE BOOKS ADD CATEGORY VARCHAR2(10);
表已更改。

4.修改BOOKS 表中title列的字段类型为VARCHAR2(20)，并设置唯一约束UNIQUE。

SQL> ALTER TABLE BOOKS MODIFY TITLE VARCHAR2(30) UNIQUE;
表已更改。

5.使用表级约束添加语句，为BOOKS表中的author列添加UNIQUE约束，约束名为UA。

SQL> ALTER TABLE BOOKS ADD CONSTRAINT UA UNIQUE(AUTHOR);
表已更改。

6.使用表级约束删除语句，删除上一步添加的约束UA。

SQL> ALTER TABLE BOOKS DROP CONSTRAINT UA;
表已更改。

三、DML语句练习

1.修改BOOKS表中之前插入的数据('20240101','Oracle数据库管理与开发','李真',48)的cost字段为460.5，category字段为计算机。

SQL> UPDATE BOOKS

2 SET COST=460.5,CATEGORY='计算机'

3 WHERE ISBN='20240101';
已更新 1 行。

2.向BOOKS表、CUSTOMERS表、ORDERS表、ORDERITEM表中插入如下几条数据:

SQL> INSERT ALL
2 INTO BOOKS(ISBN,TITLE,AUTHOR,COST,CATEGORY)
3 VALUES('20240102','计算机网络','谢希仁',500,'计算机')
4 INTO BOOKS(ISBN,TITLE,AUTHOR,COST,CATEGORY)
5 VALUES('20240103','三体','刘慈欣',320,'小说')
6 INTO BOOKS(ISBN,TITLE,AUTHOR,COST,CATEGORY)
7 VALUES('20240104','球状闪电','刘慈欣',360,'小说')
8 INTO BOOKS(ISBN,TITLE,AUTHOR,COST,CATEGORY)
9 VALUES('20240105','活着','余华',400,'小说')
10 SELECT * FROM DUAL;
已创建4行。

SQL> INSERT ALL
2 INTO CUSTOMERS(CUSTOMER_ID,NAME,PHONE)
3 VALUES(1,'张三丰','13312345678')
4 INTO CUSTOMERS(CUSTOMER_ID,NAME,PHONE)
5 VALUES(2,'张无忌','13387654321')
6 SELECT * FROM DUAL;
已创建2行。

SQL> INSERT
2 INTO ORDERS(ORDER_ID,CUSTOMER_ID,ORDERDATE)
3 VALUES(1000,1,TO_DATE('24-10月-2024'));
已创建 1 行。

SQL> INSERT ALL
2 INTO ORDERITEM(ORDER_ID,ITEM_ID,ISBN,QUANTITY)
3 VALUES(1000,1,'20240103',5)
4 INTO ORDERITEM(ORDER_ID,ITEM_ID,ISBN,QUANTITY)
5 VALUES(1000,2,'20240101',10)
6 SELECT * FROM DUAL;
已创建2行。

3.查询姓刘的作者编写的书籍信息，并按照价格降序排列。

SQL> SELECT * FROM BOOKS WHERE AUTHOR LIKE '刘%' ORDER BY COST DESC;

4.删除顾客信息中，顾客名为张无忌的数据。

SQL> DELETE FROM CUSTOMERS WHERE NAME LIKE '张无忌';
已删除 1 行。

5.查询订单编号为1000的订单订购的书籍的ISBN号，书名和数量。

SQL> SELECT BOOKS.ISBN,BOOKS.TITLE,ORDERITEM.QUANTITY

2 FROM ORDERITEM,BOOKS

3 WHERE ORDERITEM.ISBN=BOOKS.ISBN AND ORDERITEM.ORDER_ID=1000;

6.查询本人用户下ORDERITEM表中的约束名、约束类型和约束绑定的列。

SQL> SELECT UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE,

2 CC.COLUMN_NAME FROM USER_CONSTRAINTS UC

3 JOIN USER_CONS_COLUMNS CC

4 ON UC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

5 WHERE UC.TABLE_NAME = 'ORDERITEM';