子查询就是SELECT 查询是另一个SELECT的附属, 此时, MySQL从最内层的查询开始, 再向外向上查询, 接着最外层的父查询被执行, 它的结果也指定给父查询.
带IN的子查询:
只有子查询返回的结果列中包含一个值时, 比较运算符才适用. 假如一个子查询的返回结果集是值的列表, 这时比较运算符就必须用关键字IN代替
IN运算符可以检测结果集中是否存在某个特定的值, 如果检测成功就执行外部的查询.
mysql> select * fromstu->;+----+----------+-------+
| 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.00sec)
mysql> SELECT * FROMscoreLevel;+----+-------+
| id | score |
+----+-------+
| 1 | 90 |
| 2 | 80 |
| 3 | 70 |
+----+-------+
3 rows in set (0.00sec)
mysql> SELECT * FROM stu WHERE score IN (SELECT score FROMscoreLevel);+----+----------+-------+
| id | username | score |
+----+----------+-------+
| 3 | xiaohong | 80 |
| 6 | 达叔 | 90 |
+----+----------+-------+
2 rows in set (0.00 sec)
NOT IN :
mysql> SELECT * FROM stu WHERE scoreNOT IN(SELECT score FROMscoreLevel);+----+----------+-------+
| 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);
Emptyset (0.00sec)
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 FROMscoreLevel);--表示在 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 FROMscoreLevel);--表示在 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 tableexcellentStudent(-> 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 FROMscoreLevel);
Query OK,4 rows affected (0.01sec)
Records:4 Duplicates: 0 Warnings: 0mysql> SELECT * FROMexcellentstudent;+----+----------+-------+
| 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.01sec)
Records:7 Duplicates: 0 Warnings: 1mysql> SELECT * FROMexcellentstudent;+----+----------+-------+
| 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;