19.数据定义语言
1.描述主要数据库对象
2.查看表结构
--2.1.命名原则
----必须为1-30字符长度
----只能包含A-Z，a-z，0-9，_,$, 和# 字符
----同一用户下，不能重名
----不能使用oracle服务器保留字
--2.2.足够的权限
----必须有create table权限
----有足够的空间
----指定表名
----列名，列数据类型，列长度
--2.3.表属于其他用户，不属于当前用户
----必须用用户名.表名
--2.4.默认值
----插入行时，为列指定缺省值
----可以使用字面值，表达式函数
----不能使用其他列的名字或者伪列
----缺省的数据类型必须匹配列的数据类型
--查询用户的所有表名信息
select * from tab;
select * table_name from user_tables;
select object_name from user_objects where object_type = 'TABLE';
--查看列名信息
hr@ORCLPDB01 2023-02-26 09:38:18> r
1* select table_name,column_name from user_tab_columns where table_name = 'EMPLOYEES'
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
EMPLOYEES EMPLOYEE_ID
EMPLOYEES FIRST_NAME
EMPLOYEES LAST_NAME
EMPLOYEES EMAIL
EMPLOYEES PHONE_NUMBER
EMPLOYEES HIRE_DATE
EMPLOYEES JOB_ID
EMPLOYEES SALARY
EMPLOYEES COMMISSION_PCT
EMPLOYEES MANAGER_ID
EMPLOYEES DEPARTMENT_ID3.描述列定义的可用数据类型
可用的日期时间类型
4.了解约束如何在表创建的时候建立
--1.介绍约束
--约束在表级实现强制规则
--约束制约关系可防止有依赖关系的表被删除
--约束类型
---- not null
---- unique
---- primary key 主键
---- foreign key 外键
---- check 检查
--2.使用约束的原则
----用户可以为约束命名，也可以是数据库服务器自动命名为SYS_SN的格式
----约束可以创建
----建表的时候
----表建成后修改
----约束可以在列级或表级定义-非空只能在列级别定义
----通过数据字典可以访问表约束信息4.1.定义约束
--列级约束
--表级约束
--非空约束
----约束定义列上不能出现空值
--唯一性约束
----表级列级都可以定义，如果定义多列必须在表级定义
--主键约束
----一个表只能有一个主键
----主键是唯一的并且非空
----可以联合主键，联合主键要求每列都非空
----主键唯一定位一行，所有主键也叫逻辑rowid
----主键不是必须的，可以没有
----主键是通过索引实现的
----索引的名称和主键的名称相同
--外键约束
----表级定义关联到子表中的列
----定义父表和表中列
----父表行被删除子表行被级联删除
----父表行被删除将依赖的外键值修改为空值
--外键约束
----外键约束在列级或表级定义
----外键约束只能关联到本表或其他表的主键或唯一键
--检查约束
----用户定义的条件，每一行必须满足
----不允许使用如下的表达式：
----涉及currval，nextval，level，rownum
----调用sysdate，uid，user，userenv
----涉及到其他关联行的查询
5.描述模式对象如何工作
5.1.约束使用
--添加约束的语法
----添加或丢弃约束，不会改变表结构
----启用或禁用约束
----使用modify子句添加非空约束
alter table xxx add [constraint constraint] type (column);
--丢失约束
----从employees表中丢失对经理的约束
alter table employees drop constraint emp_manager_fk;
----丢弃departments表中的主键约束，以及关联在employees.department_id列的外键约束
alter table departments drop primary key cascade;
--禁用约束
----使用alter table命令的disable子句，可以禁用约束
----使用cascade选项，可以禁用相关联的约束
----约束的状态可以通过uesr_constraints视图的status列查询
alter table employees disable constraint emp_emp_id_pk cascade;
--启用约束
----使用alter table 命令的enable子句，可以启用当前是出于禁用状态的约束
----如果启用了primary key或unique约束，就会自动创建主键或唯一索引
alter table employees enable constrain emp_emp_id_pk;
--删除约束
----cascade constraints子句是连同drop column子句一起使用的
----cascade constraints子句可以把被删除列上的关联的所有约束都丢弃
----cascade constraints子句还可以把被删除列上的关联的多列约束丢弃
----如果被删除的列已经被其列引用了约束，或是与其他的列共同使用了约束，那么，在删除该列的时候，必须使用cascade cnstraints子句，否则无法删除
--查看约束
----通过user_constraints,可以查看用户表所有的约束定义和名字等
select constraint_name,constraint_type,search_condition from user_constraints where table_name = 'EMPLOYEES';
----通过user_cons_columns，可以查看约束关联列的名字等
select constraint_name,column_name from user_cons_columns where table_name = 'EMPLOYEES';5.2.维护表
--只读表避免维护表期间被ddl，dml修改
--维护完毕更改为读写模式
alter table employees read only;
alter table employees read write;
--使用alter table 语句添加，修改，丢弃列
alter table xxx add
alter table xxx modify
alter table xxx drop
----修改列
--1.使用modify修改列的数据类型，大小和缺省值
--2.可以增大列的长度
--3.可以减小列的长度，但是只能减小到已有的数据长度，如果该列是数值型，那么，只有该列包含空值或没有记录时才能减小长度
--4.对缺省值的修改仅影响以后的插入行
----丢弃列
--1.使用drop column语句丢弃列
--2.每次只能丢弃一列
--3.丢弃的列可以包含数据，也可以不包含数据
--4.表必须至少保留一列
-----使用set unused
--1.使用set unuserd选项标记不再使用的列
--2.使用drop unused columns丢失标记为unused的列
--删除表，drop table xxx;6.建立简单与复杂的视图
--建立修改视图，从视图中查询数据
--关于视图
----视图时基于其他表或视图创建的逻辑表
----视图不包含自己的数据，他基于的表成为基表
----使用视图是为了：
--限制数据访问
--复杂查询简单化
--提供数据独立性
--相同的数据展现不同的视图
hr@ORCLPDB01 2023-02-26 11:08:05> r
1 create view empvu80
2 as
3 select employee_id,last_name,salary
4 from employees
5* where department_id = 80
View created.
Elapsed: 00:00:00.04
hr@ORCLPDB01 2023-02-26 11:08:07> desc empvu80;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
hr@ORCLPDB01 2023-02-26 11:08:24> select * from empvu80;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
145 Russell 14000
146 Partners 13500
147 Errazuriz 12000
148 Cambrault 11000
149 Zlotkey 10500
150 Tucker 10000
151 Bernstein 9500
152 Hall 9000
153 Olsen 8000
154 Cambrault 7500
155 Tuvault 7000
156 King 10000
--统计函数，表单时等操作的列需要定义列别名
hr@ORCLPDB01 2023-02-26 11:11:38> create view salvu50 as
2 select employee_id ID_NUMBER,last_name NAME, salary*12 ANN_SALAY
3 from employees
4 where department_id = 50;
View created.
Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-26 11:12:51>
--修改视图使用
create or replace
--限制DML操作
hr@ORCLPDB01 2023-02-26 11:18:04> r
1 create or replace view salvu50 as
2 select employee_id ID_NUMBER,last_name NAME, salary*12 ANN_SALAY
3 from employees
4* where department_id = 50 with read only
View created.
Elapsed: 00:00:00.09
--删除视图
drop view xxx7.建立维护使用序列
--1.自动产生唯一值
--2.是一个共享的对象
--3.典型的用于创建主键值
--4.可替代应用程序代码
--5.如果将序列值缓存在内存中可以提高访问效率
hr@ORCLPDB01 2023-02-26 11:27:06> create sequence dept_deptid_seq
2 increment by 10
3 start with 120
4 maxvalue 9999
5 nocache
6 nocycle;
Sequence created.
Elapsed: 00:00:00.00
--缓存序列值到内存可提高访问效率
--序列不连续发生在
---- 发生回滚
---- 系统崩溃
---- 被其他对象比如表调用过
---可以在user_sequences视图中查询下一个有效的值
--可以修改序列的步长，最大值，最小值，cycle或cache选项；修改的值对后续操作有效
--只有序列的所有者或具有修改序列权限的用户可以修改序列
--序列的起始值不能修改，如果修改，只能重建
--查看序列信息
select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;8.建立维护索引
--索引
--是一种方案对象
--由oracle服务器使用，通过指针提高查询的速度
--使用快速路径访问模式减少磁盘I/O
--不依赖与索引的表
--由Oracle服务器自动使用和维护
--自动创建：当为表定义了主键或唯一约束，会自动创建唯一索引
--手工创建：用户可以在多列上手工创建唯一索引
--可以在以下情况创建索引：
----1.某列包含的数据泛微很广
----2.某列包含大量的空值
----3.一列或多列经常作为条件出现在where子句中，或用于连接条件
----4.表很大，而大多数查询返回的行只占用行的2%-4%
--在以下情况下不需要建索引：
----1.表很小
----2.列不是经常用作查询条件
----3.大多数查询返回的行超过所有的行的2%-4%
----4.表会经常更新
----5.索引的列作为表达式的一部分被关联
--user_indexes字典视图包含了索引名和唯一性
--user_ind_columns视图包含了索引名，表名以及列
--通常将以上两个视图连接查询
hr@ORCLPDB01 2023-02-26 11:45:49> r
1 select ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniqueness
2 from user_indexes ix,user_ind_columns ic
3 where ic.index_name = ix.index_name
4* and ic.table_name = 'EMPLOYEES'
INDEX_NAME COLUMN_NAME COL_POS UNIQUENES
----------------------------------- ------------------------------ ---------- ---------
EMP_EMAIL_UK EMAIL 1 UNIQUE
EMP_NAME_IX FIRST_NAME 2 NONUNIQUE
EMP_JOB_IX JOB_ID 1 NONUNIQUE
EMP_MANAGER_IX MANAGER_ID 1 NONUNIQUE
EMP_NAME_IX LAST_NAME 1 NONUNIQUE
EMP_EMP_ID_PK EMPLOYEE_ID 1 UNIQUE
EMP_DEPARTMENT_IX DEPARTMENT_ID 1 NONUNIQUE
7 rows selected.
--基于函数索引是值基于表达式创建的索引
--索引表达式可以由列，常量，sql函数或用户自定义函数构建
--当查询中使用upper作为条件时，就会使用索引，否则走全表扫描
--删除索引
drop index xxx；
----只有drop any index权限的用户才能删除索引9.建立共有与私有同义词
--易于只想其他用户所属表
--缩短对象的名字
--public使同义词可以由多有用户访问
--私有同义词名字不能与通用户的其他对象同名
--创建同义词
create synonym d_sum for dept_sum_vu;
--删除同义词
drop synonyn d_sum;
