【Oracle学习05】Oracle DDL和模式对象
文章来源: 陶老师运维笔记-微信公众号
oracle12c DDL和对象
5.1 客户端工具
5.1.1 SQL*Plus
sqlplus 正常运行，需要提前设置ORACLE_HOME,LD_LIBRARY_PATH,PATH环境变量。
SQL and SQL*Plus Interaction
SQL Statements Versus SQL*Plus Commands
sqlplus使用命令:
#SQL*Plus Editing Commands
A[PPEND] text
C[HANGE] / old / new
C[HANGE] / text /
CL[EAR] BUFF[ER]
DEL
DEL n
DEL m n
I[NPUT]
I[NPUT] text
L[IST]
L[IST] n
L[IST] m n
R[UN]
n
n text
0 text
#SQL*Plus File Commands
SAVE filename
GET filename
START filename
@ filename
EDIT filename
SPOOL filename
EXIT
list and Append
Using the SAVE and START Commands
Using the SQL*Plus SPOOL Command
Using the AUTOTRACE Command
环境变量
$which sqlplus
/u01/app/oracle/product/12.2.0/db_1/bin/sqlplus
#需要的环境变量
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin:$PATH:$HOME/bin
sqlplus使用
语法:
sqlplus [username[/password[@database]]]
$sqlplus -H
#sqlplus [ [<option>] [{logon | /nolog}] [<start>]
$sqlplus myusername/mypassword@Host/ORCL
$sqlplus sys/oracle as sysdba
$sqlplus /nolog
##示例
$sqlplus scott/tiger@orcl
$sqlplus scott/tiger@db01:1521/orcl
##使用
Set lines 800;
Set pages 200;
select * from dual;
常见链接错误
nc -z 192.168.56.103 1521
Connection to 192.168.56.103 port 1521 [tcp/ncube-lm] succeeded!
5.1.2 SQL Developer
SQL Developer是基于jav编写,需要运行JRE。
#SQL Developer
$java -version
SQL Developer作用:
- Browse, create, and edit database objects
- Execute SQL statements and scripts in SQL Worksheet
- Create and save custom reports
- Browse the Data Modeling options in SQL Developer
SQL Developer
Using Snippets
Using Snippets: Example
Using the Recycle Bin
Debugging Procedures and Functions
Database Reporting
Data Modeler in SQL Developer
示例:
SQL Developer
示例:
5.2 数据库对象
数据库内有各种不同类型的对象，所有的对象都由某人拥有。 某人如HR，拥有的对象就是其模式。
数据库对象: 常见的有表，视图，同义词，索引和序列。
理论基础:
数据库对象
Data Models
ERM
SQL分类
HR模式
5.2.1 对象类型
Naming Rules
SQL> Set lines 800;
SQL> Set pages 100;
desc user_objects;
desc dba_objects;
desc all_objects;
#
SQL> conn hr/hr.
SQL>select object_type,count(*) from all_objects group by object_type;
SQL> select object_type,count(*) from user_objects group by object_type;
OBJECT_TYPE COUNT(*)
---------------------------------------------- ----------
SEQUENCE 3
PROCEDURE 2
TRIGGER 2
TABLE 8
INDEX 19
VIEW 1
6 rows selected.
#
select object_type,count(object_type) from dba_objects group by object_type order by object_type;
OBJECT_TYPE COUNT(OBJECT_TYPE)
---------------------------------------------- ------------------
CLUSTER 10
CONSUMER GROUP 18
CONTAINER 2
CONTEXT 18
DATABASE LINK 1
DESTINATION 2
DIRECTORY 12
EDITION 1
EVALUATION CONTEXT 13
FUNCTION 365
INDEX 2875
INDEX PARTITION 210
INDEXTYPE 11
JAVA CLASS 38201
JAVA DATA 413
JAVA RESOURCE 1730
JAVA SOURCE 2
JOB 22
JOB CLASS 15
LIBRARY 236
LOB 693
LOB PARTITION 14
LOCKDOWN PROFILE 3
OPERATOR 62
PACKAGE 1061
PACKAGE BODY 1011
PROCEDURE 225
PROGRAM 11
QUEUE 23
RESOURCE PLAN 11
RULE 1
RULE SET 19
SCHEDULE 4
SCHEDULER GROUP 4
SEQUENCE 301
SYNONYM 12203
TABLE 2159
TABLE PARTITION 308
TABLE SUBPARTITION 32
TRIGGER 147
TYPE 2938
TYPE BODY 271
UNDEFINED 15
UNIFIED AUDIT POLICY 9
VIEW 7020
WINDOW 9
XML SCHEMA 43
47 rows selected.
5.2.2 用户和模式
用户是一个可以连接数据库的人，用户有用户名和密码。 模式是包含用户所拥有对象的容器。 创建用户时，其模式也就创建了。 模式是用户拥有的对象，最初，模式是空的。
5.2.3 命名模式对象
特殊符号处理: 可以用"号括起来。
create table "with space" ("-Hyphen" date);
insert into "with space" values(sysdate);
select * from "with space";
5.2.4 对象名称空间
名称空间定义了一组对象类型，在这个组中模式名+对象名构成唯一标识。不同名称空间的对象名可相同。
SQL> conn hr/hr.
SQL>select object_type,count(*) from all_objects group by object_type;
SQL> select object_type,count(*) from user_objects group by object_type;
OBJECT_TYPE COUNT(*)
---------------------------------------------- ----------
SEQUENCE 3
PROCEDURE 2
TRIGGER 2
TABLE 8
INDEX 19
VIEW 1
6 rows selected.
5.3 可用的数据类型
5.3.1 可用的数据类型
1、字符类型
- VARCHAR2 可变长度的字符数据,如果MAX_STRING_SIZE=STANDARD,其度就是从1B到4KB,如果MAX_STRING_SIZE= EXTENDED,其长度至多 32767字节。数据保存在数据库字符集中。
- NVARCHAR2和 VARCHAR2 一样,但数据保存在国家语言字符集(它是许可Unicode 字符集之一)中。
- CHAR 固定长度的字符数据,从 1B到B,它保存在数据库字符集中。如果数据的长度比列的长度短,就会用空格进行填充。
2、数字类型
- NUMBER：该数据类型能存储精度最多达38位的数字。Oracle的NUMBER类型精度很高， 远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
NUMBER( p,s ) p表示精度（总长度） s表示小数位置且四舍五入
NUMBER(10,3) 10是总长度，3是小数位数的长度 - FLOAT 这是一个 ANS数据类型,精度为 126 位二进制、38 位十进制的浮点数。
- INTEGER 相当于NUMBER,小数位数为0。
3、日期类型
- DATE：一个7字节的定宽日期/时间数据类型。其中总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
- TIMESTAMP：一个7 字节或12.字节的定宽日期/时间数据类型。它与DATE 数据类型不同，因为TIMESTAMP 可以包含小数秒（fractional second）；带小数秒的TIMESTAMP 在小数点右边最多可以保留9位。
4、二进制及大文本数据
- RAW : RAW 变长的二进制数据,如果如果MAX_STRING_SIZE=STANDARD,则其1-4000字节,如果如果MAX_STRING_SIZE=EXTENDED,则其长度可达32767与CHAR和VARCHAR2 数据类型不同,在执行SELECT命令时, Oracle Net不会将RAW数据从数据库的字符集转换为用户进程的字符集,或者在执行 INSERT命进行反向转换。
- BLOB： (binary large object) 在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，如果要存储电子表格、字处理文档、图像文件等就很适合采用
- CLOB：（Character Large Object）在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。这种数据类型很适合存储纯文本信息。
describe命令: 查看表中的数据类型。
SQL> describe 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)
查询数据字典视图
select column_name,data_type,nullable,data_length,data_precision,data_scale
from user_tab_columns where table_name='EMPLOYEES';
image.png
5.4 创建表
创建表
Data Types
Datetime Data Types
5.4.1 创建表
create table emp
(empno number(4),
ename varchar2(10),
hiredate date default trunc(sysdate),
sal number(7,2),
comm number(7,2) default 0.03);
SQL>CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE);
使用查询建表(CTAS语句)
CTAS建表不会有索引，即新表不会有Primary key ,UNIQUE Key,Foreign Key。但是会有 NOT NULL和Check约束。
#create table as 建表。
create table dept as select * from departments where 1=1; --建表及导入数据
create table dept as select * from departments where 1=2; --建表
select count(*) from dept;
#
create table no_emps as select * from employees where 1=1;
SQL> desc no_emps
5.4.2 修改表
ALTER TABLE Statement
alert
Dropping a Column
SET UNUSED Option
Read-Only Tables
修改表字段
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
JOB_ID NUMBER
#修改
alter table emp add (job_id number);
alter table emp modify (comm number(4,2) default 0.05);
alter table emp drop column comm;
alter table emp rename column HIREDATE to recruited;
SQL> alter table emp read only;
#查看
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
RECRUITED DATE
SAL NUMBER(7,2)
5.4.3 删除表
DROP:
DROP TABLE Statement
Dropping a Table
Truncate:
TRUNCATE TABLE Statement
drop table schema.tablename;
5.4.5 GRANT/REVOKE
GRANT
Privilege Types
REVOKE
GRANT SELECT any table to PUBLIC;
5.5 临时表
临时表包括所以会话都可以访问的定义，但是其中的行是插入的会话专用的!
create global temporary table tmp_emp(dept number,salary number);
insert into tmp_emp select EMPLOYEE_ID,salary
from employees where department_id=30;
select * from tmp_emp; #可以看见记录.
select count(*) from tmp_emp;
COUNT(*)
----------
6
#开启另外一个sqlplus,是看不见刚才的数据。
SQL> select count(*) from tmp_emp;
COUNT(*)
----------
0
5.6 索引
Oracle索引知识
1、查看索引信息可以在 all_indexs 表中
2、查看索引信息及引用的列 all_ind_columns
3、查看函数索引信息 all_ind_expressions
4、oracle比较智能，有时候即使创建了索引也不会使用，比如说在数据量比较少的情况下，可能就不会用索引
5、当进行全表扫描的时候，不用索引效率会更好
6、查询可能会使用缓存，所以说如果发现执行速度变快了，不一定说明你的sql更优了，有可能是使用到了缓存而已
7、使用plsql中的“解释计划”功能可以比较执行计划的消耗，进而写出更优的sql
5.6.1 索引类型
B+树索引
注意：null 用不到B+索引。
#null 用不到B+索引，如下将全表扫描.
select * from HR.employees where last_name is null;
位图索引
预先知道SQL可以用B+索引，但是数据仓库等不明确的情况，该用位图索引。
create bitmap index emp_idx3 on emp(deptno);
#数据仓库等不明确的情况，该用位图索引。
函数索引
函数索引：就是将一个函数计算的结果存储在行的列中
#函数索引
5.6.2 创建索引
示例1:
create table dept(deptno number,dname varchar2(10));
create table emp(empno number ,surname varchar2(10) , forename varchar2(10), dob date,deptno number );
create unique index dept_idx1 on dept(deptno);
create unique index emp_idx1 on emp(empno);
create index emp_idx2 on emp(surname,forename);
create bitmap index emp_idx3 on emp(deptno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_pk primary key(empno);
alter table emp add constraint emp_fk foreign key (deptno) references dept(deptno);
示例2:
SQL> conn scott/tiger
create Index scott.emp_ename_idx on scott.emp(ename) ;
select index_name,index_type,table_name from user_indexes where TABLE_NAME = 'EMP';
##
drop table t purge;
create table t (id int, status varchar2(2));
insert into t select 1,'N' from dual;
insert into t select rownum ,'Y' from dual connect by rownum <1000;
commit;
create index idx_status on t (case when status ='N' then 'No' end);
5.7 约束
表约束是数据库能实施业务规则及保证数据依实体-关系模型的一种手段。
Constraints
Defining Constraints
Defining Constraints
UNIQUE Constraint
FOREIGN KEY Constraint
FOREIGN KEY Constraint: Keywords
CHECK Constraint
Violating Constraints
Violating Constraints
5.7.1 约束类型
- UNIQUE
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
- CHECK
5.7.2 约束状态
- ENABLE VALIDATE
- DISABLE NOVALIDATE
- ENABLE NOVALIDATE
- DISABLE VALIDATE
5.7.3 创建及管理约束
创建及管理约束
alter table dept add constraint dept_pk primary key (deptno);
#查看约束
select constraint_name,constraint_type,column_name from user_constraints natural join user_cons_columns where table_name='DEPT';
#改名
alter table xxx rename constraint old_name to new_name;
5.8 视图
create view staff_clerk as select * from employees where JOB_ID like '%CLERK%';
select * from staff_clerk where ROWNUM <=10 ;
drop view xxx;
5.9 同义词
同义词可以对任何SQL起作用，而不用考虑模式等限制。
create synonym s1 for employees;
create public synonym s1 for departments;
desc s1; #该是employees
select * from s1 where rownum<10;
5.10 序列
- sequence : 序列只有select,alter,drop没有update,delete.
创建序列：
create sequence order_seq start with 1000 maxvalue 10000 minvalue 1000
nocycle cache 100;
查看用户序列
SQL> select sequence_name from USER_SEQUENCES;
SEQUENCE_NAME
------------------------------
order_seq
#生成
select order_seq.nextval,order_seq.currval from dual
5.11 知识回顾
主要数据库对象
- 有些对象包括数据，主要是表和索引
- 编程对象(如存储过程和函数)是可以执行代码
- 视图和同义词是能够访问其他对象的对象
- 表是保存使用列定义的行的二维结构
- 表在模式中。模式名和表名形成唯一标识符。
索引
- 要实施唯一的约束，就必须使用索引
- B*树索引不包括NULL,但位图索引包含NULL
- B*树索引可以为唯一索引，也可以非唯一索引
- B*树索引适用于基数大的列，位图索引适用于基数小的列。
- 位图索引可以采用复合形式，可以基于函数，也可以降序排列。 B*树索引可是唯一的，压缩的和反向键。
约束
- 可以在创建表时定义约束,也可以在随后添加。
- 可以与列一起定义约束,或者在列之后的表级别定义约束。
- 表级别约束可能比内联定义的约束更复杂。
- 表只能有一个主键,但可以有许多唯一键。
- 主键的功能等同于 UNIQUE加上 NOT NULL。
- 唯一约束不阻止多个空值的插入。
视图
- 简单视图有一个明细表(或基表),既不使用函数也不使用聚合。
- 复杂视图可以基于任何 SELECT语句,不管多么复杂。
- 视图是模式对要在另一个模式中使用视图,必须用模式名限定视图名。
- 可以像查询表一样查询视图。
- 可以将视图联接到其他视图或者联接到表,它们可以被聚合,在有些情况下以接受DML语句。
- 视图仅作为数据字典结构存在。每当查询视图时,必须运行底层 SELECT语句
同义词
- 同义词是视图或表的另一个名称。
- 私有同义词是模式对象;
- 公有同义词存在于用户模式之外,不需要指定模式限定符就可以使用
- 同义词与视图和表共享相同的名称空间,因此可以与它们互换使用。
序列
- 序列生成唯一值—除非指定了 MAXVALUE或 MINVALUE和 CYCLE。
- 递增序列不需要提交,不能回滚。
- 任何会话都能通过读取它的下一个值来递增序列。可以获得上次发送给会但不能获得上次发出的值。
5.12 测试题
create view tab1_v as select * from hr.tab1;
create synonym tab1_s for tab1_v;
select * from tab1_s;
#sequence
create sequence order_seq start with 10;
select sequence_name from USER_SEQUENCES;
create table seqtest(c1 number,c2 varchar2(10));
alter table seqtest add constraint seqtest_pk primary key(c1);
create sequence seqtest_pk_s;
insert into seqtest values (seqtest_pk_s.nextval,'first');
insert into seqtest values (seqtest_pk_s.nextval,'second');
commit;
select seqtest_pk_s.nextval from dual;
参考:
- https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html
- Introduction to Oracle Database
- Oracle索引知识
陶老师运维笔记
