Oracle SQL 语言基础

查询数据
SELECT 语句的语法格式如下： SELECT [DISTINCT select_list FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 其中： DISTINCT ：用于删除结果集中重复的行。 select_list 指明要查询的选择列表。列表可以包括若干个列名或表达式，列名或表 达式之间用逗号隔开，用来指示应该返回哪些数据。表达式可以是列名、函数或常 数的列表。 FROM table_source 指定所查询的表或视图的名称。 WHERE search_condition 指明查询所要满足的条件。 GROUP BY group_by_expression 根据指定列中的值对结果集进行分组。 select deptno,job,avg(sal),max(sal) from emp group by deptno,job; HAVING search_condition 对用 FROM、WHERE 或 GROUP BY 子句创建的中间结果集 进行的筛选。它通常与 GROUP BY 子句一起使用。 显示平均工资高于 2000 元的部门编号、平均工资和最高工资。 select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000; [ORDER BY order_expression [ ASC | DESC ] ] 对查询结果集中的行重新排序。ASC 和 DESC 关键字分别用于指定按升序或降序排序。如果省略 ASC 或 DESC，则系统默认 为升序。 以部门编号降序及姓名升序来查询工资在 2000-3000 元的员工信息。 select * from emp where sal between 2000 and 3000 order by deptno desc,ename;
Where 子句查询条件
比较运算符:=（等于） 、>（大于） 、>=（大于等于） 、<（小于） 、<=（小于等于） 、<> （不等于） select * from emp where deptno<>20 and job='CLERK'; 范围说明:between A and B, not between A and B select * from emp where sal between 2500 and 3500 and hiredate between ‘01-1 月-81’ and ‘31-12 月-81’; 可选值列表：IN，NOT IN select * from emp where deptno in(20,30); 等价于 select * from emp where deptno=20 or deptno=30; 模式匹配：LIKE，NOT LIKE select * from emp where ename like 'A%' or ename like '_A%'; 是否空值：IS NULL，IS NOT NULL select * from emp where comm is null; 上述条件的逻辑组合：NOT（非） 、AND（与） 、OR（或）

使用字段别名： Select deptno as 部门编号,dname as 部门名称 from dept; 使用算术表达式： select empno, ename,sal, sal*12 from emp; 连接运算符： select empno,ename||'''s annual salary is'|| sal*12 from emp where deptno=20;
子查询
单行子查询： select * from emp where sal=(select max(sal) from emp); ( =,>,<,>=,<=,<> ) 多行子查询： select * from emp where job in(select distinct job from emp where deptno=20); ( IN,NOT IN,EXISTS,NOT EXISTS,ALL,ANY ) 显示高于部门编号为 20 的所有员工工资的员工信息。 select * from emp where sal>all(select sal from emp where deptno=20); 显示高于部门编号为 20 的任意员工的工资的员工信息。 select * from emp where sal>any(select sal from emp where deptno=20);
内连接 外连接empno,ename,a.deptno,dname,loc from emp a,dept b where a.deptno=b.deptno and a.deptno=20;
不等连接： select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal and a.deptno=20;
*外连接
在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的 行，而不限制另外一个表中的行。 这种连接形式在许多情况下是非常有用的。 外连接分为左外连接、右外连接和全外连接。 对于外连接，oracle 中可以使用“(+)”来表示 ，语法格式如下： select table1.column, table2.column from table1, table2 where table1.column1(+) = table2.column2
1、左外连接 左外连接对连接条件中左边的表不加限制。 返回结果集中将包括左表中所有的记录， 而不仅 仅是连接字段所匹配的记录 。如果左表中的某条记录在右表中没有匹配的记录，则结果集 相应记录的有关右表的所有字段将为空值。 select ename,a.deptno,sal,dname from emp a,dept b where a.deptno=b.deptno(+) and sal between 2500 and 3000; 等价于 select ename,a.deptno,sal,dname from emp a left outer join dept b on a.deptno=b.deptno where sal between 2500 and 3000; 2．右外连接 右外联接对右边的表不加限制。 返回结果集中将包括右表中所有的记录， 而不仅仅是连接字 段所匹配的记录 。如果右表中的某条记录在左表中没有匹配的记录，则结果集相应记录的 有关左表的所有字段将为空值。 select ename,a.deptno,sal,b.deptno,dname from emp a,dept b where a.deptno(+)=b.deptno; 等价于：select ename,a.deptno,sal,b.deptno,dname from emp a right outer join dept b on a.deptno=b.deptno; 3、全外连接 全外连接对两个表都不加限制， 所有两个表中的行都会包括在结果集中。 结果集将包含两个 数据表中的所有记录， 当某条记录在另一个数据表中没有匹配记录时， 则将另一个数据表的 选择列表字段指定为空值。 select ename,a.deptno,sal,b.deptno,dname from emp a full outer join dept b on a.deptno=b.deptno; 4、自连接（Self Join） 自连接就是一个表与它自身的不同行进行连接。 因为表名要在 FROM 子句中出现两次， 所以 需要对表指定两个别名，使之在逻辑上成为两张表。在 SELECT 子句中引用的列名也要使用 表的别名进行限定。 select empno,ename,mgr from emp where deptno=30; select a.ename 雇员,b.ename 经理 from emp a,emp b where a.mgr=b.empno and a.deptno=30;




组处理函数
把查询出的数据组的值进行汇总运算，返回单值的函数。 常用的组处理函数： 平均值：AVG 总和：SUM 最小值：MIN 最大值：MAX 计数：COUNT ( COUNT(*)、COUNT(DISTINCT…) ) select avg(sal) as arg1,avg(distinct sal) as arg2,max(sal) as max,min(sal) as min,sum(sal) as sum,count(*) as cnt1,count(sal) as cnt2,count(distinct sal) as cint3,count(comm) as cnt4 from emp where deptno=20;
在 DDL 中使用子查询 1. 在 CREATE TABLE 语句中使用子查询 通过在 CREATE TABLE 语句中使用子查询，可以在创建新表的同时插入表中的数据。 create table dept1(deptno,dname,loc) as select deptno,dname,loc from dept; create table emp1 as select * from emp; 2. 在 create view 中使用子查询 create or replace view dept_20 as select * from emp1 where deptno=20 order by empno; 在 DML 中使用子查询 1. 在 UPDATE 语句中使用子查询 将与 WARD 岗位相同的员工的工资、 补贴更新为与 WARD 完全相同。 然后确认更新的结果。 update emp1 set(sal,comm)=(select sal,comm from emp1 where ename='WARD')where job=(select job from emp1 where ename='WARD'); select ename,job,sal,comm from emp1 where job=(select job from emp1 where ename='WARD'); 2. 在 DELETE 子句中使用子查询 删除 ACCOUNTIBG 部门的员工信息。然后确认删除的结果。 delete from emp1 where deptno=(select deptno from dept1 where dname='ACCOUNTING'); select ename,job,deptno from emp1 where deptno=(select deptno from dept1 where dname='ACCOUNTING'); 3.在 INSERT 子句中使用子查询。 将 emp 表中 ACCOUNTING 部门的雇员信息插入到 emp 表中，然后确认插入的结果。 insert into emp1 select * from emp where deptno=(select deptno from dept where dname='ACCOUNTING'); select * from emp1 where deptno=(select deptno from dept1 where dname='ACCOUNTING');