MySQL

[magicdawn]

mysql collection.

resources

• http://shlomi-noach.github.io/awesome-mysql/
• https://github.com/shlomi-noach/awesome-mysql/blob/gh-pages/index.md

video

• mooc http://www.imooc.com/learn/122

blogs

• TODO

[magicdawn]

# start server
mysqld

# start client
mysql -u root -p password -P 3306 -h localhost

## more options
## --prompt 修改 prompt 提示符

# 所有的 databases
show databases;

# 切换数据库
use <db_name>;

# 查看所有的表
show tables;

# 查看所有的列信息, 方式1
show columns from <table_name>; 

# 查看表格所有列的信息
describe <table_name>; 

[magicdawn]

# database
create database if not exists <name>;

# table
create table if not exists <table_name>(
  <column_name> <column_type>,
  ...
);

column types

int

标准SQL中有 INTEGER & SMALLINT, MySQL 中有

type	bytes
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT / INTEGET	4
BIGINT	8

显示长度

• 例如写 TINYINT(4) 表示显示4位，TINYINT 有符号时最大为 -128, 且符号占1，长度为4
• 写上 INT(4), 插入一个长度为5的 11111, 依然可以正常显示出来

浮点型

type	bytes
FLOAT	4
DOUBLE	8
DECIMAL(M, D)	M+2

• M 是精度, 总长度, 不包括小数点
• D 是小数点后的长度
• FLOAT(6, 2) / DOUBLE(6, 2) / DECIMAL(6, 2) 都表示精度为6，保留2位小数. 插入的时候如果不符合, FLOAT(6,2) &DOUBLE(6,2)会自动四舍五入, 而DECIMAL(6, 2)` 则会给出 warning
• DECIMAL 默认精度为10, 保留0位小数. 插入小数时截断处理 & 给出warning

date & time

type	bytes	取值范围	0值
YEAR	1	1901 - 2155	0000
DATE	4	1000-01-01 - 9999-12-31	``0000-00-00
TIME	3	...	00:00:00
DATETIME	8	...	0000-00-00 00:00:00
TIMESTAMP	4	...	0000000000000000

字符串类型

• char(n) / varchar(n) UTF8字符集, varchar(3) 插入中文 "张三丰" 是没有问题的。
• TEXT

types	长度	占用空间
TINYTEXT	0 - 255	...
TEXT	0 - 65535	...
MEDIUMTEXT	0 - 1 6777 2150	...
LONGTEXT	0 - 42 9496 7295	...

• ENUM(“A”, "B", "C") / SET("A", "B", "C") 枚举 & 集合不多说了

二进制类型

type	取值
BINARY(M)	占M个字节, 长度为 0 - M
VARBINARY(M)	同上, 实际占用字节数根据内容变化
BIT(M)	M位
TINYBLOB	可变长, 最多 255 字节
BLOB	最多(2^16 -1)字节
MEDIUMBLOB	最多(2^24 -1)字节
LONGBLOB	最多(2^32 -1)字节

• 2^10 Byte = 1KB;
• 2^20 Byte = 1MB
• 2^24 Byte = 16MB
• 2^30 Byte = 1GB
• 2^32 Byte = 4GB 哈哈, LongBlob 4GB往数据库存, 画面太美 😂

约束

• NULL / NOT NULL
• primary key 主键约束
• foreign key 外键约束
• unique key 唯一性约束
• default value 默认值约束

自增( auto_incrment )

create table tb1(
  id int auto_increment
);

类型必须为
int / float(x, 0) / double(x, 0) 小数位数必须为0

[magicdawn]

select

SELECT 属性列表
FROM 表名和视图列表
[WHERE 条件]
[GROUP BY 属性列表 [HAVING 条件]]
[ORDER BY 属性 [ASC|DESC]] -- 默认 ASC 升序
[LIMIT [offset ,] count]

属性列表

• 属性
• 聚合函数, count() sum avg min max
• case 语句

可以使用 DISTINCT 属性 进行去重处理

case 语句

case语句可作为一个属性列

CASE
    WHEN 条件1 THEN 值1
    WHEN 条件2 THEN 值2
    ELSE 值n
END

where

运算符

• > >= < <= = !=
• 其他比较奇怪的 <> = !=, 还有 !> = '<=' ...

字符串

• 属性 [not] LIKE "值"
  • 若是常量, LIKE 可换为 =
  • % 表示任意个字符
  • _ 表示一个字符
• 属性 REGEXP "值" 使用正则表达式匹配

in

1. [not] in (值1, 值2)
2. [not] in 单列子查询

between ... and ...

[not] between value1 and value2

IS [not] NULL

注意 IS [not] NULL 是一个整体, IS 不可换为 =

子查询

in 子查询

属性 in (单列结果子查询)

运算符 子查询

子查询结果是一个标量结果, 单值

where score > (select avg(score) from students)

exists 子查询

where [not] exists (子查询)

判断子查询是否有结果

any/all 子查询

where score > any (子查询)

where score > all (子查询)

任意 / 所有, 都满足where条件

group by

group by 属性列表

• 之所以说是属性列表, 如 a,b, 先按 属性a分组, 然后再按 b 分组, a b 均相同的分为一组
• GROUP_CONCAT(属性) 将组内属性名用 , join的结果
• Having 条件同 where

LIMIT

• offset 叫 start 更合适, 即是从 0 开始的 start, 可省略
• count 行数

UNION

将两个查询结果合并

select name from tb1
union
select name from tb2

• union 会进行去重处理
• union all 只是简单的拼接, 不去重

[magicdawn]

node-mysql

1. createConnection
2. connect
3. query
4. end

• 若调用 end 后, 再次 connect, Cannot enqueue Handshake after invoking quit.
• 若调用 connect 后, 再次 connect, Cannot enqueue Handshake after already enqueuing a Handshake.

mysql.escape

拼 sql 可以使用, mysql.escape

> mysql.escape('-- select name')
'\'-- select name\''
> s = _
'\'-- select name\''
> cl
clearImmediate  clearInterval   clearTimeout    cluster

> console.log(s)
'-- select name'
undefined
> mysql.escape("-- select name")
'\'-- select name\''

mysql.escapeId

对 identifier 进行 escape

mysql.format

var sql = "SELECT * FROM ?? WHERE ?? = ?";
var inserts = ['users', 'id', userId];
sql = mysql.format(sql, inserts);

[magicdawn]

waterline + sails-mysql

[magicdawn]

Sequelize

• http://docs.sequelizejs.com/en/latest/

[magicdawn]

索引

index method

method	引擎
BTree	InnoDB / MyISAM / MEMORY
Hash	MEMORY

index type

• normal 可建立在任意数据类型上, 不包含完整性约束.
• unique 唯一性索引. 主键是一种特殊的唯一性索引
• fulltext 能创建在 char / varchar / text 类型数据列上. 只有 MyISAM 引擎支持.
• 单列索引
• 多列索引
• spatial 空间索引
  • 能创建在 GEOMETRY / POINT / LINESTRING / POLYGON.
  • 被索引的字段不能为空
  • 只有使用了第一列的查询才能使用此多列索引

normal & fulltext 区别

• http://zhidao.baidu.com/link?url=aQRw9xIzz8DjN0KUkX-F-9RO-RhVZHFjQQMLeeVxUwe43jlBBcGhMzj83JSkLNL8jX9O-ZeJFKlaEy7O7a5q-g6gJih7HAbrpNOQ79PYy8C

普通索引的结构主要以B+树和哈希索引为主，用于实现对字段中数据的精确查找，比如查找某个字段值等于给定值的记录，A=10这种查询，因此适合数值型字段和短文本字段
全文索引是用于检索字段中是否包含或不包含指定的关键字，有点像搜索引擎的功能，其内部的索引结构采用的是与搜索引擎相同的倒排索引结构，其原理是对字段中的文本进行分词，然后为每一个出现的单词记录一个索引项，这个索引项中保存了所有出现过该单词的记录的信息，也就是说在索引中找到这个单词后，就知道哪些记录的字段中包含这个单词了。因此适合用大文本字段的查找。

创建

建表时

create table table_name(
  属性 类型 约束,
  ...
  [UNIQUE | FULLTEXT | SPATIAL] KEY|INDEX [index别名] (
    属性 [(长度)] [ASC | DESC]
  )
)

• 长度: 可选参数, 指索引长度, 必须字符串才可以使用

新创建

create [UNIQUE | FULLTEXT | SPATIAL] KEY|INDEX [index别名] 
on 表名 (
    属性 [(长度)] [ASC | DESC]
)

使用 alter table 创建

alter table 表名 
add [UNIQUE | FULLTEXT | SPATIAL] KEY|INDEX [index别名] (
  ...
)

删除

drop index index_name on table_name;

[magicdawn]

group by

单独使用 group by

如果不使用聚合函数, 那么分组后显示组内第一条记录
分组计数可以使用 count(*)

with rollup

加一条汇总记录

[magicdawn]

mysql 函数

[magicdawn]

datetime 相关

http://www.jb51.net/article/23966.htm