常用的sql语句大全，建议收藏

一,查询语句

设置查询格式设置

设置行大小
set linesize 140;
设置页宽度
set pagesize 120;

修改登录 的设置文件 
C:Oracle\Administrator\product\11.2.0\client_1\sqlplus\admin

查询日期的格式： select *from v$nls_parameters;

1, 查看表的结构

语法：

desc 表名

2, 查询语句

语法：select [distinct] *|column_name|expr|alias from tablename

①, 虑空函数的使用 nvl(a, b)

题目：查询员工号，姓名，月薪，奖金，年薪

16新的计算

sql： select empno as “员工号”,ename “姓名”,sal 薪水,comm “奖 金”,sal*16 年薪,sal*16+nvl(comm,0) 年收入 from emp;

②, 去除重复的数据 distinct

题目：查询员工号，姓名，月薪，奖金，年薪

sql：select distinct 字段 from 表名;

③, 伪表的使用 dual

题目：输出计算表达式 3+20*5，显示当前日期 sysdate(伪列,不依赖于某一张表,查询的时候可以使用)

sql：select 3+20*5,sysdate from dual;

二, where 条件过滤

1, 比较运算符= != <> < > <= >= (between and ) 的使用

2, 逻辑运算符 and or not 的使用

是否为null 是使用 （is null） 而不是 （= null）

3, in：在集合中使用

①, 查询部门号是10或者20的员工信息

    select * from emp where deptno in (10,20);

②, 查询不是10和20号部门的员工信息 –null

    select * from emp where deptno not in (10,20);

4, 模糊查询

语法： ‘%’匹配任意多个字符。‘_’匹配任意一个字符

①, 查询员工首字母是S的员工信息

    select * from emp where ename like 'S%';

②, 查询四个字母的员工信息

    select * from emp where ename like '____';

③, 查询带有下划线的

需要指定转义字符 escape
select * from emp where ename like ‘%/_%’ escape ‘/’;

三, 排序

select .. 选择哪些列
  from .. 从哪选
where ..  有哪些条件 
group by ..
having …
order by ..  按什么排序,怎么排 

语法：排序可以 列名，表达式，别名，序号，语法是 order by col|expr|alias|number

1, 排序 order by , 默认是asc模式，升序，desc 降序

//按照 第一个排序  下面二个是不同的结果
select *from emp order by SAL, deptno;   按照SAL排序

select *from emp order by deptno, SAL;   按照deptno排序

①, 员工信息按奖金逆序 nulls last –null

    null 默认无穷大,排序默认在最后 ,倒序就在最前
    select * from emp order by comm desc nulls last;

    nvl()的使用
    select * from emp order by nvl(comm,-1) desc ;

四, oracle的单行函数

1, 字符函数

①, lower 小写，upper 大写，initcap 首字母大写

select lower('Hello wOrld') 一,upper('Hello wOrld') 二, initcap('Hello wOrld') 三 from dual;

②, 链接符的使用concat(连接符||)，substr，length，lengthb，instr

③, instr(str1,str2) 判断str2是否在str1中,如果存在返回位置,失败返回0

select instr('helloworld','owo') 一,instr('helloworld','owow') 二 from dual;

④, lpad，rpad 左右填充 l(r)pad(str,len,C) 返回 len长度的字符串,如果str不够,用C在左(右)填充

     select lpad('hello',10,'#'),rpad('hello',10,'*') from dual;

⑤, trim，replace

trim 去首尾空格
select ‘aaa’||trim(’ hello world ‘)||’bbb’ from dual;
select trim(‘H’ from ‘HHHHHelloHHHworldHHHHH’) from dual;
replace替换
select replace(‘helloworld’,’llo’,’kk’) from dual;–替换子串
select replace(‘helloworld’,’llo’,”) from dual;–相当于去掉子串

2, 数值函数 正、负表示小数点之后，或小数点以前的位数

1, round 四舍五入 
2, trunc 截断 
3, mod 取模 
4, ceil 和floor 向上取整,向下取整 

3, to_char, to_number, to_date 隐式转换的问题

五, 条件表达式

1, case： 是一个表达式，其语法为：

CASE expr  WHEN comparison_expr1 THEN return_expr1
            [WHEN comparison_expr2 THEN return_expr2
            WHEN comparison_exprn THEN return_exprn
            ELSE else_expr]
END


使用
case end 写法  sql99标准
select empno,ename,job,sal 涨前薪水,case job when 'PRESIDENT' then sal+0
                                     when 'MANAGER' then sal+200
                                     else sal+1000 
                             end 涨后薪水
  from emp

2, decode 函数

语法 decode(expr,search1,res1,search2,res2,…,default)

select empno,ename,job,sal 涨前薪水,decode(job,’PRESIDENT’,sal+1000,’MANAGER’,sal+800,sal+400) 涨后薪水

from emp

组的使用

考虑使用having,就是和group by 一起使用,对分组数据进行过滤

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

–求10号部门员工的平均薪水

select deptno,avg(sal) from emp group by deptno having deptno=10 ;

是否可以使用where?

select deptno,avg(sal) from emp where deptno=10 group by deptno;

–having与where的区别，哪个好？ where好!

where 是先过滤,后分组,having是先分组后过滤

–常见的非法使用组函数的情况