Oracle 基本数据查询

/* DISTINCT 关键字消除取值重复的记录行 */
SELECT DISTINCT ANIMAL_NAME FROM ANIMAL
/* 查看前五条数据 */
SELECT * FROM ANIMAL WHERE ROWNUM <= 5;
/* 在确定范围内查询 */
SELECT ANIMAL_NAME,ANIMAL_REGDATE FROM ANIMAL WHERE ANIMAL_REGDATE BETWEEN '2018-07-06 05:55' AND '2018-07-06 06:03';
/* 在确定集合中查询 */
SELECT ANIMAL_NAME,ANIMAL_REGDATE FROM ANIMAL WHERE ANIMAL_REGDATE IN ('2018-07-06 05:55','2018-07-06 06:03' );
/* 字符匹配 */
SELECT ANIMAL_NAME FROM ANIMAL WHERE ANIMAL_NAME LIKE '%WA%';
/* 涉及控制 NULL 的查询 */

SELECT * FROM ANIMAL WHERE ANIMAL_REGDATE IS NULL;

/* 使用 ORDER BY 子句进行查询的排序 */
SELECT * FROM ANIMAL ORDER BY ANIMAL_ID DESC;
/* 使用 GROUP BY 子句进行分组查询 （统计各部门人数）*/
SELECT DEPID,COUNT(*) AS 部门人数 FROM EMP GROUP BY DEPID;
/* 统计各部门的平均工资 */
SELECT DEPTNO AS 部门编号, AVG(SAL) AS 平均工资 FROM EMP GROUP BY DEPTNO;
/* 使用 HAVING 子句对分组进行筛选（统计个部门人数，将多于三人的部门编号输出） */

SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 3;

/* 在 WHERE 子句中定义连接 */
SELECT ani.*, sea.* FROM ANIMAL ani, SEA_ANIMAL sea WHERE sea.ANIMAL_ID = ani.ANIMAL_ID;
/* 在 FROM 子句中定义连接（内连接，也称自然连接） */
SELECT ani.ANIMAL_ID, sea.ANIMAL_ID, sea.ANIMAL_NAME FROM ANIMAL ani INNER JOIN SEA_ANIMAL sea ON sea.ANIMAL_ID = ani.ANIMAL_ID;
/* 左外连接查询 （结果集包含左表中所有行和右表中匹配行）*/
SELECT sea.animal_id, ani.animal_name, ani.animal_id FROM animal ani LEFT OUTER JOIN sea_animal sea on (ani.animal_id = sea.animal_id)
/* 右外连接查询（结果集包含右表中所有行和左表中匹配行） */
SELECT sea.animal_id, ani.animal_name, ani.animal_id FROM animal ani RIGHT OUTER JOIN sea_animal sea on (ani.animal_id = sea.animal_id)
/* 全外连接（结果集包含左右两个表中的所有行） */
SELECT sea.animal_id, ani.animal_name, ani.animal_id FROM animal ani FULL OUTER JOIN sea_animal sea on (ani.animal_id = sea.animal_id)
/* 交叉连接 */
SELECT sea.animal_id, ani.animal_name, ani.animal_id FROM animal ani FULL JOIN sea_animal sea on ani.animal_id = sea.animal_id ORDER BY ani.animal_id;
/* 嵌套查询 （使用 ANY, ALL, EXISTS 谓词的子查询）*/
SELECT * FROM ANIMAL WHERE ANIMAL_ID IN (SELECT ANIMAL_ID FROM SEA_ANIMAL);
SELECT * FROM ANIMAL WHERE ANIMAL_ID > ANY (SELECT ANIMAL_ID FROM SEA_ANIMAL);
SELECT * FROM ANIMAL WHERE ANIMAL_ID > ALL (SELECT ANIMAL_ID FROM SEA_ANIMAL);
/* 使用 EXISTS 谓词的子查询不返回任何实际数据，只产生逻辑值 TRUE 或 FALSE */

SELECT * FROM ANIMAL WHERE EXISTS (SELECT * FROM SEA_ANIMAL WHERE ANIMAL_ID >= 1);

/*  集合操作的查询 */

SELECT * FROM ANIMAL WHERE ANIMAL_NAME = 'WARE' UNION SELECT * FROM ANIMAL WHERE ANIMAL_NAME = 'WADD';

/* 查看表空间 */
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;