一、基础查询语句
1.查询所有列
语法:select * from 表名;或select 所有列名 from 表名;
SELECT * FROM t_mysql_employees;
2.查询特定列
语法:select 列名 from 表名;
SELECT first_name,last_name FROM t_mysql_employees;
3.别名
语法一:select 列名 空格 别名 from 表名;
select first_name 姓,last_name 名 from t_mysql_employees;
语法二:select 列名 as 别名 from 表名;
select first_name as 姓,last_name as 名 from t_mysql_employees;
4.去重
语法:select distinct 列名 from 表名;
SELECT DISTINCT department_id FROM t_mysql_employees;
5.+号的作用
java中的+号:
运算符,两个操作数都为数值型。
连接符,只要有一个操作数为字符串。
MySQL中的+号:运算符
SELECT 10+50;#结果为190
SELECT '10'+90;#结果为100
SELECT 'john'+90;#结果为90,字符串换算为0
SELECT 100+NULL;#只要一方为Null,则结果为Null
拼接字符串:
语法:select concat(‘字符串A’,‘字符串B’) from 表名;#结果为AB
SELECT
CONCAT(last_name,first_name) 姓名
FROM
t_mysql_employees;
二、过滤及排序
语法:select 列名 from 表名 where 筛选条件;
1.运算符筛选
简单条件运算符:> < = != <> >= <=
#查询工资>10000的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
salary > 10000;
#查询部门编号不等于90号的员工名和部门编号
SELECT
department_id,
CONCAT( first_name, last_name ) 姓名
FROM
t_mysql_employees
WHERE
department_id <> 90;
2.逻辑筛选
逻辑运算符:
&&和and:两个条件都为true,结果为true,反之为false
||或or: 只要有一个条件为true,结果为true,反之为false
!或not: 如果连接的条件本身为false,结果为true,反之为false
#查询工资在10000到20000之间的员工名、工资以及奖金
SELECT
CONCAT( first_name, last_name ) 姓名,
salary 工资,
commission_pct 奖金
FROM
t_mysql_employees
WHERE
salary >= 10000
AND salary <= 20000;
#查询部门编号不是在90到110之间,或者工资高于15000的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
( department_id < 90 OR department_id > 110 )
OR
salary > 15000;
3.模糊查询筛选
关键词like:
%,任意多个字符,包括0个字符
_,任意单个字符
#查询员工名中包含字符a的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
CONCAT( first_name, last_name ) LIKE '%a%';
#查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT
CONCAT( first_name, last_name ) 姓名,
salary
FROM
t_mysql_employees
WHERE
CONCAT( first_name, last_name ) LIKE '__e_a%';
关键词between…and…:
区间查询,包含临界值,两个临界值不要调换顺序
#查询员工编号在100到120之间的员工信息
SELECT
*
FROM
t_mysql_employees
WHERE
employee_id BETWEEN 100
AND 120;
关键词in:
判断某字段的值是否属于in列表中的某一项,in列表的值类型必须一致或兼容,不支持通配符
#查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT
CONCAT( first_name, last_name ) 姓名,
job_id
FROM
t_mysql_employees
WHERE
job_id IN ( 'IT_PROT', 'AD_VP', 'AD_PRES' );
关键字is null:
is null或is not null 可以判断是否为null值,=或<>不能用于判断null值
#查询没有奖金的员工名和奖金率
SELECT
CONCAT( first_name, last_name ) 姓名,
commission_pct
FROM
t_mysql_employees
WHERE
commission_pct IS NULL;
安全等于<=>:既可以判断NULL值,又可以判断普通的数值,可读性较低。
4.排序
asc代表的是升序,可以省略
desc代表的是降序
语法:select 查询列表 from 表名【where 筛选条件】order by 排序的字段或表达式;
关键字Order by:
order by子句可以支持 单个字段、别名、表达式、函数、多个字段
order by子句在查询语句的最后面,除了limit子句
#按单个字段排序:查询员工姓名及工资,并按工资降序排序
SELECT
CONCAT( first_name, last_name ) 姓名,
salary
FROM
t_mysql_employees
ORDER BY
salary DESC;
#添加筛选条件再排序:查询部门编号>=90的员工信息,并按员工编号降序
SELECT
*
FROM
t_mysql_employees
WHERE
department_id >= 90
ORDER BY
employee_id DESC;
#按表达式排序:查询员工信息 按年薪降序
SELECT
*,
salary * 12 * ( 1+ IFNULL( commission_pct, 0 ) )
FROM
t_mysql_employees
ORDER BY
salary * 12 * ( 1+ IFNULL( commission_pct, 0 ) ) DESC;
#按多个字段排序:要求先按工资降序,再按employee_id升序
SELECT
*
FROM
t_mysql_employees
ORDER BY
salary DESC,
employee_id ASC;
三、分组查询
概念:分组函数作用于一组数据,并对一组数据返回一个值。
1.分组函数
分类:sum求和、avg平均值、max最大值 、min最小值 、count计算个数
特点:
- sum、avg一般用于处理数值型
- max、min、count可以处理任何类型
- 可以和distinct搭配实现去重的运算
- 一般使用count(*)用作统计行数
- 和分组函数一同查询的字段要求是group by后的字段
count(1)和count(字段)的主要区别:
count(1) 会统计表中的所有的记录数,包含字段为null 的记录。
count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
#求工资总数
SELECT SUM(salary) FROM t_mysql_employees;
#求工资平均值
SELECT AVG(salary) FROM t_mysql_employees;
#求工资最低值
SELECT MIN(salary) FROM t_mysql_employees;
#求工资最高值
SELECT MAX(salary) FROM t_mysql_employees;
#有工资的个数
SELECT COUNT(salary) FROM t_mysql_employees;
#查询工资去重后的总数和工资总数
SELECT
SUM( DISTINCT salary ),
SUM( salary )
FROM
t_mysql_employees;
#工资去重后的个数和工资总个数
SELECT
COUNT( DISTINCT salary ),
COUNT( salary )
FROM
t_mysql_employees;
2.group by
语法:select 列名 from 表【where 筛选条件】group by 分组的字段【order by 排序的字段】;
#查询每个工种的员工平均工资
SELECT
AVG( salary ),
job_id
FROM
t_mysql_employees
GROUP BY
job_id;
特点:
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类:分组前筛选和分组后筛选。分组前筛选在group by前用where关键字,分组后筛选在group by后用having关键字。
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用
#分组前筛选:查询邮箱中包含e字符的 每个部门的最高工资
SELECT
department_id 部门,
MAX( salary ) 最高工资
FROM
t_mysql_employees
WHERE
email LIKE '%e%'
GROUP BY
department_id;
#分组后筛选:查询员工个数>5的每个部门和员工个数
SELECT
COUNT( * ),
department_id
FROM
t_mysql_employees
GROUP BY
department_id
HAVING
COUNT( * ) > 5;
#分组前筛选+分组后筛选:领导编号>102 的 每个领导 手下的 最低工资大于5000 的领导编号和最低工资
SELECT
manager_id 领导编号,
MIN( salary ) 最低工资
FROM
t_mysql_employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
MIN( salary ) > 5000;
分组排序:
#每个工种 有奖金 的员工的 最高工资>6000 的 工种编号和最高工资,按最高工资升序
SELECT
job_id 工种,
MAX( salary ) 最高工资
FROM
t_mysql_employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX( salary ) > 6000
ORDER BY
MAX( salary );
按多个字段分组:
#查询 每个工种 每个部门 的 最低工资,并按 最低工资降序
SELECT
MIN( salary ),
job_id,
department_id
FROM
t_mysql_employees
GROUP BY
department_id,
job_id
ORDER BY
MIN( salary ) DESC;