mysql 知识点整理

1、整数类型：

    1.1 如果最大的整数值小于255的，应该选择tinyint类型

    1.2 在设置整形类型的显示长度后，如果存入的数值超过了设置的显示长度，但未超过其最大的默认显示长度（也就是不超过该类型允许的最大值）的情况下，那么该数值照样能插入成功

    1.3 tinyint默认显示长度4、 smallint默认长度5、int默认长度11、bigint默认长度20

    1.4 占用字节数的情况：tinyint：1字节、smallint：2字节、int：4字节（其中mysql也支持interger类型）、big：8个字节

    1.5 根据占用字节来计算类型取值范围（分有无符号的情况）：例如tinyint类型，1个字节占8位，无符号的情况：（2的8次方）- 1

        有符号的情况是(2的7次方) - 1

    1.6 补显示位的字段参数设置：zerofill,注意：使用了该参数后，该字段默认会被设置成unsigned，也就是无符号的情况

 

2、浮点类型和定点类型

   1.1 占用字节数：浮点类型：float：4个  double：8个   定点类型：decimal：精度+2（精度就是数据的长度）

   1.2 mysql中可以设置浮点和定点的精度，其形式如下：数据类型( 精度, 标度 )  其中精度是指数据的长度(其中小数点不占精度)，标度是指小数点后的长度

       例如：float(10,2):数据长度为10，小数点后2位，对于存入的数值浮点类型会根据设置的标度来四色五入，但是定点类型不是四舍五入而是按照设置的标度来截取，并且会提示警告

   1.3 不设置浮点和定点类型的精度的情况： 浮点类型是会存放实际插入的数据的精度，而定点类型是存精度为10的整数

   1.4 设置精度不是浮点类型的标准形式，如果不是确实的业务需求，最好不要使用，有可能会影响到数据库的迁移，而数据类型( 精度, 标度 )是定点类型的标注形式，一般定点类型都该设置精度

   1.5 定点类型在mysql中是以字符串形式存储的，其精度比浮点的高

    选择原则：

                   浮点数存在误差问题

                   对货币等精度敏感的数据，应该使用定点数来存储

                   尽量避免使用浮点数来进行比价的操作，因为浮点数本身存在误差，比较后不准确的问题

                   

  

 

3、时间类型

   3.1 占用字符数：year：1   date：4  time：3   datetime：8  timestamp：4

       对于这些时间类型，如果插入的值有误（格式不对或者超出取值范围等），系统会报错，并且将对应的零值插入到对应字段中

       year：0000   date： 0000-00-00   time：00:00:00    datetime：0000-00-00 00:00:00   timestamp：0000-00-00 00:00:00

 

   3.2 current_timestamp、current_date、current_time、now() 获取当前日期、时间、日期+时间，可用于插入时间类型字段中

 

   3.3 datetime和timestamp都是存储格式为0000-00-00 00:00:00的日期+时间，其区别：

       3.3.1 timestamp会根据时区来转换时间，但是其范围相对datetime来说比较小，最大值为：2038-01-19 11:14:07，如果需要范围比较大的可以应该使用datetime类型

       3.3.2 向timestamp类型插入null或者不插入任何值，其字段值自动填充为当前的时间

 

4、枚举类型

   4.1 枚举类型最多能存储65535个值，如果值末尾存在空格，mysql会默认将其过滤掉，实际上mysql对枚举类型的每个值都有一个顺序排列的编号，mysql存储的正就是这个编号而非值

   4.2 如果对枚举类型设置了not null，那么枚举类型会取第一个值为默认值，如果没有设置not null，那么将会以null为枚举的默认值，而且也允许插入null

 

5、set类型

   5.1 相对于枚举类型，set类型能够存储多个值的组合，并以逗号隔开，而枚举只能取一个值

   5.2 set类型中如果存储的是多个值的时候，其插入字段中的顺序是根据创建set类型时候定义好的值的顺序来插入显示的，例如字段name中是set('a','z','b','x'),当我们向name字段插入('a','b','z'),那么最后在这条记录中name的值显示的是（'a','z','b'）

 

(mysql中，utf8字符集的情况下，1个中文占3个字节，在gbk中1个中文占2个字节，而无论什么字符集，1个英文占1个字节，而char和varchar定义的是字节数)

 

（在mysql中也可以使用boolean布尔类型，但实际上使用的是tinyin类型）

(show warnings：查看警告信息， show errors：查询错误信息， show engines或show variables like 'have%'：查看mysql支持的存储引擎, show variables like '%storage_engine%'：查看mysql默认的存储引擎)

 

（ 字段设置为自增auto_increment，该字段必须为主键或者唯一键索引，也就是说要设置成自增必须先为主键或唯一键，反过来，要删除）

(查询某用户是否拥有select等权限：select select_priv from mysql.user where user='用户名')

 

 

索引：

      1. 是对表的一列或多列的值进行排序的一种结构，可提高查询的效率

 

      2. 索引的存储类型分为：B型树索引、哈希索引，其中myisam和innodb只支持B型树，而memory引擎支持2种，默认为哈希

 

      3. 优势：提高查询数据的速度：在有依赖关系的子父表间的联合查询、 在分组排序子句中查询

      4. 缺点之一：固然可以提高查询数据的速度，但会影响数据插入的速度，因为向已有索引的表中插入数据时，数据库会将插入的数据按索引进行排序，这样就降低了插入数据的速度，尤其插入大量数据时影响更大，在这种情况下，最好的办法是先删除索引，插入完数据后重新建立索引

 

      5. 多列索引：只有在使用了第一列才能使用整个多列索引，需要注意

 

      6. 创建空间索引必须是myisam存储引擎，并且字段不能为null

 

      7.mysiam和innodb都支持前缀索引，myisam支持前1000个字符的前缀索引，innodb支持767个字符

 

      8.全文索引必须是myisam引擎，而且必须是char、vachar、text类型的字段才能建立全文索引，一般只有在海量数据字段才建立全文检索，因为全文检索存在“停止词”的特殊属性

 

      9.哈希索引的特殊点：

                9.1 只能用 = 和 <=>这两个比较符来进行等式比较

                9.2 优化器没法使用哈希索引对order by进行加速

                9.3 只能用关键词来搜索一行

 

      10. myisam中表的数据与索引是分开存储各自独立的两个文件，innodb则是默认存储在同一个表空间里，但可以有多个文件组成（可以通过修改配置文件参数，来使得每一个innodb表都拥有各自的索引和数据文件）

 

 

视图：

      1. 是有数据库中的一个或者多个表导出来的虚拟表，不占用物理空间，数据库只存放了视图的定义，而没有存放视图中的数据，这些数据是依赖创建视图是的原有的表，一旦这些表中的数据发生了变化，视图的数据也随之改变    

 

     2. 视图的定义数据是存放在information_schema库中的VIEWS表中，可通过查询该表来查询对应的视图

 

     3. 查询视图数据、查询视图状态等命令，与查询表的命令一致，例如可以用show table status like '视图名' 来查看视图的创建情况， 使用 show create view 视图名 来查看视图的定义

 

     4. 更新视图：是指通过视图来插入、更新、删除表中的数据，由于视图是虚拟表，其中没有数据，所有视图的更新都是转换到基本表来更新的

        4.1 无法视图更新的情况一：创建视图时候包含以下函数：sum、count、max、min等计算函数

        4.2 无法视图更新的情况二：创建视图时候包含以下关键词：union、union all、distinct、group by、having等

        4.3 无法视图更新的情况三：创建视图的select语句中包含子查询

        4.4 无法视图更新的情况四：在无法更新的视图的基础上创建的视图

        4.5 无法视图更新的情况五：视图创建语句中的参数algorithm的值为temptable（temptable类型是临时表类型，mysql默认临时表示不能更新的）

 

        4.6 视图创建语句中的with cascaded | local check option中的cascaded：表示在更新视图时候，要符合基表和视图2者的条件才能进行更新，而local则只需要满足视图本身即可更新操作， 默认是cascaded类型

 

     5. 要创建视图必须的执行创建视图语句的用户具有创建视图的权限（create_view_priv），同时对涉及查询的表的列具有select权限，可通过select create_view_priv from mysql.user where user="用户名"  来查询用户是否具有建立视图的权限，而修改视图，则需要有该视图的drop权限

     

    6.视图不支持子查询，一般是将子查询做成视图，在进行包含查询

 

 

Group by：

     1. 单独使用group by只会显示每一个分组的第一条记录

     2. 没有使用集合函数的字段其显示的值是每个分组第一条记录的其字段值

     3. with rollup：如果使用了集合函数，则会显示总和的一行操作

     4. group_concat（字段名）:将每个分组的指定字段的所有值都显示出来通过逗号连接

     5. having：分组条件过滤，having中使用的字段必须是在语句中使用到的,而非表中存在的任何字段

     6. 多字段分组：group by 字段1,字段2,....

     7. order by 与 with rollup不能同时使用，limit 必须放在with rollup 后面

 

     使用with rollup 和 多字段分组的实例：

     建表：create table sales ( year int not null, country char(20) not null, product char(32) not null, profit int  )engine = innodb default charset = utf8;

 

     插入数据：insert into sales values (2004,'cha','tnt1',2001),(2004,'cha','tnt2',2002),(2004,'cha','tnt3',2003),(2005,'cha','tnt1',2004),(2005,'cha','tnt2',2005),(2005,'cha','tnt3',2006),(2005,'cha','tnt1',2007),(2005,'cha','tnt2',2008),(2005,'cha','tnt3',2009),(2006,'cha','tnt1',2010),(2006,'cha','tnt2',2011),(2006,'cha','tnt3',2012);

 

 

     

 

子查询：

     1. 带in关键词的字查询 not in

     2. 带比较符的子查询

     3. 带exists关键词子查询(not exists)：使用exists时候，内层查询不返回任何查询记录，而是返回一个真假值（其实就是内层查询有满足条件的记录数就视为真），但内层返回真值的话，外层的查询语句才会进行

     4. 带any关键词的子查询，一般跟比较符结合使用，>any表示大于任何一个值，=any表示等于任何一个值

     5. 带all关键词的子查询，一般跟比较符结合使用，>all表示大于所有值，<all表示小于所有值

 

 

合并查询结果：（union:联合的意思，即把两次或多次查询结果合并起来）

     1.使用union或union all合并多个select语句，这些select中的字段数量必须一致，字段名也必须一致

     2.union会去除重复的记录，而union all则直接将所有的记录合并

 

      要求：两次查询的列数必须一致

      推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

      可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

      如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合         并，最终只保留一行。也可以这样理解，union会去掉重复的行。

      如果不想去掉重复的行，可以使用union all。

      如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或         筛选。

      如：(select * from a order by id) union (select * from b order id);

      在子句中，order by 需要配合limit使用才有意义。如果不配合limit使用，会被语法分析器优化分析时去除

 

 

运算符：

       1. 值1（字段1） div 值2（字段2）：求商， 如果值2为零，其最终结果为null

       2. mod( 值1, 值2 )：求余  等价于%， 如果值2为零，其最终结果为null

       3. = 与 <=> 功能基本一致，唯一区别就在于后者能够用于null的判断

       4. 在mysql中，真值true用1来表示，假false用0来表示

       5. 条件判断： if(条件，结果1，结果2)：如果条件成立则取结果1，否则取结果2            例如： select name if(score > 60, '及格', '不及格') from student;

                     ifnull(值1(字段), 结果2)：如果值1不为空，则显示值1的值，否则显示结果2 例如： select name, ifnull( addr, '没有填写地址' ) from student;

                     case when 语句：select name, case when score < 60 then '不及格' when score >= 60 and score < 90 then '良好' else '优秀' end from student;

 

 

权限设置：

       mysql涉及到的权限表有：（在mysql库中）user、db、host、tables_priv、columns_priv表，

       权限的判断也是按照该顺序的，优先从user表中判断相关权限是否为Y，如果是则不需要检查后面的表，

       如果否，则检查db表，依次进行查询

 

创建新用户：可以通过create user 语句、 insert语句到表user、 grant语句来创建

 

      1、create user 'test_user'@'localhost' identified by 'pwd123', 'test_user2'@'localhost' identified by 'pwd123';(可同时创建多个用户，用逗号隔开)

 

      2、使用insert到user表，需要注意3点：

         2.1 Password字段的值需要使用PASSWORD()函数来加密

2.2 user表中的ssl_cipher x509_issuer  x509_subject 字段没有默认值，插入时候需要插入默认值，一般为空

2.3 使用insert语句后需要使用flush命令来使用户生效，flush命令可以从mysql的user表中重新装载权限，但是使用

    该命令的用户需要有reload权限

    例如：insert into user (Host,User,Password,ssl_cipher,x509_issuer,x509_subject) 

          values ( 'localhost', 'ajia', password('pwd123'), '', '', '' );

           flush privilege

      

 

      3、grant select on *.* to 'youxi'@'localhost' identified by 'pwd123';

         grant命令还能修改密码

 

 

15. mysql日记：

    1.四种类型：二进制日志、错误日志、通用查询日志、慢查询日志，mysql默认只开启错误日志

    2.作用说明：如果mysql突然停止服务，可以通过错误日志查看出现错误的原因，并且可以通过二进制日志来查看用户分别执行了哪些操作、对数据库文件做了哪些修改，然后根据二进制日志中的这些记录来修复数据库

    3.二进制日志

      3.1 开启方式：在mysql配置文件中的[mysqld]选项中加入：log-bin = 目录路径+文件名,例如：log-bin = mysql-bin-log

      3.2 二进制的文件名为文件名.number, 这number的形式为000001、000002等，每次重启mysql后都会生成一个新的二进制日志文件，也就是number会递增，

      3.3 开启了二进制日志文件后，不但会生成对应的日志文件，还会生成一个记录日志文件的清单文件：文件名.index

      3.4 默认二进制日志文件是放在数据库的数据目录下面

      3.5 查看二进制日志文件：使用mysqlbinlog命令，例如：mysqlbinlog mysql-bin.000001

      3.6 删除所有二进制日志文件：进入mysql后使用命令：reset master;删除后，日志文件会从000001开始

      3.7 使用二进制日志文件来还原数据库

          3.7.1 日志文件中记录了insert、update、create等语句

 3.7.2 mysqlbinlog 二进制日志文件 | mysql -u用户名 -p

 3.7.3 还原时候必须先还原number小的文件，逐个还原

 3.7.4 由于二进制日志文件数据量比较多，占用较多的磁盘空间，所以一般是在备份了数据库之后，删除这之前的二进制日志文件

      3.8 查看是否开启了二进制日志文件：

          3.8.1 进入mysql后：show variables like 'log_bin';

 3.8.2 查看mysql的配置文件，是否有log-bin配置项

 

      3.9 暂停二进制日志：

          3.9.1 使用命令：set sql_log_bin = 0;  开启：set sql_log_bin = 1;  

 3.9.2 暂停了之后，通过show variables like 'log_bin'查看时候，仍然是on的状态，但是进行的数据库操作不会记录到二进制日志中去

 

 

     4.错误日志

       4.1 默认开启，log-error = 目录路径+文件名

       4.2 默认是放在数据库目录中

       4.3 重新建立新的文件记录错误日志，就得日志文件被改名为文件名.err-log，使用命令：mysqladmin -uroot -p flush-logs 或者 进入mysql后，使用flush logs语句

 

     5.慢日志：

       5.1 在mysql配置文件中添加：log-slow-queries = 目录路径+文件名 （该日志文件只有在存在慢查询记录时候才会生成）

       5.2 long_query_time = 秒数  如果没有设置，则默认为10秒

 

 

  16 优化插入数据的速度

  16.1 禁用索引，插入数据时，mysql会根据表的索引对插入的数据进行排序，如果插入大量数据时，需要先禁用索引，插入完毕后再启用（该方法针对myisam引擎有效）

       禁用：alter table 表名 disable keys

       开启：alter table 表名 enable keys

       对应新创建的表，可以先插入数据后再建立索引

 

  16.2 innodb的表示根据主键来排序的，所以在导入数据之前可以对数据针对主键字段来排序，再进行导入

          关闭唯一性校验（set unique_checks = 0），导入数据后，再开启 ( set unique_checks  = 1 )

 

  16.3 能够使用load data infile 语句导入数据的就不要用insert into 语句

 

 

  17 使用optimize table语句来优化表，也就是消除由delete和update操作造成的磁盘碎片

     17.1 对myisam和innodb引擎的表都有用

     17.2 只能优化varchar、text、blog类型的字段

 

  18.常用的mysql参数优化

     key_buffer_size:索引缓存的大小，数值越大，那么使用索引进行查询的速度越快

     table_cache：能同时打开的表的个数，数值不是越大越好，因为同时打开表越多会影响操作系统性能

     query_cache_type：查询缓冲区的开启状态，0为关闭，1为开启，2是按要求使用缓冲区

     query_cache_size：查询缓冲区的大小，查询缓冲区对于那些修改操作少、相同条件查询的情况才能发挥效果，默认值为0，表示关闭

 

     max_connection：数据库最大链接次数，不是越大越好，可能会造成资源浪费

 

 

   19.char和varchar选择情况：

       19.1 在myisam引擎中，在存储要求长度不超过50个字节的情况下，优先选择char

 

       19.2 在innodb引擎中，无论是否超过50个字节长度，建议使用varchar，由于在innodb表中，内部行的存储格式没有区分固定长度和可变长度列，而char占用空间较大

 

     19.3 在memory引擎中，两者都被当成char类型来使用，所以使用char类型

 

 

   20 保存大文本数据需要使用text或者blob类型，而text只能存储字符数据，而blob类型既可以存字符数据，也可以存二进制数据，比如照片。 使用了这两种数据类型并且在进行大数据量的更新或删除操作后，应该使用 optimize table来整理碎片空间

 

     20.1 对于text和blob的查询优化，我们可以使用合成索引和前缀索引 

          合成索引：其实就是将text或blob字段的内容使用md5()、SHA1()、CRC32()等函数生成一个散列值，然后存放在另一个字段中进行查询，而这个查询必须是“精确查询”（因为散列值对类似<、>=的操作符号无作用），另外注意如果这个哈希值尾部有空格，则不能将该哈希值存放在vachar或char类型中

 

 

          前缀索引：其实就是对字段值中的前多少个字符做索引，该索引有利于“模糊查询”