数据库 MySQL 面试题

MySQL

MySQL是一个小型关系数据库管理系统。

C++中使用MySQL

include 头文件
创建数据库句柄
连接数据库
调用mysql_query函数执行sql语句
返回结果

#include <mysql.h>
 #include <iostream>
 #include <cstring>
 #include <stdio.h>
 
 using namespace std;
 
 MYSQL mysql;    // 数据库句柄
 MYSQL_RES* res;    // 查询结果集
 MYSQL_ROW row;    // 记录结构体
 
 // 查询全部数据并输出
 void display() {
     // 查询数据
     int ret = mysql_query(&mysql, "select * from student;");
     
     // 获取结果集
     res = mysql_store_result(&mysql);
     
     cout << "ID " << "name " << "age " << "score" << endl;
     // 给row赋值，判断row是否为空，不为空就打印数据
     while (row = mysql_fetch_row(res)) {
         cout << row[0] << " ";
         cout << row[1] << " ";
         cout << row[2] << " ";
         cout << row[3] << endl;
     }
     // 释放结果集
     mysql_free_result(res);
 }
 
 int main() {
     // 初始化数据库
     mysql_init(&mysql);
     
     // 设置字符编码
     mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "gbk");
     
     // 连接数据库
     if (mysql_real_connect(&mysql, "localhost", "root", "password", "数据库名", 3306, NULL, 0) == NULL) {
         printf("错误原因：%s\n", mysql_error(&mysql));
         printf("连接失败！\n");
         exit(-1);
     }
     
     display();
     
     // 关闭数据库
     mysql_close(&mysql);
     return 0;
 }

redis 和 mysql 的区别

redis 是 NoSQL，非关系型数据库，工作在内存中，数据都是以 key-value 来存的。MySQL 是关系型数据库，两者在很多方面都不同，比如索引和事务

以管理员身份打开命令行

启动服务器：net start mysql
停止服务器：net stop mysql

连接服务器：

1.mysql -u root -h 127.0.0.1 -p
2.输入密码 123456

断开服务器：

exit 或者 quit

Navicat

先命令行启动mysql服务器，再打开连接

主键（primary key）

用于唯一标识表中的每一条记录

创建数据库

create database 数据库名；

删除数据库

drop database 数据库名；

使用数据库

use 数据库名；

查看数据库

show databases;

查看数据库创建语句

show create database 数据库名；

SQL（Structured Query Language，结构化查询语言）

创建表

CREATE TABLE students ( student_id INT UNSIGNED, name VARCHAR(30), PRIMARY KEY (student_id) );

查看创建表语句

show create table 表名;

删除表

drop table 表名;

插入记录

INSERT INTO students (student_id, name) VALUE (41048101, 'Lucy') (123, 'Freeze'); INSERT INTO students VALUE (41048101, 'Lucy') (123, 'Freeze');

修改表结构

修改表名

alter table 旧表名 rename as 新表名;

添加字段

alter table 表名 add 字段名 列类型;

修改字段

alter table 表名 modify 字段名 列类型; alter table 表名 change 旧字段名 新字段名 列类型;

删除字段

alter table 表名 drop 字段名

修改记录

update 表名 set 字段1 = 值1， 字段2 =值2 where 条件;

删除记录

delete from 表名 where 条件;

索引

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针
优点：
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2.可以大大加快数据的查询速度
3.在实现数据的参考完整性方面，可以加速表和表之间的连接
4.在使用分组和排序语句进行数据查询时，也可以显著减少查询中分组和排序的时间
缺点：
1.创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2.索引需要占磁盘空间
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度

分类

普通索引：允许重复值和空值
唯一索引：索引列的值必须唯一，允许空值
组合索引：在表的多个字段组合上创建索引，列值的组合必须唯一，遵循最左前缀原则
主键索引：值唯一，不允许空值
全文索引：支持值的全文查找，允许重复值和空值，只有MyISAM支持

设计原则

1.索引并非越多越好，大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新
2.避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该对经常用于查询的字段创建索引，但要避免添加不必要的字段
3.数据量小的表最好不要索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短
4.在不同值较多的列上建立索引，比如性别只有男女两个不同值，无须建立索引
5.当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度

创建索引的方式

1.在创建表的定义语句中指定索引列
2.使用ALTER TABLE语句创建索引
3.使用CREATE INDEX语句添加索引：CREATE INDEX 索引名 ON 表名(列名)
SHOW INDEX FROM table：查询table表的索引

MySQL 的索引

常用的存储引擎有 MyISAM 和 InnoDB
两者索引结构都是 B+ 树，但 InnoDB 使用的存储方式是聚集索引，每个表有且仅有一个聚集索引。
聚集索引指不仅缓存索引还缓存真实数据。InnoDB 默认使用主键顺序 (可自定义索引)为行记录构建一颗 B+ 树，叶节点以页为单位，存放完整行记录，所有叶节点存放了整张表。
B+ 树 <-> 表，叶子节点 <-> 页
聚集索引的存储并不是物理上连续的，而是逻辑上连续的，叶子结点间按照主键顺序排序，通过双向链表连接。

非聚集索引也称为辅助索引，同样是一棵 B+ 树，通过非聚集索引查找主键，再通过主键 (作为索引)去主键索引内查找行记录。

InnoDB 支持行级锁，事务，外键，热备份和容灾，事务隔离，MVCC 等，适合更新密集的场景

MyISAM 是顺序存储的，B+ 树叶节点存放的是行记录的物理地址，定位速度快，适合查询密集的场景

MySQL 为啥用 B+ 树

不用 hash 表是因为：
1.hash 冲突；2.不支持排序和范围查询

不用二叉搜索树（ BST ）是因为：
1.不可能一次性把索引全部装入到内存，只能逐一加载索引节点，因此 BST 的 I/O 次数过多，BST 是二叉树，树的高度更高，还存在极端情况的线性高度问题；
2.B+ 树单个节点包含的值越多，相对需要的 I/O 次数就越少
3.B+ 树高度一般为 2-4 层，只需要 2-4 次 I/O

不用红黑树 (平衡二叉搜索树)是因为：
1.虽然红黑树结合了平衡树和 BST，解决了 BST 极端情况下的线性高度问题，但是依然存在着 BST 的缺点
2.红黑树，BST 更适合在内存中使用进行内部查找和排序

不用 B 树是因为：
1.B 树的内节点也会存储 key 和 data，因此平均情况下，B 树的查找效率比 B+ 树更高
2.而 B 树的叶子节点是相互独立的，B+ 树叶子节点有指向相邻节点的指针，因此 B+ 树排序和范围查找的效率更高
3.因此 B 树更适合于做单一查询更多的数据库，如非关系型数据库，而 B+ 树则更适合做排序和范围查询更多的数据库，如关系型数据库