摘要：
未使用别名（alias） 未使用别名（alias）1mysql˃SELECTDATE_FORMAT(t,'%M%e,%Y'),2-˃srcuser,sizeFROMmail;3+----------------------------+---------+---------+4|DATE_FORMAT(t,'%M%e,%Y')|srcuser|size|5+----------------------------+----
1 mysql>SELECTDATE_FORMAT(t,'%M %e, %Y'),
2 ->srcuser, size FROMmail;
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 16rows inset(0.00sec)
代码1 mysql>SELECTDATE_FORMAT(t, '%M %e, %Y') AS'Date of message',
2 ->srcuser AS'Message sender', size AS'Number of bytes'FROMmail;
3 +-----------------+----------------+-----------------+4 |Date ofmessage |Message sender |Numberofbytes |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 16rows inset(0.00sec)
数字靠右边对齐
字符串靠左边对齐
‘1+1+1’ 表示 一个字符串
没加引号的1+1+1 是一个表达式
代码1 mysql>SELECT'1+1+1'AS'the expression', 1+1+1AS'The result';
2 +----------------+------------+3 |the expression |The result |4 +----------------+------------+5 |1+1+1|3|6 +----------------+------------+7 1row inset(0.00sec)
CONCAT（） 使用
代码mysql>SELECT->DATE_FORMAT(t,'%M, %e,%Y') ASdate_sent,
->CONCAT(srcuser,'@',srchost) ASsender,
->CONCAT(dstuser,'@',dsthost) ASrecipient,
->size FROMmail;
+--------------+---------------+---------------+---------+|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|+--------------+---------------+---------------+---------+16rows inset(0.00sec)
代码mysql>SELECTt,srcuser,dstuser,size/1024ASkilobytes
->FROMmail WHEREsize/1024>500; #>前后需要空格不然报错
+---------------------+---------+---------+-----------+|t |srcuser |dstuser |kilobytes |+---------------------+---------+---------+-----------+|2006-05-1417:03:01|tricia |phil |2338.3613||2006-05-1510:25:52|gene |tricia |975.1289|+---------------------+---------+---------+-----------+2rows inset(0.00sec)
没有where语句时，会遍历所有
其中‘0’ 是假
‘1’是真
代码mysql>SELECTsrcuser,dstuser,size FROMmail WHEREsrcuser <'c'ANDsize >5000;
+---------+---------+-------+|srcuser |dstuser |size |+---------+---------+-------+|barb |tricia |58274||barb |barb |98151|+---------+---------+-------+2rows inset(0.00sec)
>SELECTsrcuser,srcuser >'c',dstuser,size, size >5000FROMmail;
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|+---------+---------------+---------+---------+-------------+16rows inset(0.00sec)
DISTINCT
代码mysql>SELECTsrcuser FROMmail;
+---------+|srcuser |+---------+|barb ||tricia ||phil ||barb ||gene ||phil ||barb ||tricia ||gene ||phil ||gene ||gene ||gene ||phil ||phil ||gene |+---------+16rows inset(0.00sec)
>SELECTDISTINCTsrcuser FROMmail;
#使用DISTINCT
mysql
+---------+|srcuser |+---------+|barb ||tricia ||phil ||gene |+---------+4rows inset(0.00sec)
统计不同结果个数 count()
代码mysql>SELECTCOUNT(DISTINCTsrcuser) FROMmail;
+-------------------------+|COUNT(DISTINCTsrcuser) |+-------------------------+|4|+-------------------------+1row inset(0.00sec)