Mysql基础

MYSQL基础

 

数据类型：

         整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

         属性：UNSIGNED，让他变成非负

         长度：可以为整数类型指定宽度，例如：INT(11)、对大多数应用是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数。

 

         比如说定义INT(3)存1234，是可以存的。

         假设存12，我们在后面制定一个属性zerofill代表0填充，就是mysql数据库自动为我们做填充，就变成012。如果指定INT(5)存12，就会补成00012，保证存储的最小宽度是5位。不指定zerofill是不会给我们0填充的，就是不指定是没有任何意义的。        

         比如INT(0)并不代表只能存一位，0代表的是宽度

 

         实数类型：FLOAT、DOUBLE、DECIMAL

         DECIMAL可存储比BIGINT还大的整数；可用于存储精确的小数

         FLOAT和DOUBLE类型支持使用标准的浮点进行近似计算。对计算来说FLOAT和DOUBLE效率更高，但是DECIMAL可以理解当成字符串处理了！所以存储的内容会更大。

        

         字符串类型：VARCHAR、CHAR、TEXT、BLOB

         CHAR和VARCHAR区别比较重要

         VARCHAR类型用于存储可变长度字符串，它比定长类型更节省空间，当其使用1或者2个额外字节记录字符串长度，列长度小于255字节，使用1个字节表示，否则用两个。如果存储的内容超过指定长度，会被截断。比如我们指定VARCHAR(16)，但是存了VARCHAR(32)位的字符串就会被截成16位，有的版本还会给你报错。VARCHAR如果定义为16，存了个18位的字符串不会变成18位，它的边长只会往小的缩，不会往大的扩。

        CHAR是定长的，根据定义的字符串长度分配足够的空间，它会根据需要采用空格进行填充以进行比较，比如定义CHAR(5)我定义了一个3位的字符串我会补两个空格。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。存储密码的时候使用CHAR，比如定义CHA(32)，无论多长字符串，md5之后都是32位。超出设定的长度也会被截断。

         对应经常变更的数据，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

         对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

         只分配真正需要的空间，更长的列会消耗更多的内存。一般只需要5-10位的字符串，就不要定义100位的，会消耗很多内存。

         尽量避免使用BLOB/TEXT类型，查询会使用临时表，导致严重的性能开销。

        

         枚举：有时可以使用枚举代替常用的字符串类型，把不重复的集合存储成一个预定义的集合。非常紧凑，把列表值压缩成1-2个字节，内部存储的其实是整数。比如说我们存储男女，内部其实是0,1。尽量避免使用数字作为ENUM枚举的常量，易混乱。排序是按照内部存储的整数来进行排序的。枚举表会使表大大减小，因为存储的是整形。

 

         日期和时间类型：尽量使用TIMESTAMP比DATETIME空间效率高。用整数保存时间戳的格式通常不太方便处理。如果需要存储微秒，可以使用BIGINT存储，微妙有很多小数位，用float和double有一定的损耗。我们可以通过乘以10万或100万变成BIGINT，将来拿出来再除就可以了。

         列属性：auto_increment、default、not null、zerofill

 

         INT(0):整形，宽度是0位

         CHAR(16):定长的字符串，我们定义的宽度是16位

         VARCHAR(16):可变长字符串，他的长度也是16位

         DATETIME:保存我们的时间日期的

         TEXT:存储字符串类型，大文本数据

 

基础操作：

         MYSQL的连接和关闭：mysql –u（用户名）-p（密码）-h（主机）-P（端口）

         其他：\G 把打印的结果垂直显示

                     \c 取消当前的mysql命令

                     \q 推出我们的mysql

                     \s 显示mysql服务器状态

                     \h 帮助信息

                     \d 改变我们的执行符，比如说吧分号改掉

 

表引擎：

InnoDB表引擎：默认的事务型引擎，最重要最广泛的存储引擎，性能非常优秀。数据存储在共享表空间，可以通过mysql配置文件分开，就是原本表的索引表的配置都在一个表存着。

对主键查询的性能高于其他类型的存储引擎。另外内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区。

         支持通过一些机制，工具做一些真正的热备份。支持崩溃后的安全恢复。支持行级锁。支持外键。

 

MyISAM表引擎：

         5.1版本前MyISAM是默认的存储引擎。拥有全文索引、压缩、空间函数。不支持事务和行级锁，不支持崩溃后的安全恢复。表存储在两个文件,MYD和MYI分别是数据和索引。设计简单，某些场景下性能很好。比如说我们要获取整个数据行的大小有多少数据，这个时候MyISAM效率还是很高的。

 

其他表引擎：Archive、Blackhole、CSV、Memory

优先选择InnoDB

 

InnoDB和MyISAM区别：前者行锁，后者表锁。前者支持事务处理，MyISAM不支持、前者默认使用共享表空间，后者不适用。noDB保存在共享表，MyISAM数据保存在.MYD文件，索引保存在.MYI等等等

 

Mysql锁机制

表锁是日常开发中的常见的问题，当多个查询同一时刻进行数据修改时，就会发生冰法控制的问题。比如好多人过来操作我们的数据，此时就会有一个表锁的问题，这样当我有时候进行操作会卡在那个地方，让我等待，这就是表锁的体现。

分为共享锁和排他锁，其实就是读锁和写锁。

 

读锁：共享的，不堵塞，多个用户可以读同一个资源，互不干扰

写锁：排他的，一个写锁会阻塞其他的写锁和读锁，这样可以只允许一个人进行写入，防止其他用户读取正在写入的资源。

锁粒度：表锁，系统性能开销最小，会锁定整张表，MyISAM使用表锁

行锁：最大程度地支持并发处理，但是也带来了最大的锁开销，InnoDB实现行级锁

 

事务处理：

Mysql提供数据处理的表引擎是InnoDB

服务器层不管理事务，由下层的引擎实现，所以同一个事务中，使用多种存储引擎不靠谱。

比如说我们操作这两张表的时候，做一个事务，一张表InnoDB，另一张MyISAM这样操作事务是不成功的。

在非事务表上执行事务操作MYSQL不会发出提醒，也不会报错

在我们做事务处理，一定要检查我们的表是不是InnoDB

 

存储过程

为以后的使用保存的一条或多条Mysql语句的集合。

存储过程就是有业务逻辑和流程的集合

可以在存储过程中创建表，跟新数据，删除等等

使用场景：通过帮数据封装在容易使用的单元中，简化复杂的操作。比如说我们在进行一个事务护着操作我们可能进行了上百条SQL语句的操作，这个时候我们可以把他封装起来，想用的时候直接拿过来用就可以了，不用再去写那么多条SQL语句了，保证数据的一致性，简化对变动的管理。

 

触发器：提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊存储过程。

使用场景：通过数据库中相关表实现级联更改（比如说改一张表让他自动去改另一张表）

实时监控某张表的某个字段的更改而需要作出的相应的处理

某些业务编号的生成（比如添加一个数据让他自动生成业务标号）

滥用会造成数据库以及应用程序的维护困难（写的太多了，不知道为什么执行一条语句另一条就执行了）