分类:
1.按条件表达式筛选: 条件运算符< ,> , = ,!=,<> ,>= ,<=
2.按逻辑表达式筛选: 逻辑运算符 && , || , | and or not;作用:用于连接条件表达式;
3.模糊查询:
like : 一般和通配符搭配使用 ,%任意多个字符 , _任意单个字符;
between and:包含临界值,前后数据顺序不可颠倒;
in:代替多个OR太繁琐的情况,列表中类型必须一致或可以兼容,
is null;=不能判断NULL
is not null:不为空
if NUll(字段,0):如果字段为null,返回0;
4字符拼接 CONCAT(’a‘,’b‘);
5.安全<=>**
/*条件查询where*/
/*
分类:
1.按条件表达式筛选: 条件运算符< ,> , = ,!=,<> ,>= ,<=
2.按逻辑表达式筛选: 逻辑运算符 && , || , | and or not;作用:用于连接条件表达式;
3.模糊查询:
like : 一般和通配符搭配使用 ,%任意多个字符 , _任意单个字符;
between and:包含临界值,前后数据顺序不可颠倒;
in:代替多个OR太繁琐的情况,列表中类型必须一致或可以兼容,
is null;=不能判断NULL
is not null:不为空
if NUll(字段,0):如果字段为null,返回0;
4.字符拼接 CONCAT(’a‘,’b‘);
5.安全<=>
*/
#1.按条件表达式筛选
#案例一:查询工资大于12000的员工信息;
SELECT
*
FROM
employees
WHERE salary > 12000 ;
#案例二:查询部门编号不等于90号的员工名和部门编号;
SELECT
last_name,
department_id
FROM
employees
WHERE department_id != 90;
#2.按逻辑表达式筛选
#案例一:查询工资在10000到20000之间的员工名,工资以及奖金;
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary > 10000
AND salary < 20000 ;
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary BETWEEN 10000 AND 20000;
#案例二: 查询部门编号不是在90到110之间,或者工资高于15000的员工信息;
SELECT *
FROM employees
WHERE NOT(department_id>90 AND department_id <110) OR salary >15000;
#3.模糊查询
# (1)like
#案例一:查询员工名中包含字符a的员工信息;
SELECT
*
FROM
employees
WHERE last_name LIKE "%a%" ;
#案例二:查询员工名中第三个字符为n,第五个字符为l;
SELECT
last_name
FROM
employees
WHERE last_name LIKE "__n_l%" ;
#案例三:查询员工名中第二个字符为_的员工;(转义字符 \;或者escape指定任意字符为转义字符)
SELECT last_name
FROM employees
WHERE last_name LIKE "_\_%";
SELECT last_name
FROM employees
WHERE last_name LIKE "_$_%" ESCAPE '$';
#(2)between and
#案例一:查询员工编号在100 到 120 之间的员工信息
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
#(3)in
#案例一:查询员工的工种编号为 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号;
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');
#(4)is 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
last_name,
commission_pct
FROM
employees
WHERE commission_pct <=> NULL ;
#案例三:查询工资为12000的员工信息;
SELECT
last_name
FROM
employees
WHERE salary <=> 12000 ;
#测试题
#(1)job_id 不为‘IT‘ 或者工资为 12000 的员工信息;
SELECT
*
FROM
employees
WHERE job_id <> 'IT'
OR salary = 12000 ;
#(2) 查看表的结构
DESC departments;
#(3)查询部departments表中涉及到了那些位置编号;
SELECT DISTINCT location_id FROM departments;
#(5)面试题:select * from employees ;和 select * from employees where commission_pct like '%%'
#and last _name like '%%' 结果是否一样?
#答 不一样, 如果判断的字段有null值,改为OR就一样了,至少有一个不为空。
SELECT * FROM employees ;
SELECT
*
FROM
employees
WHERE commission_pct LIKE '%%' AND last_name LIKE '%%';