mysql

数据库

Database Management System
简称DBMS
包括三大部分构成：
1 数据库文件集合，
主要是一系列的数据文件，作用是存储数据
2 数据库服务端，
主要负责对数据文件以及文件中的数据进行管理。
3 数据库客户端，
负责和服务端通信，向服务端能传输数据或者从服务端获取数据

Relational Database Management System
关系型数据库，是建立在关系型模型基础上的数据库，借助集合代数等数学概念和方法来处理数据库中的数据，
本质上使用一张二维表来表示关系。

innodb 和myisam区别

1、InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高
级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM
就不可以了；
2、MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到
安全性较高的应用；
3、InnoDB 支持外键，MyISAM 不支持；
4、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM
表中可以和其他字段一起建立联合索引；
5、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重
建表；

安装

linux

windows

实现数据的完整性

数据类型

存储在数据库中的所有数据值均正确的状态，如果有不正确的值，则该数据库已经丧失完整性
影响着数据中存储的数据所占的对数据进行最基本的类型约束，空间大小
只有符合类型要求的数据才能存储起来，这就是对数据完整性的一种要求
使用数据类型原则：够用就行，尽量取值范围小，这样可以节省存储空间。

数据约束

备注

配置好MySQL数据库服务后，默认创建4个库
1 information_schema 记录用户、表、视图等元数据信息，是一个虚拟出来的库，其对象都保存在内存中，没有对应的物理存在，用户无法再该库下创建对象，root都不行，唯一能做的就只有查询，
2 mysql 记录用户权限、帮助、日记等信息
3 performance_schema MySQL服务性能指标库，5.5引入
4 sys

show

对结构的操作

对内容操作

INSERT INTO 表名（列1，列2...）values(值1，值2....); (给表里面添加数据)

1、insert into 开始语句，
2、前后的列名和后面的 values 里面添加的数据要一一相符
3、values 里面的数据除了 int 以外要加单引号。

DELETE规则：
delete 不能删除单一列中的值或者表中某一列的所有值
delete 可用删除一行或多行，根据 where 子句而定

查

SELECT

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
LIMIT 属性来设定返回的记录数
OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

where条件

select * from 表名 where 条件;

排序

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

分页

select * from 表名 limit start=0,count；

当排序经过数据分析之后，根据关联度和点击量等属性排序后，所有的数据的大小对于用户来讲是个天文数字并且用户也不一定需要这么大量的数据，所以这个时候就有一个想法能不能把这么多数据分成一页一页的数据，而 根据用户的需要将数据分为一页一页地传输给用户的技术就是分页

推导公式：
已知：每页显示m条数据，当前显示第n页
求总页数：此段逻辑后面会在python项目中实现
查询总条数p1
使用p1除以m得到p2
如果整除则p2为总数页
如果不整除则p2+1为总页数
获取第n页的数据的SQL语句求解思路
第n页前有n-1页
所在第n页前已经显示的数据的总量是(n-1)m
由于数据的下标从0开始 所以第n页前所有的网页的下标是0,1,...,(n-1)m-1
所以第n页的数据起始下标是(n-1)m
获取第n页数据的SQL语句
select * from students where is_delete=0 limit (n-1)m,m
注意：在sql语句中limit后不可以直接加公式

聚合函数/组函数

聚合函数aggregation function又称为组函数。
默认情况下 聚合函数会对当前所在表当做一个组进行统计。
特点
每个组函数接收一个参数（字段名或者表达式）
统计结果中默认忽略字段为NULL的记录 要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。
不允许出现嵌套 比如sum(max(xx))

分组

将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

group by + with rollup 在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。


使用 coalesce 来设置一个可以取代 NUll 的名称

group by 关键字能根据1个或多个字段对数据进行分组
group_concat函数作用就是将每个分组中的每个成员的指定个字段拼接在一行中显示
聚合函数在和 group by 结合使用时, 统计的对象是每个分组
having 是对分组结果进行条件过滤
with rollup在分组结果最后新增一行完成汇总显示。

连接

select * from 表1 inner/left/right join 表2 on 两个表之间的限定条件
将多张表连接成一个大的数据集进行汇总显示。
一个表需要和另一个进行连接

子查询

完整语句

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [JOIN 表名]
      [ON 连接条件] 
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

合并表实例

1 查询每种类型中最贵的电脑信息。

select cate_name,max(price) as max_price from goods group by cate_name;
通过分组查询类型找出最高价格，要找出对应的信息，就得价格对应，类型对应，不然可能会混乱。



关联查询 inner join 每种类型中最贵的物品信息
 select * from goods 

-- inner join

-- (select cate_name,max(price) as max_price from goods group by cate_name) as max_price_goods

# 注意别名在后面的作用。

-- on 

goods.cate_name=max_price_goods.cate_name 
and goods.price=max_price_goods.max_price;

2 将A表的商品信息换成B表的指代值
把一个表的信息更新到另一个表

第一步	创建表 (商品种类表 goods_cates )

create table if not exists goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);

#如果不存在就 创建


第二步	同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中

-- 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)
select distinct cate_name from goods;

(注意)当我们的插入的数据是一个sql语句查询出来的 就不需要使用values
insert into goods_cates(name) (select distinct cate_name from goods);
#因为id是自增的，就不用写了
完成表信息的录入

第三部 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表

-- 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
select * from goods inner join goods_cates on goods.cate_name=goods_cates.name;

-- 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
update 
(goods inner join goods_cates ongoods.cate_name=goods_cates.name)
 set goods.cate_name=goods_cates.id;

第四部 修改表结构

-- 查看表结构(注意 两个表中的 外键类型需要一致)


-- 修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

3 在创建表的时候就插入数据

null值处理

正则 regexp

外键 foreign key

create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

视图 view

索引 index

临时表

复制表

Select 语句完整的执行顺序

事务 transaction

与python交互

SQLAlchemy

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import  Column,Integer,String
from sqlalchemy.orm import sessionmaker

#相当于mysql的connect
engine =create_engine( "mysql+pymysql://yy:123456@localhost/jing_dong",
                       encoding='utf-8',
                       echo=True)  
#在写第一段参数的时候不要加空格,不然正则解析出错
echo为真则在控制栏显示工作流程。


Base = declarative_base() 
#生成orms基类

class User(Base):
    __tablename__='user'  #表名
    id = Column(Integer,primary_key=True)
    name = Column(String(32))
    password = Column(String(64))

Base.metadata.create_all(engine)  #创建表结构

#创建与数据库的会话session  class，这里返回的是一个类，不是实例
Session_class = sessionmaker(bind=engine)

#生成session实例  #类似pymysql中的cursor
Session = Session_class()

user_obj1= User(name='yy',password='123456')#生成你要创建的数据对象
print(user_obj1.name,user_obj1.id)  #此时还没有创建
user_obj2 = User(name='yy1',password='1232456')

Session.add(user_obj1)  #要创建的数据对象添加到这个session中，一会统一创建
Session.add(user_obj2)


Session.commit()   
提交了事务才能生效。

数据库设计

sql注入

防止sql注入
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

用户管理

关于 'user'@'host'
MySQL数据库是根据 user@host 来确认是否唯一，user表中的每一条记录都是一个独立的账户
所以，user相同的并不一定值得是同一个，还要看后面的host。
MySQL在服务启动时就会将user表读取到内存。这个读取的过程会做 排序
最明确的host放在前，
通配符%是最不明确的代表，代表任意主机，排序后放在后面
空字符串 '' 尽管也表示任意主机但是优先级比%还低，置于最后
user也是相同的排序方法

备份恢复

命令行使用mysql脚本，数据库数据备份
导出所有库所有表的数据到一个sql文件中、
mysqldump -uroot -p --all-databases --lock-all-tables > ~/master_db.sql
mysqldump -u 用户名 -p密码 指定数据库 指定表 > ~/Desktop/文件名.sql
数据库数据恢复
1 在选择的数据库中
source xxxxx.sql文件
2 在命令行，不用进入数据库
mysql -uroot -p 要操作的数据库名称 < sql文件

备注

查看mysql数据文件的存放位置
show global variables like "%datadir%";

查看mysql的连接数配置
show variables like '%connection%';

修改mysql 的连接数配置
vim /etc/mysql/my.conf
max_connections=300
或者直接在数据库中修改
set global max_connections=1000;
保存重启服务生效

mysql中时间的互转
https://www.cnblogs.com/wbxk/p/10857342.html

命令行直接调用sql命令
mysql -uroot -p -e "use zabbix;select * from xxx" -N > xxx.txt
-e 后面接要使用的sql语句
-N不打印分割符号，直接将sql命令输出到xxx文件。

主备同步

change master to master_host='ip' ,master_user='',master_password='',master_port=3306,master_log_file='mysql-bin..00262',master_log_pos=565656565,master_connect_retry=10;