MySQL数据类型及sql模型

奥德赛F9

MySQL数据类型及sql模型　　

　　
存储引擎，也被称为表类型：
　　
MyISAM表: 无事务，表锁
　　
    .frm: 表结构定义文件
　　
    .MYD: 表数据
　　
    .MYI: 索引
　　

　　
InnoDB表：事务，行锁
　　
    .frm: 表结构
　　
    .ibd: 表空间（数据和索引）
　　
    所有表共享一个表空间文件；
　　
    建议：每表一个独立的表空间文件；(修改配置文件在[mysqld]项下加入innodb_file_per_table = 1)
　　

　　
MySQL:
　　
    在默认的mysql数据库中默认的存储引擎为MyISAM
　　
    其他新建的数据库的存储引擎取决于默认的存储引擎设定值
　　

　　
SHOW ENGINES
　　
SHOW TABLE STATUS [{FROM | IN} db_name]
　　
    [LIKE 'pattern' | WHERE expr]
　　

　　
程序语言连接数据的方式：
　　
    动态SQL：通过函数或方法与数据库服务建立连接，
　　
    嵌入式SQL：
　　

　　
客户端组件：mysql、mysqladmin、mysqldump、mysqlimport、mysqlcheck
　　
服务器组件：mysqld, mysqld_safe, mysqld_multi
　　

　　
my.cnf
　　

　　
/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somefile --> ~/.my.cnf
　　

　　
[mysqld]
　　
[mysqld_safe]
　　
[client]
　　
host =
　　
[mysql]
　　
绝大数配置选项都能在命令行下直接指定
　　
# mysqld --help --verbose
　　

　　
在指定datadir = /mydata/data后，在指定的目录下有名为hostname.err: 错误日志
　　

　　
MySQL启动失败的原因：
　　
1、此前服务未关闭；
　　
2、数据初始化失败；
　　
3、数据目录位置错误；
　　
4、数据目录权限问题；
　　

　　
DBA:
　　
    开发DBA：数据库设计、SQL语句、存储过程、存储函数、触发器
　　
    管理DBA：安装、升级、备份、恢复、用户管理、权限管理、监控、性能分析、基准测试
　　

　　
数据类型：
　　
    数值型
　　
        精确数值
　　
            int
　　
            decimal
　　
        近似数值
　　
            float
　　
            double
　　
            real
　　
    字符型
　　
        定长：CHAR(#)、BINARY
　　
        变长：VARCHAR(#)、VARBINARY
　　
        text, blob
　　

　　
        ENUM, SET
　　

　　
    日期时间型
　　
        date, time, datetime, timestamp
　　

　　
数据类型：
　　
1、存入的值类型；
　　
2、占据的存储空间；
　　
3、定长还变长；
　　
4、如何比较及排序；
　　
5、是否能够索引；
　　

　　
数据类型总结：
　　
整型：
　　
类型          字节       最小值                          最大值
　　
TINYINT        1         有符号-128，无符号0             有符号127，无符号255
　　
SMALLINT       2         有符号-32768，无符号0           有符号32767，无符号65535
　　
MEDIUMINT      3         有符号-8388608，无符号0         有符号8388607，无符号1677215
　　
INT和INTEGER   4         有符号-2147483648，无符号0      有符号2147483647，无符号4294967295
　　
BIGINT         8         有符号-9223372036854775808，无符号0 有符号9223372036854775807，无符号18446744073709551615
　　

　　
浮点型：
　　
类型          字节       最小值                          最大值
　　
FLOAT          4
　　
DOUBLE         8
　　
说明：当需要精确到小数点后10位以上，使用DOUBLE类型
　　

　　
定点数：
　　
类型          字节       最小值                          最大值
　　
DEC(M,D)和DECIMAL(M,D)  M+2
　　
说明：有效取值范围由M和D确定，浮点型存储的是近似值，定点数型存储的是字符串
　　

　　
位类型：
　　
类型          字节       最小值                          最大值
　　
BIT(M)         1-8        BIT(1)                          BIT(64)
　　
说明：字节数是M，取值范围为1-8
　　

　　
日期时间型：
　　
类型          字节       最小值                          最大值
　　
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         -835:59:59                      838:59:59
　　
YEAR           1          1901                             2155
　　

　　
字符串类型：
　　
CHAR系列：
　　
类型          字节      描述
　　
CHAR(M)        M        M为0-255的整数
　　
VARCHAR(M)     M         M为0-65535的整数
　　
说明：VARCHAR长度可变
　　
TEXT类型：
　　
类型          字节            描述
　　
TINYTEXT      0-255           值的长度为+2个字节
　　
TEXT          0-65535         值的长度为+2个字节
　　
MEDIUMTEXT    0-167772150     值的长度为+3个字节
　　
LOANGTEXT     0-4294967295    值的长度为+4个字节
　　
说明：用于存储大量字符串
　　
BINARY系列：
　　
类型          字节            描述
　　
BINARY(M)          M              允许长度为0-M
　　
VARBINARY(M)     M              允许长度为0-M
　　
说明：存储二进制数据，VARBINARY长度可变
　　
BLOB系列：
　　
类型          字节
　　
TINYBLOB      0-255
　　
BLOB          0-2^16
　　
MEDIUBLOB     0-2^24
　　
LOANGBLOB     0-2^32
　　
说明：存储大量的二进制数据
　　

　　
mysql> SHOW CHARACTER SET;       查看当前服务器支持的所有字符集
　　
mysql> SHOW COLLATION;             查看当前服务器支持的所有排序规则
　　

　　
修饰符：
　　
AUTO_INCREMENT  自动增长（对于整型）默认从一开始，步长为一。不能为空
　　
AUTO_INCREMENT
　　
    整型
　　
    非空
　　
    无符号（只能为正数）UNSIGNED
　　
    主键或惟一键
　　
mysql> SELECT LAST_INSERT_ID();显示上一次自动增长数值
　　

　　

　　
SQL模型
　　
    常用：
　　
    ANSI QUOTES -—A double quote (") is now treated as an identifier quote (`).
　　
                    Strings can only be quoted with a single quote (’)
　　
    IGNORE_SPACE—-This sql_mode allows a space between a built-in function and the open parenthesis
　　
    STRICT_ALL_TABLES—-When this sql_mode is not set, invalid data values are allowed,
　　
                        usually converted to valid data by using zero values or truncating the value.
　　
                        When this sql_mode is set, invalid data values are rejected, and an error is thrown.
　　
    STRICT_TRANS_TABLES -- invalid data values in a transactional table are rejected, and an error is thrown
　　
    TRADITIONAL—-Syntax and behavior closely match traditional expectations
　　

　　
查询sql模型：
　　
mysql>show global variables like 'sql_mode';
　　

　　
MySQL服务器变量
　　
    按作用域，分为两类：
　　
        全局变量
　　
            SHOW GLOBAL VARIABLES
　　
        会话变量
　　
            SHOW [SESSION] VARIABLES
　　

　　
    按生效时间，分为两类：
　　
        动态：可即时修改
　　
        静态：
　　
            写在配置文件中
　　
            通过参数传递给mysqld（或mysqld_safe）进程
　　

　　
    动态调整参数的生效方式：
　　
        全局：对当前会话无效，只对新建立会话有效；
　　
        会话：即时生效，但只对当前会话有效；
　　

　　

　　
    服务器变量：@@变量名
　　
        显示：SELECT  @@global.sql_mode
　　
        设定：SET GLOBAL|SESSION 变量名='value'