mysql 基本操作 数据类型、常用函数、存储引擎特点 （第二章）

Mysql数据类型：数值类型

整型数据，MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满

整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性

对于 BIT（位）类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M 范围从 1～ 64，如果不写则默认为 1 位。对于位字段，直接使用 SELECT 命令将不会看到结果，可以用 bin()（显示为二进制格式）或者 hex()（显示为十六进制格式）函数进行读取

日期时间类型：

日期和时间类型

 字节

最小值

最大值

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

字符串类型：

字符串类型

字节

描述及存储需求

CHAR（M）

M

M 为 0～255 之间的整数

VARCHAR（M）

 

M 为 0～65535 之间的整数，值的长度+1 个字节

TINYBLOB

 

允许长度 0～255 字节，值的长度+1 个字节

BLOB

 

允许长度 0～65535 字节，值的长度+2 个字节

MEDIUMBLOB

 

允许长度 0～167772150 字节，值的长度+3 个字节

LONGBLOB

 

允许长度 0～4294967295 字节，值的长度+4 个字节

TINYTEXT

 

允许长度 0～255 字节，值的长度+2 个字节

TEXT

 

允许长度 0～65535 字节，值的长度+2 个字节

MEDIUMTEXT

 

允许长度 0～167772150 字节，值的长度+3 个字节

LONGTEXT

 

允许长度 0～4294967295 字节，值的长度+4 个字节

VARBINARY（M）

 

允许长度 0～M 个字节的变长字节字符串，值的长度+1 个字节

BINARY（M）

M

允许长度 0～M 个字节的定长字节字符串

CHAR 和 VARCHAR二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0～255 的任何值；而 VARCHAR 列中的值为可变长字符串，长度可以指定为 0～255（5.0.3 以前）或者 65535（5.0.3 以后）之间的值。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格

 

ENUM 类型：

ENUM 中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～ 255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。最多允许有 65535 个成员。下面往测试表 t 中插入几条记录来看看 ENUM 的使用方法。

create table t (genderenum('M','F')); INSERT INTO t VALUES('M'),('1'),('f'),(NULL);

Set类型：

Set 和 ENUM 类型非常类似，也是一个字符串对象，里面可以包含0～64 个成员

Createtable  t (col   set （'a','b','c','d'）; insert into t  values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');

 

常用字符串函数：

函数

功能

CANCAT(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 个字符长度的字串

数值函数

函数

功能

ABS(x)

返回 x 的绝对值

CEIL(x)

返回大于x的最大整数值

FLOOR(x)

返回小于 x 的最大整数值

MOD(x，y)

返回 x/y 的模

RAND()

返回 0 到 1 内的随机值

ROUND(x,y)

返回参数 x 的四舍五入的有 y 位小数的值

TRUNCATE(x,y)

返回数字 x 截断为 y 位小数的结果

日期函数：

函数

功能

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 之间的天数

流程函数：

函数

功能

IF(value,t f)

如果 value 是真，返回 t；否则返回 f

IFNULL(value1,value2)

如果 value1 不为空返回 value1，否则返回 value2

CASE WHEN [value1]

THEN[result1]…ELSE[default]END

如果 value1 是真，返回 result1，否则返回 default

CASE [expr] WHEN [value1]

THEN[result1]…ELSE[default]END

如果 expr 等于 value1，返回 result1，否则返回 default

其他常用函数

函数

功能

DATABASE()

返回当前数据库名

VERSION()

返回当前数据库版本

USER()

返回当前登录用户名

INET_ATON(IP)

返回 IP 地址的数字表示

INET_NTOA(num)

返回数字代表的 IP 地址

PASSWORD(str)

返回字符串 str 的加密版本

MD5()

返回字符串 str 的 MD5 值

MyISAM特点：

Select和insert使用，支持表锁，内存和空间使用率低，支持全文引擎，

存储类型：.frm 存储表定义，.MYI存储表引擎，MYD存储表数据

数据文件和索引文件可以放在不同目录，在平均I/0，获得更快的速度。在创建表的时候，DATE DIRECTORY 和 INDEX DIRECTORY语句指定，路径是需要绝对路径，并且要有访问权限      表损坏的时候，可以check table来检查表是否健康，repair table 修复表损坏的文件

InnoDB 特点：

具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM 表，自动增长列可以是组合索引的其他列

只有InnoDB支持外键，创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引