数据库
数据库回顾
关系数据库特点
- 具体表象：用二维表（有行有列）组织数据
- 编程语言：结构化查询语言（SQL）
- DDL：数据定义语言
- DML：数据操作语言
- DCL：数据控制语言
- TCL：事务控制语言
数据引擎
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 存储限制 | 有 | 有 |
| 事务 | 支持 | |
| 锁机制 | 行锁 | 表锁 |
| B树索引 | 支持 | 支持 |
| 哈希索引 | ||
| 全文检索 | 支持（5.6+） | 支持 |
| 集群索引 | 支持 | |
| 数据缓存 | 支持 | |
| 索引缓存 | 支持 | 支持 |
| 数据可压缩 | 支持 | |
| 内存使用 | 高 | 低 |
| 存储空间使用 | 高 | 低 |
| 批量插入性能 | 低 | 高 |
| 是否支持外键 | 支持 |
InnoDB: 支持外键，事务以及行锁
DDL
DDL 主要用于创建，删除，修改数据库中的对象
核心的关键字包括: create 、 drop 、 alter
删除表和修改表
下面以学生表为例，为大家说明如何删除表和修改表。删除表可以使用drop table，代码如下所示。
DROP TABLE `tb_student`;
或
DROP TABLE IF EXISTS `tb_student`; -- 删除之前先判断数据表是否存在
需要注意的是，如果学生表已经录入了数据而且该数据被其他表引用了
修改表，添加一个新列，例如给学生表添加一个联系电话的列。
ALTER TABLE `tb_student` ADD COLUMN `stu_tel` varchar(20) NOT NULL COMMENT '联系电话';
注意：如果新增列的时候指定了非空约束（
not null），那么学生表不能够有数据，否则原来的数据增加了 stu_tel 列之后是没有数据的，这就违反了非空约束的要求；当然，我们在添加列的时候也可以使用默认值约束来解决这个问题。
修改表，删除指定的列，例如将上面添加的联系电话列删除掉。
ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;
修改表，修改列的数据类型，例如将学生表的 stu_sex 修改为字符。
ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` char(1) NOT NULL DEFAULT 'M' COMMENT '性别';
修改表，修改列的命名，例如将学生表的 stu_sex 修改为 stu_gender。
ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` boolean DEFAULT 1 COMMENT '性别';
修改表，删除约束条件，例如删除学生表的 col_id 列的外键约束。
ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;
修改表，添加约束条件，例如给学生表的 col_id 列加上外键约束。
ALTER TABLE `tb_student` ADD FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);
或
ALTER TABLE `tb_student` ADD CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);
说明：在添加外键约束时，还可以通过
on update和on delete来指定在被引用的表发生删除和更新操作时，应该进行何种处理，二者的默认值都是restrict，表示如果存在外键约束，则不允许更新和删除被引用的数据。除了restrict之外，这里可能的取值还有cascade（级联操作）和set null（设置为空），有兴趣的读者可以自行研究。
修改表的名字，例如将学生表的名字修改为 tb_stu_info。
ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;
提示：一般情况下，请不要轻易修改数据库或表的名字。
DML
DML主要负责数据的插入，删除，更新和查询
关键字包括：insert 、delete 、update 、 select
DQL
-- 使用 CASE
SELECT stu_name AS 姓名,
CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
FROM tb_student
WHERE stu_name = '杨过';
-- 使用 IF
SELECT stu_name AS 姓名,
IF(stu_sex, '男', '女') AS 性别
FROM tb_student
WHERE stu_name = '杨过';
CASE和IF
CASE
-
是一个通用的SQL表达式，能够在多种关系型数据库中使用，而不仅限于Mysql，case可以用了构建复杂的条件判断
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result_default END -
case 可以用于更复杂的条件判断，其中包含多个where条件，能够处理多个可能得结果
-
CASE: 可以处理复杂的嵌套和多条件逻辑。
IF
-
是Mysql特有的函数，通常用于简单的布尔条件判断。IF更像是一个函数，用于单一的条件判断
IF(condition, result_if_true, result_if_false) -
IF: 适用简单的二选一的情况。如判断一个布尔表达式，返回两个值之一。
-
IF: 通常用于处理简单条件，使用起来更为轻便。
在查询数据时，可以在SELECT语句及其子句（如WHERE子句、ORDER BY子句、HAVING子句等）中使用函数，这些函数包括字符串函数、数值函数、时间日期函数、流程函数等，如下面的表格所示。
常用字符串函数。
| 函数 | 功能 |
|---|---|
CONCAT |
将多个字符串连接成一个字符串 |
FORMAT |
将数值格式化成字符串并指定保留几位小数 |
FROM_BASE64 / TO_BASE64 |
BASE64解码/编码 |
BIN / OCT / HEX |
将数值转换成二进制/八进制/十六进制字符串 |
LOCATE |
在字符串中查找一个子串的位置 |
LEFT / RIGHT |
返回一个字符串左边/右边指定长度的字符 |
LENGTH / CHAR_LENGTH |
返回字符串的长度以字节/字符为单位 |
LOWER / UPPER |
返回字符串的小写/大写形式 |
LPAD / RPAD |
如果字符串的长度不足，在字符串左边/右边填充指定的字符 |
LTRIM / RTRIM |
去掉字符串前面/后面的空格 |
ORD / CHAR |
返回字符对应的编码/返回编码对应的字符 |
STRCMP |
比较字符串，返回-1、0、1分别表示小于、等于、大于 |
SUBSTRING |
返回字符串指定范围的子串 |
常用数值函数。
| 函数 | 功能 |
|---|---|
ABS |
返回一个数的绝度值 |
CEILING / FLOOR |
返回一个数上取整/下取整的结果 |
CONV |
将一个数从一种进制转换成另一种进制 |
CRC32 |
计算循环冗余校验码 |
EXP / LOG / LOG2 / LOG10 |
计算指数/对数 |
POW |
求幂 |
RAND |
返回[0,1)范围的随机数 |
ROUND |
返回一个数四舍五入后的结果 |
SQRT |
返回一个数的平方根 |
TRUNCATE |
截断一个数到指定的精度 |
SIN / COS / TAN / COT / ASIN / ACOS / ATAN |
三角函数 |
常用时间日期函数。
| 函数 | 功能 |
|---|---|
CURDATE / CURTIME / NOW |
获取当前日期/时间/日期和时间 |
ADDDATE / SUBDATE |
将两个日期表达式相加/相减并返回结果 |
DATE / TIME |
从字符串中获取日期/时间 |
YEAR / MONTH / DAY |
从日期中获取年/月/日 |
HOUR / MINUTE / SECOND |
从时间中获取时/分/秒 |
DATEDIFF / TIMEDIFF / TIMESTAMPDIFF |
返回两个时间日期表达式相差多少天/小时 |
MAKEDATE / MAKETIME |
制造一个日期/时间 |
常用流程控制函数。
| 函数 | 功能 |
|---|---|
IF |
根据条件是否成立返回不同的值 |
IFNULL |
如果为NULL则返回指定的值否则就返回本身 |
NULLIF |
两个表达式相等就返回NULL否则返回第一个表达式的值 |
其他常用函数。
| 函数 | 功能 |
|---|---|
MD5 / SHA1 / SHA2 |
返回字符串对应的哈希摘要 |
CHARSET / COLLATION |
返回字符集/校对规则 |
USER / CURRENT_USER |
返回当前用户 |
DATABASE |
返回当前数据库名 |
VERSION |
返回当前数据库版本 |
FOUND_ROWS / ROW_COUNT |
返回查询到的行数/受影响的行数 |
LAST_INSERT_ID |
返回最后一个自增主键的值 |
UUID / UUID_SHORT |
返回全局唯一标识符 |
DCL
用于权限授予和召回权限
关键字包括：grant 和 revoke
创建用户
CREATE USER 'ceshi'@'%' IDENTIFIED BY '访问密码';
该用户可以从任意主机访问数据库服务器，因为@后面使用了可以表示任意多个字符的通配符 %
如果要限制只能从192.168.0.x这个网段的主机访问数据库服务，可以使用下面的命令
DROP USER IF EXISTS 'ceshi'@'%';
CREATE USER 'ceshi'@'192.168.0.%' IDENTIFIED BY '访问密码';
注：此时创建的账号没有任何权限，所有，不能对数据库做任何操作
授予权限
我们用下面的语句为 ceshi授予查询 school 数据库学院表（tb_college）的权限。
GRANT SELECT ON `school`.`tb_college` TO 'ceshi'@'192.168.0.%';
我们也可以让 ceshi对 school 数据库的所有对象都具有查询权限，代码如下所示。
GRANT SELECT ON `school`.* TO 'ceshi'@'192.168.0.%';
如果我们希望 ceshi还有 insert、delete 和 update 权限，可以使用下面的方式进行操作。
GRANT INSERT, DELETE, UPDATE ON `school`.* TO 'ceshi'@'192.168.0.%';
如果我们还想授予 ceshi执行 DDL 的权限，可以使用如下所示的 SQL。
GRANT CREATE, DROP, ALTER ON `school`.* TO 'ceshi'@'192.168.0.%';
如果我们希望 ceshi账号对所有数据库的所有对象都具备所有的操作权限，可以执行如下所示的操作，但是一般情况下，我们不会这样做，因为我们之前说过，权限刚刚够用就行，一个普通的账号不应该拥有这么大的权限。
GRANT ALL PRIVILEGES ON *.* TO 'ceshi'@'192.168.0.%';
召回权限
如果要召回 ceshi对 school 数据库的 insert、delete 和 update 权限，可以使用下面的操作。
REVOKE INSERT, DELETE, UPDATE ON `school`.* FROM 'ceshi'@'192.168.0.%';
如果要召回所有的权限，可以按照如下所示的方式进行操作。
REVOKE ALL PRIVILEGES ON *.* FROM 'ceshi'@'192.168.0.%';
需要说明的是，由于数据库可能会缓存用户的权限，可以在授予或召回权限后执行下面的语句使新的权限即时生效。
FLUSH PRIVILEGES;
TCL
通常用于事务控制
新特性
JSON类型
json类型主要分为：json对象和json数组两种
-
json对象
{"name": "骆昊", "tel": "13122335566", "QQ": "957658"} -
json数组
[1, 2, 3][{"name": "骆昊", "tel": "13122335566"}, {"name": "王大锤", "QQ": "123456"}]
哪些地方需要使用到json呢？
举一个简单的例子，现在很多产品的用户登录都支持多种方式，例如手机号、微信、QQ、新浪微博等，但是一般情况下我们又不会要求用户提供所有的这些信息，那么用传统的设计方式，就需要设计多个列来对应多种登录方式，可能还需要允许这些列存在空值，这显然不是很好的选择；另一方面，如果产品又增加了一种登录方式，那么就必然要修改之前的表结构，这就更让人痛苦了。但是，有了 JSON 类型，刚才的问题就迎刃而解了，我们可以做出如下所示的设计。
CREATE TABLE `tb_test`
(
`user_id` bigint unsigned,
`login_info` json,
PRIMARY KEY (`user_id`)
);
INSERT INTO `tb_test`
VALUES
(1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
(2, '{"tel": "13599876543", "weibo": "wangdachui123"}');
如果要查询用户的手机和微信号，可以用如下所示的 SQL 语句。
SELECT `user_id`
, JSON_UNQUOTE(JSON_EXTRACT(`login_info`, '$.tel')) AS 手机号
, JSON_UNQUOTE(JSON_EXTRACT(`login_info`, '$.wechat')) AS 微信
FROM `tb_test`;
+---------+-------------+-----------+
| user_id | 手机号 | 微信 |
+---------+-------------+-----------+
| 1 | 13122335566 | jackfrued |
| 2 | 13599876543 | NULL |
+---------+-------------+-----------+
因为支持 JSON 类型，MySQL 也提供了配套的处理 JSON 数据的函数，就像上面用到的json_extract和json_unquote。当然，上面的 SQL 还有更为便捷的写法，如下所示。
SELECT `user_id`
, `login_info` ->> '$.tel' AS 手机号
, `login_info` ->> '$.wechat' AS 微信
FROM `tb_test`;
再举个例子，如果我们的产品要实现用户画像功能（给用户打标签），然后基于用户画像给用户推荐平台的服务或消费品之类的东西，我们也可以使用 JSON 类型来保存用户画像数据，示意代码如下所示。
创建画像标签表。
CREATE TABLE `tb_tags`
(
`tag_id` int unsigned NOT NULL COMMENT '标签ID',
`tag_name` varchar(20) NOT NULL COMMENT '标签名',
PRIMARY KEY (`tag_id`)
);
INSERT INTO `tb_tags` (`tag_id`, `tag_name`)
VALUES
(1, '70后'),
(2, '80后'),
(3, '90后'),
(4, '00后'),
(5, '爱运动'),
(6, '高学历'),
(7, '小资'),
(8, '有房'),
(9, '有车'),
(10, '爱看电影'),
(11, '爱网购'),
(12, '常点外卖');
为用户打标签。
CREATE TABLE `tb_users_tags`
(
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`user_tags` json NOT NULL COMMENT '用户标签'
);
INSERT INTO `tb_users_tags`
VALUES
(1, '[2, 6, 8, 10]'),
(2, '[3, 10, 12]'),
(3, '[3, 8, 9, 11]');
接下来，我们通过一组查询来了解 JSON 类型的巧妙之处。
-
查询爱看电影（有
10这个标签）的用户ID。SELECT `user_id` FROM `tb_users_tags` WHERE 10 MEMBER OF (`user_tags`->'$'); -
查询爱看电影（有
10这个标签）的80后（有2这个标签）用户ID。SELECT `user_id` FROM `tb_users_tags` WHERE JSON_CONTAINS(`user_tags`->'$', '[2, 10]'); -
查询爱看电影或80后或90后的用户ID。
SELECT `user_id` FROM `tb_users_tags` WHERE JSON_OVERLAPS(user_tags->'$', '[2, 3, 10]');
说明：上面的查询用到了
member of谓词和两个 JSON 函数，json_contains可以检查 JSON 数组是否包含了指定的元素，而json_overlaps可以检查 JSON 数组是否与指定的数组有重叠部分。
窗口函数
索引
Python操作mysql
在 Python3 中，可以使用mysqlclient或者pymysql三方库来接入 MySQL 数据库并实现数据持久化操作。二者的用法完全相同，只是导入的模块名不一样。
推荐使用纯 Python 的三方库pymysql，因为它更容易安装成功。
接入MySQL
首先，安装pymysql，如果需要接入 MySQL 8，还需要安装一个名为cryptography的三方库来支持 MySQL 8 的密码认证方式。
pip install pymysql cryptography
插入数据
import pymysql
no = int(input('部门编号: '))
name = input('部门名称: ')
location = input('部门所在地: ')
# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8mb4')
try:
# 2. 获取游标对象（Cursor）
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'insert into `tb_dept` values (%s, %s, %s)',
(no, name, location)
)
if affected_rows == 1:
print('新增部门成功!!!')
# 4. 提交事务（transaction）
conn.commit()
except pymysql.MySQLError as err:
# 4. 回滚事务
conn.rollback()
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
说明：上面的
127.0.0.1称为回环地址，它代表的是本机。下面的guest是我提前创建好的用户，该用户拥有对hrs数据库的insert、delete、update和select权限。我们不建议大家在项目中直接使用root超级管理员账号访问数据库，这样做实在是太危险了。我们可以使用下面的命令创建名为guest的用户并为其授权。create user 'guest'@'%' identified by 'Guest.618'; grant insert, delete, update, select on `hrs`.* to 'guest'@'%';
如果要插入大量数据，建议使用游标对象的executemany方法做批处理（一个insert操作后面跟上多组数据），大家可以尝试向一张表插入10000条记录，然后看看不使用批处理一条条的插入和使用批处理有什么差别。游标对象的executemany方法第一个参数仍然是 SQL 语句，第二个参数可以是包含多组数据的列表或元组。
删除数据
import pymysql
no = int(input('部门编号: '))
# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8mb4',
autocommit=True)
try:
# 2. 获取游标对象（Cursor）
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'delete from `tb_dept` where `dno`=%s',
(no, )
)
if affected_rows == 1:
print('删除部门成功!!!')
finally:
# 5. 关闭连接释放资源
conn.close()
说明：如果不希望每次 SQL 操作之后手动提交或回滚事务，可以
connect函数中加一个名为autocommit的参数并将它的值设置为True，表示每次执行 SQL 成功后自动提交。但是我们建议大家手动提交或回滚，这样可以根据实际业务需要来构造事务环境。如果不愿意捕获异常并进行处理，可以在try代码块后直接跟finally块，省略except意味着发生异常时，代码会直接崩溃并将异常栈显示在终端中。
更新数据
import pymysql
no = int(input('部门编号: '))
name = input('部门名称: ')
location = input('部门所在地: ')
# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8mb4')
try:
# 2. 获取游标对象（Cursor）
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
affected_rows = cursor.execute(
'update `tb_dept` set `dname`=%s, `dloc`=%s where `dno`=%s',
(name, location, no)
)
if affected_rows == 1:
print('更新部门信息成功!!!')
# 4. 提交事务
conn.commit()
except pymysql.MySQLError as err:
# 4. 回滚事务
conn.rollback()
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
查询数据
- 查询部门表的数据。
import pymysql
# 1. 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8mb4')
try:
# 2. 获取游标对象（Cursor）
with conn.cursor() as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
cursor.execute('select `dno`, `dname`, `dloc` from `tb_dept`')
# 4. 通过游标对象抓取数据
row = cursor.fetchone()
while row:
print(row)
row = cursor.fetchone()
except pymysql.MySQLError as err:
print(type(err), err)
finally:
# 5. 关闭连接释放资源
conn.close()
说明：上面的代码中，我们通过构造一个
while循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。因为如果使用fetchall一次性将所有记录抓取到一个嵌套元组中，会造成非常大的内存开销，这在很多场景下并不是一个好主意。如果不愿意使用while循环，还可以考虑使用iter函数构造一个迭代器来逐行抓取数据，有兴趣的读者可以自行研究。
- 分页查询员工表的数据。
import pymysql
page = int(input('页码: '))
size = int(input('大小: '))
# 1. 创建连接（Connection）
con = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8')
try:
# 2. 获取游标对象（Cursor）
with con.cursor(pymysql.cursors.DictCursor) as cursor:
# 3. 通过游标对象向数据库服务器发出SQL语句
cursor.execute(
'select `eno`, `ename`, `job`, `sal` from `tb_emp` order by `sal` desc limit %s,%s',
((page - 1) * size, size)
)
# 4. 通过游标对象抓取数据
for emp_dict in cursor.fetchall():
print(emp_dict)
finally:
# 5. 关闭连接释放资源
con.close()
案例讲解
下面我们为大家讲解一个将数据库表数据导出到 Excel 文件的例子，我们需要先安装openpyxl三方库，命令如下所示。
pip install openpyxl
接下来，我们通过下面的代码实现了将数据库hrs中所有员工的编号、姓名、职位、月薪、补贴和部门名称导出到一个 Excel 文件中。
import openpyxl
import pymysql
# 创建工作簿对象
workbook = openpyxl.Workbook()
# 获得默认的工作表
sheet = workbook.active
# 修改工作表的标题
sheet.title = '员工基本信息'
# 给工作表添加表头
sheet.append(('工号', '姓名', '职位', '月薪', '补贴', '部门'))
# 创建连接（Connection）
conn = pymysql.connect(host='127.0.0.1', port=3306,
user='guest', password='Guest.618',
database='hrs', charset='utf8mb4')
try:
# 获取游标对象（Cursor）
with conn.cursor() as cursor:
# 通过游标对象执行SQL语句
cursor.execute(
'select `eno`, `ename`, `job`, `sal`, coalesce(`comm`, 0), `dname` '
'from `tb_emp` natural join `tb_dept`'
)
# 通过游标抓取数据
row = cursor.fetchone()
while row:
# 将数据逐行写入工作表中
sheet.append(row)
row = cursor.fetchone()
# 保存工作簿
workbook.save('hrs.xlsx')
except pymysql.MySQLError as err:
print(err)
finally:
# 关闭连接释放资源
conn.close()
大家可以参考上面的例子，试一试把 Excel 文件的数据导入到指定数据库的指定表中，看看是否可以成功。
密码加密：
import hashlib
def gen_md5_digest(content):
return hashlib.md5(content.encode()).hexdigest()
浙公网安备 33010602011771号