mysql多表查询，创建视图
1.union联合查询
将多个select语句的结果纵向组合
select * from stuinfo union select * from stuinfoo;
union:
1.all #显⽰全部记录
2.distinct #(去除重复的值他是默认)
select * from stuinfo union all select * from stuinfoo;
查北京的⼥⽣和上海的男⽣ [晚上⾃⼰写⼀篇或多编]
select * from stuinfo where (city='上海'and sex='male') or (city='北京'and sex='female');
select * from stuinfo having city='上海'and sex='male'union all select * from stuinfo having city='北京'and sex='female';
Union的要求:
1.两边的select语句的字段数要⼀致
2，字段名可以不⼀样，最终按照第⼀个select语句的字段名返回
3.两边可以具有相同或不同的数据类型
男⽣的年龄降序,⼥⽣的年龄升序 [ 晚上⾃⼰写⼀篇或多编]
(select * from stuinfo having sex=1order by age desc limit 1000) union all (select * from stuinfo having sex=2order by age asc limit 1000);
2.多表查询
(1)分类
1.内连接
2.外连接
a.左外连接
b.右外连接
3.交叉连接
4.⾃然连接
(2)内连接(inner join)
#查询学⽣信息
select * from stuinfo inner join stumarks on stuinfo.sid = stumarks.stuno;
#inner 可以不⽤写
#给表取别名的 as 也可以省略不写
select * from stuinfo a join stumarks b on a.sid = b.stuno;
#inner join数据不完整就不显⽰
⼤坑别踩:
表连接肯定经常要⽤，如果你查询单条数据,⽽这条数据的从表信息不完整，使⽤内链接，查询出的结果为空,
但是,python代码的写法是⼀致的，为了避免python报错，基本不使⽤内链接.
(3)外连接
a.左外连接(left join)
select * from stuinfo a left join stumarks b on a.sid=b.sid;
#以左边的表为准，右边表中没有的记录⽤null表⽰
b.右外连接(right join)
select * from stuinfo a right join stumarks b on a.sid=b.sid;
##以右边的表为准，左边表中没有的记录⽤null表⽰
思考:
select * from表⼀inner join表⼆on表⼀.公共字段=表⼆.公共字段;
select * from表⼆inner join表⼀on表⼆.公共字段=表⼀.公共字段;
select * from表⼀left join表⼆on表⼀.公共字段=表⼆.公共字段;
select * from表⼀right join表⼆on表⼀.公共字段=表⼆.公共字段;
select * from表⼀left join表⼆on表⼀.公共字段=表⼆.公共字段;
select * from表⼆right join表⼀on表⼆.公共字段=表⼀.公共字段; (4)交叉连接(cross join)
#交叉连接返回的结果和内链接⼀样的
select * from stuinfo a cross join stumarks b on a.sid=b.stuno;
select * from stuinfo,stumarks;
(5)⾃然连接(natural)
1.natural join #⾃然内连接
2.natural left join #⾃然左外连接
3.natural right join #⾃然右外连接
select * from stuinfo a natural join stumarks b;
select * from stuinfo a natural left join stumarks b;
select * from stuinfo a natural right join stumarks b;
结论:
1.⾃动判断连接条件，依据的是同名字段名
2.如果没有同名的字段名返回的是笛卡尔积
3.⾃动返回结果并进⾏整理;
a.连接字段最好保留⼀个
b.连接字段最好放在最前⾯
(6)using
#指定连接字段,using也会查询出的结果进⾏整理，整理的⽅式和⾃然连接相同.
select * from stuinfo left join stumarks using(sid);
练习
1.显⽰地区和每个地区参加数学考试的⼈数，并且按⼈数降序排列
select a.city,count(b.math) c from stuinfo a left join stumarks b using(sid) group by city order by c desc;
2..显⽰男⽣⼈数和⼥⽣⼈数
select a.sex,count(a.sex) from stuinfo a group by sex;
select sex,count(sex) from stuinfo where sex=1union select sex,count(sex) from stuinfo where sex=2;
select sum(sex=1) 男,sum(sex=2) ⼥from stuinfo;
3.显⽰每个地区的男⽣⼈数,⼥⽣⼈数，总⼈数
select city 城市,count(sex) 总⼈数,sum(sex='male') 男, sum(sex='female') ⼥from stuinfo group by city;
3.⼦查询
什么叫⼦查询?
查询语句中还有查询语句，外⾯的查询称为⽗查询,⾥⾯的叫⼦查询.
⼦查询为⽗查询提供查询条件.
例题：查询数学成绩是80分的学⽣
#普通的查询
select * from stumarks left join stuinfo using(sid) where math=80;
#⼦查询
select * from stuinfo where sid=(select sid from stumarks where math = 80);
#如果使⽤等于，那就必须确保⼦查询查到的结果只有⼀个
#⼦查询的结果只能是单⼀的字段
例题：查数学最⾼分的学⽣
#普通的查询
select a.sid,a.sname,a.sex,a.age,a.city,max(b.math) from stuinfo a left join stumarks b using(sid);#问题
#⼦查询
sqlite中文版安卓select * from stuinfo where sid = (select sid from stumarks where math = (select max(math) from stumarks));
(1)in|not in
如果⼦查询中返回了多条记录，使⽤ = 会发⽣错误，那么就必须要⽤in
例题：查询数学成绩不及格的学⽣
select * from stuinfo where sid in (select sid from stumarks where math in (select math from stumark
s having math<60));
#1.数学成绩不及格 <60 60种可能
select math from stumarks having math<60; #查询到的有可能是⼀个也有可能是多个(58,59)
#2.这些成绩是那些学⽣的(sid)
select sid from stumarks where math in (58,59); #(3,4)
#3.根据学号查学⽣的信息
select * from stuinfo where sid in (3,4);
例题：查询没有参加考试的学⽣
select * from stuinfo where sid in (select sid from stumarks where math is null);
(2)some | any|all
some和any是⼀样的，表⽰⼀些，类似与 in
all 表⽰全部的元素
#some
select * from stuinfo where sid =some (select sid from stumarks where math<60);mysql语句多表查询
select *from stuinfo where sid =any (select sid from stumarks where math<60);
#条件都满⾜
爱奇艺linux版官方版select * from stuinfo where sid =all (select sid from stumarks where math<60);
查询数学90分以上的学⽣
select * from stuinfo where sid not in (select sid from stumarks where math<90);
select * from stuinfo where sid != some (select sid from stumarks where math<90);
#some = in !=some不等与 not in
#!=all 等同 not in
spring boot注解responsbody的作用查询数学90以下的学⽣
select * from stuinfo where sid =some (select sid from stumarks where math in(select math from stumarks having math<90));
(3)exists | not exists
如果有⼈math超过100分，就显⽰所有学⽣的信息
select * from stuinfo where exists(select * from stumarks where math>=100);
#成绩未达到100分就显⽰
select * from stuinfo where not exists(select * from stumarks where math>=100);
(4)⼦查询分类
1.标量⼦查询:⼦查询返回的值只有⼀个 sid =
2.列⼦查询:⼦查询返回⼀个列表 sid in (3,4)
3.⾏⼦查询：查询多个条件由多个字段组成的⾏形成:
select * from stumarks where (sid,ch,math) in (select * from stumarks where math=100);
#例如python中
sid,ch,math = ((3,4),(88,99),(100,88))
出语⽂成绩最⾼的男⽣和⼥⽣:
select * from stuinfo where sid in (select sid from stumarks where ch in ( select max(ch) from stuinfo left join stumarks using(sid) group by sex));
4.视图
1.视图是⼀张虚拟的表，他表⽰⼀张表的部分数据或多张表的综合数据,
视图的结构是建⽴在表的基础上
2.视图中没有数据，只有表结构，视图中的数据在基表中获取
3.⼀张表可以创建多个视图,⼀个视图可以引⽤多张表
(1)创建视图
create [or replace] view`视图名`
as
sql语句
create view stu_view_1
js 前端ui框架as
select sid,sname,age,sex,city,ch,math from stuinfo left join stumarks using(sid);
#视图创建完毕后，会在对应的⽂件夹中保存⼀个.frm的⽂件,⼦⽂件是视图的结构
(2)查询
select * from`stu_view_1`;
(3)修改视图
alter view stu_view_1
as
select * from stuinfo;
(4)查看视图的信息
show create view stu_view_1\G
mysql> show create view stu_view_1\G
*************************** 1.row ***************************
View: stu_view_1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`SQL SECURITY DEFINER VIEW`stu_view_1`AS select`stuinfo`.`sid`AS`sid`,`stuinfo`.
`sname`AS`sname`,`stuinfo`.`sex`AS`sex`,`stuinfo`.`age`AS`age`,`stuinfo`.`
city`AS`city`from`stuinfo`
character_set_client: gbk
collation_connection: gbk_chinese_ci
1row in set (0.00 sec)
(5)查看视图的结构
desc stu_view_1;
mysql> desc stu_view_1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sid | int(11) | NO | | 0 | |
table中文谐音怎么读| sname | varchar(255) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| city | varchar(64) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
(6)查视图;
show tables;#它也可以查看视图
select table_name from information_schema.views;
show table status where comment='view'\G;
(7)删除视图
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论