orcale数据库基本查询语句（SQL）

说明：数据库查询语句，简称SQL（结构化查询语言），

sql语句使用一般最频繁，。

第一部分 查询语句的基本理论知识（主要是单表查询）

一、SQL语句的组成成分说明

（1）、select $column ... 子句 （筛选出要显示出来的数据列）

（2）、from $table ... 子句 （选择数据来源）

（3）、where 子句 （这里表示对查询出来的数据进行过滤）

（4）、order by &column desc/asc 子句

  (显示的数据进行排序，desc表示降序，asc表示升序)

（5）、group by &column （这里表示聚合）

（6）、having 子句 （对聚合的结果进行过滤）

 需要了解的数据库内部执行的顺序：

首先执行from语句，就是采用笛卡尔积将若干张表组合成一张表

接着执行where语句进行过滤

通过select语句获取经过两层过滤之后的字段

通过order语句进行排序

通过group by 语句进行聚合。

通过having语句进行聚合过滤

二、查询中使用到的关键字和运算符号(在where子句和having子句中用到)

（1）、算术运算符和条件判断 + ,-, *, / ，>, >= , < ,<=,=,!=,<>

（2）、为表或者属性取别名 as ,空值 is null，非空值 is not null,

       去重复 distinct,连接 ||,集合判断in/not in, 模糊查询 like . 区间查询between ...and...

（4）、逻辑运算符表示或 or,表示与 and ，表示否 not.

（5）、从结果中过滤数据any 、all、some

（6）、聚合函数 sum(),avg(),max(),min(),count();

（7）、case ...when ...then 和decode；

重点说明：使用技巧和说明：

（1）、like的使用技巧，like一般是模糊查询：

select * from table1 where name like '%明%' --查询name字段中包含有“明”字的。

select * from table1 where name like '李%'--查询name字段中以“李”字开头。

select * from table1 where name like '%[0-9]%'--查询name字段中含有数字的。

select * from table1 where name like '%[a-z]%'--查询name字段中含有小写字母的。

select * from table1 where name like '_明_' --查询name字段是三个字且中间一个字是“明”字的。

（2）、any的使用技巧： Any也表示满足其中一个的意义，也是用or串起来的比较从句，区别是any一般用在非“=”的比较关系中，

      这也很好理解，英文中的否定句中使用any肯定句中使用some，这一点是一样的。

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >any(select sal from scott.emp where job='MANAGER');

带any的查询过程等价于两步的执行过程。

第一步、select sal from scott.emp where job='MANAGER'--查询出职务为经理的工资集合

第二步、select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >2975 or sal>2850 or sal>2450;

（3）、some的使用技巧：Some在此表示满足其中一个的意义，是用or串起来的比较从句。

select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal = some(select sal from scott.emp where job='MANAGER');

 带some的嵌套查询与any的步骤相同。

第一步、子查询,执行“select sal from scott.emp where job='MANAGER'”,其结果如图4.22所示。

第二步、父查询执行下列语句。select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =2975 or sal=2850 or sal=2450;

（4）、all的使用技巧：All则表示满足其其中所有的查询结果的含义，使用and串起来的比较从句。

 select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal >all(select sal from scott.emp where job='MANAGER');

第一步、子查询,执行“select sal from scott.emp where job='MANAGER'”,其结果如图4.22所示。

第二步、父查询执行下列语句。select emp.empno,emp.ename,emp.job,emp.sal from scott.emp where sal =2975 and sal=2850 and sal=2450;

（5）、in的用法和between ...and..的用法：

select * from emp where id in(1,11,21);表示在in后面的数据中任意选择一个

select * from emp where age between 18 and 35;表示年龄在18到35之间。

（6）、distinct的用法：（用法是去除重复的数据）

select distinct id,name from emp; --这里面同事对两个字段做去重处理

select distinct name from emp;--这里只对一个字段做去重处理

（7）case 表达式，decode表达式，比较特殊的函数。

--case表达式

select name

   case gender

       when 1 then '男'

       when 2 then '女'

       else '未知'

   end 性别

   from emp;

--decode表达式

select empno,ename,sal,

decode(deptno,

   10,'财务部',

   20,'销售部',

   '未知部门')部门

   from emp;

--特殊函数

select ascii('A') from dual; --65

select chr(54740) from dual; --赵

select user from dual;

三、聚合查询（group by）,排序（order by）,having过滤和where 过滤详解

（1）、聚合查询group by ....having的用法：

在SQL语句中使用group by 子句将行划分成较小的组，一旦使用分组后select操作的对象变为各个分组后的数据，

使用聚组函数返回的是每一个组的汇总信息。所以group by通常是用作在数据库中进行统计使用。

而使用having子句 限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息Oracle

按照group by 子句中指定的表达式的值分组查询结果。

对于聚合查询最重要的是几个聚合函数，求和，平均值，最大值，最小值，行数。

如下下面的例子：

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

from emp where depno = 10 group by deptno having sal >= 3000 ;

使用聚合函数需要注意的地方：

A、查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方，在where 子句中不能使用聚组函数。

B、查询语句的select 语句中不能出现不是聚合的类，比如id，name，如果存在会报错。

C、group by 后面可以根据多个列进行聚合比如:group by deptno,job，这样就会根据两个列进行聚合查询。

（2）、查询结果排序（order by）的用法。

1、ORDER BY 中关于NULL的处理

缺省处理，Oracle在Order by 时认为null是最大值，所以如果是ASC升序则排在最后，DESC降序则排在最前。

当然，你也可以使用nulls first 或者nulls last 语法来控制NULL的位置。

Nulls first和nulls last是Oracle Order by支持的语法

如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)

如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)

使用语法如下：

--将nulls始终放在最前

select * from zl_cbqc order by cb_ld nulls first

--将nulls始终放在最后

select * from zl_cbqc order by cb_ld desc nulls last

2、几种排序的写法

单列升序：select<column_name> from <table_name> order by <column_name>; （默认升序，即使不写ASC）

单列降序：select <column_name> from <table_name> order by <column_name> desc;

多列升序：select <column_one>, <column_two> from <table_name> order by <column_one>, <column_two>;

多列降序：select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> desc;

多列混合排序：select <column_one>, <column_two> from <table_name> order by <column_one> desc, <column_two> asc;

3、注意有时候order by 后面可以使用数字代替字段

select id,name from emp order by 1 desc;表示按照id倒叙排列，这是因为order by 是根据查询结果的列进行排序。

order by 语句是在最后才执行，就是在所有的查询结果已经完成的情况下根据结果进行升降序排列的。

4、 自定义排序，可以根据查询结果自定义排序。这个事在已经知道查询结果的情况下面进行的。

select * from tb order by decode(blogid,3,1,2), blogid;

查询结果就是按照blogid的3,1,2进行排序的。

第二部分 复合查询，集合查询（按照常用的顺序）。

1、多表查询，子查询，自查询语句

（1）基于外键关联的多表查询(一般通过where语句中添加外检关联)的用法：

select id,name,gender...from t_student a,t_class b where a.class_id = b.id;

（2）基于主键的子表查询

select * from t_student where id in(select id from t_student m where m.class_id=1);

（3）子查询语句的用法：(子查询会首先执行，只要执行完成之后称为外部查询结果的一部分就行。)

方式一：select语句中设置子查询

select id,name,code,(select name from t_class c where c.name = t.id ) from t_student t.

方式二：from 语句中使用子查询

select id,name,code from t_student t,(select * from t_class c where c.id=12 ) c where t.class_id = c.id;

方式三：在where 语句中查询

select id,name,code from t_student t where t.score >(select avg(score) from t_student);

子查询总结：

在select语句中使用子查询，查询的结果是唯一值，也就是替代查询字段。

在from语句中使用子查询，查询结果是一张表，

在where 语句中使用子查询，查询的结果可以是集合也可以是唯一值。

2、集合查询

（1）union/union all并集查询，

select * from student where class_id=1 union select * from student where class_id=3;

（2）intersect 交集查询

select * from student where class_id=1 intersect select * from student where class_id=3;

（3）minus 差集查询

select * from student where class_id=1 minus select * from student where class_id=3;

集合查询需要注意的地方：

select语句中参数类型和个数要一致。

可以使用括号改变集合执行的顺序

如果有order by子句，必须放到每一句查询语句后

集合运算采用第一个语句的表头作为表头

3、连接查询（分为：等值连接，不等值连接，外连接，自连接）

（1）等值连接和不等值连接，前面的多表查询里面已经说明并使用了，就是where语句

里面外键关联采用等值或者不等值的相关联。连接 n个表,至少需要 n-1个连接条件。

 例如：连接三个表，至少需要两个连接条件。

使用表名前缀在多个表中区分相同的列。

在不同表中具有相同列名的列可以用表的别名加以区分。

使用别名可以简化查询。

使用表名前缀可以提高执行效率。

（2）自连接（就是将一张表当做两张表进行查询使用）

select work.name||'work for'||manager.name

from emp work ,emp manager

where worker.manager_id=manager,employee_id;

（3）外连接（分为做外连接，右外连接，）

外连接使用的情况就是：有两张表emp和dept表，其中dept中有一个部门下面没有员工，

如果外连接(+)在dept这边的话，那么没有员工的单位也会显示出来，反之员工表也是类似。

通常这样处理信息不完整的情况华获取对应的数据。

select id,name,deptname

form emp a,dept b

where a.deptno(+) = b.id;//这样当emp表中有数据为空的话也能够显示

select id,name,deptname

form emp a,dept b

where a.deptno= b.id(+);//这样dept表中有数据为空的话也能够显示出来。

（4）、叉集（cross join）和笛卡尔积差不多

selct * from emp cross join dept；

（5）、自然连接

NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。

在表中查询满足等值条件的数据。

如果只是列名相同而数据类型不同，则会产生错误。

假设emp表中有一个字段是dept_id ，dept表中的主键也是dept_id

select * from emp natural join dept

这样就能够根据字段名称进行自然的匹配。

（6）使用using 子句创建连接

select * from emp e join dept using(dept_id)

（7）使用ON子句连接（还能够实现多表连接，就是不断的在后面join表on条件）

select * from emp e join dept d

on e.dept_id = d.dept_id

（8）左外连接,右外连接，满外连接

select * from emp e left outer join dept d on(e.dept_id = d.dept_id)

select * from emp e right outer join dept d on(e.dept_id = d.dept_id)

select * from emp e full outer join dept d on(e.dept_id = d.dept_id)

小技巧： SQL优化策略

  SQL优化的主要面对的是数据库怎么样实现数据库查询的。

SQL优化就是在结果正确的前提下尽量，尽量减少访问的数据块，减少扫描IO的次数

 （1）尽量少的使用IN操作符而采用EXIST代替。

 （2）尽量使用NOT EXIST代理NOT IN操作符。

 （3）尽量不用‘<>’或者 '!=' 操作符，使用 如 a!=0可以写成 a>0 or a<0 。

 （4）设计表的时候把索引列设置为NOT NULL，这样就能够减少索引列进行判断字符是否为空

 （5）尽量不用通配符‘%’或者'_'作为查询字符串的第一个字符。因为会产生全表的扫描

 （6）避免在where 子句上面使用函数。如 sub(no,1,4)='5400' 改成no like '5400%'

 （7）用'>='代替'>'比如 a>2改成a>=3;

 （8）where 后面的条件顺序上面做优化

      1、表连接语句写在最前面，可以过滤掉很多的查询

 （9）使用表的别名，并将之作为每一列的前缀。

 （10）尽量不要显示或隐式的运算字段，如：a+20>50 改成 a>30

 （11）使用union all代理union,因为不用排除重复的记录行。

 （11）尽量使用packages，使用package在第一次调用时能将整个包load进内存