mysql数据库知识总结

数据库介绍

数据库是用于存储和管理数据的系统，它允许用户以安全且高效的方式访问、更新和管理数据。数据库系统通常包括数据库管理系统（Database Management System, DBMS）和数据库本身。DBMS 是一种软件，它提供了创建、维护和操作数据库的能力，而数据库则是实际存储数据的地方。
DBMS 数据库管理系统：创建管理数据库的软件服务器

发展

• 层次
• 网状
• 关系数据库 ‘*’
• 对象数据库
• noSQL ‘*’ Not Only SQL 非关系数据库统称

数据库是磁盘上的一组文件有关联的一组数据对象的集合
物理结构：数据、日志、控制文件

表空间：一个或多个物理文件

• 段：按功能划分的
• 区：最小分配单位
• 块：最小使用（读写）单位

逻辑的数据对象：

• 表：存数据，多个列构成，列叫字段，名称唯一
• 视图：虚拟表
• 索引：快速查找
• 存储过程
• 函数
• 触发器：特定的条件执行
• 包：函数，过程做封装

关系数据库

基于关系代数建立，数据存储在二维表中，进行各种关系运算

IBM研究员的60年代的论文首次提到关系代数，70年代Oracle的创始人 安德尔森，基于这篇文章开发出了Oracle

产品

• Oracle 70年代 商业产品
• Db2 IBM产品 商业产品
• SQL server 商业产品
• Sybase 商业产品
• PostgreSQL 开源c实现 ‘最先进的数据库’ 学院派 PgSQL
• MySQL 开源c++实现 ‘最流行的数据库’ 8.0提供了文档数据库的支持
• SQLite 开源c实现 嵌入式数据库 几百k
• OceanBase
• …

noSQL

• redis 键值数据库(KV) 缓存
• mongoDB 文档数据库 能在很多场景替代数据库 打破了关系范式
• neo4j 图数据库 网络
• clickhouse/Hbase 列数据库
• 时序数据库
• 巨衫数据库 分布式文档数据库

MySQL安装

安装的mysql是数据库服务，用于创建、操作、管理数据库

1. 软件仓库安装
   sudo apt install mysql-server
   yum/dnf install mysql-server
   ubuntu 22 默认安装 8.0

2. 从官网下载安装文件

3. 添加mySQL官方仓库

4. 下载源码编译

MySQL 核心操作

SQL：结构化查询语言，技术规范标准

mysql服务管理

service mysql start
service mysql stop
service mysql restart
service mysql status

状态查看

/h 查看帮助
/s 查看状态
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
以上表示字符集，这四个字符集要一样否则可能会乱码

select user from user; 查看用户
show databases; 查看数据库
show tables; 查看表
desc table_name; 查看表结构

DCL 数据控制语言

用户与权限

-- 创建用户
create user 'user_name'@'host' identified by 'password';
-- 授权 host为'%' 表示所有主机
grant all on db_name.* to 'user_name'@'host';
-- 进入MySQL
mysql -u user -p

DDL 数据定义语言

DDL:数据定义语言，创建、删除、修改、截断

• create
• drop
• alter
• truncate

数据库相关

-- 创建数据库
create database db_name;
-- 删除数据库
drop database db_name;
-- 连接数据库
use db_name;

表相关

-- 创建表
create table table_name(
    col1 INT AUTO_INCREMENT PRIMARY KEY,
    col2 DECIMAL(7,2),
    col3 CHAR(12) NOT NULL DEFAULT '10086',
    col4 VARCHAR(13) CHARACTER SET utf8mb4,
    col5 ENUM('Y','N','NUL'),
    col6 date,
    col7 set('a','b','c')
);

-- 备份表结构并写入数据
create table t2 as select * from t1;

-- 修改表结构
alter table t1
add col7| drop col2 | modify(change) col3 | RENAME COLUMN col4 to col5 | RENAME t2;
增加列   | 删除列     | 修改列               | 重命名列                    | 重命名表

-- 截断：删除所有记录、释放表空间、重置自动增长的计数器（删除表结构再新建表）
truncate table t1;

视图相关

-- 创建视图,基于表查询创建的一个命名的SQL语句，查询视图相当于运行SQL语句
create view v_dept_view as
select d.name, count(s.id) as 人数
from staff s
right join dept d on s.dept_id = d.id
group by d.id,d.name
order by 人数 desc;

DML 数据操作语言

DML:数据库操作语言，增加、删除、修改数据

• insert
• delete
• update

insert into table_name(col1,col2,col3,col4,col5) values(1,2.3,'hello','world','Y',curdate(),'a,b');
-- 插入指定字段
insert into table_name(col1,col5,col7) values(1122,2,3);
-- 插入多条记录
insert into table_name(col1,col7) values(1213,curdate()),(1345,curdate()),(1534,curdate());
-- 更新指定记录
update table_name set col1=1,col3='100' where col2=2.3;
-- 更新所有记录
update table_name set col1=1
-- 删除指定列
delete from table_name where id=11122;

DQL 数据查询语言

下面是一个查询语句的例子

-- 查看表内容
select ** from table_name;
-- 查看表格式化内容
select ** from table_name\G
-- 投影运算 只查看指定列
select col1, col2 from table_name;

select
    c1,
    distinct c2, -- 去重
    c3 + ifnull(c4, 0), -- 函数 运算
    c4 * 12
from t1
inner join t2 on t1.fk = t2.pk -- 内连接
LEFT | RIGHT join t3 on t1.fk = t3.pk -- 左右连接
-- 过滤条件：
-- 比较 !=, <>, >=, >
-- 离散与范围 [not] in (离散值,,), between min and max
-- is NULL | not null
-- 子查询 in | any | all (subquery), 'not in' == '<> all', 'in' == '= any'
-- 模糊匹配 like '%a%' | ‘_a_'    _表示单个字符，%表示任意个字符
-- 日期字符串也可以比较 '2022-01-01' > '2021.01.01'
where 过滤条件
-- 分组：分组后的字段可以和聚合函数一起查询，分组后只能查询分组字段和聚合函数
group by 分组字段
-- 针对分组字段过滤
having 分组过滤条件
-- 排序 asc | desc 按排序字段正序 | 倒序排序
order by 排序字段
-- 分页：以下等价于limit m,n
limit 每页显示的记录数n offset 偏移m;

聚合函数

聚合函数一般不能与其它字段一起查询，除非字段在group by语句中出现，聚合函数可以出现在select之后from之前，也可以出现在having语句中

• count([distinct]) 计数并且自动去除NULL，加distinct去除重复值
• sum()
• avg()
• max()
• min()

表连接

• 笛卡尔积 左边表的所有行与右边表的所有行连接，所得行数为两表行数乘积

  select * from staff,dept;
  

• 内连接 交集 所有匹配条件的字段

  select * from staff inner join dept on dept_id = dept.id;
  -- 等价于
  select * from staff,dept where dept_id = dept.id;
  

• 外连接
  • 左外连接 包含左表所有记录，右表没有匹配的记录的字段都用NULL填充
  • 右外连接 包含右表所有记录，左表没有匹配的记录的字段都用NULL填充
  • 全连接 并集 包含左右表的所有链接后的记录

  -- 左连接
  select s.id, d.name, city from staff s left join dept d on s.dept_id = d.id;
  -- 右连接
  select s.id, d.name, city from staff s right join dept d on s.dept_id = d.id;
  -- 全连接
  select s.id, d.name, city from staff s left join dept d on s.dept_id = d.id
      -- 联合
  union
  select s.id, d.name, city from staff s right join dept d on s.dept_id = d.id;
  

• union 联合查询 将两个结果集合并（拼接） union all 不去重

  -- 左连接
  select s.id, d.name, city from staff s left join dept d on s.dept_id = d.id
  -- 联合
  union
  -- 右连接
  select s.id, d.name, city from staff s right join dept d on s.dept_id = d.id;
  

• 自然连接 自动根据两个表的相同字段进行内连接，并去除多余的列和行

子查询

条件语句中嵌套查询语句，嵌套的查询相当于返回一个集合,在连接中嵌套子查询相当于返回一个表

  select * from staff
  where dept_id = (select dept_id from staff where name = 'tom') 
  and salary > (select salary from staff where name = 'tom');

  select d.*, ifnull(t.n, 0) num
  from dept d
  left join
  (
      select dept_id, count(id) as n
      from staff
      group by dept_id
  ) t
  on d.id = t.dept_id;

窗口函数

窗口函数：窗口函数可以在一组相关的行（窗口）上执行计算，同时保留每行的独立性,语法是在函数后加over()
与聚合函数相比，聚合函数是对分组后的每一组做统计，而窗口函数是对分区（窗口）后的每一行起作用

• row_number() over(order by column desc) 生成序号并且可以按列排序
• rank() over(order by column desc) 生成排名并且可以按列排序
• dense_rank() over(order by column desc) 生成无并列排名并且可以按列排序

select id, name, salary, row_number() over(order by salary desc) n from staff;
-- 分组分别操作
select id, name, gender, salary, row_number() over(partition by gender order by salary desc) n from staff;

TCL 事务控制语言

TCL:事务控制语言，用于管理事务，保证数据的一致性

• begin 开始手动事务模式
• commit 提交事务
• rollback 回滚事务

关系建模

ER模型 ER图

数据库中的表存储的是实体（事物、概念），以及实体之间的关系
实体：对应表，包含属性（特征）
属性: 字段名 数据类型 约束条件 默认值 自动增长

实体间关系：

• 1:1
• 1:N
• N:M

关系范式

• 1NF 列是单一数据项，不可拆分
• 2NF 非键字段直接依赖主键
• 3NF 非键字段之间不存在依赖关系,或者是主键与非键字段是间接依赖
  以上范式作为数据库设计的指导,可适当拆分达到3NF，降低冗余和保证数据完整性，但是可适当打破范式提高性能

表：存数据，多个列构成，列叫字段，名称唯一，多个行（记录），不存在两条完全一样的行
键：选定的一列或多列构成

• 主键 一个主键唯一地标识一条记录，主键值唯一，通常不修改，采用两种策略
  • 业务主键 现实中有意义的可能会变化
  • 代理主键 在表中无意义，不会改变，通常自动生成 序列、自动增长、UUID（全世界唯一,包括时间戳、时钟序列、节点ID等信息）
• 外键 外键可以有多个，建立当前表与引用表之间的联系，是引用其他表中的主键或唯一键
• 唯一键 必须唯一

数据类型

各数据库管理系统中有不同的数据类型及SQL方言，不尽相同

• 数值
  • 整型
    • BIT 1bit
    • TINYINT BOOL 1
    • SMALLINT 2
    • MEDIUMINT 3
    • INTEGER INT 4
    • BIGINT 8
  • 浮点型
    • DECIMAL
    • NUMERIC
    • FLOAT
    • REAL
    • DOUBLE
• 字符
  • CHAR 定长 默认255字符
  • VARCHAR 变长 实际存储的字符为数据字符数+1(多一个’\0’)
  • BINARY 定长二进制字符，字节
  • TEXT 文本 最多存65535个字符
  • LONGTEXT 长文本 最多存4294967295个字符
  • BLOB 长二进制
  • ENUM 枚举 单选 最多65535个单项 ''为0
  • SET 集合 值’a,b’二进制为0011（a为1，b为2,集合为ab值的或）
• 日期与时间
  • DATE 日期 ‘0000-00-00’
  • TIME 时间 ‘00:00:00’
  • DATETIME 日期+时间 ‘0000-00-00 00:00:00’
  • TIMESTAMP 时间戳 ‘0000-00-00 00:00:00’
  • YEAR 年 0000
• JSON 原生的JavaScript数据对象，同时也是通用的数据交换格式

//列表 数组
[
    'a','b','c',123,true,false,null
]
//对象
{
    "name":'张三',
    "age":18,
    "sex":true,
    "hobby":["足球","篮球"],
    "address":{
        "province":"北京",
        "city":"北京",
        "street":"朝阳区"
    }
}

• 空间数据类型 GIS

数据库引擎

• InnoDB * 通用的，支持事务处理
• MyISAM 早期使用，不支持外键、事务处理
• Memory 内存
• NDB 集群

事务与死锁

事务

数据操作：CRUD
业务操作：业务逻辑，包含一个或多个数据操作，如开户、支付和购物
事务：一组数据操作，遵循ACID原则

• A:原子性 构成事务的多个操作不能被分割，要么全部成功，要么全部失败（回滚、撤销）
• C:一致性 事务前后，数据状态一致
• I:隔离性 多个事务执行过程中互不干扰，有以下几种隔离级别：
  • 读未提交（没隔离）
  • 读已提交 其它事务不能读取未提交的数据
  • 可重复读（Mysql默认）事务中的所有查询都会创建并只能看到该事务开始时的数据快照（MVCC ，多版本并发控制），这意味着在同一个事务A中，多次读取同一数据会得到相同的结果，即使在这期间有其他事务B对数据进行了修改，因为修改也只是修改事务B的快照。
  • 串行化 一个事务结束后其他事物才可以开始，保证事务串行执行，防止死锁
• D:持久性 事务结束数据从内存写入到磁盘

并行事务的问题:

• 脏读：一个事务读取了另一个事务未提交的数据，如果随后那个事务回滚了，那么第一个事务读取的数据就是无效的,导致数据不一致
• 不可重复读：多次读取同一数据，在没有新数据插入的情况下，返回的结果却不同，这是因为在两次读取之间，有其他事务提交了对数据的修改。
• 幻读：指在一个事务中，两次相同的范围查询返回了不同的结果集，这是因为在两次查询之间，有其他事务插入了新的行。

隔离级别	脏读	不可重复读	幻读
读未提交	+	+	+
读已提交	-	+	+
可重复读	-	-	+
串行化	-	-	-

分布式事务

死锁

死锁：多个事务互相等待对方释放锁，导致无法继续执行

last_insert_id() 返回最近插入的自动增长的id

锁

• 行锁：锁定某行、某几行数据或者行之间的间隙，同一时间只能有一个事务对行数据进行读写操作
• 表锁：锁定表，同一时间只能有一个事务对一张表进行读写操作
• 乐观锁：假定不会发生冲突，在读取时判断数据是否被修改，如果被修改，则重新读取数据，直到数据未被修改，才返回数据
• 悲观锁：假定会发生冲突，在读取时先加锁，直到读取结束，才释放锁

索引

索引：加速查询，提高查询效率，占空间，对插入、更新、删除性能有影响

基于B+树 BTREE

• 主键索引 主键的列的索引,唯一的并且不能为空
• 普通索引
• 唯一索引
• 全文索引 全文索引用于在大量文本中进行全文搜索，可以识别文本中的关键词
• 函数索引 基于表达式或函数计算结果的索引，建立在函数结果上的
• 位图索引：使用位图或位数组来存储和检索数据，适用于低基数列，即列中不同值较少的情况，如性别
• 反向键索引：通过反转键值，可以使得原本连续的键值在索引中分散得更均匀

函数

• 内置函数
  • 聚合函数
  • 单行函数
• 自定义函数

字符串函数

• concat() 拼接

  select name, level, concat(name, ' is ', level) as info from staff;
  

• lcase() 小写/ ucase() 大写 大小写转换

  select ucase(name) from staff where lcase(name) = 'tom';
  

• quote() 加引号
• ltrim() rtrim() trim()去除字符串首/尾/左右空格

  select trim('   tom   ') as t, ltrim('   tom   ') as l, rtrim('   tom   ') as r;
  

• lpad() rpad() 用指定字符填充字符串

  -- 设置填充至10个字符
  select quote(lpad('tom', 10, '·')) as l, rpad('tom', 10, '·') as r;
  

• locate(substr, colmun) 查找字符串位置
• cast(str as type) 转换数据类型
• substring() 截取字符串

  -- 从第二个字符开始截取两个字符
  select substring('hello mysql', 2, 2);
  -- 从第二个字符开始截取到末尾
  select substring('hello mysql', 2);
  

• length() 字符串字节长度 char_length() 字符串字符长度

  mysql> select char_length('测试中文');
  +-----------------------------+
  | char_length('测试中文')     |
  +-----------------------------+
  |                           4 |
  +-----------------------------+
  1 row in set (0.00 sec)
  
  mysql> select length('测试中文');
  +------------------------+
  | length('测试中文')     |
  +------------------------+
  |                     12 |
  +------------------------+
  1 row in set (0.00 sec)
  

• 密码加密：md5()生成32位md5加密字符串，sha2()转换成64位16进制字符串

  select md5(name), char_length(md5(name)) from staff;
  select name, sha2(name, 256), char_length(sha2(name, 256)), length(sha2(name, 256)) from staff;
  

• uuid() 生成唯一id uuid_short() 生成短

  insert into t1 values(uuid());
  

日期函数

• now() sysdate() 当前日期时间，curdate() 当前日期，curtime() 当前时间,year() month() day() hour() minute() second()
• unix_timestamp() 时间戳 从1970-01-01 00:00:00开始的秒数
• date_format() 格式化日期 time_format() 格式化时间

  select date_format(now(), '%m-%d-%y %h:%i:%s %W') as now;
  

• date_add(日期，interval n 单位) adddate(日期，天), 日期加减 addtime()时间加减

  select date_add(curdate(), interval 20 day);
  select adddate(curdate(), 20);
  SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
  select addtime('23:59:59', '1:1:1');
  

• datediff() 日期差 timediff() 时间差

数值函数

• round() 四舍五入到小数点后指定位数，默认取整
• floor() 向下取整 ceil() 向上取整
• truncate() 截断到小数点后指定位数

select round(3.7), round(123.456, 2), ceil(123.456), floor(123.456), truncate(123.456, 2);

自定义函数

自定义函数：有参数，有返回值，不能独立执行，嵌入到sql语句中执行

CREATE FUNCTION `salary`(sal dec)
RETURNS decimal(10,0)
BEGIN
	RETURN sal * 14;
END

存储过程

存储过程：一组命名的SQL语句的块，可以重复使用，封装一条或多条SQL语句，无返回值，除非参数是out|inout|in类型，可以得到值

-- 基本语法

CREATE
PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT, INOUT s_day varchar(20))
-- 存储过程体
BEGIN
	-- 把SQL中查询的结果通过INTO赋给变量
	SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
    -- 条件语句
	IF `day` = 0 THEN
	SELECT '星期天' INTO s_day;
	ELSE IF `day` = 1 THEN
	SELECT '星期一' INTO s_day;
    CASE num  -- 条件开始
	WHEN 1 THEN 
		SELECT '输入为1';
	ELSE 
	SELECT '不是1';
	END CASE; -- 条件结束
    declare num int;
    set num = 0;
    -- 循环语句
    WHILE num<10 DO -- 循环开始
         SET num = num+1;
    END WHILE; -- 循环结束
END

-- 列子
CREATE PROCEDURE `change_balance`(
	p_from int,
    p_to int,
    p_amount int
)
BEGIN
	update account set balance = balance - p_amount where id = p_from;
    update account set balance = balance + p_amount where id = p_to;
    insert into log(`from`, `to`, `amount`) values(p_from, p_to, p_amount);
    commit;
END

MySql API

安装libmysqlclient库

apt install libmysqlclient-dev

头文件：/usr/include/mysql
库目录：/usr/lib/mysql

API使用

结构体

• MYSQL 数据库连接处理器
• MYSQL_RES 结果集，查询返回的数据(行列)
• MYSQL_ROW 一行，从MYSQL_RES中获取一条记录
• MYSQL_FIELD 字段信息：列名，类型，长度

函数

• mysql_init(MYSQL*) 初始化
• mysql_real_connect(…) 连接
• mysql_close(MYSQL*) 关闭
• mysql_query(MYSQL*, const char*) 执行以’\0’结尾的sql语句
• mysql_real_query(MYSQL*, const char *stmt_str,unsigned long length) 执行length长度sql语句
• mysql_store_result(MYSQL*) 获取结果集
• mysql_free_result(MYSQL_RES*) 释放结果集
• mysql_fetch_row(MYSQL_RES*) 获取一行
• mysql_fetch_field(MYSQL_RES*) 获取字段
• mysql_num_rows(MYSQL_RES*) 获取结果集行数
• mysql_num_fields(MYSQL_RES*) 获取结果集列数

一个链接数据库的例子：

#include <stdio.h>
#include <mysql/mysql.h>

int main(int argc, char const *argv[])
{
    // 初始化
    //连接器
    MYSQL *mysql = NULL;
    // 结果集 结构体
    MYSQL_RES *res = NULL;
    // 行 字符串数组
    MYSQL_ROW row;
    mysql = mysql_init(NULL);
    if (mysql == NULL)
    {
        fprintf(stderr, "mysql_init failed\n");
        return -1;
    }

    // 参数
    char *host = "192.168.74.134";
    char *user = "test";
    char *passwd = "123456";
    char *db = "hr";
    int port = 3306;

    // 连接
    mysql = mysql_real_connect(mysql, host, user, passwd, db, port, NULL, 0);
    if (mysql == NULL)
    {
        fprintf(stderr, "连接失败\n");
        mysql_error(mysql);
        return -1;
    }
    printf("连接成功\n");

    // 执行sql语句
    char *sql = "update dept set name = '运维', city = '深圳' where id = 10";
    mysql_query(mysql, sql);

    sql = "select id, name, city from dept";
    mysql_query(mysql, sql);

    // 获取结果集
    res = mysql_store_result(mysql);
    if (res == NULL)
    {
        fprintf(stderr, "获取结果集失败\n");
        mysql_error(mysql);
        return -1;
    }
    printf("行数：%ld\n", res->row_count);
    printf("列数：%u\n", res->field_count);

    // 遍历结果集
    // 打印表头
    // 字段元数据
    MYSQL_FIELD *field = malloc(res->field_count * sizeof(MYSQL_FIELD));
    field = mysql_fetch_fields(res);
    for(int i = 0; i < res->field_count; ++i)
    {
        printf("%s\t", field[i].name);
    }
    printf("\n");
    while((row = mysql_fetch_row(res)))
    {
        for(int i = 0; i < res->field_count; ++i)
        {
            printf("%s\t", row[i]);
        }
        printf("\n");
    }
    mysql_free_result(res);
    mysql_close(mysql);
    return 0;
}

编译参数

    cc mysql.c -o mysql -L/usr/lib/mysql -lmysqlclient