一、数据库基本概念

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

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

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

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

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

主流的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日志类型：

慢查询日志:

慢查询日志命令：
#查看是否开启慢查询日志
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存储引擎:

#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

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

十、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数据库提供的内置函数，包括数学函数、字符串函数、日期和时间函数、聚合函数、条件判断函数等，这些内置函数可以帮助用户更方便地处理表中的数据，简化用户的操作。

函数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]
说明：

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

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

1. offset指定要返回的第一行的偏移量。第一行的偏移量是0，而不是1。
2. 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
说明：

1. aggregate_function表示聚合函数。
2. 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功能：

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

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.每次查询每张表仅能使用一个索引