MySql技术内幕之MySQL入门(2)
接上一篇。
mysql> source create_member.sql; # 创建member表
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.26 sec)
mysql> source insert_member.sql; # 插入member表数据
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.08 sec)
Query OK, 1 row affected (0.08 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.08 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.28 sec)
Query OK, 1 row affected (0.07 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.14 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.04 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
mysql> SHOW TABLES; # 列出默认数据库中的表
+------------------+
| Tables_in_sampdb |
+------------------+
| member |
| president |
+------------------+
3 rows in set (0.00 sec)
mysql> SHOW DATABASES; # 列出当前连接到服务器上的所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampdb |
| sys |
+--------------------+
5 rows in set (0.00 sec)
美史联盟表
以上创建了两个表：president表和member表。
下面简单介绍一下这两张表
president表
mysql> DESC president;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| last_name | varchar(15) | NO | | NULL | |
| first_name | varchar(15) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| city | varchar(20) | NO | | NULL | |
| state | varchar(2) | NO | | NULL | |
| birth | date | NO | | NULL | |
| death | date | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
姓名
出生地（城市和州）
出生日期
逝世日期
member表
mysql> DESC member;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| member_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| last_name | varchar(20) | NO | | NULL | |
| first_name | varchar(20) | NO | | NULL | |
| suffix | varchar(5) | YES | | NULL | |
| expiration | date | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| street | varchar(50) | YES | | NULL | |
| city | varchar(50) | YES | | NULL | |
| state | varchar(2) | YES | | NULL | |
| zip | varchar(10) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
| interests | varchar(255) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
ID编号
姓名
有效期
电子邮件地址
通信地址
电话号码
兴趣爱好
检索信息
mysql> SELECT * FROM president; # 检索全部信息
+------------+---------------+--------+---------------------+-------+------------+------------+
| last_name | first_name | suffix | city | state | birth | death |
+------------+---------------+--------+---------------------+-------+------------+------------+
| Washington | George | NULL | Wakefield | VA | 1732-02-22 | 1799-12-14 |
| Adams | John | NULL | Braintree | MA | 1735-10-30 | 1826-07-04 |
| Jefferson | Thomas | NULL | Albemarle County | VA | 1743-04-13 | 1826-07-04 |
| Madison | James | NULL | Port Conway | VA | 1751-03-16 | 1836-06-28 |
| Monroe | James | NULL | Westmoreland County | VA | 1758-04-28 | 1831-07-04 |
| Adams | John Quincy | NULL | Braintree | MA | 1767-07-11 | 1848-02-23 |
| Jackson | Andrew | NULL | Waxhaw settlement | SC | 1767-03-15 | 1845-06-08 |
| Van Buren | Martin | NULL | Kinderhook | NY | 1782-12-05 | 1862-07-24 |
| Harrison | William H. | NULL | Berkeley | VA | 1773-02-09 | 1841-04-04 |
| Tyler | John | NULL | Greenway | VA | 1790-03-29 | 1862-01-18 |
| Polk | James K. | NULL | Pineville | NC | 1795-11-02 | 1849-06-15 |
| Taylor | Zachary | NULL | Orange County | VA | 1784-11-24 | 1850-07-09 |
| Fillmore | Millard | NULL | Locke | NY | 1800-01-07 | 1874-03-08 |
| Pierce | Franklin | NULL | Hillsboro | NH | 1804-11-23 | 1869-10-08 |
| Buchanan | James | NULL | Mercersburg | PA | 1791-04-23 | 1868-06-01 |
| Lincoln | Abraham | NULL | Hodgenville | KY | 1809-02-12 | 1865-04-15 |
| Johnson | Andrew | NULL | Raleigh | NC | 1808-12-29 | 1875-07-31 |
| Grant | Ulysses S. | NULL | Point Pleasant | OH | 1822-04-27 | 1885-07-23 |
| Hayes | Rutherford B. | NULL | Delaware | OH | 1822-10-04 | 1893-01-17 |
| Garfield | James A. | NULL | Orange | OH | 1831-11-19 | 1881-09-19 |
| Arthur | Chester A. | NULL | Fairfield | VT | 1829-10-05 | 1886-11-18 |
| Cleveland | Grover | NULL | Caldwell | NJ | 1837-03-18 | 1908-06-24 |
| Harrison | Benjamin | NULL | North Bend | OH | 1833-08-20 | 1901-03-13 |
| McKinley | William | NULL | Niles | OH | 1843-01-29 | 1901-09-14 |
| Roosevelt | Theodore | NULL | New York | NY | 1858-10-27 | 1919-01-06 |
| Taft | William H. | NULL | Cincinnati | OH | 1857-09-15 | 1930-03-08 |
| Wilson | Woodrow | NULL | Staunton | VA | 1856-12-19 | 1924-02-03 |
| Harding | Warren G. | NULL | Blooming Grove | OH | 1865-11-02 | 1923-08-02 |
| Coolidge | Calvin | NULL | Plymouth Notch | VT | 1872-07-04 | 1933-01-05 |
| Hoover | Herbert C. | NULL | West Branch | IA | 1874-08-10 | 1964-10-20 |
| Roosevelt | Franklin D. | NULL | Hyde Park | NY | 1882-01-30 | 1945-04-12 |
| Truman | Harry S | NULL | Lamar | MO | 1884-05-08 | 1972-12-26 |
| Eisenhower | Dwight D. | NULL | Denison | TX | 1890-10-14 | 1969-03-28 |
| Kennedy | John F. | NULL | *line | MA | 1917-05-29 | 1963-11-22 |
| Johnson | Lyndon B. | NULL | Stonewall | TX | 1908-08-27 | 1973-01-22 |
| Nixon | Richard M. | NULL | Yorba Linda | CA | 1913-01-09 | 1994-04-22 |
| Ford | Gerald R. | NULL | Omaha | NE | 1913-07-14 | 2006-12-26 |
| Carter | James E. | Jr. | Plains | GA | 1924-10-01 | NULL |
| Reagan | Ronald W. | NULL | Tampico | IL | 1911-02-06 | 2004-06-05 |
| Bush | George H.W. | NULL | Milton | MA | 1924-06-12 | NULL |
| Clinton | William J. | NULL | Hope | AR | 1946-08-19 | NULL |
| Bush | George W. | NULL | New Haven | CT | 1946-07-06 | NULL |
| Obama | Barack H. | NULL | Honolulu | HI | 1961-08-04 | NULL |
+------------+---------------+--------+---------------------+-------+------------+------------+
43 rows in set (0.00 sec)
mysql> SELECT first_name FROM president; # 检索单个信息
+---------------+
| first_name |
+---------------+
| George |
| John |
| Thomas |
| James |
| James |
| John Quincy |
| Andrew |
| Martin |
| William H. |
| John |
| James K. |
| Zachary |
| Millard |
| Franklin |
| James |
| Abraham |
| Andrew |
| Ulysses S. |
| Rutherford B. |
| James A. |
| Chester A. |
| Grover |
| Benjamin |
| William |
| Theodore |
| William H. |
| Woodrow |
| Warren G. |
| Calvin |
| Herbert C. |
| Franklin D. |
| Harry S |
| Dwight D. |
| John F. |
| Lyndon B. |
| Richard M. |
| Gerald R. |
| James E. |
| Ronald W. |
| George H.W. |
| William J. |
| George W. |
| Barack H. |
+---------------+
43 rows in set (0.00 sec)
mysql> SELECT first_name, last_name FROM president; # 检索多个信息
+---------------+------------+
| first_name | last_name |
+---------------+------------+
| George | Washington |
| John | Adams |
| Thomas | Jefferson |
| James | Madison |
| James | Monroe |
| John Quincy | Adams |
| Andrew | Jackson |
| Martin | Van Buren |
| William H. | Harrison |
| John | Tyler |
| James K. | Polk |
| Zachary | Taylor |
| Millard | Fillmore |
| Franklin | Pierce |
| James | Buchanan |
| Abraham | Lincoln |
| Andrew | Johnson |
| Ulysses S. | Grant |
| Rutherford B. | Hayes |
| James A. | Garfield |
| Chester A. | Arthur |
| Grover | Cleveland |
| Benjamin | Harrison |
| William | McKinley |
| Theodore | Roosevelt |
| William H. | Taft |
| Woodrow | Wilson |
| Warren G. | Harding |
| Calvin | Coolidge |
| Herbert C. | Hoover |
| Franklin D. | Roosevelt |
| Harry S | Truman |
| Dwight D. | Eisenhower |
| John F. | Kennedy |
| Lyndon B. | Johnson |
| Richard M. | Nixon |
| Gerald R. | Ford |
| James E. | Carter |
| Ronald W. | Reagan |
| George H.W. | Bush |
| William J. | Clinton |
| George W. | Bush |
| Barack H. | Obama |
+---------------+------------+
43 rows in set (0.00 sec)
指定检索条件
mysql> SELECT last_name, first_name FROM president
-> WHERE last_name = 'ROOSEVELT'; ＃ 指定检索条件，last_name = 'ROOSEVELT'
+-----------+-------------+
| last_name | first_name |
+-----------+-------------+
| Roosevelt | Theodore |
| Roosevelt | Franklin D. |
+-----------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT last_name, first_name FROM president
-> WHERE last_name = 'roosevelt'; # 字符集通常不区分大小写
+-----------+-------------+
| last_name | first_name |
+-----------+-------------+
| Roosevelt | Theodore |
| Roosevelt | Franklin D. |
+-----------+-------------+
2 rows in set (0.00 sec)