SQL查询语句包

简单查询

SELECT * FROM emp;

sqlplus的基本设置：

-- 设置每行的宽度
SET LINESIZE 120;
-- 设置每页行数
SET PAGESIZE 30;
-- 设置某一列显示的宽度

COL 列名 FOR A长度;

例子：COL ENAME FOR A10;-- 设置ENAME数据列显示宽度为10

--切换连接（如果是sys的话才需要加AS SYSDBA,其他账户不用加）

CONN sys/change_on_install AS SYSDBA;

-- 查询用户下的所有数据表
SELECT * FROM tab;
-- 查看用户表的结构
DESC 表名;-- sqlplus 特有
--观察 dept表数据内容
SELECT * FROM dept;
-- 简单查询基本语法一
-- SELECT [DISTINCT] * FROM emp;
例子：SELECT * FROM emp;
-- 简单查询基本语法二
SELECT 列名1,列名2 FROM emp
例子：SELECT empno,ename,sal FROM emp;
-- 查询语句，别名的使用
SELECT empno AS "编号",ename AS "姓名",sal AS "薪资" 
FROM emp;
-- 别名的简写
SELECT empno "编号",ename "姓名",sal "薪资" 
FROM emp;
SELECT empno "001编号",ename 姓名,sal 薪资
FROM emp;
-- 查询 emp 表的所有职位信息：
SELECT DISTINCT job
FROM emp;
-- 查询 emp表的员工编号、姓名、职位、年薪(薪资13 + 补贴(300 + 180)*12)。则：
SELECT empno,ename,job,sal*13 + (300+180)*12 年薪
FROM emp;
-- 查询中使用常量

SELECT '常量' AS "常量别名",empno,ename,job 

FROM emp;

-- 查询中使用连接符，连接数据列
SELECT empno || ename
FROM  emp;
-- 查询结果显示格式为： 编号：7369，姓名：SMITH 。
SELECT '编号：'||empno||'，姓名：'||ename AS "雇员信息"
FROM emp;
SELECT '编号:'||empno||',姓名:'||ename||',职位:'||job||',领导编号:'||mgr||',雇佣日期:'||hiredate||',薪 资:'||sal||',佣金'||comm||',部门编号:'||deptno AS "雇员信息"
FROM emp;
SELECT empno 编号,ename 姓名,job 职位,mgr 领导编号,hiredate 雇佣日期,sal 薪资,comm 佣 金,deptno 部门编号
FROM emp;
-- 限定(条件)查询。
SELECT [DISTINCT] 列名....
FROM 表名
WHERE 条件
-- 查询emp表 薪资大于 1500的雇员编号，姓名，薪资，职位
SELECT empno,ename,sal,job
FROM emp
WHERE sal > 1500;
-- 查询emp表中，名字叫做 SMITH 的雇员的所有信息
SELECT *
FROM emp

WHERE ename = 'SMITH';

--AND

--查询emp表中，基本工资在 1200~2500之间 的雇员的所有信息
SELECT *
FROM emp
WHERE sal>1200 AND sal < 2500;
-- 查询 emp表中，部门编号不是30，且职位是 MANAGER 的雇员信息
SELECT *
FROM emp

WHERE deptno<>30 AND job='MANAGER';

--NOT

-- 查询emp表中，薪资大于 2500，或者 职位是 SALESMAN 的雇员的 编号，姓名，职位，薪资

-- 查询职位不是 CLERK 的雇员信息
SELECT *
FROM emp

WHERE NOT job='CLERK';

--BETWEEN...AND

-- 查询emp表中，基本工资在 1200~2500(包含1200和2500)之间 的雇员的所有信息
SELECT *
FROM emp
WHERE sal BETWEEN 1200 AND 2500;
SELECT *
FROM emp
WHERE sal>=1200 AND sal<=2500;
-- 查询emp表中，所有时间 hiredate 在1981年雇佣的雇员信息。
-- 1981年指：（1981-01-01~1981-12-31）
SELECT *
FROM emp 

WHERE hiredate BETWEEN '01-1月 -81' AND '31-12月 -81';

--IS NULL、IS NOT NULL

-- 查询emp表中，所有佣金 COMM 为空的雇员信息（没有提层的雇员）。
SELECT *
FROM emp
WHERE comm IS NULL;
SELECT *
FROM emp
WHERE NOT comm IS NULL;
SELECT *
FROM emp

WHERE  comm IS NOT NULL;

--IN、NOT IN

--  查询EMP 表中，部门信息为 10，和30 的雇员信息
SELECT *
FROM emp
WHERE empno NOT IN(7844,7900);
SELECT *
FROM emp

WHERE NOT(empno IN(7844,7900));

--查询排序

--升序、降序

--查询所有雇员信息，并按部门编号降序排列，如果部门编号相同则按薪资升序排列。
SELECT *
FROM emp
ORDER BY deptno DESC,sal ASC;
--查询薪资高于2500的雇员信息，并按姓名升序排列。
SELECT *
FROM emp
WHERE sal>2500

ORDER BY ename ASC;

--查询 雇员姓名包含“A”的所有雇员信息，查询的结果按照基本薪资由高到低（降序）排列，如果基本薪资相 同则按照雇佣日期由早到晚（升序）排列。
SELECT *
FROM emp
WHERE ename LIKE '%A%'
ORDER BY sal DESC,hiredate ASC;
--如果SELECT子句使用了DISTINCT之后不能使用SELECT子句没有的字段进行排序
--下面的示例是错误的不能根据sal来排序
SELECT DISTINCT job
FROM emp
ORDER BY sal DESC;
--函数查询
--字符串函数
--将字符串大写转为小写
SELECT LOWER('HELLOWORLD')
FROM dual;
SELECT UPPER('helloworld')
FROM dual;
SELECT INITCAP('hElLOWorld')
FROM dual;
--获取字符串(字符)长度
SELECT LENGTH('Hello World')
FROM dual;
--获取字符串(字节)长度
SELECT LENGTHB('Hello World！')
FROM dual;
--截取字符串Hello
SELECT SUBSTR('Hello World!',1,5)
FROM dual;
--截取字符串World!
SELECT SUBSTR('Hello World!',6)
FROM dual;
--替换
SELECT REPLACE('Hello World!','world','世界')
FROM dual;
--数值函数
--设置保留的位数，四舍五入,如果保留的位数是负数的话是保留到十位
SELECT ROUND(123.789,2)
FROM dual;
--查询员工工资，保留到十位
SELECT ROUND(sal,-1)
FROM emp;
--TRUNC
SELECT TRUNC(128.789)
FROM dual;
--编号对薪资取余
SELECT MOD(empno,sal)
FROM emp;
SELECT MOD(10,3)
FROM dual;
--日期函数SYSTIMESTAMP、SYSDATE
--三个日期的计算公式一，日期+天数
SELECT SYSDATE+2
FROM dual;
SELECT SYSTIMESTAMP
FROM dual;
--三个日期的计算公式一，日期-天数
SELECT SYSDATE-2
FROM dual;
--计算所有员工的入职时间到现在的天数(取整),前一个数-后一个数
SELECT TRUNC(SYSDATE-hiredate)
FROM emp;
--查看4个月后的日期
SELECT ADD_MONTHS(SYSDATE,4)
FROM dual;
--查看 姓名为 SCOTT 的雇员 雇佣日期前一个月的日期。
SELECT hiredate,ADD_MONTHS(hiredate,-1)
FROM emp
WHERE ename='SCOTT';
--查看 所有雇员到今天为止 所雇佣的月份数。
SELECT MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp;
--查看当前时间日期所在月份的最后一天的日期。
SELECT LAST_DAY(SYSDATE)
FROM dual;
--查看下一个星期一的日期。（当前日期为2017年4月10日 星期一）
SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期一')--结果为9月4
FROM dual;
SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二')--结果为8月29
FROM dual;
--转换函数
--将 '13' 字符串数值内容和 '22' 字符串数值内容 相加。
SELECT TO_NUMBER('13')+TO_NUMBER('32')+22
FROM dual;
--TO_CHAR();
--查询出scott入职的那个月有多少天
SELECT TO_CHAR(LAST_DAY(hiredate),'dd')
FROM emp
WHERE ename='SCOTT';
--格式化金额(999的位置只能多，不能少，L必须在最前面，中间不能有空格之类的)
SELECT TO_CHAR(12345678,'L999,999,999')
FROM dual;
--格式化时间
--计算2008-08-18 08:08:08到现在是多少天
SELECT SYSDATE-TO_DATE('2008-08-18 08:08:08','yyyy-mm-dd hh24:mi:ss')
FROM dual;
--通用函数
--查询雇员职位薪资提成（没有则显示0）NVL
SELECT job,sal,NVL(comm,0)
FROM emp;
--查看emp的雇员编号 姓名 薪资 提成 月薪（薪资+提成）
SELECT empno,ename,sal,comm,sal+NVL(comm,0)
FROM emp;
SELECT DECODE(empno,7788,'匹配成功7788',8888,'匹配成功8888','匹配不到，添加默认值')
FROM emp;
--查询雇员编号 名字 职位(用中文显示) DECODE
SELECT empno,ename,DECODE(job,'MANAGER','经理','SALESMAN','销售','CLERK','办事员','其他')
FROM emp;
--多表查询
--查询雇员编号,姓名,职位,薪资,等级
SELECT e.empno,e.ename,e.job,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
--查询雇员编号,姓名,薪资,等级,职位,部门名称
SELECT  e.empno,e.ename,e.sal,s.grade,e.job,d.dname
FROM emp e,salgrade s,dept d
WHERE (e.sal BETWEEN s.losal AND s.hisal) AND e.deptno=d.deptno;
--查询每个雇员的编号,姓名,职位,薪资,部门名称,部门位置
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno;
--查询每个雇员的编号，姓名，职位，工资，工资等级。
SELECT e.empno,e.ename,e.job,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
--查询每个雇员的编号，姓名，职位，工资，工资等级，部门名称。
SELECT e.empno,e.ename,e.job,e.sal,s.grade,d.dname
FROM emp e,salgrade s,dept d
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
--内连接
--查询每个雇员的编号,姓名,职位,薪资,部门名称,部门位置
--JOIN ON
SELECT e.empno,e.ename,e.job,e.sal,d.dname,e.deptno
FROM emp e JOIN dept d ON(e.deptno=d.deptno);
--JOIN USING
-- 表A JOIN 表B USING(指定一个可以消除笛卡尔积的关联字段)  
-- 注意：USING()指定的关联字段后，SELECT不能出现被关联表的关联字段。如,错误例子
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.deptno
FROM emp e JOIN dept d USING(deptno);
--自然连接
-- NATURAL JOIN  字句。要求 两张之间必须存在关联字段才可以
SELECT e.empno,e.ename,e.job,e.sal,d.dname
FROM emp e NATURAL JOIN dept d;
-- 查询每个雇员的编号，姓名，职位，工资，工资等级。
-- JOIN...ON() 子句  
SELECT e.empno,e.ename,e.job,e.sal,s.grade
FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);
--内连接，自身（两张相同的表）连接
--要求查询员工编号 姓名、职位、领导名称
SELECT a.empno 编号,a.ename 姓名,a.job 职位,b.ename 领导名称
FROM emp a JOIN emp b ON(a.mgr=b.empno);
--外连接(左外连接)
-- 查询出每一位雇员的编号、姓名、职位、部门编号、部门名称。
SELECT e.empno,e.ename,e.job,e.deptno,d.dname
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
--ORACLE简洁写法
SELECT 
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);
--外连接(右外连接)
SELECT e.empno,e.ename,e.job,e.deptno,d.dname
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno=d.deptno);
--ORACLE简洁写法
SELECT e.empno,e.ename,e.job,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;
--外连接(全连接)
SELECT e.empno,e.ename,e.job,e.deptno,d.dname

FROM emp e FULL JOIN dept d ON(e.deptno=d.deptno);