14_MySQL条件查询

本节所涉及的sql语句：

-- 去除结果集中的重复记录

SELECT job FROM t_emp;
SELECT DISTINCT job FROM t_emp;
SELECT DISTINCT job,ename FROM t_emp;

-- 条件查询(一个部门)

SELECT deptno,empno,ename,sal
FROM t_emp
WHERE deptno=10 AND sal>=2000;

-- 条件查询(两个部门)

SELECT deptno,empno,ename,sal
FROM t_emp
WHERE (deptno=10 OR deptno=20) AND sal>=2000;

-- 条件查询(两个部门，同时加排序)

SELECT deptno,empno,ename,sal,comm,
FROM t_emp
WHERE (deptno=10 OR deptno=20) AND sal>=2000
ORDER BY deptno ASC,sal DESC;


-- 算数运算符(部门为10，年收入大于15000，入职时间大于20年)

SELECT deptno,ename,sal,hiredate,
(sal+IFNULL(comm,0))*12 AS income
FROM t_emp
WHERE deptno=10 AND (sal+IFNULL(comm,0))*12>=15000
AND DATEDIFF(NOW(),hiredate)/365>=20;

-- 比较运算符

SELECT empno,ename,deptno,job,hiredate
FROM t_emp
WHERE deptno IN (10,20,30) AND job!="SALESMAN"
AND hiredate<"1985-01-01"


SELECT 
    ename,comm
FROM t_emp WHERE comm IS NOT NULL;

SELECT 
    ename,sal 
FROM t_emp WHERE comm IS NULL
AND sal BETWEEN 2000 AND 3000;

SELECT ename,sal 
FROM t_emp 
WHERE comm IS NULL
AND sal BETWEEN 2000 AND 3000
AND ename LIKE "_LAKE";