--1.1查询表中的所有行与列
desc emp;
select * from emp;
--1.2从表中检索部分行
select * from emp where job='SALESMAN';
--1.3查找空值(NULL不支持加,减,乘,除,大小比较,相等比较)
SELECT * FROM emp WHERE comm IS NULL;
SELECT REPLACE('abcde','a',NULL) AS str FROM dual;--bcde
--GREATEST(expr_1, expr_2, ...expr_n)函数,从表达式中返回最大的值。数据类型以expr_1为准。
SELECT greatest(1,NULL) from dual;--NULL
--1.4将空值转换为实际值
--coalesce(e1,e2,...)函数,如果comm IS NULL则返回0,否则返回comm
--nvl(ex1,ex2)
SELECT coalesce(comm,0) FROM emp;
CREATE OR REPLACE VIEW v AS
SELECT NULL AS C1,NULL AS C2,1 AS C3,NULL AS C4,2 AS C5,
NULL AS C6 FROM DUAL UNION ALL
SELECT NULL AS C1,NULL AS C2,NULL AS C3,3 AS C4,NULL AS C5,2 AS C6 FROM DUAL;
SELECT * FROM v;
SELECT COALESCE(C1,C2,C3,C4,C5,C6) FROM v;
--1.5查询满足多个条件的行
SELECT * FROM EMP WHERE (DEPTNO=10 OR COMM IS NULL OR (SAL <= 2000 AND DEPTNO=20));
--1.6从表中检索部分列
SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP WHERE DEPTNO = 10;
--1.7为列取有意义的名称
SELECT ENAME AS 姓名,DEPTNO AS 部分编号, SAL AS 工资,COMM AS 提成 FROM EMP;
--1.8在where子句中引用取别名的列
SELECT * FROM (SELECT SAL AS 工资,COMM AS 提成 FROM EMP ) x WHERE 工资<1000;
--1.9拼接列
SELECT ename || '的工作是' || job AS msg FROM emp WHERE deptno = 10;
--1.10在SELECT中使用条件逻辑
SELECT ename,sal,
CASE
WHEN sal<=2000 THEN '过低'
WHEN sal>=4000 THEN '过高'
ELSE 'OK'
END AS status
FROM emp WHERE deptno = 10;
--按工资分档次查询人数
SELECT 档次,COUNT(*) AS 人数 FROM (SELECT (
CASE
WHEN sal <= 1000 THEN '0000-1000'
WHEN sal <=2000 THEN '1000-2000'
WHEN sal <=3000 THEN '2000-3000'
WHEN sal <=4000 THEN '3000-4000'
WHEN sal <=5000 THEN '4000-5000'
ELSE '好高'
END
) AS 档次,ename,sal FROM emp)
GROUP BY 档次 ORDER BY 1;
--1.11限制返回的行数
--取前两行数据
SELECT * FROM emp WHERE rownum <=2;
--取第二行数据
SELECT * FROM (SELECT rownum as sn,emp.* from emp WHERE rownum<=2) where sn = 2;
--1.12从表中随机返回n条记录
SELECT empno,ename FROM (SELECT empno,ename FROM emp ORDER BY dbms_random.value()) WHERE rownum<=3;
--1.13模糊查询
CREATE OR REPLACE VIEW v AS
SELECT 'ABCEDF' as vname FROM dual
UNION ALL
SELECT '_BCEFG' as vname FROM dual
UNION ALL
SELECT '_BCEDF' as vname FROM dual
UNION ALL
SELECT '_\BCEDF' as vname FROM dual
UNION ALL
SELECT 'XYCEG' as vname FROM dual
SELECT * FROM v WHERE vname LIKE '%CED%';
SELECT * FROM v WHERE vname LIKE '_BCE%';
SELECT * FROM v WHERE vname LIKE '\_BCE%' ESCAPE '\';
SELECT * FROM v WHERE vname LIKE '\_\\BCE%' ESCAPE '\';