oracle 查询相关语句总结

select 列表 from 表名 where条件 + group by + having + order by
1.from： 数据源
2.where子句： 筛选符合条件的结果 不能使用分组函数
3.group by：对where子句筛选的结果进行分组
4.having子句： 对group by子句分组的结果再进行筛选，拿到符合条件的组，可能用到分组函数
5.order by：

1.Select

    select * from table;
    select colums from table;

    //数字和日期可以使用数学运算符：
    select salary*12 from table;
    select date+1 from table;

    //使用别名显示 关键字(AS)可省略 别名缺省“”默认就是大写的，加
    上“”则写的是啥就是啥
    select last_name AS "别名" from table; 

    //连接显示 关键字 (||) 字符串用单引号'' 连接字符串使用 "||", 
    而不是 java 中的 "+"
    select last_name || job_id AS employ from table;
    select last_name ||' is '||job_id AS employ_detil from table;

    //删除重复行 (DISTINCT)
    select distinct job_id from table;

    //显示表结构 （DESCRIBE）可缩写为DESC
    describe table;desc table;

---

2.过滤(where conditions)：

    逻辑 AND OR NOT 
    select colums1,columns2 from table where condition;
    //BETWEEN ...AND...(包含边界值)
        select last_name ,job_id from table where salary 
        between 1000 and 2000;
    //[NOT]IN(set) 等于列表中的一个
        select last_name ,job_id from table where salary 
        in(2000,4000,5000);
        select last_name ,job_id from table where salary nit 
        in(2000,4000,5000);
    //LIKE 模糊查询
        查询last_name中包含字母'a'的（%代表多个字符）
        select last_name ,job_id from table where last_name like 
        '%a';
        查询last_name中第二个字符是字母'a'的（ _代表1个字符）
        select last_name ,job_id from table where last_name like 
        '_a%';
        查询last_name中第三个字符是字母'a'的（ 两个_中间没空格）
        select last_name ,job_id from table where last_name like 
        '__a%';
        查询包含'_'的 '\'转义
        select last_name ,job_id from table where last_name like 
        '%\_%' escape '\' ;
    // IS [NOT] NULL 
        select last_name ,job_id from table where commission_pct 
        is null;
        select last_name ,job_id from table where commission_pct 
        is not null;
    //日期过滤
        select last_name ,job_id from table where 
        to_char(hire_date,'yyyy-mm-dd') = '1993-07-04'; 

---

3.分组：GROUP BY

查询凡是不是组函数的列，都应该让它出现在group by 当中。
也就是说select 不在group by中的列 就会报错”非法使用组函数”

  //查询每个部门的每个职位的平均工资
    select department_id , job_id ，avg(salary) 
    from employees 
    group by department_id，job_id
    //先将员工以部门号进行分组，再将每个部门组按照job_id进行呢分组

//集合函数或者分组函数(

分组函数 (max、min、avg、sum、count)只能在选择列、having子句、order 
by 子句中使用；
avg, sum只适用于number类型，默认都是all
where 不能使用组函数 用having代替
avg（expr）返回所有记录平均值 
count（expr）返回不为空的记录数量 
sum(expr) 返回每个分组的总和
max（expr）
min(expr) 一般用在where语句

---

4.having

    //求各部门中平均工资大于6000的部门
    select department_id avg(salary) 
    from employees 
    having avg(salary) >6000
    group by department_id;

---

7.排序

（order by colums1,colums2 ASC(升序)(DESC降序)）
select last_name ,job_id from table where commission_pct is null
order by last_name ASC;

    //多列排序
    order by last_name,job_id ASC;(多个列排序要用，隔开)
    order by last_name ASC,job_id DESC;
    //多列排序，本质是先按照department_id降序排序，相同的department_id
    //的行再按照job_id升序排序
    select sal, department_id from employees order by 
    department_id desc,sal asc;

Oracle内置函数

1.字符函数(下标从1开始)

lower('HELLOW') = hellow   全小写
upper('hellow') = HELLOW   全大写
initcap('hellow word')=Hellow Word 首字母大写
length（'helloword'）= 9 返回字符长度
concat('hellow','word') 字符连接
substr（'helloword',1,5）= hello 从第一个位置开始截取5个字符（字符串下标从1开始）
instr('hellow','o') = 5 返回字符所在位置，不存在返回0
lpad(salary,10,'*') = ****240000 10位输出左对齐
rpad(salary,10,'*') = 240000**** 10位输出左对齐
trim('h' from 'helloword') = elloword 截取一个字符，只截取首尾
replace('hellow','el','oo') = hoolow el替换为oo 替换所有

2.数字函数

round(number, m) m是正数 四舍五入到小数点后m位
                 m是负数 四舍五入到小数点前m位
round(45.926, 2)=45.93 四舍五入
trunc（45.926）= 45
trunc(45.926, 2)=45.92 截断
MOD(1600, 300)=100 求余数
abs() 返回绝对值
sign（）返回参数的符号，正数返回1，负数返回-1,0返回0
ceil（）返回大于等于输入参数的最小整数
floor（）返回小于等于参数的最大整数

---

3.日期
yyyy 年
mm 月
dd 日
day 星期
hh hh24 小时 mi 分钟 ss ssAM秒

sysdate 系统日期 时间
sysdate-1 日期减去一天
两个日期相减返回日期之间相差的天数。
months_between(date1,date2) 相差的月数
add_months(date,3) date加3个月
next_day(date,'sunday') 返回date日期之后的下一个星期日的日期
last_day(date) 返回本月最后一天日期
extract(日历字段名 from date数据或timestamp数据) 日历字段名:year month day hour minute second
1.如果日期型数据是date类型,只能获取年月日:
  extract(year from birth)
  extract(month from birth)
  extract(day from birth)
2.如果日期型数据是timestamp类型,才可以获取时分秒
  extract(hour from birth)
  extract(minute from birth)
  extract(second from birth)

//查询入职10年以上的人
select * from employees where add_(hire_date,10*12) < sysdate
//查询入职天数
select sysdate-hiredate from employees;

---

4.转换函数

1.Character <--> Date
    Date-->character 
    to_char(date,'yyyy-mm-dd') ='1993-06-02'
    使用双引号""向日期中添加字符
    to_char(date,'yyyy"年"mm"月"dd"日"') ='1993年06月02日'

    character-->Date
    to_date('1993-07-06','yyyy-mm-dd') = date
2.Character <--> Number
    Character-->Number to_number('char','format')
    to_number('123456','999999') = 123456

    Number-->Character to_char(number,'format')
    to_char(123456,'999999') = '123456'
    to_char(123456,'L999,999') = '￥123,456'
    to_number('￥123456','L999999') = 123456
    format： 
         9 代表数字，数字位不够时前面忽略 ，
         0 0 数字位不够时前面用0补齐， 
         ￥数字前加美元符号，
         L 数字前本地货币
         C 数字前加国际货币符
         ，逗号，

5.通用函数

NVL (expr1, expr2) expr1为NULL返回expr2，expr2的类型要跟expr1一样，否则利用转换函数进行转换
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。
NULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1 
COALESCE (expr1, expr2, ..., exprn) expr1为NULL返回expr2 expr2为NULL返回expr3....

---

6.条件表达式
1>.case表达式:

    case clumn1 when value1 then clumn2*1.0
                when value2 then clumn2*2.0
                else value
                end 别名

//给每个职员加工资:工资在6000元以下的将加20%,工资在6000元-8000元的将加18%,工资在8000元以上的加15%
    select employee_id,last_name,salary ,
    case when salary>6000 and salary <8000 then salary*(1+0.18)
         when salary> 8000 then salary*(1+0.15)
         else salary
         end 加薪之后
    from employees；     

2.>decode函数

DECODE(case,case1,value1,case2,value2,default_value) 

    select last_name ,department_id,decode( department_id, 10 ,salary*1.0,
                                     20 ,salary*2.0,
                                     30, salary*3.0,
                                     salary) as new_salary
    from employees where department_id in(10,20,30,40)

---

.多表查询（连接查询）

1>查询连接的俩个表中的相同列时，必须在列名之前加上表名前缀
2>要统计的时候才使用外连接

selest...from table1 INNER/left/right JOIN table2 ON 连接条件 where 查询条件

1.内连接：(INNER JOIN)只能查询出连接表匹配的记录，不匹配的记录无法查出

    select last_name,e.department_id,department_name
    from employees e ,departments d
    where e.department_id = d.department_id;

或selest...from table1 INNER JOIN table2 ON 连接条件 where 查询条件

    select last_name,e.department_id,department_name
    from employees e INNER JOIN departments d 
    ON e.department_id = d.department_id

2自连接：连接自身表的引用也就是连接自己
//查询所有员工的上级领导的名字。

    select e1.employee_id,e1.last_name,e1.manager_id,e2.last_name
     from employees e1,employees e2
     where e1.manager_id = e2.employee_id;

3外连接： selest…from table1 OUTER JOIN table2 ON 连接条件 where 查询条件

左外连接:(LEFT JOIN)如果条件没匹配，返回左表的列表项，右表的列表项为空
//selest...from table1 LEFT JOIN table2 ON 等值表达式/非等值表达式
        select last_name,e.department_id,department_name
        from employees e LEFT JOIN departments d 
        ON e.department_id = d.department_id;
//或用（+）selest...from table1 ,table2 where 等值表达式/非等值表达式
        select last_name,e.department_id,department_name
        from employees e , departments d 
        where e.department_id = d.department_id(+);
右外连接：(RIGHT JOIN)如果条件没匹配，返回右表的列表项，左表的列表项为空
//selest...from table1 RIGHT JOIN table2 ON 等值表达式/非等值表达式
//或用（+）
        select last_name,e.department_id,department_name
        from employees e , departments d 
        where e.department_id(+) = d.department_id;
全连接：(FULL JOIN) 如果条件没匹配 返回左表的列表项，右表的列表项为空，返回右表的列表项，左表的列表项为空
//selest...from table1 FULL JOIN table2 ON 等值表达式/非等值表达式 
    使用（+）注意：
    1.总是放在非主表的一边 左表外连接，放在右表;右表外连接，放在左表；
    2.只能用在 where子句中，并且 不能与 OUTER JOIN 一起使用
    3.不能用于全外连接
    4.如果外连接有多个条件，那么每一个条件都需要使用该操作符   

---

子查询

单行比较操作符
<> ！=不等于
= 等于
多行比较操作符
ANY ,ALL ,IN

 select id, salary
 from employees
 where salary < any(
                    select salary
                    from employees
                    where department_id = 10
                   )

1。查询平均工资最低的部门信息

 select * from department
  where department_id = (
                        select department_id 
                        from employees
                        having avg(salary) = (
                            select min(avg(salary)) 
                            from  employees
                            group by department_id
                                              )
                        group by department_id
                        )

2。查询平均工资最低的部门信息和平均工资

select d.*, (select avg(salary) from employees e where e.department_id = d.department_id group by department_id) 
    from department d
    where department_id = (
                        select department_id 
                        from employees
                        having avg(salary) = (
                            select min(avg(salary)) 
                            from  employees
                            group by department_id
                                              )
                        group by department_id
                        )

3.查询各个部门最高工资中，最低的工资是                    

select max(salary)
    from employees
    having max(salary) = (
                       select min(max(salary))
                       from employees
                       group by department_id
                      )
    group by department_id

4.查询各个部门最高工资中，最低的那个部门的最低工资是

    select min(salary)
    from employees
    where department_id = (
                      select department_id
                      from employees
                      having max(salary) = (
                            select min(max(salary))
                            from employees
                            group by department_id
                                           )
                      group by department_id

                      )
    group by department_id

5.查询1990年进公司中工资最高的员工信息

select *
    from employees
    where salary = (select max(salary)
                    from employees
                    where to_char(hire_date,'yyyy')='1990'
                    )
    and to_char(hire_date,'yyyy')='1990'

6.各部门中工资比本部门平均工资高的员工信息

    select *
    from employees e1
    where salary > (
                    select avg(salary)
                    from employees e2
                    where e1.department_id = e2.department_id
                    group by department_id
                   )

7。查询last_name中包含u的员工在相同部门的员工

    select * 
    from employees
    where department_id in (
                       select department_id
                       from employees
                       where last_name like '%u%'
                        )

8。查询90,100俩部门人数最多的那个部门

    select department_id  ,count(*)
    from employees e
    where department_id in (90,100)
    having count(*) = (select max(count(*)) from employees where 
    department_id in(90,100) group by department_id )
    group by department_id;
    //去除包含U的人
    and last name not like '%u%'