打怪升级之小白的大数据之旅(三十三)＜Mysql的查询＞

最新推荐文章于 2024-05-19 17:03:37 发布

GaryLea

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量95

点赞数

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/Li_G_yuan/article/details/116223638

版权

打怪升级之小白的大数据之旅(三十三)

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`;

自连接与非自连接

自连接

自连接就是自己这张表与自己进行连接

#查询所有的员工姓名及管理者的姓名。
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语法打基础的…重在理解

GaryLea

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
打怪升级之小白的大数据之旅(三十三)＜Mysql的查询＞

打怪升级之小白的大数据之旅(三十四)Mysql查询基础知识上次回顾基本查询过滤模糊查询与排序多表查询单行函数多行函数组函数分组子查询库的管理表的管理基本SELECT语句查看表中内容select 字段名1，字段名2 .......from 表名select * #*表示所有的字段from 表名select查询有两个小知识点:# 第一个，select加法SELECT 1+1;# 前面的语法格式不是说了，必须from表名么？因为它的全写是:SELECT
复制链接

扫一扫