#条件查询 /*
语法:
select 查询列表 from 表名 where 筛选条件;
分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:&& || ! and or not
三、模糊查询 :like between and in is null
*/#查询工资>12000的员工信息SELECT*FROM employees WHERE salary>12000;/*
like
特点:
①一般和通配符搭配使用
通配符:
% 任意多个字符,包括0个字符
_任意单个字符
字符型和数字都可以
*/#查询名字中包含a的名字SELECT*FROM employees WHERE last_name LIKE'%a%';SELECT last_name,salary FROM employees WHERE last_name LIKE'__n_l%';#查询员工名中第二个字符为_的员工名(转义字符)SELECT last_name,salary FROM employees WHERE last_name LIKE'_\_%';SELECT last_name,salary FROM employees WHERE last_name LIKE'_$_%'ESCAPE'$';/*between
①包含两个临界值
②两个临界值不能颠倒
*/SELECT*FROM employees WHERE employee_id BETWEEN100AND120;/*
IN
不能使用通配符
*/SELECT last_name , job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');/*
IS
IS只能用在IS NULL 的情况下
*/SELECT last_name , commission_pct FROM employees WHERE commission_pct ISNULL;/*
IS NULL :仅仅可以判断null值,可读性较高,判断该字段是否为null,0或者1
<=> :既可以判断null值,又可以判断普通的数值,可读性较低
*/
#条件查询 /*
语法:
select 查询列表 from 表名 where 筛选条件;
分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:&& || ! and or not
三、模糊查询 :like between and in is null
*/#查询工资>12000的员工信息SELECT*FROM employees WHERE salary>12000;/*
like
特点:
①一般和通配符搭配使用
通配符:
% 任意多个字符,包括0个字符
_任意单个字符
字符型和数字都可以
*/#查询名字中包含a的名字SELECT*FROM employees WHERE last_name LIKE'%a%';SELECT last_name,salary FROM employees WHERE last_name LIKE'__n_l%';#查询员工名中第二个字符为_的员工名(转义字符)SELECT last_name,salary FROM employees WHERE last_name LIKE'_\_%';SELECT last_name,salary FROM employees WHERE last_name LIKE'_$_%'ESCAPE'$';/*between
①包含两个临界值
②两个临界值不能颠倒
*/SELECT*FROM employees WHERE employee_id BETWEEN100AND120;/*
IN
不能使用通配符
*/SELECT last_name , job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');/*
IS
IS只能用在IS NULL 的情况下
*/SELECT last_name , commission_pct FROM employees WHERE commission_pct ISNULL;/*
IS NULL :仅仅可以判断null值,可读性较高,判断该字段是否为null,0或者1
<=> :既可以判断null值,又可以判断普通的数值,可读性较低
*/
/*
查询排序语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表【asc|desc】
默认升序
一般放在查询语句的最后面
*/SELECT*FROM employees ORDERBY salary DESC;#按入职时间先后进行排序SELECT*FROM employees
WHERE department_id>=90ORDERBY hiredate ASC;#按年新高低显示员工信息SELECT*,salary*12*(1+IFNULL(commission_pct,0))AS 年薪
FROM employees
ORDERBY salary*12*(1+IFNULL(commission_pct,0))DESC;#按姓名长度显示员工姓名和工资SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDERBY LENGTH(last_name)DESC;#先按工资升序,再按员工编号排序SELECT*FROM employees
ORDERBY salary ASC,employee_id DESC;