一、SQL 与 MySQL 的基础认知

SQL，即结构化查询语言，是一种具有多种功能的数据库语言，它以交互性强为特点，为用户提供极大便利。数据库管理系统充分利用 SQL 语言能提高计算机应用系统的工作质量与效率。它不仅能独立应用于终端，还能作为子语言为其他程序设计提供助力，可与其他程序语言一起优化程序功能，为用户提供更全面的信息。

数据库主要分为关系型数据库和非关系型数据库。关系型数据库强调以二维表格形式存储数据，如 MySQL、ORACLE、DB2、Sqlserver、SQLite 等。SQL 是所有关系型数据库管理系统都要遵循的规范，其分类包括 DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）、DCL（数据控制语言）。DDL 用来定义数据库对象，关键字有 create、alter、drop 等；DML 用来对数据库中表的记录进行更新，关键字有 insert、delete、update 等；DQL 用来查询数据库中表的记录，关键字有 select、from、where 等；DCL 用来定义数据库的访问权限和安全级别。

MySQL 是最流行的关系型数据库管理系统之一，需要遵循 SQL 规范。SQL 规范如同普通话，各数据库厂商都遵循的 ISO 标准，而 MySQL 特有的关键字等如同方言。MySQL 6 以后开始收费，本次课程使用社区开源免费版。

二、MySQL 的连接与数据类型

（一）MySQL 连接方式

• 登录方式：方式 1：mysql -u 用户名 -p 密码。
• 方式 2：mysql -u 用户名 -p回车后再输入密码。
• 方式 3：mysql -h 主机 ip 地址 -u 用户名 -p回车后再输入密码。
• 使用 MySQL.sock 来进行本地登陆，
• 如：mysql -S /mysqldata/3309/socket/mysql.sock -u root -p。通过mysql_config_editor工具将登陆 MySQL 服务的认证信息加密保存在.mylogin.cnf文件，实现免密登录，安全性最高。编辑.my.cnf文件实现免密登录，但要保证文件为该用户可读，防止明文存储的密码被泄露。编辑/etc/my.cnf文件实现免密登录，但此方式最大问题是明文存储密码，见配置文件各用户可见，非常的不安全，不推荐使用。
• 登出方式：
• 方式 1：exit。
• 方式 2：quit。
• 方式 3：\q。
• 连接指定主机：在命令行终端中，使用以下命令连接 MySQL 指定的主机：mysql -h 主机名 -u 用户名 -p。连接成功后，可以执行 SQL 语句，不再需要连接时可使用exit退出 MySQL 命令行工具。

（二）MySQL 数据类型

• 整数：int，默认长度是 11 位，例如存储用户编号等整数类型的数据。
• 浮点数：float：Python 默认浮点类型，可用于存储带有小数的数值数据。double：Java 默认浮点类型，适用于需要更高精度的浮点数存储。decimal(x,y)：x 是有效位数默认 10 位，y 是小数点后位数默认是 0，能提供更精确的小数存储。
• 日期时间：date：存储年月日，如用户的出生日期。datetime：存储年月日时分秒，用于记录具体的时间点。timestamp：时间戳，从 1970 年 1 月 1 日到现在的秒/毫秒，可用于记录事件发生的时间。
• 字符串类型：char()：固定长度的字符数据，适用于长度固定的字符串存储。varchar(可变长度)，Python 中对应string，对于长度不固定的字符串存储更加灵活。

（三）MySQL 常用编码

• gbk：属于中国编码，主要用于解决中文汉字编码问题。
• utf - 8：国际编码，能够解决不同国家的字符编码问题，被广泛应用于多语言环境下的数据存储。
• SQL 代码文件一般用.sql结尾。

三、MySQL 的核心操作

（一）数据库操作

数据库相关英文单词为database。

• 创建数据库：create database [if not exists] 库名 [DEFAULT CHARACTER SET utf8];。例如，create database test1;，如果库已存在再次创建会报错，可使用if not exists解决，如create database if not exists test1;，也可指定编码，如create database test3 CHARACTER SET utf8;。
• 删除数据库：MySQL 删除数据库有多种方式，如drop database 库名;是直接删除表信息，速度最快，但是无法找回数据，例如删除user表可以使用drop table user;。truncate (table)是删除表数据而不是删除表结构，速度第二快，不能与where一起使用，例如删除user表可以使用truncate table user。delete from是删除表中的数据，没用删除表中的结构，速度最慢，但是可以与where连用，可以删除指定的行，例如删除user表中的所有数据可以使用delete from user;，删除user表的指定记录可以使用delete from user where user_id = 1。三种方式的区别在于：相同点是truncate和不带where子句的delete，drop都会删除表内的数据；drop，truncate都是DDL语句（数据定义语言），执行后会自动提交。不同点是语句类型不同，delete语句是数据库操作语言（DML），truncate，drop是数据库定义语言（DDL）；效率一般来说drop > truncate> delete；是否删除表结构方面，truncate和delete只删除数据不删除表结构，truncate删除后将重建索引（新插入数据后id从0开始记起），而delete不会删除索引（新插入的数据将在删除数据的索引后继续增加），drop语句将删除表的结构包括依赖的约束，触发器，索引等；安全性方面，drop和truncate删除时不记录MySQL日志，不能回滚，delete删除会记录MySQL日志，可以回滚；返回值方面，delete操作后返回删除的记录数，而truncate返回的是0或者-1（成功则返回0，失败返回-1）。总结来说，希望删除表结构时，用drop；希望保留表结构，但要删除所有记录时，用truncate；希望保留表结构，但要删除部分记录时，用delete。
• 切换数据库：use 库名;，例如use test1;。
• 查看所有数据库：show databases;。
• 查看指定库的建库语句：show create database 库名;，例如show create database test1;。
• 查看当前使用的是哪个数据库：select database();。

（二）表的基本操作

表相关英文单词为table。

• 创建表：create table 表名(字段名 字段类型 [约束], 字段名 字段类型 [约束],...);，例如create table student(id int, name varchar(100), age int);，可使用if not exists避免已存在时报错，如create table if not exists student2(id int, name varchar(100), age int);。
• 删除表：MySQL 删除表也有多种方式，同删除数据库类似，有drop table、truncate table和delete from三种方式，具体区别可参考删除数据库的说明。例如drop table student2;。
• 修改表名：rename table 旧表名 to 新表名;，例如rename table student to stu1;。
• 查看所有表：show tables;。
• 查看指定表的结构：可以用DESCRIBE、DESC、EXPLAIN、SHOW COLUMNS FROM或SHOW FIELDS FROM表名等语句，它们的效果是一样的。例如desc stu1;。
• 直接使用某个数据库中的某个表：语法为数据库名.表名。
• 修改表：在创建好表之后，如果对表的结构不满意，可以使用ALTER TABLE语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。具体语法如ALTER TABLE <表名> [修改选项]，修改选项的语法格式如下：{ ADD COLUMN <列名><类型> | CHANGE COLUMN <旧列名><新列名><新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名><类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }。例如修改表名可以使用ALTER TABLE <旧表名> RENAME [TO] <新表名>；修改表字符集可以使用ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>；修改字段名称可以使用ALTER TABLE <表名> CHANGE <旧字段名><新字段名><新数据类型>；修改字段数据类型可以使用ALTER TABLE <表名> MODIFY COLUMN <列名><新数据类型>；删除字段可以使用ALTER TABLE <表名> DROP COLUMN <列名>。

MySQL 的表操作是数据库管理中非常重要的部分，熟练掌握这些操作可以更好地管理和维护数据库。

四、MySQL 的进阶应用

1. 创建带有外键的表

创建带有外键的表可以使用以下语法：CREATE TABLE child_table (id INT NOT NULL,parent_id INT, name VARCHAR(255),FOREIGN KEY (parent_id) REFERENCES parent_table (id));。例如，创建一个部门表department和一个课程表course，可以这样写：createtable department(dept_name varchar(20),budget int,descript varchar(20),primary key(dept_name));createtable course(course_id varchar(20),deptnames varchar(20),credits int,foreign key(deptnames) references department(dept_name));。在实际应用中，若要删除department和course这两个表，则必须先删除course表，再删除department表。

2. 外键的作用

外键在 MySQL 中起着重要的作用，主要有以下几点：

• 保持数据一致性和完整性：确保子表中的数据与父表中的相关数据对应，防止出现无效数据。例如，在订单表和产品表中，订单表中的产品 ID 外键引用了产品表中的产品 ID 主键，当在产品表中删除一条记录时，外键约束将阻止在未删除引用该记录的所有订单表记录之前进行该操作，从而防止数据的不一致性。
• 强制数据级联：可以设置外键规则，使它自动级联执行操作。当删除或更新表的主键记录时，可以使子表关联的数据也跟着一起操作。例如，可以设置ON DELETE CASCADE和ON UPDATE CASCADE，当父表中的数据被删除或更新时，子表中的相关数据也会被自动删除或更新。
• 优化查询性能：通过使用外键索引，MySQL 可以优化表之间的查询性能。索引可以快速查找子表中引用特定主键记录的记录，从而减少扫描大量数据的需要。

3. 数据库设计的重要性

良好的数据库设计可以显著提高应用程序的性能、维护性和可扩展性。在设计数据库时，需要考虑数据的一致性、完整性、可用性和可扩展性等因素。例如，可以使用数据库规范化来减少数据冗余和提高数据完整性，将易变和不易变的数据部分分离，以提高读写性能等。

4. 设置自增键初始值

在 MySQL 中，可以使用ALTER TABLE语句来设置自增键的初始值。例如，alter table test AUTO_INCREMENT = 1000;可以将表test的自增键初始值设置为 1000。如果想要将自增主键归零，可以使用truncate table表名的方法，或者使用dbcc checkident (’table_name’, reseed, new_reseed_value)，将当前值设置为new_reseed_value。

5. 使用 pymysql 进行 Python 与 MySQL 交互

Python 可以使用pymysql库与 MySQL 数据库进行交互。安装pymysql库后，可以使用以下代码连接到 MySQL 数据库：

可以使用以下代码创建表、插入数据、查询数据、更新数据和删除数据：