mysql查询表达式解析

1、mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 10 | |
| sex | tinyint(1) | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT id,username FROM users;

+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | John |
| 10 | John |
| 11 | ROSE |
| 12 | Ben |
+----+----------+
7 rows in set (0.00 sec)

mysql> SELECT username,id FROM users;

+----------+----+
| username | id |
+----------+----+
| Tom | 1 |
| John | 2 |
| Tom | 3 |
| John | 4 |
| John | 10 |
| ROSE | 11 |
| Ben | 12 |
+----------+----+

mysql> SELECT users.id, users.username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | John |
| 10 | John |
| 11 | ROSE |
| 12 | Ben |
+----+----------+

mysql> SELECT id AS userID, username AS uname FROM users;
+--------+-------+
| userID | uname |
+--------+-------+
| 1 | Tom |
| 2 | John |
| 3 | Tom |
| 4 | John |
| 10 | John |
| 11 | ROSE |
| 12 | Ben |
+--------+-------+

 2、查询结果分组

[GROUP BY {col_name | position} [ASC | DESC], ...]

 

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | John | 456 | 27 | 0 |
| 10 | John | 456 | 21 | 0 |
| 11 | ROSE | 67 | 4 | 0 |
| 12 | Ben | 456 | 13 | NULL |
+----+----------+----------+-----+------+
7 rows in set (0.01 sec)

 

mysql> SELECT sex FROM users GROUP BY sex;
+------+
| sex    |
+------+
| NULL  |
| 0       |
+------+

mysql>
mysql> SELECT sex FROM users GROUP BY 1;  # 1表示select语句中第一个出现的字段
+------+
| sex |
+------+
| NULL |
| 0 |
+------+

3、having语句设置分组条件

mysql> SELECT sex,age FROM users GROUP BY 1 HAVING age > 35;
Empty set (0.00 sec)

4、对查询结果进行排序

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | John | 456 | 27 | 0 |
| 10 | John | 456 | 21 | 0 |
| 11 | ROSE | 67 | 4 | 0 |
| 12 | Ben | 456 | 13 | NULL |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY id DESC;  #以降序方式进行排序

+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 12 | Ben | 456 | 13 | NULL |
| 11 | ROSE | 67 | 4 | 0 |
| 10 | John | 456 | 21 | 0 |
| 4 | John | 456 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 2 | John | 456 | 38 | 0 |
| 1 | Tom | 123 | 29 | 0 |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY age;  #排序默认为升序
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 11 | ROSE | 67 | 4 | 0 |
| 12 | Ben | 456 | 13 | NULL |
| 10 | John | 456 | 21 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | John | 456 | 27 | 0 |
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY age, id DESC;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 11 | ROSE | 67 | 4 | 0 |
| 12 | Ben | 456 | 13 | NULL |
| 10 | John | 456 | 21 | 0 |
| 4 | John | 456 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
+----+----------+----------+-----+------+

5、限制查询结果返回的数量

[LIMIT  {[OFFSET,] row_count | row_count OFFSET  offset}]

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 4 | John | 456 | 27 | 0 |
| 10 | John | 456 | 21 | 0 |
| 11 | ROSE | 67 | 4 | 0 |
| 12 | Ben | 456 | 13 | NULL |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | Tom | 123 | 29 | 0 |
| 2 | John | 456 | 38 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2,2;  #第一个2为偏移量，第二个为限制为2条记录
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 3 | Tom | 123 | 27 | 0 |
| 4 | John | 456 | 27 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM users ORDER BY id desc;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 12 | Ben | 456 | 13 | NULL |
| 11 | ROSE | 67 | 4 | 0 |
| 10 | John | 456 | 21 | 0 |
| 4 | John | 456 | 27 | 0 |
| 3 | Tom | 123 | 27 | 0 |
| 2 | John | 456 | 38 | 0 |
| 1 | Tom | 123 | 29 | 0 |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY id desc LIMIT 2,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 10 | John | 456 | 21 | 0 |
| 4 | John | 456 | 27 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.01 sec)