【MYSQL】总结

最新推荐文章于 2021-10-15 22:14:03 发布

在找bug的路上

最新推荐文章于 2021-10-15 22:14:03 发布

阅读量314

点赞数

分类专栏： c++

本文链接：https://blog.csdn.net/qq_36553828/article/details/107428499

版权

c++ 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.介绍

（一）数据库操作

1.cmd进入mysql:

mysql  -uroot -p密码

2.创建数据库：

create database test  character set 编码（'utf8'） collate 校对规则('utf8_bin')  //test为数据库名

3.展示数据库列表

show databases;

4.选择数据库

use rest_0716  // rest_0716为新建数据库名

5.删除数据库

drop database test_0716  //删除数据库“test_0716”

6.其他

show create database 数据库名称   // 查询数据库的创建信息
select	database();		//查询正在使用的数据库
alter	database	数据库名称	character	set	'gbk'	collate '校验规则'  //更改数据库编码

7.常用数据类型

类型		大小		用途
BIT		                 标识符（flase/ture）
TINYINT		1		   小整数值
INT			4			大整数
BIGINT		8			极大整数
FLOAT		4			单精度浮点数值
DOUBLE		8			双精度浮点数值
DATE	    3		YYYY-MM-DD			日期
TIME		3		HH:MM:SS			时间
DATETIME	8		同上		日期 时间
char		定长字符串	gender char(1)	范围：0<=M<=25
Varchar		可变长字符串	email varchar(20)  	范围：0<=M<=65535
text		文本串		content text	约2w-6w个字符（受字符集的影响）
NULL 不是假,也不是真,而是"空"，任何运算符,判断符碰到NULL,都得NULL，NULL的判断只能用is null,is not null

（2）数据表
1、创建数据表

CREATE TABLE 数据表名 (字段名 字段类型);   //定义

CREATE TABLE `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,  //AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1
   `runoob_title` VARCHAR(100) NOT NULL,   //不能为空
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,  
   PRIMARY KEY ( `runoob_id` )   //设置主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;  //ENGINE 设置存储引擎，CHARSET 设置编码。

2.约束条件（大写）：
非空约束(not null)
唯一性约束(unique) //字段名不能重复 1. ，unique(email) （表级约束） 2. 字段类型 unique 列级约束
主键约束(primary key) PK //该字段不能重复也不能为空还会默认添加”索引——index”
外键约束(foreign key) FK

3.删除数据表

DROP TABLE 数据表名 ;

DROP TABLE runoob_tbl ;

(二）数据操作

1.增加数据(insert)

//命令格式
INSERT INTO table_name ( field1, field2,...fieldN ) 
VALUES 
( value1, value2,...valueN );
//举例
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date)
VALUES
("学习 PHP", "菜鸟教程", NOW());

2.删除(delete)

DELETE FROM table_name [WHERE Clause];

DELETE FROM runoob_tbl WHERE runoob_id=1;

3.改（update）

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];

UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=1;

4.查（select）

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

在这里插入图片描述

(三)查询

1.ORDER BY：用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。多列时，先按照第一个column name排序，在按照第二个column name排序。

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]];

SELECT * from runoob_tbl ORDER BY submission_date data_test DESC;  //按照submission_date降序，相同时按data_test降序

2.GROUP BY：GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;    //计算数量

（四）外键

6.1、首先是外键的定义
如果一个字段X在一张表（表一）中是主关键字，而在另外一张表（表二）中不是主关键字，则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

6.2、主键表和外键表的理解
（1）以公共关键字作主键的表为主键表（父表，主表）
（2）以公共关键字作外键的表为外键表（从表，外表）

6.3 外键的作用
各种书籍和网上的资料大多都是抽象空洞的解释：保证数据的完整性和一致性。个人对此的理解与接下来的外键约束一起讲。

6.4分类：
外键的不同约束方式：
前置条件：

详细阅读：这里以用户表和用户组表为例，这是一个典型的多对一关系，多个用户对应于一个用户组。首先创建用户组表：
创建用户组表
create table t_group (
	id int not null,
	name varchar(30),
	primary key (id)
);
并插入两条记录：
插入记录
insert into t_group values (1, 'Group1');
insert into t_group values (2, 'Group2');

1、级联(cascade)方式
主表删除或更新一条记录---->从表相应记录外键项同时更新或记录被删除；

create table t_user (
	id int not null,
	name varchar(30),
	groupid int,
	primary key (id),
	foreign key (groupid) references t_group(id) on delete cascade on update cascade  //进行外链的设置
);

2、置空(set null)方式
主表删除一条记录---->从表相应记录外键项置空；

create table t_user (
	id int not null,
	name varchar(30),
	groupid int,
	primary key (id),
	foreign key(groupid) references t_group(id) on delete set null on update set null
);
参照完整性测试
	insert into t_user values (1, 'qianxin', 1); #可以插入
	insert into t_user values (2, 'yiyu', 2);    #可以插入
	insert into t_user values (3, 'dai', 3);     #错误，无法插入，用户组3不存在，与参照完整性约束不符

3、禁止(no action / restrict)方式
从表中有相关引用，因此主表中无法删除，也无法修改其主键；

禁止方式
create table t_user (
	id int not null,
	name varchar(30),
	groupid int,
	primary key (id),
	foreign key (groupid)references t_group(id) on delete no action on update no action
);
参照完整性测试
	insert into t_user values (1, 'qianxin', 1); #可以插入
	insert into t_user values (2, 'yiyu', 2);    #可以插入
	insert into t_user values (3, 'dai', 3);     #错误，无法插入，用户组3不存在，与参照完整性约束不符

（五）数据表结构设计

数据库实体间有三种对应关系：一对一，一对多，多对多。
1.一对一关系示例：
一个学生对应一个学生档案材料，或者每个人都有唯一的身份证编号。
注：一般不用，可用一张表表示出来

一对多关系示例：
一个学生只属于一个班，但是一个班级有多名学生。
实现方法：在数据库中通过添加主外键约束实现，学生表为避免删除数据时造成数据混乱！
3.多对多关系示例：
一个学生可以选择多门课，一门课也有多名学生。
实现方法：对于多对多表，通过关系表就建立起了两张表的联系！多对多时建立主外键后，要先删除约束表内容再删除主表内容

（六）多表查询

join操作

在数据库的查询中，多表连接查询是一大难点，也是多表查询里的重点。连接主要有以下四种情况：

INNER JOIN（内连接）：如果表中有至少一个匹配，则返回行【在语法中可以省略INNER关键字】

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN（左连接）：从左表返回所有的行，如果右表中没有匹配，对应的列返回Null

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
WHERE table1.column_name = table2.column_name

RIGHT JOIN（右连接）：从右表返回所有的行，如果左表中没有匹配，对应的列返回Null

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
WHERE table1.column_name = table2.column_name

FULL JOIN（全连接）：只要其中一个表中存在匹配，则返回行（即结合左连接和右连接的结果）

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

这里主要要理清两个问题：1）以哪个表为基础（从哪个表返回）；2）遇到没有匹配的怎么处理。
以下是各个连接方式的关系图：
在这里插入图片描述
对于学生表，课程表两张表来说，分别进行四种连接方式，结果如下：

注：1.实际中，最常用的是inner join
2.在有些语句里应用了“,”隔开两张表，它的作用相当于inner join

（七）多表查询

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列

前提条件：建立一个数据库

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL  
);

MySQL索引类型包括：
（1）普通索引：

CREATE INDEX indexName ON mytable(username(length));   //创建索引

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [indexName] (username(length))  
);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

（八）索引

8.1. 索引的定义
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
作用：索引可以提高查询效率
索引类型(常见的数据库书籍中的关于索引类别的一些称呼)：
①唯一索引：不允许其中任何两行具有相同值的索引
使用主键和候选键建立的索引就是唯一索引，因为主键和候选键都可以确定唯一一个元组，即一张表中不存在相同的主键和候选键。在MySQL中，当你建立一个主键和候选键之后，MySQL会为它们分别建立索引。毕竟要想满足唯一性，依然会在更新数据的时候检验是否已经存在该主键或者候选键，而索引无疑是快速检验的标配。
②.主键索引：
可以认为是特殊的唯一索引，仅利用主键建立的索引
③.单一索引：
任何一个单一数据项建立的索引
假如有下表【country，city，personNumber】，如果我们想查询某个国家的人数，我们就应当以国家建立索引，这样单一数据项建立的索引就是单一索引。
④.复合索引：
多个数据项建立的索引
假如有下表【country，city，personNumber】，如果我们想查询某个城市的人数，我们就应当以【country，city】建立索引，多个数据项建立索引的时候，我们应当指定其排序的先后顺序，此处国家应优先排序，城市次之。
⑤.聚簇索引：
利用主键建立的索引，其物理存放顺序与主键顺序一致。因为数据只有一个物理存放顺序，所以一个表只有一个聚簇索引。
在MySQL中，选定主键之后将会自动为主键创建索引。该索引可以维护主键的唯一性。非叶子节点包含了主键值，而叶子节点则指向了一条完整的记录
⑥.非聚簇索引(二级索引，辅助索引)：除了聚簇索引之外，其余所有的索引都是非聚簇索引
非聚簇索引为什么是二级索引呢？重点在于一个二字。可以料想如果WHERE条件不是根据主键进行索引，那么我们就需要基于该非主键建立的索引进行检索，这样建立的索引叶子节点中包含了记录的主键，再使用主键在聚簇索引中找寻到完整的记录。可以说进行了两次B+ Tree查找而不是一次
⑦.覆盖索引：一个索引包含(覆盖)所要查询的字段的值，注意覆盖索引与具体的查询有关
假如有下表【country，city，personNumber】，如果我们想查询某个城市的人数，覆盖索引指的是可以将你想要查询的列建立在一个索引中，如（国家，城市，人数）作为一个复合索引，此时查找某一个国家的所有城市利用索引就可以完成，实际上这也相当于完成了聚簇索引的功能

数据库常用的数据结构：

1.Ⅰ. B+ Tree索引(MySQL，SQL Server，Oracle)

在这里插入图片描述

B+ Tree索引优点：

①.全值匹配：指的是和索引中所有列进行匹配。假设以(姓，名，出生日期)三个数据项建立复合索引，那么可以查找姓名为张三，出生日期在2000-12-12的人
②.匹配最左前缀：假设以(姓，名，出生日期)三个数据项建立复合索引，可以查找所有姓张的人
③.匹配列前缀：假设有姓为司徒，司马的人，我们也可以查找第一列的前缀部分，如查找所有以司开头的姓的人
④.匹配范围值：可以查找所有在李和张之间的姓的人，注意范围查询只在复合索引的优先排序的第一列。（假设姓名按照拼音排序）
⑤.精确匹配前面列并范围匹配后一列：可以查找姓李并出生日期在2000-12-12之后的人或姓名为张三并出生日期在2000-12-12之后的人，注意范围第一个范围查询后面的列无法再使用索引查询
⑥.只访问索引的查询：即查询只需访问索引，而无需访问数据行。（此时应想到索引中的覆盖索引）

B+ Tree索引缺点：
①.如果不是按照索引的最左列开始查找，则无法使用索引。如无法查找名为龙的人，也无法查找在2000-12-12之后出生的人，当然也无法查找姓中以龙结尾的人（注意为和含有的区别）
②.不能跳过索引中的列：无法查找姓李并在2000-12-12之后出生的人
③.如果查询中包括某个列的范围查询，则其右边所有列都无法使用索引优化查询

2.哈希索引(MySQL，Oracle)：

在这里插入图片描述
哈希索引优点：
①.快速查询：参与索引的字段只要进行Hash运算后就可以快速定位到该记录，时间复杂度约为1

哈希索引缺点：
①.哈希索引只包含哈希值和行指针，所以不能用索引中的值来避免读取行
②.哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序和范围查询
③.哈希索引不支持部分索引列查询，因为哈希索引始终是使用索引列的全部数据进行哈希计算
④.哈希索引只支持等值比较查询，如=，IN()，<=>操作
⑤.如果哈希冲突较多，一些索引的维护操作的代价也会更高

（九）事务

数据引擎主要的二种：InnoDB与MyISAM
下图是二者的对比：
在这里插入图片描述

1.事务的概念

事务就是「一组原子性的SQL查询」，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。
事务处理可以确保除只有本事务单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。
事务主要用于处理操作量大，复杂度高的数据，比如A给B转钱，A的账户扣除金额，B的账户增加金额，这一过程的中途不能发生一部分执行（A的账户扣除金额），一部分未执行（B的账户未增加金额），因此需要把上述的过程封装成一个事务。

案列分析：
用银行业务举个栗子，用户lemon有两银行卡，一张是招商银行CMBC的工资卡，另一张是工商银行ICBC的储蓄卡，每月5号发工资都要把招行卡的100万转到建设银行储蓄卡账户。
名称简写如下：

招商银行（CMBC）：“存么？白痴！”
中国工商银行（ICBC）： “爱存不存！”
中国建设银行（CCB）： “存？存不？”

这个转账的操作可以简化抽成一个事务，包含如下步骤：

查询CMBC账户的余额是否大于100万
从CMBC账户余额中减去100万
在ICBC账户余额中增加100万

以下语句对应创建了一个转账事务：

START TRANSACTION;
SELECT balance FROM CMBC WHERE username='lemon';
UPDATE CMBC SET balance = balance - 1000000.00 WHERE username = 'lemon';
UPDATE ICBC SET balance = balance + 1000000.00 WHERE username = 'lemon';
COMMIT;

2.事务的ACID特性是什么？

ACID其实是事务特性的英文首字母缩写，具体的含义是这样的：

原子性（atomicity)
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
一致性（consistency)
数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，CMBC账户中也不会损失100万，不然lemon要哭死因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。如果事务中一条语句执行失败，任何已经执行成功的语句也不会保存到数据库中。
隔离性（isolation)
通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时如果有其他人也准备给lemon的CMBC账户存钱，那他看到的CMBC账户里还是有100万的。
持久性（durability)
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且「不可能有能做到100%的持久性保证的策略」否则还需要备份做什么。

3.事务的实现方式：

原子性、一致性、持久性通过数据库的redo log和undo log来完成，redo log称为重做日志，用来保证事务的原子性和持久性，undo log称为回滚日志，用来保证事务的一致性。事务的隔离性通过锁机制来实现。

4.事务并发引发的问题：

当一个事务读取另一个事务修改的数据时，可能会出现脏读（dirty reads），不可重复读（non-repeatable reads）和幻读（phantom reads） 现象。因为读现象是和事务的隔离级别息息相关的，而InnoDB中事务的隔离级别有四个，由高到低分别是：串行读（SERIALIZABLE）、可重复读（REPEATABLE-READ）、读提交（READ-COMMITTED）和读未提交（READ-UNCOMMITTED）。

1.脏读
在事务A修改数据之后提交数据之前，这时另一个事务B来读取数据，如果不加控制，事务B读取到A修改过数据，之后A又对数据做了修改再提交，则B读到的数据是脏数据，此过程称为脏读Dirty Read。
在这里插入图片描述
2.不可重复读
一个事务内在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。
3.幻读：
事务A在按查询条件读取某个范围的记录时，事务B又在该范围内插入了新的满足条件的记录，当事务A再次按条件查询记录时，会产生新的满足条件的记录（幻行 Phantom Row）
在这里插入图片描述
不可重复读与幻读有什么区别？

不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）
幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的「记录数不一样」。（因为中间有其他事务提交了插入/删除）

对于事务的各种隔离级别，可能出现的读现象如下表：
在这里插入图片描述
简单做个总结：如果采用串行化隔离级别，不用担心出现任何读现象，但是这是以降低性能和并发性为代价的。

5.隔离级别（isolation level）

串行化（SERIALIZABLE）
串行化隔离级别是最高的隔离级别，它使用了最保守的锁策略。它阻止任何其他事务插入或更改此事务读取的数据，直到该事务完成。简单的来说，就是一个事务一个事务的来执行，显然性能会很低。在这种隔离级别下，一个事务中的相同查询可以反复执行，每次查询结果是一样的。从当前事务开始执行，任何更改另一个事务提交的数据的尝试都会导致当前事务等待（阻塞）。这是SQL标准指定的默认隔离级别（注意不是MySQL）。在实践中，这种严格程度是很少需要的。
重复读（REPEATABLE-READ）
这是MySQL的InnoDB引擎默认的隔离级别，它阻止查询的任何行被其他事务更改。因此，阻塞不可重复读，而不是幻读。也就是说在可重复读中，可能会出现幻读。重复读使用一种中等严格的锁定策略，以便事务中的所有查询都能看到来自相同快照(即事务启动时的数据)的数据。
当拥有该级别的事务执行 UPDATE … WHERE, DELETE … WHERE, SELECT … FOR UPDATE和LOCK IN SHARE MODE操作时，其他事务可能需要等待。
读提交（READ-COMMITTED）
事务无法看到来自其他事务的未提交数据，但可以看到当前事务启动后另一个事务提交的数据。当拥有这种级别的事务执行 UPDATE … WHERE or DELETE … WHERE操作时，其他事务可能需要等待。但是该事务可以执行 SELECT … FOR UPDATE, and LOCK IN SHARE MODE操作，其他事务不需要等待。
读未提交（READ-UNCOMMITTED）
它是最低的隔离级别。它会读取到其他事务修改尚未提交的数据，使用此隔离级别就需要非常小心，认识到这种级别下的查询结果可能不一致或不可复制，这取决于其他事务同时在做什么。通常，具有此隔离级别的事务只执行查询，而不执行插入、更新或删除操作。
在实际环境中，应当根据是否允许出现脏读（dirty reads），不可重复读（non-repeatable reads）和幻读（phantom reads）现象而选择相应的隔离级别。例如在大数据中，少量的数据不一致不会影响到最后的决策，这种情况下可以使用较低的隔离级别以提交性能和并发性。

在找bug的路上

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MYSQL】总结

1.介绍（一）数据库操作1.cmd进入mysql:mysql -uroot -p密码2.创建数据库：create database test character set 编码（'utf8'） collate 校对规则('utf8_bin') //test为数据库名3.展示数据库列表show databases; 4.选择数据库use rest_0716 // rest_0716为新建数据库名5.删除数据库drop database test_0716 //删除数据库“
复制链接

扫一扫

专栏目录