mysql 條件查詢、邏輯查詢、模糊查詢、通配符、轉義字符

通配符不等代表null

條件查詢

select 查詢列表 from 表名 where 篩選條件;

分類:

按條件表達式篩選 =，！=
按邏輯表達式篩選 and or not
模糊查詢 like、between and、in、is null

查詢工資大于12000的員工信息

SELECT * FROM employees WHERE salary >12000;

查詢部門編號不等90的員工名和部門編號,要查詢的編號

SELECT last_name,department_id FROM employees WHERE department_id <>90;

按邏輯表達式篩選

查詢工資在10000到20000之間的員工名、工資以及獎金

SELECT last_name ,salary,commission_pct FROM employees WHERE salary<20000
AND salary>10000;

查詢部門編號不是在90到110之間，或者工資高于15000的員工

SELECT
    *
FROM employees
WHERE department_id <90 
OR  department_id >110
OR      salary>15000;
#或者
SELECT *
FROM employees
WHERE  NOT(department_id>=90 AND department_id<=110) OR salary>15000;

#查找員工姓名和工資不在8000到17000，且按工資升序排列

SELECT
    last_name,
    salary
FROM
    employees
WHERE
 salary NOT  BETWEEN 8000 AND 17000
ORDER BY
    salary ASC;

模糊查詢
like,查詢員工名中包含字符a的員工信息

SELECT 
    *
FROM 
    employees
WHERE
    last_name LIKE '%a%';   //百分號%是通配字符,代表其他任意多個字符，_代表任意一個字符

案例:查詢員工名中第三個字符為e，第五個字符為a的員工名和工資

SELECT 
    last_name,
    salary
FROM employees
WHERE 
    last_name LIKE '__e_a%';  //2個下劃線+e+一個下劃線+a

案例：查詢員工名中第二個字符為_的員工名,正常轉義字符\

SELECT 
    last_name
FROM 
    employees
WHERE
    last_name LIKE '_\_%';

自定義轉義字符

SELECT 
    last_name
FROM 
    employees
WHERE
    last_name LIKE'_@_%' ESCAPE '@';