一、Mysql数据库简介
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统(Relational Database Management System)的特点:
- 数据以表格的形式出现
- 每行为各种记录名称
- 每列为记录名称所对应的数据域
- 许多的行和列组成一张表单
- 若干的表单组成database
二、Mysql的基本操作(SQL语句)
1、创建或删除一个数据库:
CREATE DATABASE 数据库名; drop database 数据库名;mysql> create DATABASE datetest; //创建一个数据库
mysql> drop database datetest; //删除一个数据库
2、选择已创建数据库
mysql> use datetest;
3、在所选择的数据库中创建一个表
注意:MySQL命令终止符为分号 ; -> 是换行符标识 。使用箭头标记 -> 不是 SQL 语句的一部分,它仅仅表示一个新行,如果一条SQL语句太长,我们可以通过回车键来创建一个新行来编写 SQL 语句,SQL 语句的命令结束符为分号 ;。mysql> CREATE TABLE vol_can_test1( ->test_id INT NOT NULL AUTO_INCREMENT, ->datetime DATETIME NOT NULL, ->PRIMARY KEY ( test_id )) ->ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、查看表详情
```sql mysql> use datetest //使用数据库 Database changed mysql> show tables; //查看数据库中的数据表 +——————————+ | Tables_in_datetest | +——————————+ | vol_can_test1 | +——————————+ 1 row in set (0.00 sec) mysql> desc vol_can_test1 ; //查看数据表中的详情
+—————-+—————+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +—————-+—————+———+——-+————-+————————+ | test_id | int(11) | NO | PRI | NULL | auto_increment | | test_time | NO | | NULL | | +—————-+—————+———+——-+————-+————————+ 2 rows in set (0.01 sec)
<a name="AEpgK"></a>
### 5、删除数据库中的一个表
```sql
mysql> use datetest; //选择表所在数据库
Database changed
mysql> DROP TABLE vol_can_test1 //删除表
Query OK, 0 rows affected (0.8 sec)
6、在表中插入数据
INSERT INTO table_name ( field1, field2,...fieldN )VALUES( value1, value2,...valueN );
注意如果数据是字符型,必须使用单引号或者双引号,如:"value"。
MySQL> INSERT INTO vol_can_test1(test_id,test_time)
->VALUES(2,NOW());
Query OK, 1 row affected (0.00 sec)
7、查询表中的数据
mysql> use datetest //选择数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from vol_can_test1; //选择数据表
+---------+---------------------+
| test_id | test_time |
+---------+---------------------+
| 1 | 2020-04-02 14:39:28 |
| 2 | 2020-04-02 14:39:40 |
| 3 | 2020-04-02 14:39:48 |
+---------+---------------------+
3 rows in set (0.00 sec)
8、更新语句
如果我们需要修改或更新 MySQL 中的数据,我们可以使用 SQL UPDATE 命令来操作。
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
可以根据排序,更新同一个表中的一批数据,如更新最新的一条记录(order by … desc limit 1)
UPDATE device_usage_logs SET action_time=now() WHERE `dev_registration_code`='10000002' and `action_type`='2' order by action_time desc limit 1;
9、存在则更新,不存在则插入
INSERT INTO ··· ON DUPLICATE KEY UPDATE···(存在则更新,不存在则插入)(项目中记录设备状态信息)
在MySQL数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的唯一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。另外,ON DUPLICATE KEY UPDATE不能写where条件。
示例:mysql>create table kid_score( id tinyint unsigned not null, birth_day date not null, score int unsigned not null, primary key(id, birth_day) // --唯一索引是由 id + birth_day 两个字段组成 ) engine = InnoDB; mysql>insert into kid_score(id, birth_day, score) values (1,'2019-01-15',10),(2,'2019-01-16',20); //初始化数据 mysql> select * from kid_score; //查看表的初始内容 +----+------------+-------+ | id | birth_day | score | +----+------------+-------+ | 1 | 2019-01-15 | 10 | | 2 | 2019-01-16 | 20 | +----+------------+-------+ 2 rows in set
唯一索引重复,执行更新(id=1,birth_day=2019-01-15)主键重复,则更新
mysql> insert into kid_score(id, birth_day, score) values (1,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50; Query OK, 2 rows affected mysql> select * from kid_score; +----+------------+-------+ | id | birth_day | score | +----+------------+-------+ | 1 | 2019-01-15 | 60 | | 2 | 2019-01-16 | 20 | +----+------------+-------+ 2 rows in set
唯一索引不重复,执行插入(id=2,birth_day=2019-01-15)主键不重复,则插入
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50; Query OK, 1 row affected mysql> select * from kid_score; +----+------------+-------+ | id | birth_day | score | +----+------------+-------+ | 1 | 2019-01-15 | 60 | | 2 | 2019-01-15 | 30 | | 2 | 2019-01-16 | 20 | +----+------------+-------+ 3 rows in set
唯一索引重复,应该执行更新,但更新值与原值相同
mysql> insert into kid_score(id, birth_day, score) values (2,'2019-01-16',20) ON DUPLICATE KEY UPDATE score = 20; Query OK, 0 rows affected mysql> select * from kid_score; +----+------------+-------+ | id | birth_day | score | +----+------------+-------+ | 1 | 2019-01-15 | 60 | | 2 | 2019-01-15 | 30 | | 2 | 2019-01-16 | 20 | +----+------------+-------+ 3 rows in set
SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
- 可以设定多个字段来排序。
- 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
- 可以添加 WHERE…LIKE 子句来设置条件。
值得注意的是这些语句可以嵌套使用,在项目中读取数据库时,第一次排序时读取表voltest1中的date栏中的最后1000个数据,是降序排列;为了使最新的数据按升序排列,第二次排序是在读取的1000个数据中进行。第一次需要读id,因为第二次排序用到id。
mysql>SELECT date FROM (SELECT test_id, date FROM voltest1 order by test_id DESC limit 1000) aa order by test_id
10、按时间查询记录
//正序查询(最旧)
mysql> select * from device_usage_logs where dev_registration_code= 10000001 order by action_time limit 1;
+--------+-----------------------+-------------+---------------------+
| use_id | dev_registration_code | action_type | action_time |
+--------+-----------------------+-------------+---------------------+
| 3 | 10000001 | offlinr | 2020-09-14 09:04:59 |
+--------+-----------------------+-------------+---------------------+
1 row in set (0.00 sec)
//反序查询(最新)
mysql> select * from device_usage_logs where dev_registration_code= 10000001 order by action_time desc limit 1;
+--------+-----------------------+-------------+---------------------+
| use_id | dev_registration_code | action_type | action_time |
+--------+-----------------------+-------------+---------------------+
| 2606 | 10000001 | Online | 2021-01-24 10:28:23 |
+--------+-----------------------+-------------+---------------------+
1 row in set (0.01 sec)
//多条件查询,获取最新的十条记录(注意括号与参数之间的空格,否则认为语法错误)
mysql> select * from device_usage_logs where ( dev_registration_code = 10000001 and action_type = 'Online' ) order by action_time desc limit 0,10;
+--------+-----------------------+-------------+---------------------+
| use_id | dev_registration_code | action_type | action_time |
+--------+-----------------------+-------------+---------------------+
| 2242 | 10000001 | online | 2021-01-06 19:32:25 |
| 2156 | 10000001 | Online | 2021-01-01 16:17:17 |
| 2154 | 10000001 | Online | 2021-01-01 15:26:20 |
| 2140 | 10000001 | Online | 2021-01-01 11:52:45 |
| 2134 | 10000001 | Online | 2020-12-31 09:03:13 |
| 2130 | 10000001 | Online | 2020-12-30 11:17:30 |
| 2128 | 10000001 | Online | 2020-12-30 10:39:03 |
| 2126 | 10000001 | Online | 2020-12-30 09:27:42 |
| 2124 | 10000001 | Online | 2020-12-30 08:22:56 |
| 2122 | 10000001 | Online | 2020-12-29 11:32:04 |
+--------+-----------------------+-------------+---------------------+
11、删除语句
DELETE FROM table_name [WHERE Clause]
- 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
三、 C语言常用API
1、mysql_init
MYSQL* mysql_init(MYSQL* mysql);
补充:如果mysql是NULL指针,该函数将分配、初始化、并返回新对象。否则,将初始化对象,并返回对象的地址。如果mysql_init()分配了新的对象,应当在程序中调用mysql_close() 来关闭连接,以释放对象。
2、mysql_options()
—用于设置额外的连接选项并影响连接的行为,
可多次调用此函数来设置多个选项。
int mysql_options(MYSQL* mysql,enum mysql_option,const void* arg);
补充:在mysql_init()之后与mysql_real_connect()之前调用mysql_options()。
3、mysql_real_connect()
—连接到MySQL server
MYSQL* mysql_real_connect(MYSQL* mysql,const char* host,const char* user,
const char* passwd,const char* db,unsigned int port,
const char* unix_socket,unsigned long client_flag)
说明:在你能够执行需要有效MySQL连接句柄结构的任何其它API函数之前,mysql_real_connect() 必须成功完成。
参数:
- “host”——必须是主机名或IP地址。如果host是NULL或字符串“localhost”,连接将被视为与本地主机连接。
- “unix_socket”——通常指定为NULL
- “client_flag”——通常为0,但是,也能将其设置为具有特定功能的标志组合。
4、mysql_query()
——执行以null结尾的字符串指定的sql查询语句
补充:mysql_query() 不能用于执行包含二进制数据的sql查询语句,此时,你必须使用mysql_real_query()。int mysql_query(MYSQL* mysql,const char* stmt_str)
如果执行成功将返回0,如果执行错误,将返回非0值。5、mysql_store_result()
说明:在调用mysql_query()或者mysql_real_query() 之后,你必须调用mysql_store_result()或者mysql_use_result() 对于那些成功检索了数据的每个查询(select,show,describe,explain,check table等)。MYSQL_RES* mysql_store_result(MYSQL* mysql)
mysql_store_result()将查询的全部结果读取到客户端,分配1个MYSQL_RES结构,并将结果置于该结构中。
调用mysql_num_rows()可以找出结果集中的行数。可以调用mysql_fetch_row()来获取结果集中的行,或调用mysql_row_seek()和mysql_row_tell()来获取或设置结果集中的当前行位置。
在对结果集处理完成之后,也必须调用mysql_free_result()函数释放内存,否则可能造成内存泄露。6、mysql_use_result()
说明:mysql_use_result() 不像mysql_store_result() 那样将结果集实际读取到客户端。它必须通过对mysql_fetch_row() 的调用,对每一行分别进行检索。这将直接从服务器读取结果,而不会将其保存到临时表或本地缓冲区内,与mysql_store_result() 相比,速度更快而且使用的内存更少。使用mysql_use_result() 时,必须执行mysql_fetch_row() ,直至返回NULL值。MYSQL_RES* mysql_use_result(MYSQL* mysql)
在对结果集处理完成之后,也必须调用mysql_free_result()函数释放内存,否则可能造成内存泄露。7、mysql_fetch_row()——从结果集中获取下一行
返回值:下一行的MYSQL_ROW结构。当 mysql_fetch_row( ) 执行后返回一个二级指针,也可以理解为指针字符串数组,此函数执行完后会返回下一个字符串数组的地址,如果下一个字符串数组地址不存在,则返回NULL。(如果没有更多的行可检索或者发生错误时,则返回NULL)MYSQL_ROW mysql_fetch_row(MYSQL_RES* result)
8、mysql_fetch_fields()——从结果集中获取下一列
对于结果集,返回所有MYSQL_FIELD 结构的数组。每个结构提供了结果集中 1 列的字段定义MYSQL_FIELD* mysql_fetch_fields(MYSQL_RES* result)
返回值:关于结果集所有列的 MYSQL_FIELD 结构的数组。9、mysql_free_result()
说明:当你完成了对结果集的处理之后,你必须释放其使用的内存(通过调用mysql_free_result() 函数)。也不要尝试在释放内存之后,去获取结果集。void mysql_free_result(MYSQL_RES* result)
10、mysql_error()——返回最近调用MySQL函数发生的错误信息
返回值:描述错误信息的以null结尾的字符串,如果未发生错误,则返回空字符串。const char* mysql_error(MYSQL* mysql)
11、mysql_close()——关闭一个server连接
说明:关闭前面已经打开的连接。如果句柄是由mysql_init() 或mysql_connect() 自定分配的,mysql_close() 还将解除分配由mysql指向的连接句柄。 必须释放,否则会造成内存泄露void mysql_close(MYSQL* mysql)
四、 C语言访问与操作Mysql数据库
当 mysql_fetch_row( ) 执行后返回一个二级指针,也可以理解为指针字符串数组,此函数执行完后会返回下一个字符串数组的地址,如果下一个字符串数组地址不存在,则返回NULL。
示例:#include <stdio.h> #include <stdlib.h> #include <mariadb/mysql.h> #include <string.h> int main(int argc, char** argv) { MYSQL conn; MYSQL_RES res; MYSQL_ROW row; // typedef char** MYSQL_ROW char tableName[30]; char query[1024]; unsigned short numFields; char column[30][40]; //1.初始化 mysql_init(&conn); //2.连接数据库 if(!mysql_real_connect(&conn, "localhost", "root", "123456", "library", 0, NULL, 0)) { fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&conn)); return -1; } //3.选择 table strcpy(tableName, "adminInfo"); sprintf(query, "select * from %s", tableName); mysql_query(&conn, query); //4.取出 table 选择的内容 res = *mysql_store_result(&conn); printf("%s\n", &res); //5.统计 table 字段 numFields = mysql_num_fields(&res); printf("%d\n", numFields); //6.保存字段 for(int i = 0; i < numFields; ++i) { strcpy(column[i], mysql_fetch_field(&res)->name); // printf("%s\n", column[i]); } //7.打印每一行的内容 while( (row = mysql_fetch_row(&res)) != NULL ) { for(int j=0; j<numFields; j++) { printf("%s = %s\t\n", column[j], row[j]); } } mysql_free_result(&res); //释放结果集 mysql_close(&conn); //关闭mysql return 0; }