SAST2023笔记——数据库相关

mysql

许多代码都需要在进程外存储一些数据（比如电子系的程设作业成绩管理系统…），对于小白程序员来说最简单的方法就是把数据存入文件中，有需要的时候读取文件。但显然对文件的读写操作效率太过低下，我们需要一个兼具效率、便捷性、易用性的工具，于是数据库便闪亮登场了。关系型数据库另一个特殊的作用就是可以利用外键建立表之间的关系，让我们稍后解释。

安装

首先，请运行

1
2
3

sudo apt update
sudo apt upgrade
sudo apt install mysql-server

来安装mysql。届时可用service mysql start来启动mysql。

创建新用户

我们先运行mysql -u root -p来登录 mysql 的 root 用户，之后你应该会发现终端形如：mysql> 。我们运行CREATE USER 'admin'@'%' IDENTIFIED BY '123456';来创造一个名为 admin 、密码为 123456 的用户。（请注意， mysql 需要我们打出 ‘ ; ‘ 来运行命令！）然后我们在 root 里为 admin 用户添加所有数据库的权限：GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';，此时即可用exit;退出 root 用户。（我在使用 root 用户的时候遇到了一些问题，所以推荐大家通常情况下像这样新建用户来使用数据库）在终端里再次运行mysql -u admin -p，然后输入密码 123456 即可用 admin 身份登录 mysql 。（或者使用mysql -u admin -p123456可以直接登录，但这样的话密码就直接暴露在 shell 日志中了，服务器中慎用！）此时可以运行SELECT USER();来查看当前用户。（值得一提， SELECT 命令基本都是显示当前的 xxx 比如当前用户、当前所在数据库等等，而 SHOW 命令基本都是显示所有的 xxx 比如所有用户，所有数据表等等）

创建数据表

请注意，我们现在刚刚登入这个用户，还没有选定任何数据库，所以我们需要运行：

1
2
3

CREATE DATABASE hello;

USE hello;

来创造一个名为 hello 的数据库并使用它，此时可以使用SHOW DATABASES;来查看所有数据库（里面会有一些自带的系统库）或是SELECT DATABASE();来查看当前数据库（这里应该会显示 hello ）。

然后我们运行：

CREATE TABLE Students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sid CHAR(10) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    major VARCHAR(255) NOT NULL
);

CREATE TABLE Courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cid VARCHAR(8) NOT NULL UNIQUE,
    title VARCHAR(255) NOT NULL,
    credit INT NOT NULL,
    time VARCHAR(255)
);

CREATE TABLE Association (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(2),
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES Students(id),
    CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES Courses(id)
);

来创造一些示例数据表。请注意，数据库是数据表的集合，数据库由若干数据表组成。

让我们来依次看一下这三个数据表都有什么： Students 里面有主键id，被赋予了AUTO_INCREMENT类型，这意味着缺省值将是递增的自然数，还有sid是一个不能为NULL的、不能重复（UNIQUE）的值，其余元素不再赘述； Association 里有外键 student_id 和 course_id ，他们分别外链到了 Students 和 Courses 两张数据表中的 id 属性。这里外链的效果导致假如 Association 的某条数据的 student_id 并不是真实存在的 Students 的 id ，那么就会报错。

我们可以用SHOW TABLES;来查看当前数据库中的所有数据表、可以使用DESC Association;来查看某个数据表中的所有字段、也可以使用SELECT * FROM Courses;来暴力查看整张表。（虽然但是，用 vscode 的拓展插件就可以快速可视化…）

此时我们尝试一些其他的操作：比如给 Studens 添加新字段：ALTER TABLE Students ADD gender VARCHAR(10);、删除已有字段：ALTER TABLE Students DROP COLUMN gender;、修改已有字段：ALTER TABLE Students MODIFY COLUMN sid CHAR(20);。在有权限的时候，我们还可以删库跑路：DROP TABLE Students;。（慎用！！）

最后，我们可以用 INSERT命令来插入具体的每条数据。（终于到这一步了…）运行INSERT INTO Courses (cid, title, credit,time) VALUES('20230002', 'English', '2','3-2');即可。剩余数据请照搬此法自行添加。

最终数据库效果如图（安装了vscode的Mysql插件）：

PyMySQL

仅仅在终端中使用数据库未免有些太过简陋，我们实际上也可以在 Python 中使用 MySQL 。我们可以尝试运行如下代码插入数据：

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost', #本地连接
                             user='admin', #用户名
                             password='123456', #密码
                             database='hello', #数据库名
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    # Create a new record in Students table
    sql = "INSERT INTO `Students` (`sid`, `name`, `major`,`gender`) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql, ('2023000001', 'John', 'Computer Science', 'male'))

# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()