学习总结
（1）GROUP BY子句的位置一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）
1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
当被聚合的键中，包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来，也就是字段中为NULL的数据会被聚合为一组。
（2）在使用DISTINCT 时，NULL也被视为一类数据。NULL存在于多行中时，会被合并为一条NULL数据。
（3）不能对NULL使用任何比较运算符，只能通过IS NULL语句来判断。
（4）为什么ORDER BY中可以使用SELECT定义的别名呢？
这是因为在MySQL中，ORDER BY的执行次序在SELECT之后。
（5）本次主要是复习mysql，主要注意Pymysql的使用。
文章目录
- 学习总结
- 一、 Ubuntu下安装MySQL
- 1.1 安装
- 1.2 配置MySQL的安全性
- 1.3 以root用户登录
- 1.4 修改密码
- 1.5 撤销用户授权
- 1.6 删除用户
- 2.0 数据库的创建
- 2.1 表的创建
- 2.2 表的删除
- 2.3 表的更新
- 3.1 算术运算符
- 3.2 比较运算符
- 3.3 逻辑运算符
- 4.1 聚合函数
- 4.2 对表分组
- 4.3 使用WHERE语句
- 4.4 为聚合结果指定条件（having）
- 4.5 对表的查询结果进行排序
- 5.1 插入默认值
- 5.2 数据的删除
- 5.3 数据的更新
- 6.1 安装pymysql
- 6.2 连接数据库
- 6.3 创建游标
- 6.4 类方法
- 6.5 实战
- （1）创建表和插入数据。
- （2）在示例1的基础上，执行查询。
- （3）SQL注入的问题
本文属于新闻推荐实战—数据层—构建物料池之MySQL。MySQL数据库在该项目中会用来存储结构化的数据（用户、新闻特征），作为算法工程师需要了解常用的MySQL语法（比如增删改查，排序等），因为在实际的工作经常会用来统计相关数据或者抽取相关特征。
一、 Ubuntu下安装MySQL
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
安装教程是在Ubuntu20.04下进行的，安装的MySQL版本为8.0.27。
1.1 安装
sudo apt install mysql-server mysql-client
在输入密码后，再输入yes即可开始安装。
安装完成后，通过运行命令mysql -V查看版本号：
lyons@ubuntu:~$ mysql -V
mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
验证MySQL服务正在运行，命令行下输入：
sudo service mysql status
如果正在运行，则会显示：
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2021-10-27 10:27:59 CST; 9h ago
Main PID: 6179 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 4599)
Memory: 348.9M
CGroup: /system.slice/mysql.service
└─6179 /usr/sbin/mysqld
10月 27 10:27:59 ubuntu systemd[1]: Starting MySQL Community Server...
10月 27 10:27:59 ubuntu systemd[1]: Started MySQL Community Server.
1.2 配置MySQL的安全性
（1）首先，运行命令mysql_secure_installation：
sudo mysql_secure_installation
（2）VALIDATE PASSWORD COMPONENT 设置验证密码插件。它被用来测试MySQL用户的密码强度，并且提高安全性。如果想设置验证密码插件，请输入y：
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
接下来，将进行密码验证等级设置，根据数字设置对应等级，这里设置为0：
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
（3）设置密码
为MySQL root用户设置密码，设置过程中密码不会显示。如果设置了验证密码插件，将会显示密码的强度。
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
（4）移除匿名用户
默认情况下，MySQL安装有一个匿名用户，允许任何人登录MySQL，而不必为他们创建用户帐户。输入y进行删除：
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
（5）禁止远程root用户登录
输入y后按enter，将会禁止root用户登录。
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
（6）删除测试库
输入y后按enter，将会删除测试库。
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
（7）重新加载特权表
输入y后按enter，将会重新加载特权表。
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
至此，配置完成。
1.3 以root用户登录
在MySQL 8.0上，root 用户默认通过auth_socket插件授权。auth_socket插件通过 Unix socket 文件来验证所有连接到localhost的用户。
这意味着你不能通过提供密码，验证为 root。此时，输入mysql -uroot -p可能会被拒绝访问：
lyons@ubuntu:~$ mysql -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
若要以 root 用户身份登录 MySQL服务器，输入sudo mysql，如下：
# 登录密码为linux系统用户的root密码
lyons@ubuntu:~$ sudo mysql
[sudo] lyons 的密码：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
退出MySQL，请输入exit命令：
mysql> exit
Bye
lyons@ubuntu:~$
如果你想以 root 身份登录 MySQL 服务器，便于使用其他的程序。可以将验证方法从auth_socket修改成mysql_native_password。
你可以通过运行下面的命令实现：
-- 语法中的'你的密码’指的是你自己设置的登录密码，可设置为字母数字组合。
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
FLUSH PRIVILEGES;
示例：
-- 在mysql下，将密码设置为'mysql123'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql123';
Query OK, 0 rows affected (0.00 sec)
-- 刷新系统权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
-- 现在便可以通过mysql -uroot -p登录
-- 登录密码为前面设置的'mysql123'
lyons@ubuntu:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> exit
Bye
-- 同时，命令sudo mysql会被拒绝访问
lyons@ubuntu:~$ sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
当然，若要再次修改回sudo mysql的方式来登录root用户，方法类似：
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket BY '你的密码';
FLUSH PRIVILEGES;
- 方式2
推荐的选项，就是创建一个新的独立管理用户，拥有所有数据库的访问权限：
# 创建用户
CREATE USER '用户名'@'localhost' identified by '你的密码'
# 赋予admin用户全部的权限，你也可以只授予部分权限
GRANT ALL PRIVILEGES ON *.* TO '用户名'@'localhost';
示例：
# 创建名为admin的用户，密码为mysql123
mysql> create user 'admin'@'localhost' identified by 'mysql123';
Query OK, 0 rows affected (0.01 sec)
# 将访问所有database以及表的权利授权用户admin
#with gran option表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限
mysql> grant all privileges on *.* to 'admin'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 查看已有的用户
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| admin | localhost |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
# 退出root用户登录
mysql> exit
Bye
# 登录admin用户，输入密码mysql123即可登录成功
lyons@ubuntu:~$ mysql -uadmin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
说明：'admin'@'localhost'中，localhost指本地才可连接，可以将其换成%指任意ip都能连接，也可以指定ip连接。
1.4 修改密码
将用户admin的登录密码修改为mysql321：
ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql321';
1.5 撤销用户授权
# 查看用户的权限
show grants for 'admin'@'localhost';
# 撤销用户的权限
# 用户有什么权限就撤销什么
revoke all privileges on *.* from 'admin'@'localhost';
1.6 删除用户
drop user 'admin'@'localhost';
注：MySQL 8.0版本和5.0部分命令有所改掉，上述语法都是在8.0版本下运行通过的；请务必检查自己的MySQL版本号。
二、表的基本操作
2.0 数据库的创建
通过CREATE命令，可以创建指定名称的数据库，语法结构如下：
CREATE DATABASE [IF NOT EXISTS] <数据库名称>;
MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
MySQL下不运行存在两个相同名字的数据库，否则会报错。如果使用IF NOT EXISTS（可选项），可以避免此类错误。
示例：
-- 创建名为shop的数据库。
CREATE DATABASE shop;
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。
2.1 表的创建
创建表的语法结构如下：
CREATE TABLE <表名> （<字段1> <数据类型> <该列所需约束>，
<字段2> <数据类型> <该列所需约束>，
<字段3> <数据类型> <该列所需约束>，
<字段4> <数据类型> <该列所需约束>，
.
.
.
<该表的约束1>， <该表的约束2>，……）；
示例：
-- 创建一个名为Product的表
CREATE TABLE Product(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INT,
purchase_price INT,
regist_date DATE,
PRIMARY KEY (product_id)
);
不同的数据类型：
-
CHAR为定长字符，这里CHAR旁边括号里的数字表示该字段最长为多少字符，少于该数字将会使用空格进行填充。 -
VARCHAR表示变长字符，括号里的数字表示该字段最长为多少字符，存储时只会按照字符的实际长度来存储，但会使用额外的1-2字节来存储值长度。
2.2 表的删除
删除表的语法结构如下：
DROP TABLE <表名>;
-- 例如：DROP TABLE Product;
说明：通过DROP删除的表示无法恢复的，在删除表的时候请谨慎。
2.3 表的更新
通过ALTER TABLE语句，我们可以对表字段进行不同的操作，下面通过示例来具体学习用法。
示例：
- 创建一张名为Student的表
CREATE TABLE Student(
id INT PRIMARY KEY,
name CHAR(15)
);
DESC student;
-- 结果如下
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | char(15) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
- 更改表名
通过RENAME命令，将表名从Student => Students。
ALTER TABLE Student RENAME Students;
- 插入新的字段
通过ADD命令，新增字段sex和age。
-- 不同的字段通过逗号分开
ALTER TABLE Students ADD sex CHAR(1), ADD age INT;
其它插入技巧：
-- 通过FIRST在表首插入字段stu_num
ALTER TABLE Students ADD stu_num INT FIRST;
-- 指定在字段sex后插入字段height
ALTER TABLE Students ADD height INT AFTER sex;
DESC Students;
-- 结果如下
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int | YES | | NULL | |
| id | int | NO | PRI | NULL | |
| name | char(15) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| height | int | YES | | NULL | |
| age | int | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
- 字段的删除
通过DROP命令，可以对不在需要的字段进行删除。
-- 删除字段stu_num
ALTER TABLE Students DROP stu_num;
- 字段的修改
通过MODIFY修改字段的数据类型。
-- 修改字段age的数据类型
ALTER TABLE Students MODIFY age CHAR(3);
通过CHANGE命令，修改字段名或类型
-- 修改字段name为stu_name，不修改数据类型
ALTER TABLE Students CHANGE name stu_name CHAR(15);
-- 修改字段sex为stu_sex，数据类型修改为int
ALTER TABLE Students CHANGE sex stu_sex INT;
DESC Students;
-- 结果如下
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| stu_name | char(20) | YES | | NULL | |
| stu_sex | int | YES | | NULL | |
| height | int | YES | | NULL | |
| age | char(3) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1. 常数的查询
SELECT子句中，除了可以写字段外，还可以写常数。
SELECT
'商品' AS string,
'2009-05-24' AS date,
product_id,
product_name
FROM Product;
-- 结果如下
+--------+------------+------------+--------------+
| string | date | product_id | product_name |
+--------+------------+------------+--------------+
| 商品 | 2009-05-24 | 0001 | T恤衫 |
| 商品 | 2009-05-24 | 0002 | 打孔器 |
| 商品 | 2009-05-24 | 0003 | 运动T恤 |
| 商品 | 2009-05-24 | 0004 | 菜刀 |
| 商品 | 2009-05-24 | 0005 | 高压锅 |
| 商品 | 2009-05-24 | 0006 | 叉子 |
| 商品 | 2009-05-24 | 0007 | 擦菜板 |
| 商品 | 2009-05-24 | 0008 | 圆珠笔 |
+--------+------------+------------+--------------+
8 rows in set (0.00 sec)
2. 删除重复行
在SELECT语句中使用DISTINCT可以去除重复行。
SELECT
DISTINCT regist_date
FROM Product;
-- 结果如下
+-------------+
| regist_date |
+-------------+
| 2009-09-20 |
| 2009-09-11 |
| NULL |
| 2009-01-15 |
| 2008-04-28 |
| 2009-11-11 |
+-------------+
6 rows in set (0.01 sec)
在使用DISTINCT 时，NULL也被视为一类数据。NULL存在于多行中时，会被合并为一条NULL数据。
还可以通过组合使用，来去除列组合重复的数据。DISTINCT关键字只能用在第一个列名之前。
SELECT
DISTINCT product_type, regist_date
FROM Product;
-- 结果如下，列出了所有的组合
+--------------+-------------+
| product_type | regist_date |
+--------------+-------------+
| 衣服 | 2009-09-20 |
| 办公用品 | 2009-09-11 |
| 衣服 | NULL |
| 厨房用具 | 2009-09-20 |
| 厨房用具 | 2009-01-15 |
| 厨房用具 | 2008-04-28 |
| 办公用品 | 2009-11-11 |
+--------------+-------------+
7 rows in set (0.00 sec)
2.4 表的复制
表的复制可以将表结构与表中的数据全部复制，或者只复制表的结构。
-- 将整个表复制过来
CREATE TABLE Product_COPY1
SELECT * FROM Product;
SELECT * FROM Product_COPY1;
-- 结果如下
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
-- 通过LIKE复制表结构
CREATE TABLE Product_COPY2
LIKe Product;
SELECT * FROM Product_COPY2;
-- 结果如下
Empty set (0.00 sec) -- 表为空的
DESC Product_COPY2;
-- 结果如下
-- 表结构已复制过来
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | 0 | |
| purchase_price | int | YES | | NULL | |
| regist_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
三、运算符
3.1 算术运算符
我们可以在SELECT语句中使用计算表达式：
SELECT
product_name,
sale_price,
sale_price * 2 AS "sale_price_x2"
FROM Product;
-- 结果如下
+--------------+------------+---------------+
| product_name | sale_price | sale_price_x2 |
+--------------+------------+---------------+
| T恤衫 | 1000 | 2000 |
| 打孔器 | 500 | 1000 |
| 运动T恤 | 4000 | 8000 |
| 菜刀 | 3000 | 6000 |
| 高压锅 | 6800 | 13600 |
| 叉子 | 500 | 1000 |
| 擦菜板 | 880 | 1760 |
| 圆珠笔 | 100 | 200 |
+--------------+------------+---------------+
8 rows in set (0.00 sec)
- 四则运算所使用的运算符**(+、-、*、/）**称为算术运算符。
- 在运算表达式中，也可以使用**()**，括号中的运算表达式优先级会得到提升。
- NULL的计算结果，仍然还是NULL。
3.2 比较运算符
在 WHERE 子句中通过使用比较运算符可以组合出各种各样的条件表达式。
SELECT product_name, product_type
FROM Product
WHERE sale_price = 500;
常见比较运算符如下表：
运算符
| 含义
|
=
| 相等
|
<>
| 不相等
|
>=
| 大于等于
|
>
| 大于
|
<=
| 小于等于
|
<
| 小于
|
- 不能对NULL使用任何比较运算符，只能通过
IS NULL语句来判断：
SELECT
product_name,
purchase_price
FROM Product
WHERE purchase_price IS NULL;
希望选取不是 NULL 的记录时，需要使用IS NOT NULL运算符。
MySQL中字符串的排序与数字不同，典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。例如：
'1' < '10' < '11' < '2' < '222' < '3'
3.3 逻辑运算符
（1）使用NOT否认某一条件：
SELECT
product_name,
product_type,
sale_price
FROM Product
WHERE NOT sale_price >= 1000;
（2）AND运算符合OR运算符
SELECT product_type, sale_price
FROM Product
WHERE product_type = '厨房用具'
AND sale_price >= 3000;
-- 结果如下
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 厨房用具 | 3000 |
| 厨房用具 | 6800 |
+--------------+------------+
2 rows in set (0.00 sec)
SELECT product_type, sale_price
FROM Product
WHERE product_type = '厨房用具'
OR sale_price >= 3000;
-- 结果如下
+--------------+------------+
| product_type | sale_price |
+--------------+------------+
| 衣服 | 4000 |
| 厨房用具 | 3000 |
| 厨房用具 | 6800 |
| 厨房用具 | 500 |
| 厨房用具 | 880 |
+--------------+------------+
5 rows in set (0.00 sec)
（3）逻辑运算符和真值
- 符NOT、AND 和 OR 称为逻辑运算符；
- 真值就是值为真（TRUE）或假 （FALSE）；
- 在查询NULL时，SQL中存在第三种真值，不确定（UNKNOWN），NULL和任何值做逻辑运算结果都是不确定；
- 考虑 NULL 时的条件判断也会变得异常复杂，因此尽量给字段加上NOT NULL的约束。
四、分组查询
4.1 聚合函数
通过 SQL 对数据进行某种操作或计算时需要使用函数。
-
COUNT：计算表中的记录数（行数） -
SUM： 计算表中数值列中数据的合计值 -
AVG： 计算表中数值列中数据的平均值 -
MAX： 求出表中任意列中数据的最大值 -
MIN： 求出表中任意列中数据的最小值
示例：
-- 计算全部数据的行数
SELECT COUNT(*) FROM Product;
-- 结果如下
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
注意点1：除了COUNT可以将*作为参数，其它的函数均不可以。
-- 计算最高的销售价格
SELECT MAX(sale_price) FROM Product;
-- 结果如下
+-----------------+
| MAX(sale_price) |
+-----------------+
| 680000 |
+-----------------+
1 row in set (0.00 sec)
注意点2：当将字段名作为参数传递给函数时，只会计算不包含NULL的行。
示例：
-- purchase_price字段是包含NULL值的
SELECT purchase_price FROM Product;
-- 结果如下
+----------------+
| purchase_price |
+----------------+
| 500 |
| 320 |
| 2800 |
| 700 |
| 1250 |
| NULL |
| 198 |
| NULL |
+----------------+
8 rows in set (0.00 sec)
以*为参数传递给COUNT函数
SELECT COUNT(*) FROM Product;
-- 结果如下
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
以purchase_price为参数传递给COUNT函数
SELECT COUNT(purchase_price) FROM Product;
-- 结果如下
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
| 6 |
+-----------------------+
1 row in set (0.00 sec)
可以看到结果并不一样，函数忽略了值为NULL的行。
SUM，AVG函数时也一样，计算时会直接忽略，并不会当做0来处理！特别注意AVG函数，计算时分母也不会算上NULL行。
注意点3：MAX/MIN函数几乎适用于所有数据类型的列，包括字符和日期。SUM/AVG函数只适用于数值类型的列。
注意点4：在聚合函数删除重复值
SELECT COUNT(DISTINCT product_type)
FROM Product;
-- 结果如下
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
| 3 |
+------------------------------+
1 row in set (0.01 sec)
DISTINCT必须写在括号中。这是因为必须要在计算行数之前删除 product_type 字段中的重复数据。
4.2 对表分组
如果对Python的Pandas熟悉，那么大家应该很了解groupby函数，可以根据指定的列名，对表进行分组。在MySQL中，也存在同样作用的函数，即GROUP BY。
语法结构如下：
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;
示例：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
-- 结果如下
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+--------------+----------+
3 rows in set (0.01 sec)
- 在该语句中，我们首先通过
GROUP BY函数对指定的字段product_type进行分组。分组时，product_type字段中具有相同值的行会汇聚到同一组。 - 最后通过
COUNT函数，统计不同分组的包含的行数。
简单来理解：
- 例如做操时，老师将不同身高的同学进行分组，相同身高的同学会被分到同一组，分组后我们又统计了每个小组的学生数。
- 将这里的同学可以理解为表中的一行数据，身高理解为表的某一字段。
- 分组操作就是
GROUP BY，GROUP BY后面接的字段等价于按照身高分组，统计学生数就等价于在SELECT后用了COUNT(*)函数。
注意：GROUP BY子句的位置一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）
1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
当被聚合的键中，包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来，也就是字段中为NULL的数据会被聚合为一组。
4.3 使用WHERE语句
在对表进行分组之前，也可以是先使用WHERE对表进行条件过滤，然后再进行分组处理。语法结构如下：
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE
GROUP BY <列名1>, <列名2>, <列名3>, ……;
示例：
-- WHERE语句先将表中类型为衣服的行筛选出来
-- 然后再按照purchase_price来进行分组
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
-- 结果如下
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 2800 | 1 |
+----------------+----------+
2 rows in set (0.01 sec)
该语法实际的执行顺序为：
FROM → WHERE → GROUP BY → SELECT
- 使用
GROUP BY子句时，SELECT子句中不能出现聚合键之外的字段名。即，若GROUP BY选中purchase_price字段进行分组，则在SELECT语句中只能选中purchase_price字段，其它字段如product_id等均不行。 -
WHERE语句中，不可以使用聚合函数。WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件。即WHERE MAX(purchase_price) > 1000这样的语句是非法的。
4.4 为聚合结果指定条件（having）
前面提到了WHERE语句中不能使用聚合函数，但是实际操作时需要通过聚合函数来进行过滤怎么办呢？这就要用到HAVING语句了。语法结构如下：
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>
在HAVING的子句中能够使用的 3 种要素如下所示：
● 常数
● 聚合函数
● GROUP BY子句中指定的字段名（即聚合键）
示例：
-- 不使用HAVING语句
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type;
-- 结果如下
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服 | 2500.0000 |
| 办公用品 | 300.0000 |
| 厨房用具 | 279500.0000 |
+--------------+-----------------+
3 rows in set (0.00 sec)
-- 使用HAVING语句
-- 通过HAVING语句将销售平均价格大于等于2500的组给保留了
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
-- 结果如下
+--------------+-----------------+
| product_type | AVG(sale_price) |
+--------------+-----------------+
| 衣服 | 2500.0000 |
| 厨房用具 | 279500.0000 |
+--------------+-----------------+
2 rows in set (0.00 sec)
可以看到使用HAVING语句后，输出的结果有所变化。大致流程如下：
- 首先，
FROM语句会选中表Product； - 然后，
GROUP BY语句会选中字段product_type进行分组； - 之后，通过
HAVING语句将销售平均价格大于等于2500的组保留下来； - 最后，通过
SELECT语句将保留下的组的产品类型和平均价格显示出来；
如果是对表的行进行条件指定，WHERE和HAVING都可以生效。
-- 下面两条语句执行结果一致
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;
-- 结果如下
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
+--------------+----------+
1 row in set (0.01 sec)
但是，一般而言如果是对表的行进行条件指定，最好还是使用WHERE语句，因为WHERE的执行速度更快。
4.5 对表的查询结果进行排序
使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示。
在ORDER BY子句中可以使用SELECT子句中定义的别名。
-- 将product_id命名为ID，然后按照ID进行排序
SELECT product_id as ID, product_name, sale_price, purchase_price
FROM Product
ORDER BY ID;
-- 结果如下
+------+--------------+------------+----------------+
| ID | product_name | sale_price | purchase_price |
+------+--------------+------------+----------------+
| 0001 | T恤衫 | 1000 | 500 |
| 0002 | 打孔器 | 500 | 320 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 300000 | 700 |
| 0005 | 高压锅 | 680000 | 1250 |
| 0006 | 叉子 | 50000 | NULL |
| 0007 | 擦菜板 | 88000 | 198 |
| 0008 | 圆珠笔 | 100 | NULL |
+------+--------------+------------+----------------+
8 rows in set (0.00 sec)
为什么ORDER BY中可以使用SELECT定义的别名呢？
这是因为在MySQL中，ORDER BY的执行次序在SELECT之后。
五、数据的插入及更新
5.1 插入默认值
在前面我们创建表时，字段sale_price包含了一条约束条件，默认为0。我们在插入数据时，可以直接用DEFAULT对该字段赋值。前提是，该字段被指定了默认值。
-- 通过显式方法设定默认值
INSERT INTO
ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
VALUES ('0009', '擦菜板', '厨房用具', DEFAULT, 790, '2009-04-28');
-- 通过隐式方法插入默认值
INSERT INTO
ProductIns (product_id, product_name, product_type, purchase_price, regist_date)
VALUES ('0010', '擦菜板', '厨房用具', 790, '2009-04-28');
5.2 数据的删除
通过DROP TABLE或者DELETE语句，可以对表进行删除，但二者存在一定的区别。
-
DROP TABLE 语句可以将表完全删除。 -
DELETE 语句会留下表结构，而删除表中的全部数据。
无论通过哪种方式删除，数据都是难以恢复的。
- 通过
DROP进行删除
语法结构为：
- 通过
DELETE进行删除
语法结构如下，记得要加FROM：
同时，也可以通过WHERE语句来指定删除的条件：
DELETE FROM <表名>
WHERE <条件>;
需要注意的是，DELETE语句的删除对象并不是表或者列，而是记录（行）。
示例：
SELECT * FROM Product;
-- 结果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
-- 删除销售价格大于等于4000的行
DELETE FROM Product
WHERE sale_price >= 4000;
-- 结果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
6 rows in set (0.00 sec)
- 通过
TRUNCATE进行删除
在MySQL中，还存在一种删除表的方式，就是利用TRUNCATE语句。它的功能和DROP类似，但是不能通过WHERE指定条件，优点是速度比DROP快得多。
TRUNCATE Product;
-- 结果如下
mysql> SELECT * FROM Product;
Empty set (0.00 sec)
mysql> DESC Product;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | NULL | |
| purchase_price | int | YES | | NULL | |
| regist_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5.3 数据的更新
当我们使用INSERT语句插入错误的数据后，若我们不想删除后从新插入，那就要使用到UPDATE语句。
- 基本用法
UPDATE的语法结构如下：
UPDATE <表名>
SET <字段名> = <表达式>;
示例：
-- 由于前面演示删除语句时，表Product的内容已清空
-- 所以，这里从新进行数据插入
INSERT INTO Product VALUES
('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20'),
('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20'),
('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15'),
('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'),
('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28'),
('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11')
;
-- 修改表中所有行regist_date的值
UPDATE Product
SET regist_date = '2009-10-10';
-- 结果如下
mysql> SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-10-10 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-10-10 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | 2009-10-10 |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-10-10 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-10-10 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-10-10 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2009-10-10 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-10-10 |
+------------+--------------+--------------+------------+----------------+-------------+
8 rows in set (0.00 sec)
- 指定条件
UPDATE <表名>
SET <列名> = <表达式>
WHERE <条件>;
示例：
UPDATE Product
SET regist_date = '2021-10-30'
WHERE product_id = '0001';
注意，你也可是使用NULL对表进行更新，不过更新的字段必须满足没有主键和NOT NULL的约束条件。
- 多列更新
多列更新只需要用逗号（，）连接更改的字段即可。
UPDATE Product
SET
sale_price = sale_price * 10,
purchase_price = purchase_price / 2
WHERE product_type = '厨房用具';
六、Pymysql的使用
在正式介绍pymysql的用法之前，我们先思考一件事，我们希望借助pymysql完成什么事情？
之前，我们在命令行下，通过输入SQL语句来完成对数据库和表的增删改查。那么，我们也希望能够在Python下能够完成同样的操作，并且能够返回相应的反馈。具体任务包括：
- 登陆并连接到MySQL下的用户；
- 切换到相应的数据库下；
- 完成对表的增删改查；
接下来的内容将围绕这3部分来介绍。
6.1 安装pymysql
通过pip，我们可以完成对pymysql的安装：
python3 -m pip install PyMySQL
6.2 连接数据库
如果希望在Python中操作MySQL数据库，那么首先就要登陆到MySQL下的用户。
我们通过创建库pymysql下的类connect的一个实例来登陆到数据库。
注意要使用到数据库的用户名，如果忘记用户名的小伙伴可以输入密码进入mysql后执行select user,host from mysql.user;语句就能看到用户名。
示例：
import pymysql
# 这里登陆到我之前创建的admin账户
db = pymysql.connect(
host='localhost',
user='admin',
password='mysql123',
database='shop',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
参数解释：
-
host：数据库服务器地址，默认localhost； -
user：所要登陆的用户名； -
password：用户的登录密码； -
database：所要连接的数据库库名； -
charset：使用的字符类型； -
cursorclass：定义游标使用的类型，通过指定游标使用的类型，在返回输出的结果时将按照指定的类型进行返回。例如，这里设置为字典游标。
6.3 创建游标
关于游标，可以理解为在命令行中的光标。
在命令行中，我们是在光标处键入语句的。这里游标的起到类似作用。
# 创建游标
cursor = db.cursor()
实际上，除了在初始化connect的实例时指定游标类型，我们在初始化游标时也可以指定游标类型，默认为元组类型。
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
cursors共支持四类游标：
-
Cursor: 默认，元组类型 -
DictCursor: 字典类型 -
SSCursor: 无缓冲元组类型 -
SSDictCursor: 无缓冲字典类型
6.4 类方法
初始化完类connect和cursor的实例后，我们先来了解一下这两个类下包含的方法。了解这些方法有利于我们后面在python下操作mysql：
-
close()：在完成操作后，需要关闭与数据库之间的连接； -
commit()：如果执行语句中发生了数据更改，需要提交更改到稳定的存储器； -
cursor(cursor=None)：创建一个游标，前面我们在初始化connect类是指定了游标类型，通过cursor初始化游标时，也可以进行游标类型指定； -
rollback()：事务回滚；
-
pymysql.cursors下的类方法：
-
close()：结束时，关闭游标； -
execute()：通过游标执行语句； -
executemany()：通过游标执行多条语句； -
fetchone()：获取单条数据； -
fetchmany(size=None)：获取size条数据； -
fetchall()：获取单条数据； -
scroll(value, mode)：数据的查询操作都是基于游标，可以通过scroll控制游标的位置。
-
mode=absolute：绝对位置移动，控制游标位置到上一次查询的第value条数据，最小值为0； -
mode=relative：相对位置移动，基于当前位置，跳过value条数据；
更详细的资料，可参考官方的API或者Github:
6.5 实战
（1）创建表和插入数据。
# -*- coding: utf-8 -*-
"""
Created on Wed Dec 15 14:27:08 2021
@author: 86493
"""
import pymysql
# 这里登陆到我之前创建的admin账户
connection = pymysql.connect(
host='localhost', # 数据库服务器地址
# user='admin',
user='root', # 用户名
password='123456', # 密码
database='shop', # shop数据库
charset='utf8mb4', # 使用的字符类型
cursorclass=pymysql.cursors.DictCursor # 定义游标使用的类型
)
# 创建游标
cursor = connection.cursor()
# 指定游标类型
cursor = connection.cursor(cursor = pymysql.cursors.DictCursor)
# 1、创建一个表
sql = """
CREATE TABLE Employee1(
id INT PRIMARY KEY,
name CHAR(15) NOT NULL
)
"""
# 提交执行
cursor.execute(sql)
# 2、往表中插入数据
sql = "INSERT INTO Employee1 (id, name) VALUES (%s, %s)"
values = [(1, 'XiaoBai'),
(2, 'XiaoHei'),
(3, 'XiaoHong'),
(4, 'XiaoMei'),
(5, 'XiaoLi')]
try:
# 通过executemany可以插入多条数据
cursor.executemany(sql, values)
# 提交事务
connection.commit()
except:
connection.rollback()
# 3、关闭光标及连接
cursor.close()
connection.close()
（2）在示例1的基础上，执行查询。
import pymysql
# 以admin身份连接到数据库shop
connection = pymysql.connect(
host='localhost',
user='admin',
password='mysql123',
database='shop',
charset='utf8mb4',
)
with connection:
# 创建游标
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
# 1. 通过fetchone只查询一条
cursor.execute("SHOW CREATE TABLE Employee")
result = cursor.fetchone()
print(f'查询结果1： \n{result}')
# 2. 通过fetchmany查询size条
cursor.execute("DESC Employee")
result = cursor.fetchmany(size=2)
print(f'查询结果2： \n{result}')
# 3. 通过fetchall查询所有
cursor.execute("SELECT * FROM Employee")
result = cursor.fetchall()
print(f'查询结果3： \n{result}')
# 4. 通过scroll回滚到第0条进行查询
cursor.scroll(0, mode='absolute')
result = cursor.fetchone()
print(f'查询结果4： \n{result}')
# 5. 通过scroll跳过2条进行查询
cursor.scroll(2, mode='relative')
result = cursor.fetchone()
print(f'查询结果5： \n{result}')
cursor.close()
控制台打印结果如下：
查询结果1：
{'Table': 'Employee', 'Create Table': 'CREATE TABLE `Employee` (\n `id` int NOT NULL,\n `name` char(15) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'}
查询结果2：
[{'Field': 'id', 'Type': 'int', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {'Field': 'name', 'Type': 'char(15)', 'Null': 'NO', 'Key': '', 'Default': None, 'Extra': ''}]
查询结果3：
[{'id': 1, 'name': 'XiaoBai'}, {'id': 2, 'name': 'XiaoHei'}, {'id': 3, 'name': 'XiaoHong'}, {'id': 4, 'name': 'XiaoMei'}, {'id': 5, 'name': 'XiaoLi'}]
查询结果4：
{'id': 1, 'name': 'XiaoBai'}
查询结果5：
{'id': 4, 'name': 'XiaoMei'}
（3）SQL注入的问题
先建立一个表并插入数据：
import pymysql
# 以admin身份连接到数据库shop
connection = pymysql.connect(
host='localhost',
user='admin',
password='mysql123',
database='shop',
charset='utf8mb4',
)
# 创建游标
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
sql = """
CREATE TABLE UserInfo(
id INT PRIMARY KEY,
name VARCHAR(15),
password CHAR(15) NOT NULL
)
"""
cursor.execute(sql)
sql = "INSERT INTO UserInfo (id, name, password) VALUES (%s, %s, %s)"
values = [(1, 'XiaoBai', '123'),
(2, 'XiaoHei', '234'),
(3, 'XiaoHong', '567'),
(4, 'XiaoMei', '321'),
(5, 'XiaoLi', '789')]
cursor.executemany(sql, values)
connection.commit()
再写一个程序，根据输入判定登陆是否成功：
import pymysql
# 以admin身份连接到数据库shop
connection = pymysql.connect(
host='localhost',
user='admin',
password='mysql123',
database='shop',
charset='utf8mb4',
)
# 创建游标
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
while True:
user = input("输入用户：").strip()
password = input("输入密码：").strip()
sql = "select name, password from UserInfo where name='%s' and password='%s' " % (user, password)
cursor.execute(sql)
# 打印用户和密码
result=cursor.fetchone()
print(result)
if result:
print("成功登陆\n")
else:
print("登陆失败\n")
在控制台下，我们进行了三组用户和密码的验证：
输入用户：XiaoBai
输入密码：123
{'name': 'XiaoBai', 'password': '123'}
成功登陆
输入用户：XiaoBai
输入密码：321
None
登陆失败
输入用户：XiaoBai' -- dsd
输入密码：321
{'name': 'XiaoBai', 'password': '123'}
成功登陆
可以看出，第1组和第2组验证正常，但是第三组出现了异常，输入错误的密码却可以正确登陆。
这是因为在MySQL中--的含义是注释，如果通过字符串进行拼接：
select name, password from UserInfo where name='XiaoBai' -- dsd' and password='321'
实际等价于：
select name, password from UserInfo where name='XiaoBai'
解决办法：通过execute或者executemany来进行拼接。将语句：
sql = "select name, password from UserInfo where name='%s' and password='%s' " % (user, password)
cursor.execute(sql)
改为：
sql = "select name, password from UserInfo where name=%s and password=%s"
cursor.execute(sql, (user, password))
附：时间规划
Reference
（1）datawhale notebook
（2）mysql官方文档
