MySQL 笔记

数据库设计概论

数据表类型

MyISAM
成熟稳定易于管理

InnoDB
是MyISAM的一种更新换代产品
支持事务

HEAP
内存数据库，数据库服务停止运行，数据丢失

数据库文件存放位置

默认：
Linux
/var/lib/mysql
Windows
c:\Programs\MySQL\MySQL Server n.n\data

每个数据表有一个.frm定义文件，位于数据库名字文件夹中
保存数据表的结构定义：列名、数据类型等
data/dbname/tablename.frm
data/dbname/db.opt 整个数据库的结构定义和设置

MyISAM
data/dbname/tablename.MYD 数据表的数据
data/dbname/tablename.MYI 数据表的全部索引

InnoDB
inno_file_per_table配置选项
默认：
data/dbname/tablename.idb 数据表的数据和索引
data/ibdata 表空间和撤销日志
data/ib_logfile InnoDB日志
data/dbname/tablenamb.TRG 触发器

数据类型

整数 xxxINT

如果存入的数据值超出了数据类型的取值范围，则把它替换为最大或最小可取值。

TINYINT(m) 8位整数 -128 ~ 127
m给出的是select查询结果中的数据列宽度（maximum display width），对取值范围没有影响
SMALLINT(m) 16位整数 -32768 ~ 32767
MEDIUMINT(m) 24位整数
INT(m) INTEGER(m) 32位整数
BIGINT(m) 64位整数
SERIAL bigint auto_increment not null primary key 简写

AUTO_INCREMENT整数
当插入一条新纪录时，自动将该列最大值加1赋值给新纪录中的这个字段。
必须与NOT NULL、 PRIMARY KEY、UNIQUE同时使用
最多只能有一个AUTO_INCREMENT数据列
只有使用INSERT插入新纪录并且没有为字段给出一个值时才起作用

获得自动生成的AUTO_INCREMENT值：在本次连接或本次事务里执行
SELECT LAST_INSERT_ID()

如果计数达到最大值，将无法递增，插入操作无法继续进行
对于INSERT DELETE非常频繁的数据表来说最好使用BIGINT

BOOL 是TINYINT的同义词
BIT在5.0.3版本之后为一种可以存储多达64位二进制数的新数据类型。

FLOAT(m,d) 4字节 m 十进制数字的总个数（不影响精度），d小数点后面的数字个数
DOUBLE(m,d) 8字节
REAL(m,d) 同DOUBLE

DECIMAL(p,s)
p数据值的数字总个数（最大值65），s小数点后面的数字个数（最大值30）
比如DECIMAL(6,3)的可表示范围为-999.999~999.999
以字符串的形式来保存数据
FLOAT DOUBLE会进行必要的舍入，如果误差不可接受，比如财务数据，应该选用DECIMAL

日期与时间

DATE
2003-12-31 取值范围：1000-01-01~9999-12-31 3个字节

TIME
23:59:59 3个字节

DATETIME
YEAR 100~2155 1个字节

对日期和时间数据进行合法性检查
sql_mode系统变量
ALLOW_INVALID_DATES
NO_ZERO_DATE
NO_ZERO_IN_DATE

TIMESTAMP
这个类型的字段会在数据记录的其他字段被修改时自动刷新

字符串类型

CHAR(20)
占用20个字节，前导空格存储前将被去掉，比较短的字符串在尾部加空格，读出数据时会自动删除这些空格，因此不能保存结尾确实有空格的字符串

CHAR(n) 固定长度字符串 最多255个字符
VARCHAR(n) 可变长度 最多255个 5.0.3版本后 n<65535 必须指定n
TINYTEXT 最多255个
TEXT 2^16-1
MEDIUMTEXT 2^24-1
LONGTEXT 2^32-1

UNICODE定义了每个字符的编码，但没有定义如何存储这些编码。
因此UNICODE有好几种变体：
UCS-2 universal character set 通用字符集
UTF-8 unicode transfer format Unicode传输格式

UCS-2也叫UTF-16
弊端：字符串空间占用量翻了一倍，字节0会出现在很多地方，而许多C语言程序、电子邮件服务器等都把0字节视为字符串的结束标志。

现在的版本可以为每个数据列分别指定一种字符集和排序方式。

show collation 命令

BLOB

binary large object
存储二进制数据的最佳选择
BLOB和TEXT的区别是二进制数据根据它们的二进制编码进行比较和排序

有不少人认为应该把二进制数据如图像保存为外部文件，数据库只用来保存这些文件的链接。

把短小的数据和长的数据混合存放在同一个数据表里是一种非常不好的做法，会导致所有数据记录的存取速度变慢

BLOB数据必须以一个整体来读写和传输。

BIT(n) n二进制位数，最大取值64 溢出时，所有位都为1
TINYBLOB 255
BLOB
MEDIUMBLOB
LONGBLOB

ENUM和SET

缺点：ENUM和SET 其他数据库可能不支持，不便于移植

ENUM
最多65535个，字段的取值只能是这个集合中的某个成员

SET
允许集合成员的任意组合但不能超过64个

数据列选项

NULL 可以包含NULL值，默认设置
NOT NULL
DEFAULT XXX 在输入时没有给出一个具体值，则使用xxx作为默认值
PRIMARY KEY
AUTO_INCREMENT
UNSIGNED
CHARACTER SET name [COLLATE sort] 指定一种字符集和一种可选的排序方式

数据库设计要求

• 数据表里没有重复冗余的数据
• 数据表里没有order1 order2 order3等数据列（order订单），就算定义了10个这样的数据列，也迟早会发生某个用户想要订购11件商品的事情
• 全体数据表的空间占用总量越小越好
• 使用频率高的数据库查询都能以简单高效的方式执行

MySQL对数据列名字不区分大小写
对数据库和数据表的名字区分大小写

第一次尝试时最好不要立刻去创建和使用真正的MySQL数据表，应该先在Exel等电子表格程序里用一些工作表把MySQL数据表勾勒出来

第一范式
- 内容相似的数据列必须消除（办法是在创建一个数据表来存放它们）
- 必须为每一组相关数据分别创建一个数据表
- 每条数据记录必须用一个主键来标识

第二范式
- 只要数据列里的内容出现重复，就意味着应该把数据表拆分成多个字表
- 拆分形成的数据表必须用外键关联起来

第三范式
与主键没有直接关系的数据列必须消除（再创建一个数据表来存放它们）

如果等到数据库里已经充满了数据，当配套的客户端程序也已经开发完成的时候，才猛然发现数据库设计方案还需要修改，那么将要花费的时间和精力可就太大了。

把信息存入数据库的目的是为了让更多人能够使用SQL查询命令把它们再迅速准确地查出来。

数据表的个数越多，从中提取有关数据生成查询结果的复杂性也越大。为了提高查询的效率，适度的冗余有时反而是必要的。

外键约束

只要数据库里发生了变化，所谓的外键约束条件就会去检查是否有数据表之间的交叉引用关系受到影响。

索引

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。

视图

视图使得人们可以为一个或多个数据表定义一个特殊的表现形式。视图在行为上与数据表没有什么区别，可以使用SELECT查询命令去查询数据，也可以使用INSERT、UPDATE、DELETE命令修改数据。

SQL语言入门

简介

SQL命令可以分为3大类：

DML Data Manipulation Language 数据处理语言
SELECT, INSERT, UPDATE, DELETE

DFL Data Definition Language 数据定义语言
CREATE TABLE, ALTER TABLE

DCL Data Control Language 数据控制语言
GRANT, REVOKE
用来帮助人们设置和调整MySQL访问控制机制的SQL命令。

SELECT

确定数据表里有多少条记录
SELECT COUNT(publID) FROM publishers

确定数据表里有多少条不重复的记录
SELECT COUNT(DISTINCT publID) FROM titles

限制查询结果中的数据记录个数
SELECT title FROM titles LIMIT 2
查询第3和第4条记录LIMIT offset n
SELECT title FROM titles LIMIT 2, 2

ORDER BY

排序
SELECT authName FROM authors ORDER BY authName [DESC]

获得可用的排序方式的清单，命令：
SHOW COLLATION

WHERE

筛选
字符’%’是代表任意字符串的通配符
SELECT authName FROM authors WHERE authName LIKE ‘%er%’

使用LIKE操作符进行的比较操作在比较大数据库表上往往非常慢：
1. 需要读取和分析数据表里的全部数据记录
2. 这类查询无法用索引来优化

使用IN枚举
SELECT authID, authName FROM authors WHERE authID IN (2, 7, 12)

MySQL不支持colname=NULL这样的用法，如果打算对包含NULL值的数据记录进行搜索，就必须使用ISNULL(colname)函数。

涉及多个数据表的关联查询（LEFT/RIGHT JOIN）

GROUP BY等的用法，也参见以下更加通俗易懂的教程
参考：http://www.w3school.com.cn/sql/

返回所有数据库的清单

SHOW DATABASE

返回当前数据库里的所有数据表的清单

SHOW TABLES

返回当前数据表里的所有数据列的清单

SHOW COLUMNS

INFORMATION_SCHEMA数据表家族

使用INFORMATION_SCHEMA数据表家族馋看数据库、数据表、数据列等的元数据。

数据库备份

使用mysqldump命令

较慢，但有着最好的兼容性，适合迁移的场合（从一个MySQL服务器复制到一个MySQL服务器）。

直接复制整个数据库目录

最简单、最快。出于效率和信息安全的考虑，应该先让MySQL服务器停止运行。
只适用于MYISAM数据表，不适用于InnoDB数据表。

把日志文件和定期制作的增量备份文件相结合

利用镜像机制进行备份

其他

飘号的使用

如果你的表名或者是列名和mysql的关键字有冲突，就得写“`”这个符号，否则会报错。

mysql中SELECT column+0 FROM table是什么意思

mysql会根据上下文自动转换类型
这一句的意思就是，将被本来是字符类型的字段值以数值型返回
如果字段存放的值为数值字符，则会转换为数值型返回
如果字段存放的值为字母或者汉字，则直接返回0