MySQL 是常用的关系型数据管理系统之一（relational database management system，RDBMS），RDBMS 可以用来帮助用户存储，管理和检索数据。MySQL 是一个开源软件，同时非常强大灵活。这篇文章会介绍如何安装 MySQL，MySQL 的基本操作，以及如何使用 Python 来操作 MySQL。

安装 MySQL

Ubuntu

sudo apt-get update # 更新 apt package index
sudo apt-get install mysql-server # 安装 MySQL 包

CentOS

sudo yum install mysql-server
/etc/init.d/mysqld start

安装过程结束后，MySQL服务会自动启动。你可以使用systemctl status mysql来查看到当前的运行状态。你可以使用mysql -V来查看安装的版本。

MySQL 操作

连接到 MySQL 的命令行

在安装完成后，你可以在终端中输入命令来登陆到 MySQL。如果你是 Ubuntu 18.04 的系统，同时 mysql 版本在5.7以上，你可以使用：

sudo mysql -u root

来登陆到 MySQL 数据库。对于一些较早版本的 MySQL，你可以使用下面的命令登陆：

mysql -u user -p

其中-u表示用户名, -p表示登录密码。当登陆成功后，你就可以到 MySQL 的命令行中输入命令来执行操作了。如果忘记密码可以重新设置。

MySQL 的命令有下面两个特点：

  1. 所有的 MySQL 命令都以一个;结束一行，如果没有发现分号，命令不会被执行；
  2. 为了便于阅读，MySQL 的指令通常会使用大写字母，数据库名称，表的名称，字符串会使用小写字母，不过 MySQL 本身不会因为大小写报出错误。

创建和删除数据库

MySQL 把数据以多个 databases 的形式存储，每个databases 包含多个 tables。你可以通过下面的命令来查看目前的 databases：

mysql> SHOW DATABASES;

创建一个新的数据库使用命令CREATE DATABASE，例如下面的命令创建了一个名称为db1的 database：

mysql> CREATE DATABASE db1;

你可以再次通过SHOW DATABASES命令来查看当前有哪些数据库，从而确认db1被成功地创建了。在这个例子中，SHOW DATABASES的返回结果如下：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

如果不再需要一个数据库，可以使用DROP命令。例如下面的命令会删除删除数据库db1：

mysql> DROP DATABASE db1;

使用数据库

一个database可以包含多个tables。当有了一个 database 之后，我们可以向其中创建不同的 table 来填充数据。首先使用USE命令来打开一个数据库：

mysql> USE db1;

然后用SHOW命令来显示当前数据库中所有表：

mysql> SHOW TABLES;

由于目前db1数据库中还没有任何数据，所以你会看到程序返回一个Empty set结果。

Table 操作

创建和删除表

现在要在空的数据库中添加表格，这需要使用CREATE TABLE命令。下面的例子创建了一个关于国家土地面积和人口的表格：

CREATE TABLE countries (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
continent VARCHAR(20),
population INT,
area FLOAT,
last_update DATE);

这个表格的名称为countries，它包含6个列，其中id一列是一个自动增加的整型数字；name是国家的名称，数据类型为字符串，长度不超过20个字符；continent是国家的地理位置，数据类型也为长度不超过20个字符的字符串；population是国家的人口，数据类型为整型；area是国土面积，数据类型为一个浮点数；last_update是一行数据最后被修改的时间。

创建表格完成后，可以使用SHOW TABLES;命令来确认表格的存在，使用DESCRIBE countries;命令来查看表格每一列的数据类型。

如果不需要这个表格了，可以使用DROP命令来删除表格：

mysql> DROP TABLE db1;

创建好表格后可以使用INSERT INTO命令向表格中插入数据：

INSERT INTO countries (name, continent, population, area, last_update) VALUES ("China", "Asia", 1433783686, 9706961, "2019-07-01");
INSERT INTO countries (name, continent, population, area, last_update) VALUES ("India", "Asia", 1352642280, 3287590, "2019-07-01");
INSERT INTO countries (name, continent, population, area, last_update) VALUES ("United States", "Americas", 327096265, 9372610, "2019-07-01");

插入数据完成后，使用下面的语句来到countries中查询数据：

mysql> SElECT * FROM countries;

删除一行数据

DELETE FROM countries where name="India";

修改数据

UPDATE countries SET population=9999999 WHERE name="India";

添加一列或者删除一列到表格

ALTER TABLE potluck ADD gdp float;

ALTER TABLE potluck ADD gdp float AFTER name;

ALTER TABLE potluck DROP gdp;

使用 Python 来操作 MySQL

pip install mysql-connector-python

Python Pandas 数据导出到 MySQL

pip install sqlalchemy pymysql

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:password@localhost/db1")
con = engine.connect()
df.to_sql('df', con=con, if_exists='replace',index=False)

from sqlalchemy import create_engine, text
engine = create_engine("mysql+pymysql://root:password@localhost/db1")
result = engine.execute(text("SELECT * FROM countries;"))
for row in result.fetchall():
    print(row)

SQLite

.open db.sqlite3
.tables

SELECT * FROM auth_user;

获取帮助：.help

创建table

Method 1:

create table animals as
    select "dog" as kind, 4 as legs, 20 as weight union
    select "cat"        , 4        , 10           union
    select "ferret"     , 4        , 10           union
    select "parrot"     , 2        , 6            union
    select "penguin"   , 2        , 10           union
    select "t-rex"      , 2        , 12000;

Method 2:

CREATE TABLE animals (
    kind text not null, 
    legs int not null, 
    weight int not null
);

INSERT INTO animals(kind, legs, weight)
VALUES
    ("dog", 4, 20),
    ("cat", 4, 10),
    ("ferret", 4, 10),
    ("parrot", 2, 6),
    ("penguin", 2, 10),
    ("t-rex", 2, 12000);

Method 3

创建一个文本文件，写入SQL语句，然后在通过下面指令读入该文件：

.read animals.sql

删除table

DROP TABLE animals;

JOIN

create table people as
    select "Peter" as name, 1 as gender, 32 as age, "dog" as animal union
    select "John"         , 1          , 19       ,"parrot"         union
    select "Liu"          , 0          , 23       , "tiger"         union
    select "Lily"         , 0          , 31       , "cat";

SELECT *
FROM animals
CROSS JOIN people;

-- The INNER JOIN clause matches each row from the (people) table with every row from the (animals) table based on the join condition.

SELECT *
FROM people
INNER JOIN animals ON
    kind = animal;

-- The LEFT JOIN clause selects data starting from the left table (animals) and matching rows in the right table (people) based on the join condition.

SELECT p.name, a.kind
FROM people p
LEFT JOIN animals a ON
    a.kind = p.animal;

参考