MySQL基础查询操作
#案例:查询所有部门编号,不包含重复的
SELECT
DISTINCT department_id
FROM
employees;
#案例:合并&重命名
SELECT
CONCAT('a','b','c') AS 结果;
SELECT
CONCAT(last_name,' ', first_name) AS 姓名
FROM
employees;
#显示表格所有信息:
DESC employees ;
#无论什么类型加上null类型数据后,都会变成null,所以可以使用IFNULL将奖金率为NULL变为0
SELECT
IFNULL(commission_pct,0)
FROM
employees;
SELECT
commission_pct AS 原始奖金率,
IFNULL(commission_pct, 0) AS 奖金率
FROM
employees ;
#判断查询
SELECT
*
FROM
employees
WHERE
salary>=1200;
#不等于符号 <>或者 !=
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id<>90;
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000 AND salary <= 20000;
SELECT
*
FROM
employees
WHERE
#department_id < 90 || department_id > 110 || salary > 15000;
!(department_id >= 90 AND department_id <= 110) || salary > 10000;
#模糊查询:
/*
like
特点:
①一般和通配符搭配使用
通配符:% 任意多个字符,包含多个字符;
_ 任意单个字符;
between and
in
is null | is not null
*/
#1.like
#案例1:查询员工名中包含字符a的员工信息
USE myemployees;
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%'; #字符型的值必须用单引号,%代表的是通配符;
#案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__c_h%';
#案例3:查询员工中第二个字符为_的员工名
SELECT
*
FROM
employees
WHERE
#last_name like '_\_%'; #和java类似,\默认充当转义字符
last_name LIKE '_$_%' ESCAPE '$'; #也可以指定转义字符,效果和上面一样
#2.between and
/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换位置
*/
#案例1:查询员工编号在100到120之间的员工信息
SELECT
*
FROM
employees
WHERE
#employee_id >= 100 && employee_id <= 120;
employee_id BETWEEN 100 AND 120;
#3.in
/*
含义:判断某字段的值是否属于in列表中的某一项
特点:
①使用in提高语句简洁度
②in列表的值类型必须统一或者兼容:
③job_id IN (‘AD_VP’, ‘IT_%’) 这种写法是不支持的,in可以认为是一种等于判断,通配符是like型判断;
*/
#案例:查询员工的工种编号是 AD_VP、IT_PROG、AD_PERS中的一个员工名和工种编号
SELECT
last_name, job_id
FROM
employees
WHERE
#job_id = 'AD_VP' || job_id = 'IT_PROG' || job_id = 'AD_PERS';
job_id IN ('AD_VP', 'IT_PROG','AD_PERS');
#4.is null
/*
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
#案例2:查询有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
#5.安全等于 <=>
/*
<=> :既可以判断null值,又可以判断普通的等于
*/
#案例1:查询没有奖金的员工名和奖金率
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
#案例2:查询工资为12000的员工信息
SELECT
*
FROM
employees
WHERE
salary <=> 12000;
#进阶3:排序查询
/*
语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点:
1.asc代表的是升序,desc代表的是降序,如果不写,默认是升序;
*/
#案例1:查询员工信息,要求工资从高到低排列
SELECT
*
FROM
employees
ORDER BY
salary DESC;
#案例2:查询部门编号>=90的员工信息,按入职时间先后进行排序【添加筛选条件】
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
#案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT
*, salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
salary*12*(1+IFNULL(commission_pct, 0)) DESC;
#案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT
*, salary*12*(1+IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY
年薪 DESC;
#案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT
CONCAT(last_name, first_name) AS 姓名, salary
FROM
employees
ORDER BY
LENGTH(姓名) DESC;
#案例6:查询员工信息,要求先按工资升序,再按员工编号降序【按多个要求排序】
SELECT
*
FROM
employees
ORDER BY
salary ASC, employee_id DESC;