一、数据类型、常用函数、编码
一、数据类型
数据类型主要分为5个类型：整型、
1、INT类型
具体有TINYINT（1字节）、SMALLINT（2）、MEDIUMINT（3）、INT（4）、BIGINT（8）
常用的是INT类型，而且每个整型类型都有一个有符号（Signed）和无符号(UnSigned)类型，分别代表不同大小。
例如我们现在创建一个z表，里面有个字段为a且其符号类型为无符号、另一个字段b为tinyint类型且有符号：
create table z ( a int unsigned,b tinyint signed); 此时查看表定义内容：
我们可以看到int（10）和tinyint（4）中的10和4表示显示的宽度，例如现在b是123456，其宽度是6为数，而我们查表出来显示的是1234这四位宽度，但这并不是数据库存储的真实数据。（即这个只是显示宽度，不是真实宽度）
自增属性：AUTO_INCREMENT。
如果我们想要a进行自增，此时可以修改a字段为自增属性：
可以看到a从1开始自增 。
可以使用select last_insert_id()； 查看上一次执行自增的值是多少。（这里查询结果会是2）
如果要做自增的话，一般不建议用int类型，建议用bigint类型。因为int类型的长度虽然有21亿，当对于大数据量的话远远不够，所以尽量使用bigint（长度比较大）
自增的话这里也有一点要注意：如果表中有1、2、3、4，然后把4给删了，此时再进行数据插入不会进行自增值回溯（即4被删了就不会出现有4的数据了），所以插入的值会是5。
但是有一种情况会出现子增值回溯，就是mysql重启后。如果我们有1、2、3、4，此时删除掉4，如果mysql不重启，再插入时的值会是5，这就是自增值不回溯。但是如果我们再删除完4之后，先重启mysql，然后再插入数据，此时插入的数据则是4，这就是自增值回溯问题。（注意：如果删除的不是最后一个，是不会有回溯问题的，即如果删除了2，剩下134，然后重启再次插入还是5，最后会是1345）
自增值回溯问题则会导致我们原先删掉的值又出现了，导致数据混乱。
其实每次自增值是由表的一个属性AUTO_INCREMENT决定的。
此时如果这个属性是5，则下次插入的值会是5。而mysql重启该值会被重新进行（select max（aoto_increment column）+1 from z）初始化，该初始化就是计算列数+1。这就是数据回溯的原因。
mysql5.7自增值回溯这个问题是解决不了的，在8.0解决了这个问题，就是将这个自增值持久化，而不是每次重启时会重新计算。
一般对于符号类型建议使用有符号类型（Signed，默认就是有符号类型）。
2、数字类型
建议数据类型直接用decimal类型即可。
数字类型对应的一些函数：
1）、floor（）：向下（小）取整。2）、round（）：向上取整
3）、rand（）：随机获取0-1的值。
如果要去n-m的值：floor( n + rand() * (m-1) ) 例如1-100 ：select floor（1+rand（）*99）；
3、字符串类型
其中用的最多的是varchar变长类型。
其中可以看到存储的类型有分字符、二进制的类别。例如varchar（10）表示可以存10个字符。而binary（10）表示存10个字节。
例如我们现在创建一个表：
往里面加数据：
此时c中只能存一个字节，这里报出长度的问题。
字符串类型————字符集（编码的规则）
常见字符集：utf8、utf8mb4、gbk、gb18030。
生产环境中只推荐用utfmb4。（一些表情之类的utf8是存不下的）
所以我们可以在配置文件中配置好字符集类型：（默认的字符集是latin1）
注意：修改之前的数据库和表不生效，只对后面新建的数据库、表生效。
也可以动态修改之前或者新建的表的字符集。
字符集的转换不是可以任意转换的，小字符集可以转为大字符集。大字符集转小字符集就会出错。
我们可以使用hex（）函数（转换为16进制）查看字符在utf8mb4字符集下的编码：
如果此时将这个0xE68891这个16进制值插入表中，查询的时候mysql会将其转换成相应的‘我’字符。
类型转换函数：cast（）：例如将int类型转换为char类型
将a从utf8mb4转换为gbk（这个a在两种字符集一样）
字符串类型———排序规则（collation）
utf8mb4对应的collation是utf8mb4_general_ci。
collation主要是字符串中用来比较的规则（大小写不敏感）
md5（）：加密函数
concat()：拼接字符串
区分大小写设置：这样这个字段a就会区分大小写
length（）：查看有多少个字节 （括号里面填充的是字符串，如果不是会进行隐形转换） charilength（）：查看多少个字符
upper、lower：转换为大小写
concat_ws():根据某字符进行连接：
repeat（）：产生重复的字符
lpad、rpad：左填充和右填充
对于大对象可以用text、blob存储。但一般不建议一些图片或者视频存到数据库中，可以建立专门的文件服务器。
字符串类型其实还有一种枚举类型————ENUM & SET。 可以起到约束的作用：
例如性别约束为男女，插入其他的就会报错。
日期类型
now（）：查看当前时间。now（6）：表示显示毫秒的长度（5.6开始支持）
一般都是使用datetime或者timestamp这两种类型。这两个类型是有区别的：
unix_timestamp():表示某个时间到1970-1-1 的秒数（这会转换为int类型）。
所以有些人在设计时间是喜欢将时间字段设置为int类型，但一般不建议这么做，所以一般还是使用datetime或者timestamp（2038年会被用完），不过还是建议使用datetime。
不过timestamp是可以跨时区的，如果业务跨不同时区则可以用timestamp。timestamp会根据时区来相应调整时间。
例如我们往z表中分别插入a（datetime）、b（timestamp），如果时区改变，b的值也会做调整。
sysdate和now是不同的：
注意一些函数一般不要这么使用：即放在where后面，这样是用不到索引的（当然也可以用函数索引）
如果没用函数索引，此时一般建议写成这样：
JSON数据类型
简单来说就是字符串，只是格式是json格式的字符串。
这样定义表的时候可以不定义任何字段，可以用json直接代替，然后列新增减少在json字符串中体现就行。（也称schema free）
对于json类型，我们也可以用json_extract()函数值获取部分json内容：
先创建表：
例如对于json类型的字段，我现在像获取name：
这时发现多了一对‘’‘’，此时可以再用json_unquoted（）函数去掉。
不过这样的写法有点麻烦。在mysql5.7开始支持下面的写法：
mysql5.7支持的 json相关函数：
而且mysql5.7可以创建函数索引，用了上面的函数是可以在建立函数索引的情况下用到索引的。
NULL类型——可以是字符串、数字、时间等等。
二、表
mysql默认存储引擎都是基于行存储的，每行记录都是基于列进行组织的。
创建表：create table 表名 （字段定义1，字段定义2，..........）;
删除表：drop table 表名；
修改表：alter table （具体比较多，可以百度）
外键Foreign key （一般都不使用）
主键 primary key
1、表的元数据
创建的每个表的元数据（一些表名、字段信息等）都会存在mysql自带的information_schema数据库中。其中表的信息存在tables表中，字段信息存columns表中
tables表中记录了表的所属数据库、表名、表的引擎、表类型等等。
columns表中则有一些所属表名、字段名、字段类型、是否外键、字符集等等。
一般dba会对数据库的非系统表进行扫描（根据这两张表进行扫描），查看哪些表不是innodb的，以便改为innodb引擎，再看哪些表字符集不是utf8mb4的。
分区表（不是分库分表）
Mysql 分区表-分区操作 - xibuhaohao - 博客园
mysql 表分区优缺点_mysql分区表的原理和优缺点_老杨的诗和远方的博客-CSDN博客
即我们看到的数据库可能是一张表，但是mysql会将这张大表分解为多张小表进行存储。5.7只支持水平分区，即根据某个字段按照某种规则进行分区。
注意分区字段要么是索引、要么是索引的一部分，所以说mysql是局部分区（例如上面的col3不是索引，所以创建分区表会报错）
mysql支持的分区类型：
分区表也可以按照年份、日期等进行分区，从而对数据按照某种规则进行分区管理。
范式：一二三范式
1nf：就是每张表都要有主键，且所有属性依赖于主键
2nf：没有部分依赖（即没有属性依赖于主键的一部分）
3nf：没有传递依赖。
一般使用的是反范式设计。
主键很多人都是使用uuid，在mysql8中有对uuid进行优化，将uuid转化为16个字节。
临时表：
想要创建一个tmp临时表： create temporary table tmp (a int);
注意这种临时表和我们讲group by时的临时表（内存表，在sql执行期间mysql隐式创建的）是不一样的。这种临时表是手工创建的
创建完临时表后如果查看表是看不到这个表的，但是可以对该临时表进行插入等操作：
这种临时表是会话级别的，其他会话是不会收到影响的。
这种临时表的内容是存放在mysql的data目录下的一个临时表空间文件（ibtmp1），每次重启mysql会初始化清除掉临时表空间的数据。
mysql还有一个临时目录（默认/tmp）：这里有相应的文件用来存放临时表的表结构（ibtmp1是存放临时表的数据内容）
三、select
select + 函数（可以使用后面from获取到的字段）/字段（从from后面获取到的字段）+from +表名+where +group by +having +order by +limit
1、order by
ASE:从大到到小，降序。默认
DESC：升序。
我们来看看不排序和排序所花费的时间：
在一个600万数据的表中：
未作排序前：
排序后：
这时证明排序是消耗比较大的时间成本的。
此时我们可以查看下mysql给我们排序时分配的内存大小：（262KB大小）
显然默认的是比较小的，这个参数是基于会话级别上修改的。我们在这个会话中修改这个值为256M
然后重新去执行排序的语句：可以发现，此时时间缩短了很多。
所以对于应用中，如果存在大量排序且没其他手段优化了，可以考虑下提高一下排序的内存空间。
不过要注意下，每当有一个会话建立，都会分配给这个会话相应的排序内存空间，所以分配太大的时候，如果会话太多可能导致内存不足/内存浪费，所以要适当使用。
所以我们可以在配置文件中添加上这么一个参数（只能设置会话级别的）：
再次执行排序sql：
也是用来10s多，所以可能这个sql用到的内存不需要太大，32m已经足够了。
而我们大概要设置多大？？
我们可以用show status like ‘sort%’来查看下这些排序内存的状态
这里的信息有一个sort_merge_passes的指标，如果这个指标过大，则表示其排序内存是不够大的，其存在和磁盘交互的情况（将数据暂存到磁盘，然后再进行归并等操作）。这个时候就要调整这个排序内存大小。
我们这里的32M是足够的，所以这个sort_merge_passes是0。如果现在的值为256k，我做了一次排序sql执行后再次查看其状态：
在执行下一次sql前，一般使用flush status；将之前执行sql的记录清除，否则前后执行的信息会进行叠加：
在256k排序内存下执行排序sql ：
这个时候这个参数变成了3927，其值是很大的，所以需要对排序内存进行扩大。
2、limit
取前n条数据：limit n；
limit 10,10； 从第10条开始取，一共取10条。（实际就是取10条数据，然后将最后10条返回）如果limit 的深度很深的话（例如limit 10000，10），这样的效率是比较慢的（会取10010条数据，再将最后10条返回）
如果深度较深的话性能会降低，那么此时就可以用索引去优化：
3、group by 分组
例如订单表中，我们需要查看每个月的订单总金额，此时就得使用group by进行查询了。
其中o_totalprice是每个订单的金额，o_orderDATE是订单日期。（此时根据订单时间进行分组，然后统计各组的金额总值）
select sum(o_totalprice), date_format(o_orderDATE,'%Y%m')
from orders
group by date_format(o_orderDATE,'%Y%m');
分组也可以接多个字段：group by 字段1，字段2；
则表示字段1和字段2相同的放在一组,一般分组后是要对每组的数据进行聚合运算，例如sum、count等。
现在我们要求每个月每个客户产生的订单的总金额多少、订单数量、平均订单数额？（客户表o_clerk）(此时则用到多个字段进行group by)（雇员和月份进行分组，然后再统计）
group by的优化（非索引方式）
进行数据分组，每组的数据都会存放到临时表中。那么此时可以对于临时表进行优化
临时表有一个参数tmp_table_size(默认16M)
可以用show variables like '%tmp%'; 查看
如果分组出来的数据比较大时需要调大临时表的内存大小，可以取调大这个参数。
哪我们怎么知道临时表的大小是否足够？
类似上面，临时表有一个状态：其中create_tmp_disk_tables:表示基于磁盘创建临时表的次数。（临时表内存大小存不下时，就会存到磁盘上）
如果这个数据较大，则表明临时表的内存分配不足，则可适当调大其内存大小。
这个和之前order by的参数类似，设置只可以为会话级别的，我们可以通过监控全局的看这个参数是否一直在增长来判断临时表是否需要调大。（会累加的）
我们可以配置下每个会话的临时表：
count(*)、count(1)、count（字段）
count(字段)：返回该字段非null值的个数
count（*）和count(1)（或者count（数字）的结果都一样，count 1、2、3、。。都一样）：表示返回记录行数。
having ：过滤
主要要区分having和where的区别。
而having表示对select后面聚合之后的值进行过滤（例如这里使用了聚合之后的值count）。（where不能使用聚合后的值）
where 和having区别？？
我们如果改写上面的sql成这样：
这时会报字段不存在。从sql可以看出，之前的表已经被group by进行分组了，最后只剩下month、count、sum这三个列，所以最后进行having会报字段不存在。而having一般是使用select后面的字段。
此时我们修改为select后面有的列month：
此时就能发现运行成功。但是相对于之前的待遇where的sql，时间相差很多倍。
其实带有where的sql快，是因为之前的数据通过where已经被筛选了大部分，所以到后面的分组、haviing操作就会快很多。
group by后只能查出聚合函数的值、分组字段：
例如表中有字段1、字段2、字段2。我们现在对字段1进行group by，那么select 字段1，sum（字段2），count（字段3）这样是对的，那么如果写成select 字段1，sum（字段2），字段3 这个时候在5.7中会报错，5.6不会报错（非分组字段出现时会随机显示数据表的某条数据）。
5.7会报错是因为涉及到一个参数sql_mole：
如果将5.6的sql_mole设置的和5.7一样，那么5.6运行后也会报错。
5.7的这种模式是比较严格的，会约束一些非法数据、非法操作等。
所以一些mysql从5.5、5.6升级到5.7、8.0就会出现一些sql错误，可能就是sql_mole的改变。
分组后获取每组中的前n条数据：这时就需要使用group by的一个函数：
例如现在有一个这样的表：
我想根据userid进行分组后显示每组的数据：这时可以用group_concat()聚合函数将分组的某个字段进行拼接起来。
默认是用，号进行拼接，也可以自定义自己的符号，并且实现对组数据的排序：
排序也可以根据分组中其他字段值进行排序：
group_concat(price,'：',1):可以对price进行分组后以：号隔开，然后获取第一个值
四、子查询
一、子查询使用
5.6之后对子查询有了较大的优化。（5.6之前的in语句都会被重写为existst语句，而existst在外表大于子表时性能低于in的。所以5.6之前的版本in有时候性能是很低的。5.6之后in不会进行重写，还会对in做特定的优化）
1.ANY关键字
假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么，select ...from ... where a > any(...);
->select ...from ... where a > result1 or a > result2 or a > result3;
即a大于子查询中的任意一个，等同于a大于子查询的最小值即可。
2.ALL关键字
ALL关键字与any关键字类似，只不过上面的or改成and。即:select ...from ... where a > all(...);
->select ...from ... where a > result1 and a > result2 and a > result3;
即a大于子查询中的每一个，等同于a大于子查询的最大值。
3.SOME关键字
some关键字和any关键字是一样的功能。所以:select ...from ... where a > some(...);
->select ...from ... where a > result1 or a > result2 or a > result3;
4、in （推荐用）
即存在某个子查询返回的结果，匹配子集中的任一元素
in也可以类比为or的语句：
二、子查询分类
独立子查询（只涉及自己的表）、相关子查询（其子查询不是独立的需要用到外表）
三、exists
主要区别in 和 exists
in和exists的结果一般的相同的，但是其运行机制不同
in的写法：（独立子查询：in后面的子查询和外面的没有关联）
修改为existst的sql：（相关子查询，里面的子查询需要关联外面的进行过滤）（即将in语句逻辑改到子查询中取）
其实用exists和in都可以实现一样的效果，那么什么时候用in、什么时候用exists？
外层查询表小于子查询表（数据少于），则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。MySQL中 in 和 exists 区别_riemann_的博客-CSDN博客_mysql中in和exists
in的逻辑：子查询一次查完，查出的结果一条一条的去外查询表中匹配扫描（被扫描表：外表。扫描次数：子表行数）
existst逻辑：外查询表每条数据都去子查询中关联执行。（被扫描表：子表，扫描次数：外表行数）
如果要效率最高，此时要求扫描次数少，那么in时适用于子表较小的时候，即外层查询表大于子查询表；而同样exists适用于外表较小，即外层查询表小于子查询表。
not in 和 not existst？
not exists性能是高于not in的，not in是用不到索引的，因为not in实质上等于!= and != ···，因为!=不会使用索引，故not in不会使用索引。
一般逻辑中这个值应该是1，而not in如果后面的集合中含有null值则不会返回1值，只会返回null和0值。而在where后面null和0都表示false，所以此时就会出现问题：
从上面可以看出a表的userid = 3是不存在b中的，按照正常逻辑应该返回3的数据，但是因为not in 在子查询结果中存在null值，所以只能返回null和0，所以where一直会认为null和0就是false，所以最后的结果永远是空的。
所以这时我们需要将子查询的null值过滤掉，此时可以改写为这样：
那么此时使用not exists呢？
此时可以发现not existst是没有这样的问题的。
exists和in区别总结：
in：独立子查询，适用于外表大于子表的情况，not in不能使用索引，not in会出现子查询中有null则查出的结果永远为空的情况。existst：关联子查询，适用于外表小于子表的情况。不会出现not in 子查询null查询结果空的问题。
not的推荐使用not exists，in和exists看场景使用。
四、子查询优化
其实到5.6版本，子查询的优化已经比较好了，主要需要注意的是索引的使用。
五、联表
1、inner join /join 内联 （求交集）
select * from a
inner join b
where a.x = b.x
2、outer join 外联
分为左联left join/left outer join、右连 right join/right outer join
left join：
select * from a left join b on a.id = b.id
以a为主体，联表一起查出b表符合条件的数据。a的所有数据都要出现，右边的表根据筛选条件出现。
right join：
select * from a right outer join b on a.id = b.id//
以b为主体，联表一起查出a表符合条件的数据。
像其他在a表不在b表，在b表不在a表，两表并集之类的，可以看：MySQL 7种 JOIN连表方法 - 码上先生 - 博客园
union：拼接两者的结果
笛卡尔积: select * from a, b
注意：where后面是可以这样写的：where （id，name）in （select id1，name1 from a）；这样就会以（id，name）为一个整体去匹配后面的结果集。
这个语句即如果插入的值的字段a是唯一约束的，那么插入重复的值此时不会报错停止，而是继续执行后面的update操作把这个a值变为a+10.
replace：和insert类似，都是插入操作：
不过replace和insert有时不同，例如我们现在去对于两个唯一索引a、b。
此时进行replace into z values (1,2);
此时replace会先插入a=1，如果z表有a=1的记录则因为是唯一索引，此时就会对其记录进行删除，然后再执行b=2的插入，如果表中存在b=2的也会被删除。最后就会删除所有a=1或者b=2的记录，然后增加a=1，b=2这条记录。replace优点就是可以用来幂等
replace和on duplicate还是不太建议使用。
六、Prepare sql
可以防止sql注入，动态查询条件。
sql注入：比如我们现在有一个语句：select * from a where id = 100；
此时被人找到接口，将sql修改为：select * from a where id = 100 or 1=1；此时不管怎样，都会查出所有数据。
使用prepare 语法去执行这个sql：
set @s = 'select * from a where id = ?'; set @a = '100 or 1=1'; prepare stmt from @s; execute stmt using @a;最后的结果会是select * from a where id = 100；的结果，这样就起到了防止sql注入的情况。
七、存储过程、自定义函数
1、存储过程：将一些sql语句集存放在mysql服务端，用户可以通过存储过程名和传参多次调用的程序模块。
Oracle：用的存储过程较多；mysql：基本不用。
mysql的存储过程性能很差。
存储过程——流控制语言
创建一个存储过程：其中begin、end是存储过程的开始和结束标识。存储过程名称：delete_matches,括号内是参数。
调用存储过程：call delete_matches（10）；
2、自定义函数
例如：创建一个自定义的函数：
其主体也是以begin、end为开始结束标志。
3、触发器
触发时机可以是before，也可以是after。触发事件可以是insert、update、delete。
创建一个触发器：
查看所有触发器：SHOW TRIGGERS\G;
具体看：MySQL触发器trigger的使用 - GeaoZhang - 博客园
4、视图：（根据从其他表查来的数据存成一个视图）
视图是虚拟的，不存在任何数据文件的，即每次查视图时是实时去调用视图的语句。
创建视图：
5、事件event
即定时器，定时会触发执行。mysql默认不打开。
可以在配置文件中配置开启：
3947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
