mysql开发环境的搭建与基本使用

最新推荐文章于 2024-07-23 14:00:25 发布

gopyer

最新推荐文章于 2024-07-23 14:00:25 发布

阅读量399

点赞数 5

分类专栏： Mysql数据库文章标签： sql mysql

本文链接：https://blog.csdn.net/fuxily/article/details/139319056

版权

Mysql数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

安装docker

请参考：国内源安装docker时卡住或网络很慢怎么办

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

创建mysql目录，用于管理docker compose
```
jagitch@jagitch-MS-7B93:~$ mkdir mysql5.7
```

创建etc、var目录用于映射容器中mysql的配置和数据文件

jagitch@jagitch-MS-7B93:mysql5.7$ cd mysql5.7

jagitch@jagitch-MS-7B93:mysql5.7$ mkdir etc var

创建mysql的配置文件

jagitch@jagitch-MS-7B93:mysql5.7$ cd etc

jagitch@jagitch-MS-7B93:mysql5.7$ mkdir mysql

jagitch@jagitch-MS-7B93:mysql5.7$ touch mysql/my.cnf

my.cnf的内容如下：

[mysql]
default-character-set=utf8mb4

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

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

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

创建docker-compose.yaml文件

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

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

启动容器
```
docker compose up -d
```
此时就可以通过.env文件中配置的端口号连接该mysql了

使用mysql客户端访问数据库

安装mysql客户端

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

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

jagitch@jagitch-MS-7B93:~$ mysql -u root -h 127.0.0.1 -P 3306 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

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

查看mysql服务器中所有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

创建数据库

mysql> create database book_store;
Query OK, 1 row affected (0.00 sec)

选择刚创建的数据库

mysql> use book_store;
Database changed

创建表

myssql> 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.02 sec)

查看所有表

mysql> show tables;
+----------------------+
| Tables_in_book_store |
+----------------------+
| b_book               |
+----------------------+
1 row in set (0.00 sec)

查看指定表详情

mysql> 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.00 sec)

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

mysql> ALTER TABLE b_book MODIFY author varchar(100);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

添加表字段

mysql> ALTER TABLE b_book ADD created DATETIME DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表字段

mysql> ALTER TABLE b_book ADD created2 DATETIME;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE b_book DROP COLUMN created2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> CREATE TABLE b_test ( id int);
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE b_test;
Query OK, 0 rows affected (0.01 sec)

一般创建表，管理表结构都是数据库管理员来操作，我们需要创建一些用户给程序来使用，这避免了给程序使用的账号分配过大的权限，提高安全性。下面以创建一个banana用户为例
```
mysql> CREATE USER 'banana'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
```
%表示允许所有的IP，如果将%换成192.168.1.8，那么只有当客户端的IP地址是192.168.1.8时才允许连接，123456是登陆密码，这是一个非常弱鸡的密码，实际使用时请修改

给banana用户分配权限

mysql> GRANT SELECT ON book_store.* TO 'banana'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT ON book_store.* TO 'banana'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT UPDATE ON book_store.* TO 'banana'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT DELETE ON book_store.* TO 'banana'@'%';
Query OK, 0 rows affected (0.00 sec)只分配了b_book数据库下面所有表的增删改查权限，没有修改表结构和新增表删除表的权限。

刷新权限

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

打开一个终端，登陆mysql

jagitch@jagitch-MS-7B93:~$ mysql -u banana -h 192.168.3.12 \
-D book_store -P 3306 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

查询b_book表数据

mysql> select * from b_book;
Empty set (0.00 sec)

此时还没有数据

插入表数据

mysql> INSERT INTO b_book(name,author)
    -> VALUES("三国演义","罗贯中");
Query OK, 1 row affected (0.00 sec)

mysql> select * from b_book;
+----+--------------+-----------+------+---------------------+
| id | name         | author    | isbn | created             |
+----+--------------+-----------+------+---------------------+
|  1 | 三国演义     | 罗贯中    | NULL | 2024-05-30 11:08:00 |
+----+--------------+-----------+------+---------------------+
1 row in set (0.00 sec)

更新表数据

mysql> UPDATE b_book set isbn='978-7-5101-3674-0' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT * FROM b_book;
+----+--------------+-----------+-------------------+---------------------+
| id | name         | author    | isbn              | created             |
+----+--------------+-----------+-------------------+---------------------+
|  1 | 三国演义     | 罗贯中    | 978-7-5101-3674-0 | 2024-05-30 11:08:00 |
+----+--------------+-----------+-------------------+---------------------+
1 row in set (0.00 sec)

删除表数据

mysql> DELETE FROM b_book where name = '三国演义';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM b_book;
Empty set (0.00 sec)