mysql技巧_MySQL技巧

一、存储引擎

存储引擎是MySQL数据库的核心、心脏、发动机，它决定了数据如何存储，查询的时候如何搜索数据，索引如何创建等等；是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

常用存储引擎

InnoDB：

1. 事务处理、回滚、崩溃修复能力和多版本并发控制

2. 自增长AUTO_INCREMENT

3. 外键(FOREIGN KEY)

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM：

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

Memory：

1. 数据全部放在内存中

2. 哈希索引

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

MySQL 5.1版本之前默认的存储引擎是MyISAM，之后默认是InnoDB

二、数据类型

数值类型

TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255。

INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295。

BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615。

FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。float(5,2) 999.919

DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数。

字符串类型

短文本：char varchar

CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。 char(5) abcde ab 内存空间会浪费，性别：男/女 char(1)，查询匹配规则是所有字节一起匹配，查询性能相对比varchar高

VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如：VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。 5 10 12 节省内存空间 abcde，查询匹配规则是每个字节依次匹配

BLOB 或 TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。

日期和时间类型

DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1973年12月30日将被存储为1973-12-30。

DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-3123:59:59之间。例如，1973年12月30日下午3:30，会被存储为1973-12-30 15:30:00。

TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。

TIME - 存储时间在HH:MM:SS格式。

YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4

三、SQL

1.快速创建表结构相同的表(不含任何数据)：create table t2 like t1

2.查看创建数据库对象的语句：show create  database/database/... 对象名称

3.列举数据库当前对象清单： show databases/tables/...

4.删除对象，判断是否存在防止抛异常：drop table/database... if exists t1

5.'曾改换删'表字段结构：alter table 表名 add/modify/change/drop 列名 (change要加新列名) 列类型(drop不用类型)

6.delete不会影响主键自增长(auto_increment)的当前值，truncate会直接将表自增长的值重置为1，无论是否设置初始值还是增长到哪里了。

7.窗口函数：

sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)

A: 需要被加工的字段名称

B: 分组的字段名称

C: 排序的字段名称

D: 计算的行数范围

rows between unbounded preceding and current row  # 包括本行和之前所有的行

rows between current row and unbounded following # 包括本行和之后所有的行

rows between 3 preceding and current row  # 包括本行和前面三行

rows between 3 preceding and 1 following  # 从前面三行和下面一行，总共五行

# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded

preceding and current row.

# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded        preceding

and unbounded following

切片开窗分组函数：ntile(n)over(partition by order by )

行偏移函数-向上1行：lag(c1,1,c1)over(partition by order by)

行偏移函数-向下1行：laad(c1,1,c1)over(partition by order by)