Oracle与Mysql的高级查询与难点sql
Oracle与Mysql的高级查询与难点sql
mysql查询杨凯专属频道OracleSQL
一、连接查询
1.内连接
内连接用于返回满足连接条件的所有记录。默认情况下,在执行连接查询时如果没有指定任何连接操作符,那么这些连接查询都属于内连接。
Sql代码
1. SELECT a.dname,b.ename from dept a,emp b where a.deptno=b.deptno and a.deptno=10;
在FROM子句中指定INNER JOIN选项,也可以指定内连接。
Sql代码
1. SELECT a.dname,b.ename from dept a INNER JOIN emp b where a.deptno=b.deptno and a.deptno=10;
从Oracle9i开始,如果主表的主键列和从表的外部键列名称相同,那么可以使用NATURAL JOIN 关键字自动执行内连接操作。
Sql代码
1. SELECT dname,ename FROM dept NATURAL JOIN emp;
2.左外连接
左外连接通过指定LEFT[OUTER] JOIN选项来实现。当使用左外连接时,不仅会返回满足连接条件的所有记录,而且还会返回不满足连接条件的连接操作符左边表的其他行。
Sql代码
1. SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;
3.右外连接
右外连接通过指定RIGHT[OUTER] JOIN选项来实现的。当使用右外连接时,不仅会返回满足连接条件的所有行,而且还会返回不满足连接条件操作符右边表的其他行。
Sql代码
1. SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;
4.完全外连接
完全外连接是指定FULL[OUTER] JOIN 选项来实现的。当使用完全外连接时,不仅会返回满足连接条件的所有行,而且还会返回不满足连接条件的所有其他行。
Sql代码
1. SELECT a.dname,b.ename FROM dept a FULL JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;
相当于没连接去掉where条件
Sql代码
1. SELECT a.dname,b.ename from dept a,emp;
5.使用(+)操作符
在Oracle9i之前,当执行外连接时,都是使用连接操作符(+)来完成的。尽管可以使用操作符(+)执行外连接操作。但Oralce9i开始Oracle建议使用OUTER JOIN执行外连接。语法如下:
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1(+) = table2.column2;
当使用(+)操作符执行外连接时,应该将该操作符放在显示较少行(完全满足连接条件行)的一端。
一句话,左外连接时,(+)要放在右边表的一端。右外连接时,(+)放在左边表的一端。
左外连接:
Sql代码
1. SELECT a.dname,b.ename FROM dept a, emp b where a.deptno=b.deptno(+) AND b.deptno(+)=10;
右外连接
Sql代码
1. SELECT a.dname,b.ename FROM dept a, emp b where a.deptno(+)=b.deptno AND a.deptno(+)=10;
二、递归查询
1.语法
Sql代码
1. Select * from …. Where [结果过滤条件语句]
2. Start with [起始条件过滤语句]
3. Connect by prior [中间记录过滤条件语句]
2.例子
Sql代码
1. Select * from company t Where t.flag=1
2. Start with t.company_id=50500000
3. Connect by prior t.company_id=t.parent_id
说明:
Sql代码
1. select [level],column,expr from table [where condition]
2. [start with] //[起点]
3. [connect by prior + 主键=外键 或 外键=主键]
a.自顶向下: 左边放主键,右边放外键。
b.自底向上: 右边放主键,左边放外键。
c.level(伪列)层次的级别,不固定值。
三、更改数据库的时间格式
1、临时更改
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'
2.永久更改
在注册表[HKEY_CODE_MACHINE\SOFTWARE\ORACLE]里面,加入NLS_DATE_FORMAT,值为YYYY-MM-DD HH24:MI:SS。
3、Unix下,在用户的.profile文件中增加以下内容:
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
export NLS_DATE_FORMAT
附:在SQLPLUS下,实现中-英字符集转换
Sql代码
1. alter session set nls_language='AMERICAN';
2. alter session set nls_language='SIMPLIFIED CHINESE';
四、复制表和数据
1)建表
Sql代码
1. create table test as select * from dept; --从已知表复制数据和结构
2. create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据
建立视图:
Sql代码
1. CREATE OR REPLACE VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;
建立实体化视图:
Sql代码
1. CREATE MATERIALLIZED VIEW summary_emp AS SELECT deptno,job,avg(sal) avgsal, sum(sal) sumsal FROM emp GROUP BY cube(deptno,job);
实体化视图管理是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度,只要在实体化视图管理上创建了统计,查询优化器将自动的使用实体化视图管理,这特性被称为QUERY REWRITE(查询重写).与普通的视图不同的是实体化视图管理存储数据,占据数据库的物理空间的.
2)不使用列的列表插入数据:
Sql代码
1. insert into test select * from dept;
3)使用DEFAULT插入数据:如果列存在默认值,则会使用其默认值;如果列不存在默认值,则自动使用NULL.
Sql代码
1. insert into dept values(10,'市场部',default);
4)使用子查询插入数据
将查出来的内容全部插入
Sql代码
1. insert into employee(empno,ename,sql,deptno)
2. se
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?