Oracle数据库Sql语句详解大全

hpdong

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;

yonina_1225

yonina_1225

cq520131517

good