mysql 检索信息
select语句用来从数据表中检索信息；其一般格式为：
SELECT what_to_select FROW which_table WHERE conditions_to_satisfy;
what_to_select ：指出你想要看见的内容，可以是列的一个表，或*表示“所有的列”。
which_table ：指出你要检索数据的表。
conditions_to_satisfy :WHERE子句是可选项，如果选择该项，则conditions_to_satify指定行必须满足的检索条件。
1，查看整张表的数据
mysql>select * from student;
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
| id | fName | lName | age | sex | phone | city | state | createDate |
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
| 1 | 四 | 李 | 22 | 女 | 13223231123 | 成都市 | 四川省 | 2017-05-29 00:00:00 |
| 2 | 五 | 王 | 33 | 男 | 13900002222 | 成都市 | 四川省 | 2017-05-29 00:00:00 |
| 3 | Joy | Mark | 18 | 女 | 17620201010 | 成都市 | 四川省 | 2017-05-29 00:00:00 |
| 4 | Wong | Kiwin | 18 | 男 | 13528282323 | 成都市 | 四川省 | 2017-05-29 00:00:00 |
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
4 rows in set (0.00 sec)
2，查看指定行的数据(查看的是lName列中‘李’所在行的整行数据)
mysql> select * from student where lName='李';
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
| id | fName | lName | age | sex | phone | city | state | createDate |
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
| 1 | 四 | 李 | 22 | 女 | 13223231123 | 成都市 | 四川省 | 2017-05-29 00:00:00 |
+----+-------+-------+------+------+-------------+--------+--------+---------------------+
1 row in set (0.00 sec)
3，查看指定列的数据(查看的是lName列的整列数据)
mysql> select lName from student;
+-------+
| lName |
+-------+
| 李 |
| 王 |
| Mark |
| Kiwin |
+-------+
4 rows in set (0.00 sec)
4，查看具体位置的数据(查看的是lName列中的'王')
mysql> select lName from student where lName='王';
+-------+
| lName |
+-------+
| 王 |
+-------+
1 row in set (0.00 sec)
5，查看满足一定条件限制的数据(查看的是age>20对应的fName和lName的数据)
mysql> select fName,lName from student where age>20; +-------+-------+ | fName | lName | +-------+-------+ | 四 | 李 | | 五 | 王 | +-------+-------+ 2 rows in set (0.00 sec)
6,查看满足组合条件的数据（age>20 的 女生）
mysql> select fName,lName from student where (age>20 and sex='女'); +-------+-------+ | fName | lName | +-------+-------+ | 四 | 李 | +-------+-------+ 1 row in set (0.00 sec)
7,去除重复数据的关键字：distinct(当distinct关键字同时修饰几个字段时，以重复数据最少的字段为准)
mysql> select distinct createDate from student;
+---------------------+
| createDate |
+---------------------+
| 2017-05-29 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select distinct sex from student; +------+ | sex | +------+ | 女 | | 男 | +------+ 2 rows in set (0.00 sec)
mysql> select distinct sex,lName from student;
+------+-------+
| sex | lName |
+------+-------+
| 女 | 李 |
| 男 | 王 |
| 女 | Mark |
| 男 | Kiwin |
+------+-------+
4 rows in set (0.00 sec)
mysql> select distinct sex,createDate from student;
+------+---------------------+
| sex | createDate |
+------+---------------------+
| 女 | 2017-05-29 00:00:00 |
| 男 | 2017-05-29 00:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> select distinct createDate,sex from student;
+---------------------+------+
| createDate | sex |
+---------------------+------+
| 2017-05-29 00:00:00 | 女 |
| 2017-05-29 00:00:00 | 男 |
+---------------------+------+
2 rows in set (0.00 sec)
8，以某个字段排序：order by(binary区分大小写)字段 desc(降序)/asc(升序)
mysql> select fName,age,createDate from student order by age;//默认为升序 +-------+------+---------------------+ | fName | age | createDate | +-------+------+---------------------+ | Joy | 18 | 2017-05-29 00:00:00 | | Wong | 18 | 2017-05-29 00:00:00 | | 四 | 22 | 2017-05-29 00:00:00 | | 五 | 33 | 2017-05-29 00:00:00 | +-------+------+---------------------+ 4 rows in set (0.00 sec) mysql> select fName,age,createDate from student order by age desc; +-------+------+---------------------+ | fName | age | createDate | +-------+------+---------------------+ | 五 | 33 | 2017-05-29 00:00:00 | | 四 | 22 | 2017-05-29 00:00:00 | | Joy | 18 | 2017-05-29 00:00:00 | | Wong | 18 | 2017-05-29 00:00:00 | +-------+------+---------------------+ 4 rows in set (0.00 sec)
//其中会先将age按照默认的升序排列，再在age相同的范围内对createDate按降序排序
mysql> select age,phone,createDate from student order by age,createDate desc;
+------+-------------+---------------------+
| age | phone | createDate |
+------+-------------+---------------------+
| 18 | 17620201010 | 2015-09-19 00:00:00 |
| 18 | 13528282323 | 2007-12-30 00:00:00 |
| 22 | 13223231123 | 2017-05-29 00:00:00 |
| 33 | 13900002222 | 2016-08-20 00:00:00 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)
//先将age按默认升序排列，再在age相同的情况下对createDate按降序排序，最后在createDate相同的情况下对phone按升序排序
mysql> select age,phone,createDate from student order by age,createDate desc,phone asc;
+------+-------------+---------------------+
| age | phone | createDate |
+------+-------------+---------------------+
| 18 | 13223231123 | 2017-05-29 00:00:00 |
| 18 | 17620201010 | 2017-05-29 00:00:00 |
| 18 | 13528282323 | 2007-12-30 00:00:00 |
| 33 | 13900002222 | 2016-08-20 00:00:00 |
+------+-------------+---------------------+
4 rows in set (0.00 sec)
9，日期计算
mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet; +----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Whistler| 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Puffball| 1999-03-30 | 2003-08-19 | 4 | +----------+------------+------------+------+ 此处，YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值 的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。整个表达式有些难懂， 使用alias (age)来使输出的列标记更有意义。
