Oracle 基础

1
2

select * from emp;
SELECT * FROM EMP;

1

select * from emp where ename='SMITH';

1
2

select upper('abc');  // 执行会报错
select upper('abc') from dual;  // 这样才可以执行

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

select
    *|指定的列名
from
    表名
where
    条件
group by
    分组
having
    条件
order by
    排序

1
2

select *|{[distinct column|expression [aliad],……]}
		from table;

1

select *|列名 from 表名

1
2
3
4

-- 查询所有
select * from emp;
-- 查询部分列
select ename,job from emp;

1

select 列名 别名, 列名 别名, …… from 表名;

1
2

-- 别名用法
select ename "员工名称", job "工作内容" from emp;

1

select distinct *|列名, ... from 表名;

1
2

-- 消除重复的数据
select distinct ename from emp;

1
2
3
4
5
6

--查询员工的年薪:空值是oracle里面最大的
select ename, sal*12 from emp;
-- 这里有点问题：如果comm是空的，那么所有数据都是空的
select ename, sal*12+comm from emp;
--nvl函数，判断空值用的 第一个参是值，第二参数：如果第一个参数是null，那么就用第二个参数来替换
select ename, sal*12+nvl(comm, 0) from emp;

1
2

-- 使用||拼接字符
select 'abc' || 'def' || 'ghi'  from dual;

1
2
3

select *|{[distinct column|expression [aliad],……]}
from table
[where condition(s)]

1
2
3
4
5
6
7
8

-- 查询薪资大于1500 
select * from emp where sal > 1500;

-- 查询薪资大于600，并且有奖金的员工
select * from emp where sal > 600 and comm > 0;

-- 查询薪资大于等于1500 并且小于等于3000
select * from emp where sal >= 1500 and sal <= 3000;

1
2

-- 范围查询： between xx and XXX;   包括边界值
select * from emp where sal between 1500 and 3000;

1
2
3
4

-- 列不为空
列名 IS NOT NULL
-- 列为空
列名 IS NULL

1
2

-- 查询薪资大于600，并且有奖金的员工
select * from emp where sal > 600 and comm is not null and comm > 0;

1
2
3
4

-- 列的值在某个范围内
列名 IN (值1, 值2, ....)
-- 列的值不在某个范围内
列名 NOT IN (值1, 值2,...)

1
2

-- in操作 ：在某个数据内
select * from emp where deptno in(10, 20);

1
2
3
4
5

-- 模糊查询 like
-- 查询名字中有个M 字符的
select * from emp where ename like '%M%';
-- 查询名字中第二个字母是M 字符的
select * from emp where ename like '_M%';

1
2
3
4

SELECT * |列名
    FROM 表名
    {WEHRE 查询条件}
    ORDER BY 列名 1 ASC|DESC，列名 2...ASC|DESC

1
2
3
4
5
6
7

-- 工资从小到大排列
select * from emp order by sal asc;
-- 如果存在多个排序字段可以用逗号分隔
select * from emp order by sal asc, hiredate desc;
-- 如果出现Null就要注意
select * from emp where sal > 400 order by comm asc nulls first;
select * from emp where sal > 400 order by comm desc nulls last;

1
2

select * from emp order by sal nulls first;
select * from emp order by sal desc nulls last;

1
2
3
4

select *
from (select rownum as xx,e.*
		from emp e) t
where xx >startIndex and xx <= maxResult;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- =======对员工表的工资排序【降序】之后，取出第二页每页3条记录
-- 先将表按工资进行排序
select * from emp
       order by sal desc,empno;
-- 以上面查询结果做为新表，增加rownum列
select rownum rnum,t.*
       from (select * from emp
       order by sal desc,empno) t;
-- 再以上面查询结果做为新表，以rownum做为查询索引
select *
   from (
        select rownum rnum,t.*
        from (
             select * from emp
             order by sal desc,empno
             ) t
        ) e
   where e.rnum>3 and e.rnum<=6;

1
2

SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名 1 别名
	{WHERE 限制条件ORDER BY 排序字段 ASC|DESC...}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- 多表查询（笛卡尔积）
select * from emp,dept;

-- 多表关联查询
select * from emp e,dept d where e.deptno=d.deptno;
select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;

-- 自关联查询
-- 需求：查询出每个员工的上级领导
-- emp表中的mgr字段是当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，可以使用mgr字段和empno来关联
select e.empno,e.ename,e.mgr,e1.empno,e1.ename from emp e,emp e1 where e.mgr=e1.empno;

-- 需求：在上一个例子的基础上查询该员工的部门名称
-- 只要在上一个例子基础上再加一张表dept的关联，使用deptno来做关联字段
select e.empno,e.ename,e.mgr,e1.empno,e1.ename,e.deptno,d.dname
       from emp e,emp e1,dept d 
       where e.mgr=e1.empno
       and e.deptno=d.deptno;
-- 需求：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e.empno,e.ename,e.mgr,e.deptno,decode(s.grade,
             '1','等级1',
             '2','等级2',
             '3','等级3',
             '4','等级4',
             '5','等级5',
             '?') as "员工薪级",
       d.dname,e1.empno,e1.ename,
             case s1.grade
               when 1 then '等级1'
               when 2 then '等级2'
               when 3 then '等级3'
               when 4 then '等级4'
               else '等级5'
             end
             as "领导薪级" 
       from emp e,emp e1,dept d,salgrade s,salgrade s1
       where e.mgr=e1.empno
       and e.deptno=d.deptno
       and e.sal between s.losal and s.hisal
       and e1.sal between s1.losal and s1.hisal;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 右连接，特点：右表记录一定会全部显示完整
-- 需求：当我们在做基本连接查询的时候，查询出所有的部门下的员工
-- 编号为 40 的部门下没有员工，但是要求把该部门也展示出来
select * from emp e,dept d where e.deptno(+)=d.deptno;
-- 等价于：
select * from emp e right join dept d on e.deptno=d.deptno;

-- 左连接，特点：左表记录一定会全部显示完整
-- 需求：查询出所有员工的上级领导
--  KING 的上级领导没有被展示，需要使用外连接把他查询出来
select e.empno,e.ename,e.mgr,e1.empno,e1.ename
       from emp e,emp e1
       where e.mgr=e1.empno(+);
-- 等价于：
select e.empno,e.ename,e.mgr,e1.empno,e1.ename
       from emp e left join emp e1
       on e.mgr=e1.empno;

1
2
3
4
5

select select_list
	from table
	where expr operator
		(select select_list
			from table);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- 单行子查询
select ename,job,sal from emp 
       where job=
             (select job 
              from emp
              where empno=7566)
       and sal>
              (select sal 
              from emp
              where empno=7782);
-- 使用聚合函数作为子查询条件
select deptno,min(sal) from emp
       group by deptno
       having min(sal)> 
              (select min(sal)
               from emp
               where deptno=20);
-- 需求：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工
select e1.ename,e1.job,e1.sal
       from emp e1
       where e1.sal>
             (select e2.sal from emp e2
             where e2.empno=7654)
       and e1.job=
             (select e3.job from emp e3
             where e3.empno=7788);
-- 需求：查询每个部门的最低工资和最低工资的雇员和部门名称
select e.ename,s.minsal,d.dname
       from emp e,
       (select e1.deptno,min(e1.sal) minsal
       from emp e1
       group by e1.deptno) s,
       dept d
       where e.deptno=d.deptno
       and e.sal=s.minsal;

1

exists(sql 查询语句)

1
2
3
4
5
6
7
8

select * from emp where exists (select * from dept where deptno=1);
-- 等同于：select * from emp where 1=2
select * from emp where exists (select * from dept where deptno=10);
-- 等同于：select * from emp where 1=1
-- 范例：查询有员工的部门
select * from dept d where exists (select * from emp e where e.deptno = d.deptno);
-- 使用in替换
select * from dept d where d.deptno in (select  distinct deptno from emp);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 先排序
select e.empno,e.ename,e.sal
       from emp e
       order by e.sal desc nulls last,e.empno asc;
-- 使用伪列rownum
select rownum,t.empno,t.ename,t.sal
       from (select e.empno,e.ename,e.sal
            from emp e
            order by e.sal desc nulls last,e.empno asc) t
       where rownum <= 3;

1
2
3
4
5
6
7
8

-- 先查询每个部门的平均薪水
select e.empno,e.ename,e.sal,t.avgsal
       from emp e,(select deptno,avg(sal) avgsal
              from emp
              group by deptno) t
       where e.sal > t.avgsal
       and e.deptno=t.deptno
       order by e.empno asc;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- TO_CHAR 函数对日期的转换成字符串
select e.hdate,count(*)
       from (select to_char(hiredate, 'yyyy') hdate from emp) e
       group by e.hdate
       order by e.hdate asc;

-- 把上面的当成一张表，这里的聚合函数是为了补全语法
select sum(t.dcount) as "Total",
       sum(decode(hdate,'1980',dcount)) as "1980",
       sum(decode(hdate,'1981',dcount)) as "1981",
       sum(decode(hdate,'1982',dcount)) as "1982",
       sum(decode(hdate,'1987',dcount)) as "1987"
from (select e.hdate,count(*) dcount
       from (select to_char(hiredate, 'yyyy') hdate from emp) e
       group by e.hdate) t;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

-- 建表语句和插入数据
-- 案例【1.1】查询学员列表，显示学员姓名，电话，班级名称
select stu.name "学员姓名",stu.tele "电话",cla.name "班级名称"
       from T_STUDENT stu,T_CLASS cla
       where stu.classid=cla.id;

-- 案例【1.2】查询学员成绩列表，显示班级类型名称，班级名称，学员姓名，课程名称，考试分数
select ct.name "班级类型名称",cla.name "班级名称",stu.name "学员姓名",course.name "课程名称",mark.score "考试分数"
    from T_STUDENT stu,T_CLASS cla,T_CLASS_TYPE ct,T_COURSE course,T_MARK mark
    where stu.classid=cla.id
    and cla.type=ct.id
    and stu.id=mark.studentid
    and course.id=mark.courseid
    and course.type=ct.id;

-- 案例【1.3】查询学员成绩列表，显示学员姓名，考试分数，如果该学员没有考试记录也要列出姓名
-- 方式1
select s.name "学员姓名",m.score "分数"
       from T_STUDENT s
       left join T_MARK m
       on s.id=m.studentid;
-- 方式2 oracle特有方式
select s.name "学员姓名",m.score "分数"
       from T_STUDENT s,T_MARK m
       where s.id=m.studentid(+);

-- 案例【1.4】统计每门课程的平均分
select courseid,round(avg(score)) "AVGSCORE" from T_MARK group by courseid;

-- 案例【1.5】上例的基础上显示课程名称
select m.courseid,c.name,round(avg(m.score)) "AVGSCORE"
       from T_MARK m,T_COURSE c
       where m.courseid=c.id
       group by m.courseid,c.name;

-- 案例【1.6】继续在上例的基础上统计平均分大于60且小于80的课程，并按平均分由高到低排序
select * from
       (select m.courseid,c.name,round(avg(m.score)) "AVGSCORE"
       from T_MARK m,T_COURSE c
       where m.courseid=c.id
       group by m.courseid,c.name) t
       where t.AVGSCORE > 60
       and t.AVGSCORE < 80
       order by t.AVGSCORE desc;

-- 案例【1.7】查询唐浩然的同班同学
-- 先查询所在班级
select classid from T_STUDENT
       where name='唐浩然';
-- 以上表做为基础条件，再查询学生表，
select * from T_STUDENT
       where classid=(
             select classid from T_STUDENT
             where name='唐浩然'
       )
       and name <> '唐浩然';

-- 案例【1.8】查询唐浩然的同班同学并且学历一样的同学
-- 查询学历
select edu from T_STUDENT
       where name='唐浩然';

-- 以上面查询学历做为子查询条件
select * from T_STUDENT
       where classid=(
             select classid from T_STUDENT
             where name='唐浩然')
       and name <> '唐浩然'
       and edu=(
           select edu from T_STUDENT
           where name='唐浩然');

-- 案例【1.9】查询每门科目最高分数
-- 方式1
select c.name,max(m.score) maxscore from T_COURSE c,T_MARK m
       where c.id=m.courseid
       group by m.courseid,c.name;

-- 方式2,子查询方式
select courseid,max(score) maxscore
       from T_MARK
       group by courseid;
-- 以上表做为子表
select c.name,m.maxscore
       from T_COURSE c,(
            select courseid,max(score) maxscore
            from T_MARK
            group by courseid) m
        where c.id=m.courseid;

-- 案例【2.0】分数在90分以上的同学
-- 查询90分以上所有学生的ID，并去重
select distinct STUDENTID from T_MARK where SCORE > 90;
-- 以上表做为子查询条件
select * from T_STUDENT
       where ID in (
             select distinct STUDENTID
             from T_MARK
             where SCORE > 90);

-- 案例【2.1】查询曾经缺考的同学
-- 查询有参数考试的学生
select distinct STUDENTID from T_MARK;
-- 以上表子查询条件
select * from T_STUDENT
       where ID not in (
             select distinct STUDENTID
             from T_MARK);

-- 案例【2.2】查询成绩排名表的第二页（按每页5条数据）
-- 先按成绩排名查询
select * from T_MARK
       order by score desc;
-- 以上面查询的表为基础，添加rownum
select rownum "R",t.* from (
       select * from T_MARK
       order by score desc) t;
-- 以上表为基础，进行分页查询
select * from (
       select rownum "R",t.* from (
              select * from T_MARK
              order by score desc) t)
       where r>5 and r<=10;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 并集
select *
from emp
where sal>1500 or deptno=20;

-- or 关键字效率会很低，全表查询，索引用不了
-- 方式2
select * from emp where sal > 1500
union -- 使用union all包括重复部分
select * from emp where deptno = 20;

1
2
3
4
5
6
7
8
9

-- 交集
-- 方式1
select *
from emp
where sal>1500 and deptno=20;
-- 方式2
select * from emp where sal > 1600
intersect
select * from emp where deptno = 20;

1
2
3
4
5

-- 差集
select * from emp where to_char(hiredate,'yyyy')='1981' -- a
minus
select * from emp where job='MANAGER' or job = 'PRESIDENT'; -- b
-- 如果a minus b,a 中没有在b 中的数据，全部显示

1
2
3

select * from 表名
start with 条件一 --开始查询位置
connect by 条件二  --指定查询当前的关系

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查询员工的领导
select * from emp
start with empno=7369
-- prior:从哪个方向进行检索
connect by empno=prior mgr;

-- 查询领导的下属是那些
select * from emp
start with empno=7566
connect by mgr=prior empno;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- 字符串拼接(只能是两个拼接)
select concat('abc','zxy') from dual;
-- 字符串拼接（三个拼接）建议使用||
select concat(123,concat('abc','zxy')) from dual;

-- 大小写
select lower('MOON') from dual;
select upper('moon') from dual;

-- 查询用户名称小写的
select lower(ename) from emp;

-- 字符串长度
select length('abc123') as "字符长度" from dual;

-- 截取字符串
/*
   substr(v1,p1,p2)
           v1:要截取的字符串
           p1: 从哪里开始截取,0 和 1 都是一样的，都是开始下标
           p2: 截取的长度
*/
select substr('moonzero',0,3) from dual;
select substr('moonzero',1,3) from dual;
select substr('moonzero',2,3) from dual;

-- 字符串替换，第一个参数是源字符串，第二个参数被替换的字符串，第三个是替换字符串
select replace ('moonzero','oo','00') from dual;

1

round(数字, 保留的小数位)

1

trunc(数字, 保留的小数位)

1

mod(被除数，除数)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

--操作数字
-- 四舍五入
--只保留整数位(不写参数，默认只保留整数位)
select round(58.8888) from dual;
-- 第二个参数，保留两位小数
select round(58.8888, 2) from dual;
select round(58.8838, 2) from dual;
-- 直接截断
select trunc(58.8888, 2) from dual;
-- 求余
select mod(1200, 500) from dual;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- sysdate : 当前数据库所在服务器的时间
select sysdate from dual;

-- 查询员工入职的天数
select * from emp;
select ename,round(sysdate - hiredate) "入职天数" from emp;
-- 在上面的基础上，算一下入职的周数
select ename,round((sysdate - hiredate)/7) "入职周数" from emp;
-- 计算入职的月数
select ename,round(months_between(sysdate,hiredate)) "入职月数" from emp;
-- 获得几个月后的日期
select add_months(sysdate, 3) from dual;

1

to_char(date, 'format_model')

1

to_char(number, 'format_model')

1

to_number(char[, 'format_model'])

1

to_date(char[, 'format_model'])

1
2
3
4
5
6
7
8
9

-- 把时间转成字符串,10以下的月前面被被补了前导零，可以使用 fm 去掉前导零
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(sysdate, 'fmyyyy/mm/dd') from dual;
--  把字符串转成时间
select to_date('2018/9/10', 'yyyy/mm/dd') from dual;
-- 把数字转成字符
select to_char(123) from dual;
-- 把字符串转成数字,转换后可以进行运算
select to_number('123')+to_number('321') from dual;

1
2
3
4
5

-- 如果expr1为空，则输出expr2。前后的数据类型要一致。
nvl(expr1, expr2)
nvl2(expr1, expr2, expr3)
nullif(expr1, expr2)
coalesce(expr1, expr2,……, exprn)

1
2

-- nvl（p1,p2） ,如果p1为空，就输出p2,前后的数据类型要一直
select ename,nvl(comm, 0) from emp;

1
2
3
4
5
6

case expr when comparison_expr1 then return_expr1
		[when comparison_expr2 then return_expr2
			……
		when comparison_exprn then return_exprn
		else else_expr]
end

1

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 条件表达式
select ename,deptno from emp;
-- 使用DECODE 函数
select ename,deptno,
       decode(deptno,10,'开发部',20,'销售部',30,'运维部','大Boss')
       from emp;
-- 使用CASE 表达式
select ename,deptno,
       case deptno
         when 10 then '开发部'
         when 20 then '销售部'
         when 30 then '运维部'
         else '大Boss'
       end
       from emp;

1
2
3
4
5

select *|{[distinct column|expression [aliad],……]}
	from table
	[where condition(s)]
	[group by group_by_expression]
	[order by column]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 计算所有员工数
select count(*) from emp;

-- 把所有员工的工资加起来
select sum(sal) from emp;

-- 计算部门的平均工资
select round(avg(sal),2),decode(deptno,10,'开发部',20,'销售部',30,'运维部','大Boss')
       from emp
       group by deptno;

-- 计算部门的平均工资大于2000
select round(avg(sal)),decode(deptno,10,'开发部',20,'销售部',30,'运维部','大Boss')
       from emp
       group by deptno
       having avg(sal) > 2000;

1
2
3
4
5
6

-- 创建表空间
create tablespace moonzero
datafile 'c:\test.dbf'
size 100m
autoextend on
next 10m

1
2
3
4

-- 创建用户
create user moon
identified by moon
default tablespace moonzero

1

grant dba to 用户名

1

revoke connect,resource from 用户名;

1

select sys_guid() from dual;

1
2

create table [schema.]table
		(column datatype [default expr][,…]);

1
2
3

create TABLE table
		[(column, column,……)]
as subquery;

1
2
3
4
5
6
7
8
9

-- 创建表
create table person(
       pid number(10),
       name varchar2(10),
       gender number(1) default 1,
       birthday date
);
-- 插入数据，插入数据后，记得要点击提交
insert into person values (1,'剑圣',1,to_date('2018-2-2', 'yyyy-mm-dd'));

1

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)

1

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)

1

ALTER TABLE 表名称 RENAME COLUMN 列名 1 TO 列名 2

1

alter table 表名称 drop column 列名

1
2
3
4
5
6

-- 在 person 表中增加列 address
alter table person add(address varchar2(10));
-- 把 person 表的 address 列的长度修改成 20 长度
alter table person modify(address varchar2(20));
-- 修改列名
alter table person rename column ddd to address;

1

DROP TABLE 表名;

1

create table 新表名 as (select * from 要复制的表名);

1

constraint person_pk_pid primary key(pid)

1
2
3
4
5
6
7

create table person(
       pid number(10),
       name varchar2(10),
       gender number(1) default 1,
       birthday date,
       constraint person_pk_pid primary key(pid)
);

1

constraint person_name_uk unique(name)

1

constraint person_gender_ck check(gender in (1,2))

1
2

constraint order_detail_order_id_fk foreign key(order_id)
	references orders(order_id) on delete cascade

1

insert into 表名[(列名 1，列名 2，...)] values (值 1，值 2，...)

1

insert into 表名 values(值 1，值 2，...)

1
2
3
4

-- 全部修改：
UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....
-- 局部修改：
UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；

1
2
3
4
5

-- 在 update 中使用子查询：
-- 例如：给 NEW YORK 地区的所有员工涨 100 员工资
update emp set sal=sal+100
       where deptno in (
             select deptno from dept where loc='NEW YORK');

1
2
3
4
5
6
7

MERGE <hint> INTO <table_name> -- 表名称
USING <table_view_or_query> -- 表查询信息
ON (<condition>) -- 条件
WHEN MATCHED THEN <update_clause> -- 更新操作
DELETE <where_clause> -- 删除操作
WHEN NOT MATCHED THEN <insert_clause> -- 插入操作
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 例子1：
MERGE INTO USERINFO a
USING (
  SELECT '1' as UNAME
  FROM dual
  ) b
ON (a.UNAME = b.UNAME)
WHEN MATCHED THEN
  UPDATE SET a.ADDR = 'ddd', a.UTC = 1234567
WHEN NOT MATCHED THEN
  INSERT (a.ADDR, a.UTC, a.UNAME) VALUES ('aaa', 7654321, '1');

-- 例子2：
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
    INSERT (a,b) VALUES(T2.a,T2.b);

1

delete from 表名 where 删除条件;

1
2
3
4
5
6
7
8

-- 事务保存点：savepoint
-- savepoint sp1;
insert into person values(3,'幽鬼',2,null,'dota2');
-- 设置保存点,保存点之前提交，保存点之后的回滚
savepoint sp1;
insert into person values(2,'插入不进去的',3,null,'xxoo');
rollback to sp1;
commit;

1
2
3
4
5

create [or replace] [force|noforce] view视图名称
	[(alias[, alias]...)]
as 查询语句
[with check option [constraint constraint]]
[with read only [constraint constraint]];

1
2
3
4

-- 创建视图（权限不够，可以使用system开通全部特权，是系统最高权限）
create view empvd20 as select * from emp t where t.deptno=20;
-- 视图创建完毕就可以使用视图来查询（这个名字的虚表），查询出来的都是 20 部门的员工
select * from empvd20;

1

create or replace view 视图名称 as 查询语句

1

create or replace view 视图名称 as 查询语句 with read only

1
2
3
4
5
6
7

create sequence sequence(序列名称)
		[increment by n]		-- 步长值，每次增加的数值
		[start with n]		-- 初始值
		[{maxvalue n | nomaxvalue}]		-- 使用最大值，必须循环
		[{minvalue n | nominvalue}]
		[{cache n | nocache}]		-- 缓存n个值，默认值20
		[{cycle | nocycle}];		-- 循环，循环结束都从1开始

1

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

1

create index 索引名 on 表名(列名1, 列名2)

1
2

create index idx1 on 表名(a列,b列);
create index idx1 on 表名(b列,a列);

1
2
3
4

-- 范例：给 person 表的 name 建立索引
create index pname_index on person(name);
-- 范例：给 person 表创建一个 name 和 gender 的索引
create index pname_gender_index on person(name,gender);

1
2
3
4

select * from person where name='11';  会触发索引
select * from person where name ='11' and address ='22'; 会触发
select * from person where name ='11' or address ='22'; 不会触发
select * from person where address ='22'; 不会触发

1

create [public] synonym 同义词名称 for 用户.表名;

1

create public synonym emp for scott.emp;

1