MYSQL学习，冲冲冲
数据库学习的重要性是：各个网站媒体依赖的数据存储在数据库内。
数据库：用来存储和管理数据的技术。
在研发岗内需要使用，在面试题内出现--重要性。
学习sql语言，使用mysql数据库，学习内容包括：事务，存储引擎，索引，sql优化，锁，日志，主从复制，读写分离，分库分表。
基础篇：mysql概述，sql，约束，函数，多表查询，事务。
进阶篇：存储引擎，索引，sql优化，视图/存储过程/触发器，锁，innoDB核心，mysql管理。
运维篇：日志，主从复制，分库分表，读写分离。
一.mysql概述
1.相关概念 :
| 数据库 | 存储数据的仓库，数据是有组织的进行存储 | DB |
| 数据库管理系统 | 操纵和管理数据库的大型软件 | DBMS |
| sql | 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 | SQL |
通过sql 可以操作数据库管理系统，然后通过数据库管理系统来操作数据库以及数据库中的数据。
2.主流的数据库管理系统：
oracle:收费贵
mysql:中小型，免费社区版本
sqlserver：收费
postersql：中小型免费
3.mysql的简单操作
通过以管理员身份运行操作的cmd操作框，可以进行如下操作，停止与开启mysql的运行
MySQL的客户端连接：
方法一：使用mysql提供的客户端命令行工具
方法二：系统自带的命令行工具执行指令
mysql -u root -p
注意：使用这种方法要配置好环境
4.数据模型
（套娃表）
1.关系型数据库：
1）概念：是指建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
2）特点：使用标存储数据，格式统一，便于维护
使用sql语言操作，标准统一，使用方便
2.数据模型：使用客户端连接DNMS,连接数据库，在数据库中可以存储多张表。
二.SQL
1.sql通用语法
1）sql语句可以单行或者多行书写，以分号为结尾
2）sql语句可以使用空格/缩进来增强语句的可读性
3）mysql数据库的SQL语句不区分大小写，关键字建议使用大写
4）注释：
单行注释：--注释内容或#注释内容
多行注释：/*注释内容*/
2.sql分类
| 分类 | 全称 | 说明 |
| DDL | data definition language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | data manipulation language | 数据操作语言，用来对数据库表中的数据进行增删改查 |
| DQL | data query language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | data control language | 数据控制语言，用来创建数据库用户，控制数据库的访问权限 |
3.DDL(数据定义语言
1）数据库操作 //方括号内可省略
1.查询
查询所有数据库：SHOW DATABASES;
查询当前数据库：SELECT DATABASES; //查询当前处于哪个数据库
2.创建
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
直接使用create database 数据库名； //会导致，若数据库重名报错。
使用create database if not exists; //若重名则忽略此条语句
3.删除
DROP DATABASES[IF EXISTS]数据库名；
4.使用
USE 数据库名；
2）表操作
1.查询
查询当前数据库所有表 SHOW TABLES;
查询表结构 DESC 表名；
查询指定表的链表结构：SHOW CREATE TABLES 表名；
2.创建
CREATE TABLE 表名(字段1 字段1类型 [COMMENT 字段1 注释],
字段2 字段2类型 [COMMENT 字段2 注释],
…………………………//最后一个没有逗号，注释可以省略
）[COMMENT 表注释];
3.数据类型
| 数据类型 | 描述 | 大小 |
|---|---|---|
| tinyint（0-255） | 十分小的数据 | 1个字节 |
| smallint（0-65535） | 较小的数据 | 2个字节 |
| mediumint（0-1677215） | 中等大小的数据 | 3个字节 |
| int | 标准的整数 | 4个字节 |
| bigint | 较大的数据 | 8个字节 |
| float | 浮点数 | 4个字节 |
| double | 浮点数 | 8个字节 |
| decimal | 字符串形式的浮点数，一般用于金融计算 |
| 数据类型 | 描述 | 大小 |
|---|---|---|
| char | 字符串固定大小//性别 | 0~255 |
| varchar | 可变字符串//更加灵活，姓名 | 0~65535 |
| tinytext | 微型文本 | 2^8-1 |
| text | 文本串 | 2^16-1 |
| 数据类型 | 描述 | 格式 |
|---|---|---|
| date | 日期格式 | YYYY-MM-DD |
| time | 时间格式 | HH：mm：ss |
| datetime | 最常用的时间格式 | YYYY-MM-DD HH：mm：ss |
| timestamp | 时间戳，1970.1.1到现在的毫秒数 | |
| year | 年份表示 |
4.修改
1）添加字段：
ALTER TABLE 表名 ADD 字段名 类型（长度)[COMMENT 注释][约束];
2)修改字段
1.修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度);
2.修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[comment 注释][约束];
3)删除字段
ALTER TABLE表名 DROP 字段名;
4）修改表名
ALTER TABLE 表名 RENAME TO 新表名；
5，删除
1）删除表：
DROP TABLE [IF EXISTS]表名；
2)删除指定表，并且重新创建该表：
TRUNCATE TABLE 表名；//相当于一键清空
总结：DDL语法主要有：
数据库操作：show databases;create database 数据库名；use 数据库名；select database();droop database 数据库名；
表操作： show tables;create table 表名（字段 字段类型 注释，字段 字段类型……）注释；desc 表名；show create table 表名；alter table 表名
add/modify/drop/rename to……‘drop table 表名；
4.DML（对数据库中表的数据记录进行增删改操作
1.添加数据
1）给指定字段添加数据
INSERT INTO 表名（字段1，字段2 ，，，）VALUES(值1，值2，，）；
2）给全部字段添加数据
INSERT INTO 表名 VALUE (值1，值2，，）；
3）批量增加数据
INSERT INTO 表名（字段1，字段2，，，）VALUE(值1，值2，，）；
INSERT INTO 表名 VALUE(值1，值2，，）（值1，值2，,);
注意：插入数据时，指定的字段顺序需要与值的顺序是一致的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。
2.修改数据（UPDATE)
UPDATE 表名 SET 字段名1=值1，字段名2=值2，，，[WHERE 条件]
3.删除数据 //与drop 的区别是drop操作对象是表，而delete操作对象是数据
DELETE FROM 表名[WHERE 条件]
( 注意：DELETE语句的条件可有可无，若没有则删除的是整张表的数据
DELETE语句不能删除某一个字段的值，可以用UPDATE）
5.DQL（执行查询数据库）关键字：select
语法：SELECT
字段列表 //可以用*来代替，但是不提倡
FROM
表名列表
WHERE
条件列表 //分组之前
GROUP BY
分组字段列表
HAVNG
分组后条件列表 //分组之后
ORDER BY
排序字段列表 //ASC DESC
LIMT
分页参数
1.基本查询
1）查询多个字段
SELECT 字段1，字段2，，，FROM 表名；
SELECT * FROM 表名；
2）设置别名
SELECT 字段1 [AS 别名1]，字段2[AS 别名2],,,FROM 表名；
3)去除重复记录
SELECT DISTINCT 字段列表 FROM 表名；
2.条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表；
1）运算符
比较运算符 注释
= 等于
> 大于
< 小于
<= 小于等于
>= 大于等于
!=或<> 不等于
IS NULL 判断一个值是否为NULL
IS NOT NULL 判断一个值是否不为 NULL
BETWEEN 12 AND 13 两者之间
IN 在集合中 where age in(a,b,c);
LIKE 通配符匹配，模糊查询 where id like '%x'//最后一个是x 或者 like'____________x'//前面十七个下划线代表十七个数字，不做要求
GREATEST 两个或多个参数时返回最大值
LEAST 两个或多个参数时返回最小值
逻辑运算符 功能
AND或&& 并且
OR或|| 或者
NOT或！ 非，不是
2）聚合函数
即纵向计算
常见聚合函数
count 统计数量 SELXCT COUNT(ID)EMP//统计emp表内的id列
max 最大值
min 最小值
avg 平均值 SELECT AVG(ID)EMP//统计平均值
sum 求和
注意：所有的NULL值不参与所有聚合函数运算
3）分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
注：WHERE与HAVING的区别：
执行时机不同：where在分组之前进行过滤，不满足where的不会参与分组，而having是分组之后对结果进行过滤
判断条件不通：where不能对聚合函数进行判断，而having可以。
例子：
根据性别分组，统计男性员工和女修员工的数量
SELSECT GENDER,COUNT (*)FREE EMP GROUP BY GENDER;
注意：
执行顺序：where》聚合函数》having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义
4)排序查询
语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2，，，：
排序方式：ASC:升序//可省略，省略的话就是升序
DESC：降序
注意：如果是多字段
5）分页查询
语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数；
注意：起始索引从0开始，起始索引=（页码查询-1）*每页显示记录数
分页查询是数据库的方言，不同数据库有不同的实现,mysql中是limit,
如果查询到是第一页的数据，起始索引可以省略，直接简写为limit 10.//select * from emp limit 0,10;==select * from emp limit 10;
6)DQL的执行顺序：
首先执行from ,通过from来决定，要查询的是哪一张表的数据，通过where来决定，查询的条件，第三步通过group by 和having来决定组织后的条件，第四步，决定查询返回哪些字段，执行select，第五步执行order by 和 limit.
6.DCL(控制数据库的访问权限）//主要时DBA 在使用
1.管理用户
1）查询用户
USE mysql;
SELECT * FROM USER;//主机名 :host
2)创建用户
CREATE USER '用户名'@‘主机名’ IDENTIFIED BY ‘密码’；//没有空格，写'%'在主机名时，，指任意主机都可以
3）修改用户密码
ALTER USER '用户名'@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’；
4）删除用户
DROP USER '用户名‘@'主机名'；
2.权限控制
1）查询权限
SHOW GRANT FOR '用户名'@'主机名';
2）授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3）撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
三，约束
1.概述
1.概念：约束时作用在表中字段上的规则，用于限制存储在表中的元素。
2.目的：保证数据库中数据的正确，有效性和完整性。
3.分类: 约束 描述 关键字
非空约束 限制该字段的数据不能为NULL NOT NULL
唯一约束 保证该字段的所有数据都是唯一的 UNIQUE
主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT //没有指定值，则使用默认值
检查约束 保证字段值满足一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY
注意：约束时作用在表中字段上的，可以创建表，修改表的时候添加约束
2.应用
1）非空约束，唯一约束：
create table useer(
name varchar(10) not null unque，//要求不为空且唯一
2）主键约束
id int primary key auto_increment comment '主键',//主键约束且自动增长
………………
）comment '用户表'；
3.外键约束
1.外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
具有外键的表称为子表，子表所关联的表称为父表。
CREATE TABLE 表名（
字段名 数据类型，
……
[CONSTRAINT][外键名称] FOREiGN KEY(外键字段名）REFERENCES 主表（主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FORIGN KEY (外键字段名）REFERENCES 主表（主表列名);
2.删除/更新行为
行为 说明
NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如有则不允许删除/更新
RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如有则不允许删除/更新
CASCADE 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如有，则页删除/更新在子表中的记录
SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如有则设置子表中该外键值为NULL
SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值
在添加外键时设定删除/更新行为
ALTER TABLE 表名 ADD CONSTRANT 外键名称 FOREIGN KEY （外键字段）REFERENCES 主表名（主表字段名) ON UPDATA CASCADE ON DELETE CASCADE;
四.函数
1.函数
定义：是指一段可以直接被另一程序调用的程序或者代码。
2.主要函数
1.字符串函数
函数 功能
CONCAT(S1,S2,S3,,) 字符串拼接，将S1,S2,,拼接成一个字符串
LOWER 将字符串全部转化为小写
UPPER 将字符串全部转化为大写
LPAD(str,n,pad) 左填充，用pad对str左边进行填充，达到n个字符串长度
RPAD(str,n,pad) 右填充，用pad对str右边进行填充，达到n个字符串长度//pad字符串可以很短，可以重复使用填充
TRIM(str) 去掉字符串头部与尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
2.数值函数
函数 功能
CEIL(X) 向上取整
FLOOR(X) 向下取整
MOD(X,Y) 返回x/y的模
RAND() 返回0-1内的随机数
ROUND(X,Y) 求参数x 的四舍五入的值，保留Y位小数
3.日期函数
函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(DATE) 获取指定DATE的年份
MONTH(DATE) 获取指定DATE的月份
DAY(DATE) 获取指定DATE的日期
DATE_ADD(DATE,INTERVAL EXPR TYOE) 返回一个日期/时间值加上一个时间间隔EXPR后的时间值
DATEDIFF(DATE1,DATE2) 返回起始时间DATE1和结束时间DATE2之间的天数
应用：查询所有员工的入职天数，并且根据入职天数倒叙排序
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
4.流程函数
实现条件筛选，提高流程效率
函数 功能
IF(VALUE,T,F) 如果value为true则返回t，否则返回f
IFNULL(VALUE1,VALUE2) 如果value不为空，返回value1,否则返回value2
CASE WHEN [VAL1] THEN[RES1].....ELSE[DEFAULT] END 如果val1为true,返回res1...否则返回default默认值
CASE [EXPR] WHEN [VAL1] THEN [RES1]...ELSE[DEFAULT] END 如果expr的值等于val1,返回res1....否则返回default，默认值
应用：查询emp表中的员工姓名和员工工作地址（北京/上海-->一线城市，其他--->二线城市）
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then ‘二线城市’ end) as '工作地址'
from emp;
五.多表查询
1.多表关系
各个表之间存在着各种各样的联系：一对多（多对一），多对多，一对一
实现：在多的一方建立外键，指向一的一方的主键
1）多对多
实现：建立第三张中间表，中间表至少包括两个外键，分别关联两方主键
2）一对一
多用于单表拆分，将基础的字段放在一张表中其他详情字段放在另一张表中。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的，从而保证一对一
2.多表查询概述
单表查询：select * from表名；
多表查询：select * from 表名1，表名2； //会有重合，数量为多个表内所有数据之和， 笛卡尔积
|
select* from 表1，表2 where 表1.列名=表2.列名；//去除冗余信息，关联信息的合并为一条
分类：1)连接查询：
3.内连接：相当于查询A，B交集部分数据。
隐式内连接：select 字段列表 from 表1，表2 where 条件...; //
例子： select emp.name , dept.name from emp,dept where emp.dept_id=dept.id;
显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件....; //
select emp.name, dept.nme from emp join dept on emp.dept_id = dept.id;
4.外连接：(左右是指建立连接时的方位，箭头出发点，指向点）
左外连接：查询左表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件....;
右外连接: 查询右表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
5.自连接：当前表与自身的连接查询，自连接必须使用表别名。
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;
起别名来建立新的联系，进行查询。
联合查询-union,union all:就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表A....条件
union [all] //union 可以去重，union all 直接合并，不去重
select 字段列表 from 表B...条件
注意：联合查询的列数必须相同
6.子查询：
概念：嵌套select语句，称为嵌套语句，又称子查询
select * from t1 where column1=(select column1 from t2);
子查询外部语句可以是insert,update ,delete.select 中的任何一个
根据查询结果不同：分为：
标量子查询（子查询结果为单个值,可以时单个值（数字，字符串，日期
常用操作符号：= <> > >= < <=
select id from emp where age=40;
列子查询（子查询结果为一列
常用操作符：in ,not in, any ,some, all
操作符 描述
in 在指定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回列表中，有任意一个满足即可
some 与any等同，使用some 的地方都可以使用any
all 子查询返回列表的所有值都必须满足
select id from dept where name ='suga' or name='agustd';
select id from dept where name in(select id from dept where id=4);
select * from dept where (salary>all(select salary from dept where dept_id=(select id from emp where name =''财务);
行子查询（子查询结果为一行，多列
常见操作符：=，<>,in,not in
select * from emp where (salary ,manage)=(select salary,managerid from emp where name ='张无忌');
表子查询（子查询结果为多列多行
常用操作符：in
根据子查询位置分为:where select from子查询
六.事务
1.事务简介
事务是一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。
2.事务操作
1）查看/设置事务提交方式
select @@autocommit;//为1 则为自动提交，
set @@ autocommit=0; //设置为手动提交
1.1）开启事务
start transaction 或 begin;
2）提交事务
commit;
3）回滚事务
rollback;
3.事务四大特性
A 原子性:事务时不可分割的最小操作单元，要么全部成功，要么全部失败
C 一致性：事务完成时，必须使所有的数据都保持一致状态
I 隔离性：数据库提供的个礼机制，保证事务在不受外部 并发操作影响的独立环境下运行
D 持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的
4.并发事务问题
问题 描述
脏读 一个事务读取到另一个事务还没有提交的数据
不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在输入数据时，又发现这行数据已经存在了
5.事务隔离级别：解决事务的并发问题
隔离级别 脏读 不可重复读 幻读` //可解决是0 ，不可解决是1
read unconmmitted 1 1 1 读，未提交
read committed 0 1 1 读，已提交
(默认）epeatable read 0 0 1 mysql的默认级别，可重复读
serializable 0 0 0 串行化，都可以解决，隔离级别最高，安全性最低
查看事务的隔离级别：
select @@transaction_isolation;
设置事务的隔离级别：
set [session gobal] transaction isolation level {read uncommitted| read committed|repeatable read|serializable}
标签：mysql,数据,数据库,使用,基础,查询,表名,where,select From： https://www.cnblogs.com/killjoyskr/p/17365945.html