#like 模糊查询 查询 相似值 #%% 代表不确定个数的字符 代表前后有若干个值可能是0 ，1，或者其他 #查询name中包含 a的值 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE '%a%'; #查询name中 a 开头的值 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE 'a%'; #查询name中 a 结尾的值 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE '%a'; #查询包含a e的值 #写法1 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'; #写法2 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%'; #查询第二个字符是a的值 #_ 代表不确定的字符 SELECT department_id,last_name,salary FROM employees WHERE last_name LIKE '_a%'
#REGEXP RLIKE 正则表达式 #查询 是不是以为y开头，是不是以为O结尾，是不是包含 wen SELECT 'yanwenchao' REGEXP '^y','yanwenchao' REGEXP 'o$', 'yanwenchao' REGEXP 'wen' FROM DUAL; #... 3个点代表中间是3个任意值 前后对应即可 #【af】 其中有任意一个字母就算通过 或者都有 SELECT 'yanwenchao' REGEXP 'yan...ch','yanwenchao' REGEXP '[af]' FROM DUAL;
1是true 2是false
4. 空运算符
空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。 SQL语句示例如下：
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL; +--------------+--------------+-------------+-----------+ | NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL | +--------------+--------------+-------------+-----------+ | 1 | 1 | 0 | 0 | +--------------+--------------+-------------+-----------+ 1 row in set (0.00 sec)
#查询commission_pct等于NULL。比较如下的四种写法 SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct); SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
#查询commission_pct 为空 SELECT last_name,salary ,commission_pct FROM employees #WHERE salary = NULL; #只要null参与判断不会有任何结果 WHERE commission_pct IS NULL; #或者 作为关键字 SELECT last_name,salary ,commission_pct FROM employees #WHERE salary = NULL; #只要null参与判断不会有任何结果 WHERE ISNULL(commission_pct);
5. 非空运算符
非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。 SQL语句示例如下：
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL; +------------------+-----------------+---------------+ | NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL | +------------------+-----------------+---------------+ | 0 | 1 | 1 | +------------------+-----------------+---------------+ 1 row in set (0.01 sec)
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
#查询commission_pct 不 为空 SELECT last_name,salary ,commission_pct FROM employees #WHERE salary = NULL; #只要null参与判断不会有任何结果 WHERE commission_pct IS NOT NULL; #或者 作为关键字 SELECT last_name,salary ,commission_pct FROM employees #WHERE salary = NULL; #只要null参与判断不会有任何结果 WHERE NOT commission_pct <=>NULL;
