CREATE TABLE class (
id AUTO_INCREMENT,
name ()
);
student (
id AUTO_INCREMENT,
name (),
sex (),
class_id ,
(class_id) class(id)
);
class(name) (),(),();
student(name, sex, class_id) (,,),(,,),(,,);
INT
PRIMARY KEY
VARCHAR
20
CREATE TABLE
INT
PRIMARY KEY
VARCHAR
20
CHAR
1
INT
FOREIGN KEY
REFERENCES
INSERT INTO
VALUES
'java113'
'java78'
'C++110'
INSERT INTO
VALUES
'张三'
'男'
1
'李四'
'女'
1
'王五'
'男'
2
如果我们用多表查询(笛卡尔积),则会出现下面这种情况:
SELECT*FROM student, class;
这两个表的联合查询,其实是笛卡尔积,因此其查询的列数就是两个表的列数之和,行数则是两个表行数的乘积。如果表内容多,其数据就会变得非常庞大,因此上面这样我们联合查询是不对的。正确的做法是班级 id 要一样才是正确的结果,剩余都是错误的结果。
为了让其按照我们的要求查询,我们需要加上 WHERE 条件,当其class 表中的 id 和 student 表中的 class_id 相等时候才可以。
-- 此时只需要加上 where 条件即可SELECT*FROM student, class WHERE student.class_id = class.id;
-- 查询其对应列的信息SELECT student.id, student.name, sex, student.class_id, class.id, class.name
FROM student, class
WHERE student.class_id = class.id;
-- 查询张三的信息SELECT student.id, student.name, student.sex, student.class_id, class.id, class.name
FROM student, class
WHERE student.class_id = class.id AND student.name ='张三';
如果我们不添加其对应的表,并且还是相同的列名,此时就会出现此时的 id 对应的列是模棱两可的,就是我们不知道这个列是来自哪个表,因此就会出错。
联合查询步骤
确定查询表 --> 进行笛卡尔积
确定连接条件
加入查询条件
精简查询列,也可以给表取别名
内连接
上面我们只是简单的介绍了可以联合查询,下面我们就来详细介绍一些联合查询的方式。
-- 方式一SELECT 字段 FROM 表 1 别名 1, 表 2 别名 2WHERE 连接条件 AND 其他条件;
-- 方式二SELECT 字段 FROM 表 1 别名 1 [INNER] JOIN 表 2 别名 2ON 连接条件 WHERE 其他条件;
-- 此时的别名不是必须要取的,并且此时取别名的 as 是可以省略的
此时我们创建四个表来举例:
-- 创建课程表CREATE TABLE course (
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 班级表CREATE TABLE class (
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 学生表CREATE TABLE student (
id INTPRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
class_id INT,
FOREIGN KEY (class_id) REFERENCES class(id)
);
-- 分数表CREATE TABLE score (
student_id INT,
course_id INT,
score INT,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
-- 课程表INSERT INTO course (name) VALUES('Java'),('C++'),('MySQL'),('操作系统'),('计算机网络'),('数据结构');
-- 班级表INSERT INTO class(name) VALUES('Java001 班'),('C++001 班'),('前端 001 班');
-- 学生表INSERT INTO student (name, class_id) VALUES('唐三藏',1),('孙悟空',1),('猪悟能',1),('沙悟净',1),('宋江',2),('武松',2),('李逵',2),('不想毕业',2);
-- 成绩表INSERT INTO score (score, student_id, course_id) VALUES
(70.5,1,1),(98.5,1,3),(33,1,5),(98,1,6),(60,2,1),(59.5,2,5),(33,3,1),(68,3,3),(99,3,5),(67,4,1),(23,4,3),(56,4,5),(72,4,6),(81,5,1),(37,5,5),(56,6,2),(43,6,4),(79,6,6),(80,7,2),(92,7,6);
查询唐三藏的所有学科成绩:
-- 我们用上面两种方式都可以SELECT s.name, sc.score FROM student AS s, score AS sc WHERE s.id = sc.student_id AND s.name ='唐三藏';
SELECT s.name, sc.score FROM student AS s JOIN score sc ON s.id = sc.student_id AND s.name ='唐三藏';
例如查询所有学生的总成绩:
SELECT s.name, SUM(sc.score) FROM student AS s, score AS sc WHERE s.id = sc.student_id GROUPBY s.id;
-- 要进行分组,因为其求和肯定要是一个学生的成绩
查询所有同学的详细信息包括考试成绩:
SELECT s.id, s.name AS 姓名,c.name AS 课程,sc.score AS 分数
FROM course c, student s, score sc
WHERE s.id = sc.student_id AND c.id = sc.course_id
ORDERBY s.id;
SELECT s.id, s.name, sc.*FROM student s LEFTJOIN score sc ON s.id = sc.student_id;
# 因为未考试的成绩为空,因此其可以筛选掉考试的同学
SELECT s.id, s.name, s.class_id FROM student s LEFTJOIN score sc ON s.id = sc.student_id WHERE sc.score ISNULL;
右外连接
查询没有学生的班级:
SELECT*FROM student s RIGHTJOIN class c ON s.class_id = c.id;
# 此时没有学生班级其学生的 id 肯定为空
SELECT*FROM student s RIGHTJOIN class c ON s.class_id = c.id WHERE s.id ISNULL;
自连接
自连接就是自己对自己求笛卡尔积,在查询的时候可以使用 WHERE 条件对结果进行过滤,或者可以使用自己对自己表的数据进行比较,但是自连接的时候要对表起别名。
SELECT*FROM score, score;
此时会报错,因为使用两个名字相同的表或者是没有取别名。
SELECT*FROM score AS s1, score AS s2;
查询 MySQL 成绩大于 Java 成绩大的学生:
-- 分开写
# 先获取其对应课程 id
SELECT id, name FROM course WHERE name ='MySQL'OR name ='java';
SELECT*FROM score s1, score s2
WHERE s1.student_id = s2.student_id
AND s1.course_id =3AND s2.course_id =1-- 学生 id 相同AND s1.score > s2.score; -- MySQL 成绩>java-- 放在一起写SELECT s1.*, s2.*FROM score s1, score s2, course c1, course c2
WHERE s1.student_id = s2.student_id
AND s1.course_id = c1.id AND s2.course_id = c2.id
AND c1.NAME ='MySQL'AND c2.NAME ='java'AND s1.score > s2.score;
此时如果想看到其所有信息,就要将其 student、class 和 score 都要进行笛卡尔积:
SELECT stu.name AS 姓名,c.name AS 班级,s1.score AS MySQL, s2.score AS java
FROM score s1, score s2, course c1, course c2, student stu, class c
WHERE s1.student_id = s2.student_id
AND s1.course_id = c1.id AND s2.course_id = c2.id
AND stu.id = s1.student_id AND stu.class_id = c.id
AND c1.NAME ='MySQL'AND c2.NAME ='java'AND s1.score > s2.score;
子查询
有时候我们进行查询时候的 WHERE 条件可能还需要进行查询,就是一个查询的结果作为另一个查询的条件。
SELECT*FROM table1 WHERE col_name1 {=|IN} (SELECT col_name1 FROM table2 WHERE col_name2 {=|IN} [(SELECT...)]...)
单行子查询
WHERE 条件里的查询只返回一行数据。
例如查询姓名为 '不想毕业'的同学,因此要先查找其班级 id,后面再根据其班级 id 进行查找。
SELECT*FROM student WHERE class_id = (SELECT class_id FROM student WHERE name ='不想毕业');
当然这个我们还是可以分开写的:
SELECT class_id FROM student WHERE name ='不想毕业';
SELECT*FROM student WHERE class_id =2;
多列子查询
返回多行数据,因此我们要是用 [NOT] IN 关键字。
查询课程 MySQL 和 Java 的成绩:
SELECT*FROM score WHERE score.course_id IN (SELECT id FROM course WHERE name ='MySQL'OR name ='java');
当然也可以查询除了 MySQL 和 Java 的成绩:
SELECT*FROM score WHERE score.course_id NOTIN (SELECT id FROM course WHERE name ='MySQL'OR name ='java');
多列子查询
多列子查询就是返回的是多个列的数据。
例如:查询重复录入的分数:
INSERT INTO score(score, student_id, course_id) VALUES(70.5,1,1),(98.5,1,3),(60,2,1);
# 查询重复成绩
SELECT*FROM score
WHERE (score, student_id, course_id) IN (SELECT score, student_id, course_id FROM score GROUPBY score, student_id, course_id HAVINGCOUNT(*) >1);
# 先求平均分
SELECTAVG(sc.score) FROM score sc, class c, student s
WHERE c.id = s.class_id AND sc.student_id = s.id AND c.name ='java001 班';
SELECT*FROM score s, (SELECTAVG(sc.score) AS score FROM score sc, class c, student s WHERE c.id = s.class_id AND sc.student_id = s.id AND c.NAME ='java001 班') tmp
WHERE s.score > tmp.score;