`

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 '赵%'

 

分享到:
评论

相关推荐

    Oracle Sql语句转换成Mysql Sql语句

    本项目提供了一个Java源码工具,能够帮助用户便捷地将Oracle SQL语句转换为MySQL SQL语句。 Oracle SQL与MySQL SQL的主要差异在于以下几个方面: 1. **数据类型**:Oracle支持的数据类型如NUMBER、LONG、RAW等在...

    2-OracleSQL语句.md

    2-OracleSQL语句.md

    mysql虚拟了一个rowid(类似跟oracle的rowid)--SQL语句.sql

    mysql虚拟了一个rowid(类似跟oracle的rowid)--SQL语句

    oracle 查看当前会话执行的sql语句

    ### Oracle 查看当前会话执行的SQL语句 在Oracle数据库管理中,有时我们需要了解某个特定会话(Session)正在执行哪些SQL语句。这在性能调优、问题诊断等场景下尤为重要。以下将详细介绍如何通过不同的方法来查看...

    Oracle-Sql语句资料oracle+110个常用函数经典SQL语句大全.zip

    Oracle_Sql语句资料oracle+110个常用函数经典SQL语句大全,可供学习参考。

    oracle-SQL-note.rar_oracle

    Oracle SQL是数据库管理和数据操作的重要工具,主要用于在Oracle数据库系统中执行查询、更新、插入和删除等操作。...通过实践这些练习,你可以加深对SQL的理解,提高在Oracle环境中编写高效、准确SQL语句的能力。

    ORACLE数据库SQL语句美化器

    对ORACLE-SQL进行一些布局优化,更新它的格式

    sqlserver自动生成sql语句工具sqlserver转oracle

    本篇文章将详细探讨如何利用工具实现SQL Server自动生成SQL语句并转换到Oracle。 首先,标题中的"sqlserver自动生成sql语句工具"指的是可以分析SQL Server数据库结构和数据,自动生成对应的SQL创建语句的软件。这种...

    ORACLE-SQL语句学习教程解读.pdf

    Oracle SQL语句是数据库管理员和开发人员在Oracle数据库系统中进行数据操作的主要工具。本教程将深入探讨Oracle SQL的各种核心概念和技术,旨在帮助初学者和经验丰富的用户更好地理解和使用SQL语句。 首先,我们从...

    oracle-查找硬解析问题SQL语句

    在数据库中硬解析是万恶之源,为大家提供一个查找并且定位oracle硬解析问题SQL语句脚本

    liferay-portal-sql-6.1.1-ce-ga2-20120731132656558

    4. **性能优化脚本**:为了提高Liferay在Oracle数据库上的性能,可能包含索引创建、查询优化等方面的SQL语句。 5. **备份和恢复脚本**:用于定期备份数据库或在系统出现问题时恢复数据的安全措施。 6. **自定义...

    oracle-sql-the-essential-reference

    ### Oracle SQL:核心参考知识点详解 #### 一、概述 《Oracle SQL:核心参考》(Oracle SQL: The Essential Reference)是一本由David C. Kreines撰写的书籍,于2000年由O'Reilly出版社出版。该书为Oracle SQL的...

    Pro Oracle SQL-成为SQL语言编写专家

    - **SQL语句分类**:SQL语句大致分为五类:数据定义语言 (DDL)、数据操纵语言 (DML)、数据查询语言 (DQL)、数据控制语言 (DCL) 和事务控制语言 (TCL)。每种类型的语句都有其特定的功能和应用场景。 - DDL 主要用于...

    oracle笔记,入门体验篇,基本的SQL-SELECT语句

    oracle笔记,入门体验篇,基本的SQL-SELECT语句,简单入门,有具体的案例代码!

    压测Oracle的SQL语句的性能情况

    本文将深入探讨如何利用压力测试工具来评估和优化Oracle数据库中的SQL语句性能。 标题"压测Oracle的SQL语句的性能情况"暗示了我们关注的是在高负载情况下,Oracle数据库处理SQL查询的能力。压力测试(Pressure ...

    Oracle OCP SQL-1Z0-071培训方案.xlsx

    ●DDL和SQL SELECT语句 ●相关数据分组 ●对数据进行操纵、限制和排序 ●创建报表 ●单行函数和分组函数 ●数据字典视图 ●子查询 ●大数据集 ●模式对象 ●分层检索 ●集合操作符 ●正则表达式支持 ●从多表...

    Oracle实验二-SQL语句综合应用

    在本实验中,我们主要探讨了Oracle数据库中的SQL语句应用,涉及到了表、视图、索引以及序列等核心概念。以下是对这些知识点的详细解释: 1. **创建表**: - 使用`CREATE TABLE`语句创建了一个名为`person`的表,...

    ORACLE-plan-a-sql-tuning.rar_oracle

    "ORACLE-plan-a-sql-tuning.rar_oracle"这个压缩包显然包含了关于Oracle SQL调优的培训材料,特别是"ORACLE执行计划和SQL调优.ppt",这可能是一个详细的PPT演示文稿,用于解释如何理解执行计划以及如何进行SQL优化。...

    oracle Sql语句二

    oracle Sql语句二 oracle Sql语句二

    nacos-oracle-11g.sql语句

    nacos-oracle-11g.sql语句

Global site tag (gtag.js) - Google Analytics