子查询就是SELECT 查询是另一个SELECT的附属, 此时, MySQL从最内层的查询开始, 再向外向上查询, 接着最外层的父查询被执行, 它的结果也指定给父查询.
- 带IN的子查询:
只有子查询返回的结果列中包含一个值时, 比较运算符才适用. 假如一个子查询的返回结果集是值的列表, 这时比较运算符就必须用关键字IN代替
IN运算符可以检测结果集中是否存在某个特定的值, 如果检测成功就执行外部的查询.mysql> select * from stu -> ; ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 3 | xiaohong | 80 | | 4 | 周星星 | 0 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 7 | 浩南 | 60 | | 8 | 山鸡 | 50 | | 9 | 孙猴子 | 85 | | 10 | 李四 | 55 | ---- ---------- ------- 10 rows in set (0.00 sec) mysql> SELECT * FROM scoreLevel; ---- ------- | id | score | ---- ------- | 1 | 90 | | 2 | 80 | | 3 | 70 | ---- ------- 3 rows in set (0.00 sec) mysql> SELECT * FROM stu WHERE score IN (SELECT score FROM scoreLevel); ---- ---------- ------- | id | username | score | ---- ---------- ------- | 3 | xiaohong | 80 | | 6 | 达叔 | 90 | ---- ---------- ------- 2 rows in set (0.00 sec)
NOT IN :
mysql> SELECT * FROM stu WHERE score NOT IN (SELECT score FROM scoreLevel); ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 4 | 周星星 | 0 | | 5 | 凌凌漆 | 100 | | 7 | 浩南 | 60 | | 8 | 山鸡 | 50 | | 9 | 孙猴子 | 85 | | 10 | 李四 | 55 | ---- ---------- ------- 8 rows in set (0.00 sec)
- 带比较运算符的子查询:
=, !=, >, >=, <, <=......SELECT * FROM stu WHERE score>=(SELECT score FROM scoreLevel WHERE id=1);
mysql> SELECT * FROM stu WHERE score>=(SELECT score FROM scoreLevel WHERE id=1); ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | ---- ---------- ------- 4 rows in set (0.00 sec)
- 带关键字EXISTS 的子查询:
使用关键字EXISTS查询时, 内层查询语句不返回查询的记录, 而是返回一个真假值. 如果内层的查询语句查询到满足条件的语句的记录, 就返回一个真值(true), 否则返回一个假值(false).当返回的值为true时, 外层查询语句将进行查询, 当返回结果为false时, 外层查询语句不进行查询或者查询不出任何记录.
SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=2);
mysql> SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=10); Empty set (0.00 sec) mysql> SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=2); ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 3 | xiaohong | 80 | | 4 | 周星星 | 0 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 7 | 浩南 | 60 | | 8 | 山鸡 | 50 | | 9 | 孙猴子 | 85 | | 10 | 李四 | 55 | ---- ---------- ------- 10 rows in set (0.00 sec)
- 带ANY, SOME, ALL 的查询:
mysql> SELECT * FROM stu WHERE score>=ANY(SELECT score FROM scoreLevel);--表示在 stu 中 >= scoreLevel 中的最小值(70)//SOME 和 ANY效果一样 ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 3 | xiaohong | 80 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 9 | 孙猴子 | 85 | ---- ---------- ------- 6 rows in set (0.00 sec)
mysql> SELECT * FROM stu WHERE score>=ALL(SELECT score FROM scoreLevel);--表示在 stu 中 >= scoreLevel 中的最大值(90) ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | ---- ---------- ------- 4 rows in set (0.00 sec)
CREATE .......SELECT .......:
mysql> CREATE table excellentStudent( -> id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(100) NOT NULL, -> score TINYINT UNSIGNED NOT NULL -> )ENGINE=INNODB CHARSET=UTF8 SELECT * FROM stu WHERE score>=ALL(SELECT score FROM scoreLevel); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM excellentstudent; ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | ---- ---------- ------- 4 rows in set (0.00 sec)
INSERT.......SELECT.........:
mysql> INSERT excellentStudent(username) SELECT username FROM user; Query OK, 7 rows affected, 1 warning (0.01 sec) Records: 7 Duplicates: 0 Warnings: 1 mysql> SELECT * FROM excellentstudent; ---- ---------- ------- | id | username | score | ---- ---------- ------- | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 7 | a | 0 | | 8 | b | 0 | | 9 | c | 0 | | 10 | d | 0 | | 11 | e | 0 | | 12 | f | 0 | | 13 | g | 0 | ---- ---------- ------- 11 rows in set (0.00 sec)
去掉字段的重复值:
SELECT DISTINCT(字段名) FROM 表名;
创建一个与table_name2一样的表结构的table_name1(创建好的table_name1有结构, 但是内容为空.):
CREATE TABLE table_name1 LIKE table_name2;
-
合并查询结果
合并查询结果是将多个SELECT语句的查询结果合并到一起, 使用关键字 UNION 和 UNION ALL.其中, UNION 是将多个表中的记录去重后合并到一起, 而 UNION ALL 只是简单的合并操作.
UNION:
SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称... FROM tbl_name2;
UNION ALL:
SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称... FROM tbl_name2;