一、数值类型
1. 数值类型
标准sql中包含数据类型 INTEGER, SMALLINT, DECIMAL, NUMERIC，以及FLOAT, REAL, DOUBLE。mysql在此基础上进行了拓展，增加了TINYINT, MEDIUMINT, BIGINT三种长度不同的整数，以及BIT类型，用来存放位数据。
| 整数类型 | 字节 |
|---|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT/INTEGER | 4 |
| BIGINT | 8 |
| 浮点数类型 | 字节 |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
| 定点整数类型 | 字节 |
|---|---|
| DEC(M,D)/DECIMAL(M,D) | M+2 |
(1.1) 整数类型
整数类型，mysql支持在类型名称后面的小括号内指定显示宽度，如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如果不显式指定宽度则默认为int(11),这一般配合zero_fill一块使用，如
create table t1(id int(5) zerofill);#表t1中的id列宽度为5位整数，如果数据位数小于5，则在前面添加0；如果数据位数大于5，仍然显示正确的数据，即不会受到数据位数的限制，只要保证该数据是个合法的4字节可以表示的整数。
整数类型有unsigned属性，如果一个列指定为zerofill，则mysql会自动给该列添加unsigned属性。
整数类型有uto_increment属性一般从1开始，每行增加1，在插入null到一个auto_increment列时，mysql插入一个比该列中当前最大值大1的值。一个表中最多只能有一个auto_increment列。对于任何想要使用auto_increment的列，应该定义为not null，并定义为primary key或定义为unique键。比如:
create table ai (id int auto_increment not null primary key);
create table ai(id int auto_increment not null, primary key(id));
create table ai(id int auto_increment not null, unique(id));
(1.2) 浮点数类型和定点数类型
定点数在mysql内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。浮点数和定点数都可以用类型名称后加"(M,D)"的形式来表示，M为整数加小数部分的总位数，D为小数部分的位置。float， double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统来决定）来显示，decimal在不指定精度时，默认的整数为为10，默认的小数为为0.
比如用默认的decimal来存放数据1.23， 则实际只会保留整数位1，小数位的.23被丢弃。
(1.3) BIT类型
bit位类型用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1到64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()（显示为二进制格式）或者hex()（显示为十六进制格式）函数进行读取。
2. 日期和时间类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 19700101080001 | 2038年的某个时刻 |
| TIME | 3 | -838:59:59 | 838:59:59 |
| YEAR | 1 | 1901 | 2155 |
TIMESTAMP值返回后显示为 YYYY-MM-DD HH:MM:SS格式的字符串，显示宽度固定为19个字符；如果想要获得数字值，在TIMESTAMP列添加"+0"即可。如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示；TIMESTAMP还和时区相关，插入日期时，会先转化为本地时区后存放，而从数据库取出时，也同样需要经日期转换为本地时区后显示。
3. 字符串类型
mysql包含CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET等多种字符串类型。
(3.1) CAHR和VARCHAR类型
CHAR 长度为创建表时声明的长度，0-255；VARCHAR为可变长字符串。在检索时，CHAR列删除了尾部的空格，而VARCHAR保留这些空格。比如：
create table vc(v varchar(4), c char(4));
insert into vc values('ab', 'ab');
select length(v), length(c) from vc;#结果为4， 2
select concat(v, '+'), concat(c, '+') from vc; #显示为 ab +和 ab+.
即，char列最后的空格在做操作时都已经被删除，而varchar依然保留空格。
(3.2) BINARY和VARBINARY类型
BINARY和VARBINARY包含二进制字符串而不包含非二进制字符串。当保存BINARY值时，在值的最后通过填充"0x00"以达到指定的字段定义长度。对于一个BINARY(3)列，插入'a'时，变为'a '.
(3.3) ENUM类型
1~255个成员的枚举需要1个字节，255~65535个成员的枚举需要2个字节，最多允许65535个成员。枚举类型的成员为 字符串类型：1个或多个字符。比如：
create table t(en enum('hello','world'));
(3.4) SET类型
SET类型也是一个字符串对象，里面可以包含0~64个成员，每个成员对应一个比特位，即最多有8个比特位。
SET类型一次可以选取多个成员，而ENUM只能选一个。比如，在表t中插入多组不同的成员：
create table t(col set('a','b','c','d'));
insert into t values('a,b'), ('c,d'), ('a, c, d'); #一次插入多个记录
二、算术运算符
mysql支持算术运算符、比较运算符、逻辑运算符、位运算符。
算术运算符
包括 +,-,*,/,%(mod)。在mysql命令行下，通过select expression 来得到表达式的值。
比较运算符
包括=, <>或!=, <=>, <, <=, >, >=, between, in, is null, is not null, like, regexp或rlike.
其中<=>判断左右两边是否相等，其中一个操作数是null也可以正确执行。
is null判断操作数是否为空，如select a is null; 返回1表示true，0表示false。select 0 is null; #返回0，因为数字0不是null
like运算符使用格式为a like %123%，其中%为通配符。
regexp使用格式为str regexp str_pat，当str字符串中含有str_pat相匹配的字符串时，返回值为1，否则返回0.
逻辑表达式
包括not或者!, and或者&&, or或者||, xor（异或).
位运算符
包括 &, |, ~, ^, >>, << 等。
运算符优先级
和c/c++有差别，比如位运算符的优先级貌似比较高，需要在使用时注意。
三、 常用函数
1. 字符串函数
| 函数 | 功能 |
|---|---|
| concat(s1,s2....sn) | 连接s1,s2...sn为一个字符串 |
| insert(str, x, y, instr) | 将字符串str从第x个位置开始，y个字符长度的子串替换为字符串instr |
| lower(str) | 将字符串str中所有字符变为小写 |
| upper(str) | 将字符串str中所有字母变为大写 |
| left(str, x) | 返回字符串str最左边的x个字符 |
| right(str,x) | 返回字符串str最右边的x个字符 |
| lpad(str,n,pad) | 用字符串pad对str最左边进行填充，直到长度为n个字符串长度 |
| rpad(str,n,pad) | 用字符串pad对str最右边进行填充，直到长度为n个字符串长度 |
| ltrim(str) | 去掉字符串str左侧的空格 |
| rtrim(str) | 去掉字符串str右侧的空格 |
| repeat(str,x) | 返回str重复x次的结果 |
| replace(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
| strcmp(s1,s2) | 比较字符串s1和s2 |
| trim(str) | 去掉字符串行尾和行头的空格 |
| substring(str, x, y) | 返回从字符串str x位置起y个字符长度的字串 |
2. 数值函数
| 函数 | 功能 |
|---|---|
| abs(x) | 返回x的绝对值 |
| ceil(x) | 返回大于x的最小整数值 |
| floor(x) | 返回大于等于x的最小整数值 |
| mod(x,y) | 返回x/y的模 |
| rand() | 返回参数x的四舍五入的有y位小数的值 |
| round(x,y) | 返回参数x的四舍五入的有y位小数的值 |
| truncate(x,y) | 返回数字x截断为y位小数的结果 |
3. 日期和时间函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| unix_timestamp(date) | 返回日期date的unix时间戳 |
| from_unixtime | 返回unix时间戳的日期值 |
| week(date) | 返回date为一年中的第几周 |
| year(date) | 返回date的年份 |
| hour(time) | 返回time的小时值 |
| minute(time) | 返回time的分钟值 |
| monthname(date) | 返回date的月份名 |
| date_format(date, fmt) | 返回按字符串fmt格式化日期date值 |
| date_add(date interval expr type) | 返回一个日期或者时间值加上一个时间间隔的时间值 |
| datediff(expr, expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
4. 流程函数
| 函数 | 功能 |
|---|---|
| if(value,t f) | 如果value是真，返回t；否则返回f |
| ifnull(value1,value2) | 如果value1不为空，返回value1，否则返回value2 |
| case when [value1] then [result] .... else [default] end | 如果value1为真，返回result1，否则返回default |
| case [expr] when [value1] then [result]...else [default]end | 如果expr等于value1，返回result，否则返回default |
比如，有一张表t，存放雇员的id和薪水salary。认为薪水在2000以上的为高收入，记为high，2000以下的为低，记为low。
select user_id, if(salary > 2000, 'high', 'low') from t;#得到user_id和工资水平高低的对应
或者可以这样
select user_id, case when salary<=2000 then 'low' else 'high' end from t;
ifnull(value1, value2)一般用来替换null值，由于null值不能参与数值运算，该语句就是把null值用0来替换。
select ifnull(salary, 0) from t;#列出所有的salary，如果salary那一列为空，则显示0.
5. 其他常用函数
| 函数 | 功能 |
|---|---|
| database() | 返回当前数据库名 |
| version() | 返回当前数据库版本 |
| user() | 返回当前登录用户名 |
| inet_aton(ip) | 返回ip地址的网络字节序数字表示 |
| inet_ntoa(num) | 返回数字代表的ip地址 |
| password(str) | 返回字符串str的加密版本 |
| md5(str) | 返回字符串str的md5值 |
其中，字符串的加密版本password(str)返回的是一个41字节长的字符串；md5(str)返回str的md5值，常用来对应用中的数据进行加密。