Oracle简单查询，常用函数（SQL语句集）

--全表查询
SELECT * FROM 表名;
SELECT * FROM EMP;
--字段查询
SELECT 字段,字段 FROM 表名;
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;
--查询所有用户
SELECT USERNAME FROM DBA_USERS;
--查看用户的表
SELECT TABLE_NAME FROM USER_TABLES;
SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='SYS';
--查看表结构
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMP';
--条件查询
SELECT 字段... FROM 表名 WHERE 条件;
SELECT * FROM EMP WHERE SAL>2000;
SELECT * FROM EMP WHERE SAL>2000 AND SAL<3000;
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;
SELECT * FROM EMP WHERE DEPTNO =10;
--逻辑查询 OR 或 AND 与
SELECT * FROM EMP WHERE DEPTNO=10 AND JOB='MANAGER'
SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='ANALYST';
SELECT * FROM EMP WHERE (DEPTNO=10 AND JOB='MANAGER') OR (DEPTNO=20 AND JOB='ANALYST');
--IN
SELECT * FROM EMP WHERE EMPNO=7782 OR EMPNO=7788 OR EMPNO=7369;
SELECT 字段 FROM EMP WHERE 字段 IN(字段值);
SELECT * FROM EMP WHERE EMPNO IN(7782,7788,7369);
-- 不等于 <>
SELECT * FROM EMP WHERE DEPTNO <> 10;--推荐
SELECT * FROM EMP WHERE DEPTNO != 10;
SELECT * FROM EMP WHERE DEPTNO NOT IN(10);
-- IS NULL 将为空的数据查询出来
SELECT * FROM EMP WHERE COMM IS NULL;
--模糊查询 %表示任意可以是0到多个字符 _表示任意一个字符
--查询员工姓名中以S开头的信息
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
--查询员工姓名中以S结尾的信息
SELECT * FROM EMP WHERE ENAME LIKE '%S';
--查询员工姓名中第三位是A的信息
SELECT * FROM EMP WHERE ENAME LIKE '__A%';
--查询员工姓名中含有S的信息
SELECT * FROM EMP WHERE ENAME LIKE '%S%';
--查询里面的排序 ORDER BY
--按照数字
SELECT * FROM EMP ORDER BY SAL;--系统默认升序
SELECT * FROM EMP ORDER BY SAL DESC; --降序
--按照字母
SELECT * FROM EMP ORDER BY ENAME ASC; --升序
SELECT * FROM EMP ORDER BY ENAME DESC;--降序
--按照多个字段排序 排序是有顺序的先按照前面字段排序，如果前面字段相同再按照后面字段进行排序
--按照部门的编号升序，工资的降序
SELECT * FROM EMP ORDER BY DEPTNO,SAL DESC;
--按照名字的升序，工资的升序
SELECT * FROM EMP ORDER BY JOB,SAL,ENAME;
--COUNT(字段) 统计 会忽略null值
SELECT COUNT(COMM) FROM EMP;
--MAX 求最大值 AS 别名
SELECT MAX(SAL) AS MAXSAL FROM EMP;
--MIN 求最小值
SELECT MIN(SAL) FROM EMP;
--SUM 求和
SELECT SUM(SAL) FROM EMP;
--AVG 求平均值
SELECT AVG(SAL) FROM EMP;
--DISTINCT 去掉重复值 在字段的前面加上DISTINCT
SELECT DEPTNO FROM EMP;
SELECT DISTINCT DEPTNO FROM EMP;
--分组查询 GROUP BY 
--按部门统计人数
SELECT DEPTNO,COUNT(*) AS TATALS FROM EMP GROUP BY DEPTNO;
--求部门的平均工资
SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO;
--求每个部门的最高工资
SELECT DEPTNO,MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO;
--求每个部门的最低工资
SELECT DEPTNO,MIN(SAL) AS MINSAL FROM EMP GROUP BY DEPTNO;
--去掉重复值
SELECT DISTINCT DEPTNO FROM EMP;
--GROUP BY 按照字段分组进行结果统计
--HAVING 对统计结果进行筛选
--ORDER BY  排序
--如果三者一起使用：顺序是先GROUP BY 再HAVING 最后ORDER BY
--求出部门平均工资大于2000的部门并按平均工资进行降序排序
SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY
 DEPTNO HAVING AVG(SAL) >2000 ORDER BY AVG(SAL) DESC;
--求出部门人数大于5 按部门升序排序
SELECT DEPTNO,COUNT(*) AS TATALS FROM EMP
 GROUP BY DEPTNO HAVING COUNT(*)>=5 ORDER BY DEPTNO;
--常用的系统函数 DUAL表示伪表
--求绝对值 ABS
SELECT ABS(-50) FROM DUAL;
SELECT ABS(4) FROM DUAL;
--求平方根 SQRT
--通过伪表
SELECT SQRT(9) FROM DUAL;
--通过表里面的数据
SELECT SQRT(COMM) FROM EMP;
--求幂 POWER
SELECT POWER(2,3) FROM DUAL;
SELECT POWER(2,7) FROM DUAL;
--四舍五入 ROUND
--去整
SELECT ROUND(3.1415926) FROM DUAL;
SELECT ROUND(3.8111) FROM DUAL;
--保留有效位数
SELECT ROUND(3.8222,3) FROM DUAL;
SELECT ROUND(3.8262,2) FROM DUAL;
--字符连接 CONCAT
SELECT CONCAT('青青','你好') AS ENAME FROM DUAL;
SELECT CONCAT('JEFFREY','你好') AS ENAME FROM DUAL;

--截取字符串 SUBSTR(c1,n1,n2)
--C1表示字符串 n1 表示从第几位开始（起始值是1） n2表示截取多少位，
--如果没有n2值就一直截取到末尾
SELECT SUBSTR('JEFFREY',1,3) AS ENAME FROM DUAL;
SELECT SUBSTR('JEFFREY',2) FROM DUAL;
SELECT SUBSTR(' zzz ',5)FROM DUAL;

--替代REPLACE
SELECT REPLACE('YOUR LOVE YOU','YOUR','I') AS ENAME FROM DUAL;
SELECT REPLACE('zzz南生与我 很聪明','很聪明','真的很聪明') AS ENAME FROM DUAL;
--转换成大写 UPPER
SELECT UPPER('sdsjsfjs')AS ENAME FROM DUAL;
SELECT UPPER('JDKSsddJD') AS ENAME FROM DUAL;
--转换成小写 LOWER
SELECT LOWER('DJFJSFdjfDJFS') AS ENAME FROM DUAL;

--时间类型转换成字符类型 TO_CHAR
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') AS TIMES FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY') AS TIMES FROM DUAL;
--字符类型转换成时间类型 TO_DATE
SELECT TO_DATE('2015-12-12','YYYY-MM-DD') AS TIMES FROM DUAL;

--对日期进行加减 ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-12) FROM DUAL;
--求一个月里面的最后一天 LAST_DAY
--求系统时间的月份的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--求EMP表里面的受雇日期的月份的最后一天
SELECT LAST_DAY(HIREDATE) FROM EMP;