oracle 的使用

一. docker 模式下进入数据库

ubuntu@jiang:~$ sudo docker ps -a
sudo: unable to resolve host jiang
CONTAINER ID        IMAGE                             COMMAND                  CREATED             STATUS              PORTS                                      NAMES
f096e625bcd3        oracleinanutshell/oracle-xe-11g   "/bin/sh -c '/usr/sb…"   About an hour ago   Up About an hour    22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp   compassionate_lamport

ubuntu@jiang:~$ sudo docker exec -it f096e625bcd3 /bin/bash
sudo: unable to resolve host jiang
root@f096e625bcd3:/#

二.

1. 打开终端窗口并执行oraenv 命令以设置环境变量。

2. 执行以下语句以系统管理员身份连接到数据库：

sqlplus sys / <password> as sysdba;

3.默认情况下，HR模式已锁定。执行以下语句以解锁HR模式。

ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK; # 演示用户

4.行以下命令以连接到HR 模式：

　　connect hr/hr

SQL> connect hr/hr
Connected.

5. DESCRIBE命令提供了一个指定的表或视图的描述。表和视图的描述包含以下信息：

列名
是否允许每列的空值（NULL或NOT NULL）
列的数据类型，例如 DATE，NUMBER， VARCHAR2
柱的精度，例如VARCHAR2（50）

语法：DESC [RIBE] <table >

执行以下命令以查看EMPLOYEES表的说明：

DESCRIBE EMPLOYEES;

SQL> desc employees
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                   NOT NULL NUMBER(6)
 FIRST_NAME                        VARCHAR2(20)
 LAST_NAME                   NOT NULL VARCHAR2(25)
 EMAIL                       NOT NULL VARCHAR2(25)
 PHONE_NUMBER                        VARCHAR2(20)
 HIRE_DATE                   NOT NULL DATE
 JOB_ID                    NOT NULL VARCHAR2(10)
 SALARY                         NUMBER(8,2)
 COMMISSION_PCT                     NUMBER(2,2)
 MANAGER_ID                        NUMBER(6)
 DEPARTMENT_ID                        NUMBER(4)

三.表操作

　　查询表

1. select 语句

语法：SELECT {* | [DISTINCT] column | expression [alias]，...} FROM <table>;

1). 查询表的所有数据

SELECT * FROM departments; //

SQL> SELECT * FROM departments;

DEPARTMENT_ID DEPARTMENT_NAME             MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
       10 Administration                200        1700
       20 Marketing                 201        1800
       30 Purchasing                114        1700

2). SELECT 语句中指定列名来显示表中的特定数据列

select JOB_ID,JOB_TITLE FROM jobs;

SQL> select JOB_ID,JOB_TITLE FROM jobs;

JOB_ID       JOB_TITLE
---------- -----------------------------------
AD_PRES    President
AD_VP       Administration Vice President

2. 限制数据

1. where

修改SELECT语句。执行以下查询以将行数限制为DEPARTMENT_ID 60：

SELECT * 
FROM departments 
WHERE department_id = 60;

2.order by 排序子句

升序

SELECT  LAST_NAME, JOB_ID, HIRE_DATE
FROM employees 
WHERE JOB_ID='SA_REP' ORDER 
BY HIRE_DATE;

降序

SELECT  LAST_NAME, JOB_ID, HIRE_DATE
FROM employees 
WHERE JOB_ID='SA_REP' ORDER 
BY HIRE_DATE DESC;

三. 创建架构

示例：您将创建一个名为ONLINE_SHOPPE的模式。该模式描绘了一个与客户群和商品一起运营的在线商店。有关客户的信息存储在CUSTOMERS表中，有关商品的信息存储在COMMODITIES表中，订单详细信息存储在ORDERS表中。

1. 创建用户

　　数据库管理员执行许多任务其中一个更常见的任务是创建数据库用户并为其分配唯一的用户名。用户使用用户名和密码登录数据库后，可以发出数据库SQL语句来创建对象，查询对象和管理数据库。创建用户是一种创建模式的方法, 您将执行CREATE USER语句以创建和配置数据库用户。

语法：CREATE USER <USER> IDENTIFIED BY <password>;

1). 执行以下语句以管理员身份连接到数据库，并创建名为ONLINE_SHOPPE的用户。

CONNECT sys/<password> as sysdba;

CREATE USER online_shoppe IDENTIFIED BY online; //创建用户online-shoppe 密码为online

SQL>CREATE USER online_shoppe IDENTIFIED BY online;

User created.

在Oracle数据库中创建了名为ONLINE_SHOPPE的模式。

2. 分配权限

当多个用户访问数据库对象时，您可以使用特权控制对象的授权。权限控制用户是否可以修改另一个用户拥有的对象。通过以下方式授予或撤销它们：

实例管理员
具有ADMIN权限的用户
对象的所有者

通常，有两种类型的权限：

系统特权：对任何对象（例如表，视图和索引）执行特定操作的权限。只有实例管理员或具有ADMIN 权限的用户才能分配或撤消系统权限。
对象权限：对对象执行特定操作或访问其他用户对象的权限。对象的所有者拥有该对象的所有对象权限，并可以将该对象的对象权限分配给其他数据库用户。

以下是一些基本的系统和对象权限：

系统权限：

创建数据库中任何用户拥有的表，视图或索引
更改数据库中的表，视图或索引
删除数据库中的表，视图或索引

对象特权：

将值插入表中
为表创建外键依赖项
从表中选择
更新表格

您可以使用GRANT语句为用户和角色分配权限。要分配权限，必须为您分配ADMIN OPTION 或GRANT ANY PRIVILEGE系统特权。

语法：GRANT <grant_privilege> TO <user>;

1).

使用CREATE USER 语句创建用户时，默认情况下用户的权限域为空。管理员根据用户将来可能执行的任务为用户分配权限。ONLINE_SHOPPE 用户建立会话，创建表，并针对表写入DML语句。执行以下语句，为ONLINE_SHOPPE用户分配所需的权限：

GRANT CREATE SESSION to online_shoppe;
GRANT CREATE TABLE to online_shoppe;
GRANT UNLIMITED TABLESPACE to online_shoppe;
GRANT SELECT ANY TABLE to online_shoppe;
GRANT UPDATE ANY TABLE to online_shoppe;
GRANT INSERT ANY TABLE to online_shoppe;
GRANT DROP ANY TABLE to online_shoppe;
grant create any trigger to user_name;

2.创建表

在ONLINE_SHOPPE 模式中创建表之前，您应该了解表和完整性约束的概念。

表：数据库中数据存储的基本单位。在表中，数据存储在行和列中。您可以使用表名，一组列，数据类型和宽度定义表。

完整性约束：表中列的规则。您可以指定这些规则以在定义它们的列中强制实施数据完整性。Oracle数据库的基本约束包括：

语法：CREATE TABLE [schema。] table （column datatype [DEFAULT expr] [，...]）;

执行以下步骤以在架构中创建CUSTOMERS， COMMODITIES和ORDERS表。

1). 连接到ONLINE_SHOPPE架构并创建CUSTOMERS表，并将CUSTOMER_ID 列作为主键。

 connect ONLINE_SHOPPE/online;  #连接到视图
 CREATE TABLE customers(
   customer_id    VARCHAR2(4),
   customer_name  VARCHAR2(20),
   address        VARCHAR2(60),
   contact        VARCHAR2(20), 
   CONSTRAINT cust_id_pk PRIMARY KEY(customer_id) ); #customer_id作为主键约束，别名为cust_id_pk

2). 创建COMMODITIES与表COMMODITY_ID 列作为主键和UNIT_PRICE 列作为一个非空列。

CREATE TABLE commodities(
   commodity_id     VARCHAR2(4),
   commodity_name   VARCHAR2(20),
   unit_price       NUMBER(8,2) NOT NULL,
   CONSTRAINT comm_id_pk PRIMARY KEY(commodity_id) );

3). 使用以下命令创建ORDERS表：

ORDER_ID列作为主键
COMMODITY_ID和CUSTOMER_ID 作为外键
UNITS和TOTAL_COST为NOT NULL值
检查数字列上的约束以接受大于零的值

CREATE TABLE orders(
   order_id       VARCHAR2(4),
   customer_id    VARCHAR2(4),
   commodity_id   VARCHAR2(4),
   units          NUMBER(8,2) NOT NULL, #整数部分6位数，小数部分2位数
   total_cost     NUMBER(8,2) NOT NULL,
   CONSTRAINT ordr_id_pk PRIMARY KEY(order_id),
   CONSTRAINT ordr_cust_fk FOREIGN KEY (customer_id)REFERENCES customers(customer_id),
   CONSTRAINT ordr_comm_fk FOREIGN KEY (commodity_id)REFERENCES commodities(commodity_id),
   CONSTRAINT check_unit CHECK(units > 0),  #约束检查 
   CONSTRAINT check_totl CHECK(total_cost > 0) );

3.插入，修改，删除记录

1).插入数据：执行INSERT 语句以将数据行添加到数据库表。

语法：INSERT INTO table [（column [，column ...]）] VALUES（value [，value ...]）;

执行以下语句以将数据插入CUSTOMERS，COMMODITIES和ORDERS 表。

INSERT INTO customers VALUES ('C001', 'BDAVIS', 'Boston', '650.551.4876');
INSERT INTO customers VALUES ('C002', 'SSTEPHEN', 'ST.Louis', '650.501.9321');
INSERT INTO customers VALUES ('C003', 'DCARTER', 'California', '650.507.6632');

INSERT INTO commodities VALUES ('M001', 'DVD Player', 109);
INSERT INTO commodities VALUES ('M002', 'Cereal', 03);
INSERT INTO commodities VALUES ('M003', 'Scrabble', 29);

INSERT INTO orders VALUES ('R001', 'C003', 'M002', 50, 150);
INSERT INTO orders VALUES ('R002', 'C001', 'M003', 30, 87);
INSERT INTO orders VALUES ('R003', 'C003', 'M001', 6, 654);

2). 修改数据：使用UPDATE 语句修改数据库表中的数据行。执行以下语句将DVD播放器的单价从109美元更改为129美元：

UPDATE commodities SET unit_price = 129 WHERE commodity_name = 'DVD Player';

select * from commodities where UNIT_PRICE=129;

3). 删除数据：使用DELETE 语句从数据库表中删除数据行。执行以下语句以删除ORDERS表中的第一条记录：

DELETE FROM orders WHERE order_id ='R001';

4.撤销和保存记录（事务）

1). 执行COMMIT语句以保存您在上一节中执行的数据操作事务。

承诺;

2). 执行以下语句以删除订单ID为R002的行，并查询ORDERS 表以确保删除该记录。

DELETE FROM orders WHERE order_id ='R002'; SELECT * FROM orders;

输出显示记录已成功删除。

3). 执行以下语句以撤消删除订单ID为R002的行，并查询表以显示记录：

ROLLBACK; SELECT * FROM orders;

之前的DELETE语句已回滚。

注意：使用COMMIT 语句永久保存事务后，无法撤消事务。

5. 删除表

DROP TABLE 语句以从数据库中删除表及其数据

语法：DROP TABLE <table>;

1). 执行DROP TABLE语句以删除CUSTOMERS表。

DROP TABLE customers;

SQL> DROP TABLE ONLINE_SHOPPE.orders;
Table dropped.

由于CUSTOMER_ID 列上的引用完整性约束，将显示错误消息。

2). 包括CASCADE CONSTRAINTS子句以删除表及其参照完整性约束。

DROP TABLE customers CASCADE CONSTRAINTS;

6. 撤销权限

执行REVOKE 语句以撤消用户和角色系统特权。要撤消系统特权或角色，必须使用ADMIN OPTION为您分配特权。

1). 以SYS用户身份连接到数据库，并撤消ONLINE_SHOPPE的CREATE SESSION权限。

CONNECT sys/<password> as sysdba; REVOKE CREATE SESSION FROM online_shoppe;

SQL> REVOKE CREATE SESSION FROM online_shoppe;

Revoke succeeded.

2). 以ONLINE_SHOPPE身份连接到数据库。

CONNECT online_shoppe/online;

您无法连接，因为您没有CREATE SESSION权限。