内连接
简单的说就是,连接两张表,把两张表变成一张表,再根据选择条件显示相应的内容
select * from tablename1 inner join tablename2 on condition
mysql> create database school; #创建数据库school
mysql> use school; #选择数据库school
mysql> CREATE TABLE class (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`teacher` varchar(64) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
); #创建班级表 class
mysql> insert into class values(101, '萌新一班', 'Martin'),(102, '萌新二班', 'Rock'),(103, '萌新三班', 'Janny'); #创建成绩表 grade
mysql> CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT UNIQUE,
`name` varchar(64) DEFAULT NULL,
`class_id` int DEFAULT NULL,
`sex` enum('F','M') DEFAULT NULL
);
mysql> insert into student values(1,'小花',101,'M'),(2,'小红',102, 'F'),(3,'小军',102,'F'),(4,'小白',101,'F'); #插入多条记录
mysql> select * from class inner join student on class.id = student.class_id; #查询class 表和student 表中班级字段相同的记录并进行内连接
mysql> select * from class as a inner join student as b where a.id = b.class_id; #同上,使用别名查询
外连接
外连接会返回至少一个表的所有值,不仅仅是相匹配的,即使不匹配,也会返回,但是显示空值
外连接的outter可省略,外连接也分为左外连接和右外连接
select * from tablename1 LEFT | RIGHT outer join tablename2 on condition
左外连接
外连接查询中的左外连接,就是指新关系中执行匹配条件时,以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行,而不仅仅是连接列所匹配的行,如果左表的某行在右表中没有匹配行,则在相关联的结果行中,右表的所有选择列表均为空值。
mysql> use school; #选择数据库school
mysql> select * from student as a left join class as b on a.class_id = b.id; #左连接查询所有学生对应的班级信息
mysql> select * from class as a left join student as b on a.id = b.class_id; #左连接查询所有班级的学员信息
右外连接
外连接查询中的右外连接在新关系中执行匹配条件时,以关键字RIGHT JOIN右边的表为参考表,如果右表的某行在左表中没有匹配行,左表将返回空值。
mysql> use school; #选择数据库school
mysql> select * from student as a right join class as b on a.class_id = b.id; #右连接查询所有班级对应的学员信息
mysql> select * from class as a right join student as b on a.id = b.class_id; #右连接查询所有学员对应的班级信息
合并查询
在MySQL中通过关键字UNION来实现合并操作,即可以通过其将多个SELECT语句的查询结果合并在一起,组成新的关系。
具体语法形式如下:
SELECT field1,field2,…,fieldn FROM tablename1
UNION | UNION ALL
SELECT field1,field2,…,fieldn FROM tablename2
UNION | UNION ALL SELECT field1,field2,…,fieldn ;
注意: 多个选择语句select 的列数相同就可以合并,union和union all的主要区别是union all是把结果集直接合并在一起,而union 是将union all后的结果再执行一次distinct,去除重复的记录后的结果。
mysql> use school; #选择数据库school
mysql> select teacher from class union all select name from student; #查询班级表所有老师和学生表中所有学生姓名
mysql> select teacher as people from class union all select name as people from student; #同上,使用别名查询
mysql> select teacher,id from class union all select name,class_id from student; #查询班级表所有(老师、班级ID)和学生表中所有学生(姓名、班级ID)
子查询
子查询就是一个查询语句中嵌套了另一个查询语句,通关子查询可以实现多表查询
比较运算子查询
子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中,<>与!=是等价的。比较运算符在子查询中使用得非常广泛,如查询分数、年龄、价格和收入等。
mysql> use school; #选择数据库school
mysql> select teacher from class where id = (select class_id from student where name='小花'); #查询“小花”所在班级班主任的姓名
注意:使用比较运算符时,select 子句获得的记录数不能大于1条!!!
带关键字IN的子查询
NOT IN的用法与IN相同。
例如:查询student 表中“小花”所在班级班主任的名字。SQL语句如下:
mysql> use school; #选择数据库school
mysql> select teacher from class where id in (select class_id from student where name='小花'); #查询student 表中“小花”所在班级班主任的名字
mysql> select teacher from class where id in (select class_id from student where name like '小%'); #查询姓名以“小”字开头的学生所在班级班主任的姓名
带关键字EXISTS的子查询
关键字EXISTS表示存在,后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行;如果至少返回一行,那么EXISTS的结果为true,此时外层语句将进行查询;如果子查询没有返回任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询。
例如:如果102班存在学生记录,就查询102班的班级信息的记录。SQL示例语句如下:
mysql> use school; #选择数据库school
mysql> select * from class where id=102 and exists (select * from student where class_id=102); #如果102班存在学生记录,就查询102班的班级信息
带关键字ANY的子查询
关键字ANY表示满足其中任一条件。使用关键ANY时,只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。
例如,需要查询哪些学生可以获取奖学金,那么首先要有一张奖学金表,从表中查询出各种奖学金要求的最低分,只要一个同学的乘积大于等于不同奖学金最低分的任何一个,这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如,“>ANY”表示大于任何一个值,“=ANY”表示等于任何一个值。
例如:查询数据库school的表student中哪些学生可以获得奖学金。学生的成绩达到其中任何一项奖学金规定的分数即可,SQL语句示例如下:
mysql> use school; #选择数据库school
mysql> create table scholarship (score int, level varchar(64));
mysql> insert into scholarship values(240, '二等奖'),(257,'一等奖');
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ANY (select score from scholarship); #查询能获得奖学金的学院记录
带关键字ALL的子查询
关键字ALL表示满足所有条件。使用关键字ALL时,只有满足内层查询语句返回的所有结果才可以执行外层查询语句。
例如,需要查询哪些同学能够获得一等奖学金,首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高,只有当成绩高于所有奖学金最低分时,这个同学才可能获得一等奖学金。
关键字ALL也经常与比较运算符一起使用。例如,“>ALL”表示大于所有值,“<ALL”表示小于所有值。
例如:查询数据库school的表student中哪些学生可以获得一等奖学金,即学生的总成绩要达到一等奖学金规定的分数,而一等奖学金是最高奖学金。SQL语句示例如下:
mysql> use school; #选择数据库school
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) >= ALL (select score from scholarship); #查询能获得一等奖学金的同学记录
mysql> select st.id, st.name, st.math+st.chinese+st.english total from grade st where (math+chinese+english) < ALL (select score from scholarship); #查询不能获得奖学金的同学记录