MySQL多表数据查询(DQL)
数据准备:
/* ------------------------------------创建班级表------------------------------------ */ CREATE TABLE classes( id INT PRIMARY KEY, className VARCHAR(10), classDesc VARCHAR(10) ); INSERT INTO classes VALUES(1,'高一(1)班','尖子班'); INSERT INTO classes VALUES(2,'高一(2)班','优秀班'); INSERT INTO classes VALUES(3,'高一(3)班','普通班'); INSERT INTO classes VALUES(4,'高一(4)班','逃课班'); /* ------------------------------------创建课程表------------------------------------ */ CREATE TABLE courses( cid VARCHAR(10) PRIMARY KEY, cname VARCHAR(10), teacher VARCHAR(10) ); INSERT INTO courses VALUES ('ca','政治','Tom'); INSERT INTO courses VALUES ('cb','历史','Smith'); INSERT INTO courses VALUES ('cc','地理','Lida'); INSERT INTO courses VALUES ('cd','物理','David'); INSERT INTO courses VALUES ('ce','化学','Alan'); INSERT INTO courses VALUES ('cf','生物','Jack'); /* ------------------------------------创建学生表------------------------------------ */ CREATE TABLE students( sid INT PRIMARY KEY, sname VARCHAR(10), age INT, class INT, CONSTRAINT students_fk FOREIGN KEY (class) REFERENCES classes(id) ); INSERT INTO students VALUES (1001,'张三',18,4); INSERT INTO students VALUES (1002,'李四',20,3); INSERT INTO students VALUES (1003,'王五',25,1); INSERT INTO students VALUES (1004,'孙六',21,1); INSERT INTO students VALUES (1005,'赵七',23,3); /* ------------------------------------创建选课表------------------------------------ */ CREATE TABLE electives( id INT PRIMARY KEY AUTO_INCREMENT, student INT, course VARCHAR(10), CONSTRAINT electives_fk1 FOREIGN KEY (student) REFERENCES students(sid), CONSTRAINT electives_fk2 FOREIGN KEY (course) REFERENCES courses(cid) ); INSERT INTO electives(student,course) VALUES (1001,'cc'),(1003,'cb'),(1005,'ca'); INSERT INTO electives(student,course) VALUES (1003,'ce'),(1001,'cf'),(1005,'cd'); INSERT INTO electives(student,course) VALUES (1005,'cc'),(1004,'ce'),(1003,'ca'); INSERT INTO electives(student,course) VALUES (1002,'cd'),(1004,'ca'),(1001,'cb'); INSERT INTO electives(student,course) VALUES (1002,'cb'),(1004,'cd'),(1002,'cf');
架构设计图:
一、内连接查询
1.1 多对一关系查询
Example01: 查询全部学生及班级详情
mysql> SELECT s.sname,s.age,c.className,c.classDesc FROM students s,classes c WHERE s.class = c.id; /*隐式内连接查询*/ +--------+------+--------------+-----------+ | sname | age | className | classDesc | +--------+------+--------------+-----------+ | 张三 | 18 | 高一(4)班 | 逃课班 | | 李四 | 20 | 高一(3)班 | 普通班 | | 王五 | 25 | 高一(1)班 | 尖子班 | | 孙六 | 21 | 高一(1)班 | 尖子班 | | 赵七 | 23 | 高一(3)班 | 普通班 | +--------+------+--------------+-----------+ 5 rows in set (0.00 sec) mysql> SELECT s.sname,s.age,c.className,c.classDesc FROM students s INNER JOIN classes c ON s.class = c.id; /*显式内连接查询*/ +--------+------+--------------+-----------+ | sname | age | className | classDesc | +--------+------+--------------+-----------+ | 张三 | 18 | 高一(4)班 | 逃课班 | | 李四 | 20 | 高一(3)班 | 普通班 | | 王五 | 25 | 高一(1)班 | 尖子班 | | 孙六 | 21 | 高一(1)班 | 尖子班 | | 赵七 | 23 | 高一(3)班 | 普通班 | +--------+------+--------------+-----------+ 5 rows in set (0.00 sec)
注意:显式内连接查询条件判断用on。
Example02:查询尖子班与普通班的学生平均年龄
mysql> SELECT c.classDesc,AVG(s.age) FROM students s,classes c WHERE s.class = c .id GROUP BY c.classDesc; +-----------+------------+ | classDesc | AVG(s.age) | +-----------+------------+ | 尖子班 | 23.0000 | | 普通班 | 21.5000 | | 逃课班 | 18.0000 | +-----------+------------+ 3 rows in set (0.00 sec)
1.2 多对多关系查询
Example03:查询张三的全部选修学科
mysql> SELECT s.sname,c.cname FROM electives e,students s,courses c WHERE e.student = s.sid AND e.course = c.cid AND s.sname = "张三"; +--------+--------+ | sname | cname | +--------+--------+ | 张三 | 地理 | | 张三 | 生物 | | 张三 | 历史 | +--------+--------+ 3 rows in set (0.02 sec)
Example04:查询物理老师David的全部学生
mysql> SELECT c.teacher,s.sname FROM electives e,students s,courses c WHERE e.student = s.sid AND e.course = c.cid AND c.teacher = "David"; +---------+--------+ | teacher | sname | +---------+--------+ | David | 赵七 | | David | 李四 | | David | 孙六 | +---------+--------+ 3 rows in set (0.03 sec)
Example05:查询各个老师的学生个数
mysql> SELECT c.teacher,COUNT(s.sid) FROM electives e,students s,courses c WHERE e.student = s.sid AND e.course = c.cid GROUP BY c.teacher; +---------+--------------+ | teacher | COUNT(s.sid) | +---------+--------------+ | Alan | 2 | | David | 3 | | Jack | 2 | | Lida | 2 | | Smith | 3 | | Tom | 3 | +---------+--------------+ 6 rows in set (0.02 sec)
二、外连接查询
2.1 左外连接查询
Example06:查询全部学生及班级详情(保留左表的全部记录)
mysql> SELECT s.sname,s.age,c.className,c.classDesc FROM students s LEFT JOIN classes c ON s.class = c.id; +--------+------+--------------+-----------+ | sname | age | className | classDesc | +--------+------+--------------+-----------+ | 王五 | 25 | 高一(1)班 | 尖子班 | | 孙六 | 21 | 高一(1)班 | 尖子班 | | 李四 | 20 | 高一(3)班 | 普通班 | | 赵七 | 23 | 高一(3)班 | 普通班 | | 张三 | 18 | 高一(4)班 | 逃课班 | +--------+------+--------------+-----------+ 5 rows in set (0.00 sec)
2.2 右外连接查询(保留右表的全部记录)
Example07:查询全部学生及班级详情
mysql> SELECT s.sname,s.age,c.className,c.classDesc FROM students s RIGHT JOIN classes c ON s.class = c.id; +--------+------+--------------+-----------+ | sname | age | className | classDesc | +--------+------+--------------+-----------+ | 张三 | 18 | 高一(4)班 | 逃课班 | | 李四 | 20 | 高一(3)班 | 普通班 | | 王五 | 25 | 高一(1)班 | 尖子班 | | 孙六 | 21 | 高一(1)班 | 尖子班 | | 赵七 | 23 | 高一(3)班 | 普通班 | | NULL | NULL | 高一(2)班 | 优秀班 | +--------+------+--------------+-----------+ 6 rows in set (0.00 sec)
注意:外连接查询条件判断用on。
三、子查询
3.1 标量子查询(子查询返回的结果是一行一列的数据)
Example08:查询尖子班的学生详情
mysql> SELECT * from students where class = (SELECT id from classes where classDesc = '尖子班'); +------+--------+------+-------+ | sid | sname | age | class | +------+--------+------+-------+ | 1003 | 王五 | 25 | 1 | | 1004 | 孙六 | 21 | 1 | +------+--------+------+-------+ 2 rows in set (0.02 sec) mysql> select s.* from students s,classes c where s.class = c.id and c.classDesc= '尖子班'; /*内连接查询实现*/ +------+--------+------+-------+ | sid | sname | age | class | +------+--------+------+-------+ | 1003 | 王五 | 25 | 1 | | 1004 | 孙六 | 21 | 1 | +------+--------+------+-------+ 2 rows in set (0.00 sec)
3.2 列子查询(子查询返回的结果是一列的数据)
Example09:查询普通班与逃课班的学生详情
mysql> SELECT * FROM students WHERE class IN (SELECT id FROM classes WHERE classDesc IN ('普通班','逃课班')); +------+--------+------+-------+ | sid | sname | age | class | +------+--------+------+-------+ | 1001 | 张三 | 18 | 4 | | 1002 | 李四 | 20 | 3 | | 1005 | 赵七 | 23 | 3 | +------+--------+------+-------+ 3 rows in set (0.00 sec) mysql> SELECT s.* FROM students s,classes c WHERE s.class = c.id AND c.classDesc IN ('普通班','逃课班'); /*内连接查询实现*/ +------+--------+------+-------+ | sid | sname | age | class | +------+--------+------+-------+ | 1001 | 张三 | 18 | 4 | | 1002 | 李四 | 20 | 3 | | 1005 | 赵七 | 23 | 3 | +------+--------+------+-------+ 3 rows in set (0.00 sec)
3.3 表子查询(子查询返回的结果是一个表的数据)
Example10:查询年龄大于21的学生的选修学科详情
mysql> SELECT s.sname,s.age,c.cname FROM electives e,(SELECT * FROM students WHERE age > 21) s,courses c WHERE e.student = s.sid AND e.course = c.cid; /*先条件 过滤再内连接*/ +--------+------+--------+ | sname | age | cname | +--------+------+--------+ | 王五 | 25 | 历史 | | 王五 | 25 | 化学 | | 王五 | 25 | 政治 | | 赵七 | 23 | 政治 | | 赵七 | 23 | 物理 | | 赵七 | 23 | 地理 | +--------+------+--------+ 6 rows in set (0.00 sec) mysql> SELECT s.sname,s.age,c.cname FROM electives e,students s,courses c WHERE e.student = s.sid AND e.course = c.cid AND s.age > 21; /*先内连接再条件过滤*/ +--------+------+--------+ | sname | age | cname | +--------+------+--------+ | 王五 | 25 | 历史 | | 王五 | 25 | 化学 | | 王五 | 25 | 政治 | | 赵七 | 23 | 政治 | | 赵七 | 23 | 物理 | | 赵七 | 23 | 地理 | +--------+------+--------+ 6 rows in set (0.00 sec)
注意:表子查询结果必须给别名才能被调用。
代码改变一切!