#2、in
/*
功能：查詢某欄位的值是否屬于指定的串列之內
a in(常量值1，常量值2，常量值3，...)
a not in (常量值1，常量值2，常量值3，...);
*/
#a案例一：查詢部門編號是30/50/90的員工名和部門編號
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');
#3/between and/not between and
/*判斷某個欄位的值是否介于什么什么之間
*/
#案例一：部門編號是30-90之間的部門編號員工姓名
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND department_id<=30;
#案例二：查詢年薪不是10000-200000之間的員工姓名、工資、年薪
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))NOT BETWEEN 100000 AND 200000;
#4、is null/is not null
#案例1：查詢獎金率為空的員工資訊
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;
#= 只能判斷普通的內容
#isnull 只能判斷null值
#<=>安全等于，既能判斷普通內容，又能判斷null值
SELECT *
FROM employees
WHERE commission_pct <=> 0.3;
練習：
SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)),department_id
FROM employees
WHERE employee_id=176;
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 30 AND 50;
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
SELECT job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/228102.html
標籤：其他
上一篇：45天閉門刷題，精通這份Java高級架構面試檔案，入職阿里漲薪20K
下一篇：硬碟重新磁區，給C盤重新分配空間