1. mysql

文档地址：http://dev.mysql.com/doc/refman/5.7/en/

mysql

1.1. mac 安装 mysql

1.1.1. rpm 包方式安装

Installing MySQL on Linux Using the MySQL Yum Repository
Download MySQL Yum Repository下载rpm包 mysql57-community-release-el6-9.noarch.rpm
安装

rpm -Uvh mysql57-community-release-el6-9.noarch.rpm

查看可以装的列表

yum repolist all | grep mysql
yum repolist enabled | grep mysql

安装

yum install mysql-community-server

1.2. 使用

启动 mysql

sudo service mysqld start

停止 mysql

sudo service mysqld stop

第一次启动的时候会生成 root 用户和默认密码

grep 'temporary password' /var/log/mysqld.log

查看默认 root 密码。

mysql -uroot -p

设置新 root 密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

允许mysql远程访问

修改 bind-address

/etc/mysql/mysql.conf.d/mysqld.cnf 的 bind-address 改成系统IP

修改数据库

mysql -u root –p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;

1.3. 存储引擎

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。
InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。
MySQL存储引擎InnoDB与Myisam的六大区别

1.4. 服务器连接排序规则

Connection Character Sets and Collations
utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。
utf8_unicode_ci 准确度高，但校对速度稍慢。
如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了，到现在也没发现问题。。。
采用 utf8 编码的 MySQL 无法保存占位是4个字节的 Emoji 表情,需要用 utf8mb4

为安全起见，推荐用 utf8mb4_unicode_ci

1.5. 数据库数据类型

Data Types

1.6. Char Varchar text

参考 MySQL之char、varchar和text的设计

char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。
同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
超过char和varchar的n设置后，字符串会被截断。
char的上限为255字节，varchar的上限65535字节，text的上限为65535。
char在存储的时候会截断尾部的空格，varchar和text不会。
varchar会使用1-3个字节来存储长度，text不会。

总体来说：

char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。
varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。
text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。

1.7. 外键

思考大家设计数据库时使用外键吗？

主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性
外键：是另一表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。所以说，如果谈到了外键，一定是至少涉及到两张表。

ON DELETE

RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。
CASCADE（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删掉。
SET NULL：将外键设置为空。
NO ACTION：什么都不做。

1.8. FLOAT DOUBLE DECIMAL

float、double(浮点数)区别还有和decimal(定点数)的比较

MySQL浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位）. 格式是：1位符号位 8位表示指数 23位表示尾数
double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）. 格式是：1位符号位 11位表示指数 52位表示尾数
decimal:数字型，128bit，不存在精度损失，常用于涉及货币或其他精度敏感的数据。（28个有效位）M:1～65, D:0～30

double 和 float 的区别是double精度高，有效数字16位，float精度7位。但double消耗内存是float 的两倍，double的运算速度比float慢得多

1.9. NULL VS ''

空值不一定为空

TimesTamp 当前时间
auto_increment属性的列会插入一个正整数序列

空值不一定等于空字符

1.10. 连接查询

在没有 ON 的情况下交叉连接与内连接是一样的，有 ON 的时候就是内连接，无 ON 就是交叉连接。

table1 CROSS JOIN table2 = table1 JOIN table2 = table1, table2 = table1 INNER JOIN table2

1.10.1. on VS where

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回临时表的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。

1.10.2. 笛卡尔积(交叉连接)

加条件只能用where,不能用on
不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积
有where子句，往往会先生成两个表行数乘积的数据表，然后才根据where条件从中选择。

SELECT * FROM table1 CROSS JOIN table2   
SELECT * FROM table1 JOIN table2   
SELECT * FROM table1,table2

1.10.3. 内连接 INNER JOIN

只列出匹配的记录。

使用比较运算符（包括=、>、<、<>、>=、<=、!>和!<）进行表间的比较操作

select * from TableA a inner join TableB b on a.b_id = b.id

1.10.4. 外连接

左外连接 LEFT [OUTER] JOIN

左表列出全部，右表只列出匹配的记录。

select * from TableA a left join TableB b on a.id = b.id

右外连接 RIGHT [OUTER] JOIN

右表列出全部，左表只列出匹配的记录。

select * from TableA a right join TableB b on a.id = b.id

完全外连接 FULL [OUTER] JOIN

完全外连接包含full join左右两表中所有的行，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。

select * from TableA a full join TableB b on a.id = b.id

自连接

参与连接的表都是同一张表。(通过给表取别名虚拟出两张表)

1.11. DATETIME VS TIMESTAMP

DATETIME 范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，
TIMESTAMP 范围为1970-01-01 08:00:01到2038-01-19 11:14:07。
TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

1.12. sql子查询

嵌套SELECT语句

1.12.1. 用于替代表达式的子查询

参考用于替代表达式的子查询.aspx)

在 Transact-SQL 中，除了在 ORDER BY 列表中以外，在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何能够使用表达式的地方都可以用子查询替代。

以下示例说明如何使用此增强功能。此查询找出所有山地车产品的价格、平均价格以及两者之间的差价。

USE AdventureWorks2008R2;
GO
SELECT Name, ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS Average,
    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)
    AS Difference
FROM Production.Product
WHERE ProductSubcategoryID = 1;

1.12.2. 使用子查询原则

一个子查询必须放在圆括号中。
将子查询放在比较条件的右边以增加可读性。子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句，并且如果指定了它就必须放在主 SELECT 语句的最后。
在子查询中可以使用两种比较条件：单行运算符(>, =, >=, <, <>, <=) 和多行运算符(IN, ANY, ALL)。

1.13. and or not

sql语句中not and or的执行优先级从高到低依次为:not,and,or

select * from table where a=1 and b=2 or c=3 and d=4;

select * from table where (a=1 and b=2) or (c=3 and d=4);

mysql