文章目录
基础查询
格式
select 查询列表 from 表名;
查询列表可以是表中的字段、常量、常量值、表达式、函数;
查询的结果是一个虚拟的表格;
启用myemployees库
USE myemployees;
查询表中的单个字段
SELECT last_name FROM employees;
查询表中的多个字段
SELECT last_name,salary,email FROM employees;
查询表中所有的字段
SELECT * FROM employees;
着重号
如果字段名与关键字名相同,用着重号标识。
SELECT `last_name` FROM employees;
查询常量值
SELECT 100;
SELECT 'join';
查询表达式
SELECT 10*10;
查询函数
SELECT VERSION();
起别名
如果要查询的字段有重名的情况,使用别名可以区分
SELECT 10%8 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
SELECT last_name 姓,first_name 名 FROM employees;
别名中包含关键字或者特殊符号用双引号包起来
SELECT salary AS "out put" FROM employees;
去重
案例:查询员工表中涉及到的所有部门编号
SELECT `department_id` FROM `employees`;
SELECT DISTINCT `department_id` FROM `employees`;
+的作用
两个操作数都为数值型,则做加法运算
SELECT 100+90;
其中一个为字符型,试图将字符型数值转换为数值型,如果转换成功,则继续做加法运算
SELECT '100'+90;
如果转换失败,则将字符型数值转换成0
SELECT 'join'+90;
如果其中一方为null,则结果为null
SELECT NULL+90;
concat实现连接
案例:查询员工姓和名连接成一个字段,并显示为姓名
SELECT CONCAT(last_name,' ',first_name) AS 姓名 FROM `employees`;
IFNULL
SELECT
`commission_pct`,
IFNULL(`commission_pct`,0) AS 奖金率
FROM
`employees`;
案例:显示出表employees的全部列,各个列之间用逗号连接,列头显示成out_put
SELECT
CONCAT(`employee_id`,',',`first_name`,',',`last_name`,',',IFNULL(`commission_pct`,0)) AS out_put
FROM
`employees`;
ISNULL
SELECT ISNULL(commission_pct),commission_pct FROM employees;
判断某字段或表达式是否为null,如果为null,返回1,否则返回0
条件查询
格式
SELECT 查询列表
FROM 表名
WHERE 筛选条件
分类
1.按条件表达式筛选
条件运算符:> < = != <> >= <=
2.按逻辑表达式筛选
逻辑运算符:&&(AND) ||(OR) !(NOT)
3.模糊查询
like、between、in、is NULL
按条件表达式筛选
案例1:查询工资大于12000的员工信息
SELECT *
FROM employees
WHERE salary>12000;
案例2:查询部门编号不等于90的员工名和部门编号
SELECT last_name,department_id
FROM employees
WHERE department_id<>90;
按逻辑表达式筛选
案例3:查询工资在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;
案例4:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=100) OR salary>15000;
模糊查询
LIKE
LIKE
一般和通配符搭配使用
通配符 %:任意多个字符,包含0个字符
_:任意单个字符
案例1:查询员工中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
last_name,
salary
FROM
employees
WHERE
last_name LIKE '__e_a%';
案例3:查询员工名中第二个字符为_的员工名
SELECT
*
FROM
employees
WHERE
last_name LIKE '_\_%';
ESCAPE
SELECT
*
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';
#ESCAPE '$':$之后的_不作为通配符
BETWEEN AND
包含临界值
案例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
判断某字段的值是否属于in列表中的某一项
列表的值类型必须一致或兼容
案例:查询员工的工种编号是IT_PROG、AD_PRES、AD_VP中的一个员工名和工种编号
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id = 'IT_PROG' OR job_id = 'AD_PRES' OR job_id = 'AD_VP';
#------------------------------------------------
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROG','AD_PRES','AD_VP');
IS 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
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
案例:查询工资为12000的员工信息
SELECT
last_name,
commission_pct
FROM
employees
WHERE
salary <=> 12000;
排序查询
SELECT 查询列表
FROM 表
[WHERE 筛选条件]
ORDER BY 排序列表 [ASC|DESC]
案例:查询员工信息,要求工资由高到低排序
SELECT * FROM employees ORDER BY salary DESC;
案例:查询部门编号>=90的员工信息,按入职时间的先后进行排序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY hiredate ASC;
案例:按年薪的高低显示员工的信息和年薪(按表达式排序)
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
案例:按年薪的高低显示员工的信息和年薪(按别名排序)
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
案例:按姓名的长度显示员工的姓名和工资(按函数排序)
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
案例:查询员工信息,要求先按工资排序(降序),再按员工编号排序(升序)(按多个字段排序)
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
分组查询
SELECT 分组函数,列(要求出现在group by的后面)
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的列表
[ORDER BY 子句]
注意:查询列表比较特殊,要求是分组函数和group BY 后出现的字段
特点:分组查询中的筛选条件分为两类
类别 | 数据源 | 位置 | 关键字 |
---|---|---|---|
分组前筛选 | 原始表 | GROUP BY 子句的前面 | WHERE |
分组后筛选 | 分组后的结果集 | GROUP BY 子句的后面 | HAVING |
1.分组函数做条件肯定是放在having子句中
2.能用分组前筛选的,就优先考虑使用分组前筛选
3.支持按单个或多个字段分组
4.也可以添加排序
案例:查询每个工种的最高工资
SELECT MAX(salary) AS 最高工资,job_id AS 工种
FROM employees
GROUP BY job_id;
案例:查询每个位置上的部门个数
SELECT COUNT(*) 部门个数,location_id AS 位置
FROM departments
GROUP BY location_id;
添加筛选条件
分组前筛选
案例:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
案例:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
案例:查询哪个部门的员工个数>2
1.查询每个员工的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
分组后筛选
2.根据1的结果进行筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
案例:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1.查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
2.根据1的结果继续筛选,最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) >12000;
案例:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及最低工资
1.查询每个领导手下员工的最低工资
SELECT MIN(salary),manager_id
FROM employees
GROUP BY manager_id;
2.添加筛选条件,编号>102
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
3.添加筛选条件,最低工资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
按表达式或函数分组
案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
1.查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name);
2.添加筛选条件
SELECT COUNT(*),LENGTH(last_name) AS len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >5;
按多个字段分组
案例:查询每个部门每个工种的员工的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
添加排序
案例:查询每个部门每个工种的员工的平均工资,并按照平均工资的高低显示
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;
部门编号不为空且平均工资>1000
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;