5.2 条件查询
SELECT
*
FROM
employees
WHERE
salary>12000;
SELECT
last_name,
department_id,
FROM
employees
WHERE
department_id<>90;
SELECT
last_name,salary,commission_pct
FROM
employees
WHERE
salary>=10000 AND salary<=20000;
SELECT
*
FROM
employees
WHERE
NOT(department_id<90 AND department_id>110) OR salary>15000;
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
SELECT
last_name,salary
FROM
employees
WHERE
last_name LIKE '__n_l%';
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%';
SELECT
*
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
SELECT
*
FROM
employees
WHERE
employees_id BETWEEN 100 AND 120;
SELECT
last_name,department_id
FROM
employees
WHERE
department_id IN(30,50,90);
SELECT
last_name,department_id
FROM
employees
WHERE
department_id=30 OR department_id=50 OR department_id=90;
SELECT
*
FROM
employees
WHERE
job_id NOT IN('SH_CLERK','IT_PROG');
SELECT
*
FROM
employees
WHERE
NOT(job_id ='SH_CLERK' OR job_id = 'IT_PROG');
SELECT
*
FROM
employees
WHERE
commission_pct IS NULL;
SELECT
*
FROM
employees
WHERE
commission_pct IS NOT NULL;
= 只能判断普通的内容
IS NULL 只能判断NULL值
<=> 安全等于,既能判断普通内容,又能判断NULL值
5. 3 排序查询
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary;
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC;
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
SELECT * FROM employees ORDER BY salary ASC,employees_id DESC;