查询

普通查询

• 实际开发慎用

SELECT * FROM EMP;

查询指定列

SELECT EMPNO,ENAME,JOB,SAL FROM EMP;

查询指定行

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO=10;

查询部门号为20的员工信息，且工资大于1500

SELECT * FROM EMP WHERE DEPTNO=20 AND SAL>1500;

别名的使用

SELECT EMPNO 员工号,ENAME 姓名,JOB 职位,SAL 薪资  FROM EMP;
SELECT EMPNO AS 员工号,ENAME AS 姓名 FROM EMP;
SELECT E.ENAME,E.JOB FROM EMP E;

查询中使用NULL

• 0不是NULL

SELECT * FROM EMP WHERE COMM IS NULL;

使用常量

SELECT EMPNO,ENAME,'潭州' 工作单位 FROM EMP;

伪列

• 建立时按顺序，使用条件将始终为1
• 等于和大于超过1将失效

SELECT ROWNUM,E.* FROM EMP E;
SELECT ROWNUM,EMPNO,ENAME FROM EMP WHERE ROWNUM<5;
SELECT ROWNUM,EMPNO,ENAME FROM EMP WHERE ROWNUM>5;
SELECT ROWNUM,EMPNO,ENAME FROM EMP WHERE ROWNUM=5;
SELECT ROWNUM,E.* FROM EMP E ORDER BY SAL;

排序

• ORDER BY
• ASC(默认)升序，DESC 降序

SELECT * FROM EMP ORDER BY SAL ASC;
SELECT * FROM EMP ORDER BY SAL DESC;
SELECT * FROM EMP ORDER BY HIREDATE;  --(时间类型排序)
SELECT * FROM EMP ORDER BY HIREDATE DESC;
SELECT * FROM EMP ORDER BY ENAME;   --(字符串排序)

查询员工信息，按照工资额升序，如果工资一致就安照入职时间降序

SELECT * FROM EMP ORDER BY SAL,HIREDATE ASC;

分页查询

SELECT S.* FROM
(SELECT ROWNUM R,E.* FROM
(SELECT * FROM EMP ORDER BY SAL) E) S
WHERE R>5 AND R<=10;

查询工资大于2000的员工进行排序

SELECT * FROM EMP WHERE SAL>2000 ORDER BY SAL;

使用别名进行年薪排序

SELECT EMPNO,ENAME,JOB,SAL*12 年薪 FROM EMP ORDER BY 年薪;

范围查询

• BETWEEN..AND 范围查找
• IN 包含查找
• LIKE 正则表达式模糊查找

BETWEEN

SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;
SELECT * FROM EMP WHERE SAL>=2000 AND SAL<=3000;

IN

SELECT * FROM EMP WHERE DEPTNO IN(10,20);
SELECT * FROM EMP WHERE DEPTNO=20 OR DEPTNO=10;

LIKE

SELECT * FROM EMP WHERE ENAME LIKE '%A%';
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
SELECT * FROM EMP WHERE ENAME LIKE '_A%';

拓展

查询当前登录用户

SELECT USER FROM DUAL;   --DUAL 虚表

查询表的约束

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMP';

查询表结构

SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME='EMP';

查看当前用户下所有表

SELECT TABLE_NAME FROM USER_TABLES;