8.笔记 MySQL学习——检索信息

8.笔记 MySQL学习——检索信息

1.  相关表查找

查找表如下：

mysql> select * from student;

+--------+-----+------------+

| name  | sex | student_id |

+--------+-----+------------+

| Kyle  | M   |          1 |

| Abby  | F   |          2 |

| Joseph | M   |         3 |

+--------+-----+------------+

3 rows in set (0.00 sec)

mysql> select 2+2,'Hello,world' ,version();

+-----+-------------+-----------+

| 2+2 | Hello,world | version() |

+-----+-------------+-----------+

|   4| Hello,world | 5.7.10    |

+-----+-------------+-----------+

1 row in set (0.00 sec)

2.  指定检索条件

mysql> select * from score where score > 95;

+------------+----------+-------+

| student_id | event_id | score |

+------------+----------+-------+

|         5 |        3 |    97 |

|        18 |        3 |    96 |

|         1 |        6 |   100 |

|         5 |        6 |    97 |

|        11 |        6 |    98 |

|        16 |        6 |    98 |

+------------+----------+-------+

6 rows in set (0.00 sec)

mysql> select last_name,first_name,state frompresident where state='VA' or state='MA';

+------------+-------------+-------+

| last_name | first_name  | state |

+------------+-------------+-------+

| Washington | George      | VA   |

| Adams     | John        | MA    |

| Jefferson | Thomas      | VA    |

| Madison   | James       | VA    |

| Monroe    | James       | VA    |

| Adams     | John Quincy | MA    |

| Harrison  | William H.  | VA    |

| Tyler     | John        | VA    |

| Taylor    | Zachary     | VA    |

| Wilson    | Woodrow     | VA    |

| Kennedy   | John F.     | MA    |

| Bush      | George H.W. | MA    |

+------------+-------------+-------+

12 rows in set (0.00 sec)

3.  NULL值

mysql> select null<0,null=0,null<>0,null>0;

+---------+--------+---------+--------+

| null <0 | null=0 | null<>0 |null>0 |

+---------+--------+---------+--------+

|   NULL |   NULL |    NULL |  NULL |

+---------+--------+---------+--------+

1 row in set (0.00 sec)

NULL值是无值 或 未知值。

可以使用IS NULL 或IS NOT NULL 来查找NULL值。

4.  对查询结果排序

mysql> select last_name,first_name from presidentorder by last_name;

+------------+---------------+

| last_name | first_name    |

+------------+---------------+

| Adams     | John          |

| Adams     | John Quincy   |

| Arthur    | Chester A.    |

| Buchanan  | James         |

| Bush      | George W.     |

| Bush      | George H.W.   |

| Carter    | James E.      |

| Cleveland | Grover        |

| Clinton   | William J.    |

| Coolidge  | Calvin        |

| Eisenhower | Dwight D.     |

| Fillmore  | Millard       |

| Ford      | Gerald R.     |

| Garfield  | James A.      |

| Grant     | Ulysses S.    |

| Harding   | Warren G.     |

| Harrison  | William H.    |

| Harrison  | Benjamin      |

| Hayes     | Rutherford B. |

| Hoover    | Herbert C.    |

| Jackson   | Andrew        |

| Jefferson | Thomas        |

| Johnson   | Andrew        |

| Johnson   | Lyndon B.     |

| Kennedy   | John F.       |

| Lincoln   | Abraham       |

| Madison   | James         |

| McKinley  | William       |

| Monroe    | James         |

| Nixon     | Richard M.    |

| Obama     | Barack H.     |

| Pierce    | Franklin      |

| Polk      | James K.      |

| Reagan    | Ronald W.     |

| Roosevelt | Franklin D.   |

| Roosevelt | Theodore      |

| Taft      | William H.    |

| Taylor    | Zachary       |

| Truman    | Harry S       |

| Tyler     | John          |

| Van Buren | Martin        |

| Washington | George        |

| Wilson    | Woodrow       |

+------------+---------------+

43 rows in set (0.00 sec)

MYSQL 的 SQL语句和 ORACLE的还是很像的。

5.  限制查询

mysql> select last_name,first_name,birth frompresident order by birth limit 5;

+------------+------------+------------+

| last_name | first_name | birth      |

+------------+------------+------------+

| Washington | George     | 1732-02-22 |

| Adams     | John       | 1735-10-30 |

| Jefferson | Thomas     | 1743-04-13 |

| Madison   | James      | 1751-03-16 |

| Monroe    | James      | 1758-04-28 |

+------------+------------+------------+

5 rows in set (0.00 sec)

6.  对输出列进行计算和命名

mysql> select concat (first_name ,' ',last_name),concat (city,', ',state) from president;

+------------------------------------+--------------------------+

| concat (first_name ,' ',last_name) |concat (city,', ',state) |

+------------------------------------+--------------------------+

| George Washington                  | Wakefield, VA            |

| John Adams                         | Braintree, MA            |

| Thomas Jefferson                   | Albemarle County, VA     |

| James Madison                      | Port Conway, VA          |

| James Monroe                       | Westmoreland County,VA  |

| John Quincy Adams                  | Braintree, MA            |

| Andrew Jackson                     | Waxhaw settlement,SC    |

| Martin Van Buren                   | Kinderhook, NY           |

| William H. Harrison                | Berkeley, VA             |