MySQL数据高级查询之连接查询、联合查询、子查询[通俗易懂]

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `student_id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `major` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT *
FROM t1
CROSS JOIN t2

DROP TABLE IF EXISTS `cms_manage`;
CREATE TABLE `cms_manage` (
  `id` int(11) NOT NULL,
  `admin_user` varchar(32) DEFAULT NULL,
  `login_count` int(11) DEFAULT NULL,
  `level` int(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `cms_level`;
CREATE TABLE `cms_level` (
  `id` int(11) NOT NULL,
  `level_name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT
	m.id,
	m.admin_user,
	m.login_count,
	l.level_name
FROM
	cms_manage as m
JOIN cms_level as l  ON m.level = l.id

SELECT
	m.id,
	m.admin_user,
	m.login_count,
	l.level_name
FROM
	cms_manage as m
LEFT JOIN cms_level as l  ON m.level = l.id

SELECT *
FROM t2
UNION
SELECT *
FROM t2

SELECT *
FROM t2
UNION all
SELECT *
FROM t2

SELECT *
FROM t1
UNION all
SELECT *
FROM t2

FROM t2 
WHERE `name`="大圣"
UNION all
SELECT *
FROM t2
WHERE `name`="aa"

按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

From子查询: 子查询跟在from之后

Where子查询: 子查询出现where条件中

Exists子查询: 子查询出现在exists里面

按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

标量子查询: 子查询得到的结果是一行一列

列子查询: 子查询得到的结果是一列多行

行子查询: 子查询得到的结果是多列一行(多行多列) （1,2,3出现的位置都是在where之后)

表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

SELECT title
FROM t22
WHERE category_id=(SELECT category_id FROM t11 WHERE name='科技')