第 二 天 函数和基本查询
第 二 天 函数和基本查询
1、过滤
where , > , < , = , <= , >= , <> ,
between and,not between and,in, not in
like:模糊查询：_:单个字符 %:任意个字符
is null,is not null
and,or(and优先级大于or)
2、排序
order by 在select语句的结尾
ASC(升序，默认)
DESC(降序)
3、函数
lower,upper
initcap:单词首字母变为大写
concat：字符串连接和||同
substr：求字串
length：其字符串长度
instr：求字串的位置
lpad：位数不够的时候左边补字符
rpad：位数不够的时候右边补字符
trim：去掉首尾的空格
round：四合五入
mod：求余
trunc：截取
sysdate:系统日期
to_char：
to_date:
to_number:
months_between:两个日期相隔的月数
add_month:加月份之后的日期
next_day:指出给定日期的下一天
last_day:给定日期的最后一天
round:四舍五入日期
对Month:大于15天就前进一个月
对year:大于6就前进一年
round(sysdate,'month'),如果月数大于16就前进一个月
trunc:截取日期
trunc(sysdate,'D')：截取到本周的第一天0:00
trunc(sysdate,'DD'):截取到本日的0：00
trunc(sysdate,'MM'):截取到本月的第一天
trunc(sysdate,'yyyy'):得到今年的第一天
nvl(expr1,expr2)
nvl2(expr1,expr2,expr3)
nullif(expr1,expr2)：如果表达式相等，返回空，否则返回expr1的值
coalesce(exp1,expr2...exprn):如果表达式1不为空，返回表达式1的值，如果表达式1为空，返回表达式2的值，注意：通常最后一个参数会被设定为一个常量
SQL> select ename,sal,comm,coalesce(comm+sal,sal,100) from emp;
sql函数的使用—系统函数
■ sys_context
1) terminal ：当前会话客户所对应的终端的标识符
2) lanuage: 语言
3) db_name: 当前数据库名称
4) nls_date_format:当前会话客户所对应的日期格式
5) session_user: 当前会话客户所对应的数据库用户名
6) current_schema: 当前会话客户所对应的默认方案名？
7) host: 返回数据库所在主机的名称
通过该函数，可以查询一些重要信息，比如你怎在使用哪个数据库?
select sys_context('userenv','db_name') from dual;
4、条件语句
把日期按照年月日格式显示
select ename,to_char(hiredate,'yyyy-mm-dd') from emp;
选择日期在
SQL> select ename,to_char(hiredate,'yyyy-mm-dd') from emp
2 where hiredate between to_date('
3 and to_date('
字符本身大小写敏感，必须要用单引号
select ename,sal,job from emp where ename='SCOTT';
匹配姓名第二个字母为A的员工姓名和工作
select ename,job from emp where ename like '_A%';
and优先级大于or,所以一下执行结果不一样
SQL> select ename,job,sal from emp
2 where job='PRESIDENT' or job='MANAGER' and sal >2500;
SQL> select ename,job,sal from emp
2 where (job='PRESIDENT' or job='MANAGER') and sal >2500;
虚表：dual
desc dual:查看虚表结构
把HELLO转换为小写
select lower('HELLO') from dual;
select upper('hello') from dual;
把每个单词的首字母变为大写：initcap
select initcap('hello word') from dual;
以下两句结果一样
SQL> select ename,sal,job from emp where ename=Upper('scott');
SQL> select ename,sal,job from emp where lower(ename)='scott';
字符串连接
SQL> select concat('hello',' word') from dual;
SQL> select 'hello'||' word' from dual;
SQL> select length('Hello word!') from dual;
SQL> select substr('Hello word!',1,5) from dual;
返回第一次遇到该值的位置
SQL> select instr('helloWorld','W') from dual;
去掉首尾的空格
SQL> select trim(' Hello World!') from dual;
SQL> select trim('h' from'hello world!') from dual;
TRIM('H'FRO
-----------
ello world!
SQL> select trim('e' from'hello world!') from dual;
TRIM('E'FROM
------------
hello world!
薪水不够位数10位补*号，在右边补*
select rpad(sal,10,'*') from emp;
在左边补*
lpad(sal,10,'*')
round函数的应用
round(34.456564,2)=34.46
round(34.456564,-1)=30
round(35.456564,-1)=40
mod函数求余数，没有整数和负数之分
mod(10,3)=1 ,mod(10,-3)=1
trunc函数截取
trunc(45.678,2)=45.67
trunc(45.678,0)=45
trunc(45.678,-1)=40
sysdate
转化系统日期的格式为年-月-日格式
select to_char(sysdate,'yyyy-mm-dd') from dual;
emp员工在雇佣了多少天
select ename,hiredate,round(sysdate-hiredate) as day from emp;
emp员工在雇佣了多少年
select ename,hiredate,round((sysdate-hiredate)/365) as "HireYear" from emp;
求当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select next_day(sysdate,'星期五') from dual;
SQL> select round(sysdate,'month') from dual;
ROUND(SYSDATE,
--------------
01-2月 -11
to_char函数的应用
SQL> select to_char(123456.78,'$
TO_CHAR(1234
------------
$123,456.78
SQL> select to_char(123456.78,'L
TO_CHAR(123456.78,'L9
---------------------
￥123,456.78
SQL> select to_char(123456,'L
TO_CHAR(123456,'L
---------------------
￥123,456.00
SQL> select ename,to_char(sal,'$
ENAME TO_CHAR(SAL
---------- -----------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
SQL> select to_number('$123,456.78','$999,999.99') from dual;
计算每个员工的薪水和奖金之和
SQL> select ename,sal,comm,nvl2(comm,sal+comm,sal) as salary from emp;
case的应用
SQL> select ename,job,sal,
2 case job when 'CLERK' then sal*1.1
3 else sal
4 end salary from emp;
decode应用，和case类似
SQL> select ename,job,sal,
2 decode(job,'MANAGER',sal*1.5,
3 'CLERK',sal*1.2,
4 sal)
5 from emp;
