MySQL多表联查函数

2024-03-27 02:44
文章标签 mysql 函数 多表 database 联查

本文主要是介绍MySQL多表联查函数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1 多表联查

1.1 表之间的关系

表和表的关系有:

  • 一对一 老公 --> 老婆 , 人 ---> 身份证/户口本

  • 一对多 皇帝 --> 妻妾 , 人 ---> 房/车

  • 多对多 订单 --> 商品

1.2 合并结果集

合并结果集,是将多表查询的结果纵向合并

语法:

select field1,field2 from t1
union   -- 合并结果集
select field1,field2 from t2
create table tb_a(id int,name char(10),age int
);
​
create table tb_b(id int,name char(10)
);
/*合并结果集的两个表的字段数量,类型要一致
-----------
union 联合数据,将数据纵向拼接,如果有重复数据会去重
union all 如果有重复数据会全部保留
--------------
场景:
当表很大时,可以拆分成多个表
就可以使用联合查询
*/
select id,name from tb_a
union all
select id,name from tb_b

1.3 连接查询【重要】

连接查询是将多张表数据连接在一起(横向)查询返回,

这个连接是多表的乘积,t1 * t2 , 这就是笛卡尔积

连接查询需要使用表之间的关联关系来过滤数据

连接查询分为以下几种

  • 内连接

  • 外连接

1.3.1 内连接

数据准备, class表是班级表,stu是学生表, 一个班级对应多个学生

两表的关联列是 学生表(stu)中的cid,引用了班级表(class)中的主键cid

语法:

select 字段列表 from 表1 inner join 表2 on 表1.字段 = 表2.字段
/*内连接select 字段列表 from 表1 inner join 表2 on 表1.字段 = 表2.字段
*/
-- 查询学生信息以及学生关联的班级信息
select * from stu inner join class on stu.cid = class.cid;
-- 查询学生的学号,姓名,分数,班号,班名
select stu.sid,stu.sname,stu.score,stu.cid,class.cname 
from stu 
inner join class 
on stu.cid = class.cid;
-- 也可以给表设置别名
select s.sid,s.sname,s.score,s.cid,c.cname 
from stu s
inner join class c
on s.cid = c.cid;
-- 内连接特点:只会查询满足关联条件的数据
​
-- 内连接标准写法
select * from stu inner join class on stu.cid = class.cid;
-- 内连接可以简写成(推荐)
select * from stu s,class c where s.cid = c.cid;
​
-- 标准语法
-- 简写
-- 特点
-- 什么叫笛卡尔积
-- 去除笛卡尔积
​

练习

-- 查询1班信息,以及对应学生信息
select * from class c,stu s where c.cid = s.cid and c.cid = 1;
-- 查询成绩大于60的学生信息,以及对应的专业
select * from stu s,class c where s.cid = c.cid and score > 60;
-- 查询班级编号,班级名称,和每班人数
select c.cid,c.cname,count(sid) from class c,stu s 
where c.cid = s.cid
group by c.cid,c.cname

1.3.2 外连接

外连接又分为左外连接,右外连接

法:

select 字段列表 from 表1 left|right outer join 表2 on 表1.字段 = 表2.字段

内外连接有什么区别?

  • 内连接只查询符合关联添加的数据

  • 外连接会保留不符合条件的数据

-- 1) 外连接会保留不符合条件的数据
-- 2) 左外是以左表为主,左表中有不符合条件的数据也会保留
--    右外相反...
​
-- 查询学生信息以及对应的班级信息
-- 左外
select * from stu s left outer join class c on s.cid = c.cid
​
-- 右外
select * from stu s right outer join class c on s.cid = c.cid
​
-- outer可以省略
select * from stu s left join class c on s.cid = c.cid

1.4 子查询【重要】

子查询(subquery)也叫嵌套查询

  • 将sql语句当表,写在from后面

  • 将sql语句当条件,写在where后面

-- 子查询就是嵌套查询
-- 查询的结果是一张虚拟表
select sid,sname,age from stu where sex = '男'
​
-- 子查询当表
select * from 
(select sid,sname,age from stu where sex = '男') t
where t.age > 50
​
-- 子查询当条件,但是要注意条件的值的个数(列数和行数)
select age from stu where sid = 1001
-- 年龄大于学号为1001这个人的年龄
select * from stu 
where age > (select age from stu where sid = 1001)
​
-- 查询与张三同一个班级的学生。
select * from stu 
where cid = (select cid from stu where sname = '张三');
-- 成绩高于3号班级所有人的学生信息
select * from stu
where score > (select max(score) from stu where cid  = 3)
-- 有2个以上直接组员的学生信息
select * from stu where sid in(
select groupLeaderId from stu 
group by groupLeaderId 
having count(sid) > 2)
​
-- 求1008学生编号、姓名、组长编号和组长姓名
SELECTt1.sid,t1.sname,t1.groupLeaderId,t2.sname 
FROMstu t1,(SELECT* FROMstu WHEREsid = ( SELECT groupLeaderId FROM stu WHERE sid = 1008 ) ) t2 
WHEREt1.sid = 1008
-- 上面这题可以改造成自连接
select s.sid,s.sname,s.groupLeaderId,z.sname from stu s,stu z where s.groupLeaderId = z.sid and s.sid = 1008
​
 

2 函数
 

2.1 字符串函数
 


-- =========== 字符串函数 ============
select charset('abc'); -- 返回字符集
select charset('abc') from dual; -- from dual,只是为了补全sql语句
-- concat(str1,....) 连接字符串 【重要】
select concat('a','1','b','2') from dual;
select concat('a','1','b','2'),sid,sname from stu;
select concat(sid,sname),sid,sname from stu;
select concat('我叫',sname,',今年',age,'明年',age+1,'岁') from stu;
-- instr(string,substring),返回substring在string中出现的位置,没有返回 0
select instr('java','c');
select instr(sname,'三') from stu;
​
-- 转大写,转小写
select ucase('abc'),lcase('abc');
-- left(string2,length) 从 string2 中的左边起取 length 个字符
select left('java',2)
select left(sname,1) from stu; -- 取出姓氏
​
-- length 获得长度 , utf8中,一个中文三个字节
select length(sname),sname from stu;
select length('abc');
​
-- 替换
-- REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str
select replace('java','av','AV');
select replace(sname,'三','叁') from stu;
​
-- SUBSTRING (str , position [,length ] 截取
select substring('java',2); -- 从第2位,取到末尾
select substring('java',2,2); -- 从第2位,取2个
-- 取出stu表中姓名,姓,名
select sname 姓名 ,left(sname,1) 姓,substring(sname,2) 名 from stu;
​
insert into stu (sname) value('java');
insert into stu (sname) value(substring('java',2,2));
update stu set sname = left('史密斯',1) where sid = 1011
 

2.2 数学函数
 

-- =========== 数学函数 ============
-- 绝对值
select abs(-1);
select abs(1);
-- 向上取整,向下取整
select ceiling(10.1),floor(10.1);
-- 保留几位小数
select format(1.12345,3);
-- 随机数,0-1之间
select rand();
insert into stu (sid) values (rand() * 1000)
-- 小数四舍五入,可以指定小数点保留的位数
select round(5.12645,2);
-- truncate() 截取数据,后面指定保留的小数点位数
select truncate(5.12645,2);
 

2.3 日期函数【重要】
 

-- =========== 日期函数 ============
-- 获得当前时间
select sysdate();
select now();
insert into t10 values (rand()*1000,sysdate());
select current_date(); -- 当前日期
select current_time();  -- 当前时间
select current_timestamp(); -- 当前日期时间
-- 添加时间
select addtime('17:19:10','01:00:50')
-- 添加日期
select adddate('2000-01-30',interval 2 year);
select date_add('2000-01-30',interval 2 month);
select date_add('2000-01-30',interval 2 day);
-- 时间相减
select date_sub('2000-01-30',interval 2 month);
-- 日期相差多少天
select datediff('2022-01-01',now())
-- 获得单独年,月,日
select year(now());
select month(now());
select day(now());
-- 当月生日人数
select count(*) from t10 where month(birthday) = month(now())
 

2.4 日期字符串转换函数【重要】
 

-- =========== 日期/字符串转换函数 ============
/*日期 --> 字符串  date_format(date,'%Y-%m-%d')字符串 --> 日期 str_to_date('datestr','%Y-%m-%d') ---------------------日期模板%Y年 %m月 %d日%H时 %i分钟 %S秒
*/
select date_format(now(),'%Y年%m月%d日')
select str_to_date('2022年11月18日','%Y年%m月%d日')
​
insert into t10 (id,birthday) value (1,str_to_date('2020-01-01','%Y-%m-%d'))
 

 

2.5 流程函数【重要！！】
 

-- 范围判断
-- CASE WHEN [expr1] THEN [result1]… ELSE [default] END 如果expr是真, 返回result1,否则返回default
-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,score,case
when score < 60 then '不及格'
when score <= 70 then '及格'
when score <= 80 then '中等'
when score <= 90 then '良好'
else '优秀'
end as 等级
​
from stu
 

 

 

 

 

 

 


                                    

                                        这篇关于MySQL多表联查函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
                                    

                                    

                                

                            

                            

                        

                    

                    

                        

                            

                            http://www.chinasem.cn/article/850694。
                            
                        

                    

                    
                    

                        
                            
                        
                    

                

                

                    相关文章
                

                    

                        

                            

                                

                                    
                                        SQL中的外键约束
                                    
                                

                            

                            

                                

                                    SQL中的外键约束
                                

                                

                                    外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表  以下是一个外键约束的实例展示
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        基于MySQL Binlog的Elasticsearch数据同步实践
                                    
                                

                            

                            

                                

                                    基于MySQL Binlog的Elasticsearch数据同步实践
                                

                                

                                    一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        如何去写一手好SQL
                                    
                                

                            

                            

                                

                                    如何去写一手好SQL
                                

                                

                                    MySQL性能  最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。  《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        性能分析之MySQL索引实战案例
                                    
                                

                            

                            

                                

                                    性能分析之MySQL索引实战案例
                                

                                

                                    文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结   一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        MySQL数据库宕机，启动不起来，教你一招搞定！
                                    
                                

                            

                            

                                

                                    MySQL数据库宕机，启动不起来，教你一招搞定！
                                

                                

                                    作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        hdu1171（母函数或多重背包）
                                    
                                

                            

                            

                                

                                    hdu1171（母函数或多重背包）
                                

                                

                                    题意：把物品分成两份，使得价值最接近  可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）  其中指数为价值，每一项的数目为（该物品数+1）个 代码如下：  #include<iostream>#include<algorithm>
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        MySQL高性能优化规范
                                    
                                

                            

                            

                                

                                    MySQL高性能优化规范
                                

                                

                                    前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范  所有数据库对象名称必须使用小写字母并用下划线分割  所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）  数据库对象的命名要能做到见名识意，并且最后不要超过32个字符  临时库表必须以tmp_为前缀并以日期为后缀，备份
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        [MySQL表的增删改查-进阶]
                                    
                                

                            

                            

                                

                                    [MySQL表的增删改查-进阶]
                                

                                

                                    🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢  💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        MySQL-CRUD入门1
                                    
                                

                            

                            

                                

                                    MySQL-CRUD入门1
                                

                                

                                    文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL      认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Java 连接Sql sever 2008
                                    
                                

                            

                            

                                

                                    Java 连接Sql sever 2008
                                

                                

                                    Java 连接Sql sever 2008 /Sql sever 2008 R2      import java.sql.Connection;    import java.sql.DriverManager;    import java.sql.ResultSet;    import java.sql.Statement;    public class TestJDBC
                                

                                

                                    阅读更多...
                                

                            

                        

                    

            

        

    


    


    

        

            Copyright China编程   23002807@qq.com
        

        

            沪ICP备2023033072号-2