SQL知识点4 - oracle

创建、维护表( 属于DDL )

create - 创表

注意事项

• 当前用户拥有 create table 权限
• 当前用户拥有一定大小的表空间
• 使用不属于本用户的表：用户名.表名
• 数据字典
  • user_tables：查看当前用户所有表对象

 
  语法结构

// 方法1
create table 表名 (
    列名 数据类型 【约束】 
)

// 方法2
create table 表名 [ (列名) ] as  子查询           // 新表列名列表 必须与 子查询 匹配  -- 列名不能多不能少 ----  注意该语句没有复制父表的约束

 
  示例1

create table student (
    id number(11),
    sex char(2) default('男'),
    sname varchar2(15)          // 括号内最后一行不需要写 逗号
);      //  sql语句记得 分号结束

 
  示例2：不会复制父表的约束

create table dept_copy (a, b, c) as select * from dept;

create table dept_copy  as select * from dept  where 1=0;     //只创建表结构

 

alter - 修改表结构

注意事项

• 修改表
  • 只能修改 列名、数据类型及长度、默认值
  • 数据类型修改： 该列的所有数据都必须为 null 或者 ‘’
  • 数值型长度修改： 长度变小 → 改前数据必须为空、长度变大 → 无限制
  • 字符型长度修改： 长度表小 → 符合当前数据的最小值、长度变大 → 无限制
     
• 删除列
  • 默认： 被外键引用的列，不能被删除
  • 表必须保留一列
     

  语法结构

// 增加新列
alter table 表名 add ( 新列的定义 )

// 修改列
alter table 表名 modify ( 列的定义 )

// 修改列名
alter table dept rename column 旧列名 to 新列名;

// 删除列
alter table 表名 drop (列名)

 

  示例

// 增加咧
alter table dept add ( describe varchar2(30) not null );

// 修改列
alter table dept modify ( loc varchar(30)  );

// 修改列名
alter table dept rename column loc to local;

// 删除列
alter table dept drop (ｄescribe );

 

drop - 删除表

注意事项

• 表的结构、以及数据被删除
• 约束、索引被删除
• 与表关联视图、同义词被保留、但已失效

 
  语法结构

drop table 表名;

 

rename - 重名表名

  语法结构

rename 旧表名 to 新表名

 

truncate - 截断表

注意事项

• 权限： 表拥有者 或者 权限是delete any table
• 删除表中所有数据

  语法结构

truncate table 表名

 

视图 – view

选择性的显示数据表的一部分

 
注意事项

• 复杂视图如果有 单行、多行函数，必须写明视图列名 或者 基表函数别名
   

• update、delete只能作用视图显示出来的数据，其他未显示的视图数据即使能运行成功，但并不能作用到基表
   

• with read only：不能通过视图执行任何DML语句
   

• with check option：insert的数据必须在视图能显示、即符合where范围
   

• 数据字典

  • user_views：包含视图的定义
  • user_updatable_columns：包含描述哪些列可以更新、插入、删除
  • user_objects：包含用户的对象

 
 
通过视图进行DML操作

• delete - 创视图时的子查询不存在以下内容即可以删除
  • group函数、( group by、distinct )关键字
     
• update - 创视图时的子查询不存在以下内容即可以修改
  • group函数、( group by、distinct )关键字
  • 表达式定义的列
  • rownum 伪列
     
• insert - 创视图时的子查询不存在以下内容即可以插入
  • group函数、( group by、distinct )关键字
  • 表达式定义的列
  • rownum 伪列
  • 基表中未在视图中选择的其它列定义为非空并且没有默认值 — ？？？？看不懂

 

 

创建视图

  语法结构

// or replace修改已经存在的视图、force基于是否存在，都会创此视图
create 【 or replace 】【 force / noforce 】 view 视图名  【 视图列名 】
as 子查询
【 with check option 【constraint 约束名】 】  //通过视图进行插入、修改的数据行满足所定义的查询
【 with ready only 】    // 不能在视图进行DML操作

 
  示例

// 简单视图
create view view_emp as select * from emp;

// 复杂视图
create view dept_sum ( dname, minsal, avgsal )
as select dname, mix(sal), avg(sal) 
    from dept, emp
    where dept.deptno = emp.deptno
    group by dname;

删除视图

  语法结构

drop view 视图名;

 

约束 – constraint

列值必须符合某种限制 – 不仅仅是数据类型

oracle默认约束命名： SYS_C + 数字n

自定义约束建议命名规则： 表名_列名_约束类型

数据字典

• user_constraints：查看表上所有约束
• user_cons_columns：查看与约束相关联的列名

 

添加约束 - 创表时

  语法结构

// 方法1 - 创表时
create table 表名 (
    列名 数据类型  【 constraint 约束名 】references 参照表( 参照列 ) 删除类型 ,    // 外键列级别约束
    列名 数据类型 默认值 【 constraint 约束名 】  约束类型,       // 列级别约束、在每列后面
    
    【 constraint 约束名 】 约束类型( 列名 ) ,            // 表级别约束，定义所有列之后，在写约束
    【 constraint 约束名 】foreign key( 外键列名 ) references  参照表( 参照列 ) 删除类型// 外键表级别约束
)

 

  示例

create table student (
    sid number(11) constraint student_sid_unique unique,
    sname varchar2(16) not null,
    loc varchar2(30),
    sex char(2) check (sex in ( '男', '女' ),
    age number,
    constraint student_loc_unique unique(loc),
    constraint student_age_check check( age between 18 and 30 )
);

 

追加约束 - 已经创表

  语法结构

alter table 表名 add  【 constraint 约束名 】 约束类型( 列名 ) 

alter table 表名 add 【 constraint 约束名 】 foreign key( 外键列名 ) references 参照表( 参照列 )                 // 追加外键

alter table 表名 modify  ( 列名【 constraint 约束名 】not null )    // 特殊、追加不空约束

 
  示例

alter table student add constraint student_sid primary key ( sid );

alter table student add foreign key( sname ) references school(sname);

alter table student modify ( sex not null );

 

删除约束

  语法结构

alter table 表名 drop  primary/foreign key( 列名 );

alter table 表名 drop constraint 约束名 【 cascade 】   //【cascade】跟该表关联的约束一同删除

 
  示例

alter table student drop primary key cascade  // 外表的外键约束一同删除

alter table student drop constraint student_sid_unique;

 

启用、禁用约束

大批数据导入、禁用约束能提高导入效率

  语法结构

 // 禁用约束、cascade外表关联该列的约束一同禁用
alter table 表名 disable constraint 约束名 【 cascade】 ;

// 启用约束u
alter table 表名 enable constraint 约束名;

 

索引 – index

类似书本目录、查找页面内容不需要全书翻页查找，只需根据目录查找大概的范围位置

 

注意事项

• 占用空间
• 降低DML操作速度
• 数据字典
  • user_indexs：包含索引的名称、及其唯一性
  • user_ind_columns：包含索引名称、表名、列名

例如：empno创建了索引，但是查询条件 （empno+12）有算术、单行函数则该索引不会起作用

创建索引

手动创建索引名建议使用：idx_表名_列名

 

  语法结构

create index 索引名 on 表名( 列名 );

 
  示例

// 单列索引
create index idx_emp_empno on emp( empno );

// 复合索引
create index idx_emp_empnoDeptno on emp( empno, deptno );

 
 
  测试检索速度

// emp_copy表  有1千万条数据
select * from emp where empno=100000;     
// 无索引时：2.749、2.746、2.734、2.730、2.722 秒

create  index idx_empCopy_empno on emp_copy(empno);     // empno列上创建索引
// 有索引时：0.041、0.037、0.032、0.041 秒

// 有索引 比 无索引 快了将近100多陪的速度

 

删除索引

索引被删除 → 索引所占的空间被释放

  语法结构

drop index 索引名

 

同义词( 对象别名 ) – synonym

数据库对象的指针 → 类似指向java对象的变量名
公有同义词的创建、删除 → 只能由数据库管理员进行操纵

创建同义词

  语法结构

create 【public】synonym 对象别名 for 对象名   ---  默认 private 

 
  示例

create synonym s_emp for emp;     // emp表指针变量为 s_emp

// 两者查询是一样的
select * from emp;     
select * from s_emp; 

&emsp；

删除同义词

  语法结构

drop synonym 同义词( 对象别名 )