SQL-- 语句 oracle

恢复和备份==================

EXP

exp text01/1234 file="c:\t01.dmp"

IMP

imp text01/1234="c:\t01.dmp" full=y

 

11G中有个新特性，当表无数据时，不分配segment，以节省空间

 

 

-- 删表

truncate是DDL语言

--表有约束时无法truncate，

--1、先drop掉造成约束的表2、再去truncate要删的表内容

 

 

在mysql和oracle里执行sql文件？

 

一、在mysql里执行sql如何进行？导入 source +文件

 

二、oracle里，执行sql文件，@+文件路径

 @d:\test007.sql

 执行完成之后，多了几个字段：奖金、导师编号========使用工具写

 首选项-用户界面-字体-编辑器-改变字体

 

在pl/sql 里修改数据

先：

select * from dept   --- for updata

把锁点开，就可以通过工具修改数据

 

select * from dual --哑表

 

-- where 后‘in’ 或 ‘=’ --in后面有多个值可选/=后面只能一个值

select t.tno, t.tname from TEACHER t where t.gender = '女'

 

alter table teacher rename column gendar to gender

 

-- like “李%”李开头任意个字名字

-- like “李**”李开头三个字名字

-- like “%李%”名字中有李

 

select t.tno, t.tname, t.tid from TEACHER t 

where t.gender = '女' and t.tname like '李%'

 

select * from teacher t where t.sal >=10000 and t.sal < 20000

 

between and  [ ] --oralce里是闭区间

 

-- order by sal desc 降序； 默认为ASC 升序

select * from teacher where job='研发' or job='讲师' 

order by sal desc 降序； 默认为ASC 升序

 

select * from dept

 

字符串拼接 ||

--要求查询后输出的语句例如：insert into dept values(10,'研发部','北京')；

--在oracle里  两个单引号“ ' ”输出显示一个引号“ ' ”  

select 'insert into dept values(',deptno,dname,loc,');' 

from dept

 

select 'insert into dept values('||deptno||dname||loc||');'

from dept

 

select 'insert into dept values('||deptno||','||dname||','||loc||');'

from dept

 

在oracle里两个单引号表示一个引号

select 'insert into dept values('||deptno||','''||dname||''','''||loc||''');'

from dept

 

 子查询

select t.tname from teacher t 

where t.gender='男' 

and t.deptno in 

(select d.deptno from dept d where d.dname='招生部')

 

select t.tname,

       t.deptno,

       (select dname from dept where dept.deptno = t.deptno) as deptname

  from teacher t

 where t.deptno in (select deptno

                      from dept

                     where dept.deptno = t.deptno

                       and dept.dname in ('招生部', '人力部'))

 

 连接查询

 

--招生部门所有男老师的姓名

select teacher.tname, teacher.deptno, dept.dname

  from teacher

 inner join dept

    on teacher.deptno = dept.deptno

 where dept.dname = '招生部'

   and teacher.gender = '男'

 

--内连接--只有有部门编号的老师才显示姓名

select teacher.tname, teacher.deptno, dept.dname

  from teacher

 inner join dept

    on teacher.deptno = dept.deptno

 

--外连接--左连接--所有老师的部门编号    

select teacher.tname, teacher.deptno, dept.dname

  from teacher

 left join dept

    on teacher.deptno = dept.deptno

 

--自连接--所有老师的姓名以及其导师

select t1.tname, t2.tname as 导师

  from teacher t1

  left join teacher t2

    on t1.mgrno = t2.tno

 

--自连接和+外连接--所有老师的姓名,部门以及其导师

select t1.tname, t2.tname as 导师, dept.dname

  from teacher t1

  left join teacher t2

    on t1.mgrno = t2.tno

  left join dept

    on t1.deptno = dept.deptno;

    

--列出所有的姓名和出生日期

select t.tname,t.birthdate from teacher t     

 

 

----单行函数----

 

--round 1表示小数点后一位，-1 表示十位

select round(sal,1) from teacher

select round(sal,-1) from teacher

 

--字符截取，trim去掉空格

select substr(tid, 1, 2), 

substr(tid, 0, 2), 

substr(tid, 1),length(trim(tid))

 from teacher

 

--查当前系统时间--通过dual表

select sysdate+1/4 from dual

 

--to_date  等转换

--例如：to_char将111111转换成111,111格式 

------- to_char把时间转换成yyyy-mm-dd hh24:mi:ss格式

select to_number('112'),to_char(111111,'999,999'), 

to_date('1986-12-12','yyyy-mm-dd'),

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

from dual

 

-- nvl(t.comm,0)如果t.comm为null 则显示0；

-- nvl2(t.com,comm+1000,0)如果t.comm不为null，则comm加上1000；为null则显示0；

-- decode(t.comm,null,0,2300,2500,t.comm)如果t.comm为null则显示0，如果comm为2300则显示2500，除此以外显示其本来的值；

select t.sal,t.comm,

(t.sal+nvl(t.comm,0)) gz,

(t.sal+nvl2(t.comm,comm+1000,0)) gz2,

decode(t.comm,null,0,2300,2500,t.comm) gz3

from teacher t 

 

 

----聚合函数----

 

select deptno,sum(sal),avg(sal),count(0),max(sal),min(sal) 

from teacher group by deptno

 

--查看部门员工超过10人以上的数据

select deptno, sum(sal), avg(sal), count(0), max(sal), min(sal)

  from teacher

 group by deptno

having count(*) > 10 -- and sal>1000 这是错误的，having后面必须是聚合函数；

 

 

----分析函数----

--根据部门分组，以工资来排名；

--注意1、rank() 2、dense_rank() 3、row_number() 的区别

--ROW_NUMBER 返回连续的排位，不论值是否相等

--RANK 具有相等值的行排位相同，序数随后跳跃

--DENSE_RANK 具有相等值的行排位相同，序号是连续的

select tname,deptno,

rank() over(partition by deptno order by sal) rank,

dense_rank() over(partition by deptno order by sal) dense_rank,

row_number() over(partition by deptno order by sal) row_number

from teacher

 

----联合查询----

--UNION（重复的只显示1份）\UNION ALL（所有的都显示）:返回各个查询的所有记录；

--INTERSECT:返回两个查询共有的记录；

--MINUS:返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录；

 

 

 

----Oracle伪列----

--ROWID 是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID 伪列快速地定位表中的一行

--ROWNUM

1、rownum是基于结果集（所以要先获得结果集，再使用如where rownum <= 5的条件句）；

2、从1开始（所以最直接的where条件不能 为 rownum >= 5 或者 rownum=5 ）；

 

--ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数

 

select rowid,rownum,teacher.* from teacher 

 

--查前5个数据

select rownum,tname from teacher where rownum <= 5

 

--查第6到10个数据

--错误的：select rownum,tname from teacher 

where rownum >= 5 and rownum <= 10 因为rownum是基于结果集的--

 

--要用下面这样的查询（思路：分层过滤，要需要用别名）：

select * from 

(select rownum as rn,tname from teacher where rownum<=10)t 

where t.rn >= 5

 

select tno,rownum from teacher order by tno

--分页

--第一层:加限制条件

--第二层:给结果集过滤最大的范围(10条)

--第三层:给结果过滤出最小的范围(从第几条开始)

 

--分析函数 rank 可以用来做分页

SELECT *

FROM (SELECT teacher.*,

ROW_NUMBER () OVER (ORDER BY

sal DESC) rank

FROM teacher)

WHERE rank >=5 AND rank<=9;

 

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

 一般优化技巧：

建议不用“*”代替所有列名

删除所有数据用TRUNCATE代替DELETE

（下面的几条 在11G版本优化器自带优化了）

用NOT EXISTS 代替NOT IN

用EXISTS代替IN

用EXISTS代替DISTINCT

 ----表连接方法：

        驱动表的选择

   WHERE子句的连接顺序

   表连接关系放在前面

   过滤记录越多的条件子句应放置到后面

 

select * from teacher t,dept d

 where t.deptno = d.deptno

-- from 后面靠右的那个表就是驱动表(这两张表都没有索引的情况) 

-- 有索引的情况下,应该是没索引的那张表是驱动表

-- 驱动表要选择小表(A 数据量 小,过滤后的数据量小)

 

A(10)  B(10W)(有索引)

 

10

 

80% 男老师,20% 女老师

--where dept ="人力部" and gender = "女" 

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

--查找所有有部门的老师列表() 

select t.tname,t.deptno from teacher t

where t.deptno in(select deptno from dept)

 

--逻辑关联 错误(因为可能有t.deptno不在dept.deptno范围内的情况)

select t.tname,t.deptno from teacher t 

where t.deptno is not null

 

--a 就是一个常量,exists后面的子查询是否返回数据(是否为空)

select t.tname,t.deptno from teacher t 

where exists

( select 'a' from dept where dept.deptno = t.deptno)

 

--distinct 去重（用exists代替distinct）

--查询出出现在教师表里的不同的部门编号

select distinct deptno from teacher where deptno is not null

 

select d.deptno from dept d where exists 

(select 'x' from teacher t where t.deptno = d.deptno)

 

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

--分页查询--

 

select t1.*

  from (select t.*, rownum rn from 

  (select * from teacher order by tno) t) t1

 where t1.rn >= 5

   and t1.rn <= 10

 

-- 视图 --

--分页sql view   

/*create view v_teacher_page as

select t1.*

  from (select t.*, rownum rn from 

  (select * from teacher order by tno) t) t1*/

  

 select * from v_teacher_page  where rn >=5 and rn <=10

 

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

-- 分区 --

 

insert into partition_table values(1,2012);

 

insert into partition_table values(2,2013);

 

insert into partition_table values(3,2014);

 

insert into partition_table values(4,2015);

 

--指定分区查询

select * from partition_table partition(pt_2012);

 

 

 

 

select * from teacher t where t.tname like '赵%'