数据库编程基础
一、前言
在以往我们学习Python编程的过程中，若想持久性保存数据，只有一种手段，即将数据写入文件中！但是将数据写入文件又过于随意，数据何种格式存入完全看人！
比如，我们用台式机玩游戏，单机游戏存档只能存在本地，当我们出差带着笔记本出门，闲时又想玩一下时就没招了，要么提前备份存档等等，无论如何都是比较麻烦的！
那么，能不能将它变成弱联网单机游戏？即游戏官方给玩家们提供一个仓库，专门用于存放游戏存档，所有玩家只需要从这里下载存档载入游戏即可！这时候就要提到数据库了！
二、数据库基本概念
2.1、何为数据库
数据库，顾名思义即存放数据的仓库。只不过此库并非类似存储粮食的地窖等，是存储于计算机存储设备之上，而且数据按照特定的规则进行存放！
好比图书馆存放书籍，是一个书架一个书架摆放着各种书籍，当现代社会数据量过于庞大时，这种方法就不够用了！我们可以通过数据库比较方便地将海量数据存放入计算机存储设备内，从而节省空间与成本！
2.2、何为数据库系统
上面提到了数据库的基本概念，其实数据库系统也是一款大型应用程序，它能够科学有效地存、取数据，这是核心！用我们之前学过的知识点来解释，数据库系统就是一款基于网络通信的应用程序！既然是一款应用程序，那当然很多人都可以写出来（主要是你能写成什么样。。。）！所以，目前市面上有很多款数据库管理系统，我们可以给它们简单分两类：
2.3.1、关系型数据库
数据之间彼此有关系或约束，数据通常以表格存储！比如说库里有一段数据：id:1 age:18 name:'sanxi'，一看就知道是一个人的信息了，age、name、id之间彼此有关系，因为合起来是代表一个人的基本信息！
- 开源界标杆：MySQL
- 开源界新星：PostgreSQL
- 甲骨文的商业数据库：Oracle
- 巨硬微软家的：SQL Server
2.3.2、非关系型数据库
存储数据通常以K/V键值对形式，类似Python中字典格式！此类数据库多用于充当缓存用！
- Redis
- MongoDB
2.3、库、表、字段概念
关系型数据库存储数据按照不同级别来存放，级别从左到右分别递减：库、表、字段，对应行政区中：省、市、乡一样！
库
即文件夹！
表
即文件
字段
即文件内一行行的数据（字符串）
2.4、SQL语句由来
以MySQL为例吧，它也是C/S架构，它不单单支持MySQL自己的客户端，还支持其它编程语言对MySQL进行数据相关的操作！编程语言有那多种，怎么能做到兼容与识别各种各样的编程语言呢？
好比字符编码，在没有Unicode以前，各国都拥有不同的字符编码格式，乱象横生，直到Unicode的出现统一了编码格式！哎，既然是要让所有编程语言都能用，那我也提供一种统一标准好了，所有编程语言都按照我规定的标准来进行交互！就像当年秦始皇一统天下后统一度量衡、文字、郡县制等等千古功绩！在数据库领域，这种标准叫SQL语句！不管你是用什么客户端、何种编程语言，按照规定的语句我就能识别你的操作！
SQL语句必须以分号;为结尾！！！
三、MySQL安装与基本配置
在实际工作环境中，我们不会轻易使用某款软件的最新版本，因为新版本可能会出现各种问题，或者与当前环境不兼容而导致一系列问题发生！因此，我们多数使用LTS，全称long-term support，即长期支持版，因为生产环境稳定性比新功能更重要！
MySQL是我们最常用的开源关系型数据库，后来被Oracle收购，也不知道会不会闭源甚至消失，毕竟Oracle什么德行大家有目共睹！所以MySQL作者又开启了MySQL分支：MariaDB，高度兼容MySQL！在实际工作环境，多数数据库版本还是5.6-5.8版本！
先来一张mariadb与MySQL版本对应图吧，官网扒下来的！
3.1、Linux下安装
会玩Linux，还需要看我这教程吗。。。所以本文主要针对Windows平台！
3.2、Windows下安装
3.2.1、下载MySQL
尽量使用国内镜像源站，它们下载速度较官网快！
- 官网下载：https://mariadb.com/downloads/，挑选版本和对应平台下载！
- 清华镜像站（推荐）：https://mirrors.tuna.tsinghua.edu.cn/mariadb/
3.2.2、安装MySQL
最省事做法是跟我一样，直接下载MSI安装包一键安装，步骤如下：
这里没什么特别需要，默认即可！
安装完后，桌面会有一个图形化客户端：HeidiSQL，但是我们初学者先从命令行窗口学起，扎实基础最重要！所以点击开始菜单，点击MySQL client对应客户端即可直接打开客户端命令行窗口！
这是命令行方式启动：
这是图形化界面启动：
3.3、配置环境变量及系统服务
如果是按照我上面的方法安装MySQL是不需要单独启动服务端mysqld与客户端mysql的，只有下载了免安装包解压后才需要配置环境变量和系统服务！
Windows命令行可以用CMD，也可以用powershell，Windows键+X键选择即可！
3.3.1、Windows知识点补充
查看当前指定进程
PS C:\Users\AERO> tasklist | findstr mysql
mysqld.exe 14548 Services 0 345,032 K
杀死指定进程（只能在管理员下才能执行）
PS C:\Users\AERO> taskkill /F /PID 14548
错误: 无法终止 PID 为 14548 的进程。
原因: 拒绝访问。
3.3.2、环境变量配置
如果是免安装版，每次启动mysql客户端都得先在命令行指定绝对路径的程序才能启动台麻烦，我们可以将mysqld所在的文件路径添加到系统环境变量中即可！桌面右键计算机-属性-高级系统设置-高级-环境变量，变量名填入mysql，变量值填你的实际文件夹路径，确定即可！
注意：有用户变量和系统变量，区别是用户变量仅对你当前用户生效，系统变量则是对所有用户生效！
3.3.2、配置系统服务
如果是免安装版，每次得去命令行先启动服务端太烦了，我们可以将MySQL添加到系统服务中并设置开机自启动！首先得去打开powershell，cd到MySQL的bin目录
PS C:\Windows\system32> cd "C:\Program Files\MariaDB 10.3\bin" # 目录或文件名有空格时，必须用引号引起来！
PS C:\Program Files\MariaDB 10.3\bin> .\mysqld --install # 指定mysqld为系统服务
The current server installed: "C:\Program Files\MariaDB 10.3\bin\mysqld.exe" "--defaults-file=C:\Program Files\MariaDB 10.3\data\my.ini" "MySQL"
PS C:\Program Files\MariaDB 10.3\bin> .\mysqld --remove # 移除MySQL服务
搜索框输入service.msc打开系统服务窗口，找到刚添加的MySQL，右键属性-设置为自动启动即可开机自启动mysqld服务端
3.4、连接mysqld服务端
按照我的方式按照直接点开快捷方式，输入密码即可登录MySQL（无密码则直接回车）！如果是免安装版，使用以下格式：
PS C:\Users\AERO> mysql -h 127.0.0.1 -P 3306 -u root -p
-h，即服务端地址，本机即127.0.0.1
-P，即MySQL端口，默认是3306
-u，指定登录用户
-p，密码，不要把密码写在选项后面，这样别人就知道你密码是什么了，不写密码则回车后密文方式输入比较安全！
3.5、密码相关
3.5.1、修改密码
这是你还记得密码的情况下在bin目录的操作
PS C:\Program Files\MariaDB 10.3\bin> .\mysqladmin -u root -p password # 谨记莫将密码明文写出来
Enter password: # 原密码
New password: ******** # 新密码
Confirm new password: ******** # 再次确认
3.5.2、重置密码
跳过密码验证
如果忘了密码，需要跳过授权表，先停止mysqld服务，命令行方式启动时跳过用户名密码验证功能！
PS C:\Program Files\MariaDB 10.3\bin> .\mysqld --skip-grant-tables
2021-01-18 21:04:03 0 [Note] C:\Program Files\MariaDB 10.3\bin\mysqld.exe (mysqld 10.3.27-MariaDB) starting as process 16420 ...
# 此时再去客户端连接服务端就不需要密码了
PS C:\Program Files\MariaDB 10.3\bin> .\mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.27-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
修改当前用户密码
跳过密码验证后，我们当然要去重新设置密码啦！修改当前用户的密码命令如下：先强调一下，SQL语句跟Python语法一样，只是一种规范而不是强制性要求，但是我们立志作为一名合格、正规的程序员，应该严格按照规范来写！以后SQL语句全部用大写，这是一种规范！
MariaDB [(none)]> UPDATE mysql.user SET PASSWORD=password('sanxi777') WHERE USER='root' and HOST='localhost';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0
刷新权限
更改用户权限后需要立即刷新权限才能生效
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.119 sec)
退出MySQL
MariaDB [(none)]> quit # quit或者exit都行，这两命令不需要区别大小写，它们连分号都不用也行！
正常方式重启服务端并登陆MySQL
3.6、MySQL配置文件修改
MSI安装版
此版的配置文件在data目录下有一个my.ini配置文件
免安装版
在MySQL安装目录下，有一个my-default.ini配置模板，我们需要自建个后缀为.ini的配置文件
配置文件格式
MySQL配置文件，改动需要重新启动才能生效！分三大块：分别对应服务端、客户端、其它客户端
- [mysqld] # 一旦服务端启动立即加载它下面的配置
- [mysql] # 一旦有客户端连接进来立即加载它下面的配置
- [client] # 一旦有客户端连接进来立即加载它下面的配置，和上面的区别是这里是各种编程语言的连接接口
四、SQL语句基础
大部分程序的业务逻辑其实都是增删改查操作，这又分三大块：
4.1、针对库的增删改查
增
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name COLLATE [=] collation_name
MariaDB [(none)]> CREATE DATABASE sanxi;
Query OK, 1 row affected (0.006 sec)
# 或者这样
MariaDB [(none)]> CREATE DATABASE haha charset=utf8; # 直接指定字符集编码
Query OK, 1 row affected (0.003 sec)
查
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| haha |
| information_schema |
| mysql |
| performance_schema |
| sanxi |
| test |
+--------------------+
6 rows in set (0.019 sec)
# 或者这样可以查看数据库相关信息
MariaDB [(none)]> SHOW CREATE DATABASE haha;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.001 sec)
改
ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name COLLATE [=] collation_name
MariaDB [(none)]> ALTER DATABASE haha CHARSET='UTF8';
Query OK, 1 row affected (0.002 sec)
删
实际工作环境不能随便删除数据哈！！！不然真就《论MySQL从删库到跑路》了！
MariaDB [(none)]> DROP DATABASE haha; # 直接删除整个数据库
Query OK, 0 rows affected (0.050 sec)
4.2、针对表的增删改查
在操作表的时候，需要提前切换库或者指定库！
切换当前库
MariaDB [(none)]> USE sanxi;
Database changed
MariaDB [sanxi]>
增
MariaDB [sanxi]> CREATE TABLE student(id int,name char(4),age int); # 创建表student并添加字段，后续再详细介绍字段！
Query OK, 0 rows affected (0.041 sec)
查
查看当前库的所有表
MariaDB [(none)]> USE sanxi; # 需要指定数据库
Database changed
MariaDB [sanxi]> SHOW TABLES; # 查看当前库的所有表
+-----------------+
| Tables_in_sanxi |
+-----------------+
| student |
+-----------------+
1 row in set (0.001 sec)
查看表信息
方式一：
MariaDB [sanxi]> SHOW CREATE TABLE student; #
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` char(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.022 sec)
方式二：更为美观
MariaDB [sanxi]> DESC student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.059 sec)
describe TABLE; 直接简写desc TABLE
改
修改表中字段信息
MariaDB [sanxi]> ALTER TABLE student modify name char(16);
Query OK, 0 rows affected (0.114 sec)
Records: 0 Duplicates: 0 Warnings: 0
删
再次强调，不要随便删数据！！！
方式一
MariaDB [(none)]> USE sanxi;
Database changed
MariaDB [sanxi]> DROP TABLE student; # 直接删除整张表
Query OK, 0 rows affected (0.051 sec)
方式二
MariaDB [sanxi]> DROP TABLE sanxi.student; # 指定绝对路径删除表
Query OK, 0 rows affected (0.021 sec)
4.3、针对数据的增删改查
增
MariaDB [sanxi]> INSERT INTO student VALUES(1,'sanxi',18); # 往表中添加数据，必须一一对应字段，且为元祖格式
Query OK, 1 row affected (0.023 sec)
查
查看表中所有字段所有数据
该命令当表中数据量特别大时不建议使用
MariaDB [sanxi]> SELECT * FROM student;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | sanxi | 18 |
+------+-------+------+
1 row in set (0.002 sec)
查看指定字段的数据
平常建议使用这种方式查看指定字段的数据
MariaDB [sanxi]> SELECT name,age FROM student;
+-------+------+
| name | age |
+-------+------+
| sanxi | 18 |
+-------+------+
1 row in set (0.001 sec)
改
涉及修改数据或配置时，一定要加条件，不然就变成所有数据全部改掉了！
MariaDB [sanxi]> UPDATE student SET name='chrystal' WHERE id=1; # WHERE后面跟条件，满足条件的才处理
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [sanxi]> SELECT name,age FROM student; # sanxi已经被改成chrystal了！
+----------+------+
| name | age |
+----------+------+
| chrystal | 18 |
+----------+------+
1 row in set (0.000 sec)
删
一定要加条件再删数据，当然了，不要随意删除数据！！！
MariaDB [sanxi]> DELETE FROM student WHERE id=1;
Query OK, 1 row affected (0.003 sec)
MariaDB [sanxi]> SELECT name,age FROM student;
Empty set (0.000 sec)
4.4、字符集设置
4.4.1、查看配置信息
\s可以显示当前用户配置信息，编码不一致可能会导致乱码问题，最好统一编码标准！
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 10.3.27-MariaDB, for Win64 (AMD64), source revision dba846ce2a4c57363c4f0256b0e6d2dd1a55ac40
Connection id: 12
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server: MariaDB
Server version: 10.3.27-MariaDB mariadb.org binary distribution
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8 # 默认是ladin，我安装时选择了UTF-8
Db characterset: utf8 # 默认是ladin，我安装时选择了UTF-8
Client characterset: gbk # 最好统一编码为UTF-8
Conn. characterset: gbk # 最好统一编码为UTF-8
TCP port: 3306
Uptime: 35 min 7 sec
Threads: 7 Questions: 9 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 10 Queries per second avg: 0.004
--------------
4.4.2、修改字符编码
命令行方式：
这样改可以全局生效，不加global没意义，关闭当前窗口就恢复原来的样子了！
- SET GLOBAL CHARACTER_SET_DATABASE=utf8;
- SET GLOBAL CHARACTER_SET_SERVER=utf8;
- SET GLOBAL CHARACTER_SET_CLIENT=utf8;
- SET GLOBAL CHARACTER_SET_CONNECTION=utf8;
- SET GLOBAL CHARACTER_SET_RESULTS=utf8;
MariaDB [(none)]> SET GLOBAL CHARACTER_SET_DATABASE=utf8;
Query OK, 0 rows affected (0.000 sec)
配置文件方式
[mysqld]
CHARACTER_SET_SERVER=utf8
CHARACTER_SET_DATABASE=utf8
[mysql]
DEFAULT-CHARACTER-SET=utf8
[client]
DEFAULT-CHARACTER-SET=utf8