mysql select表达式_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> SELECTid 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 usersORDER 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)