MYSQL相关知识汇总-原创手记-慕课网

一、数据库基本概念

数据：
数据是事实或观察的结果，它是对客观事物的逻辑归纳，是信息的表现形式和载体，可以是符号、文字、数字、语音、图像、视频等。

数据库：
数据库是按照数据结构来组织、存储和管理数据的仓库。
数据库是长期储存在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

数据库分类：
根据数据模型的不同，数据库通常被分为层次数据库、网状数据库和关系数据库。
对数据库的划分常见的还有另外一种，即关系型数据库和非关系型数据库。像我们经常听说的Redis、MongoDB就属于非关系型数据库。

RDBMS的特点：
RDBMS是Relational Database Management System的简称，即关系型数据库管理系统

数据以表格的形式出现
每行为一条记录
每列为记录名称所对应的数据域（Field）
许多的行和列组成一张单表（Table）
若干单表组成数据库（Database）
查询方式：关系型数据库采用结构化查询语言（即SQL）来对数据库进行查询
事务性：关系型数据库强调ACID规则，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability））
读写性能：关系型数据库十分强调数据的一致性，并为此降低读写性能付出了巨大的代价，在面对海量数量处理、高并发数据读写等场景时性能下降的非常厉害

主流的RDBMS：
SQL Server
• 微软推出的DBMS（最初由Microsoft、Sybase和Ashton-Tate共同研发）
• 运行于Windows NT平台
Sybase
• Sybase公司
• 可以运行于Windows NT、Unix/Linux平台
DB2
• IBM公司
• 主要运行于Unix（包括IBM的AIX）、Linux和Windows
Oracle
• Oracle公司
• 世界第一个支持SQL语言的商业数据库，应用广泛，功能强大
MySQL
• 瑞典MySQL AB公司开发，08年被SUN收购，09年SUN被Oracle收购
• 体积小，速度快，开源免费；最流行的关系型数据库管理系统

二、数据库的基本操作 DDL

SQL简介：
SQL是Structured Query Language的缩写，即结构化查询语言。SQL是一门标准的计算机语言，用于访问和操作数据库，其主能包括数据定义、数据操纵、数据查询和数据控制。

SQL分类：
1.DDL
数据定义语言 Data Definition Language
DDL用于数据库、表、视图等的建立、删除。
DDL包括CREATE、ALTER、DROP等。
2.DML
数据操纵语言 Data Manipulation Language
DML用于添加、删除和修改数据表中的记录。
DML包括INSERT、DELETE和UPDATE。
3.DCL
数据控制语言 Data Control Language
DCL包括数据库对象的权限管理和事务管理。
DCL包括COMMIT、ROLLBACK、GRANT等。
4.DQL
数据查询语言 Data Query Language
查询是数据库的基本功能。
DQL中使用SELECT查询数据表中的记录。

DDL：
#创建数据库
CREATE DATABASE 数据库名
#删除数据库
DROP DATABASE 数据库名

三、数据查询 DQL

官网：https://dev.mysql.com/doc/refman/8.0/en/select.html

select语法：
select 字段一,字段二
from 表名
where 条件一 and ( 条件二 or 条件三）
group by 分组依据一,分组依据二
having 分组后筛选条件
order by 排序字段 [asc|desc]
limit 开始行,结束行

四、索引的使用慢查询日志

mysql日志类型：

日志	描述
重做日志(redo log)	重做日志是一种物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。
回滚日志(undo log)	回滚日志是一种逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。
二进制日志(binlog)	二进制日志是一种逻辑格式的日志，以二进制文件的形式记录了数据库中的操作，但不记录查询语句。
错误日志(errorlog)	错误日志记录着mysqld启动和停止，以及服务器在运行过程中发生的错误的相关信息。
慢查询日志(slow query log)	慢查询日志记录执行时间过长和没有使用索引的查询语句。
一般查询日志(general log)	记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log都会将其记录下来。
中继日志(relay log)	中继日志类似二进制；可用于复制架构中，使从服务器和主服务器的数据保持一致。

慢查询日志:

参数	描述
slow_query_log	是否开启慢查询日志，1表示开启，0表示关闭。
slow_query_log_file	慢查询日志存储路径，可选。注意：MySQL 5.6之前的版本，参数名为 log-slow-queries
long_query_time	阈值，当SQL语句的响应时间超过该阈值就会被记录到日志中。
log_queries_not_using_indexes	未使用索引的查询也被记录到慢查询日志中，可选。
log_output	日志存储方式，默认为FILE。log_output=‘FILE’表示将日志存入文件 log_output=‘TABLE’表示将日志存入数据库log_output=‘FILE,TABLE’表示同时将日志存入文件和数据库

慢查询日志命令：
#查看是否开启慢查询日志
show variables like ‘slow%’;
#临时开启慢查询日志
set slow_query_log=‘ON’;
set long_query_time=1;
#慢查询日志文件所在位置
show variables like ‘%datadir%’;

五、存储过程

#存储过程示例：
根据用户id和邮件内容content给用户发邮件

 
delimiter //
create procedure send_email(in user_id int, in content text)
begin
/* 根据用户id查询邮箱email */
set @user_email=(select email from user_info where id=user_id);
/* 模拟发送邮件 */
insert into email_info(email, content, send_time) values(@user_email, content, now());
end;
//
delimiter ;
call send_email(1, '欢迎加入MySQL阵营！');

六、Mysql安装与配置

流行的框架组合：LAMP(Linux+Apache+MySQL+PHP)

MySQL的官网是：
www.mysql.com，在下载界面可以看到两种版本选择： Community和Enterprise，即社区版和企业版。

MySQL社区版的下载地址：
http://dev.mysql.com/downloads/mysql/

MySQL最新版本8.0.18的下载地址：
https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.18.0.msi

启停MySQL服务：
启动mysql服务：net start mysql80
停止mysql服务：net stop mysql80

配置环境变量（非必须）：
将MySQL安装路径下的 MySQL Server 8.0\bin 添加到环境变量path中。如果采用默认安装，添加到环境变量path中的完整路径 C:\Program Files\MySQL\MySQL Server 8.0\bin

连接到MySQL：
#连接到本机上的MySQL服务器：mysql –h localhost –u root -p
连接到MySQL数据库之后，可以使用status命令或version()函数查看MySQL版本信息。

七、数据库表的操作 DDL

MySQL存储引擎:

存储引擎	描述
MyISAM	拥有较快的插入、查询速度，但不支持事务
InnoDB	支持ACID事务，支持行级锁，支持外键；MySQL 5.5版本后的默认存储引擎
MRG_MYISAM	将一组结构相同的MyISAM表聚合成一个整体，再进行增删改查操作
Memory	所有数据存储在内存中，响应快；MySQL重启时数据会全部丢失
Archive	归档，且有压缩机制，适用于历史数据归档
CSV	逻辑上由逗号分隔数据，会为每张表创建一个.csv文件

#MySQL的建表语法
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition,…)] [table_options]

[select_statement]说明：
TEMPORARY：表示创建临时表，在当前会话结束后将自动消失
IF NOT EXISTS：在建表前，先判断表是否存在，只有该表不存在时才创建
create_definition：建表语句的关键部分，用于定义表中各列的属性
table_options：表的配置选项，例如：表的默认存储引擎、字符集
select_statement：通过select语句建表
#添加字段sex，类型为VARCHAR(1)
ALTER TABLE contacts ADD sex VARCHAR(1);
#修改字段sex的类型为tinyint
ALTER TABLE contacts MODIFY sex tinyint;
#删除字段sex
ALTER TABLE contacts DROP COLUMN sex;
#删除contacts表
DROP TABLE contacts;

八、数据库表的操作 DML

插入数据：
INSERT 插入单条数据：
INSERT INTO table_name (field1, field2, …, fieldN) VALUES (value1, value2, …, valueN);
INSERT 插入多条数据：
INSERT INTO table_name (field1, field2, …, fieldN) VALUES (valueA1, valueA2, …, valueAN), (valueB1,
valueB2, …, valueBN), …, (valueN1, valueN2, …, valueNN);
注意事项：
1、如果字段是字符型，值必须使用单引号或者双引号，如”value”;如果值本身带单引号或双引号，需要转义
2、如果所有列都要添加数据，insert into语句可以不指定列，即
INSERT INTO table_name VALUES (value1, value2, …, valueN);

修改数据：
update语法：
UPDATE table_name SET field1=newValue1, field2=newValue2 [WHERE Clause]
注意事项：
1、可以同时更新一个或多个字段
2、可以通过where子句来指定更新的范围，如果不带where，则更新数据表中的所有记录

删除数据：
delete语法：
DELETE FROM table_name [WHERE Clause]
注意事项：
1、可以通过where子句来指定删除的范围，如果不带where，则删除数据表中的所有记录

九、数据完整性

数据完整性：
数据完整性是指存储在数据库中的数据，应该保持一致性和可靠性。
关系模型允许定义三类数据约束，它们是实体完整性、参照完整性以及用户定义的完整性约束，其中前两种完整性约束由关系数据库系统自动支持。

实体完整性：
实体完整性要求每张表都有唯一标识符，每张表中的主键字段不能为空且不能重复。
约束方法：唯一性约束、主键约束、标识列

域完整性：
域完整性是针对某一具体关系数据库的约束条件，它保证表中某些列不能输入无效的值。
域完整性指列的值域的完整性，如数据类型、格式、值域范围、是否允许空值等。
约束方法：限制数据类型、检查约束、默认值、非空约束

参照完整性：
参照完整性要求关系中不允许引用不存在的实体。
约束方法：外键约束

用户自定义完整性：
用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
约束方法：规则、存储过程、触发器

唯一性约束：
在MySQL中，可以使用关键字 UNIQUE 实现字段的唯一性约束，从而保证实体的完整性。
UNIQUE 意味着任何两条数据的同一个字段不能有相同值。
一个表中可以有多个 UNIQUE 约束。
#在创建表时添加唯一性约束
create table person(
id int not null auto_increment primary key comment ‘主键id’,
name varchar(30) comment ‘姓名’,
id_number varchar(18) unique comment ‘身份证号’
);

外键约束：
外键（FOREIGN KEY）约束定义了表之间的一致性关系，用于强制参照完整性。
外键约束定义了对同一个表或其他表的列的引用，这些列具有PRIMARY KEY或UNIQUE约束。
#学生表
create table stu(
stu_no int not null primary key comment ‘学号’,
stu_name varchar(30) comment ‘姓名’
);
#成绩表
create table sc(
id int not null auto_increment primary key comment ‘主键id’,
stu_no int not null comment ‘学号’,
course varchar(30) comment ‘课程’,
grade int comment ‘成绩’,
foreign key(stu_no) references stu(stu_no)
);

十、条件查询

在SQL中，insert、update、delete和select后面都能带where子句，用于插入、修改、删除或查询指定条件的记录。

#SQL语句中使用where子句语法
SELECT column_name FROM table_name WHERE column_name 运算符 value

运算符	描述
=	等于
<>或！=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between and	相当于>=并且<=

在where子句中，使用and、or可以把两个或多个过滤条件结合起来。
#and、or运算符语法
SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3

运算符	描述
and	表示左右两边的条件同时成立
or	表示左右两边只要有一个条件成立

十、DQL IN\ LIKE

运算符 IN 允许我们在 WHERE 子句中过滤某个字段的多个值。
#where子句使用in语法
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)

在where子句中，有时候我们需要查询包含xxx 字符串的所有记录，这时就需要用到运算符like。
#where子句使用like语法
SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’
说明：
1、LIKE子句中的%类似于正则表达式中的*，匹配任意0个或多个字符
2、LIKE子句中的_匹配任意单个字符
3、LIKE子句中如果没有%和_，就相当于运算符=的效果

十一、函数

函数：
我们通常说的MySQL函数指的是MySQL数据库提供的内置函数，包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等，这些内置函数可以帮助用户更方便地处理表中的数据，简化用户的操作。

函数	描述
数学函数	如ABS、SQRT、MOD、SIN、COS、TAN、COT等
字符串函数	如LENGTH、LOWER、UPPER、TRIM、SUBSTRING等
日期和时间函数	如NOW、CURDATE、CURTIME、SYSDATE、DATE_FORMAT、YEAR、MONTH、WEEK等
聚合函数	COUNT、SUM、AVG、MIN、MAX
条件判断函数	IF、IFNULL、CASE WHEN等
系统信息函数	VERSION、DATABASE、USER等
加密函数	MD5、SHA1、SHA2等

函数now()：
函数now()用于返回当前的日期和时间。
应用场景：
在实际应用中，大多数业务表都会带一个创建时间create_time字段，用于记录每一条数据的产生时间。在向表中插入数据时，就可以在insert语句中使用now()函数。
示例如下：
insert into user(id, name, create_time) values(1, ‘zhangsan’, now());

函数date_format()：
函数date_format()用于以指定的格式显示日期/时间。
应用场景：
在实际应用中，一般会按照标准格式存储日期/时间，如 2019-12-13 14:15:16 。在查询使用数据时，往往又会有不同的格式要求，这时就需要使用date_format()函数进行格式转换。
示例如下：
select name, date_format(birthday, ‘%Y/%m/%d’) from user;

聚合函数：
聚合函数是对一组值进行计算，并返回单个值。
MySQL常用的聚合函数有5个，分别是count（记录总数）、sum（指定列的总和）、avg（指定列的平均值）、min（指定列的最小值）和max（指定列的最大值）。

函数ifnull()：
函数ifnull()用于处理NULL值。
ifnull(v1,v2)，如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

case when：
case when是流程控制语句，可以在SQL语句中使用case when来获取更加准确和直接的结果。
SQL中的case when类似于编程语言中的if else或者switch。
#case when的语法有2种
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
CASE WHEN [expr] THEN [result1]…ELSE [default] END

十二、排序与分页

order by :
在SQL中，使用order by对查询结果集进行排序，可以按照一列或多列进行排序。
#order by语法
SELECT column_name1, column_name2
FROM table_name1, table_name2
ORDER BY column_name, column_name [ASC|DESC]
说明：

ASC表示按升序排列，DESC表示按降序排列。
默认情况下，对列按升序排列。

limit的使用:
在SELECT语句中使用LIMIT子句来约束要返回的记录数，通常使用LIMIT实现分页。
#limit语法
SELECT column_name1, column_name2
FROM table_name1, table_name2
LIMIT [offset,] row_count
说明：

offset指定要返回的第一行的偏移量。第一行的偏移量是0，而不是1。
row_count指定要返回的最大行数。
【经验分享】limit的分页公式：
limit (page-1)*row_count, row_count

十三、GROUP BY与HAVING

GROUP BY：
从字面上理解，group by表示根据某种规则对数据进行分组，它必须配合聚合函数进行使用，对数据进行分组后可以进行count、sum、avg、max和min等运算。
#group by语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
说明：

aggregate_function表示聚合函数。
group by可以对一列或多列进行分组。

HAVING：
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以对分组后的各组数据进行筛选。
#having语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

十四、表连接（内连接、外连接、自连接）

表连接：
表连接（JOIN）是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。
#表连接语法
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

图片描述
交叉连接（cross join）：没有用where子句的交叉连接将产生笛卡尔积，第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小

十五、查询分析器EXPLAIN

explain简介：
explain命令可以查看SQL语句的执行计划。当explain与SQL语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括有关如何联接表以及以何种顺序联接表的信息。

explain功能：

分析出表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

explain的使用：
explain的使用很简单，只需要在SQL语句之前加上explain命令即可，除select语句外，explain也能分析insert、update和delete语句。

explain结果解析：
图片描述

十六、索引的基本使用

索引：
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定记录。
数据库的索引好比一本书的目录，能够加快数据库的查询速度；
索引是快速搜索的关键，如果不加索引，查找任何一条特定的数据都会进行一次全表扫描。

索引的使用：
1、创建索引
#创建普通索引
CREATE INDEX indexName ON tableName(columnName(length));
#创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName(length));
#创建复合索引
CREATE INDEX indexName ON tableName(columnName1, columnName2, …);
2、删除索引
DROP INDEX [indexName] ON tableName;
3、查看索引
SHOW INDEX FROM tableName;

实战经验：

1.选择区分度高的列建立索引：
区分度计算公式：count(distinct col)/count(*)，它表示字段不重复的比例
2.避免对索引列进行计算：
例如：
from_unixtime(create_time)='2014-05-29’
不会用到索引
3.每次查询每张表仅能使用一个索引