MySQL的基本使用

MySQL基本使用

使用前需安装库函数与引用头文件

库函数安装命令：

1
sudo apt install libmysqlclient-dev

头文件引用：

1
#include <mysql/mysql.h>

1. 连接

初始化一个连接

1
MYSQL * mysql = mysql_init(NULL);

写类型指令：

mysql_real_connect函数的原型：

1
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);
1
MYSQL *ret = mysql_real_connect(mysql,"localhost","root","123","table",3306,NULL,0);

参数说明：

  • mysql：指向MYSQL结构的指针，用于保存连接相关的信息。
  • host：要连接的MySQL服务器的主机名或IP地址。
  • user：用于连接到MySQL服务器的用户名。
  • passwd：连接MySQL服务器所需的密码。
  • db：要连接的数据库名称。
  • port：MySQL服务器的端口号。0为默认情况下，MySQL使用3306端口。
  • unix_socket：Unix域套接字的路径。如果使用TCP/IP连接，则为NULL。
  • client_flag：客户端标志，用于设置连接的特定选项，例如加密、压缩等。

检查报错

1
2
3
if(ret =NULL){
		fprintf(stderr,"mysql connect error:%s\n",mysql_error(mysql));
}

指令写入

1
int qret = mysql_query(mysql,sql);
1
2
3
if(ret =NULL){
		fprintf(stderr,"SQL statement error:%s\n",mysql_error(mysql));
}

读类型指令

基本步骤：

  1. 执行查询语句：

    使用mysql_query函数执行查询语句，并将结果存储在MYSQL_RES类型的变量中。

    1
    2
    3
    MYSQL_RES *res;
    mysql_query(mysql, "SELECT * FROM table_name");
    res = mysql_store_result(mysql);

    上述示例中，connection是已经建立好的MySQL连接对象，”SELECT * FROM table_name”是查询语句，mysql_query用于执行查询，mysql_store_result用于将结果存储在result变量中。

  2. 处理结果集：

    使用mysql_num_fields函数获取结果集中的字段数目，使用mysql_fetch_row函数逐行获取结果集的数据。

    1
    2
    3
    4
    5
    6
    7
    8
    int num_fields = mysql_num_fields(res);
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(res)) != NULL) {
        for (int i = 0; i < num_fields; i++) {
            printf("%s\t", row[i]);
        }
        printf("\n");
    }

    上述示例中，mysql_num_fields用于获取结果集的字段数目，mysql_fetch_row用于逐行获取结果集中的数据。通过循环遍历每行数据，并在内部循环中逐个字段打印出来。

  3. 释放结果集：

    使用mysql_free_result函数释放结果集的内存空间。

    1
    mysql_free_result(res);

    当您完成对结果集的使用后，应该使用mysql_free_result函数释放结果集所占用的内存空间。

请注意，在使用MYSQL_RES时，需要先执行查询语句并获得结果集，然后通过逐行遍历和处理结果集中的数据。最后，务必释放结果集的内存空间以避免内存泄漏。

2. 增

创建一个新的数据库

1
CREATE DATABASE 数据库名;

使用某个数据库，可以使用use语句

1
use mysql

创建一张表

1
CREATE TABLE table_name (column_name column_type);
1
2
3
4
5
6
7
8
9
10
11
 CREATE TABLE table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  filename VARCHAR(256),
  username VARCHAR(256),
  pre_id INT,
  filetype CHAR(1),
  filepath VARCHAR(512),
  isdelet INT,
  md5 VARCHAR(1024),
  UNIQUE KEY (username(256), filepath(512))
);

增加数据

1
INSERT INTO table_name VALUES (value1, value2,...valueN);
1
2
3
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

增加列

1
2
ALTER TABLE 表名
ADD 列名 数据类型 [列约束];

3. 删

删除一个数据库

1
drop database <数据库名>;

删除一个数据表

1
DROP TABLE table_name ;

数据表中删除数据

1
DELETE FROM table_name [WHERE Clause];

删除列

1
2
ALTER TABLE 表名
DROP COLUMN 列名;

4. 改

修改数据表数据

1
2
3
UPDATE 表名
SET 列名1 = 新值1, 列名2 = 新值2, ...
WHERE 条件;

修改列

1
2
ALTER TABLE 表名
MODIFY 列名 新数据类型 [新列约束];

修改表

1
2
ALTER TABLE 旧表名
RENAME TO 新表名;

5. 查

筛选！！！

不等于：<>

时间可以比大小

逻辑运算：与and 或or and优先级高于or

通配符：模糊匹配like中 %匹配任意字符串 _匹配单一字符

空值：is null is not null

聚集函数：在select后的field1前面加函数如：select max(field1) select min(field1) select avg(field1)

分组聚集：结果将按照分组条件分组 group by 子句（分组聚集)如SELECT customer_id FROM orders GROUP BY sex

展示数据表格式

1
2
desc table_name;
describle table_name;

查找语句SELECT语句用于从数据库表中检索数据，并将其作为结果集返回。以下是SELECT语句的一般语法：

1
2
3
4
5
6
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 列名 [ASC|DESC];

具体步骤如下：

  1. 使用SELECT关键字指定要检索的列名。使用通配符*选择所有列。
  2. 使用FROM关键字指定要从中检索数据的表名。对于多个表，可以使用JOIN操作符进行表连接。
  3. 可选：使用WHERE关键字指定筛选条件，限制要返回的行。如果省略WHERE子句，将返回表中的所有行。
  4. 可选：使用GROUP BY关键字按照指定的列对结果进行分组。GROUP BY通常与聚合函数（如SUM、COUNT、AVG等）一起使用。
  5. 可选：使用HAVING关键字指定分组后的筛选条件，HAVING子句用于对分组后的结果集进行筛选。
  6. 可选：使用ORDER BY关键字按照指定的列对结果进行排序，默认升序。可以附加ASC关键字表示升序（默认），或DESC关键字表示降序。

排序：order by默认升序。后加ASC或DESC，表示升序或降序

1
2
3
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
ORDER BY condition1,condition2 DESC;

内连接：将两个表的行连接查询

1
2
3
SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列;

6. 常见类型

  1. 整数类型（Integer Types）：
    • TINYINT：1字节，范围为-128到127（有符号）或0到255（无符号）
    • SMALLINT：2字节，范围为-32768到32767（有符号）或0到65535（无符号）
    • MEDIUMINT：3字节，范围为-8388608到8388607（有符号）或0到16777215（无符号）
    • INT或INTEGER：4字节，范围为-2147483648到2147483647（有符号）或0到4294967295（无符号）
    • BIGINT：8字节，范围为-9223372036854775808到9223372036854775807（有符号）或0到18446744073709551615（无符号）
  2. 浮点数类型（Floating-Point Types）：
    • FLOAT(M, D)：4字节，单精度浮点数
    • DOUBLE(M, D)：8字节，双精度浮点数
  3. 定点数类型（Fixed-Point Types）：
    • DECIMAL(M, D)：存储精确小数，M表示总位数，D表示小数点后的位数
  4. 字符串类型（String Types）：
    • CHAR(M)：固定长度字符串，最多255个字符
    • VARCHAR(M)：可变长度字符串，最多65535个字符
    • TEXT：可变长度的大文本对象，最多65535个字符
  5. 日期和时间类型（Date and Time Types）：
    • DATE：日期，格式为’YYYY-MM-DD’
    • TIME：时间，格式为’HH:MM:SS’
    • DATETIME：日期和时间，格式为’YYYY-MM-DD HH:MM:SS’
    • TIMESTAMP：日期和时间，自1970年1月1日以来的秒数表示
    • YEAR：年份，范围为1901到2155
  6. 布尔类型（Boolean Type）：
    • BOOL或BOOLEAN：存储true或false

7. 常见约束

  1. 主键约束（Primary Key Constraint）：

    • 主键用于唯一标识表中的每一行数据，并确保数据的唯一性。

    • 示例：

      1
      2
      3
      4
      5
      CREATE TABLE customers (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        email VARCHAR(100)
      );

  2. 非空约束（Not Null Constraint）：

    • 非空约束确保列中的值不能为空。

    • 示例：

      1
      2
      3
      4
      5
      CREATE TABLE employees (
        id INT,
        name VARCHAR(50) NOT NULL,
        age INT NOT NULL
      );

  3. 唯一约束（Unique Constraint）：

    • 唯一约束确保列中的值在表中是唯一的，允许空值。

    • 示例：

      1
      2
      3
      4
      5
      CREATE TABLE products (
        id INT,
        name VARCHAR(50),
        code VARCHAR(10) UNIQUE
      );

      创建多列唯一约束：

      1
      2
      3
      4
      5
      6
      7
      CREATE TABLE 表名 (
          列1 数据类型,
          列2 数据类型,
          列3 数据类型,
          CONSTRAINT 约束名 UNIQUE (列1, 列2, 列3)
      );

      1
      2
      ALTER TABLE 表名
      ADD CONSTRAINT 约束名 UNIQUE (列1, 列2, ...);

  4. 外键约束（Foreign Key Constraint）：

    • 外键约束用于建立表之间的关联，确保引用表中的数据存在于被引用表中的列中。

    • 示例：

      1
      2
      3
      4
      5
      6
      CREATE TABLE orders (
        id INT PRIMARY KEY,
        customer_id INT,
        total_amount DECIMAL(10, 2),
        FOREIGN KEY (customer_id) REFERENCES customers(id)
      );

  5. 检查约束（Check Constraint）：

    • 检查约束用于定义列中的值必须满足的条件。

    • 示例：

      1
      2
      3
      4
      5
      6
      7
      CREATE TABLE products (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        quantity INT,
        price DECIMAL(10, 2),
        CHECK (quantity >= 0 AND price >= 0)
      );

  6. 默认约束（Default Constraint）：

    • 默认约束用于为列指定默认值。

    • 示例：

      1
      2
      3
      4
      5
      6
      CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        employment_date DATE DEFAULT CURRENT_DATE()
      );

8. 其他

在数据库命令行中输入以下内容，显示数据库的版本

1
select version(), current_date;

show语句可以展示当前服务端已经存在的数据库

1
show databases;

show语句展示当前选择的数据库当中的所有表

1
show tables;

MySQL数据库事务和索引

一、事务

1. 概念

构成单一逻辑工作单元的操作集合，即：一组命令的集合。

2. 事务的性质（ACID）

  • 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。无论是操作系统崩溃，还是计算机停止运行，这项要求都要成立。即：事务中的操作为一个整体，要么都做，要么都不做。
  • 一致性(Consistency)：事务作为一个原子性操作，它从一个一致性的数据库状态开始运行，事务结束时，数据库的状态必须再次是一致的。即：事务的执行的前后，数据的完整性保持一致。且事务执行前后数据的总和不变。
  • 隔离性(Isolation)：尽管多个事务可能并发执行，但系统保证，对于任何一对事务Ti和Tj ,在Ti看来， Tj要么在Ti开始之前已经完成，要么在Ti完成之后才开始执行。因此，每个事务都感觉不到系统中有其他事务在并发地执行。即：一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事物之间不能互相干扰。隔离并不是100%隔离的，可能隔离一部分。
  • 持久性(Durability)：一个事务成功完成后，它对数据库的改变必须是永久的，即使出现系统故障。即：指一个事物一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3. 事务的基本操作

1
2
3
4
5
6
7
8
9
10
11
12
#开启一个事务，标记事务的起点
begin/start transaction;

#提交事务,表示事务成功被执行。   也可以发生故障异常结束事务
commit;

#回滚 (默认回滚到事务开始的状态)
rollback;
rollback to sp2;#可以回滚到指定的回滚点

#回滚点 （理解为快照）
savepoint;

查看表的中的字段

1
show create table member;
1
2
3
4
5
6
7
8
9
10
CREATE TABLE `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `math` float DEFAULT NULL,
  `e_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique_idx` (`name`),
  KEY `name_math_idx` (`name`,`math`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
1
desc member;
1
2
3
4
5
6
7
8
9
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  | UNI | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| math   | float       | YES  |     | NULL    |                |
| e_date | datetime    | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

二、并发产生的四个问题

1. 脏写

概念：多个事务并发写同一数据时，先执行的事务所写的数据会被后写的数据覆盖

2. 脏读

概念：如果一个事务T1向数据库写数据，但该事务还没提交或终止，另一个事务T2就看到了事务T1写入数据库的数据。

3. 不可重复读

概念：一个事务有对同一个数据项的多次读取，但是在某前后两次读取之间，另一个事务更新该数据项，并且提交了。在后一次读取时，感知到了提交的更新

4. 幻读

概念：一个事务需要进行前后两次统计，在这两次统计期间，另一个事务插入了新的符合统计条件的记录，并且提交了。导致前后两次统计的数据不一致

总结：脏读，就是读了一个没有提交的数据；不可重复读，前后两次读数据的时候，数据的内容发生了变化，此时该数据是已经提交到数据库中了；幻读，前后两次读数据的时候，数据的条数发生了变化，此时数据也是已经提交到数据库中了。

三、隔离级别

四个隔离级别：读未提交、读已提交、可重复读、可串行化。(隔离等级越来越高，并发程度越来越低。)

1. 读未提交(read uncommitted)

在此隔离级别下面，避免了脏写现象，但是可以产生脏读、不可重复读与幻读现象。

2. 读已提交(read committed)

在此隔离级别下面，避免了脏写与脏读现象，但是可以产生不可重复读与幻读现象。

3. 可重复(repeatable read)

可以避免脏写、脏读、不可重复读的现象。

该隔离级别是可以产生幻读现象的，但是演示方法不一样，可以借助主键进行演示，因为主键是唯一的，不能重复。

4. 可串行化(serializable)

可以避免所有的并发产生的问题，避免脏写、脏读、不可重复读、幻读。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#查看当前事务的隔离级别
select @@session.transaction_isolation;
select @@global.transaction_isolation;
select @@tx_isolation;

#查询当前隔离级别
select @@session.transaction_isolation;

#设置权限
#set session transaction isolation level xxxxx
#设置当前隔离级别为读未提交(A设置权限，则A访问他人时有限制)
set session transaction isolation level read uncommitted;

#设置当前隔离级别为读已提交(A设置权限，则A访问他人时有限制)
set session transaction isolation level read committed;

#设置当前隔离级别为可重复读(A设置权限，则A访问他人时有限制)
set session transaction isolation level repeatable read;

#设置当前隔离级别为可串行化(A设置权限，则A访问他人时有限制)
set session transaction isolation level serializable;

总结：MySQL支持四种隔离级别，默认支持的是可重复读；Oracle只支持两种隔离级别，读已提交与可串行化，默认支持的是读已提交。隔离级别越高，串行程度越高，并发产生的问题越少。

四、索引

1. 概念

帮助MySQL高效获取数据的数据结构

索引可以提升查询速率，索引是数据结构

2. 数据结构

  • 顺序存储：会顺序扫描，从第一条一直扫描到满足条件的数据，时间复杂度是O(N)。
  • 二分查找：查询数据的时候，时间复杂度O(logN),需要连续的大段空间。
  • 二叉树：时间复杂度也是O(logN),树的高度会非常的高，每次查询的时候，都会进行磁盘IO，树的高度会影响查询的速率。
  • 哈希：时间复杂度O(1),使用哈希的时候，数据是没有顺序的，但是往往在数据库中是需要进行范围查找的；如果哈希有哈希冲突，那么也会影响查询速度。
  • B树：多路平衡树，一个结点可以存放多个索引，那么就可以急剧降低树的高度，那么就可以减少磁盘IO的次数。B树的结点需要存放key值以及key对应的value值。
  • B+树：非叶子结点只存放key值，不存放value值，那么就可以让一个结点中数据的条数增加，进而可以减少树的高度。
1
mysql> show variables like 'innodb_page_size'; #每个大结点最大占用的空间大小
1
2
3
4
5
6
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.05 sec)

可以从时间复杂度、连续内存、磁盘IO的次数、范围查找几个维度。

MySQL底层使用的数据结构是B+树。

3. B+树的特点

  • 非叶子节点不存储data，只存储key
  • 所有的叶子节点存储完整的一份key信息以及key对应的data
  • 每一个父节点都出现在子节点中，是子节点的最大或者最小的元素
  • 每个叶子节点都有一个指针，指向下一个节点，形成一个链表