学习环境
- 虚拟机版本: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
上面两条语句实现的检索是一样的。有一些情况下是需要完全限定名的。