MySQL基本原理、常用命令和常见面试题
MySQL基本原理、常用命令和常见面试题
基本概念
数据库三范式
- 数据库表中的字段都是单一属性，不可再分。强调的是列的原子性；数据库表的每一列都是不可分割的原子数据项；
- 要求数据库表中的每个实例或行必须可以被唯一区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一标识属性列被称为主键
- 确保每列都和主键列直接相关，而不是间接相关不存在其他表的非主键信息；
DML：数据操作语言，用于检索或者修改数据。我们平常最常用的增删改查就是DML；
DDL：数据定义语言，用于操作数据结构，比如创建表，删除表，更改索引都是DDL；
DCL：数据控制语言，用于定义数据库用户的权限，比如创建用户，删除用户都是DCL；
mysql数据库
MySQL是一个传统的关系型数据库。广泛应用于OLTP场景（支持事务）
OLTP:联机事务处理，是传统的关系型数据库的主要应用，用于基本的事务处理；【日常处理】
OLAP:联机分析处理，数据仓库系统的主要应用；支持复杂的分析操作，侧重决策支持，并且提供易懂的查询结果；【专业数据分析】
mysql特性
存储引擎
存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
show ENGINEs;//查看当前服务器支持的存储引擎
1.MyISAM引擎：不支持事务，也不支持行级锁和外键 约束,只支持全文索引,数据文件和索引文件是分开保存的。特点:访问速度快,只适合查询或插入为主的应用
2.InnoDB引擎：提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。
3.MEMORY引擎：所有数据都在内存中，数据处理速度快，但是安全不高；
索引
大家都知道加上索引sql语句会变快，为什么？这个问题大家却回答的没有逻辑性；
不过没关系，一一分析一下几个大问题
1、索引是什么
2、什么能够成为索引？
3、索引为什么能加快速度？
4、mysql的索引是什么，为什么选择B+Tree?
1.索引是什么？
索引你是一种特殊的文件，他们包含着对数据表里所有记录的引用指针；
索引是一种数据结构，而且索引是一个文件，是要占据物理空间的；
2.索引的优缺点
优点：
可以大大加快数据的检索速度；
通过使用索引，可以再查询的过程中，使用优化隐藏器，提高系统的性能；
缺点：
时间方面：创建索引和维护索引需要耗费时间，而且索引也需要动态的维护，会降低增/删/改的执行效率；
空间方面：索引需要占物理空间；
3.创建索引的方式
alter table table_name add index index_name(column_list);
create index index_name on table_name(column_list);
4.MySQL有几种索引类型？
普通索引：一个索引只包含单个列，一个表可以有多个单列索引；
唯一索引：索引猎德值必须是唯一的，但允许有空值；
复合索引：多列值组成一个索引，专门用于组合搜索，效率大于索引合并；
聚簇索引：并不是单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引都在同一个结构中保存了b+Tree和数据行；
非聚簇索引：不是聚簇索引，就是非聚簇索引；
5.索引的底层实现方式
Hash索引：对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针；
B-Tree索引：
在这里插入图片描述
B+Tree
在这里插入图片描述
6.为什么索引结构默认选择B+Tree？（这种问题就是分析其他索引的缺点，说出B+Tree的优点即可）
Hash索引虽然可以快速定位，但是没有顺序，IO复杂度高；
MySQL基于Hash实现，只有Memory存储引擎显式支持哈希索引；
Hash不支持范围查询；
如果有大量重复键值的情况下，哈希索引的效率很低，以为存在哈希碰撞问题；
B+Tree非叶子节点不存储数据，只有叶子节点才存储数据；
B+树是为粗盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点，各叶子节点之间通过双向链表进行连接；
锁
从锁粒度来讲：表级锁、行级锁
从强度上讲：共享锁/读锁、排它锁/写锁；
MySQL服务器层并没有实现行锁机制，行锁只在存储引擎层实现；
InnoDB支持行锁he表锁；而MyISAM支持表锁；
表锁
概念：顾名思义其实就是锁定整张表；不管什么存储引擎，对于表锁的策略都是一样的；是开销最小的锁机制；
优点：表锁直接锁定整个表，所以可以很好的避免死锁问题；
缺点：锁的粒度大带来的就是锁资源争用的概率也会最高，导致并发率降低；
行锁
概念：锁住某一行；
分类：
(1)记录锁：基于唯一索引的，锁住的是改行的索引记录；即使没有设置索引，也会有innodb自己的“隐式主键”来进行锁定；
(2)间隙锁：锁定一段范围内的索引记录
共享锁/读锁
允许事务读操作；多个事务在同一时刻可以读取同一个资源；
排它锁/写锁
一个写锁会阻塞其他的读锁和写锁，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源；
如何加锁
锁机制：InnoDB是可以随时加锁，但不代表可以随时解锁；只有事务commit或者rollback才可以释放锁，而且所有的锁在同一时刻被释放；
对于常见的DDL语句（create,alter）InnoDB会自动给相应的表加表级锁；
对于常见的DML语句（update,delete,insert）InnoDB会自动给相应的记录加写锁；
对于普通的select语句，InnoDB不会加任何锁；
InnoDB也支持通过特定的语句进行显示锁定：两种模式：
select …… lock in share mode加共享锁/读锁；
select …… for updates 加排它锁/写锁；
事务
特性：原子性，一致性，隔离性，持久性
原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败；
一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态，换一种方式理解就是：事务按照预期生效，数据的状态是预期的状态；
隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离；
持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响;
数据通过原子性、隔离性、持久性来保证一致性；
原子性实现原理:InnoDB,在干活之前，先将要做的事情记录到一个叫undo log的日志文件中，如果失败了或者主动rollback，就可以通过undo log的内容，将事务回滚；(undo log：逻辑日志，记录的是sql执行相关的信息)
原子性
start transaction;
//操作语句
commit;
rollback;
事务提交（commit）和事务回滚（rollback）。
一致性
一致性主要说明的是事务的前后，数据库中的数据的状态要确保一致。
隔离性
隔离性的体现，多个并发事务之间是隔离的。
持久性
性能优化
数据类型
数值型
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INT 4
BIGINT 8
FLOAT 4
DOUBLE 8
DECIMAL
字符串型
CHAR 定长字符串
CARCHAR 变长字符串
TINYBLOB
TINYTEXT
BLOB 二进制的文本数据
TEXT 长文本数据
...
日期和时间型
YEAR 1
TIME 3
DATE 3
DATETIME 8
TIMESTAMP 4
约束
primary key ：主键约束
主键约束就是用来标识表中的数据，避免出现重复的数据，被主键约束所修饰的'列'，其列值是唯一且非空的，而且一张表中只能有一个主键约束
unique：唯一约束
唯一约束就是用来修饰一列或者多列的组合值，使其具有唯一性，防止用户输入重复的数据
default：默认值约束
默认值约束是用来知道某列的默认值，当执行插入操作的时候，如果该列没有插入列值，系统会自动把默认值编程列值，每列只能设置一个默认值
not null：非空约束
非空约束就是在执行插入操作时候，被非空约束所修饰的列，列值不能为空
常用操作命令
数据库操作
//创建数据库
CREATE DATABASE mydatabase;
//show查看数据库
show mydatabase;
//创建表
CREATE TABLE users (
id varchar(100) primary key,
passwd varchar(100) not null,
status int DEFAULT 0
);
//删除表
DROP TABLE users;
//SELECT查看表
SELECT * FROM users;
DESCRIBE users;
SELECT *
FROM users
LIMIT 5,5;//返回第5行开始的5行数据(从0开始算,与数组一样)
SELECT users.id
FROM mydatabase.users;//完全限定的表名
数据操作
//去重
SELECT DISTINCT order_num
FROM OrderItems
//ORDER BY排序数据
SELECT id
FROM users
ORDER BY id;//按单列id的字母顺序小到大升序排序(默认ASC关键字)
SELECT id, score
FROM users
ORDER BY score, id;//按多列score,id升序排序,先看score,相同再看id
SELECT id
FROM users
ORDER BY id DESC////按单列id的字母顺序大到小降序排序
SELECT id, score
FROM users
ORDER BY score DESC, id;//按多列score降序排序,id升序排序,先看score,相同再看id
SELECT score
FROM users
ORDER BY score DESC
LIMIT 1;//通过排序和限制找到score的最大值
//WHERE过滤(搜索)
常用操作符:=(等于), <>或!=(不等于),BETWEEN(之间)
SELECT id, score
FROM users
WHERE score = 2.50;//返回score等于2.50的id和score
//组合WHERE子句,最好用括号
SELECT id,score
FROM users
WHERE score BETWEEN 5 AND 10;//返回score在5到10之间的id和score
SELECT id, score
FROM users
WHERE id = 5 OR id = 10;////返回id是5或10的id和score
//通过IN操作符
SELECT id, score
FROM users
WHERE id IN (5, 10);////返回id是5或10的id和score
SELECT id, score
FROM users
WHERE id NOT IN (5, 10);////返回id不是5或10的id和score
//用谓词LIKE+通配符&或_过滤
SELECT id, score
FROM users
WHERE id LIKE 'jet%';//使用搜索模式'jet%',搜索jet+任意字符的id(%代表任何字符出现任意次数, 0次也可,但是不能匹配NULL,区分大小写)
SELECT id, score
FROM users
WHERE id LIKE 'jet_';//使用搜索模式'jet_',搜索jet+一个字符的id(_代表任意一个字符, 区分大小写)
//用正则表达式REGEXP搜索
SELECT id
FROM users
WHERE id REGEXP '.000'
ORDER BY id;//正则表达式'.000',匹配任意一个字符
SELECT id
FROM users
WHERE id REGEXP '1|2'
ORDER BY id;//正则表达式1|2,|是正则表达式中的OR,匹配存在1和2的数据
SELECT id
FROM users
WHERE id REGEXP '[123] Ton'
ORDER BY id;//正则表达式[123] Ton,[]是正则表达式中的另一种形式的OR,是[1|2|3]的缩写,也可写成[1-3],只匹配单独存在1或2或3的数据
SELECT id
FROM users
WHERE id REGEXP '\\.'
ORDER BY id;//表示搜索.,对应其他的特殊字符\\f(换页),\\n(换行),\\r(回车),\\t(制表),\\v(纵向制表)
//字符类搜索
[:alnum:]
[:digit:]
//定位符
^文本开始,$文本结尾,[[:<:]]词开始,[[:>:]]词结尾
1.WHERE再ORDER BY
2.不区分大小写
3.NULL代表空值,与0不同
4.优先处理AND
5.IN更加方便,简单
6.NOT可对IN,BETWEEN,EXISTS和LIKE等子句取反
7.LIKE和REGEXP的区别,LIKE匹配整个列,REGEXP列值内进行匹配(可用^和$定位符实现LIKE的功能)
8.[123]和[1|2|3]的区别
//函数
//文本处理函数
SELECT id, Upper(id) AS id_upcase
FROM users
ORDER BY id;//id转换为大写并保存到id_upcase列中
//日期和时间处理函数
SELECT id
FROM users
WHERE Date(date) = '2005-09-01';
//数字处理函数
Abs()等
//聚焦函数
SELECT AVG(score) AS avg_score
FROM users;//返回值avg_score
SELECT COUNT(*) AS score_num
FROM users;//对表中行的数目进行计数,并在score_num中返回
//分组
//分组GROUP BY子句
SELECT id, COUNT(*) AS num
FROM users
GROUP BY id;//指定两个列id和num，计算每个id的数量并返回到num中（按组聚集）
//过滤分组HAVING子句
与WHERE类似，但HAVING过滤分组，WHERE过滤行
SELECT id, COUNT(*) AS orders
FROM users
GROUP BY id
HAVING COUNT(*) >= 2;//过滤了COUNT(*) >= 2的分组
//排序过滤分组
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;//按order_num分组，按组过滤，按ordertotal排序
GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前
其他的执行顺序：
from
where
group by
having
聚合函数
select
order by
//联结
主键：两个表共有的列名
外键：某个表的一列，它包含另一个表的主键值
可伸缩性：适应不断增加的工作量而不失败
内联结：inner join。取两列的交集。
//创建内联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;//INNER JOIN 两个表内联结，联结子句中用ON代替WHERE
外连接 (OUTER JOIN)分为三种
1. 左外连接 (LEFT OUTER JOIN 或 LEFT JOIN）：左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为NULL
2. 右外连接 (RIGHT OUTER JOIN 或 RIGHT JOIN)：与左(外)连接相反，右(外)连接，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录，左表记录不足的地方均为NULL
3. 全外连接 (FULL OUTER JOIN 或 FULL JOIN)：左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充
MySQL中不支持全外连接，可以使用 UNION 来合并两个或多个 SELECT 语句的结果集
//创建外联结
SELECT customers.cust_id, order.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;//OUTER JOIN两个表外联结，必须指定LEFT或RIGHT指定包括其所有行的表
//组合查询
union all的使用
select prod_name
from Products
union all
select cust_name
from Customers
order by prod_name;//连接，前面的列名会保留
//在组合查询中，排序只能放在所有语句的最后，且出现一次。
//插入
INSERT INTO users VALUES(1,111);
Qstring sql = QString(" insert into users values(%1, %2, %3);").arg(id).arg(status).arg(passwd);
#include <QSqlQuery>
QSqlQuery query;
if(query.exec(sql))
{
QMessageBox::information(this, "提示","插入成功");
}else{
QMessageBox::information(this, "提示","插入失败");
}
//查找
QsqlQuery query;
query.exec("select * from users;");
while(query.next())
{
qDebug() << query.value(0); //注意类型
qDebug() << query.value(1);
qDebug() << query.value(2);
}
// 删除
QsqlQuery query;
query.exec("delete from users where id="1";");
