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