心得：想想你的处境…你所拥有的只是暂时的。
数据库多表查询：
当我们存储数据时候，往往会把数据存储在多张表当中，我们如何从多张表当中取出我们需要的数据，往往是通过这些数据的关联性来建立联系。
创建两个表，来看看多表操作：
mysql> #建表
mysql> create table department(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> create table employee(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') not null default 'male',
-> age int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> #插入数据
mysql> insert into department values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into employee(name,sex,age,dep_id) values
-> ('egon','male',18,200),
-> ('alex','female',48,201),
-> ('wupeiqi','male',38,201),
-> ('yuanhao','female',28,202),
-> ('liwenzhou','male',18,200),
-> ('jingliyang','female',18,204)
-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql>
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)同时查询两张表，会出现什么状况呢，生成的新表会是一个笛卡尔积：
笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积.
select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+1. where查询
select * from employee,department where employee.dep_id=department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+#2. 多表连接查询语法(重点)
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
2.1 内连接查询
select * from employee inner join department on employee.dep_id=department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+2.2 左连接查询，保留左表不相关的数据。
select * from employee left join department on employee.dep_id=department.id;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
+----+------------+--------+------+--------+------+--------------+2.3 右连接查询，保留右表不相关的数据。
select * from employee right join department on employee.dep_id=department.id;
+------+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-----------+--------+------+--------+------+--------------+2.4 全连接查询，用union连接左右连接查询的结果
select * from employee left join department on employee.dep_id=department.id union
-> select * from employee right join department on employee.dep_id=department.id;
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+例子1.以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department on employee.dep_id=department.id where age>25;
+---------+--------------+
| name | name |
+---------+--------------+
| alex | 人力资源 |
| wupeiqi | 人力资源 |
| yuanhao | 销售 |
+---------+--------------+例子2 以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.name,department.name from employee inner join department on employee.dep_id=department.id where age>25 order by age asc;
+---------+--------------+
| name | name |
+---------+--------------+
| yuanhao | 销售 |
| wupeiqi | 人力资源 |
| alex | 人力资源 |
+---------+--------------+3. 子查询，将查询到的结果当作条件进一步筛选原表，或者其他表
3.1 #查询平均年龄在25岁以上的部门名
先从employee中找出大于25岁的部门id号，然后存department中筛选出存在这些id的部门信息
select * from department where id in(select dep_id from employee group by dep_id having avg(age)>25);
+------+--------------+
| id | name |
+------+--------------+
| 201 | 人力资源 |
| 202 | 销售 |
+------+--------------+3.2 查看技术部员工信息
先从department 中找出技术对应的id，然后在employee中找出id对应的信息
select * from employee where dep_id=(select id from department where name="技术");
+----+-----------+------+------+--------+
| id | name | sex | age | dep_id |
+----+-----------+------+------+--------+
| 1 | egon | male | 18 | 200 |
| 5 | liwenzhou | male | 18 | 200 |
+----+-----------+------+------+--------+3.3查询没有员工的部门名
筛选出存在人的部门编号，再用not in 语句来筛选部门信息
select * from department where id not in (select distinct dep_id from employee);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+3.4 查询大于所有人平均年龄的员工名与年龄（比较运算符）
得先得到平均年龄，然后通过where语句来定位。
select avg(age) from employee;
+----------+
| avg(age) |
+----------+
| 28.0000 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select * from employee where age >(select avg(age) from employee);
+----+---------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+---------+--------+------+--------+
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
+----+---------+--------+------+--------+3.5 EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
exist条件成立：
select * from employee where exists (select id from employee where dep_id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |exists条件不成立：
select * from employee where exists (select id from employee where dep_id=20);
Empty set (0.00 sec)练习题：
1.查询所有的课程的名称以及对应的任课老师姓名
select cname,tname from course inner join teacher on course.teacher_id=teacher.tid;
+--------+-----------------+
| cname | tname |
+--------+-----------------+
| 生物 | 张磊老师 |
| 物理 | 李平老师 |
| 体育 | 刘海燕老师 |
| 美术 | 李平老师 |
+--------+-----------------+
4 rows in set (0.00 sec)2.查看男女各有多少人
select gender,count(sid) from student group by gender;
+--------+------------+
| gender | count(sid) |
+--------+------------+
| 女 | 6 |
| 男 | 10 |
+--------+------------+3.查看物理分数为100的学生姓名
1.>先找到物理课程对应的id
2.>筛选出物理课并且考100的学生id
3.>通过学生id在去学生表中拿到对应的姓名
select sname from student where sid in (select student_id from score where course_id=( select cid from course where cname="物理") and num=100);
+--------+
| sname |
+--------+
| 张四 |
| 铁锤 |
| 李三 |
+--------+
3 rows in set (0.00 sec)4、查询平均成绩大于八十分的同学的姓名和平均成绩
#拿到平局分
select avg(num) from score;
+----------+
| avg(num) |
+----------+
| 67.0851 |
+----------+
1 row in set (0.00 sec)
#拿到大于平均分对应的学生id
select distinct student_id from score where num>(select avg(num) from score);
+------------+
| student_id |
+------------+
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+------------+
12 rows in set (0.01 sec)
#拿到对应的姓名
select * from student where sid in (select distinct student_id from score where num>(select avg(num) from score));
+-----+--------+----------+--------+
| sid | gender | class_id | sname |
+-----+--------+----------+--------+
| 2 | 女 | 1 | 钢蛋 |
| 3 | 男 | 1 | 张三 |
| 4 | 男 | 1 | 张一 |
| 5 | 女 | 1 | 张二 |
| 6 | 男 | 1 | 张四 |
| 7 | 女 | 2 | 铁锤 |
| 8 | 男 | 2 | 李三 |
| 9 | 男 | 2 | 李一 |
| 10 | 女 | 2 | 李二 |
| 11 | 男 | 2 | 李四 |
| 12 | 女 | 3 | 如花 |
| 13 | 男 | 3 | 刘三 |
+-----+--------+----------+--------+5、查询所有学生的学号，姓名，选课数，总成绩
#1 找出每个学生选的课程数目，和总成绩
select student_id,count(sid),sum(num) from score group by student_id;
+------------+------------+----------+
| student_id | count(sid) | sum(num) |
+------------+------------+----------+
| 1 | 3 | 85 |
| 2 | 3 | 175 |
| 3 | 4 | 329 |
| 4 | 4 | 257 |
| 5 | 4 | 257 |
| 6 | 4 | 276 |
| 7 | 4 | 264 |
| 8 | 4 | 264 |
| 9 | 4 | 268 |
| 10 | 4 | 297 |
| 11 | 4 | 297 |
| 12 | 4 | 297 |
| 13 | 1 | 87 |
+------------+------------+----------+
13 rows in set (0.00 sec)
#2 用学生表和新表进行内连接
select * from student as t1 inner join (select student_id,count(sid),sum(num) from score group by student_id) as t2 on t1.sid=t2.student_id;
+-----+--------+----------+--------+------------+------------+----------+
| sid | gender | class_id | sname | student_id | count(sid) | sum(num) |
+-----+--------+----------+--------+------------+------------+----------+
| 1 | 男 | 1 | 理解 | 1 | 3 | 85 |
| 2 | 女 | 1 | 钢蛋 | 2 | 3 | 175 |
| 3 | 男 | 1 | 张三 | 3 | 4 | 329 |
| 4 | 男 | 1 | 张一 | 4 | 4 | 257 |
| 5 | 女 | 1 | 张二 | 5 | 4 | 257 |
| 6 | 男 | 1 | 张四 | 6 | 4 | 276 |
| 7 | 女 | 2 | 铁锤 | 7 | 4 | 264 |
| 8 | 男 | 2 | 李三 | 8 | 4 | 264 |
| 9 | 男 | 2 | 李一 | 9 | 4 | 268 |
| 10 | 女 | 2 | 李二 | 10 | 4 | 297 |
| 11 | 男 | 2 | 李四 | 11 | 4 | 297 |
| 12 | 女 | 3 | 如花 | 12 | 4 | 297 |
| 13 | 男 | 3 | 刘三 | 13 | 1 | 87 |
+-----+--------+----------+--------+------------+------------+----------+
13 rows in set (0.00 sec)
#改名编排之后的表格
select t1.sid,t1.sname,t2.cla_num,t2.sum_soc from student as t1 inner join (select student_id,count(sid) as cla_num,sum(num) as sum_soc from score group by student_id) as t2 on t1.sid=t2.student_id;
+-----+--------+---------+---------+
| sid | sname | cla_num | sum_soc |
+-----+--------+---------+---------+
| 1 | 理解 | 3 | 85 |
| 2 | 钢蛋 | 3 | 175 |
| 3 | 张三 | 4 | 329 |
| 4 | 张一 | 4 | 257 |
| 5 | 张二 | 4 | 257 |
| 6 | 张四 | 4 | 276 |
| 7 | 铁锤 | 4 | 264 |
| 8 | 李三 | 4 | 264 |
| 9 | 李一 | 4 | 268 |
| 10 | 李二 | 4 | 297 |
| 11 | 李四 | 4 | 297 |
| 12 | 如花 | 4 | 297 |
| 13 | 刘三 | 1 | 87 |
+-----+--------+---------+---------+
13 rows in set (0.00 sec)题准备：
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
