数据库基础--MySQL多表查询之联表查询

联表查询

定义：多张表联合在一起查询，例如学生信息与学生班级表、部门与员工表

创建两张表，主表与从表

CREATE TABLE `TestMain`(
	`id` INT Not NULL AUTO_INCREMENT,
	 `name`VARCHAR(10),
	  `introduction` VARCHAR(255),
		PRIMARY KEY(`id`)
);
CREATE TABLE `Test`(
	`id` INT Not NULL AUTO_INCREMENT,
	 `name`VARCHAR(10),
	  `introduction` VARCHAR(255),
		`age` INT(3) DEFAULT '3',
		testid INT NOT NULL,
		PRIMARY KEY(`id`),
		CONSTRAINT TEST_ID FOREIGN KEY (testid) REFERENCES TestMain(id)
);

交叉连接查询

语法定义：SELECT * FROM 表名1，表名2 

运行结果:

缺陷：会产生数据冗余，出现了两条同样的从表数据

内连接查询

显示内连接

语法:Select * FROM 表名1 INNER JOIN 表名2 ON 条件

示例：

SELECT * FROM testmain INNER JOIN test ON testmain.id=testid

运行结果

隐式内连接

Select * FROM 表名1，表名2 WHERE 条件

示例：

SELECT * FROM testmain,test WHERE testmain.id=testid

运行结果

外连接查询

左连接

以左表数据为基准，左表有而右表没有，也会返回数据，只是返回值为null

SELECT * FROM testmain LEFT JOIN test ON testmain.id=testid

运行结果

右连接

以右表数据为基准，右表有而左表没有，也会返回数据，只是返回值为null

SELECT * FROM testmain RIGHT JOIN test ON testmain.id=testid

运行结果

左连接与右连接的查询结果进行连接并进行去重

SELECT * FROM testmain LEFT JOIN test ON testmain.id=testid;
UNION
SELECT * FROM testmain RIGHT JOIN test ON testmain.id=testid

运行结果

子查询

简单示例

定义：一个查询语句嵌套在另一个查询语句中的查询

SELECT * FROM test WHERE age IN(SELECT MAX(age) FROM test)

语法释义：查询出test表中年龄最大学生的所有信息

运行结果

需求2：查询testid为1和2的学生所有信息

从表数据：

主表数据：

SQL语句：

SELECT * FROM test JOIN testmain ON testid=testmain.id AND(testid=1 OR testid=2)

运行结果

需求：查询testid为1且年龄大于3的所有学生的信息

SQL语句

SELECT * FROM test JOIN testmain ON testid=testmain.id AND(testid=1 AND age>3)

运行结果

子查询相关关键字

ALL

需求1：查询年龄大于testid为1的学生的年龄的学生的所有信息

test表数据如下

SQL语句：

SELECT * FROM test WHERE age> ALL(SELECT age FROM test WHERE testid=1)

语法释义:简单理解就是ALL关键字把查询到的所有年龄，都装入了一个集合，比where age=这样的查询方式查询范围更大

运行结果

NOT IN

IN:判断所查询出的值是否在某个集合中

需求：查询testid=1和3的所有学生的信息

SQL语句:

SELECT * FROM test WHERE testid IN(1,3)

此处也可通过多表连接的方式进行查询

SELECT * FROM test WHERE testid IN (SELECT id FROM testmain WHERE testmain.id=1 OR testmain.id=3)

运行结果：

NOT IN：则可以取反

SQL语句：

SELECT * FROM test WHERE testid NOT IN (SELECT id FROM testmain WHERE testmain.id=1 OR testmain.id=3)

运行结果

EXISTS

定义：判断查询的结果是否存在，比In的效率要高，一般数据量大的情况下推荐使用EXISTS

查询到了返回TRUE,为查询到返回FALSE

编写EXISTS注意点：

SQL语句

SELECT * FROM test WHERE EXISTS(SELECT * FROM test WHERE age>4)

执行结果

大家可以发现，此时的查询结果显然有问题，这是因为我们在EXISTS后面的括号当中，将语句给写死了，就一直返回的为TRUE.

正确写法

SELECT * FROM test  S1 WHERE EXISTS(SELECT * FROM test S2 WHERE S1.age>4)

通过内嵌一次判断，来保证最终输出的结果为正确的，S1 ,S2为临时表名，就是为了区分开EXISTS里的test与EXISTS外的test

运行结果

表自关联

(测试数据库数据)

SQL语句

SELECT * FROM company type AS A,
company type AS B
WHERE A.ID=B.parent_id;

运行结果

写在最后：

多表查询在实际运用中十分广泛，本次分享的都是一些较为简单的用法。希望能够给大家带来帮助，SQL语句是需要大家去勤加练习的。笔者小，中，大厂均有面试经历，每日分享全栈知识与面试题，希望能够与大家共同进步。