未使用别名（alias）
1 mysql> SELECT DATE_FORMAT(t,'%M %e, %Y'),
2 -> srcuser, size FROM mail;
3 +----------------------------+---------+---------+
4 | DATE_FORMAT(t,'%M %e, %Y') | srcuser | size |
5 +----------------------------+---------+---------+
6 | May 11, 2006 | barb | 58274 |
7 | May 12, 2006 | tricia | 194925 |
8 | May 12, 2006 | phil | 1048 |
9 | May 13, 2006 | barb | 271 |
10 | May 14, 2006 | gene | 2291 |
11 | May 14, 2006 | phil | 5781 |
12 | May 14, 2006 | barb | 98151 |
13 | May 14, 2006 | tricia | 2394482 |
14 | May 15, 2006 | gene | 3824 |
15 | May 15, 2006 | phil | 978 |
16 | May 15, 2006 | gene | 998532 |
17 | May 15, 2006 | gene | 3856 |
18 | May 16, 2006 | gene | 613 |
19 | May 16, 2006 | phil | 10294 |
20 | May 17, 2006 | phil | 873 |
21 | May 19, 2006 | gene | 23992 |
22 +----------------------------+---------+---------+
23 16 rows in set (0.00 sec)
使用别名
代码
1 mysql> SELECT DATE_FORMAT(t, '%M %e, %Y') AS 'Date of message',
2 -> srcuser AS 'Message sender', size AS 'Number of bytes' FROM mail;
3 +-----------------+----------------+-----------------+
4 | Date of message | Message sender | Number of bytes |
5 +-----------------+----------------+-----------------+
6 | May 11, 2006 | barb | 58274 |
7 | May 12, 2006 | tricia | 194925 |
8 | May 12, 2006 | phil | 1048 |
9 | May 13, 2006 | barb | 271 |
10 | May 14, 2006 | gene | 2291 |
11 | May 14, 2006 | phil | 5781 |
12 | May 14, 2006 | barb | 98151 |
13 | May 14, 2006 | tricia | 2394482 |
14 | May 15, 2006 | gene | 3824 |
15 | May 15, 2006 | phil | 978 |
16 | May 15, 2006 | gene | 998532 |
17 | May 15, 2006 | gene | 3856 |
18 | May 16, 2006 | gene | 613 |
19 | May 16, 2006 | phil | 10294 |
20 | May 17, 2006 | phil | 873 |
21 | May 19, 2006 | gene | 23992 |
22 +-----------------+----------------+-----------------+
23 16 rows in set (0.00 sec)
字符串与表达式的区别
数字靠右边对齐
字符串靠左边对齐
‘1+1+1’ 表示 一个字符串
没加引号的1+1+1 是一个表达式
代码
1 mysql> SELECT '1+1+1' AS 'the expression', 1+1+1 AS 'The result';
2 +----------------+------------+
3 | the expression | The result |
4 +----------------+------------+
5 | 1+1+1 | 3 |
6 +----------------+------------+
7 1 row in set (0.00 sec)
合并多列组成复合值
CONCAT（） 使用
代码
mysql> SELECT
-> DATE_FORMAT(t,'%M, %e,%Y') AS date_sent,
-> CONCAT(srcuser,'@',srchost) AS sender,
-> CONCAT(dstuser,'@',dsthost) AS recipient,
-> size FROM mail;
+--------------+---------------+---------------+---------+
| date_sent | sender | recipient | size |
+--------------+---------------+---------------+---------+
| May, 11,2006 | barb@saturn | tricia@mars | 58274 |
| May, 12,2006 | tricia@mars | gene@venus | 194925 |
| May, 12,2006 | phil@mars | phil@saturn | 1048 |
| May, 13,2006 | barb@saturn | tricia@venus | 271 |
| May, 14,2006 | gene@venus | barb@mars | 2291 |
| May, 14,2006 | phil@mars | tricia@saturn | 5781 |
| May, 14,2006 | barb@venus | barb@venus | 98151 |
| May, 14,2006 | tricia@saturn | phil@venus | 2394482 |
| May, 15,2006 | gene@mars | gene@saturn | 3824 |
| May, 15,2006 | phil@venus | phil@venus | 978 |
| May, 15,2006 | gene@mars | tricia@saturn | 998532 |
| May, 15,2006 | gene@saturn | gene@mars | 3856 |
| May, 16,2006 | gene@venus | barb@mars | 613 |
| May, 16,2006 | phil@venus | barb@venus | 10294 |
| May, 17,2006 | phil@mars | tricia@saturn | 873 |
| May, 19,2006 | gene@saturn | gene@venus | 23992 |
+--------------+---------------+---------------+---------+
16 rows in set (0.00 sec)
where语句中不可以使用别名
代码
mysql> SELECT t,srcuser,dstuser,size/1024 AS kilobytes
-> FROM mail WHERE size/1024 > 500; #>前后需要空格不然报错
+---------------------+---------+---------+-----------+
| t | srcuser | dstuser | kilobytes |
+---------------------+---------+---------+-----------+
| 2006-05-14 17:03:01 | tricia | phil | 2338.3613 |
| 2006-05-15 10:25:52 | gene | tricia | 975.1289 |
+---------------------+---------+---------+-----------+
2 rows in set (0.00 sec)
使用where语句与移除where语句的区别
没有where语句时，会遍历所有
其中‘0’ 是假
‘1’是真
代码
mysql> SELECT srcuser,dstuser,size FROM mail WHERE srcuser <'c' AND size > 5000;
+---------+---------+-------+
| srcuser | dstuser | size |
+---------+---------+-------+
| barb | tricia | 58274 |
| barb | barb | 98151 |
+---------+---------+-------+
2 rows in set (0.00 sec)
> SELECT srcuser,srcuser > 'c',dstuser,size, size > 5000 FROM mail;
mysql
+---------+---------------+---------+---------+-------------+
| srcuser | srcuser > 'c' | dstuser | size | size > 5000 |
+---------+---------------+---------+---------+-------------+
| barb | 0 | tricia | 58274 | 1 |
| tricia | 1 | gene | 194925 | 1 |
| phil | 1 | phil | 1048 | 0 |
| barb | 0 | tricia | 271 | 0 |
| gene | 1 | barb | 2291 | 0 |
| phil | 1 | tricia | 5781 | 1 |
| barb | 0 | barb | 98151 | 1 |
| tricia | 1 | phil | 2394482 | 1 |
| gene | 1 | gene | 3824 | 0 |
| phil | 1 | phil | 978 | 0 |
| gene | 1 | tricia | 998532 | 1 |
| gene | 1 | gene | 3856 | 0 |
| gene | 1 | barb | 613 | 0 |
| phil | 1 | barb | 10294 | 1 |
| phil | 1 | tricia | 873 | 0 |
| gene | 1 | gene | 23992 | 1 |
+---------+---------------+---------+---------+-------------+
16 rows in set (0.00 sec)
过滤重复信息，使得查询结果唯一化
DISTINCT
代码
mysql> SELECT srcuser FROM mail;
+---------+
| srcuser |
+---------+
| barb |
| tricia |
| phil |
| barb |
| gene |
| phil |
| barb |
| tricia |
| gene |
| phil |
| gene |
| gene |
| gene |
| phil |
| phil |
| gene |
+---------+
16 rows in set (0.00 sec)
> SELECT DISTINCT srcuser FROM mail;
#使用DISTINCT
mysql
+---------+
| srcuser |
+---------+
| barb |
| tricia |
| phil |
| gene |
+---------+
4 rows in set (0.00 sec)
统计不同结果个数 count()
代码
mysql> SELECT COUNT(DISTINCT srcuser) FROM mail;
+-------------------------+
| COUNT(DISTINCT srcuser) |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)