多表查询(含完整例子)

多表查询1.多表联合查询1.1 语法select字段1,字段2... from 表1,表2... [where 条件] 不加等值条件直接进行查询,则会出现以下效果,这种结果我们称之为笛卡尔乘积笛卡尔乘积公式 : A表中数据条数*B表中数据条数= 笛卡尔乘积.select * from person,dept 上述语句查询结果：1.2 等值连接...

闲人不梦卿

7698人浏览 · 2020-05-06 17:20:52

闲人不梦卿 · 2020-05-06 17:20:52 发布

文章目录

- 多表查询

多表查询

1.多表联合查询

1.1 语法

select  字段1,字段2... from 表1,表2... [where 条件]

不加等值条件直接进行查询,则会出现以下效果,这种结果我们称之为笛卡尔乘积

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积.

在这里插入图片描述

select * from person,dept

上述语句查询结果：
在这里插入图片描述

1.2 等值连接

等值条件直接进行查询,不会去重重复字段，新关系的记录数：两表相同字段相同值的记录数=∑n * m, n为A表某种与B表相同的值的个数，m为B表与A表相对应值的个数

select user.name,user.product_name,product.company from user,product where user.product_name = product.name

在这里插入图片描述

上述语句查询结果：

在这里插入图片描述

2.多表连接查询

2.1 语法

SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

2.2 内连接查询

含义：查询满足某一条件的A，B内部的数据，也即是得到的是A,B内部共有数据

查询语句：

select * from person inner join dept on person.dept_id = dept.did

效果如下：

在这里插入图片描述

数学模型表示：
在这里插入图片描述

2.3 左连接查询

含义：查询A的所有数据，和满足某一条件的B的数据

查询语句：

select * from person left join dept on person.dept_id = dept.did

效果如下：
在这里插入图片描述

数学模型表示：
在这里插入图片描述

2.4 左连接查询 [where B.column is null]

含义：查询A中的所有数据减去"与B满足同一条件的数据"，然后得到的A剩余数据

查询语句：

select * from person left  join dept on person.dept_id = dept.did where dept.did is null

效果如下：

在这里插入图片描述
数学模型表示：

在这里插入图片描述

2.5 右连接查询

含义：B的所有数据，和满足某一条件的A的数据

查询语句：

select * from person right  join dept  on person.dept_id = dept.did

效果如下：
在这里插入图片描述
数学模型表示：

2.6 联合查询

含义：满足某一条件的公共记录，和独有的记录
查询语句：

select * from person left  join dept on person.dept_id = dept.did
union 
select * from person right  join dept  on person.dept_id = dept.did

效果如下：

在这里插入图片描述

数学模型表示：

在这里插入图片描述

2.7 联合查询 [where column is null]

含义：查询A，B中不满足某一条件的记录之和

查询语句：

select * from person left  join dept on person.dept_id = dept.did where dept.did is null
union 
select * from person right  join dept  on person.dept_id = dept.did where person.dept_id is null

效果如下：
在这里插入图片描述

2.8 交叉连接查询

含义：查询A，B中不满足某一条件的记录之和
语法：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

查询语句：

select * from person cross  join dept

效果如下：

在这里插入图片描述

还可以为cross join指定条件（where）：

select * from person cross  join dept on person.dept_id = dept.did

效果如下：
在这里插入图片描述
实际上实现了内连接的效果

2.9 子查询

含义：子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

语法：

WHERE <表达式> <操作符> (子查询)

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等。

1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

3）any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。some是any的别名，用法相同。

4）all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

例子：
1.作为表名使用

select * from (select * from person) as 表名;

求最大工资那个人的姓名和薪水例子

select t.name,t.salary from (select name,salary,max(salary) from person) as t

运行效果：

在这里插入图片描述

2.子查询包含=操作符

求最大工资那个人的姓名和薪水例子

select name,salary  from person where salary = (select max(salary) from person)

在这里插入图片描述

对比2.和1.的例子，可以得知1.例子是运行结果是错误的，错误的原因是因为select name,salary,max(salary) from person 并不是获取最大工资那个人的姓名和薪水，而是获取了最大工资，姓名只是获取第一行，而不是最大工资那行的姓名。

3.子查询包含any 关键字

假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，


select ...from ... where a > any(...);
->
select ...from ... where a > result1 or a > result2 or a > result3;

求工资大于任意一个在3000-5000水平的人的姓名和薪水例子

select name,salary from person where salary > any(select salary from person where   salary >= 2000.00 and salary <= 5000.00)

在这里插入图片描述

4.子查询包含all 关键字

ALL关键字与any关键字类似，只不过上面的or改成and。即:

select ...from ... where a > all(...);
->
select ...from ... where a > result1 and a > result2 and a > result3;

求工资大于在3000-5000水平的人的姓名和薪水例子

select name,salary from person where salary > all(select salary from person where   salary >= 2000.00 and salary <= 5000.00)

在这里插入图片描述

4.子查询包含in 关键字
查询部门是销售部和市场部的人员姓名和年龄的例子：

select * from person where dept_id in (select did from dept where dname in ('销售部','市场部'))

在这里插入图片描述

5.子查询包含EXISTS 关键字

查询是否存在市场部，如果存在，就查询出该部门人员中年龄大于20的记录例子：

select * from person where  exists(select dname from dept where dname ='市场部') and age >20  and dept_id = (select did from dept where dname ='市场部')

在这里插入图片描述

所需表和数据

-- 创建部门
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


-- 添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `price` float(255, 0) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `company` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `product` VALUES ('1', 21, 'office工具', '金山');
INSERT INTO `product` VALUES ('2', 30, 'office工具', '微软');
INSERT INTO `product` VALUES ('3', 10, '报表', '帆软');
INSERT INTO `product` VALUES ('4', 1, '报表', '易数');
INSERT INTO `product` VALUES ('5', 12, 'apple手机', '苹果');
INSERT INTO `product` VALUES ('6', 14, 'huawei手机', '华为');
INSERT INTO `product` VALUES ('7', 15, '报表', '易晰');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES ('1', 'tbb', 'china', '报表');
INSERT INTO `user` VALUES ('2', 'rose', 'Japen', 'office工具');
INSERT INTO `user` VALUES ('3', 'xm', 'English', '报表');
INSERT INTO `user` VALUES ('4', 'xb', 'chian', 'office工具');
INSERT INTO `user` VALUES ('5', 'ca', 'chian', 'huawei手机');