打怪升级之小白的大数据之旅(三十三)
Mysql的查询
上次回顾
上一章对数据库以及Mysql的基础操作进行了介绍,本章节主要是对Mysql的查询操作进行介绍,然后Mysql的相关操作就结束啦,因为我们大数据就用这么多,至于Mysql的视图啦,交叉表啦这些内容,我们用不到,好了,开始今天的正题:
基本查询
- 语法格式:
select 字段名1,字段名2 ....... from 表名 select * #*表示所有的字段 from 表名
- 示例代码:
SELECT employee_id,first_name,salary FROM employees; #*表示所有的字段 SELECT * FROM employees;
- select查询的两个小知识点:
# 第一个,select加法 SELECT 1+1; # 前面的语法格式不是说了,必须from表名么?因为它的全写是: SELECT 1+1 FROM DUAL; # 第二个,与Null作运算,任何数据和null做运算结果为null SELECT 1+NULL,'aab'+NULL;
起别名
向字段起别名
- 语法格式
# 给字段起别名 select 字段名 as 别名 from 表名; # 简写方式 select 字段名 别名 from 表名;
- 示例代码
# 使用as起别名 SELECT salary,salary*3 AS new_salary,first_name fn FROM employees # 省略as SELECT salary,salary*3 new_salary,first_name fn FROM employees
向表起别名
-
语法格式
# 给表起别名 select * from 表名 as 别名;
-
示例代码
SELECT salary FROM employees as e
-
注意: 如果别名中有空格,那么别名必须使用双引号括起来(单引号也可以)
SELECT salary*3 AS "n salary" FROM employees;
消除重复行
- 语法格式
select distinct 字段名1,字段名2,… from 表名
- 示例代码
SELECT DISTINCT salary FROM employees;
条件查询
使用where子句对表中的数据筛选,结果为true的行会出现在结果集中,where通常配合运算符使用
- 语法格式
select * from 表名 where 条件;
- 示例代码
SELECT DISTINCT salary FROM employees WHERE salary>10000
运算符
这个老生常谈了,java中也学过,作用都是一样的,就不在详细介绍了
比较运算符
运算符 | 解释 |
---|---|
> | 大于 |
< | 小于 |
= | 等于 |
<> | 不等于 |
>= | 大于等于 |
<= | 小于等于 |
- 注:不等于在SQL中使用<>,虽然!=也可以,但不建议使用
示例代码
#需求:查询所有员工中薪水大于5000的有哪些。
SELECT salary
FROM employees
WHERE salary > 5000;
#需求:查询不在部门为100号的员工有哪些
SELECT first_name,department_id
FROM employees
#where department_id != 100;
WHERE department_id <> 100;
#需求:查询部门为100号的员工有哪些
SELECT first_name,department_id
FROM employees
WHERE department_id = 100;
逻辑运算符
运算符 | 解释 |
---|---|
and | 并且 |
or | 或者 |
not | 不是 |
- 注:逻辑运算符常与范围运算符一起使用
示例代码:
#需求:查询员工部门号为60或90的员工有哪些
SELECT first_name,department_id
FROM employees
#WHERE department_id=60 || department_id=90;
WHERE department_id=60 or department_id=90;
运算符的优先级
- 优先级由高到低的顺序为:小括号,not,比较运算符,逻辑运算符
- and比or先运算,如果同时出现并希望先算or,需要结合()使用
范围查询
查询关键字 | 说明 |
---|---|
in(x,xx,xxx) | 通常表示多个非连续的数据 |
not in(x,xx,xx) | 不再(多个非连续的数据)中 |
between xx and xx | 通常表示一个连续范围中的数据 |
not between xx and xx | 通常表示不在一个连续范围中的数据 |
-
注:范围查询会常用于查询的数据是多条的情况
-
示例代码:
#需求:查询薪水大于等于5000小于等于9000的员工有哪些 SELECT last_name,salary FROM employees #where salary>=5000 and salary<=9000; # WHERE salary>=5000 && salary<=9000 WHERE salary between 5000 and 9000; # [5000,9000] #错误的写法 #where salary between 9000 and 5000;#表示salary>=9000 and salary<=5000 #需求:查询薪水不在[5000,9000]范围的员工有哪些 SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 9000;
模糊查询
当我们需要对某个字段进行模糊匹配时就需要用到关键字LIKE
来进行模糊查询
- 语法格式
# ""中可以可以使用%和_ %代表任意字符任意个数 _代表一个字符 SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ""
- 包含任意个数任意字符的查询
# 查询姓名中包含a的员工有哪些 SELECT first_name FROM employees #%表示任意个数的任意字符 WHERE first_name LIKE '%a%';
- 对一个任意字符的查询
#查询姓名中第二个字符为e的员工有哪些 SELECT first_name FROM employees #_表示一个任意的字符 WHERE first_name LIKE '_e%';
- 同时包含两个字符的查询
#查询姓名中包含a又包含e的员工有哪些 SELECT first_name FROM employees # 两种实现方式 where first_name like '%a%e%' or first_name LIKE '%e%a%'; WHERE first_name LIKE '%a%' AND first_name LIKE '%e%';
- 特殊字符的查询一
#查询姓名中第二个字符为_的员工有哪些 SELECT first_name FROM employees #\_转义字符表示内容为_ WHERE first_name LIKE '_\_%';
- 特殊字符的查询二:指定转义字符
#指定转义字符 SELECT first_name FROM employees WHERE first_name LIKE '_$_%' ESCAPE '$'; #指定$为转义字符
空判断
查询关键字 | 说明 |
---|---|
is null | 判断是null值 |
not is null | 判断不是null值 |
-
注: 在SQL中,null值是一个特殊值,它通常代表数据为空
-
示例代码
#需求:查询奖金率为null的员工有哪些 SELECT commission_pct FROM employees WHERE commission_pct IS NULL; #需求:查询奖金率不为null的员工有哪些 SELECT commission_pct FROM employees WHERE commission_pct IS NOT NULL;
排序
我们可以根据特定的条件来对数据进行排序,默认的排序是升序
- 语法格式
select 字段名1,字段名2..... from 表名 where 过滤条件 order by 字段名 desc/asc,字段名2 desc/asc .......
- 降序排序
# 查询所有员工的薪水并按照薪水降序排序 SELECT first_name,salary FROM employees ORDER BY salary DESC;
- 升序排序
# 查询所有有奖金的员工的薪水并按照薪水升序排序 SELECT first_name,commission_pct,salary FROM employees WHERE commission_pct IS NOT NULL #order by salary asc; ORDER BY salary; #默认是升序
- 二级排序
#所有员工按照部门排序(升序)如果部门相同再按照薪水排序(降序) SELECT department_id,salary FROM employees ORDER BY department_id ASC,salary DESC;
- 按照别名排序
#查询job_id为FI_ACCOUNT的所有员工,薪水升3倍,并按照最新的薪水排序(降序) SELECT job_id,salary,salary*3 "ns" FROM employees WHERE job_id='FI_ACCOUNT' ORDER BY ns DESC; #可以按照别名进行排序
函数
单行函数
函数关键字 | 说明 |
---|---|
LOWER() | 将所有的内容转成小写 |
UPPER() | 将所有的内容转成大写 |
CONCAT() | 字符串拼接 |
SUBSTR(“带截取的字符串”,1,5) | 截取字符串,1表示起始位置,5表示长度 |
LENGTH(‘HelloWorld’) | 字符串长度 |
INSTR(‘HelloWorld’, ‘W’) | w在字符串中的首次出现的位置 |
LPAD(salary,10,’*’) | 向右对齐,10 表示字符串的长度,如果长度不够用* 补全 |
RPAD(salary, 10, ‘*’) | 向左对齐,10 表示字符串的长度,如果长度不够用* 补全 |
TRIM(‘H’ FROM ‘HelloWorld’) | 去除字符串两端指定的字符 |
REPLACE(‘abcd’,‘b’,‘m’) | 将字符串中的b字符替换成m |
ROUND(45.926, 2) | 四舍五入,如果是0则保留整数,值就是46,如果是2,表示保留2为小数,值就是45.92,如果是-1,值为50 |
TRUNCATE(45.926,0): | 截断,不会进行四舍五入,直接截断,例如,0的时候,值为45 |
MOD(1,3) | 求余,求1除以3的余数 |
now() | 当前的日期和时间 |
ifnull(字段名,默认值) | 判断是否是空 |
case表达式 | 类似java的swich…case |
示例代码:
/*
LOWER('SQL Course') :将所有的内容转成小写
UPPER('SQL Course') :将所有的内容转成大写
*/
SELECT LOWER('aAbCdE');
SELECT UPPER('aAbCdE');
SELECT LOWER(first_name),UPPER(last_name)
FROM employees;
/*
CONCAT('Hello', 'World') : 字符串拼接
SUBSTR('HelloWorld',1,5) : 截取子串
1 :表示起始位置(注意:索引位置从1开始)
5 :表示长度
LENGTH('HelloWorld') :字符串长度
INSTR('HelloWorld', 'W') : w在字符串中的首次出现的位置
LPAD(salary,10,'*') : 向右对齐
10 :表示字符串的长度
* :如果长度不够用*补全
RPAD(salary, 10, '*') :向左对齐
10 :表示字符串的长度
* :如果长度不够用*补全
TRIM('H' FROM 'HelloWorld') : 去除字符串两端指定的字符
REPLACE('abcd','b','m') : 将字符串中的b字符替换成m
*/
SELECT CONCAT('hello','longge');
SELECT CONCAT(first_name,last_name)
FROM employees;
SELECT SUBSTR('HelloWorld',2,2);
SELECT first_name,LENGTH(first_name)
FROM employees;
SELECT INSTR('HelloWorldWWWW', 'W');
SELECT LPAD(salary,10,' '),RPAD(salary,10,'*')
FROM employees;
SELECT TRIM('H' FROM 'HHHHHelloHWorldHHHHHHH');
SELECT TRIM(' ' FROM ' ello World ');
SELECT REPLACE('abcdefgaaaaaaaaaa','a','A');
/*
ROUND: 四舍五入
ROUND(45.926, 2) 45.93
TRUNCATE: 截断
TRUNCATE(45.926) 45
MOD: 求余
MOD(1600, 300) 100
*/
SELECT ROUND(45.926, 2),ROUND(45.926, 0),ROUND(45.926, 1),ROUND(45.926, -1);
SELECT TRUNCATE(45.926, 2),TRUNCATE(45.926, 0),
TRUNCATE(45.926, 1),TRUNCATE(45.926, -1);
#结果的正页和第一个参数正负有关
SELECT MOD(3,2),MOD(-3,2),MOD(3,-2),MOD(-3,-2);
/*
now() : 当前的日期和时间
*/
SELECT NOW();
/*
ifnull(字段名,默认值) : 如果字段的内容为null那么就用默认值进行替换
*/
SELECT commission_pct,IFNULL(commission_pct,100)
FROM employees;
#需求:求所有员工的工资。
SELECT salary+ IFNULL(commission_pct,0) * salary
FROM employees;
case 表达式
-
语法格式
case 字段名 when 值1 then 返回值1 when 值2 then 返回值2 when 值3 then 返回值3 ....... else 返回值 end
-
示例代码:
查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, #则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, #30 号部门打印其工资的 1.3 倍数 SELECT department_id,salary,CASE department_id WHEN 10 THEN salary*1.1 WHEN 20 THEN salary*1.2 WHEN 30 THEN salary*1.3 END AS new_salary FROM employees WHERE department_id IN(10,20,30); #在上一道题的基础上加一个其它部门为原来的2倍 SELECT department_id,salary,CASE department_id WHEN 10 THEN salary*1.1 WHEN 20 THEN salary*1.2 WHEN 30 THEN salary*1.3 ELSE salary*2 END AS new_salary FROM employees;
聚合函数(组函数)
组函数作用于一组数据,并对一组数据返回一个值
组函数语法格式:
SELECT [字段1,字段2] 组函数(字段名), ...
FROM 表名
[WHERE 过滤条件]
[GROUP BY 需要分组的字段名]
[ORDER BY 需要排序的字段名];
AVG(平均值)和 SUM(合计)函数
可以对数值型数据使用AVG 和 SUM 函数
示例代码:
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
MIN(最小值)和 MAX(最大值)函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数
示例代码:
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
COUNT(计数)函数
COUNT(*) 返回表中记录总数,适用于任意数据类型
示例代码:
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
也可以使用数值来记录总数,效果和count()相同,但效率要优于count()
SELECT COUNT(1)
FROM employees
WHERE department_id = 50;
分组(GROUP BY)
基本使用
- 我们可以使用group by来对所需要的数据进行分组
- group by 常和聚合函数配合使用
- 当group by单独使用时,只显示出每组的第一条记录, 所以group by单独使用时的实际意义不大
- 语法格式
SELECT 需要分组的字段, 组函数(字段) FROM 表名 GROUP BY 需要分组的字段1,需要分组的字段2;
- 示例代码
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;
连接查询(多表查询)
连接的分类
连接查询也成为多表查询,根据需求不同可以作以下分类
- 内连接与外连接
- 自连接与非子连接
- 等值连接与非等值连接
内连接与外连接
-
外连接分为左外连接与右外连接
-
内连接指的是多张表之间公有的
-
举个栗子,我有一张女生表,一张男生表
-
内连接就是两张表都有的部分,在这两张表中,内连接就是所有的情侣
-
示例代码:
#内连接,查询所有的情侣 SELECT * FROM boy JOIN girl ON boy.boy_id = girl.boy_id; ```
-
左外连接
-
左外连接就是以左边表为准,两边共有的部分(包含左边的所有数据),在这两张表中,左外连接就是所有男生以及他们的女朋友
-
示例代码
# 查询所有男生以及他们的女朋友 SELECT * FROM boy LEFT JOIN girl ON boy.boy_id = girl.boy_id; ```
-
右外连接
-
右外连接和左外连接相反,以右边表为准,两边共有的部分,在这两张表中,由外连接就是所有女生以及他们的男朋友
-
示例代码
# 查询所有女生以及他们的男朋友 SELECT * FROM boy RIGHT JOIN girl ON boy.boy_id = girl.boy_id; ```
-
满外连接
- 满外连接就是两张表的合并,包含了两张表的所有内容
- MySQL中并不支持满外连接,所以我没有介绍,如果有需要,可以使用关键字union来实现以下满外连接
#满外连接(mysql不支持) #union将两张表的数据进行合并(去重) union all不去重 select * from boy left join girl ON boy.`boy_id`=girl.`boy_id` union all select * from boy right join girl ON boy.`boy_id`=girl.`boy_id` # 在后面学到hive的时候,可以使用full实现满外连接: SELECT * FROM `boy` full JOIN girl ON boy.`boy_id`=girl.`boy_id`;
等值连接与非等值连接
- 等值连接
- 等值连接就是有
=
的连接#等值连接 SELECT first_name FROM employees WHERE first_name='Diana';
- 等值连接就是有
- 非等值连接
- 非等值连接就是不使用=的连接,例如前面使用的between关键字
#查询所有员工薪水的等级 SELECT e.`first_name`,e.`salary`,j.`GRADE` FROM employees e JOIN job_grades j ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;
- 非等值连接就是不使用=的连接,例如前面使用的between关键字
自连接与非自连接
- 自连接
- 自连接就是自己这张表与自己进行连接
#查询所有的员工姓名及管理者的姓名。 SELECT e.`first_name` 员工姓名,e2.`first_name` 管理者姓名 FROM employees e JOIN employees e2 #e当成员工表 e2管理者表 ON e.`manager_id`= e2.`employee_id`;
- 自连接就是自己这张表与自己进行连接
- 非自连接
- 非自连接就是自己这张表和其它表做连接
查询所有员工薪水的等级 SELECT e.`first_name`,e.`salary`,j.`GRADE` FROM employees e JOIN job_grades j ON e.`salary` BETWEEN j.`LOWEST_SAL` AND j.`HIGHEST_SAL`;
- 非自连接就是自己这张表和其它表做连接
- 注意事项
- 如果字段在多张表中都有,那么该字段前面应该加表名(因为效率高)
- 如果没有连接条件或连接条件错误会导致笛卡尔集的错误
- 笛卡尔集会在下面条件下产生:
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接
- 为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件
子查询
- 在一个查询语句a再嵌套另一个查询语句b,那么b查询语句叫作子查询。a查询语句叫作主查询(外查询)
- 子查询分为 单行子查询 和多行子查询
- 子查询中先执行子查询再执行主查询
单行子查询
- 子查询的结果只有一行
- 单行子查询使用的运算符: > >= < <= <>
- 示例代码
*/ #需求:谁的工资比 Abel 高? #方式一 : #1.先查出Abel工资是多少 SELECT salary FROM employees WHERE last_name='Abel'; #11000 #2.再查询有哪些员工的工资比查出的工资高 SELECT last_name,salary FROM employees WHERE salary > 11000; #方式二:自连接 SELECT e.`last_name`,e.`salary` FROM employees e JOIN employees e2 ON e.`salary` > e2.`salary` AND e2.`last_name`='Abel'; #方式三:子查询 SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name='Abel' ); #需求:题目:返回job_id与141号员工相同,salary比143号员工多的员工 # 姓名,job_id 和工资 #方式一: #1.先查询出141号员工的job_id SELECT job_id FROM employees WHERE employee_id=141;#ST_CLERK #2.查询出143号员工的薪水 SELECT salary FROM employees WHERE employee_id=143;#2600 #3.查询出薪水大于2600,job_id为ST_CLERK SELECT job_id,salary FROM employees WHERE job_id='ST_CLERK' AND salary>2600; #方式二 SELECT job_id,salary FROM employees WHERE job_id=( SELECT job_id FROM employees WHERE employee_id=141 ) AND salary>( SELECT salary FROM employees WHERE employee_id=143 ); #题目:返回公司工资最少的员工的last_name,job_id和salary #方式一: #1.先求出最少工资 SELECT MIN(salary) FROM employees; #2100 #2.查询薪水为2100的员工的last_name,job_id和salary SELECT last_name,job_id,salary FROM employees WHERE salary=2100; #方式二: SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); #题目:查询最低工资大于50号部门最低工资的部门id和其最低工资 SELECT department_id,MIN(salary) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id=50 );
多行子查询
- 子查询的结果有多行
- 多行子查询使用的运算符: in any all
- 示例代码
#多行子查询: #题目:返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员 # 工号、姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ANY( #查询job_id为‘IT_PROG’部门的所有薪水 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id <> 'IT_PROG'; #题目:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 # 的员工号、姓名、job_id 以及salary SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ALL( #查询job_id为‘IT_PROG’部门的所有薪水 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id <> 'IT_PROG';
分页
- 分页比较简单,当我们查询数据过大时,可以使用分页来限定显示的数据条数,就像我们在浏览电商网站时,一个页面可能只有10条商品,再次下拉时,会有一个加载的交互,然后再出来下10数据
- 语法格式
SELECT * FROM table LIMIT 起始位置,展示的数据条数;
- 分页通用公式
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;
总结
本章节主要对Mysql的查询方法进行归纳总结,大家不用死记硬背,知道我这篇博客在这里,当需要用时,打开博客搜索相关API即可,还是那句话,Mysql的学习是为了我们后面大数据框架中的Hive语法打基础的…重在理解