十分钟带你入门mariadb数据库开发

概览

本文从零开始，手把手带领读者使用Docker安装mariadb数据库，并以一个book_store数据库为例，讲解了用户的创建、数据库表的创建修改查询等。

安装docker

参考另一文章《使用国内源安装docker》

使用容器化技术快速安装 mariadb

1. 创建mariadb目录，用于管理docker compose

   jagitch@jagitch-MS-7B93:~$ mkdir mariadb
   

2. 创建mariadb的配置文件

   jagitch@jagitch-MS-7B93:mariadb$ mkdir etc
   
   jagitch@jagitch-MS-7B93:mariadb$ touch etc/my.cnf
   

   my.cnf的内容如下：

   [mysql]
   default-character-set=utf8mb4
   
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_general_ci
   max_allowed_packet=32M
   

3. 创建.env文件配置环境变量

   jagitch@jagitch-MS-7B93:mysql5.7$ cat .env
   PORT=3306
   USER=test
   PASS=t123456
   ROOT_PASS=t12345678
   

4. 创建docker-compose.yaml文件

   jagitch@jagitch-MS-7B93:mysql5.7$ touch docker-compose.yaml
   

   services:
     db:
       image: mariadb
       restart: always
       container_name: mariadb
       env_file: .env
       volumes:
         - ./var/lib/mysql:/var/lib/mysql
         - ./var/log/mysql:/var/log/mysql
         - ./etc/my.cnf:/etc/my.cnf:ro
         - /etc/localtime:/etc/localtime:ro
       ports:
         - '$PORT:3306'
       environment:
         MYSQL_USER: "$USER"
         MYSQL_PASSWORD: "$PASS"
         MYSQL_ROOT_PASSWORD: "$ROOT_PASS"
         MYSQL_ROOT_HOST: '%'
   

5. 启动容器

   docker compose up -d
   

6. 此时就可以通过.env文件中配置的端口号连接该mysql了

使用mysql客户端访问数据库

1. 安装mysql客户端

   jagitch@jagitch-MS-7B93:~$ sudo apt install mariadb-client
   

2. 使用管理员账号登陆mysql服务器

   jagitch@jagitch-MS-7B93:mariadb$ mariadb -u root -h 127.0.0.1 --port 3306 -p
   Enter password: 
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 6
   Server version: 11.4.2-MariaDB-ubu2404 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)]> 
   

   管理员账号是root，密码配置在.env文件中。

3. 查看mariadb服务器中所有的数据库

   MariaDB [(none)]> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+
   4 rows in set (0.001 sec)
   

   show databases;查看所有的数据库，mysql语句需以分号结尾。

4. 创建数据库

   MariaDB [(none)]> create database book_store;
   Query OK, 1 row affected (0.000 sec)
   

5. 选择刚创建的数据库

   MariaDB [(none)]> use book_store;
   Database changed
   

6. 创建表

   MariaDB [book_store]> CREATE TABLE b_book( id int NOT NULL AUTO_INCREMENT,
        name varchar(200) NOT NULL,
        author varchar(50) NOT NULL,
        isbn varchar(30), 
        PRIMARY KEY(id)
        );
   Query OK, 0 rows affected (0.021 sec)
   

7. 查看所有表

   MariaDB [book_store]> show tables;
   +----------------------+
   | Tables_in_book_store |
   +----------------------+
   | b_book               |
   +----------------------+
   1 row in set (0.001 sec)
   

8. 查看指定表详情

   MariaDB [book_store]> desc b_book;
   +--------+--------------+------+-----+---------+----------------+
   | Field  | Type         | Null | Key | Default | Extra          |
   +--------+--------------+------+-----+---------+----------------+
   | id     | int(11)      | NO   | PRI | NULL    | auto_increment |
   | name   | varchar(200) | NO   |     | NULL    |                |
   | author | varchar(50)  | NO   |     | NULL    |                |
   | isbn   | varchar(30)  | YES  |     | NULL    |                |
   +--------+--------------+------+-----+---------+----------------+
   4 rows in set (0.001 sec)
   

9. 修改表字段，例如将author字段长度改为100

   MariaDB [book_store]> ALTER TABLE b_book MODIFY author varchar(100);
   Query OK, 0 rows affected (0.039 sec)              
   Records: 0  Duplicates: 0  Warnings: 0
   

10. 添加表字段

    MariaDB [book_store]> ALTER TABLE b_book ADD created DATETIME DEFAULT CURRENT_TIMESTAMP;
    Query OK, 0 rows affected (0.027 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

11. 删除表字段

    MariaDB [book_store]> ALTER TABLE b_book ADD created2 DATETIME;
    Query OK, 0 rows affected (0.029 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [book_store]> ALTER TABLE b_book DROP COLUMN created2;
    Query OK, 0 rows affected (0.029 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

12. 删除表，先创建表b_test，再删除b_test表

    MariaDB [book_store]> CREATE TABLE b_test ( id int);
    Query OK, 0 rows affected (0.019 sec)
    
    MariaDB [book_store]> DROP TABLE b_test;
    Query OK, 0 rows affected (0.015 sec)
    

13. 一般创建表，管理表结构都是数据库管理员来操作，我们需要创建一些用户给程序来使用，这避免了给程序使用的账号分配过大的权限，提高安全性。下面以创建一个banana用户为例

    MariaDB [book_store]> CREATE USER 'banana'@'%' identified by '123456';
    Query OK, 0 rows affected (0.009 sec)
    

    %表示允许所有的IP，如果将%换成192.168.1.8，那么只有当客户端的IP地址是192.168.1.8时才允许连接，123456是登陆密码，这是一个非常弱鸡的密码，实际使用时请修改

14. 给banana用户分配权限

    MariaDB [book_store]> GRANT SELECT ON book_store.* TO 'banana'@'%';
    Query OK, 0 rows affected (0.009 sec)
    
    MariaDB [book_store]> GRANT INSERT ON book_store.* TO 'banana'@'%';
    Query OK, 0 rows affected (0.009 sec)
    
    MariaDB [book_store]> GRANT UPDATE ON book_store.* TO 'banana'@'%';
    Query OK, 0 rows affected (0.009 sec)
    
    MariaDB [book_store]> GRANT DELETE ON book_store.* TO 'banana'@'%';
    Query OK, 0 rows affected (0.009 sec)
    

    只分配了b_book数据库下面所有表的增删改查权限，没有修改表结构和新增表删除表的权限。

15. 刷新权限

    MariaDB [book_store]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.001 sec)
    

使用新创建的banana用户进行增删改查

1. 打开一个终端，登陆mysql

   jagitch@jagitch-MS-7B93:lib$ mysql -u banana -h 127.0.0.1 \
   -D book_store -P 3307 -p
   Enter password: 
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 7
   Server version: 11.4.2-MariaDB-ubu2404 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 [book_store]>
   

2. 查询b_book表数据

   MariaDB [book_store]> SELECT * FROM b_book;
   Empty set (0.000 sec)
   

   此时还没有数据

3. 插入表数据

   MariaDB [book_store]> INSERT INTO b_book(name,author)
        VALUES("红楼梦","曹雪芹");
   Query OK, 1 row affected (0.009 sec)
   
   MariaDB [book_store]> SELECT * FROM b_book;
   +----+-----------+-----------+------+---------------------+
   | id | name      | author    | isbn | created             |
   +----+-----------+-----------+------+---------------------+
   |  1 | 红楼梦    | 曹雪芹    | NULL | 2024-06-01 12:06:52 |
   +----+-----------+-----------+------+---------------------+
   1 row in set (0.000 sec)
   

4. 更新表数据

   MariaDB [book_store]> UPDATE b_book set isbn='978-7-5101-3674-0' where id = 1;
   Query OK, 1 row affected (0.009 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   MariaDB [book_store]> SELECT * FROM b_book;
   +----+-----------+-----------+-------------------+---------------------+
   | id | name      | author    | isbn              | created             |
   +----+-----------+-----------+-------------------+---------------------+
   |  1 | 红楼梦    | 曹雪芹    | 978-7-5101-3674-0 | 2024-06-01 12:06:52 |
   +----+-----------+-----------+-------------------+---------------------+
   1 row in set (0.000 sec)
   

5. 删除表数据

   MariaDB [book_store]> DELETE FROM b_book where name = '红楼梦';
   Query OK, 1 row affected (0.002 sec)
   
   MariaDB [book_store]> SELECT * FROM b_book;
   Empty set (0.000 sec)
   

总结

本文详细介绍了如何使用Docker安装Mariadb，并使用mariadb-client演示了mariadb数据库、用户、数据库表的创建，以及如何向mariadb数据库存取数据。

推荐阅读

1. 教你如何1秒安装一个Linux系统（ubuntu,debian,centos） 比虚拟机安装系统省时省心省力

2.【Git从入门到精通】系列课程02：从0开始为项目使用git进行版本控制

3.【Go零基础系列】002：第一个Go程序