Oracle常用命令

Oracle 数据库
--了解Oracle的编码
select userenv('language') from dual


1、两种数据存储形式
1）文件
---清楚数据在文件中的存储格式
---对数据的处理过程全部需要代码实现
---对文件的并发操作也要通过代码实现

2）数据库
---表（table）是数据库中存储数据的基本单位


2、数据库标准语言：SQL
----结构化查询语言（Structured Query Language）
------数据定义语言（DDL Data Definition Language）
------数据操作语言（DML Data Manipulation Language）
------事务控制语言（TCL Transaction Control Language）
------数据查询语言（DQL Data Query Language）
------数据控制语言（DCL Data Control Language）


3、DB 和 DBMS
1）数据库（DATABASE）
---关系数据库使用关系或二维表存储信息。

2）关系型数据库管理系统（RDBMS）
-----关系型数据库管理系统（Relationship Database Management System）是一套软件，用在数据库中存储数据，维护数据，查询数据等等；


4、DDL 语句
1）create
----create table 表名（字段名 类型（范围），.....）；
 
-----create table 表名 as select 语句;//基于一个表创建另一个表

2）drop
----drop table 表名；//放回回收站
----drop table 表名 purge；//不放回回收站
----flashback table 表名 to before drop；//从回收站恢复表

3）desc
----desc 表名；//查看表结构

4)添加一列字段
   alter table 表名 add 字段名 类型;

5)修改字段类型和大小,是否允许为空
   alter table 表名 modify (字段 类型);

提示:如果修改类型;或将存储空间大改成小的;修改成非空;可能会产生错误,原因是可表中现有数据冲突导致改表结构失败.

6)删除一列字段
   alter table 表名 drop column 字段名;
 
7)修改表名字
   rename  表名 to 新表名;
   
提示:表名最大长度30,一个中文占2个的话,最大用15个中文字符.避免名称与关键字冲突.不要以数字和_开始.
   
8)字段设置default默认值
  在insert操作时,不为该字段指定值时,采用默认值写入.显式指定值时,指定什么写入什么,null也可以写入.
 ....类型 default 数值，。。。
default可以用于insert语句和update语句

 //删除主键约束
      alter table foo_2  drop constraint foo2_pk;

/创建一个序列,从1开始计数,递增加1
      create sequence 序列名 start with 值;

创建索引
    create index 索引名 on 表名(字段名)

//创建或更新某个视图
    create or replace view 视图名 as  select语句;

===========================================================
      //查看约束,使用user_constraints系统表
      desc user_constraints;
      select table_name,constraint_name,
         constraint_type from user_constraints;
      //查看用户表,使用user_tables系统表
      select table_name from user_tables;

======================================================


5、DML 语句
a.添加
   insert into 表名 (字段名1,字段名2...)values (值1,值2...);

b.更新
   update 表名 set 字段名=值,字段名=值 [where 字段名=条件值]

c.删除
   delete from 表名 [where 字段名=条件值]


6、TCL语句
  a、commit 事物提交

  b、rollback 事物回滚


7、DQL 语句
    select *或字段名...  from 表名  [where 字段名=条件值]

8、DCL 语句
  负责权限控制.(由DBA使用)
  create user...创建用户
  grant 授权
  revoke 收回权限




9、字符串函数
 --concat(s1,s2)或者s1 || s2
 
 --大小写转换
   upper(s):将s转成大写
   lower(s):将s转成小写
   initcap(s):将s中单词首字母大写

--过滤空格
    trim(s):过滤s中前后(左右)空格
    ltrim(s):过滤s中前面(左)空格
    rtrim(s):过滤s中后面(右)空格

--补位函数
   lpad(s,n,char) : 当s不够n位时,在s左边补充char字符.
   rpad(s,n,char):当s不够n位时,在s右边补充char字符.
  注意:如果s超出n位,采取截取方式保留n位.

 --截取函数
   substr(s,begin[,size]):对s进行截取;begin表示从
  第几个字符开始截取;size表示截取多少个字符
  substr(s,begin):
  如果begin为负数,表示从后向前取begin位字符  
  如果begin为正数,表示从begin位开始取到最后

--字符查找
   instr(s,s1[,begin,n]):在s中查找s1第一次出现的位置.
  begin表示从哪个字符开始查找;n表示第几次
  匹配.

 --计算长度
  length(s):计算s字符个数.(一个中文算一个长度)



10、数值函数
 -- round():四舍五入

 --trunc():截取
   trunc(n)一个参数取整,将小数截掉
   trunc(n,s)保留s位小数,将多余的截掉

 --mod():求余数

 --ceil(n):向上取整(取大于或等于n的整数)

 --floor(n):向下取整(取小于或等于n的整数)


11、日期操作
 *1)日期类型
    date:存储年月日小时分钟秒
    timestamp:存储年月日小时分钟秒毫秒时区
   上述类型定义时不需要指定大小.
 *2)日期关键字
    sysdate : 获取Oracle服务器系统当前时间
    systimestamp : 获取Oracle服务器系统当前时间

  to_date(s,format):将符合format格式的s字符串转成date
                              
  to_char(date,format):将date值按format转换成字符串
                                   
  to_timetamp(s,format):将s字符串转成timestamp类型
                                     
  format格式元素为yyyy-mm-dd hh:mi:ss

3）其它函数
--last_day(d):返回d日期中月份的最后一天的日期

--add_months(d,n):对d日期值的月份加n.n值可以为负数.    
                                                   
--next_day(d,星期几):从d日期开计算下一个星期几的时间日期.

--least(n1,n2):返回两个数值中小的那一个

--greatest(n1,n2):返回两个数值中大的那一个

--round(d):对日期的小时分钟秒部分进行舍入操作(12小时前的舍弃;
                  12小时后的日期加1)
                  
--trunc(d):对日期的小时分钟秒部分截取掉.
--extract函数
  extract(year from 日期值)//返回日期值的年
  extract(month from 日期值)//返回日期值的月
  extract(day from 日期值)//返回日期值的日
extract(hour from timestamp类型值)//返回小时
extract(minute from timestamp类型值)//返回分钟
extract(second from timestamp类型值)//返回秒
select extract(hour from systimestamp) from dual;


12、空值操作

--nvl(字段,值1):当字段值为null时返回值1;
                          字段值不为null返回字段值;
--nvl2(字段,值1,值2):当字段值不为null返回值1;
                          当字段值为null返回值2;

-------------面试题----------------
drop table xxx;//删除表结构和数据,释放空间
delete from xxx;//删除表数据,结构还在.可以回滚
truncate table xxx;//删除表数据,结构还在,
//效率高,数据不能回滚
-------------------------------------


13、主键

 a.主键特点
      --非空,不允许为null
      --唯一,不允许重复
      --企业中一个表肯定会定义一个主键
      --主键其实就是字段(可以由一到多个字段
        承担主键职责)
      --主键用于定位要操作的某一条记录
         (利用主键值查询,最多返回一条)
      --主键值一般不参与业务操作,不允许更新

 b.3种定义方法
    create table 表名（字段 类型 primary key,。。。。）
    create table 表名（字段 类型,..........，constraint 约束名 primary key(字段));
    //建完表之后添加主键
     alter table 表名 add constraint 约束名 primary key(字段);
    提示:如果字段数据违反主键规则,会创建失败.


14、查询语句

1).基本语句(最简单的结构)
  select *或字段名... from 表名;
  --*代表表中所有字段名
  --select 决定返回哪些列的值，结果集
  --from决定对哪些表查询
2).where子句
   select * from 表名 where 条件....;
  默认是对表中所有记录查询.如果需要提取一部分记录,需要用where指定过滤条件.满足指定条件的记录返回.

3).使用like条件(模糊查询)
   用于模糊条件值的查询;适用于字符串类型字段
   --%表示0到多个任意字符.
   --_下划线表示1个任意字符

 4)使用in和not in
    //字段值等于值1或者等于值2或者等于值3...
    字段名 in (值1,值2,值3...)
   //字段值不等于给出的值列表中
    字段名 not in (值1,值2,值3...)

5)使用between...and...
   字段名 between 最小值 and 最大值
   判断某个字段值在指定范围区间.即某个字段值大于等于?并且小于等于?

6)使用is null,is not null
      判断某个字段值是否为null;                                   


7)使用any和all条件
  --字段名 >any(值1,值2,值3...)
   含义:字段值大于指定集合中任意一个值,就算满足条件.(即工资大于最小的值)
 --字段名>all(值1,值2,值3...)
    含义:字段值大于指定集合中所有值,才算满足条件.(即工资大于最大的值)


8).select子句过滤重复记录功能
  select [distinct] *或字段名...from 表名;

   distinct作用是过滤重复的记录.两行记录如果各个字段值都相同才算重复;有一个字段不同都不算重复.

9).order by子句
   作用是将查询结果进行排序.
  select ...from ... order by 字段名... [asc或desc];
  --asc表示升序,默认值
  --desc表示降序
  --排序字段名可以写一个或多个

提示:desc或asc只对前面一个字段有效.


10).聚合函数(分组函数)
   max():取一组中的最大值
   min():取一组中的最小值
   avg():求一组的平均值
   sum():求一组的合计值
   count():统计一组中非空值的个数
 
  --空值不参与分组统计和计算.
  --一组执行一次返回一个结果.
    默认所有记录算一组

11).group by子句
  前面5个聚合函数,默认将所有结果当作一组进行计算.可以利用group by将结果分成若干小组,之后聚合函数会以小组为单位执行计算.

提示:SQL中使用了聚合函数,返回记录数由分组决定,有几组就会返回几行记录.

注意:当select后面使用了聚合函数,此外还有其他查询字段,其他查询字段必须在group by后面存在.聚合函数使用的字段可以不用写在group by后面.(Oracle必须按此规则编写,其他数据库例如MySQL有时可省略一两个字段)

12).having子句
   having用于使用聚合函数条件过滤.
   where子句中不能使用聚合函数.

----------完整SQL的SELECT结构-------------
select ...//显示哪些列信息
from ...//从哪些表查询
where ...//限定条件,不符合的过滤掉
group by ...//按指定字段分组
having ...//指定聚合函数过滤条件
order by ...//按指定字段排序
--------------------------------------------

13).使用别名
 在select,from,where ,order by子句中经常会使用
别名.
-----from后面使用别名
   from 表名 别名;
   注意:在遇到多表关联查询时才使用表别名.
----order by后面使用别名

-----having使用别名

14).使用子查询
  子查询可以用在where,having,from,select子句中
   注意:子查询语句需要用()括起来.
 
 1)where部分
   a.单行子查询
     例如使用>,<,>=,<=,=,<>条件时,子查询语句只能返回一个值.

   b.多行子查询
     例如使用in,not in,>any,>all等条件,子查询可以返回一列多行值.
 2)having部分
 3)from部分   //from后面子查询充当临时数据源
 4)select部分


=================== day   five========================

1.关联查询
  在查询时,有时候需要提取两个或两个以上数据表的字段值,就需要采用关联查询.
  1)笛卡尔积连接
     select *  from 表名1,表名2;
   --*代表from后面所有表的所有列
   --查询结果数量等于表1记录数*表2记录数
     (表1中每行记录都要和表2记录结合一次形成一行结果)

 *2)等值连接
    两个表连接,有等值关联的条件字段.
  --两个表必须有关联条件的字段
  --返回记录有两个表决定,条件是关联字段值相等
  //返回满足ta.id和tb.id字段值相等的记录

  --------新等值连接写法-------------
   语法: ...from 表a [INNER] JOIN 表b ON(关联条件)

  3)外连接
    *a.左外连接
     --外连接返回记录由主表决定,主表记录至少返回一次.另一张表没有对等记录补null值
     --left outer join关键字左边的表为主表
      .....from 表a left outer join 表b on(关联条件)

-----------旧的外连接语法-----------------
注意:带(+)方是补充表,另一方为主表

     b.右外连接
     与左外连接相似,采用right outer join ... on(...)结构.
    该关键字右边表为主表,以改表记录显示为主.
    
     c.全外连接
    --返回结果等于左外连接+右外连接,将重复记录去掉

  4)join...using,left outer join ... using语法
   作用与join...on一致. 当两个表的关联字段名字和类型都一致时,可以使用该语法结构.  

    注意:关联条件字段前面不能使用别名.

   5)自连接


2.Oracle特有操作
  1)rownum列  
   rownum通常被称为伪列.在表结构和select查询结果中Oracle会自动追加该列值.
   该值为1,2,3,4,5....连续序列.

提示： Oracle中需要借助rownum列进行分页查询,取出指定部分的记录.
     注意:利用rownum当过滤条件时,只能使用<和<=条件符号.不能使用>,>=,=(可以等于1,其他值不    支持)等条件不好.

  --先写最内部select,按要求字段排序
  --再写中间的select,将rownum列提取出来给个别名rn
  --最后写最外部的select和where条件



1.高级操作
 *1)分支判断函数
     a.decode函数
     格式:decode(字段或表达式,
                           值1,结果1,
                           值2,结果2,
                            ... ...
                           默认结果)
      当字段或表达式值等于值1返回结果1,等于值2返回结果2,都不满足返回默认结果.

     
  b.case when...then...else...end
  更适合做一些>,<或区间条件判断的函数处理.


2.集合操作
可以将多个select结果集进行合并,求差集等操作.
*a.union和union all(求并集)用于将两个select结果集合并.
   union all:合并之后允许重复记录
   union:合并之后过滤重复记录


b.intersect(求交集)
   用于返回两个select结果集相同的记录.

c.minus(求差集)
 用于返回第一个select有的,而第二个select没有的记录.



集合操作使用注意事项:
--两个select结果集列(字段)数必须相同
--两个select结果集列(字段)类型必须一致
--需要排序时,将order by写在后面的select语句中.


*3.排序(排列)函数(分析函数)
 row_number(),rank(),dense_rank()排序函数.
 使用格式如下:
  row_number() over(partition by 字段1 order by 字段2 [asc|desc])
  --将查询结果先按字段1进行分组
  --小组记录按字段2进行排序
  --然后利用排序函数生成编号.
 1)row_number()
   对记录进行排名,编号顺序且唯一.

---------------------------------
row_number()和rownum区别
--row_number()是Oracle10g开始提供的高级函数
--row_number()可以进行组内排序.而rownum是
  对所有记录排序.
-------------------------------


2)rank()
  对记录进行排名,编号允许重复,跳跃.

3)dense_rank()
  对记录进行排名,编号允许重复,连续不跳跃.


4.高级分组函数(聚合函数)
   使用格式:group by rollup(分组字段)
 1)rollup
    a.使用方法1    
     group by rollup(字段1);
     --在原查询统计结果后面添加一行合计
     --合计行记录字段1的值为null
     --结果会默认按select后面字段顺序排序

   b.使用方法2
     group by rollup(字段1,字段2)
    --在原查询结果上新增小计和合计记录
    --按字段1生成小计记录
    --在结果集最后生成合计记录
    --结果集按默认规则排序,根据select后面字段升序排列.

 2)cube
    --cube(字段),单个参数功能与rollup一样,都可以追加合计行记录
       
    --cube(字段1,字段2),多个参数,会分别按字段1和字段2进行小计.最后添加一行
      合计记录

=================day07===========================

-----------------------------------
1.请你介绍下Oracle数据库主要的对象有哪些
表,约束,视图,序列,索引,同义词
特点:独立存储和管理,可以单独创建,修改和删除.
函数:分为单行函数和分组函数(多行函数)
 单行函数:一行记录调用一次.例如字符函数,
                  数值函数,日期函数.
  分组函数:一组记录调用一次.例如max,min,avg
                 count,sum聚合函数


2.约束
   1)约束的作用
     负责对表中数据进行验证,通过验证才能进入表中,可以保证表中数据完整性和正确性.
   2)约束的类型
     **主键约束:非空且唯一
     *外键约束: 值必须符合在参考字段中存在
     *唯一约束:唯一性,不能重复
     检查约束:符合指定的检查条件
     -**非空约束:不能为空

3）非空约束
  a.建表时添加
     create table 表名（字段 类型 not null,。。。。）
  b.后期添加，通过修改实现
     alter table 表名 modify 字段 类型 not null;
  c.取消非空约束
     alter table 表名 modify 字段 类型 Null；

4)主键约束
 --非空且唯一
      --可以作用在一个字段,也可以多个
      --一个表只能创建一次主键(数量只能1个)

 a.建表时添加（系统会自动分配一个约束名）
  create table 表名(字段 类型 primary key,......);

  create table 表名(字段 类型，。。。。。，constraint 约束名 primary key(字段));

 b.后期添加
  alter table 表名 add constraint 约束名 primary key(字段名）

 c.删出主键
  alter table 表名 frop constraint 约束名；



 5)外键约束
 --可以为null
 --所参考的字段必须是主键或唯一约束的字段
EMP--deptno(外键,参考DEPT的deptno)
DEPT--deptno(主键或唯一约束)
 --外键字段值必须在参考字段列存在
 --外键约束数量可以使用多个
 --一个外键约束作用在一个字段上

 contraint name foreign key(value)
 


 6)唯一性约束
       --允许为null
       --有值时,不允许重复
       --作用在单个字段上

contraint name unique(name)


 7)检查约束
    --允许为null
    --有值,必须符合指定的检查条件
    --作用在一个字段上
    --可以为不同字段指定多个约束


=====约束总结=======
--检查,外键,唯一性约束,允许写入null值,
   写入null时,不会触发检测.
--主键一个表只能有一个主键约束
--外键约束必须参照一个主键或唯一约束列
===================



3.视图
   1)什么是视图
    --视图没有数据存储空间
    --视图只存储了一个select语句
    --对视图查询时,它需要去表中提取数据
   2)视图的好处
     将一个select查询语句存储,可以对视图查询.
    --简化复杂的查询操作
    --将重要和敏感信息隐藏
   3)视图的操作
        a.创建
    //创建或更新某个视图
    create or replace view 视图名
    as
    select语句;


4.序列操作
  1)什么是序列,有什么作用
     Oracle特有对象.相当于一个计数器.在Oracle中经常利用序列生成表的主键ID值.
  2)序列操作
    a.创建
      //创建一个序列,从1开始计数,递增加1
      create sequence 序列名;
      完整语法格式如下:
      create sequence 序列名
      [start with 1|integer] //指定计数起始点,默认1
      [increment by 1|integer] //指定递增量,默认1
      [maxvalue integer|nomaxvalue]//指定最大值
      [minvalue integer|nominvalue]//指定最小值
      [cycle|nocycle]//是否循环计数,默认不循环
      [cache size|nocache]//指定一次生成几个值放入缓存,默认nocache.
     
    b.使用
     //序列名.nextval将计数器当前值加1并返回
      insert into foo_1 (id,name)
         values (序列名.nextval,'scott');
    //查看foo_seq序列计数器的当前值
      select foo_seq.currval from dual;
    c.删除
      drop sequence 序列名;

5.索引
   1)什么是索引,有什么用?
    可以为某些字段创建索引,好处是可以提升
查询操作效率.
      select empno,ename
   from emp where ename='scott';
    当在ename上创建索引后,会提高查询效率.
  2)操作
   a.创建
    create index 索引名 on 表名(字段名);
   ---------示例-------
   create index emp_ename on emp(ename);
   
   b.删除
     drop index 索引名;

   3)索引使用注意事项
   索引虽然可以提升查询效率,但不是越多越好,
 因为它会耗费存储空间.
    a.适合建立索引的字段
      --主键,唯一性约束的字段
      --常出现在where部分当检索条件的字段
      --外键字段
      --两个表的关联字段
      --group by后面的字段
    b.不适合建立索引的字段
      --条件字段使用了函数
        where upper(ename) = 'SCOTT';
      --条件字段使用了表达式
        where sal+100>1200;
      --条件字段进行is null,is not null条件
         where comm is null;
      --条件字段使用了like
         where ename like '%A%';
      --条件使用了not或<>否定的判断条件
         where ename <> 'scott';
         where deptno not in(10,20)