shell脚本连接、读写、操作mysql数据库实例

Shell连接、读写、操作MySQL

前置条件

在使用过程中需要安装mysql-client，可以使用以下命令完成：

sudo apt-get install mysql-client

另外，还需要安装以下软件包：

• mysql

sudo apt-get install mysql-server

• mysql-connector-python

pip install mysql-connector-python

连接MySQL数据库

连接MySQL数据库，需要使用以下命令：

$ mysql -h <host> -u <username> -p

其中：

• -h <host>：指定连接的MySQL主机
• -u <username>：指定连接的MySQL用户
• -p：指定需要输入密码进行连接

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql>

连接成功后，可以在终端输入show databases;命令来查看当前数据库列表。

读取数据

读取MySQL数据库，需要先连接到相应的数据库，然后使用SELECT命令查询数据。

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql> use mydatabase;
Database changed
mysql> SELECT * FROM mytable;
+----+--------+-----------+
| id | name   | age       |
+----+--------+-----------+
|  1 | Alice  |        21 |
|  2 | Bob    |        25 |
|  3 | Charlie|        32 |
+----+--------+-----------+
3 rows in set (0.00 sec)

写入数据

写入MySQL数据库，需要先连接到相应的数据库，然后使用INSERT命令插入数据。

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql> use mydatabase;
Database changed
mysql> INSERT INTO mytable (name, age) VALUES ('David', 28);
Query OK, 1 row affected (0.01 sec)

操作MySQL

操作MySQL数据库，需要先连接到相应的数据库，然后使用相应的命令进行操作。

创建表

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql> use mydatabase;
Database changed
mysql> CREATE TABLE user (
    ->   id INT NOT NULL AUTO_INCREMENT,
    ->   name VARCHAR(50) NOT NULL,
    ->   age INT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

修改表

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql> use mydatabase;
Database changed
mysql> ALTER TABLE user ADD COLUMN gender VARCHAR(10) DEFAULT 'unknown';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

示例：

$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19-0ubuntu5 (Ubuntu)

...

mysql> use mydatabase;
Database changed
mysql> DROP TABLE user;
Query OK, 0 rows affected (0.01 sec)

示例代码

#!/bin/bash 
HOST="127.0.0.1" 
USER="root" 
PASS="password" 
DB="mydatabase" 

mysql --user=$USER --password=$PASS --host=$HOST $DB << EOF 
    SELECT * FROM mytable; 
EOF

import mysql.connector

cnx = mysql.connector.connect(user='root', password='password',
                              host='127.0.0.1',
                              database='mydatabase')

cursor = cnx.cursor()

query = ("SELECT * FROM mytable")

cursor.execute(query)

for (id, name, age) in cursor:
  print("{} - {} - {}".format(id, name, age))

cursor.close()
cnx.close()

以上就是Shell脚本连接、读写、操作MySQL数据库实例的完整攻略。