爱站程序员基地以“MySQL必知必会”开头的文章大部分是学习**《MySQL必知必会》**这本之前没看完的经典书籍的学习记录。
为什么会有这个系列：大二时的数据库专业课更倾向于理论学习、在WEB后端开发的数据库操作中大多使用的是ORM API，让我对具体的原生MySQL语句的掌握还存在一些不足，而且为了对MySQL语句语法进行一个比较全面的复习，所以才有了这个系列。
主要内容：会按照书中目录step by step进行，使用navicat可视化软件，根据目录的需求驱动，记录SQL语句、分析与结果。当然，这部分知识并不是孤立的，有时会结合python进行实现，会参考《高性能MySQL》、《数据库系统概论》等资料关联重要原理，还会对一些可用的ORM API的实例化操作进行记录。
一，连接数据库
使用python连接MySQL数据库：
先创建数据库：CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`email` varchar(255) COLLATE utf8_bin NOT NULL,`password` varchar(255) COLLATE utf8_bin NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_binAUTO_INCREMENT=1 ;连接并操作：import pymysql.cursors# Connect to the databaseconnection = pymysql.connect(host=\'localhost\',user=\'root\',password=\'root\',db=\'test\',port=3306,charset=\"utf8\",cursorclass=pymysql.cursors.DictCursor)try:with connection.cursor() as cursor:# Create a new recordsql = \"INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)\"cursor.execute(sql, (\'webmaster@python.org\', \'very-secret\'))# connection is not autocommit by default. So you must commit to save your changes.connection.commit()with connection.cursor() as cursor:# Read a single recordsql = \"SELECT `id`, `password` FROM `users` WHERE `email`=%s\"cursor.execute(sql, (\'webmaster@python.org\',))result = cursor.fetchone()print(result)finally:connection.close()打印：{\'id\': 1, \'password\': \'very-secret\'}
- 使用pymysql提供的参数化语句避免注入。
二，创建及使用数据库
1，创建数据库：
mysql> CREATE DATABASE crashcourse;Query OK, 1 row affected (0.10 sec)mysql> show create database crashcourse;+-------------+---------------------------------------------------------------------------------------------------------------------------------------+| Database | Create Database |+-------------+---------------------------------------------------------------------------------------------------------------------------------------+| crashcourse | CREATE DATABASE `crashcourse` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION=\'N\' */ |+-------------+---------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.02 sec)mysql> show databases; # 了解数据库，返回数据库列表+--------------------+| Database |+--------------------+| crashcourse || information_schema || messageboard || mysql || mysqlite3 || performance_schema || spyders || sys |+--------------------+8 rows in set (0.01 sec)
2，使用数据库：
mysql> use crashcourse; #指定使用的数据库Database changed
三，创建及使用数据表
1，创建数据表：
CREATE TABLE customers(cust_id int NOT NULL AUTO_INCREMENT,cust_name char(50) NOT NULL ,cust_address char(50) NULL ,cust_city char(50) NULL ,cust_state char(5) NULL ,cust_zip char(10) NULL ,cust_country char(50) NULL ,cust_contact char(50) NULL ,cust_email char(255) NULL ,PRIMARY KEY (cust_id)) ENGINE=InnoDB;mysql> sshow create table productnotes; #查看创建表productnotes表的mysql代码语句+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| productnotes | CREATE TABLE `productnotes` (`note_id` int NOT NULL AUTO_INCREMENT,`prod_id` char(10) NOT NULL,`note_date` datetime NOT NULL,`note_text` text,PRIMARY KEY (`note_id`),FULLTEXT KEY `note_text` (`note_text`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.03 sec)mysql> show tables;+-----------------------+| Tables_in_crashcourse |+-----------------------+| customers || orderitems || orders || productnotes || products || vendors |+-----------------------+6 rows in set (0.01 sec)
2，使用数据表：
插入数据：INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10001, \'Coyote Inc.\', \'200 Maple Lane\', \'Detroit\', \'MI\', \'44444\', \'USA\', \'Y Lee\', \'ylee@coyote.com\');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10002, \'Mouse House\', \'333 Fromage Lane\', \'Columbus\', \'OH\', \'43333\', \'USA\', \'Jerry Mouse\');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10003, \'Wascals\', \'1 Sunny Place\', \'Muncie\', \'IN\', \'42222\', \'USA\', \'Jim Jones\', \'rabbit@wascally.com\');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)VALUES(10004, \'Yosemite Place\', \'829 Riverside Drive\', \'Phoenix\', \'AZ\', \'88888\', \'USA\', \'Y Sam\', \'sam@yosemite.com\');INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)VALUES(10005, \'E Fudd\', \'4545 53rd Street\', \'Chicago\', \'IL\', \'54545\', \'USA\', \'E Fudd\');显示表列：mysql> show columns from customers;+--------------+-----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------+------+-----+---------+----------------+| cust_id | int | 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.02 sec)
3，使用
SHOW
查看一些内部信息：
show status; # 用于显示广泛的服务器状态信息show grants; #显示授予用户（所有用户或特定用户）的安全权限show errors; # 显示服务器错误内容show warnings； #显示服务器警告内容
四，使用SELECT检索数据
SELECT
的用途是从一个或多个表中检索信息。
1，检索单个列
使用SELECT，必须至少给出——想选择什么，以及从什么地方选择。
mysql> select prod_name from products; # 从products表中检索prod_name 单列+----------------+| 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.03 sec)
- 如果没有明确排序查询结果（下一章介绍），则返回的数据的顺序没有特殊意义。
2，检索多个列
在SELECT关键字后给出多个列名，列名之间必须以逗号分隔。
mysql> select prod_id,prod_name,prod_price from products; # 从products表中检索prod_name，prod_name,prod_price 多列+---------+----------------+------------+| prod_id | prod_name | prod_price |+---------+----------------+------------+| ANV01 | .5 ton anvil | 5.99 || ANV02 | 1 ton anvil | 9.99 || ANV03 | 2 ton anvil | 14.99 || DTNTR | Detonator | 13.00 || FB | Bird seed | 10.00 || FC | Carrots | 2.50 || FU1 | Fuses | 3.42 || JP1000 | JetPack 1000 | 35.00 || JP2000 | JetPack 2000 | 55.00 || OL1 | Oil can | 8.99 || SAFE | Safe | 50.00 || SLING | Sling | 4.49 || TNT1 | TNT (1 stick) | 2.50 || TNT2 | TNT (5 sticks) | 10.00 |+---------+----------------+------------+14 rows in set (0.03 sec)
- 从上述输出可以看到， SQL语句一般返回原始的、无格式的数据。数据的格式化是一个表示问题，而不是一个检索问题。
3，检索所有列
在实际列名的位置使用星号（
*
）通配符。
mysql> select * from products; # 从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.03 sec)
4，检索不同的行
使用
DISTINCT
关键字，此关键字指示MySQL只返回不同的值。
mysql> select prod_id,vend_id from products;+---------+---------+| prod_id | vend_id |+---------+---------+| ANV01 | 1001 || ANV02 | 1001 || ANV03 | 1001 || FU1 | 1002 || OL1 | 1002 || DTNTR | 1003 || FB | 1003 || FC | 1003 || SAFE | 1003 || SLING | 1003 || TNT1 | 1003 || TNT2 | 1003 || JP1000 | 1005 || JP2000 | 1005 |+---------+---------+14 rows in set (0.03 sec)mysql> select distinct vend_id from products;+---------+| vend_id |+---------+| 1001 || 1002 || 1003 || 1005 |+---------+4 rows in set (0.03 sec)
- DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id,prod_price，除非指定的两个列都不同，否则所有行都将被检索出来.
5，结果限制
1，返回第一行或前几行，可使用LIMIT子句：
mysql> select prod_name from products limit 5; #从第 0 行开始，返回前 5 行+--------------+| prod_name |+--------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || Detonator || Bird seed |+--------------+5 rows in set (0.03 sec)
- 带一个值的
LIMIT
总是从第一行开始，给出的数为返回的行数。
2，返回从行5开始的5行：
mysql> select prod_name from products limit 5,5;#从第 5 行开始，检索 5 行+--------------+| prod_name |+--------------+| Carrots || Fuses || JetPack 1000 || JetPack 2000 || Oil can |+--------------+5 rows in set (0.03 sec)
- 带两个值的LIMIT可以指定从行号为第一个值的位置开始。
mysql> select prod_name from products limit 4 OFFSET 3; #从第 3 行开始，检索 4 行+-----------+| prod_name |+-----------+| Detonator || Bird seed || Carrots || Fuses |+-----------+4 rows in set (0.03 sec)
- 这是MySQL 5支持LIMIT的另一种替代语法
6，使用完全限定的表名
使用完全限定的名字来引用列（同时使用表名和列字）。
select products.prod_name from products;等价于select prod_name from products;等价于select products.prod_name from crashcourse.products;
五，排序数据
使用SELECT语句的
ORDER BY子句
，ORDER BY子句取一个或多个列的名字，根据需要排序检索出的数据。
- SQL语句由子句构成，有些子句是必需的，而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。
mysql> select prod_name from products order by prod_name;+----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || Bird seed || Carrots || Detonator || Fuses || JetPack 1000 || JetPack 2000 || Oil can || Safe || Sling || TNT (1 stick) || TNT (5 sticks) |+----------------+14 rows in set (0.03 sec)
- 通常， ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。
六，按多个列排序
只要指定列名，列名之间用逗号分开即可。
mysql> select prod_id, prod_price,prod_name from products order by prod_price, prod_name; #先按价格，再按产品名排序+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| FC | 2.50 | Carrots || TNT1 | 2.50 | TNT (1 stick) || FU1 | 3.42 | Fuses || SLING | 4.49 | Sling || ANV01 | 5.99 | .5 ton anvil || OL1 | 8.99 | Oil can || ANV02 | 9.99 | 1 ton anvil || FB | 10.00 | Bird seed || TNT2 | 10.00 | TNT (5 sticks) || DTNTR | 13.00 | Detonator || ANV03 | 14.99 | 2 ton anvil || JP1000 | 35.00 | JetPack 1000 || SAFE | 50.00 | Safe || JP2000 | 55.00 | JetPack 2000 |+---------+------------+----------------+14 rows in set (0.03 sec)
七，指定排序方向
1，降序排列desc：
mysql> select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name; #先按价格降序排列，再按产品名升序排列+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| JP2000 | 55.00 | JetPack 2000 || SAFE | 50.00 | Safe || JP1000 | 35.00 | JetPack 1000 || ANV03 | 14.99 | 2 ton anvil || DTNTR | 13.00 | Detonator || FB | 10.00 | Bird seed || TNT2 | 10.00 | TNT (5 sticks) || ANV02 | 9.99 | 1 ton anvil || OL1 | 8.99 | Oil can || ANV01 | 5.99 | .5 ton anvil || SLING | 4.49 | Sling || FU1 | 3.42 | Fuses || FC | 2.50 | Carrots || TNT1 | 2.50 | TNT (1 stick) |+---------+------------+----------------+14 rows in set (0.03 sec)
- 若包含desc，则使用的降序，未用的默认为升序ASC
指定具体列的升降序
mysql> select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name desc; #先按价格降序排列，再按产品名降序排列+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| JP2000 | 55.00 | JetPack 2000 || SAFE | 50.00 | Safe || JP1000 | 35.00 | JetPack 1000 || ANV03 | 14.99 | 2 ton anvil || DTNTR | 13.00 | Detonator || TNT2 | 10.00 | TNT (5 sticks) || FB | 10.00 | Bird seed || ANV02 | 9.99 | 1 ton anvil || OL1 | 8.99 | Oil can || ANV01 | 5.99 | .5 ton anvil || SLING | 4.49 | Sling || FU1 | 3.42 | Fuses || TNT1 | 2.50 | TNT (1 stick) || FC | 2.50 | Carrots |+---------+------------+----------------+14 rows in set (0.03 sec)
- 在字典（ dictionary）排序顺序中，A被视为与a相同，这是MySQL（和大多数数据库管理系统）的默认行为。但是，许多数据库管理员能够在需要时改变这种行为（如果你的数据库包含大量外语字符，可能必须这样做）。
八，简单应用
使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。
mysql> select prod_price from products order by prod_price desc limit 1; # 最高值+------------+| prod_price |+------------+| 55.00 |+------------+1 row in set (0.13 sec)mysql> select prod_price from products order by prod_price asc limit 1; # 最低值+------------+| prod_price |+------------+| 2.50 |+------------+1 row in set (0.03 sec)
- 先排序，在限制取值。
