Python 数据库学习

Bilibili链接：https://www.bilibili.com/video/BV1PT4y1e7UU/?spm_id_from=333.337.search-card.all.click&vd_source=8d0dafcc47e01df337946850b388e907

YouTuBe链接：https://www.youtube.com/watch?v=gvRXjsrpCHw&t=40s

1 概述

1.1 什么是数据库

可以简单理解为存储所有资料的一个集合。

数据库需要用 SQL（Structured Query Language）语言进行访问和管理。

1.2 数据库的分类

数据库分为关联式数据库和非关联式数据库：

• 关联式数据库（RDBMS）（不同的语言语法有细微的不同）

  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server

• 非关联式数据库（NRDBMS）(没有统一的语言)

  • MogoDB
  • Redis
  • DynamoDB
  • Elaticsearch

2 安装 MySQL

为什么要用 MySQL，因为目前用的比较多。

2.1 Windows

下载链接：https://dev.mysql.com/downloads/

安装过程：

类型：选Custom -> 分别选择 MySQL Server、MySQL Workbench、MySQL Shell 的最新版本 -> 一直下一步 -> 输入 root 密码 -> 然后下一步下一步就可以

2.2 Mac

下载链接：https://dev.mysql.com/downloads/mysql/

安装过程：

选好芯片类型 -> 选所下载的文件格式DMG Archive -> 下一步下一步 -> 输入用户的密码 -> 输入 root 密码

再安装 MySQL Workbench

下载链接：https://dev.mysql.com/downloads/workbench/

选所下载的文件格式（macOS (x86, 64-bit), DMG Archive）-> 拖拽安装即可

3 表格 & 键 tables & keys

• 主键（Primary Key）：唯一表示某一资料的属性

• 外键（Foreign key）：对应到另一张表格的主键，用于增加属性

• 可以设两个或者三个甚至更多主键，因为对于有的属性来说，一个主键不能将其分隔开。

4 创建资料库 create database

-- MySQL 的 关键字 习惯用大写（小写也可以），在软件里会以蓝色表示。
CREATE DATABASE `sql_tutorial`;  创建数据库 --- 这里的名称习惯用反引号引起来，用于避免和关键字的冲突
SHOW DATABASES;  查看资料库
DROP DATABASE `sql_tutorial`;  删除资料库

5 创建表格 create table

USE `数据库名`;

-- 创建表格
CREATE TABLE `student`(
    `student_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `major` VARCHAR(20)
    这里的 PRIMARY KEY 可以单独拿出来写
    PRIMARY KEY(`student_id`)
);
-- 查看表格
DESCRIBE `student`;
-- 删除表格
DROP TABLE `student`;
-- 增加表格属性
ALTER TABLE `student` ADD gpa DECIMAL(3, 2);
-- 删除表格属性
ALTER TABLE `student` DROP COLUMN gpa;

6 储存资料 insert

-- 存储数据
INSERT INTO 'student' VALUES(1, '小白', '历史');  这里字符串可以用单双引号，习惯用单引号（看看其他课程咋说的）
-- 搜寻数据库里所存放的数据
SELECT * FROM `student`;
-- 可以用小括号去更改赋值的顺寻
INSERT INTO `student`(`major`, `name`, `student_id`) VALUES('数学', '小蓝', 6);  传值的时候若没有指定则为空

7 限制、约束 constraint

在创建表格的时候给属性一些限制，例如不能为空、唯一等

-- 在新建表格的时候增加限制
CREATE TABLE `student`(
    `student_id` INT PRIMARY KEY AUTO_INCREMENT,  自动加一
    `name` VARCHAR(20) NOT NULL,  不能为空
    `major` VARCHAR(20) UNIQUE,  不能重复
    `age` INT DEFAULT 18  默认值
);

8 修改&删除资料 update & delete

-- 关闭预设的更新模式
SET SQL_SAFE_UPDATES = 0;

-- 将 major 等于 英语 的词换成等于英语文学
UPDATE `student`
SET `major` = '英语文学'
WHERE `major` = '英语';

-- 将生物和化学合并成生化
UPDATE `student`
SET `major` = '生化'
WHERE `major` = '英语' OR `major` = '化学';

-- 同时更新两个属性 
UPDATE `student`
SET `major` = '生化环才', `name` = '退学'
WHERE `major` = '生物' OR `major` = '化学';


-- 删除 student_id = 4 的数据
DELETE FROM `student`
WHERE `student_id` = 5;

-- 删除 name = '小黑' 且 major = '生化' 的数据
DELETE FROM `student`
WHERE `name` = '小黑' AND `major` = '生化';

-- 把分数小于 70 的数据删掉
DELETE FROM `student`
WHERE `score` < 70;


-- 大于: > 、等于: =、小于: <:、大于等于: >=、小于等于: <=、不等于: <>

-- 删除 student 里的所有资料
DELETE FROM `student`;

9 取得资料 select

-- 取得资料
-- 取得表格里面的所有属性的值
SELECT * FROM `student`;

-- 取得表格里面的 name 属性的值
SELECT `name` FROM `student`;

-- 取得表格里面的 name、major 属性的值
SELECT `name`, `major` FROM `student`;

-- 取得表格里面的所有属性的值，顺便排序(默认升序 ASC)
SELECT * FROM `student` ORDER BY `score`;
-- 取得表格里面的所有属性的值，顺便降序排序
SELECT * FROM `student` ORDER BY `score` DESC;

-- 取得表格里面的所有属性的值，按照 score、student_id 降序排序
SELECT * FROM `student` ORDER BY `score`, `student_id` DESC;

-- 取得表格里面的所有属性的值，按照 score、student_id 升序排序的前三个
SELECT * FROM `student` ORDER BY `score`, `student_id` LIMIT 3;

-- 返回符合条件的值
SELECT * FROM `student` WHERE `score` > 60;

-- 返回符合条件的值的前两个
SELECT * FROM `student` WHERE `score` > 60 LIMIT 3;

-- 返回符合指定属性值的内容
SELECT * FROM `student` WHERE `major` IN ('语文', '英语', '数学');

10 创建公司资料库

-- 创建公司资料库表单
-- 删除 student 表格
DROP TABLE `student`;
-- 创建员工的表格
CREATE TABLE `employee`(
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `birth_date` DATE,
    `sex` varchar(1),
    `salary` INT,
    `branch_id` INT,
    `sup_id` INT
);

-- 创建岗位的表格
CREATE TABLE `branch`(
    `branch_id` INT PRIMARY KEY,
    `branch_name` VARCHAR(20),
    `manager_id` INT,
    FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL
);

-- 补上员工表格的 FOREIGN KEY
ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`)
REFERENCES `branch`(`branch_id`) ON DELETE SET NULL;

ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `employee`(`emp_id`) ON DELETE SET NULL;

-- 创建客户的表格
CREATE TABLE `client`(
    `client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20),
    `phone` VARCHAR(20)
);

-- 创建 works_with 表格
CREATE TABLE `works_with`(
    `emp_id` INT,
    `client_id` INT,
    `total_sales` INT,
    PRIMARY KEY(`emp_id`, `client_id`),
    FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
    FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);

-- 删除带有外键的表格需要先禁用外键的检查，删除修改完后，需要再次启用。
-- 禁用外键检查
SET FOREIGN_KEY_CHECKS=0;
-- 启用外键检查
SET FOREIGN_KEY_CHECKS=1;

这里先建好所有的表再做相应的对应是一个好习惯？

-- 新增公司资料
-- 新建部门资料
INSERT INTO `branch` VALUES(1, '研发', NULL);
INSERT INTO `branch` VALUES(2, '行政', NULL);
INSERT INTO `branch` VALUES(3, '咨询', NULL);
-- 新建员工资料
INSERT INTO `employee` VALUES(206, '小黄', '1998-10-08', 'F', 50000, 1, NULL);
INSERT INTO `employee` VALUES(207, '小绿', '1985-09-16', 'M', 29000, 2, 206);
INSERT INTO `employee` VALUES(208, '小黑', '2000-12-19', 'M', 35000, 3, 206);
INSERT INTO `employee` VALUES(209, '小白', '1997-01-22', 'F', 39000, 3, 207);
INSERT INTO `employee` VALUES(210, '小蓝', '1925-11-10', 'F', 84000, 1, 207);
-- 将 branch 的 manager_id 纠正过来
UPDATE `branch`
SET `manager_id` = 206
WHERE `branch_name` = '研发';
UPDATE `branch`
SET `manager_id` = 207
WHERE `branch_name` = '行政';
UPDATE `branch`
SET `manager_id` = 208
WHERE `branch_name` = '咨询';
-- 新增客户资料
INSERT INTO `client` VALUES(400, '阿狗', '254354335');
INSERT INTO `client` VALUES(401, '阿猫', '25633899');
INSERT INTO `client` VALUES(402, '旺财', '45354345');
INSERT INTO `client` VALUES(403, '露西', '54354365');
INSERT INTO `client` VALUES(404, '艾瑞克', '18783783');
-- 新增销售金额的资料
INSERT INTO `works_with` VALUES(206, 400, 70000);
INSERT INTO `works_with` VALUES(207, 401, 24000);
INSERT INTO `works_with` VALUES(208, 400, 9800);
INSERT INTO `works_with` VALUES(209, 403, 24000);
INSERT INTO `works_with` VALUES(210, 404, 87940);

11 取得公司资料

-- 取得公司资料
-- 1. 取得所有员工资料
SELECT * FROM `employee`;
-- 2. 取得所有客户资料
SELECT * FROM `client`;
-- 3. 按薪水低到高取得员工资料
SELECT * FROM `employee` ORDER BY `salary`;
-- 4. 取得薪水前 3 高的员工
SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 3;
-- 5. 取得所有员工的名字
SELECT `name` FROM `employee`;
-- 6. 取得所有员工的性别（不重复）
SELECT  DISTINCT `sex` FROM `employee`;

12 聚合函数 aggregate function

聚合函数可以让我们更方便的让我们取得数据库里面的信息

-- aggregate function 聚合函数
-- 1. 取得员工人数 --- * 代表表格里总共有多少数据，若传入属性名称则代表该属性有多少数据（NULL不计入）
SELECT COUNT(*) FROM `employee`;
SELECT COUNT(`sup_id`) FROM `employee`;
-- 2. 取得所有出生在 1970-01-01 之后的女性员工数
SELECT COUNT(*) FROM `employee` WHERE `birth_date` > '1970-01-01' AND `sex` = 'F';
-- 3. 取得所有员工的平均薪水
SELECT AVG(`salary`) FROM `employee`;
-- 4. 取得所有员工薪水的总和
SELECT SUM(`salary`) FROM `employee`;
-- 5. 取得薪水最高的员工
SELECT * FROM `employee` WHERE `salary` = (SELECT MAX(`salary`) FROM `employee`);
-- 6. 取得薪水最低的员工
SELECT * FROM `employee` WHERE `salary` = (SELECT MIN(`salary`) FROM `employee`);

13 万用字元 wildcard

有点类似于正则表达式

-- 万用字元 wildcard  % 代表多个字元，_ 代表一个字元
-- 1. 取得电话号码尾数是 335 的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';
-- 2. 取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';
-- 3. 取得生日在 12 月的员工
SELECT * FROM `employee` WHERE `birth_date` LIKE '%-12-%';

14 联集 union

把搜寻的结果串起来

-- 把搜寻到的结果联合在一起（注意：联合的数据类型和数据量要保持一致）
-- 1. 员工名字 union 客户名字
SELECT `name` 
FROM `employee`
UNION
SELECT `client_name` 
FROM `client`;
-- 2. 员工id + 员工名字 union 客户id + 客户名字
SELECT `emp_id` AS `total_id`, `name` as `total_name` 
FROM `employee`
UNION
SELECT `client_id`, `client_name` 
FROM `client`;
-- 3. 员工薪水 union 销售金额
SELECT `salary` 
FROM `employee`
UNION
SELECT `total_sales` 
FROM `works_with`;

15 连接 join

-- join 连接（结果 from 在前，join 在后）
-- 取得所有部门经理的名字
SELECT * 
FROM `branch`
JOIN `employee`
ON `branch`.`manager_id` = `employee`.`emp_id`;
-- left join 条件没有匹配成功也返回 from 后的数据 RIGHT JOIN 也同理
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee`
LEFT JOIN `branch`
ON `manager_id` = `emp_id`;

16 子查询 subquery

把一个查询的结果赋给另一个查询的值

-- subquery 子查询
-- 1. 找出研发部门的经理名字
SELECT `emp_id`, `name` 
FROM `employee` 
WHERE `emp_id` = (
    SELECT `manager_id` 
    FROM `branch` 
    WHERE `branch_name` = '研发'
);
-- 2. 找出对单一客户销售金额超过 50000 的员工名字
SELECT `name` 
FROM `employee` 
WHERE `emp_id` IN (
    SELECT `emp_id` 
    FROM `works_with` 
    WHERE `total_sales` > 50000
);

17 on delete

-- on delete
-- ON DELETE SET NULL --- 删除的话所关联的值设置为 NULL
-- ON DELETE CASCADE --- 删除的话所关联的值也一起删除

DELETE FROM `employee`
WHERE `emp_id` = 207;

SELECT * FROM `branch`;

SELECT * FROM `works_with`;

18 python 连接 MySQL

安装 mysql-connector-python 模块
pip install mysql-connector-python

# 初始化，创建一个连线
connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='xxxxxxxx')
# 调用 cursor() 开始使用数据库
cursor = connection.cursor()

# 创建资料库
# cursor.execute("CREATE DATABASE `qq`;")

# 取得所有资料库名称
cursor.execute("SHOW DATABASES;")
records = cursor.fetchall()
for record in records:
    print(record)

# 选择资料库
cursor.execute("USE `sql_tutorial`;")

# 创建表格
cursor.execute("CREATE TABLE `student_python`("
               "`student_id` INT,"
               "`name` VARCHAR(20),"
               "`major` VARCHAR(20),"
               "PRIMARY KEY(`student_id`));")

# 关闭 cursor()
cursor.close()
connection.close()

# 初始化，创建一个连线
connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='xxxxxxxx',
                                     database='sql_tutorial')  # 在初始化的时候可以直接指定数据库
# 调用 cursor() 开始使用数据库
cursor = connection.cursor()

# 取得部门表格里的所有资料
cursor.execute("SELECT * FROM `branch`;")
records = cursor.fetchall()
for record in records:
    print(record)

# 关闭 cursor()
cursor.close()
connection.close()

# 初始化，创建一个连线
connection = mysql.connector.connect(host='localhost',
                                     port='3306',
                                     user='root',
                                     password='Conclude_Selected7_Profile',
                                     database='sql_tutorial')
# 调用 cursor() 开始使用数据库
cursor = connection.cursor()

# 新增数据
# cursor.execute("INSERT INTO `branch` VALUES(4, '前台', 206);")

# 修改
# cursor.execute("UPDATE `branch` SET `manager_id` = 206 WHERE `manager_id` = NULL;")  # 这里的判断没有起作用

# 删除
cursor.execute("DELETE FROM `branch` WHERE `branch_id` = 4;")

# 取得部门表格里的所有资料
cursor.execute("SELECT * FROM `branch`;")
records = cursor.fetchall()
for record in records:
    print(record)

# 关闭 cursor()
cursor.close()
connection.commit()  # 如果修改了数据库就需要加上这个命令
connection.close()