【Oracle学习05】Oracle DDL和模式对象

陶老师运维笔记 2019-12-11

1024

【Oracle学习05】Oracle DDL和模式对象

文中内容主要来自于OCP考试指南1Z0-61 ,SQL基础部分。

5.1 客户端工具

5.1.1 SQL*Plus

sqlplus 正常运行，需要提前设置ORACLEHOME,LDLIBRARY_PATH,PATH环境变量。

环境变量

$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 -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

5.2 数据库对象

https://www.jianshu.com/p/8409660673a0

数据库内有各种不同类型的对象，所有的对象都由某人拥有。某人如HR，拥有的对象就是其模式。
数据库对象: 常见的有表，视图，同义词，索引和序列。

5.2.1 对象类型

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 用户和模式

参考：表空间、用户、Schema

用户是一个可以连接数据库的人，用户有用户名和密码。模式是包含用户所拥有对象的容器。创建用户时，其模式也就创建了。模式是用户拥有的对象，最初，模式是空的。

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 可变长度的字符数据,如果MAXSTRINGSIZE=STANDARD,其度就是从1B到4KB,如果MAXSTRINGSIZE= 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 变长的二进制数据,如果如果MAXSTRINGSIZE=STANDARD,则其1-4000字节,如果如果MAXSTRINGSIZE=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';

5.4 创建表

创建表

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);

使用查询建表

create table no_emps as select * from employees where 1=1;
Connected.
SQL> desc no_emps

修改表字段

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)

删除表

drop table schema.tablename;

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、查看索引信息可以在 allindexs 表中 2、查看索引信息及引用的列 allindcolumns 3、查看函数索引信息 allind_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 约束

表约束是数据库能实施业务规则及保证数据依实体-关系模型的一种手段。

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 序列

创建序列：
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索引知识

oracle

最后修改时间：2019-12-17 12:28:19

文章转载自陶老师运维笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【Oracle学习05】Oracle DDL和模式对象

【Oracle学习05】Oracle DDL和模式对象

5.1 客户端工具

5.1.1 SQL*Plus

5.1.2 SQL Developer

5.2 数据库对象

5.2.1 对象类型

5.2.2 用户和模式

5.2.3 命名模式对象

5.3 可用的数据类型

5.3.1 可用的数据类型

5.4 创建表

5.5 临时表

5.6 索引

5.6.1 索引类型

5.6.2 创建索引

5.7 约束

5.7.1 约束类型

5.7.2 约束状态

5.7.3 创建及管理约束

5.8 视图

5.9 同义词

5.10 序列

5.11 知识回顾

5.12 测试题

参考:

评论