Select - 语法指南

一、Select - 语法解析

</font>

## 二、Select - 语法示例
<font size=4>


### 单表查询
>查询表结构

mysql> desc t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(200) | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show columns from d1.t3;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(200) | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

>查询表中数据

mysql> select * from t3;
mysql> select id,name,email from t3;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | n1 | e1 |
| 2 | n2 | e2 |
| 3 | n3 | e3 |
| 4 | n1 | e1 |
| 5 | n2 | e2 |
| 6 | n3 | e3 |
+----+------+-------+

### 单表条件查询
>`= 等于`、`<> != 不等于`、`in 包含`、`not in 不包含`、`like 模糊查找`

mysql> select id,name,email from t3 where name='n1';
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | n1 | e1 |
| 4 | n1 | e1 |
+----+------+-------+
2 rows in set (0.00 sec)

### 单表多条件查询
>`or 或`、`and 与`、`in 包含`、`not in 不包含`

mysql> select id,name,email from t3 where name='n1' and id>1;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 4 | n1 | e1 |
+----+------+-------+
1 row in set (0.00 sec)

### 排序查询
>单列升序

mysql> select id,name,email from t3 order by id;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 1 | n1 | e1 |
| 2 | n2 | e2 |
| 3 | n3 | e3 |
| 4 | n1 | e1 |
| 5 | n2 | e2 |
| 6 | n3 | e3 |
+----+------+-------+
6 rows in set (0.00 sec)

>单列降序

mysql> select id,name,email from t3 order by id desc;
+----+------+-------+
| id | name | email |
+----+------+-------+
| 6 | n3 | e3 |
| 5 | n2 | e2 |
| 4 | n1 | e1 |
| 3 | n3 | e3 |
| 2 | n2 | e2 |
| 1 | n1 | e1 |
+----+------+-------+
6 rows in set (0.00 sec)

>多列混合排序

第一列升序，第二列降序

mysql> select name,age from t5 order by name,age desc;
+------+------+
| name | age |
+------+------+
| n1 | 22 |
| n2 | 23 |
| n3 | 24 |
| n4 | 25 |
| n5 | 25 |
| n5 | 11 |
| n6 | 22 |
+------+------+
7 rows in set (0.00 sec)

第一列降序，第二列升序

mysql> select name,age from t5 order by name desc,age;
+------+------+
| name | age |
+------+------+
| n6 | 22 |
| n5 | 11 |
| n5 | 25 |
| n4 | 25 |
| n3 | 24 |
| n2 | 23 |
| n1 | 22 |
+------+------+
7 rows in set (0.00 sec)

### 分组查询
>`max() 最大值`、`min()最小值`、 `avg() 平均值`、 `count() 计数`

mysql> select name,count(email) as sum from t3 group by name;
+------+-----+
| name | sum |
+------+-----+
| n1 | 2 |
| n2 | 2 |
| n3 | 2 |
+------+-----+
3 rows in set (0.00 sec)

### 条件、分组、排序、过滤

mysql> select name,count(email) sum from t3 where id>1 group by name having sum>1 order by name desc;
+------+-----+
| name | sum |
+------+-----+
| n3 | 2 |
| n2 | 2 |
+------+-----+
2 rows in set (0.01 sec)

###  笛卡尔积

mysql> select a.,b. from t3 a,t4 b;
+----+------+-------+------+------+------+
| id | name | email | id | name | age |
+----+------+-------+------+------+------+
| 1 | n1 | e1 | 1 | n1 | 22 |
| 1 | n1 | e1 | 2 | n2 | 23 |
| 1 | n1 | e1 | 3 | n3 | 24 |
| 2 | n2 | e2 | 1 | n1 | 22 |
| 2 | n2 | e2 | 2 | n2 | 23 |
| 2 | n2 | e2 | 3 | n3 | 24 |
| 3 | n3 | e3 | 1 | n1 | 22 |
| 3 | n3 | e3 | 2 | n2 | 23 |
| 3 | n3 | e3 | 3 | n3 | 24 |
| 4 | n1 | e1 | 1 | n1 | 22 |
| 4 | n1 | e1 | 2 | n2 | 23 |
| 4 | n1 | e1 | 3 | n3 | 24 |
| 5 | n2 | e2 | 1 | n1 | 22 |
| 5 | n2 | e2 | 2 | n2 | 23 |
| 5 | n2 | e2 | 3 | n3 | 24 |
| 6 | n3 | e3 | 1 | n1 | 22 |
| 6 | n3 | e3 | 2 | n2 | 23 |
| 6 | n3 | e3 | 3 | n3 | 24 |
+----+------+-------+------+------+------+
18 rows in set (0.00 sec)

### 多表查询

mysql> select a.id,a.name,b.id,b.name from t4 a,t5 b where a.id=b.id and a.id<4 and b.name<>'n2';
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | n1 | 1 | n1 |
| 3 | n3 | 3 | n3 |
+------+------+------+------+
2 rows in set (0.00 sec)

### 嵌套查询

mysql> select name , email from t3 where id > 4 and name in(select name from t4 where id =(select id from t5 where id=2));
+------+-------+
| name | email |
+------+-------+
| n2 | e2 |
+------+-------+
1 row in set (0.01 sec)

### 关联查询
>左关联

mysql> select a.name,a.email,b.name,b.age from t3 a left join t4 b on a.name=b.name;
+------+-------+------+------+
| name | email | name | age |
+------+-------+------+------+
| n1 | e1 | n1 | 22 |
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
| n3 | e3 | n3 | 24 |
| n6 | e4 | NULL | NULL |
| n7 | e5 | NULL | NULL |
+------+-------+------+------+
8 rows in set (0.00 sec)

>右关联

mysql> select a.name,a.email,b.name,b.age from t3 a right join t4 b on a.name=b.name;
+------+-------+------+------+
| name | email | name | age |
+------+-------+------+------+
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
| NULL | NULL | n4 | 25 |
| NULL | NULL | n5 | 25 |
+------+-------+------+------+
8 rows in set (0.00 sec)

>内关联

mysql> select a.name,a.email,b.name,b.age from t3 a join t4 b on a.name=b.name;
+------+-------+------+------+
| name | email | name | age |
+------+-------+------+------+
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
+------+-------+------+------+
6 rows in set (0.00 sec)

mysql> select a.name,a.email,b.name,b.age from t3 a inner join t4 b on a.name=b.name;
+------+-------+------+------+
| name | email | name | age |
+------+-------+------+------+
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
| n1 | e1 | n1 | 22 |
| n2 | e2 | n2 | 23 |
| n3 | e3 | n3 | 24 |
+------+-------+------+------+
6 rows in set (0.00 sec)

### 合并查询
>结果集直接合并，不做任何处理

mysql> select id,name from t3 union all select id,name from t4;
+------+------+
| id | name |
+------+------+
| 1 | n1 |
| 2 | n2 |
| 3 | n3 |
| 4 | n1 |
| 5 | n2 |
| 6 | n3 |
| 7 | n6 |
| 8 | n7 |
| 1 | n1 |
| 2 | n2 |
| 3 | n3 |
| 4 | n4 |
| 4 | n5 |
+------+------+
13 rows in set (0.00 sec)

>结果集去重合并

mysql> select id,name from t3 union select id,name from t4;
+------+------+
| id | name |
+------+------+
| 1 | n1 |
| 2 | n2 |
| 3 | n3 |
| 4 | n1 |
| 5 | n2 |
| 6 | n3 |
| 7 | n6 |
| 8 | n7 |
| 4 | n4 |
| 4 | n5 |
+------+------+
10 rows in set (0.00 sec)

### 克隆/备份表

mysql> create table t5 as select * from t4;
mysql> select * from t5;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | n1 | 22 |
| 2 | n2 | 23 |
| 3 | n3 | 24 |
| 4 | n4 | 25 |
| 4 | n5 | 25 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> create table t6 as select * from t4 where name='n1';
mysql> select * from t6;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | n1 | 22 |
+------+------+------+
1 row in set (0.00 sec)

### 快速复制数据

mysql> insert into t6 select * from t5;
mysql> select * from t6;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | n1 | 22 |
| 1 | n1 | 22 |
| 2 | n2 | 23 |
| 3 | n3 | 24 |
| 4 | n4 | 25 |
| 4 | n5 | 25 |
+------+------+------+
6 rows in set (0.00 sec)

mysql> insert into t6 select * from t5 where name='n3';
mysql> select * from t6;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | n1 | 22 |
| 1 | n1 | 22 |
| 2 | n2 | 23 |
| 3 | n3 | 24 |
| 4 | n4 | 25 |
| 4 | n5 | 25 |
| 3 | n3 | 24 |
+------+------+------+
7 rows in set (0.00 sec)

</font>