（三）.普通查询课堂SQL

普通查询

SELECT * FROM EMP;    --实际开发慎用

1.查询指定列(养成习惯)

SELECT EMPNO,ENAME,JOB,SAL FROM EMP;

2.查询指定行

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

3.别名的使用

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

SELECT EMPNO AS 员工号,ENAME AS 姓名 FROM EMP;

SELECT E.ENAME,E.JOB FROM EMP E;

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

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

5.查询中使用NULL(0不是null)

SELECT * FROM EMP WHERE COMM IS NULL;

6.使用常量

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

7.伪列

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;

9.排序 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 年薪;

10.范围查询 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;