MySQL

数据库出现group异常 5.7版本
https://blog.csdn.net/u012660464/article/details/113977173

数据库常用语句

如果是char、varchar类型的字段，length可以小于字段实际长度；如果是blob、text类型，必须指定length
1.添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column)

2.添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column)

3.添加全文索引
ALTER TABLE table_name ADD FULLTEXT (column)

4.添加普通索引
ALTER TABLE table_name ADD INDEX index_name (column )

5.添加组合索引
ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3)
附表字段

数据类型
备注 ：
数据结构建表 合理分配空间（查询时间与存储空间的转换）
字段起名 要有意义
二进制自行百度
1字节 占 8个位 2^8 0-255 (unsigned 无符号)
-2^7 到 2^7-1 = -128 到 127
0-255 解释 0000 0000 -> 0 ； 1111 1111 -> 2^8-1 = 255
-128-127 补码规则

例 2个字节 占 16位 0 到 2^16-1 = 65535
-2^15 -> 2^15-1 = -32768 到 32767

推导公式 N字节 0 - 2^8N-1
-2^(8N-1) - 2^(8N)-1

一个汉字是2个字节；一个英文字母（不分大小写）是一个字节；中文标点占三个字节；英文标点占一个字节

查询语句 实战避免用*

group 陷阱
select goods_id,sum(number) from goods; 这是错误表达

错误查询
where 针对表进行查询 discount 是计算结果

子查询陷阱
select goods_id，cat_id，price from test where 1 group by cat_id order by goods_id；语义错误，取的是cat_id第一次的行
综合测试题

错误语句
select name,avg(score),count(score<60) as gks from test group by name having gks > 2; 当三门都及格也查询到
select name,avg(score),sum(score<60) as gks from test group by name having gks >= 2;

子查询

//查询最大的的商品 where 子查询
SELECT * FROM goods WHERE id = ( SELECT MAX(id) FROM goods );
//查询每个栏目下的最大商品
SELECT * FROM goods WHERE id IN ( SELECT MAX(id) FROM goods GROUP BY cat_id );
//from 子查询 
SELECT * FROM ( SELECT * FROM goods ORDER BY cat_id DESC,id DESC ) as tmp GROUP BY cat_id;
//备注抛出异常 this is incompatible with sql_mode=only_full_group_by 则需要修改配置
//exists 子查询
SELECT * FROM category c WHERE EXISTS ( select * FROM goods g where g.cat_id = c.id );

关联查询

//左连接
select g.id,g.name,c.cat_name goods g left join category c on g.cat_id = c.id;
//如果两表名称有重复的要起别名
//右连接
select g.id,g.name,c.cat_name goods g right join category c on g.cat_id = c.id;
//内连接 左右连接的交集
select g.id,g.name,c.cat_name goods g inner join category c on g.cat_id = c.id;
//外连接 取所有集合的并集 mysql 不支持外连接 SQLServer

Union 合并

//举例
SELECT * FROM goods WHERE id < 3 UNION SELECT * FROM goods WHERE id > 5;
//备注 如果容量不一致 会导致丢失

下面操作实际上没什么意义

case when 查询
select id,name,is_del,(
case is_del when 1 then ‘男’
case is_del when 2 then ‘nv’
else ‘其他’
end
) del from user

不可使用关键字 正确写法

触发器 trigger

使用条件 sql1 触发 sql2 （有点像 sql1 union sql2 语法）

实例需求
商品表 goods 订单表order
当下1个订单，对应商品库存减少
分析
监听表 order
监视动作 insert
触发事件 after
触发事件 update

create trigger 触发器名称
after/befor 触发时间
insert/update/delete 监听事件
on 表名 监视地址
for each row
begin
sql1;
…
sqlN;
end

特点：
减少网络交互，速度快、自动完成静态sql
预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率
存储过程可以重复使用，减少开发人员的工作量
安全
业务逻辑封装性好，修改方便

缺点太多
能不用就不用，此为下下策
使用存储过程 占用服务器端太多的资源，对服务器造成很大的压力
难解耦，容易产生死锁
对后端寻找错误不友好，不看数据库结果根本不知道
数据库移植会增加工作量
不能DDL

拓展知识
存储过程

存储过程语法
create procedure procedureName()
begin
— sql语句;
end$

• 事务隔离级别
  • 未提交、读提交、重复读、序列化
  • https://www.cnblogs.com/ubuntu1/p/8999403.html
• 事务特点
  • 原子性（Autmic）：事务在执行性，要做到 “要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在 rollback 时也要消除对数据库得影响！
  • 一致性（Consistency）：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！
  • 隔离性（Isolation）：如果多个事务并发执行，应象各个事务独立执行一样！
  • 持久性（Durability）：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！
  • https://baijiahao.baidu.com/s?id=1611918898724887602&wfr=spider&for=pc 详细讲解
  • 
    
• 分表策略

索引
定义 ： 帮助MySQL高效获取数据的数据结构
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
平常所说的索引，如果没有特别指明，都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、
唯一索引默认都是用B树。
通过show index from tablename可以查看表的索引情况

优点
提高效率，降低数据库IO成本
通过索引列对数据进行排序，降低数据的排序成本，从而降低CPU的消耗
缺点
索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间

MySQL 各个存储引擎、及区别（一定会问 MyISAM 与 Innodb 区别）

Mysql常用存储引擎共有：MyISAM，Innodb，Memory，Archive（还有其他引擎，但不常见）

InnoDB 和 MyISAM之间的区别：

1>.InnoDB支持事务，而MyISAM不支持事务

2>.InnoDB支持行级锁，而MyISAM支持表级锁

3>.InnoDB支持MVCC（多版本并发控制）, 而MyISAM不支持

4>.InnoDB支持外键，而MyISAM不支持

5>.InnoDB不支持全文索引，而MyISAM支持。（X)

InnoDB ：如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

MyISAM：如果数据表主要用来插入和查询记录，则MyISAM（但是不支持事务）引擎能提供较高的处理效率

Memory：如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。

Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

什么是索引，作用是什么？常见索引类型有那些？Mysql 建立索引的原则？
索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针，相当于书本的目录。其作用就是加快数据的检索效率。常见索引类型有主键、唯一索引、复合索引、全文索引

锁机制
锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

mysql 的三种锁
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表级锁的锁模式（MyISAM)
MySQL 表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。
对 MyISAM 的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对 MyISAM 的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM 表的读操作和写操作之间，以及写操作之间是串行的。
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
对ＭyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对ＭyISAM 表的写操作，则会阻塞其他用户对同一表的读和写请求；ＭyISAM 表的读和写操作之间，以及写和写操作之间是串行的！（当一线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。）

预防死锁
资源一次性分配：一次性分配所有资源，这样就不会再有请求了：（破坏请求条件）
只要有一个资源得不到分配，也不给这个进程分配其他的资源：（破坏请保持条件）
可剥夺资源：即当某进程获得了部分资源，但得不到其它资源，则释放已占有的资源（破坏不可剥夺条件）
资源有序分配法：系统给每类资源赋予一个编号，每一个进程按编号递增的顺序请求资源，释放则相反（破坏环路等待条件）
避免死锁
预防死锁的几种策略，会严重地损害系统性能。因此在避免死锁时，要施加较弱的限制，从而获得 较满意的系统性能。由于在避免死锁的策略中，允许进程动态地申请资源。因而，系统在进行资源分配之前预先计算资源分配的安全性。若此次分配不会导致系统进入不安全的状态，则将资源分配给进程；否则，进程等待。其中最具有代表性的避免死锁算法是银行家算法。
银行家算法：首先需要定义状态和安全状态的概念。系统的状态是当前给进程分配的资源情况。因此，状态包含两个向量 Resource（系统中每种资源的总量）和 Available（未分配给进程的每种资源的总量）及两个矩阵 Claim（表示进程对资源的需求）和 Allocation（表示当前分配给进程的资源）。安全状态是指至少有一个资源分配序列不会导致死锁。当进程请求一组资源时，假设同意该请求，从而改变了系统的状态，然后确定其结果是否还处于安全状态。如果是，同意这个请求；如果不是，阻塞该进程知道同意该请求后系统状态仍然是安全的。
检测死锁
首先为每个进程和每个资源指定一个唯一的号码；
然后建立资源分配表和进程等待表。
解除死锁
剥夺资源：从其它进程剥夺足够数量的资源给死锁进程，以解除死锁状态；
撤消进程：可以直接撤消死锁进程或撤消代价最小的进程，直至有足够的资源可用，死锁状态。消除为止；所谓代价是指优先级、运行代价、进程的重要性和价值等。