oracle基本函数和查询

----解锁scott用户
alter user scott account unlock;
----解锁scott用户的密码【也可以用来重置密码】
alter user scott identified by tiger;
----切换到scott用户下


----where
----查询出工资高于1500的员工
----where操作是在select操作之前执行
select e.ename, e.sal s from emp e where e.sal>1500;
----order by
----奖金从高到底排序【排序中null值的问题】
select *
from emp e
order by e.comm desc nulls last;
----模糊查询
---查询名字中包含M的员工信息【模糊查询的时候区分大小写】
select * from emp e where e.ename like '_M%';
----计算出每个员工的年薪
----只有起汉语别名的时候如果用必须双引号，其余情况都用单引号。
----null不是0。数字加null值为null。null也不是''
select e.sal*12+nvl(e.comm, 0) "年薪"
from emp e;
----让员工的名字和员工的工作在一列上显示。
select e.ename, e.job from emp e;
select concat(e.ename, e.job) 工作信息 from emp e;
-----concat在oracle和mysql中通用，但是在oracle中只能连接两个字符串
select concat(e.ename, '的工作为：', e.job) 工作信息 from emp e;
-----oracle中连接多个字符串用||
select e.ename || '的工作为：' || e.job 工作信息 from emp e;



----单行函数：作用于一行，返回一个值。
----多行函数：作用于多行，返回一个值。
----dual 虚表：没有任何意义，仅仅是为了补全语法。
---mysql中查询当前系统时间
select sysdate();
---oracle中必须有from关键字，但是mysql中可以没有。
select sysdate from dual;

-----字符函数
select upper('yes') from dual;--小写变大写
select lower('YES') from dual;--大写变小写
select substr('abcdefg', 3, 2) from dual;--截取字符串
select lpad('ab', 6, 'h') from dual;--用第三个字符在前面补全第一个字符，长度是第二个参数
select instr('abcdefg', 'bchh') from dual;--后面字符串在前面中的位置
select trim('     ab      '), '     ab      ' from dual;--前后去空格
select replace('abcdefg', 'abc', 'h') from dual;--用第三个替换第一个中的第二个

----数值函数
select round(16.18, -1) from dual;--四舍五入
select trunc(16.18, -1) from dual;--直接截取
select mod(12, 5) from dual;--求余

----日期函数
----算出明天这个时候。
select sysdate+1 from dual;
----算出明年这个时候。
select sysdate+365 from dual;
----算出emp表中所有员工入职距离现在几个月
select round(months_between(sysdate, e.hiredate))
from emp e;
----算出emp表中所有员工入职距离现在几个年
select round(months_between(sysdate, e.hiredate)/12)
from emp e;
----算出emp表中所有员工入职距离现在几天
select round(sysdate-e.hiredate) from emp e;
----算出emp表中所有员工入职距离现在几周
select round((sysdate-e.hiredate)/7) from emp e;

----转换函数
select to_number('11111'), '11111' from dual;
----to_char把当前时间转换成字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
----to_date把字符串转换成日期
select to_date('2017-5-14 11:56:43', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
select to_date('2017-5-14 16:56:43', 'fm yyyy-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate, 'month') from dual;--月单词
select to_char(sysdate, 'mm') from dual;--月数字
select trim(to_char(sysdate, 'day')) from dual;--周几单词
select trim(to_char(sysdate, 'dd')) from dual;--日期数字
select trim(to_char(sysdate, 'dy')) from dual;--周几单词简写
select trim(to_char(sysdate, 'yyyy')) from dual;---年数字
select trim(to_char(sysdate, 'year')) from dual;---年单词

select to_char(999999, 'L999,999.999') from dual;---格式化数字

---拓展
---如果说按天做统计
select trunc(sysdate) from dual;
---如果说按天做统计,过了中午12点算下一天
select round(sysdate) from dual;

----通用函数
select nvl(null, 10) from dual;---【重点掌握】
select nvl2(null, 10, 6) from dual;
select nullif(2, 2) from dual;--如果两个参数一样就返回null。否则显示第一个。
select coalesce(null, 1, null, 2, null, 3) from dual;---输出第一个不为null的值

----条件表达式
----给emp表中的员工起中文名
----case语法是oracle和mysql通用
select case e.ename
       when 'SMITH' then '司马老贼'
         when 'ALLEN' then '曹贼'
           when 'WARD' then '诸葛小儿'
             when 'JONES' then '大耳贼'
               --else '小毛贼'
                 end "汉语名称", e.job
from emp e;
----oracle专用
select  decode(e.ename,
        'SMITH',  '司马老贼',
          'ALLEN',  '曹贼',
            'WARD',  '诸葛小儿',
              'JONES',  '大耳贼',
                '小毛贼')
                  "汉语名称", e.job
from emp e;
---sql语言是一门独立的编程语言。支持mysql，oracle等等数据库。hibernate又把sql封装为hql。
--decode使用把case...when...then..else ...end中关键字去掉，以逗号隔开

-----多行函数
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

select e.deptno, max(e.sal)
from emp e
group by e.deptno;

select e.deptno, min(e.sal)
from emp e
group by e.deptno;

select e.deptno, sum(e.sal)
from emp e
group by e.deptno;
---每个部门多少人
select e.deptno, count(e.sal)
from emp e
group by e.deptno;
----emp表中一共多少人
select count(1) from emp e;
select max(e.sal) from emp e;
select min(e.sal) from emp e;
select avg(e.sal) from emp e;
select sum(e.sal) from emp e;

-----group by
----每个部门的平均工资
----select后面出现的原始列，必须在group by后面出现
----如果在group by后面出现的原始列，可以不在select后面出现
select e.deptno, avg(e.sal)
from emp e
group by e.deptno, e.sal;

------查询出平均工资高于2000的部门
------where和having都是条件。条件就不能用别名。
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;


-----查询出每个部门工资高于1000的员工的平均工资
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>1000
group by e.deptno;

----查询出每个部门工资高于1000的员工,查询出平均工资高于2000的部门
select e.deptno, avg(e.sal) asal
from emp e
where e.sal>1000
group by e.deptno
having avg(e.sal)>2000;

----where是过滤分组前的数据，having是过滤分组后的数据。
----where必须放在group by之前，having必须放在group by之后。
----【错误写法】
select e.deptno, avg(e.sal) asal
from emp e
where avg(e.sal)>1000
group by e.deptno
having avg(e.sal)>2000;