oracle: sql 基础知识

摘自：http://blog.youkuaiyun.com/zhangjg_blog/article/details/16358843

1       Desc

显示表有哪些字段

如desc emp;

2       select

2.1      给查询出的数据起别名

Select ename, sal*12 annual_sal from emp;

如果名字里有空格，要加双引号

Select ename,sal*12 “annual salary” from emp;

2.2      任何含有空值的数学表达式的计算结果都是空值

Select ename,sal*12+comm from emp;

如果comm为null，计算出的结果也是null

2.3      字符串连接 ||

Select ‘employee_idis’||employee_id from emp ;

Output: employee_id is 100

单引号引起来的是字符串常量，不区分大小写。

单引号在Oracle中有3种身份：

1.     用来引用一个字符串变量，也就是界定一个字符串的开始和结束

2.     转义符，对紧随其后出现的字符（单引号）进行转义

3.     表示它本身，也就是单引号作为一个字符串的一部分。

更确切的总结：

1.     出现在表达式开头和结尾的两个单引号表示引用一个字符串，界定字符串的开始和结束。

2.     如果单引号出现在表达式中间，且多个单引号直接没有任何其他字符，那么从左到右，第一对单引号中第一个是转义符，对紧随其后的第二个单引号转义，以使第二个单引号作为一个字符出现在一个字符串常量中。第二对，第三对，类似。。。

如：select ‘’’’from dual;   output: ‘

          Select ‘test’’’’’from dual; output: test’’

2.4      列名前面加distinct

去除重复的值，查出来的行数和表的行数不一致

Select distinct deptno from emp;

 

Distinct 会作用于多个字段，指多个字段的组合不能重复

Select distinct deptno, job from emp;

3       Where

Where 过滤条件，选择特定的行

Where语句中字符串的比较要用单引号

Select * from emp where ename = ‘CLARK’ ;

3.1      Where中的条件判断

条件判断可以是： = 、>、>=、< 、<=、 <>（不等于），字符串也可以做大小比较

3.2      Where中的between and关键字

Select * from emp where sal >= 800 andsal <=1500;

等价于

Select * form emp where sal between 800 and1500;

3.3      Null值判断，用is 或is not

Select * from emp where comm is null;

3.4      in 和not in 关键字

select * from emp where sal in (800, 1500,2000);

3.5       日期处理

可以直接使用相同格式的字符串比较

Select ename from emp where hiredate > ’12-11月-15’;

3.6      or关键字

两个条件满足一个就可以

Select * from emp where deptno =10 or sal>1000;

3.7      like 和not like，模糊查询

实现模糊查询，

%代表0或多个字符

_代表一个字符

\转义通配符

escape 关键字指定转义符

 

Select * from emp where ename like ‘%ALL%’;

Select * from emp where ename like ‘_all%’;

Select * from emp where ename like ‘_al\%L%’;

Select * from emp where ename like ‘_A$%L%’escape ‘$’;

4       order by 查询结果排序

默认是升序

Select * from emp orderby sal;

Select * from emp orderby sal asc; //升序

Select * from emporder by sal desc;//降序

 

按2字段排序，当deptno相同时，内部按照ename排序

Select * from emp orderby deptno desc,ename asc;

5       单行函数

是指一行记录对于一行输出

对字符串操作的单行函数：lower，upper ，substr

Select lower(ename) from emp;

查出的每行数据中，都把ename的值转化成小写，再作为结果返回。

 

Select substr(ename,1,3) from emp;

在查询结果中，把每行的ename截取第1-3字符的字符串，并作为结果返回。

6       Chr ,数字按ASCII码转出字符

Select chr(65) from dual;

7       Ascii函数，字符转出ASCII码输出

Select ascii(‘a’) from dual;

8       Round函数，四舍五入

Select round(23.78) from dual;

Select round(23.784,2) from dual; //四舍五入到小数点后2位

9       to_char函数，按一定格式转成字符串

select to_char(sal, ‘$99,999.9999’) fromemp;

select to_char(sal, ‘L0000.0000’) from emp;

L 代表本地货币，$代表美元

格式中9：在小数点前边时，若该位没有数字，不显示

格式中0：在小数点前边时，若该位没有数字，补0

 

Select to_char(hiredate, ‘YYYY-MM-DDHH:MI:SS’) from emp;

Select to_char(sysdate,’YYYY-MM-DDHH24:MI:SS’) from dual;

HH显示12小时制，HH24显示24小时制

10   组函数

也叫多行函数，多条记录对应一条输出

10.1 max 最大值函数

select max(sal)from emp;

10.2 min 最小值函数

select min(sal)from emp;

10.3 avg 均值函数

select avg(sal)from emp;

select to_char(avg(sal), ‘9999999,99’) fromemp;

10.4 sum 求和函数

select sum(sal)from emp;

10.5  count ，某个字段，不是空值的才计数

select count(*) from emp;

select count(distinct deptno) from emp;

11    Group by ，实现分组

Select deptno, avg(sal)from emp group by deptno;

Select deptno, job from emp group by deptno, job;

12   Having 子句

过滤分组后的记录

Select deptno, avg(sal) from emp group bydeptno having avg(sal) > 2000;

Where语句是对单条记录过滤，不能过滤分组后的记录，对分组查询后的记录进行过滤的是having

13   Select 语句各子句的执行顺序

where > group by >having > order by

select avg(sal) from emp

where sal > 1000

group by deptno

having avg(sal) > 1500

order by avg(sal) desc;

14   子查询

子查询可以出现在where中，也可出现在from中

Select ename from emp where sal > (selectavg(sal) from emp);

15   连表查询

自连接

Select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

 

连接3张表

Select ename, dname, grade from

Emp e join deptd on (e.deptno = d.deptno)

Join salgrades on(e.salbetween s.losal and s.hisal)

where ename not like ‘_A%’;

15.1      左连接，右连接，全外连接

左外连接、右外连接，都以一张表为基准，该表的内容会全部显示，然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录，那么相关联的结果集行中列显示为空值（NULL）。

 

select * from emp e left outer join dept t on (e.deptno = t.deptno)

select * from emp e, dept t wheree.deptno=t.deptno(+);

注意，用+就要用关键字 where

+表示补充，哪个表有加，这个表就是匹配表，所以+在右表，就是左连接

 

全外连接，左右表都不限制，都显示，没有匹配到的地方用NULL补充。全外连接不支持 + 这种写法。

16   Rownum 分段查询

Rownum是伪字段，并且只能用于<和<=

Select ename, sal from emp where rownum <= 5 order by sal desc;//取出前5个

 

Rownum取出来形成一张新的子表，然后用这个子表查询，就可以用大于号 了

Select ename from (select rownum r , enamefrom emp) where r > 10;