mysql中子查询关键字_MySQL中常用操作--子查询

子查询就是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;