Oracle数据库(上)

一. DDL,DML,DQL,DCL，事务

1.DDL（数据定义语言，Data Definition Language）—创建表，修改表，删除表 create alter drop

2.DML（数据操作语言，data manipulation language）—insert插入 update更新 delete删除表中数据

3.DQL（数据查询语言，Data QueryLanguage）—select查询

4.DCL（数据控制语言，Data Control Language） —设置、更改用户或角色权限grant,deny,revoke

5.事务：一组相关的DML语句（可以回滚），一个DDL语句或者一个DCL语句或者一个TCL语句（不能回滚）

事务的特征：（ACID）原子性，一致性，隔离性，持久性

二．Commit,rollback，savepoint

1.commit之后不能rollback

2.DDL，DQL，DCL操作会自动提交，不能rollback

3.DML在未commit之前可以rollback

4.savepoint 保存点名：rollback to 保存点名

三．特殊比较运算符

1.between…and…(not between…and…)

eg: 显示工资在2000到4000之间的员工姓名，工资
select ename,sal from emp where sal between 2000 and 4000;

2.in(not in)

eg:显示姓名为"SMITH"或为"KING"的员工的员工姓名，参加工作时间，工作
select ename,hiredate,job from emp where ename in('SMITH','KING')

3.like(not like)

eg: 显示出姓名中倒数第二个字母为"R"的员工姓名
select ename from emp where ename like '%R_';

4.is null(is not null)

eg: 查找emp表中comm为空的记录
select * from emp where comm is null;

5.<>不等于

四．分组函数,Order by,group by…having

1.分组函数：SUM、AVG、MIN、MAX、COUNT

2.order by排序（desc降序，asc升序），默认升序

3.group by分组：SELECT子句中出现的非分组函数列必须在GROUP BY子句
中出现

4.group by…having：having可比较分组函数，where 后不可比较分组函数

格式:

select列名, 组函数(列名)
from 表名
[where 条件]
[group by 分组列]
[having 组函数表达式]
[order by 列名];

五．函数

1. 字符函数

• lower(c)---全小写

• upper(c)---全大写

• initcap(c)---首字母大写

• concat(c1, c2)---连接两个值,等同于||

• substr(c,n1[,n2])---从n1字符开始长度为n2的子串

• length(c)--- 取字符长度

• instr(s1,s2,[,n1],[n2])---返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1

• lpad(s1,n1,s2)---返回s1被s2从左面填充到n1长度

• rpad(s1,n1,s2)---返回s1被s2从右面填充到n1长度

• replace(s1,s2,s3)---把s1中的s2用s3替换

• trim(s1 from s2)--- 去除s2头部或尾部（头尾）的s1字符

2. 数值函数

• round(c,n)---将c四舍五入到小数点后的第n位。

• trunc(c,n)---将c截取到小数点后的第n位。

• mod(m,n)---m除以n得到的余数

3. 日期函数

• 常用日期格式—(YYY：4位年,YY：2位年(无世纪转换),RR：2位年份(世纪转换）,D：星期的第几天,DD：月中的第几天,DDD：年中的第几天,AM或PM：上下午,HH 或HH12或HH24:时,MI：分,SS：秒)

• sysdate—系统时间

• months_between(d1,d2)—返回d1到d2间隔的月数

• add_months(d,n)—在指定日期基础上加上相应的月数

• next_day(d,’星期一’)—返回d日期的下一个星期一的日期

• last_day(d)—返回指定日期当月最后一天的日期

• round(date[,’fmt’])—将date按照fmt指定的格式进行四舍五入，默认为‘DD’，格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS

• trunc(date[,’fmt’])—将date按照fmt指定的格式进行截取，默认为‘DD’

• extract(MONTH from d)—从d中取出年、月、日

4.转换函数

• to_char(date|number [,’fmt’])‘fmt’指的是需要显示的格式，
格式码：9：一位数字，0：一位数字或前导零，$：显示为美元符号，L：显示本地货币符号， .：小数点,：千位分割符；

• to_number

• to_date

5. 通用函数

• 空值相关函数

nvl(c1, c2)—c1为空时转换为c2

nvl2(c1,c2,c3)—c1不为空输c2，为空输出c3

nullif(c1,c2)—c1、c2不相等输出c1，相等返回空值

coalesce(c1,c2,…,Cn)—返回第一个不为空的参数

• 条件处理函数：

case表达式

eg:select ast_name, commission_pct,
    (case 列名
    when 0.1 then '低'
    when 0.2 then '中'
    when 0.3 then '高'
    else '无'
    end) 别名
    from ployees where commission_pct IS NOT NULL order by last_name;

decode(字段, 条件1,结果1[,条件2,结果2…,[,缺省值])

eg:select last_name, commission_pct,
    decode(列名,0.1,'低',0.2,'中',0.3 , '高','无') 别名
    from employees where commission_pct IS NOT NULL order by last_name;

六．外连接，内连接，Union

1.内连接：t1 inner join t2 on 条件

2.左外连接：t1 left outer join t2 on—返回t1全部值

右外连接：t1 right outer join t2 on—返回t2全部值

Select e.last_name, e.job_id, e.department_id,
d.department_name
from employees e
right outer join departments d
on e.department_id= d.department_id;

全外连接：t1 full outer join t2 on—返回t1，t2全部值

3.union：集合1 union 集合2—返回两个集合的并集

Union all—返回两个集合的并集(不去除重复部分)

七．子查询

1.多行子查询

• IN：在给定集合中

• ANY：表示任意的

>any—大于任意一个

=any—等于任意一个，相当于in

八．Merge

1.语法结构：

Merge into table_name [t_alias]
using { table | view | subquery } [t_alias]
on ( join condition )
when matched then
pudate set
col1=col1_val[,col2=col2_val]
when not matched then
insert（column_list）
values（column_values）;

2.说明：

into 子句：指定更新或插入的目标表

– using 子句：指定更新或插入的数据源，它可以是表、视图、子查询

– on 子句：合并操作的条件判断语句

3.eg:

通过子查询，修改每个班级的班级人数字段的数据
merge into class c using student2 s on(s.studentcid=c.classid)
when matched then 
update set c.ccount=(select count(1) from student2 group by s.studentcid);

九．正则表达式

Oracle使用正则表达式离不开这4个函数：

1.regexp_like(c1，’zz’)

2.regexp_substr

3.regexp_instr

4.regexp_replace