SQL:数据库Day3
条件查询
- 语法:
- 分类:
(1) 按条件表达式筛选
条件运算符:> < = != (或<>,表示不等于) >= <=
(2) 按逻辑表达式筛选
逻辑运算符:
&&(and) ||(or) !(not)
(3) 模糊查询:
like
between and
in
is null
按条件表达式筛选
#案例1:查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000;
#案例2:查询部门编号不等于90号的员工名和部门编号
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<>90;
按逻辑表达式筛选
#案例1:查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT
last_name,
salary,
commisson_pct
FROM
employees
WHERE
salary>=10000 AND salary<=20000;
#案例2:查询部门编号不是在90到110之间的,或者工资高于15000的员工信息
SELECT
*
FROM
employees
WHERE
NOT(department_id>90 AND department_id<=110) OR salaty>15000;
模糊查询
- like:
特点:
1) 一般和通配符(%:任意多个字符 _ 任意单个字符)搭配使用
2)
#案例1:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
#案例2:查询员工名中第三个字符为n,第五个字符为l的员工名和工资
SELECT
*
FROM
employees
WHERE
last_name LIKE '—__n_l%';
#案例3:查询员工名中第二个字符文为_的员工名
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%';#转义字符
#last_name LIKE '_$_%' ESCAPE '$';(推荐ESCAPE)
- between and
特点:
1)只用between and
2)包含区间端点值
3)可以颠倒顺序
#案例1:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id >= 100 AND employee_id <= 120;
#---------------------------------------
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 120;#(推荐)
- in
特点:
1) 使用in提高简洁度
2) in列表的值类型必须一致或兼容
#案例1:查询员工的工种编号是 IT_PROG、AD_VP,AD_PRES
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN ('IT_PROG','AD_VP','AD_PRES');
- is null | is not null
特点:
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name.
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
- 安全等于<=>
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name.
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例2:查询工资为12000的员工名
SELECT
last_name.
salary
FROM
employees
WHERE
salary <=> 12000;
IS NULL:仅仅可以判断NULL值,可读性较高
<=>:既可以判断NULL值,也可以判断普通的数值,可读性较低