第一部分 基础篇--------### 1.概述#### 1.1 启动与停止MySql* 启动 net start mysql80 * 停止 net stop mysql80 注意 mysql开机默认启动#### 1.2 客户端连接* 方法一使用MySQL提供的命令行客户端* 方法二系统自带的命令行工具执行指令mysql [-h 127.0.0.1] [-p 3306] -u root -p#### 1.3 CentOS下MYSQL的登录、启动、停止、重启命令##### MySql登录mysql -u root -p##### MySql配置文件/etc/my.cnf可配置端口3306sql-model等信息。##### 在CentOS根目录下执行命令###### 启动1、使用 service 启动service mysql startservice mysqld startsystemctl start mysqld.service2、使用 mysqld 脚本启动/etc/inint.d/mysql start3、使用 safe_mysqld 启动safe_mysql###### 停止1、使用 service 启动service mysql stopservice mysqld stopsystemctl stop mysqld.service2、使用 mysqld 脚本启动/etc/inint.d/mysql stop3、mysqladmin shutdown###### 重启1、使用 service 启动service mysql restartservice mysqld restartsystemctl restart mysqld.service2、使用 mysqld 脚本启动/etc/inint.d/mysql restart###### 状态查询1、使用 service 启动service mysql statusservice mysqld statussystemctl status mysqld.service### 2.SQL语句* 注释 单行注释用–或者#多行注释用/* */* SQL语句一般以分号结尾* MySql数据库的SQL语句不区分大小写* SQL语句分类 * DDL Data DefinitionLanguage 数据定义语言用来定义数据库对象(数据库表 字段) * DMLData Manipulation Language 数据操作语言用来对数据库表中的数据进行增删改 * DQL Data Query Language 数据查询语言用来查询数据库中表的记录 * DCL Data Control Language 数据控制语言用来创建数据库用户、控制数据库的 访问权限。#### 2.1 DDL 用来定义数据库对象(数据库表 字段)##### 2.1.1 DDL-数据库操作##### 2.1.2 DDL-表-操作##### 2.1.3 DDL-表-增删修改###### 对字段进行操作* 增加字段ALTER TABLE 表名 ADD 字段名 类型长度 【comment 注释】【约束】* 修改数据类型ALTER TABLE 表名 MODIFY 字段名 新的类型长度* 修改字段名和数据类型ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型长度* 删除字段ALTER TABLE 表名 DROP 字段名###### 对表进行操作* 修改表名ALTER TABLE 表名 RENAME TO 新表名* 删除指定表DROP TABLE [IF EXISTS] 表名* 删除指定表并且重新创建该表表的数据内容会被清除TRUNCATE TABLE 表名#### 2.2 DML 用来对数据库表中的数据进行增删改##### 2.2.1 DML-添加数据注意* 插入的值应该和字段顺序一致* 插入的字符串和日期数据应该要用引号##### 2.2.2 DML-修改数据UPDATE 表名 SET 字段名1值1字段名2值2……[WHERE 条件]注意* 没有加where条件默认修改整张表上该字段名的所有值##### 2.2.3 DML-删除数据DELETE FROM 表名 [WHERE 条件]注意* 没有加where条件默认删除整张表上的所有数据* delete语句不能删除某一个字段的值可以用update语句将该字段修改为null#### 2.3 DQL 用来查询数据库中表的记录##### 2.3.1 DQL-基础查询##### 2.3.2 DQL-条件查询注意* between …and… 需要小的数字放在前面大的数字放在后面##### 2.3.3 DQL-聚合函数注意* 所有的null值不参与聚合函数的计算##### 2.3.4 DQL-分组查询注意* having 指分组之后在过滤的条件* 执行顺序where聚合函数having* 分组之后查询的字段一般为聚合函数或分组字段查询其他字段无任何意义##### 2.3.5 DQL-排序查询注意* 如果按多个字段查询当第一个字段的值相同时按照第二个字段排序##### 2.3.5 DQL-分页查询SELECT 字段列表 FROM 表名 LIMIT 起始索引,分页记录数注意* 起始索引从0开始起始索引查询页码-1*每页显示记录数* 分页查询是数据库的方言不同数据库有不同的实现MySql中使用limit实现* 如果起始索引是0可以省略不写例如limit 10;##### 2.3.6 DQL-执行顺序* 编写顺序* 执行顺序#### 2.4 DCL 用来创建数据库用户、控制数据库的访问权限##### 2.4.1 DCL-用户管理注意* 主机名可以用%通配表示任意地址都可以* 这类SQL开发人员操作的较少主要是DBADatabase Administrator数据库管理人员使用##### 2.4.2 DCL-权限控制注意* 多个权限要用逗号分割* 授权时数据库名和表名可以使用*通配表示所有### 3.函数#### 3.1 字符串函数MySql有很多内置字符串函数#### 3.2 数值函数#### 3.3 日期函数#### 3.4 流程控制函数### 4.约束#### 4.1 约束-概述* 概念约束是作用于表中字段上的规则用于限制存储在表中的数据* 目的保证数据库中数据的正确有效性和完整性* 分类约束描述关键字非空约束限制该字段的数据不能为nullNOT FULL唯一约束保证该字段的所有数据都是唯一的不重复的UNIQUE主键约束主键是一行数据的唯一标识要求非空且唯一PRIMARY KEY默认约束保存数据时如果未指定该字段的值则采用默认值DEFAULT检查约束保证字段值满足某一个条件CHECK外键约束用来让两张表的数据之间建立连接保证数据的一致性和完整性FOREIGEN KEY注意约束是作用于表中字段的可以在创建表和修改表是增加#### 4.2增加约束##### 4.2.1 创建表时增加约束-案例分析create table teachers( id int primary key auto_increment comment ‘id’, name varchar(10) not null unique comment ‘姓名’, age int check(age0 age 120) comment ‘年龄’, status char(1) default ‘1’ comment ‘状态’, gender char(1) comment ‘性别’ )comment ‘驾驶表’; ##### 4.2.2 对已有的表增加约束* 增加主键/唯一约束ALTER TABLE 表名 ADD PRIMARY KEY(字段名) / UNIQUE(字段名)* 增加非空/自动增长/默认约束(修改字段的数据类型)ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL/AUTO_INCREMENT/DEFAULT 默认值* 增加检查约束ALTER TABLE 表名 ADD CONSTRIANT 字段名 check条件#### 4.3 外键约束##### 4.3.1 增加外键* 创建表时增加外键* 对已经创建过的表增加外键ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表主表列名##### 4.3.2 删除外键ALTER TABLE 表名 DROP CONSTRAINT 外键名称##### 4.3.3 外键删除和更新规则行为ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表主表列名ON DELETE 规则 ON UPDATE 规则### 5.多表查询#### 5.1 多表查询-概述##### 5.1.1 多表关系分类##### 5.1.2 多表查询-分类* 连接查询 * 内连接查询两表交集部分数据 * 外连接 * 左外连接查询左表所有数据以及两表交集部分数据 * 右外连接查询右表所有数据以及两表交集部分数据 * 自连接 当前表与自身连接查询自连接必须使用别名* 子查询#### 5.2 多表查询-连接查询##### 5.2.1 连接查询-内连接内连接查询语法* 隐式内连接SELECT 字段列表 FROM 表一表二 WHERE 条件* 显示内连接SELECT 字段列表 FROM 表一 [INNER] JOIN 表二 ON 连接条件##### 5.2.2 连接查询-外连接##### 5.2.3 连接查询-自连接#### 5.3 联合查询注意* 对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致* union all 会将所有的数据直接合并在一起union 会对合并之后的数据去重#### 5.4 子查询##### 5.4.1 标量子查询子查询返回的结果是单个值数字字符串日期等最简单的形式 常用的操作符 * 案例一* 案例二##### 5.4.2 列子查询子查询返回的结果是一列可以是多行 常用的操作符 IN、NOT、ANY、SOME、ALL* 案例一* 案例二* 案例三##### 5.4.3 行子查询子查询返回的结果是一行可以是多列 常用的操作符 、、IN、NOT IN* 案例一##### 5.4.4 表子查询子查询返回的结果是多行多列 常用的操作符 IN* 案例一* 案例二注意* 如果返回的子表需要作为一张新的表被查询要给这张子表起别名否则会Every derived table must have its own alias报错。### 6.事务#### 6.1 事务简介事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败* 开启事务* 如果执行没有异常则提交事务* 如果执行中出现异常回滚事务退到执行事务前。* 默认MySQL的事务是自动提交的也就是说当执行一条DML语句MySQL会立即隐式地提交事务。##### 事务四大特性ACID#### 6.2 事务操作* 方式一* 方式二#### 6.3 并发事务问题* 脏读* 不可重复读* 幻读#### 6.4 事务的隔离级别* 隔离级别越高数据越安全执行效率越低。第二部分 进阶篇--------### 1.存储引擎#### 1.1 MySql体系结构#### 1.2 存储引擎* 存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的所以存储引擎又称为表类型* MySql默认存储引擎为InnoDB#### 1.3 存储引擎的特点##### 1.3.1 InnoDB##### 1.3.2 MyISAM##### 1.3.3 Memory##### 1.3.4 区别##### 1.3.5 存储引擎的选择### 2.索引#### 2.1 索引-概述* 索引是帮助MySQL高效获取数据的数据结构有序。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找方法这种数据结构就是索引。* 优缺点#### 2.2 索引结构##### 2.2.1 BTree索引##### 2.2.2 Hash索引#### 2.3 索引分类* 回表查询先通过二级索引找到主键然后通过主键#### 2.4 索引-语法* 案例#### 2.5 索引-性能分析##### 2.5.1 SQL执行频率7个下划线##### 2.5.2 慢查询日志慢查询日志需要手动打开方式二进入MySQL控制台用命令行设置mysql -u root -p#查看一下默认为慢查询的时间10秒 show variables like “%long%”; #设置成2秒 set global long_query_time2; #查看一下慢查询是不是已经开启 show variables like “%slow%”; #启用慢查询 set global slow_query_log‘ON’; /usr/local/mysql/data/localhost-slow.log##### 2.5.3 show profilesshow profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数能够看到当前MySQL是否支持profile操作SELECT have_profiling ;可以通过set语句在session/global级别开启profilingset profiling 1;开关打开后接下来我们所执行的SQL语句都会被MySQL记录并记录执行时间消耗到哪儿去了。 #查看每一条SQL的耗时基本情况 show profiles; #查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id; ##### 2.5.4 explain执行计划EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 语法(直接在select语句前面加上关键字explain/desc)EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;Explain 执行计划中各个字段的含义:字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。possible_key显示可能应用在这张表上的索引一个或多个。key实际使用的索引如果为NULL则没有使用索引。key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好 。rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。