Oracle学习---从1开始

(注，本文中以oracle自带的表，员工表emp ,部门表 dept 测试相关语法)

一.Oracle支持的数据类型：

1.字符型 char 定长  最大2000个字节

对于char(10)类型，存放“张三”的情况是这样的 ：前四个字节存放“张三”，后补6个空格；

2.varchar2 变长 最大4000个字节

对于varchar2(10)类型，存放“张三”的情况是这样的：只占4个字节；

3.clob(character large object) 字符大对象 最大4G；

4.数字型 number 取值范围：-10的38次方到10的38次方  可以表示整数和小数；

number(M,N)  表示一共占M列，其中小数占N列，整数剩M-N列；

number(M)  表示一个M位整数；

5.日期类型 date 包含年月日小时分秒

                 timestamp  包含年月日小时分秒，以及毫秒；（该类型是oracle对date数据类型的扩展）

  oracle中默认的日期格式是‘DD-MON-YY’; DD表示日期，MON表示月，这个月必须是月份数字+‘月’，YY表示年，如1990年的90；

修改日期的默认格式：alter session set nls_date_format='yyyy-mm-dd' ,修改后，我们就可以使用熟悉的格式添加日期类型；

二.oracle表的管理

1.修改表的名字：rename 表名 to 新表名；

2.添加一个字段：alter table 表名 add 列名 类型；

3.修改字段的类型：alter table 表名modify 旧列名 新类型

4.修改字段的名字：alter table 表名 rename column 旧列名 to 新列名

5删除一个字段：alter table 表名 drop column 列名

6查询：select 字段名 from 表名

           select * from 表名

           select * from 表名 where 条件

7.添加数据：insert into 表名 values(所有字段的值列表)；

                  insert into 表名(字段列表) values(对应字段的值列表)

8.修改数据：update 表名 set 字段名 = 值

                   update 表名 set 字段名 = 值，字段名 =值......

                   update 表名 set 字段名 = 值 where 条件

9.删除表：delete from 表名

               delete from 表名 where 条件

10.截断表，删除大量数据时，比delete的速度快

     truncate table 表名  注意truncate table 无法添加 where 条件

11.查看表结构：desc dept;

12.给字段起别名：select enamel as ‘姓名’，sal '月薪' from emp;

13.与或非：and or  not

14.日期也是可以参与比较的

15.模糊查询：%  _

16.消除重复行：select distinct deptno,job from emp;

17.排序：order by 字段 asc/desc

              主要条件，次要条件

             排序的列不用出现在字段列表中

18.使用算数表达式；

19.DUAL表的使用：select 1+1 from dual;

20.null值与任何值计算都得null;

21.nvl函数的使用：nvl(comm,0) 表示如果comm是null，则函数返回0，否则返回comm本身；

22.字符串拼接：select ‘aa’||'bb'||'cc' from dual;

23.联合查询：

<1>.union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。UNION 命令只会选取不同的值。

<2>.union all

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

<3>.intersect(交集)

<4>.minus(差集)

三.oracle高级查询

1.聚合函数：max

                 min

                avg

                 sum

                count

2.分组查询：group by 

                  having

              对分组的结果排序

             按照两列分组

关于分组要注意的地方：（1）.聚合函数只能出现在以下3个地方

   <1>.字段列表中；<2>.having子句中；<3>.order by 子句中；

（2）.group by ,having,order by子句如果同时出现在一个查询语句中，则先后顺序是：<1>.先写group by; <2>.再写having;<3>再写order by;

根据哪一个字段分组，查询时，就只能查询哪个字段，或聚合函数；

3.连接查询(多表连接查询，多表查询)

笛卡尔积（数据量大的时候不建议使用）：

 

内连接语法：

外连接语法：

自连接：一张表，自己连接自己就是自连接；

4.子查询：按子查询出现的位置分为where型子查询，from 型子查询；

                按子查询返回的结果分为标量子查询(返回一行一列的子查询)，列子查询(返回多行一列结果的子查询)a) all,any,in,not in ;b)>all 比最大的大，<all比最小的小，>any比最小的大，<any比最大的小；

行子查询（一行多列），表子查询（多行多列）

行子查询例子：

表子查询例子：

5.分页查询：oracle查询中，可以使用rownum，表示行号

用查询结果，创建新表：

也可以在创建新表的同时，给新表的列指定别名

四.oracle函数

1.字符函数：lower （小写），upper（大写），length（长度），substr(char,off,len)或sbustr(char,off);

例子：以首字母大写，其余字母小写的方式显示所有员工的姓名.

2.数学函数

round(number,accuracy)  函数用于把数值字段舍入为指定的小数位数;

trunc trunc(123.45,1)截断数字；truncate（sysdate,'mi'）日期精确到分(不能精确到秒)；

mod() 函数是一个求余函数,其格式为: mod(nExp1,nExp2),即是两个数值表达式作除法运算后的余数。

floor() :floor(x)，也写做Floor(x)，其功能是“向下取整”，或者说“向下舍入”，即取不大于x的最大整数（与“四舍五入”不同，下取整是直接取按照数轴上最接近要求值的左边值，即不大于要求值的最大的那个值）。

ceil() 

返回大于等于N的最小整数值
SELECT CEIL(N) FROM DUAL

ceil(4.5)  = 5
 ceil(4.1)  = 5

abs() 绝对值函数。

power(number,power) 返回数字乘幂的计算结果,其中参数number表示底数；参数power表示指数。两个参数可以是任意实数，当参数power的值为小数时，表示计算的是开方；当参数number取值小于0且参数power为小数时，POWER函数将返回#NUM!错误值。

sqrt()用来计算一个非负实数的平方根。

oracle的随机函数

3.日期函数

oracle的日期函数格式默认为dd-mon-yy,如‘’21-12月-2012”

sysdate 系统时间：1>.查询员工入职多少天

add_months（日期，增量）：1>.查询入职超过10年的员工

months_between(日期，日期) 计算两个日期之间的月份差

last_day(日期)，返回指定日期所在月份的最后一天：查询在当月倒数第8天入职的员工

4.日期与字符串之间的转换函数

to_char 将日期格式化为字符串

yy  2004>04

yyyy 4位年

mm 2位月

dd 2位天

hh24 24小时制

hh12 12小时制

mi  分钟

ss 秒

 to_date 将字符串解析为日期

5.事务

oracle中的事务是隐式事务，即执行第一个DML语句时，自动开启事务。

提交事务 commit

回滚事务  rollback   rollback  to（保存点名）

事务保存点 savepoint 

事务隔离级别：oracle只支持两个隔离级别，如下：

                        set     transaction     isolation level     read  committed

                        set    transaction   isolation level      serializable

所以oracle不支持脏读，且oracle的事务隔离级别为 read commit时，防止了脏读，但仍然有不可重复读和和幻读的问题。要避免这两个问题，需要把隔离级别设置为 serializable。

锁：共享锁，排他锁

oracle中，select不会添加任何锁； inset ，delete，update会添加排他锁；

添加共享锁 ： lock table 表名 in share mode

让select 添加排他锁：select 字段列表 from 表名  for update

                                 select 字段列表 from 表名 ，表名 for  update of 表名 ，列名

表锁

行级锁

一，数据完整性

数据完整性用于确保数据库中的数据遵从一定的逻辑规则。

1.约束

约束用于确保数据库数据满足特定的规则。

在oracle中约束包括：not null 如果在列上定义了not null ，那么当添加数据时，必须为列提供数据；

                                  unique  当定义了唯一约束后，该列的值是不能重复的，但是可以为null；

                                  default 默认值约束；

                                 primary key  当定义主键约束后，该列不能重复也不能为null，一张表只能有一个主键，复合主键；

                               foreign key 用于定义主表和从表的关系

                                     check 自定义约束

建表的同时，就给字段添加约束（列级定义）：

表建好后追加约束：

删除约束：

 

转载于:https://my.oschina.net/u/3897028/blog/1976032