本文实例讲述了Mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询
本文实例讲述了Mysql连接查询、联合查询、子查询原理与用法。分享给大家供大家参考,具体如下:
首发日期:2018-04-11
-- 实验表结构
create table student(
id int,
name varchar(15),
gender varchar(15),
cid int
);
create table class(
cid int,
cname varchar(15)
);
drop table student,class;
-- 实验表数据:
insert into student values(1,"lilei","male",1),(2,"hanmeimei","male",2),(3,"jack","male",1),(4,"alice","female",4); --这里特意创建了一个class中没有的4
insert into class values(1,"linux"),(2,"python"),(3,"java"),(5,"HTML5");--这里特意创建了一个student中没有的5
select * from student;
select * from class;
-- 内连接
-- select * from student inner join class; --结果与交叉连接相同
select * from student join class on student.cid = class.cid;
select * from student inner join class on student.cid = class.cid;
select * from student left join class on student.cid = class.cid;
select * from student right join class on student.cid = class.cid;
select * from student natural join class;
select * from student natural left join class;
select * from student cross join class;
select * from student,class;
select * from student inner join class on student.cid = class.cid;-- 原本结果
select id,name,gender,c.cid,cname from student as s inner join class as c on s.cid = c.cid;-- 使用表别名
select name,gender from student
union
select * from class;
-- 因为class就两个字段,所以第一个只选出两个字段
-- 这是一个无意义的例子。仅为举例使用
select cid,cname from (select * from class where cname="python") as c;
select * from student where cid=(select cid from class where cname="python");
-- 这是一个如果学生没有选择cid=1的课,那么不输出对应课程信息的例子
select * from class where exists(select * from student where cid=1) and cid=1;
更多关于mysql相关内容感兴趣的读者可查看本站专题:《Mysql查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。
--结束END--
本文标题: mysql连接查询、联合查询、子查询原理与用法实例详解
本文链接: https://www.lsjlt.com/news/9554.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0