MySQL|Chapter3 Tutorial

安装 Mysql

  • 安装客户端与服务器

    1
    2
    3
    4
    5
    # 安装服务器
    sudo apt install mysql-server

    # 安装客户端
    sudo apt install mysql-client

  • 使用 netstat 工具查看是否安装成功

    1
    2
    3
    4
    5
    6
    7
    # 安装网络工具(netstat命令)
    sudo apt install net-tools

    sudo netstat -tap | grep mysql
    # t: tcp
    # a: all(Show both listening and non-listening sockets)
    # p: program(Show the PID and name of the program to which each socket belongs)

  • 查询默认密码(会随机生成),ubuntu 默认账号是 debain-sys-maint

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    sudo cat /etc/mysql/debian.cnf

    # [client]
    # host     = localhost
    # user     = debian-sys-maint
    # password = xxxxxxxxxxxxxxxx
    # socket   = /var/run/mysqld/mysqld.sock
    # [mysql_upgrade]
    # host     = localhost
    # user     = debian-sys-maint
    # password = xxxxxxxxxxxxxxxx
    # socket   = /var/run/mysqld/mysqld.sock

  • 登陆 mysql(使用默认提供的 debian-sys-maint 账号)并修改 root 账号

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    mysql -u debian-sys-maint -p
    # Enter password:

    # 选择mysql表
    use mysql;

    # 修改密码
    # 8.0 之前,8.0已经没有password函数了,悲
    # update mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';
    # 8.0 的写法(ubuntu22.04默认源已经只有mysql8.0了)
    ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

    flush privileges;
    quit;

    # 测试 root 账号
    mysql -u root -p

    连接/断开

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 远程连接
mysql -h host -u user -p
# > 输入密码

# 本地连接
mysql -u user -p
# > 输入密码


# 在连接的时候直接选定数据库
mysql -h host -u user -p test

# 也可以直接在命令行中输入密码(-p后不打空格直接输密码)
mysql -h host -u user -pxxxxxx test


# 断开链接
quit

简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT VERSION(), CURRENT_DATE;
# +-------------------------+--------------+
# | VERSION()               | CURRENT_DATE |
# +-------------------------+--------------+
# | 8.0.31-0ubuntu0.22.04.1 | 2022-11-20   |
# +-------------------------+--------------+
# 1 row in set (0.00 sec)

# select 可以运行简单的表达式
select SIN(PI()/4), (4+1)*5;
# +--------------------+---------+
# | SIN(PI()/4)        | (4+1)*5 |
# +--------------------+---------+
# | 0.7071067811865475 |      25 |
# +--------------------+---------+
# 1 row in set (0.00 sec)

命令提示符含义:

Prompt Meaning
mysql> Ready for new query
-> Waiting for next line of multiple-line query
\’> Waiting for next line, waiting for completion of a string that began with a single quote (\’)
\”> Waiting for next line, waiting for completion of a string that began with a double quote (\”)
`> Waiting for next line, waiting for completion of an identifier that began with a backtick (`)
/*> Waiting for next line, waiting for completion of a comment that began with /*

操作数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
# 显示所有可访问的数据库
SHOW DATABASES;

# 创建一个新的数据库
# CREATE DATABASE test;
# 使用其中的一个
USE test;

# 查看当前数据库
SELECT DATABASE();

# 创建数据库
CREATE DATABASE xxxx;
# 创建数据表
CREATE TABLE IF NOT EXISTS test(
    id INT UNSIGNED AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(40) NOT NULL,
    submission_data DATE,
    PRIMARY KEY (id)
);


# 删除数据库
DROP DATABASE xxxx;
# 删除数据表
DROP TABLE xxxx;

# 插入数据
INSERT INTO table_name
(field1, field2, field3...)
VALUES
(value1, value2, value3...);

# 查询数据
SELECT field1, field2
FROM table_name,
[WHERE xxx]
[LIMIT N]
[OFFSET N];

# 更新数据
UPDATE table_name SET field1=xxx, field2=xxx
WHERE xxx;

# 删除数据
DELETE FROM table_name
WHERE xxx

# 联合查询
# 默认是 DISTINCT ,即删除重复数据
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

# 修改已经生成的表中的字段
# 增加删除字段
ALTER TABLE table_name DROP field1;
ALTER TABLE table_name ADD field1 INT FIRST;
ALTER TABLE table_name ADD field2 INT AFTER field1;
# 修改字段属性
ALTER TABLE table_name MODIFY field CHAR(10);
ALTER TABLE table_name CHANGE field_old field_new BIGINT;
ALTER TABLE table_name MODIFY field BIGINT NOT NULL DEFAULT 100;
ALTER TABLE table_name ALTER field SET DEFAULT 1000;
ALTER TABLE table_name ALTER field DROP DEFAULT;
# 修改表名
ALTER TABLE table_name RENAME TO table_new_name;

MySQL 事务
ALTER
MySQL 函数
MySQL 数据类型 | 菜鸟教程

操作数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 查看当前数据库所有表格
SHOW TABLES；

# 创建表格
CREATE TABLE pet(name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

# 查看表格所有属性
DESCRIBE pet;
# 等价于
# show columns from pet;
# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | name    | varchar(20) | YES  |     | NULL    |       |
# | owner   | varchar(20) | YES  |     | NULL    |       |
# | species | varchar(20) | YES  |     | NULL    |       |
# | sex     | char(1)     | YES  |     | NULL    |       |
# | virth   | date        | YES  |     | NULL    |       |
# | death   | date        | YES  |     | NULL    |       |
# +---------+-------------+------+-----+---------+-------+
# 6 rows in set (0.00 sec)

加载数据

初始化阶段可以用 txt 文本批量导入数据

由于权限问题导致导入错误的可以参考:load data local

1
2
3
4
5
6
7
8
9
10
11
# # 缺省值用\N表示(数据库中会填充NULL)
# Whistler        Gwen    bird    \N      1997-12-09      \N
# Fluffy          Harold  cat     f       1993-02-04      \N
# Claws           Gwen    cat     m       1994-03-17      \N
# Buffy           Harold  dog     f       1989-05-13      \N
# Fang            Benny   dog     m       1990-08-27      \N
# Bowser          Diane   dog     m       1979-08-31  1995-07-29
# Chirpy          Gwen    bird    f       1998-09-11      \N
# Slim            Benny   snake   m       1996-04-29      \N

load data local infile 'pet.txt' into table pet;
1
2
# 导入单条数据
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

从数据库中获取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
# 获取所有数据
SELECT * FROM pet;

# 更新某一条数据
UPDATE pet SET birth='1980-09-09' WHERE name='petpet';

# 根据限定条件搜索数据
# 字符串匹配
SELECT * FROM pet WHERE name='Puffball';
SELECT * FROM pet WHERE name<>'Puffball';
# 日期比较
SELECT * FROM pet WHERE birth>'1990-01-01';
# 逻辑运算
SELECT * FROM pet WHERE name='Puffball' AND birth>='1990-01-01';
SELECT * FROM pet WHERE name='Puffball' OR birth<='1990-01-01';
SELECT * FROM pet WHERE (name='Puffball' AND birth>='1990-01-01') OR (name='petpet' OR birth<='1990-01-01');
SELECT * FROM pet WHERE death IS NOT NULL;

# 仅选定指定词条
SELECT name FROM pet;
SELECT name,owner FROM pet;
SELECT species,sex FROM pet;
# +---------+------+
# | species | sex  |
# +---------+------+
# | hamster | f    |
# | dog     | f    |
# | bird    | m    |
# | hamster | f    |
# +---------+------+
# 限定不重复词条
SELECT DISTINCT species,sex FROM pet;
# +---------+------+
# | species | sex  |
# +---------+------+
# | hamster | f    |
# | dog     | f    |
# | bird    | m    |
# +---------+------+
# 3 rows in set (0.01 sec)
SELECT DISTINCT species,DISTINCT sex FROM pet;

# 结果排序
# 排序中的比较和 WHERE 语句中的比较一样,是大小写不敏感的
# 可以强制使用 BINARY 关键字使其大小写敏感
# 使用 DESC (descending)关键字降序排列(默认是升序排列)
SELECT name,birth FROM pet;
# +----------+------------+
# | name     | birth      |
# +----------+------------+
# | Puffball | 1999-03-30 |
# | petpet   | 1980-09-30 |
# | Milk     | 1949-03-30 |
# | Pure     | 1999-03-30 |
# +----------+------------+
# 4 rows in set (0.00 sec)
SELECT name,birth FROM pet ORDER BY birth;
# +----------+------------+
# | name     | birth      |
# +----------+------------+
# | Milk     | 1949-03-30 |
# | petpet   | 1980-09-30 |
# | Puffball | 1999-03-30 |
# | Pure     | 1999-03-30 |
# +----------+------------+
# 4 rows in set (0.00 sec)
SELECT name,birth FROM pet ORDER BY BINARY name;
# +----------+------------+
# | name     | birth      |
# +----------+------------+
# | Milk     | 1949-03-30 |
# | Puffball | 1999-03-30 |
# | Pure     | 1999-03-30 |
# | petpet   | 1980-09-30 |
# +----------+------------+
# 4 rows in set, 1 warning (0.00 sec)
# 降序排列
SELECT name,birth FROM pet ORDER BY birth DESC;
# +----------+------------+
# | name     | birth      |
# +----------+------------+
# | Puffball | 1999-03-30 |
# | Pure     | 1999-03-30 |
# | petpet   | 1980-09-30 |
# | Milk     | 1949-03-30 |
# +----------+------------+
# 4 rows in set (0.00 sec)
# 多重排序
SELECT name,sex,birth FROM pet ORDER BY sex,birth;
# +----------+------+------------+
# | name     | sex  | birth      |
# +----------+------+------------+
# | petpet   | f    | 1980-09-30 |
# | Puffball | f    | 1999-03-30 |
# | Pure     | f    | 1999-03-30 |
# | Milk     | m    | 1949-03-30 |
# +----------+------+------------+
# 4 rows in set (0.00 sec)


# 计算属性,使用 CURDATE() 获取当前日期,TIMESTAMPDIFF 计算日期差
# TIMESTAMPDIFF 首个参数是单位,同时可以使用 AS 关键字指定别名
SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
FROM pet;
# +----------+------------+------------+------+
# | name     | birth      | CURDATE()  | age  |
# +----------+------------+------------+------+
# | Puffball | 1999-03-30 | 2022-11-20 |   23 |
# | petpet   | 1980-09-30 | 2022-11-20 |   42 |
# | Milk     | 1949-03-30 | 2022-11-20 |   73 |
# | Pure     | 1999-03-30 | 2022-11-20 |   23 |
# +----------+------------+------------+------+
# 4 rows in set (0.00 sec)
# 注意其中 NULL 是无法用比较运算符的(包括<>),所以只能用IS NOT
SELECT name, birth, death,
TIMESTAMPDIFF(YEAR, birth, death) AS age
FROM pet
WHERE (birth IS NOT NULL) AND (death IS NOT NULL);
# +------+------------+------------+------+
# | name | birth      | death      | age  |
# +------+------------+------------+------+
# | Milk | 1949-03-30 | 2009-04-04 |   60 |
# +------+------------+------------+------+
# 1 row in set (0.00 sec)


# 计算日期的年月日
SELECT name, birth, YEAR(birth), MONTH(birth), DAYOFMONTH(birth) FROM pet;
# +----------+------------+-------------+--------------+-------------------+
# | name     | birth      | YEAR(birth) | MONTH(birth) | DAYOFMONTH(birth) |
# +----------+------------+-------------+--------------+-------------------+
# | Puffball | 1999-03-30 |        1999 |            3 |                30 |
# | petpet   | 1980-09-30 |        1980 |            9 |                30 |
# | Milk     | 1949-03-30 |        1949 |            3 |                30 |
# | Pure     | 1999-03-30 |        1999 |            3 |                30 |
# +----------+------------+-------------+--------------+-------------------+
# 4 rows in set (0.00 sec)

# 计算出的年月日也可以参加条件运算
SELECT name, birth, MONTH(birth)
FROM pet
WHERE MONTH(birth)=9;
# +--------+------------+--------------+
# | name   | birth      | MONTH(birth) |
# +--------+------------+--------------+
# | petpet | 1980-09-30 |            9 |
# +--------+------------+--------------+
# 1 row in set (0.00 sec)

# 查找下一个月生日的词条
SELECT name, birth
FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
# 另一种写法
# +1 是防止12月直接mod成0
SELECT name, birth
FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()),12)+1;
# +--------+------------+
# | name   | birth      |
# +--------+------------+
# | ddddog | 2009-12-12 |
# +--------+------------+
# 1 row in set (0.00 sec)

# 通配符
# 通配符表达式默认是大小写不敏感的
# 使用 _ 匹配单个字符,使用 % 匹配任意个字符(包括0个)
# 使用 LIKE 和 NOT LIKE 连接
SELECT * FROM pet WHERE name LIKE 'p%';
# +----------+------------+---------+------+------------+-------+
# | name     | owner      | species | sex  | birth      | death |
# +----------+------------+---------+------+------------+-------+
# | Puffball | Diane      | hamster | f    | 1999-03-30 | NULL  |
# | petpet   | ownerowner | dog     | f    | 1980-09-30 | NULL  |
# | Pure     | Ddddiane   | hamster | f    | 1999-03-30 | NULL  |
# +----------+------------+---------+------+------------+-------+
# 3 rows in set (0.00 sec)
SELECT * FROM pet WHERE name LIKE '%u__';
# +------+----------+---------+------+------------+-------+
# | name | owner    | species | sex  | birth      | death |
# +------+----------+---------+------+------------+-------+
# | Pure | Ddddiane | hamster | f    | 1999-03-30 | NULL  |
# +------+----------+---------+------+------------+-------+
# 1 row in set (0.00 sec)

# 正则表达式通配符
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^pet');
# +--------+------------+---------+------+------------+-------+
# | name   | owner      | species | sex  | birth      | death |
# +--------+------------+---------+------+------------+-------+
# | petpet | ownerowner | dog     | f    | 1980-09-30 | NULL  |
# +--------+------------+---------+------+------------+-------+
# 1 row in set (0.01 sec)
# 强制使用大小写敏感匹配
# 不知道啥方法-好像不好使-使用c控制符
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^p' COLLATE utf8mb4_0900_as_cs);
# SELECT * FROM pet WHERE REGEXP_LIKE(name, (BINARY '^p'));
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^p', 'c');

# 行统计
# 统计所有行
SELECT COUNT(*) FROM pet;
# +----------+
# | COUNT(*) |
# +----------+
# |        5 |
# +----------+
# 1 row in set (0.00 sec)
# 分组统计
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
# +------+----------+
# | sex  | COUNT(*) |
# +------+----------+
# | f    |        4 |
# | m    |        1 |
# +------+----------+
# 2 rows in set (0.00 sec)
SELECT sex, COUNT(sex) FROM pet GROUP BY sex;

# 创建新表 event
create table event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
insert into event value('Milk', '1966-03-04', 'litter','no');
insert into event value('Pure', '2008-05-04', 'litter','nonono');

# 同时操作两个表
# 将两个表正交,并选择 pet.name=event.name 的词条
# 其中 ON 起到了 WHERE 的作用
# 同样可以在后面追加 WHERE 语句
SELECT pet.name,
TIMESTAMPDIFF(YEAR, birth, date) as age,
remark
FROM pet INNER JOIN event
ON pet.name=event.name
AND event.type='litter';
# WHERE event.type='litter';
# +------+------+--------+
# | name | age  | remark |
# +------+------+--------+
# | Milk |   16 | no     |
# | Pure |    9 | nonono |
# +------+------+--------+
# 2 rows in set (0.00 sec)
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = 'f' AND p1.death IS NULL
AND p2.sex = 'f' AND p2.death IS NULL;
# +----------+------+----------+------+---------+
# | name     | sex  | name     | sex  | species |
# +----------+------+----------+------+---------+
# | Pure     | f    | Puffball | f    | hamster |
# | Puffball | f    | Puffball | f    | hamster |
# | petpet   | f    | petpet   | f    | dog     |
# | Pure     | f    | Pure     | f    | hamster |
# | Puffball | f    | Pure     | f    | hamster |
# | ddddog   | f    | ddddog   | f    | birrrrd |
# +----------+------+----------+------+---------+
# 6 rows in set (0.00 sec)

查询数据库信息

1
2
3
4
5
6
7
8
# 查看当前数据库名称
SELECT DATABASE();

# 查看当前数据库所有表格
SHOW TABLES;

# 查看表格信息
DESCRIBE pet;

批处理文件

1
2
mysql -h host -u user -p < batch-file
# 输入密码

索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 创建表格时直接指定
CREATE TABLE table_name(
    id INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    # 以 CHAR VARCHAR 为索引列,则需要指定长度
    INDEX index_name (username(length)),
    INDEX index_name (id),
    UNIQUE index_name (id),
    UNIQUE INDEX index_name (id)
    PRIMARY KEY (id)
);


# 创建索引
CREATE INDEX index_name ON table_name (field);
CREATE UNIQUE INDEX index_name ON table_name (field);

# 删除索引
DROP INDEX index_name ON table_name;

# 使用 ALTER 修改索引
ALTER TABLE table_name ADD PRIMARY KEY (field);
ALTER TABLE table_name ADD INDEX index_name (field);
ALTER TABLE table_name ADD UNITUE index_name (field);
ALTER TABLE table_name ADD UNITUE INDEX index_name (field);
ALTER TABLE table_name ADD FULLTEXT index_name (field);
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;


# 查看索引信息
SHOW INDEX FROM table_name;

关系模型

主键
外键
索引

CPP 使用 MySQL

CPP 可以通过两种风格(两个库)访问 MySQL 数据库

1
2
3
4
5
6
# C 风格数据库连接
# 提供头文件 <mysql/mysql.h>
sudo apt install libmysqlclient-dev

# C++ 风格数据库连接
sudo apt install libmysqlcppconn-dev

C 风格

参考链接: MySQL C API programming

编译命令:

1
g++ xxx.cpp -o xxx -lmysqlclient
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
#include <mysql/mysql.h>
#include <vector>
#include <string>
#include <iostream>

using std::cout;
using std::endl;
using std::string;
using std::vector;

int create_database();
int create_table();
int fetch_data();
int multi_state();

int main()
{
    multi_state();
}

int create_database()
{

    // 初始化连接(分配空间)
    MYSQL *con = mysql_init(NULL);
    if (con == nullptr)
    {
        cout << "MySQL connection init failed." << endl;
        return -1;
    }

    // mysql_get_client_info 获取版本信息
    cout << "MySQL client version:" << mysql_get_client_info() << endl;

    // 与服务器连接
    if (mysql_real_connect(con, "localhost", "root", "explosion",
                           "test", 3306, NULL, 0) == nullptr)
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    // 执行查询命令
    if (mysql_query(con, "CREATE DATABASE testdb;"))
    {
        cout << "Create Database failed" << endl;
        mysql_close(con);
        return -1;
    }

    mysql_close(con);
    return 0;
}

int create_table()
{

    // 初始化连接(分配空间)
    MYSQL *con = mysql_init(NULL);
    if (con == nullptr)
    {
        cout << "MySQL connection init failed." << endl;
        return -1;
    }

    // 与服务器连接
    if (mysql_real_connect(con, "localhost", "root", "explosion",
                           "test", 3306, NULL, 0) == nullptr)
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    // 删除已存在的表
    if (mysql_query(con, "DROP TABLE IF EXISTS cars"))
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    // 创建表格
    if (mysql_query(con, "CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), price INT)"))
    {
        cout << "MySQL create table error" << endl;
        mysql_close(con);
        return -1;
    }

    vector<string> queries = {
        "INSERT INTO cars VALUES(1,'Audi',52642)",
        "INSERT INTO cars VALUES(2,'Mercedes',57127)",
        "INSERT INTO cars VALUES(3,'Skoda',9000)",
        "INSERT INTO cars VALUES(4,'Volvo',29000)",
        "INSERT INTO cars VALUES(5,'Bentley',350000)",
        "INSERT INTO cars VALUES(6,'Citroen',21000)",
        "INSERT INTO cars VALUES(7,'Hummer',41400)"};

    for (string s : queries)
    {
        if (mysql_query(con, s.c_str()))
        {
            cout << "MySQL insert error with: " << s << endl;
            mysql_close(con);
            return -1;
        }
    }

    // 获取最后一个插入数据的ID
    // 当且仅当定义了 AUTO_INCREMENT 属性
    int id = mysql_insert_id(con);
    cout << "The last inserted row id: " << id << endl;

    mysql_close(con);
    return 0;
}

int fetch_data()
{
    // 初始化连接(分配空间)
    MYSQL *con = mysql_init(NULL);
    if (con == nullptr)
    {
        cout << "MySQL connection init failed." << endl;
        return -1;
    }

    // 与服务器连接
    if (mysql_real_connect(con, "localhost", "root", "explosion",
                           "test", 3306, NULL, 0) == nullptr)
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    // 获取数据
    if (string s = "SELECT * FROM cars"; mysql_query(con, s.c_str()))
    {
        cout << "MySQL insert error with: " << s << endl;
        mysql_close(con);
        return -1;
    }

    MYSQL_RES *result = mysql_store_result(con);
    if (result == nullptr)
    {
        cout << "MySQL get result error" << endl;
        mysql_close(con);
        return -1;
    }

    int num_field = mysql_num_fields(result);

    MYSQL_ROW row;
    MYSQL_FIELD *field;

    // 输出表头
    while (field = mysql_fetch_field(result))
    {
        cout << field->name << ", ";
    }
    cout << endl;

    // 输出数据
    while (row = mysql_fetch_row(result))
    {
        for (int i = 0; i < num_field; i++)
        {
            cout << (row[i] ? row[i] : "NULL") << ", ";
        }

        cout << endl;
    }

    mysql_free_result(result);
    mysql_close(con);
    return 0;
}

int multi_state()
{
    // 初始化连接(分配空间)
    MYSQL *con = mysql_init(NULL);
    if (con == nullptr)
    {
        cout << "MySQL connection init failed." << endl;
        return -1;
    }

    // 与服务器连接,开启多行执行
    if (mysql_real_connect(con, "localhost", "root", "explosion",
                           "test", 3306, NULL, CLIENT_MULTI_STATEMENTS) == nullptr)
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    // 同时执行三条查询命令
    if (mysql_query(con, "SELECT name FROM cars WHERE id=2;\
                          SELECT name FROM cars WHERE id=3;\
                          SELECT name FROM cars WHERE id=6"))
    {
        cout << "MySQL connection error" << endl;
        mysql_close(con);
        return -1;
    }

    int status = 0;
    do
    {
        MYSQL_RES *result = mysql_store_result(con);

        if (result == NULL)
        {
            cout << "MySQL fetch data error" << endl;
            mysql_close(con);
            return -1;
        }

        MYSQL_ROW row = mysql_fetch_row(result);

        cout << row[0] << endl;

        mysql_free_result(result);
        status = mysql_next_result(con);
        if (status > 0)
        {
            cout << "MySQL fetch status error" << endl;
            mysql_close(con);
            return -1;
        }
    } while (status == 0);

    mysql_close(con);
    return 0;
}

C++风格

参考链接： Connector/C++

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
#include <iostream>

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

using std::cin;
using std::cout;
using std::endl;

int fetch_data();
int prepared_statement();

int main()
{
    prepared_statement();

    return 0;
}

int fetch_data()
{
    try
    {
        // 需要注意的 driver 指针无需释放
        // conn 需要手动释放
        sql::Driver *driver;
        sql::Connection *conn;
        sql::Statement *stmt;
        sql::ResultSet *result;

        // 不用添加命名空间
        driver = get_driver_instance();
        conn = driver->connect("tcp://127.0.0.1:3306", "root", "explosion");

        // 选定 test 数据库
        conn->setSchema("test");

        stmt = conn->createStatement();
        // stmt->execute("USE " EXAMPLE_DB);
        // stmt->execute("DROP TABLE IF EXISTS test");
        // stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
        // stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");
        result = stmt->executeQuery("SELECT * from cars");
        while (result->next())
        {
            cout << "\tid: " << result->getInt("id") << "\tname: " << result->getString("name") << "\tprice: " << result->getInt("price") << endl;
        }

        delete result;
        delete stmt;
        delete conn;
    }
    catch (sql::SQLException &e)
    {
        cout << "SQLException: " << e.getErrorCode() << endl;
    }

    cout << endl;

    return 0;
}

int prepared_statement()
{
    try
    {
        // 需要注意的 driver 指针无需释放
        // conn 需要手动释放
        sql::Driver *driver;
        sql::Connection *conn;

        sql::ResultSet *result;
        sql::PreparedStatement *pstmt;

        // 不用添加命名空间
        driver = get_driver_instance();
        conn = driver->connect("tcp://127.0.0.1:3306", "root", "explosion");

        // 选定 test 数据库
        conn->setSchema("test");

        // 准备预编译指令并执行两次
        pstmt = conn->prepareStatement("INSERT INTO cars(name, price) VALUES (?, ?)");
        pstmt->setString(1, "BYD");
        pstmt->setInt(2, 1111);
        pstmt->execute();

        pstmt->setString(1, "BYD Han");
        pstmt->setInt(2, 6666);
        pstmt->execute();

        delete pstmt;

        // 准备预编译查询指令
        pstmt = conn->prepareStatement("SELECT * FROM cars");
        result = pstmt->executeQuery();
        while (result->next())
        {
            cout << "\tid: " << result->getInt("id") << "\tname: " << result->getString("name") << "\tprice: " << result->getInt("price") << endl;
        }

        delete result;
        delete pstmt;
        delete conn;
    }
    catch (sql::SQLException &e)
    {
        cout << "SQLException: " << e.getErrorCode() << endl;
    }

    cout << endl;

    return 0;
}