-- 筛选出员工表中, employee_id是偶数的员工信息(名字, 员工id):
mysql>SELECT first_name, employee_id FROM employees WHERE employee_id %2=0;+-------------+-------------+| first_name | employee_id |+-------------+-------------+| Steven |100|| Lex |102|| Bruce |104|| Valli |106|| Nancy |108||...|...|-- 省略| Donald |198|| Jennifer |200|| Pat |202|| Hermann |204|| William |206|+-------------+-------------+54rowsinset(0.01 sec)
-- 查询员工表中, 工资为10000的员工信息(名字, 薪资):
mysql>SELECT first_name, salary FROM employees WHERE salary =10000;+------------+----------+| first_name | salary |+------------+----------+| Peter |10000.00|| Janette |10000.00|| Harrison |10000.00|| Hermann |10000.00|+------------+----------+4rowsinset(0.01 sec)
-- 查询员工表中, 工资在6000到8000之间的员工信息(名字, 薪资), 不包括6000和8000.
mysql>SELECT first_name, salary FROM employees WHERE salary >6000AND salary <8000;+-------------+---------+| first_name | salary |+-------------+---------+| Ismael |7700.00|| Jose Manuel |7800.00||...|...|-- 省略| Kimberely |7000.00|| Charles |6200.00|| Susan |6500.00|+-------------+---------+19rowsinset(0.00 sec)
3.2 比较运算关键字
运算符
名称
描述
示例
IS NULL
为空运算
判断值, 字符串或表达式是否为空
SELECT B FROM TABLE WHERE A IS NULL
IS NOT NULL
不为空运算
判断值, 字符串或表达式是否不为空
SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST
最小值运算
在多个值中返回最小值
SELECT D FROM TABLE WHERE C = LEAST(A, B)
GREATEST
最大值运算
在多个值中返回最大值
SELECT D FROM TABLE WHERE C = GREATEST(A, B)
BETWEEN ... AND ...
两值之间的运算
判断一个值是否在两个值之间
SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL()
为空运算函数
判断一个值, 字符串或表达式是否为空
SELECT B FROM TABLE WHERE A ISNULL(C)
IN
属于运算
判断一个值是否为列表中的任意一个值
SELECT D FROM TABLE WHERE C IN (A, B)
NOT IN
不属于运算
判断一个值是否不是一个列表中的任意一个值
SELECT D FROM TABLE WHERE C NOT IN (A, B)
LIKE
模糊匹配运算
判断一个值是否符合模糊匹配规则
SELECT C FROM TABLE WHERE A LIKE B
REGEXP
正则表达式运算
判断一个值是否符合正则表达式的规则
SELECT C FROM TABLE WHERE A REGEXP B
RLIKE
正则表达式运算
判断一个值是否符合正则表达式的规则
SELECT C FROM TABLE WHERE A RLIKE B
3.2.1 为空运算符
ISNULL或者ISNULL()判断一个值是否为NULL,如果为NULL则返回1,否则返回0.
-- 查询员工表中, 佣金比例(commission_pct)的值为NULL的员工信息(名字, 佣金比例):
mysql>SELECT first_name, commission_pct FROM employees WHERE commission_pct ISNULL;+-------------+----------------+| first_name | commission_pct |+-------------+----------------+| Steven |NULL|| Neena |NULL||...|...|-- 省略| Hermann |NULL|| Shelley |NULL|| William |NULL|+-------------+----------------+72rowsinset(0.00 sec)
-- 或者:
mysql>SELECT first_name FROM employees WHERE ISNULL(commission_pct);+-------------+----------------+| first_name | commission_pct |+-------------+----------------+| Steven |NULL|| Neena |NULL||...|...|-- 省略| Hermann |NULL|| Shelley |NULL|| William |NULL|+-------------+----------------+72rowsinset(0.00 sec)
3.2.2 不为空运算
ISNOTNULL判断一个值是否为NULL,如果不为NULL则返回1,否则返回0.
-- 查询员工表中, commission_pct不为NULL的员工信息(名字, 佣金比例):
mysql>SELECT first_name, commission_pct FROM employees WHERE commission_pct ISNOTNULL;+-------------+----------------+| first_name | commission_pct |+-------------+----------------+| John |0.40|| Karen |0.30|| Alberto |0.30|| Gerald |0.30|| Eleni |0.20||...|...|-- 省略| Jack |0.20|| Kimberely |0.15|| Charles |0.10|+-------------+----------------+35rowsinset(0.00 sec)
-- 或(先判断是否为NULL, 然后取反, 这个示例是提供一种思路):
mysql>SELECT first_name, commission_pct FROM employees WHERENOT commission_pct <=>NULL;+-------------+----------------+| first_name | commission_pct |+-------------+----------------+| John |0.40|| Karen |0.30|| Alberto |0.30|| Gerald |0.30|| Eleni |0.20||...|...|-- 省略| Jack |0.20|| Kimberely |0.15|| Charles |0.10|+-------------+----------------+35rowsinset(0.00 sec)
3.2.3 最小/大运算
-- 比较序列中最大的值(a-z对应97-122):
mysql>SELECT LEAST('g','b','t','m'), GREATEST('g','b','t','m')FROM DUAL;+------------------------+---------------------------+| LEAST('g','b','t','m')| GREATEST('g','b','t','m')|+------------------------+---------------------------+| b | t |+------------------------+---------------------------+1rowinset(0.01 sec)
-- 查询员工表中, 工资在6000到8000的员工信息(名字, 薪资):
mysql>SELECT first_name, salary FROM employees WHERE salary BETWEEN6000AND8000;+-------------+---------+| first_name | salary |+-------------+---------+| Bruce |6000.00|| Ismael |7700.00|| Jose Manuel |7800.00|| Luis |6900.00|| Matthew |8000.00||...|...|-- 省略| Sundita |6100.00|| Kimberely |7000.00|| Charles |6200.00|| Pat |6000.00|| Susan |6500.00|+-------------+---------+24rowsinset(0.00 sec)
3.2.5 属于/不属于
-- 查询员工表中, 部门编号为10, 20, 30的员工信息(姓名, 部门id):
mysql>SELECT first_name, department_id FROM employees WHERE department_id IN(10,20,30);+------------+---------------+| first_name | department_id |+------------+---------------+| Jennifer |10|| Michael |20|| Pat |20|| Den |30|| Alexander |30|| Shelli |30|| Sigal |30|| Guy |30|| Karen |30|+------------+---------------+9rowsinset(0.00 sec)-- 或:
mysql>SELECT first_name, department_id FROM employees WHERE
department_id =10OR department_id =20OR department_id =30;+------------+---------------+| first_name | department_id |+------------+---------------+| Jennifer |10|| Michael |20|| Pat |20|| Den |30|| Alexander |30|| Shelli |30|| Sigal |30|| Guy |30|| Karen |30|+------------+---------------+9rowsinset(0.01 sec)
-- 查询员工表中部门编号不是10, 20, 30的员工信息(姓名, 部门id):
mysql>SELECT first_name, department_id FROM employees WHERE department_id NOTIN(10,20,30);+-------------+---------------+| first_name | department_id |+-------------+---------------+| Steven |90|| Neena |90||...|..|-- 省略| Susan |40|| Hermann |70|| Shelley |110|| William |110|+-------------+---------------+97rowsinset(0.00 sec)
-- 查询员工表中, 名字包含字符'a'的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name LIKE'%a%';+-------------+| first_name |+-------------+| Neena || Alexander || David || Valli ||...|-- 省略| Hermann || William |+-------------+70rowsinset(0.00 sec)
-- 查询员工表中, 名字以'a'开头的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name LIKE'a%';+------------+| first_name |+------------+| Alexander || Alexander || Adam || Alberto || Allan || Amit || Alyssa || Alexis || Anthony || Alana |+------------+10rowsinset(0.00 sec)
-- 查询员工表中, 名字含有字符'a'与字符'e'的员工名字:-- 两种情况: 1. a在e前面; 2. e在a前面.
mysql>SELECT first_name FROM employees WHERE first_name LIKE'%a%e%'OR first_name LIKE'%e%a%';+-------------+| first_name |+-------------+| Neena || Alexander || Daniel ||...|-- 省略| Michael || Hermann |+-------------+29rowsinset(0.00 sec)-- 或(使用AND让匹配的数据同时满足两个条件):
mysql>SELECT first_name FROM employees WHERE first_name LIKE'%a%'AND first_name LIKE'%e%';+-------------+| first_name |+-------------+| Neena || Alexander || Daniel ||...|-- 省略| Michael || Hermann |+-------------+29rowsinset(0.00 sec)
-- 查询员工表中, 名字的第3个字符是'a'的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name LIKE'__a%';+------------+| first_name |+------------+| Diana || Adam || Shanta || Clara || Charles || Jean || Alana |+------------+7rowsinset(0.00 sec)
-- 查询职位历史表中. (job_history)职位id的第3个字符是_且第4个字符是'a'的职位id:
mysql>SELECT job_id FROM job_history WHERE job_id LIKE'__\_a%';+------------+| job_id |+------------+| AC_ACCOUNT || AC_ACCOUNT || AD_ASST |+------------+3rowsinset(0.00 sec)
-- 使用关键字ESCAPE自定义取消转义符号:
mysql>SELECT job_id FROM job_history WHERE job_id LIKE'__$_a%'ESCAPE'$';+------------+| job_id |+------------+| AC_ACCOUNT || AC_ACCOUNT || AD_ASST |+------------+3rowsinset(0.00 sec)
mysql>SELECT first_name FROM employees WHERE first_name REGEXP'^b';+------------+| first_name |+------------+| Bruce || Britney |+------------+2rowsinset(0.02 sec)-- 或:
mysql>SELECT first_name FROM employees WHERE first_name RLIKE'^b';+------------+| first_name |+------------+| Bruce || Britney |+------------+2rowsinset(0.00 sec)
mysql>SELECT first_name FROM employees WHERE first_name REGEXP't$';+------------+| first_name |+------------+| Amit || Pat |+------------+2rowsinset(0.00 sec)-- 或:
mysql>SELECT first_name FROM employees WHERE first_name RLIKE't$';+------------+| first_name |+------------+| Amit || Pat |+------------+2rowsinset(0.00 sec)
mysql>SELECT first_name FROM employees WHERE first_name RLIKE'^b.*e+.*';+------------+| first_name |+------------+| Bruce || Britney |+------------+2rowsinset(0.00 sec)-- 或:
mysql>SELECT first_name FROM employees WHERE first_name RLIKE'^b.*e.*';+------------+| first_name |+------------+| Bruce || Britney |+------------+2rowsinset(0.00 sec)
mysql>SELECT first_name FROM employees WHERE first_name REGEXP'[ai]';+-------------+| first_name |+-------------+| Neena || Alexander || David ||...|-- 省略| William |+-------------+88rowsinset(0.00 sec)
| A | B | AND (A && B) | OR (A || B) | NOT (!A) | XOR (A ^ B) | | ------- | ------- | -------------- | ------------- | ---------- | ------------- | | true | true | true | true | false | false | | true | false | false | true | false | true | | false | true | false | true | true | true | | false | false | false | false | true | false |
-- 查询员工表中, 部门id为50, 并且工资高于6000的员工信息(名字, 工资, 部门id):
mysql>SELECT first_name, salary, department_id FROM employees WHERE department_id =50AND salary >6000;+------------+---------+---------------+| first_name | salary | department_id |+------------+---------+---------------+| Matthew |8000.00|50|| Adam |8200.00|50|| Payam |7900.00|50|| Shanta |6500.00|50|+------------+---------+---------------+4rowsinset(0.00 sec)
-- 查询员工表中, 部门id为10或20的员工信息(名字, 部门id):
mysql>SELECT first_name, department_id FROM employees WHERE department_id =10OR department_id =20;+------------+---------------+| first_name | department_id |+------------+---------------+| Jennifer |10|| Michael |20|| Pat |20|+------------+---------------+3rowsinset(0.03 sec)
-- 查询员工表中, 工资不在6000 - 8000 之间的员工信息(名字, 工资):
mysql>SELECT first_name, salary FROM employees WHERE salary NOTBETWEEN6000AND8000;+------------+----------+| first_name | salary |+------------+----------+| Steven |24000.00|| Neena |17000.00|| Lex |17000.00||...|...|-- 省略| Jennifer |4400.00|| Michael |13000.00|| Hermann |10000.00|| Shelley |12000.00|| William |8300.00|+------------+----------+83rowsinset(0.00 sec)-- 查询员工表中, 佣金提成不为NULL的员工信息(名字, 佣金提成):-- SELECT first_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
mysql>SELECT first_name, commission_pct FROM employees WHERENOT commission_pct <=>NULL;+-------------+----------------+| first_name | commission_pct |+-------------+----------------+| John |0.40|| Karen |0.30|| Alberto |0.30|| Gerald |0.30||...|...|-- 省略| Alyssa |0.25|| Jonathon |0.20|| Jack |0.20|| Kimberely |0.15|| Charles |0.10|+-------------+----------------+35rowsinset(0.00 sec)
-- 异或两种情况:-- * 1. 部门id为50, 则工资小于6000.-- * 2. 部门id不为50, 则工资大于6000.
mysql>SELECT first_name, department_id, salary FROM employees WHERE department_id =50XOR salary >6000;+-------------+---------------+----------+| first_name | department_id | salary |+-------------+---------------+----------+| Steven |90|24000.00||...|..|...|-- 省略| Alana |50|3100.00|| Kevin |50|3000.00|| Donald |50|2600.00|| Douglas |50|2600.00|| Michael |20|13000.00|| Susan |40|6500.00|| Hermann |70|10000.00|| Shelley |110|12000.00|| William |110|8300.00|+-------------+---------------+----------+
-- 1. 匹配员工表中, 名字以a开头的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name REGEXP'^a';+------------+| first_name |+------------+| Alexander || Alexander ||...|-- 省略| Alexis || Anthony || Alana |+------------+10rowsinset(0.02 sec)
-- 2. 匹配员工表中, 名字以r结尾的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name REGEXP'r$';+-------------+| first_name |+-------------+| Alexander || Alexander ||...|-- 省略| Tayler || Jennifer || Jennifer |+-------------+11rowsinset(0.00 sec)
-- 3. 匹配员工表中, 名字以a或b或c开头的员工名字:
mysql>SELECT first_name FROM employees WHERE first_name REGEXP'^[a-c]';+-------------+| first_name |+-------------+| Alexander || Bruce || Alexander || Adam ||...|-- 省略| Alexis || Anthony || Britney || Alana |+-------------+16rowsinset(0.00 sec)