在讲连接查询前，我们需要像之前在基础查询一样，需要添加额外的girls数据库以及在employees数据库中添加新的工资等级表job_grades。
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
1 连接查询
连接查询分为92标准和99标准，我们分别说这两个标准。
1.1 sql92标准，仅仅支持内连接
#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2 有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
注意：92标准的内连接99也支持，但是99支持的92不一定支持。所以下面所讲的92标准的内连接99也能正常使用
*/
#引入
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#一、sql92标准
#1、等值连接
/*
① 多表等值连接的结果为多表的交集部分，因为一开始不加等值连接条件结果为笛卡尔现象
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④ 一般需要为表起别名
⑤ 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
#3、两个表的顺序可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;
#4、可以加筛选
#案例1：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
#5、可以加分组
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
#注：若department_name是一一对应，则d.`manager_id`可以不用加，当然加上也没问题
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
#6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;
#2、非等值连接
#案例1：查询员工的工资和工资级别，并且只看A等级级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
/*
创建上面的job_grades表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
*/
#3、自连接(一张表当多张表使用)
#案例1：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
#去掉id后结果为：
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
/*
①做自连接这种题可以先select有关的字段,得出多张表的结果
SELECT e.employee_id,e.last_name,e.manager_id
FROM employees e;
② 然后对比多张表的结果，例如这里对比后，得出当e.`manager_id`=m.`employee_id`时，可以拿到员工名与领导名
*/
1.2 sql99标准【推荐】：支持内连接+外连接（左外和右外）+ 交叉连接
二、sql99语法：通过join关键字实现连接
含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接
语法：
select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】
好处：语句上，连接条件和筛选条件实现了分离，简洁明了！
#1 等值查询
#案例1
#查询每个城市的部门个数（上面92的例子）
SELECT COUNT(*) count,l.city
FROM departments d
INNER JOIN locations l ON d.`location_id`=l.`location_id` GROUP BY city;
# INNER不写，默认也是INNER代表内连。
#2、非等值连接
#案例1：查询员工的工资和工资级别，并且只看A等级级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON salary BETWEEN g.`lowest_sal` AND g.`highest_sal` AND g.`grade_level`='A' ORDER BY salary DESC;
# 这里我自己给它降序排序，方便对比99与92结果是否一致，对比后是一致的。
# 3 自连接
#案例：查询员工名和直接上级的名称
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m ON e.`manager_id`=m.`employee_id`;
# 注意，自连接与等值连接的区别是：自连接的多个表的别名都是同一张表，例如这里的e和m。
# 以上就是内连接的3个例子。
# 4 左外、右外连接。看下面的案例一和二。
# 5 交叉连接。
#案例：查看beauty表和boys表所有可能的值。
SELECT * FROM `beauty` CROSS JOIN `boys`
# 实际上交叉连接就是笛卡尔乘积AB表的所有集合，所以这种连接在实际并没有太大作用。
图解上面的99语法的连接查询：
这部分是参考了mysql内连接、外连接、交叉连接区别。
1）内连接(按等值连接展开)：
Beauty表中所有的数据与Boys表所有数据进行等值查询查询。
# 没有使用上面的案例
SELECT * FROM `beauty`b INNER JOIN `boys`bo ON b.`boyfriend_id`=bo.`id`;
注：Boys漏了一列userCP字段没有截图出来。
2）外连接。左连接是 left jion 左边表作为主表，Beauty表中数据对应Boys表数据进行查询，如果有返回对应的值，没有返回null,以表中第一条数据为例，没有对应的值返回null。右连接同理。
SELECT * FROM `beauty`b LEFT JOIN `boys`bo ON b.`boyfriend_id`=bo.`id`;
3）交叉连接。实际上交叉连接就是笛卡尔乘积AB表的所有集合，所以这种连接在实际并没有太大作用。
SELECT * FROM `beauty` CROSS JOIN `boys`;
2 连接查询案例
下面是我们连接查询的案例，我们主要不是记住题目，而是我在做题时标注了如何做这种连接查询的方法，步骤已经写在注释。并且这里强调一点，主表是主语，从表是修饰主表，即定语。
#一、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
/*
分析：
1. 存在的字段用到两个表，所以使用连接查询。
2. 两个表可能存在一个表有，一个表没有的字段内容，所以使用外连接.即女神的男朋友id不在男朋友信息中。
3. 并且是女神的男朋友信息，所以女神是主表，男朋友信息是从表。
注：举例第2点，例如上面的截图外连接中，主表是beauty，次表是boys，以柳岩的boyfriend_id=8为例，去一一匹配次表，由于次表不存在8,的id，
也就是说，一个表有这个字段(一般指ON后面的字段)，一个表没有这个字段，那么就使用外连接即可，左外右外都行。并且看到是自动填充null。
*/
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;
#二、查询哪个城市没有部门
/*
分析：
5. 存在的字段用到两个表，所以使用连接查询
6. 两个表可能存在一个表有，一个表没有的字段内容，所以使用外连接.即城市可能没有部门(即总结一二，凡是表1有表2没有或者表2有表1没有，直接使用外连接即可，实际操作选择哪种连接可以查看数据库的两个表字段)
7. 并且哪个城市没有部门代表，城市是主表，部门是从表
*/
SELECT city, d.`department_id`
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;
# city后面的d.`department_id`可以不要，我这里是为了方便去掉where条件进行观察
#三、查询部门名为SAL或IT的员工信息
/*
分析：
8. 存在的字段用到两个表，所以使用连接查询
9. 两个表可能存在一个表有，一个表没有的字段内容，所以使用外连接.即员工信息可能不包含SAL或IT的部门
10. 并且部门名为SAL或IT的员工信息，所以部门名是主表，员工信息是从表
*/
SELECT e.*,d.department_name,d.`department_id`
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');
SELECT * FROM departments
WHERE `department_name` IN('SAL','IT');
157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
