SQL学习（选择数据库和检索数据）

学习环境

• 虚拟机版本：Ubuntu 18.04
• 安装好mysql server

    sudo apt-get install mysql-server

• 学习工具：MySQL Workbench
  下载后在虚拟机内打开应用
• 脚本

以下操作实际上均在终端上执行，但建议在MySQL Workbench上执行，图形化界面更利于交互，更容易理解数据库的结构。

了解数据库和表

 数据库、表、列、用户、权限、等的信息被存储在数据库和表中。不过内部的表一般不允许访问，可用Mysql的SHOW命令来显示这些信息（MySQL从内部表中来提取这些信息）

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| SQL_learning_DB    |
| mysql              |
| performance_schema |
| shiyanlou001       |
| sys                |
+--------------------+

返回可用数据库的一个列表。为了获得一个数据库内的表的列表，使用SHOW TABLES;但在这之前得先选一个想要查表的数据库，否则会报错
mysql> SHOW TABLES;
ERROR 1046 (3D000): No database selected

mysql> USE SQL_learning_DB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_SQL_learning_DB |
+---------------------------+
| customers                 |
| orderitems                |
| orders                    |
| productnotes              |
| products                  |
| vendors                   |
+---------------------------+
6 rows in set (0.00 sec)

也可以用SHOW来显示表列

mysql> SHOW COLUMNS FROM customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

也可以用DESCRIBE 作为SHOW COLUMNS的一种快捷方式。

mysql> DESCRIBE customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

检索数据

SELECT用来从一个列表或多个列表来检索信息，为了使用SELECT，必须给出两条信息——想选择什么，以及从什么地方选择。

检查单个列

mysql> SELECT prod_name FROM products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

这里因为没有明确排序查询结果，返回的数据的顺序是随机的。只要返回相同数目的行，就是正常的。

检查多个列

mysql> SELECT prod_id,vend_id,prod_name,prod_price FROM products;
+---------+---------+----------------+------------+
| prod_id | vend_id | prod_name      | prod_price |
+---------+---------+----------------+------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 |
| ANV02   |    1001 | 1 ton anvil    |       9.99 |
| ANV03   |    1001 | 2 ton anvil    |      14.99 |
| DTNTR   |    1003 | Detonator      |      13.00 |
| FB      |    1003 | Bird seed      |      10.00 |
| FC      |    1003 | Carrots        |       2.50 |
| FU1     |    1002 | Fuses          |       3.42 |
| JP1000  |    1005 | JetPack 1000   |      35.00 |
| JP2000  |    1005 | JetPack 2000   |      55.00 |
| OL1     |    1002 | Oil can        |       8.99 |
| SAFE    |    1003 | Safe           |      50.00 |
| SLING   |    1003 | Sling          |       4.49 |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 |
+---------+---------+----------------+------------+
14 rows in set (0.00 sec)

列名之间用逗号间隔即可。

检索所有列

mysql> SELECT * FROM products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

检索所有列用*表示。

检索不同的行

mysql> SELECT vend_id FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.00 sec)

mysql> SELECT DISTINCT vend_id FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.00 sec)

SELECT DISTINCT vend_id告诉MySQL只返回不同（唯一）的vend_id行，因此只返回4行

限定结果

SELECT 语句返回所有匹配的行，它们可能是制定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句。

mysql> SELECT DISTINCT vend_id FROM products LIMIT 2;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
+---------+
2 rows in set (0.00 sec)

LIMIT 2 指示MySQL返回不多于2行。

mysql> SELECT DISTINCT vend_id FROM products LIMIT 2,2;
+---------+
| vend_id |
+---------+
|    1003 |
|    1005 |
+---------+
2 rows in set (0.00 sec)

LIMIT 2,2 指示MySQL返回从行2开始的2行。列的第一行为行0。

使用完全限定的表名

SELECT products.prod_name FROM products;
SELECT products.prod_name FROM SQL_learning_DB.products

上面两条语句实现的检索是一样的。有一些情况下是需要完全限定名的。