条件查询基础
语法
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>=10000 AND salary<=20000;
案例二:查询部门编号不是在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;
模糊查询
LIKE一般和通配符搭配使用
通配符:% 百分号:任意多个字符,包含0个字符
_ 下划线:任意单个字符
案例一:查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
案例二:查询员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%';
案例三:查询员工名中第二个字符为下划线(_)的员工名,第二种方法为手动指定转义字符
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
BETWEEN AND使用BETWEEN AND可以提高语句的简洁度
包含临界值
两个值不能颠倒顺序
案例:查询员工编号在100到120之间的员工信息,第一种做法较繁琐,第二种用BETWEEN AND
SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=120;
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
IN使用IN提高语句简洁度
IN列表的值类型必须一致或兼容
不支持下划线或通配符
案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT last_name, job_id FROM employees WHERE job_id='IT_PROG' OR job_id='AD_VP' OR job_id='AD_PRES';
SELECT last_name, job_id FROM employees WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');
IS NULL=或<>不能用于判断null值
is null或is not null可以判断null值
案例:查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL;
变式:查询有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
以下为错误:
SELECT * FROM employees WHERE salary IS 12000;
安全等与 <=>优点:既可以判断NULL值又可以判断普通数值
缺点:可读性较低
案例1:查询没有奖金的员工名和奖金率
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;
案例2:查询工资为12000的员工信息
SELECT last_name, commission_pct FROM employees WHERE salary <=> 12000;