MYSQL函数、多表查询

最新推荐文章于 2024-04-19 13:36:26 发布
最新推荐文章于 2024-04-19 13:36:26 发布
阅读量411 收藏 2
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/adsdsasada/article/details/109680830
版权

子查询

当多个表中数据存在关联，且需要先由另一个表数据查询后使用进行当前表数据的查询

由多条sql语句组成用于返回指定数据

在这里插入图片描述

在这里插入图片描述

子查询中的sql语句一般返回一列数据并且有时可能返回唯一数据

函数

聚合函数

聚合函数：对一组值进行计算，并返回计算后的值 ，具有统计数据的作用，将多行数据进行聚合操作返回一个指定值

注意：在使用聚合函数时一般不会再对其他数据进行单独查询，因为聚合函数执行后仅返回一条数据，所以在进行查询时，如果其他数据存在多条也只返回第一条语句

sum()

求和，将指定列中所有数据求和后返回

在这里插入图片描述

avg()

求平均值，获取指定列平均值在这里插入图片描述

max()

在这里插入图片描述

min()

在这里插入图片描述

count()

返回满足条件的行数

在这里插入图片描述

字符串函数

CHARSET(str)返回字串字符集
CONCAT (string2 [,… ])连接字串
INSTR (string ,substring )返回substring在string中出现的位置,没有返回0
UCASE (string2 )转换成大写
LCASE (string2 )转换成小写
LEFT (string2 ,length )从tring2中的左边起取length个字符
LENGTH (string )string长度
REPLACE (str ,search_str ,replace_str )在str中用replace_str替换search_str
STRCMP (string1 ,string2 )逐字符比较两字串大小,
SUBSTRING (str , position [,length ])从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim去除前端空格或后端空格

charset()

返回指定字段字符串编码

在这里插入图片描述

concat()

连接字符串。将两字段数据或字符串进行连接

在这里插入图片描述

instr()

查找指定字符串出现的位置,不存在则返回0

在这里插入图片描述

ucase()

将指定字段数据已大写形式返回

在这里插入图片描述

lcase()

将指定字段数据已小写形式返回

在这里插入图片描述

left()

返回指定字段从左开始指定长度的数据

在这里插入图片描述

length()

返回指定字段字符个数

在这里插入图片描述

replace()

使用指定字符串替换字段中某一字符串

在这里插入图片描述

substring()

返回指定字段自定起始位置开始长度的字符串

在这里插入图片描述

trim()

空格替换

在这里插入图片描述

日期函数

用于进行日期转换以及日期格式定义的函数

ADDTIME (date2 ,time_interval )将time_interval加到date2
CURRENT_DATE ( )当前日期
CURRENT_TIME ( )当前时间
CURRENT_TIMESTAMP ( )**当前时间戳 **
DATE (datetime )返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_value d_type )在date2上减去一个时间
DATEDIFF (date1 ,date2 )两个日期差
NOW ( )当前时间
YEAR|Month|DATE (datetime )年月日

current_date()

返回当前时间(date类型)

current_time()

返回当前时间(time类型)

current_timstamp()

返回当前时间(timestamp类型)

now()

返回当前时间(datatime类型)

date()

返回指定日期字段date部分数据

在这里插入图片描述

time()

返回指定日期字段time部分数据

在这里插入图片描述

date_add()

为包含date数据的日期类型进行添加

在这里插入图片描述

需要使用interval 进行数值的转换

date_sub()

为包含date数据的日期类型进行减少

在这里插入图片描述

datediff()

返回两个日期直接相差天数（第一个日期减第二个日期）

year()

返回指定日期类型的年

month()

返回指定日期类型的月

day()

返回指定日期类型的日

数学函数

abs()

返回数值类型绝对值

ceiling()

向上取整

floor()

向下取整

least()

获取一行数据中最小值

rand()

随机数

在这里插入图片描述

在数据库表中有以下字符数据，如：
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2
现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要排成这样：
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2

#-前面升序 -后面降序
select * from sort order by str 
#查找每个-的位置
select instr(str,'-') from sort
#获取-左边的值
select CONVERT(left(str,instr(str,'-')-1),SIGNED) from sort

#获取-右边的值
select CONVERT(right(str,length(str)-instr(str,'-')),SIGNED) from sort

select * from sort order by CONVERT(left(str,instr(str,'-')-1),SIGNED) asc , CONVERT(right(str,length(str)-instr(str,'-')),SIGNED) desc

分组

关键字group by

使用指定字段进行分组，字段数据的个数就是分组的个数，这样在使用聚合函数时只会对相应分组进行聚合操作

在这里插入图片描述

聚合函数一般与分组一同使用，但是在进行条件判断时，如果对于聚合函数进行判断且存在分组那么不能使用where进行条件判断必须使用having（只针对于聚合函数的值的判断）

在这里插入图片描述

where进行筛选与having的区别

WHERE子句：
用来筛选 FROM 子句中指定的操作所产生的行
GROUP BY子句：
用来分组 WHERE 子句的输出
HAVING子句：
用来从分组的结果中筛选行

多表操作

在进行数据查询时想查询并返回多个表的数据的时候使用多表查询

连接查询

全连接

在进行多表连接查询时返回两表的笛卡尔积，在进行表连接时会将每张表的每一行与另一张表的每一行数据进行连接

注意：使用全连接会造成大量的冗余数据，所以一般在where语句中进行筛选

不建议使用全连接进行连接查询，因为全连接进行连接效率不高，因为是首先现将所有数据返回笛卡尔积之后使用where进行筛选

执行步骤为先将所有数据进行连接之后再进行条件筛选

在这里插入图片描述

内连接

INNER JOIN

语法：select *from 表1 inner join 表2 on 连接条件

内连接必须书写on连接条件（如果不写与全连接结构一致）

与全连接不同的是，内连接是在进行连接时直接将数据进行过滤之后进行返回

在这里插入图片描述

外连接

左外连接 (LEFT JOIN)
右外连接 (RIGHT JOIN)

语法：select * from 表1 left[right] join 表2 on连接条件

以左表或右表为准，保留左表或右表全部数据的基础上进行连接

在这里插入图片描述

在这里插入图片描述

sql查询语法总结

select 查询展示的字段与函数

from 数据源（可以是由另一个查询语句返回的结果作为表）

inner /left/right join 连接查询

on 连接条件

where 筛选条件

group by 分组字段

having 分组条件筛选

order by 排序字段 asc /desc

limit 起始行 ，截取行

在进行sql查询语句书写时

1、明确数据源连接

查询展示字段所在的表

2、筛选条件where

根据需求决定展示数据的行有哪些

3、分组条件

根据需求决定使用分组的字段（注意分组字段是以指定字段的值进行分组）

4、排序与截取

一些练习

–21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息
select avg(c.score) from computer c left join student s on s.studentid=c.studentid
group by s.classid having avg(c.score)>70 order by avg(c.score) desc;

–20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称
select s.studentid,s.studentname,c.score,class.classname from student s
left join computer c on s.studentid=c.studentid left join class on class.classid=s.classid
where s.studentid =(select c.studentid from computer c where c.score = (select max(score) from computer));

–19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩
select s.studentid,s.studentname,s.studentage ,c.score from student s
left join computer c on s.studentid=c.studentid
where s.studentage between 20 and 25;

–18查询出G1T07班学生的编号、姓名、班级名称、计算机成绩
select s.studentid,s.studentname,class.classname,c.score from student s
left join computer c on s.studentid=c.studentid left join class on class.classid=s.classid
where s.classid = (select class.classid from class where class.classname=‘G1T06’)
group by s.studentid;

–17删除姓名包含“c”字符的学生计算机成绩 成绩为null
update computer set score=null where studentid in
(select s.studentid from student s where s.studentname like’%c%’);

–17删除姓名包含“c”字符的学生计算机成绩 此行移除
delete from computer where studentid in
(select s.studentid from student s where s.studentname like’%c%’);

–16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
select classname,avg(score) from class left join student s on class.classid=s.classid
left join computer c on s.studentid=c.studentid group by classname;

–15将参加过考试的学生的年龄更改为20
update student set studentage=20 where studentid in
(select studentid from computer where score is not null);

–14查询男、女生的计算机平均成绩、最高分、最低分
select s.studentsex,avg(c.score),max(c.score),min(c.score) from computer c left join student s
on c.studentid=s.studentid group by s.studentsex;

–13查询出班级编号大于3的男生的学生信息
select s.* from student s where classid>3 and studentsex=‘男’;

–12查询出班级编号大于3的学生的计算机平均分信息
select avg(score) from computer c
where c.studentid in (select s.studentid from student s where classid>3 );

–11查询出班级编号大于3的学生基本信息
select s.* from student s where classid>3 ;

–10查询出和Jim住在同一个地方的学生的基本信息
select s.* from student s where studentaddress=
(select s.studentaddress from student s where studentname=‘Jim’ );

–9查询显示出班级的计算机平均分大于80的班级名称、平均分信息，并按照平均分降序显示
select s.classid,avg(c.score) from student s left JOIN
computer c on s.studentid=c.studentid
GROUP BY classid having avg(c.score)>80 order by avg(c.score) desc;

–8查询出每个班的学生的计算机成绩的平均分，最高分，最低分
select s.classid,avg(c.score),max(c.score),min(c.score) from student s left JOIN
computer c on s.studentid=c.studentid group by s.classid;

–7查询出所有学生的信息和计算机成绩信息
select s.*,c.score from student s left join
computer c on s.studentid=c.studentid ;

–6查询出计算机成绩大于80分的学生编号、姓名、班级名称
select class.classname,s.studentname,s.classid from student s left join
computer c on s.studentid=c.studentid left join class on s.classid=class.classid
where score>80;

–5查询出姓名中包含有c的学生编号、姓名、计算机成绩
select s.studentid,s.studentname,c.score from student s left join
computer c on s.studentid=c.studentid where s.studentname like(’%c%’);

–4查询出年龄大于19岁的学生编号、姓名、计算机成绩
select s.studentid,s.studentname,c.score from student s left join
computer c on s.studentid=c.studentid where s.studentage>19;

–3查询出学生的编号、姓名、所在班级名称、计算机成绩
select s.studentid,s.studentname,class.classname,c.score from student s
left join computer c on s.studentid=c.studentid left join class on s.classid=class.classid
where c.score is not null;

–2查询参加过考试的学生信息
select s.* from student s
left join computer c on s.studentid=c.studentid where c.score is not null;

–1查询出学生的编号，姓名，计算机成绩 简化
select s.studentid, s.studentname, c.score from student s
left join computer c on s.studentid=c.studentid;

–1查询出学生的编号，姓名，计算机成绩
select student.studentid,student.studentname,computer.score from student
left join computer on student.studentid=computer.studentid;

adsdsasada
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Java+MysQL：Java连接MysQL及进行简单的查询操作
开发者在线
05-14 648
最近想学习一下Java操作MySQL等数据库的教程，但是把整个网络翻遍也没找到令人满意的教程，各种报错，所以就决定，把自己的学习过程记录下来和大家分享，既可以作为日后复习巩固之用，又可以帮到需要的人，希望可以帮助以后想学习的朋友们少走一些弯路。大家一起交流共勉吧。
MySQL通过自定义函数实现递归查询父级ID或者子级ID
09-08
主要介绍了MySQL通过自定义函数实现递归查询父级ID或者子级ID,本文给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下
五、多表连接查询——聚合函数
python伊甸园的博客
12-17 1912
五、多表连接查询——聚合函数 1.什么是聚合函数 聚合函数对一组数据执行计算，集中生成汇总值。 2.常见的聚合函数有哪些？ 一部分常见的数值处理函数。 聚合函数 描述 sum() 计算某列的总和 avg() 计算某列的平均值 max() 计算某列的最大值 min() 计算某列的最小值 count() 计算某列的行数 3.示例 ...
【MySql】 MySQL中的MAX()和MIN()函数的高效使用
最新发布
2333333
04-19 1204
我说过 我不闪躲我非要这么做讲不听 也偏要爱更努力爱 让你明白没有别条路能走你决定 要不要陪我讲不听偏爱靠我感觉爱等你的依赖对你偏爱痛也很愉快🎵 张芸京《偏爱》MySQL数据库是世界上最流行的关系型数据库管理系统之一，它不仅提供了存储数据的功能，还支持复杂的查询和数据操作。在数据分析中，我们经常需要找到一个数据集中的最大值和最小值。MySQL为此提供了两个非常有用的函数：MAX() 和 MIN()。接下来，我们将探讨这两个函数的使用方法以及一些高级技巧。
mysql多表查询、函数查询
程序三两行
07-19 1126
sql92语法和sql99语法实现多表查询
mysql数据库 二十一练习题 及答案 (mysql练习题)
雁迟的博客
12-19 2735
数据库二十一练习题 create table class ( classid int primary key, classname varchar(20) ) insert into class values(1,‘G1T01’); insert into class values(2,‘G1T02’); insert into class values(3,‘G1T03’); insert into class values(4,‘G1T04’); insert into class values(5,‘
MySQL使用聚合函数进行单表查询
09-08
主要介绍了MySQL使用聚合函数进行单表查询，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
0.9 MySQL表查询之常见函数
08-03
0.9 MySQL表查询之常见函数
MySQL 分组查询和聚合函数
12-14
这个时候就要用到分组查询，分组查询的目的是为了把数据分成多个逻辑组（购买化妆品的人员是一个组，不同年龄段购买化妆品的人员也是组），并对每个组进行聚合计算的过程:。 分组查询的语法格式如下： select ...
MySQL笔记之函数查询的使用
09-10
本篇文章介绍了mysql中函数查询的使用。需要的朋友参考下
Java常用函数速查表
07-08
Java常用函数速查表，做java开发的人员，必备的学习资料
SQL中CONVERT()函数用法详解
weixin_33713350的博客
02-18 839
SQL中CONVERT函数格式: CONVERT(data_type,expression[,style]) 参数说明: expression 是任何有效的 Microsoft® SQL Server™ 表达式。。 data_type 目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。 length nchar、nvarcha...
常用SQL函数、时间函数
真的以为的博客
03-26 404
常用SQL函数： 1、SUBSTRING(CONVERT(CHAR(19), 字段, 120),1,10) 可将长日期切成一个19长度的字符串，看个人用处 2、replace(字段,’-’ , ’ ~’) 把-替换成~，用于替换字段里的某些内容 3、DATE_FORMAT(date,‘yyyy-MM-dd’) 依照指定的格式格式化日期date值 MySQL的函数 ...
多表查询 子查询 与分组函数学习笔记
weixin_38319732的博客
05-13 152
多表查询 --查询部门编号,部门名称和其所处的地区的名称 和所在的城市(2表内连接) select d.department_id,d.department_name,l.location_id,l.street_address,l.city from departments d,locations l where d.location_id = l.location_...
VLOOKUP函数制作多表查询（学生信息表/员工工资表）
weixin_45789365的博客
02-02 8891
VLOOKUP函数制作多表查询（学生信息表/员工工资表） 一、vlookup函数定义 VLOOKUP函数是Excel中的一个纵向查找函数，在工作中都有广泛应用，例如可以用来核对数据，多个表格之间快速导入数据等函数功能。 二、vlookup函数格式 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 简言之： 如果要查找谁的什么东西， 则函数为：vlookup（谁,东西所在区域,东西所在区域的第几列,精确值1/近似值0） 此方法为通过左
mysql多表查询、子查询、函数详解
m0_48811221的博客
02-25 415
多表查询 交叉查询 ##########交叉查询(没用)或者笛卡尔积查询:多个表的数据的乘积 #问题:大量数据的冗余 , 在多表查询中 多表查询建立在笛卡尔积的基础上 ##语法:select * from A , B, C,D..... SELECT * FROM dept , emp; SELECT COUNT(*) FROM dept , emp; 内连接 ###########显示内连接 #语法: select * from A inner join B on 连接的条件 #
Excel中使用VLOOKUP函数实现多表之间关联搜索功能
qq_23126569的博客
04-10 2966
Sheet1中，有学生姓名和学号这两列，假设学号是空的，想要根据sheet2的信息填补sheet1的学号，则公式为：=VLOOKUP(B3,Sheet2!A:B,2,FALSE) sheet2表为： VLOOKUP(B3,Sheet2!A:B,2,FALSE) VLOOKUP（要查找的值，查找区域，要返回的结果在查找区域的第几列，精确匹配或近似匹配） B3表示sheet1中的B3，Sheet2!A:B是区域，Sheet2表的A和B区域，FALSE表示精确查询。 ...
MySQL数据的增、删、改练习题
IT小勇
06-19 4276
– 创建学生表 CREATE TABLE student2( s_id INT AUTO_INCREMENT PRIMARY KEY, s_name VARCHAR(20) NOT NULL, s_age TINYINT NOT NULL, s_sex VARCHAR(10) DEFAULT’男’ NOT NULL, s_tel VARCHAR(50), s_education VARCHAR(2...
必会SQL笔试题
热门推荐
LuckyBrown的博客
12-30 3万+
（1）表名：购物信息 购物人 商品名称 数量 A 甲 2 B 乙 4 C 丙 1 A 丁 2 B 丙 5 …… 给出所有购入商品为两种或两种以上的购物人记录
mysql聚合函数多表查询
05-10
在MySQL中，可以使用聚合函数对多个表进行查询。聚合函数包括SUM、AVG、COUNT、MAX和MIN等函数。下面是一个简单的例子，演示如何在多个表中使用聚合函数： 假设有两个表：orders和customers，orders表中包含订单信息，customers表中包含客户信息。我们可以通过以下查询获取每个客户的订单总价： ``` SELECT customers.customer_name, SUM(orders.order_price) as total_order_price FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_name; ``` 在这个查询中，我们使用了JOIN操作来连接orders表和customers表，然后使用SUM函数计算订单总价，并使用GROUP BY子句将结果按客户名称分组。这样，我们就可以得到每个客户的订单总价。 请注意，当使用聚合函数时，必须使用GROUP BY子句将结果分组。否则，查询将会返回一个错误。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值