MySQL第四天

结果集的排序

ORDER BY 以某个字段排序

DESC 逆序

mysql> SELECT t,srcuser,srchost,dstuser,dsthost,size FROM mail WHERE size > 5000
 ORDER BY size;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2006-05-16 23:04:19 | phil    | venus   | barb    | venus   |   10294 |
| 2006-05-19 22:21:51 | gene    | saturn  | gene    | venus   |   23992 |
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-14 14:42:21 | barb    | venus   | barb    | venus   |   98151 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2006-05-14 17:03:01 | tricia  | saturn  | phil    | venus   | 2394482 |
+---------------------+---------+---------+---------+---------+---------+
8 rows in set (0.02 sec)

使用视图来简化查询

视图是一种虚拟的数据库表，它并不是实际的数据。

创建视图

代码

mysql> CREATE VIEW mail_view As
    -> SELECT
    -> DATE_FORMAT(t, '%M %e %Y') AS date_sent,
    -> CONCAT(srcuser,'@',srchost) AS sender,
    -> CONCAT(dstuser,'@',dsthost) AS recipient,
    -> size FROM mail;
Query OK, 0 rows affected (0.00 sec)

使用视图

代码

mysql> SELECT date_sent,sender, size FROM mail_view
    -> WHERE size > 10000 ORDER BY size;
+-------------+---------------+---------+
| date_sent   | sender        | size    |
+-------------+---------------+---------+
| May 16 2006 | phil@venus    |   10294 |
| May 19 2006 | gene@saturn   |   23992 |
| May 11 2006 | barb@saturn   |   58274 |
| May 14 2006 | barb@venus    |   98151 |
| May 12 2006 | tricia@mars   |  194925 |
| May 15 2006 | gene@mars     |  998532 |
| May 14 2006 | tricia@saturn | 2394482 |
+-------------+---------------+---------+
7 rows in set (0.00 sec)

多表查询

profile 表ID和 profile_contact 表profile_id对应

其中 id,name 来自profile 表

service, contact_name 来自profile_contact 表

ON表示他们之间的绑定规则

代码

mysql> SELECT id, name, service, contact_name
    -> FROM profile INNER JOIN profile_contact ON id = profile_id;
+----+------+---------+---------------+
| id | name | service | contact_name  |
+----+------+---------+---------------+
|  1 | Fred | AIM     | user1-aimid   |
|  1 | Fred | MSN     | user1-msnid   |
|  2 | Mort | AIM     | user2-aimid   |
|  2 | Mort | MSN     | user2-msnid   |
|  2 | Mort | Yahoo   | user2-yahooid |
|  4 | Carl | Yahoo   | user4-yahooid |
+----+------+---------+---------------+
6 rows in set (0.00 sec)

子查询

代码

mysql> SELECT * FROM profile_contact
    -> WHERE profile_id = (SELECT id FROM profile WHERE name= 'Mort');
+------------+---------+---------------+
| profile_id | service | contact_name  |
+------------+---------+---------------+
|          2 | AIM     | user2-aimid   |
|          2 | MSN     | user2-msnid   |
|          2 | Yahoo   | user2-yahooid |
+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql>

使用LIMIT 取出结果集中的几行

LIMIT 现在返回条数，这样可以节省客户端与服务器端之间的传递信息量

而且 LIMIT 做为分页技术的一个重要工具

比如每3条分一页

可以 LIMIT 0 , 3

LIMIT 3 , 3

代码

mysql> SELECT * FROM profile LIMIT 1;
+----+------+------------+-------+----------------------+------+
| id | name | birth      | color | foods                | cats |
+----+------+------------+-------+----------------------+------+
|  1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza |    0 |
+----+------+------------+-------+----------------------+------+
1 row in set (0.00 sec)

代码

#使用了别名 birthday  哪么ORDER BY 也必须根据别名来排序

mysql> SELECT name, DATE_FORMAT(birth, '%m - %d') AS birthday
    -> FROM  profile ORDER BY birthday LIMIT 1  ;
+------+----------+
| name | birthday |
+------+----------+
| Alan | 02 - 14  |
+------+----------+
1 row in set (0.00 sec)

代码

#分页效果
 
mysql> SELECT id,name,birth FROM profile ORDER BY id LIMIT 0, 3;
+----+------+------------+
| id | name | birth      |
+----+------+------------+
|  1 | Fred | 1970-04-13 |
|  2 | Mort | 1969-09-30 |
|  3 | Brit | 1957-12-01 |
+----+------+------------+
3 rows in set (0.00 sec)

mysql> SELECT id,name,birth FROM profile ORDER BY id LIMIT 3, 3;
+----+------+------------+
| id | name | birth      |
+----+------+------------+
|  4 | Carl | 1973-11-02 |
|  5 | Sean | 1963-07-04 |
|  6 | Alan | 1965-02-14 |
+----+------+------------+
3 rows in set (0.00 sec)