数据库笔记02

--NULL与数字进行运算，结果为NULL
SELECT ENMAE, COMM, COMM+1 FROM EMP;
--NULL与字符串运算结果不会改变原串
SELECT ENAME, COMM,COMM||'1' FROM EMP;
--处理NULL值		NVL（字段1，值2）如果字段1位NULL，给你值2
SELECT ENAME 姓名，COMM 奖金，NVL（COMM,0）+1 "＋1后的奖金" FROM EMP;

--条件查询
SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO = 30;

--执行顺序	FROM---WHERE---SELECT

--  =  >  <  >= <= != <>
--所有员工中薪资>800
SELECT * FROM EMP WHERE SAL>800;
SELECT * FROM EMP WHERE ENAME = 'SMITH';

--求除了SMITH以外的所有员工的信息
SELECT * FROM EMP WHERE ENAME != 'SMITH';
SELECT * FROM EMP WHERE ENAME <> 'SMITH';
SELECT * FROM EMP WHERE NOT ENMAE = 'SMITH';

--AND  OR  NOT     BETWEEN AND
SELECT * FROM EMP WHERE SAL>=1500 AND SAL<=2500;
SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 2500;	-->= <=
--求除了薪资>1500和30部门的员工名称,部门标号,薪资
select ename,deptno,sal from emp where not sal>=1500 and not deptno=30;
select ename,deptno,sal from emp where not(sal>=1500 or deptno=30);

--求有奖金的人
SELECT * FROM EMP WHERE COMM IS NOT NULL;
SELECT * FROM EMP WHERE NOT COM IS NULL;

--集合操作
--求薪资>1500或者30部门的员工名称，部门标号，薪资
SELECT * FROM EMP WHERE SAL>=1500
UNION 
SELECT * FROM EMP WHERE DEPTNO=30;
--UNION ALL ,全集（不去重）
SELECT * FROM EMP WHERE SAL>=1500
UNION ALL
SELECT * FROM EMP WHERE DEPTNO=30;

--MINUS求没有员工在的部门的编号
SELECT DEPTNO FROM DEPT
MINUS 
SELECT DISTINCT DEPTNO FROM EMP;

--LIKE 模糊匹配		%任意个任意字符		_任意一个字符
--姓名以A开头的所有员工的信息
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
--使用模糊匹配做精确匹配
SELECT * FROM EMP WHERE ENAME LIKE 'JONES';
--姓名中第二个字符为A的所有员工姓名
SELECT ENAME FROM EMP WHERE ENAME LIKE '_A%';

--查找姓名中带有%的用户
SELECT * FROM EMP WHERE ENAME LIKE '%C%%' ESCAPE('C');	--任意指定转义字符，需要使用ESCAPE()中说明


--事务
COMMIT;
ROLLBACK;

--IN匹配后面结果集中的任意一个数据，无法做区间判断
SELECT * FROM EMP WHERE SAL IN (1500,2000,2500,5000)
SELECT * FROM EMP WHERE ENAME IN ('KING','SMITH');

--子查询	查询语句中嵌套查询语句
SELECT * FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE IN('SALES','ACCOUNTING'));
--查询销售部中工资大于1500的员工信息
SELECT * FROM EMP WHERE SAL>1500 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALAS');

--EXISTS 存在即保留  存在即合法
SELECT * FROM EMP WHERE EXISTS (SELECT * FROM DEPT WHERE DNAME ='SALES');
--获取所有行记录
SELECT * FROM EMP ;
SELECT * FROM EMP WHERE 1=1;

--排序ORDER BY DESC 降序 / asc 升序
--SELECT * FROM 表名 WHERE 行过滤条件 ORDER BY 排序字段;	--默认升序
--FROM--WHERE-SELECT--ORDER BY 
SELECT * FROM EMP ORDER BY SAL ASC ,EMPNO DESC;