orcale
上一篇 / 下一篇 2016-10-31 22:34:53 / 个人分类:orcale查询语句
1.*表示所有列 SELECT * FROM emp; --使用 * 查询会降低查询的效率,在工作中不要轻易使用 2.选择列进行数据的显示 SELECT ename,job,sal,deptno FROM emp; --关系型数据库中,数据操作的对象和操作的结果都是关系,或者都是表 3.对列中的数据进行算术运算 SELECT ename,sal,12*sal FROM emp; SELECT ename,sal,sal+500 FROM emp; SELECT ename,hiredate,hiredate+365 FROM emp; --对日期进行加减运算,添加或者减去天数 --sysdate 表示当前的系统时间 SELECT ename,hiredate,(sysdate-hiredate)/365 FROM emp; 4.使用括号改变运算顺序 SELECT ename,sal,12*sal+100 FROM emp; SELECT ename,sal,12*(sal+100) FROM emp; 5.对空值的处理 SELECT ename,sal,comm,12*(sal+comm) FROM emp; --空值参与运算时,得到的结果也是空值 6.列的别名 SELECT ename AS xingming,job zhiwei,sal "yue xin" FROM emp; --在sql 语句中,双引号只出现在给列起别名的时候,其他情况都不允许有双引号 SELECT ename,job,sal,12*sal nianxin FROM emp; 5.连接符||,将列或者字符连接在一起 SELECT ename||job jobdes FROM emp; SELECT ename||' is a '||job FROM emp; --字符和日期格式的数据必须加单引号 6.去重显示distinct SELECT job FROM emp; SELECT DISTINCT job FROM emp; --SELECT ename,DISTINCT deptno FROM emp; 第三章 限定和排序 1.where子句对查询条件进行限定 SELECT ename,job,sal,deptno FROM emp WHERE job = 'SALESMAN'; --字符和日期需要用单引号括起来 SELECT ename,job,sal,deptno FROM emp WHERE deptno = 20; SELECT ename,job,sal,deptno FROM emp WHERE sal > 2000; SELECT ename,job,sal,deptno FROM emp WHERE job <> 'MANAGER'; SELECT ename,job,sal,comm FROM emp WHERE sal <= comm; SELECT * FROM emp; SELECT empno,ename,mgr FROM emp WHERE empno < mgr; 2.between and 范围匹配 --between 最小值 and 最大值 SELECT ename,sal FROM emp WHERE sal BETWEEN 1500 AND 3000; --where子句中的条件列,可以不出现在select 列中。 --但是为了加强结果的可读性,一般条件还是添加到select 列中 SELECT ename,sal,12*sal nianxin FROM emp WHERE 12*sal BETWEEN 10000 AND 20000; --列的别名不能出现在where子句中 3.in多个值的匹配 --in (值1,值2,……值n) SELECT ename,job,deptno FROM emp WHERE job IN ('MANAGER','CLERK','SALESMAN'); SELECT ename,job,deptno FROM emp WHERE deptno IN (10,20); 4.like模糊匹配 --like ''通配符 ' --通配符%表示任意个任意字符 --通配符_表示1个任意字符 SELECT * FROM emp WHERE ename LIKE 'S%'; SELECT * FROM emp WHERE ename LIKE '%S%'; SELECT * FROM emp WHERE ename LIKE '_A%'; SELECT ename FROM emp WHERE ename LIKE '%A\_B%' ESCAPE '\'; --反斜杠是转义字符,如果要搜索_或%等特殊字符,在前面加反斜杠 --ESCAPE '\' ,把反斜杠看做是转义字符,而不是搜索反斜杠 5. IS NULL匹配空值 SELECT * FROM emp WHERE comm IS NULL; SELECT * FROM emp WHERE mgr IS NULL; 6.逻辑运算符and,满足所有条件才会返回结果 SELECT * FROM emp WHERE job = 'CLERK' AND deptno = '30'; SELECT * FROM emp WHERE job = 'SALESMAN' AND deptno = '30' AND sal BETWEEN 1500 AND 2000; 7.逻辑运算符or,只要满足条件之一就可以返回结果 SELECT * FROM emp WHERE job = 'CLERK' OR deptno = '30'; SELECT * FROM emp WHERE job = 'CLERK' OR deptno = '30' OR sal BETWEEN 2000 AND 3000; 8.逻辑运算符not,返回不满足条件的结果 SELECT * FROM emp WHERE deptno NOT IN (10,20); SELECT * FROM emp WHERE job NOT IN ('SALESMAN','CLERK'); 9.运算优先级:数学运算>比较运算>not>and>or SELECT * FROM emp WHERE job = 'CLERK' OR deptno = '30' AND sal > 2000; SELECT * FROM emp WHERE (job = 'CLERK' OR deptno = '30') AND sal > 2000; 10.order by子句,对查询结果进行排序 --order by 列名 【asc/desc】 --asc表示升序排列,desc表示降序排列,如果不写,则默认升序排列 --order by 子句必须出现在select 语句的最后 SELECT * FROM emp WHERE deptno =20 ORDER BY sal; SELECT * FROM emp WHERE deptno =20 ORDER BY empno DESC; --order by 不仅可以对数字进行排序,也可以对字符或者日期进行排序 SELECT * FROM emp ORDER BY ename; SELECT * FROM emp ORDER BY hiredate DESC; --order by 后面可以跟多个列 SELECT * FROM emp ORDER BY deptno,sal DESC; --order by 后面的列可以不出现在select后面 SELECT ename,sal,deptno FROM emp ORDER BY job; --为了结果的可读性,一般还是会把排序的列写在select --order by 后面可以跟列的运算 SELECT ename,job,sal,deptno FROM emp ORDER BY sal*12; SELECT ename,job,deptno,hiredate FROM emp ORDER BY sysdate-hiredate DESC; --order by 后面可以跟列的别名 SELECT ename,job,sal,sal*12 nianxin FROM emp ORDER BY nianxin; 第四章 单行函数 --函数 f(x)=2x+5 f(4)=13 --函数实现特定的功能或者计算,给予函数中的参数赋值,就可以得到函数的计算结果 1.字符函数 --upper(x) 将字符串x 转换成大写 SELECT UPPER('abcdefg') FROM emp; --dual 数据库中默认的一张空表,可以用来做计算 SELECT UPPER('abcdefg') FROM dual; --函数中的参数,如果是字符串需要加引号,如果是列名不需要加引号 SELECT * FROM emp WHERE UPPER(ename) = UPPER('allen'); --lower(x) 将字符串x转换成小写 SELECT LOWER('ABCEDFG') FROM dual; SELECT LOWER(ename),LOWER(job) FROM emp; SELECT * FROM emp WHERE LOWER(job) = 'clerk'; --initcap(x) 将字符串x的首字母大写,其他字母小写 SELECT INITCAP('ABCDefg') FROM dual; SELECT INITCAP(ename),INITCAP(job) FROM emp; --length(x) 返回字符串x的长度 SELECT LENGTH('abcdefg') FROM dual; SELECT ename,LENGTH(ename),job,LENGTH(job) FROM emp; SELECT * FROM emp WHERE LENGTH(ename) = 5; --nvl(x,n) 判断x的值,如果x为空,则返回n,如果x不为空,则返回x SELECT NVL('abcdefg',0) FROM dual; SELECT comm,NVL(comm,0) FROM emp; SELECT ename,job,deptno,sal,comm,12*(sal+NVL(comm,0)) FROM emp; --参数n 只能是数字,不能是其他类型的数据 --SELECT ename,job,deptno,mgr,NVL(mgr,'BOSS') FROM emp; --nvl2(x,y,z) 判断x的值,如果x为空值则返回z,如果x不为空则返回y SELECT ename,job,deptno,sal,comm,12*(sal+NVL2(comm,comm,0)) FROM emp; SELECT ename,job,deptno,mgr,NVL2(mgr,'EMPLOYEE','BOSS') FROM emp; --y 和 z 的数据类型必须一致 SELECT ename,job,deptno,mgr,NVL2(mgr,mgr,1) FROM emp; --replace(x,y,z) 在字符串x 中查询字符串y 并且替换为字符串z SELECT REPLACE('ABCDEFG','CDE','XYZ') FROM dual; SELECT ename,REPLACE(ename,'A','WWWWW') FROM emp; --substr(x,m,n) 在字符串x中从第m个字符截取长度为n的子字符串 SELECT SUBSTR('ABCDEFG',3,3) FROM dual; SELECT ename,SUBSTR(ename,2,3) FROM emp; --第三个参数n 可以不写,如果不写则默认截取后面所有的字符 SELECT SUBSTR('ABCDEFG',3) FROM dual; SELECT ename,SUBSTR(ename,2) FROM emp; SELECT * FROM emp WHERE SUBSTR(ename,1,1) = 'A'; SELECT * FROM emp WHERE SUBSTR(ename,LENGTH(ename)) = 'S'; --第二个参数m 可以为负数,如果m是负数则从后向前数 SELECT ename,SUBSTR(ename,-3,2) FROM emp; --第二参数m 大于字符串长度返回空值 SELECT ename,SUBSTR(ename,6) FROM emp; --lpad(x,n,y) 使用字符串y 将字符串x 从左边补齐到n个字符的长度 --rpad(x,n,y) 使用字符串y 将字符串x 从右边补齐到n个字符的长度 SELECT ename,LPAD(ename,10,'name') FROM emp; SELECT ename,LPAD(ename,10,'*') FROM emp; SELECT ename,RPAD(ename,10,'name') FROM emp; SELECT ename,RPAD(ename,10,'*') FROM emp; --第三个参数y 可以不写,如果不写默认使用空格来补齐 SELECT ename,LPAD(ename,10) FROM emp; SELECT ename,RPAD(ename,10) nn FROM emp; --ltrim(x,y) 将字符串x 左边的字符串y 去掉 --rtrim(x,y) 将字符串x 右边的字符串y 去掉 SELECT LTRIM('abcdefgabc','abc') FROM dual; SELECT RTRIM('abcdefgabc','abc') FROM dual; SELECT RTRIM('abcdefgbcaacbabc','abc') FROM dual; SELECT RTRIM('abcdefgbcaacebabc','abc') FROM dual; SELECT ename,LTRIM(ename,'S') FROM emp; SELECT ename,RTRIM(ename,'S') FROM emp; SELECT ename,LTRIM(RTRIM(ename,'S'),'S') FROM emp; --第二个参数y 可以不写,如果不写默认去掉空格 SELECT LTRIM(' hello ') he FROM dual; SELECT RTRIM(' hello ') he FROM dual; --trim(both/heading/trailing y from x) 去掉字符串x 两边 / 左边 / 右边 的字符串y --both 表示两边,也可以不写 --heading 表示左边,trailing 表示右边 SELECT ename,TRIM('S' FROM ename) FROM emp; --y 可以不写,如果不写默认去掉空格 SELECT TRIM(' hello ') he FROM dual; --concat(x,y) 将字符串x和字符串y连接起来,跟 || 功能一致 --concat 函数中只能有2 个参数 SELECT CONCAT(ename,job) FROM emp; SELECT ename||' is a '||job FROM emp; SELECT CONCAT(CONCAT(ename,' is a '),job) FROM emp; --instr(x,y,m,n) 返回字符串y 在字符串x 中第m 个字符之后出现第n 次的位置 SELECT ename,INSTR(ename,'L',3,2) FROM emp; --n 可以不写,如果不写默认返回第一次出现的位置 SELECT ename,INSTR(ename,'L',3) FROM emp; --m 可以不写,如果不写默认从第一个字符开始查找 SELECT ename,INSTR(ename,'L') FROM emp; --m 可以为负,如果为负则从倒数第 m 个字符开始向左查找 SELECT ename,INSTR(ename,'L',-3) FROM emp; --查找员工姓名中不包含字母A的员工 SELECT * FROM emp WHERE ename NOT LIKE '%A%'; SELECT * FROM emp WHERE INSTR(ename,'A') = 0; 2.数字函数 --floor(n) 返回小于等于数字n 的最大整数,向下取整 SELECT FLOOR(123.856),FLOOR(123),FLOOR(-123),FLOOR(-123.456) FROM dual; --mod(m,n) 返回m 除以n 的余数 SELECT MOD(123,45) FROM dual; SELECT MOD(43.6,6.7) FROM dual; SELECT ename,job,deptno,empno,MOD(empno,4) FROM emp; --round(m,n) 将数字m 精确到小数点后n 位 SELECT ROUND(123.456,2),ROUND(123.456,1),ROUND(123.456,3), ROUND(123.456,0) FROM dual; --n 可以为负数,如果是负数则精确到小数点左边n为 SELECT ROUND(127.456,-1),ROUND(183.456,-2),ROUND(523.456,-3) FROM dual; --n 可以不写,如果不写默认精确到个位 SELECT ROUND(127.456),ROUND(123.789) FROM dual; --TRUNC(m,n) 将数字m 截取到小数点后n 位 SELECT TRUNC(123.456,2),TRUNC(123.456,1),TRUNC(123.456,3), TRUNC(123.456,0) FROM dual; --n 可以为负数,如果是负数则截取到小数点左边n为 SELECT TRUNC(127.456,-1),TRUNC(183.456,-2),TRUNC(523.456,-3) FROM dual; --n 可以不写,如果不写默认截取到个位 SELECT TRUNC(127.456),TRUNC(123.789) FROM dual; 3.日期函数 SELECT sysdate FROM dual; --查询系统时间 --对日期的加减运算,是对天数进行运算 --add_months(x,n) 给日期x加上n个月 SELECT ADD_MONTHS(sysdate,6) FROM dual; SELECT hiredate,ADD_MONTHS(hiredate,3) FROM emp; --last_day(x) 返回日期x 所在月份的最后一天 SELECT LAST_DAY(sysdate) FROM dual; SELECT hiredate,LAST_DAY(hiredate) FROM emp; --next_day(x,y) 返回日期x 之后的下一个星期几 SELECT NEXT_DAY(sysdate,'FRIDAY') FROM dual; SELECT hiredate,NEXT_DAY(hiredate,'SATURDAY') FROM emp; |
TAG: