数据库基础(9) . DML-多表操作

3.3.9.多表关联

student 表中 外键 team_id 指向 team表中主键

3.3.10.select子查询

3.3.10.1.实例1

select(select count(*) from student where stu_sex = 1) as 男学生人数,(select count(*) from student where stu_sex = 0) as 女学生人数;

3.3.10.2.实例2

select team_id, team_title, stu_count
, ( select count(*) from student where team.team_id = student.team_id) count
from team;

3.3.11.from子查询

select *
from (select team_id, avg( stu_weight) swfrom studentgroup by team_id) ll
where ll.sw > 50

3.3.12.where子查询

in 包含 all 全部 any 任意 exists 存在

3.3.12.1.all

– all 全部 --全部都满足,才会取出来
– > ALL ：比子查询返回结果中的所有值都大。
– < ALL ：比子查询返回结果中的所有值都小。

-- 查询学生表中，身高大于team_id为1所有学生的最大身高的学生记录
select stu_name, stu_height
from student
where stu_height > all (-- 子查询 : 最大为 182select stu_heightfrom studentwhere team_id = 1
);

3.3.12.2.any

– >ANY：比子查询返回结果中的某个值大。
– =ANY：与子查询返回结果中的某个值相等。
– <ANY：比子查询返回结果中的某个值小。

-- 查询学生表中，身高小于team_id为3所有学生的任意身高的学生记录
select stu_name, stu_height
from student
where stu_height < any (-- 子查询 : 分别为 156 , 166, 173select stu_heightfrom studentwhere team_id = 3
);

3.3.12.3.exists

–判断子查询是否有结果（强调有无，不关心具体是什么）
–exists：子查询至少返回一行时条件为true。

-- 有返回
select team_id, team_title, stu_count
from team
where exists (select 1
);-- 有返回
select team_id, team_title, stu_count
from team
where exists (select  stu_id from student
);-- 无返回
select team_id, team_title, stu_count
from team
where exists (select  stu_id from student where 1 != 1
);-- 根据条件 有的有返回, 有的没有
select team_id, team_title, stu_count
from team
where exists (select  team_id from student where team.team_id = student.team_id
);select team_id, team_title, stu_count
from team p
where exists (-- 子查询返回null , 但这依赖是一条记录 select  stu_count from team c where c.team_id = 4
);

3.3.12.4.not exists

–not exists：子查询不返回任何一行时条件为true。

-- 有返回
select team_id, team_title, stu_count
from team
where not exists (select 1
);-- 无返回
select team_id, team_title, stu_count
from team
where not exists (select  stu_id from student where 1 != 1
);-- 根据条件 有的有返回, 有的没有
select team_id, team_title, stu_count
from team
where not exists (select  team_id from student where team.team_id = student.team_id
);

not exists：特别应用，当子查询和主查询有关联条件时，相当于从主查询中去掉子查询的数据。

-- 有返回 , 但不包含 '玄武岩合唱团'
select team_id, team_title, stu_count
from team pt1
where not exists (select 1 from team pt2where pt2.team_title='玄武岩合唱团'and  pt2.team_title=pt1.team_title
);

3.3.13.update 子查询

在修改语句中 可以使用子查询的结果为字段赋值

update team
setstu_count = (select count(*) from student where team.team_id = student.team_id)
where 1=1

3.3.14.横向连表

3.3.14.1.笛卡尔乘积

查出关联的全部数据

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_count
from student s , team t
where s.team_id = t.team_id

3.3.14.2.内连 inner join

查出关联的全部数据

inner 是默认的, 也就是使用时可以省略

--  join
select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_countfrom student s join team t on s.team_id = t.team_id;-- inner join
select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_count
from student s inner join team t on s.team_id = t.team_id;

3.3.14.3.左连 left

查出左边表全部数据

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_count
from student s left join team t on s.team_id = t.team_id;

3.3.14.4.右连 right

查出右边表全部数据

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_count
from student s right join team t on s.team_id = t.team_id

3.3.14.5.全连 full (mysql 不支持)

select stu_id, stu_name, stu_sex, stu_birth, stu_weight, stu_height, s.team_id, stu_info,t.team_id, team_title, stu_count
from student s full join team t on s.team_id = t.team_id

3.3.14.6.多个连表

添加 sex 表

-- ----------------------------
-- Table structure for sex
-- ----------------------------
DROP TABLE IF EXISTS `sex_code`;
CREATE TABLE `sex_code`  (`sex_id` int(11) NOT NULL COMMENT '性别主键',`sex_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别名称',PRIMARY KEY (`sex_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of sex
-- ----------------------------
INSERT INTO `sex_code` VALUES (0, '女');
INSERT INTO `sex_code` VALUES (1, '男');

连接多个表

select stu_id, stu_name, stu_sex, s.team_id,t.team_id, team_title,c.sex_name
from student s left join team t on s.team_id = t.team_idleft join sex_code c on s.stu_sex = c.sex_id

3.3.15.纵向连表

数据记录合并

3.3.15.0.union 合并数据

select '男' as 性别 , count(*) as 人数  from student where stu_sex = 1
union
select '女', count(*)  from student where stu_sex = 0

3.3.15.1.union all 合集

select substr(stu_name,1,1) name   from student where stu_sex = 1
union all
select substr(stu_name,1,1) name   from student where stu_sex = 0

3.3.15.2.union 去重复 排序 并集

select substr(stu_name,1,1) name   from student where stu_sex = 1
union 
select substr(stu_name,1,1) name   from student where stu_sex = 0

3.3.15.3.intersect 交集

通过 in

select substr(stu_name,1,1) name
from student
where stu_sex = 1and substr(stu_name,1,1)  in (select substr(stu_name,1,1)from studentwhere stu_sex = 0);

3.3.15.4.minus 在前不在后的元素集合 差集

通过 not in

select substr(stu_name,1,1) name
from student
where stu_sex = 1and substr(stu_name,1,1) not in (select substr(stu_name,1,1)from studentwhere stu_sex = 0);

3.3.16.执行顺序

3.3.16.1.编写顺序

select [呈现形式] 代替表达式(与from 连接)
from [数据来源] 临时表
join [连表]
where [过滤条件] 嵌套查询
group by [分组模式]
having [分组过滤条件]
order by [排序]
limit [n],[m] 显示 [ n + 1, n + m ]

3.3.16.2.执行顺序

1 from / join
2 where
3 group by
4 having
5 select
6 distinct
7 union
8 order by
9 limit