当前位置:数据库 > Oracle >>

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
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,