MySQL中常用操作--子查询

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)

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)

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;

MySQL中常用操作--子查询

标签：就是   --   重复   ble   语句   子查询   tin   info   from