第二章：
1.sql分类
DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。
常用的语句关键字主要包括 create、drop、alter等。
DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字。
主要包括insert、delete、udpate和select等
DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等 。个人感觉主要是运维大哥去管。grant和revoke用法：http://m.blog.csdn.net/article/details?id=7487519
（1）DDL:
1.把某列放到最前面alter table Persons modify City varchar(255) first;
（2）DML:
1.插入多条：
insert into
Persons (City, Id_P, LastName, FirstName, Address)
values
(1,1,1,1,1),
(2,2,2,2,2),
(3,3,3,3,3);
2.查询不重复的记录：select distinct * from Persons;
3.排序：select * from Persons Oder by City desc, Id_P asc;
4.问：having和where区别？
having 是对聚合后的结果进行条件的过滤,而 where 是在聚 合前就对记录进行过滤,如果逻辑允许,我们尽可能用 where 先过滤记录,这样因为结果 集减小,将对聚合的效率大大提高,最后再根据逻辑看是否用 having 进行再过滤。
5.with rollup:对分类聚合后的结果进行再汇总select City ,count(1) from Persons group by City with rollup;
6.表连接：
内连接（inner join）：仅选出两张表中互相匹配的记录
外连接：会选出其他不匹配的记录 ，分为：
左连接(left join)：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接(right join)：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
问：inner和left区别，怎么选择？
7.子查询
关键字：in, not in, =, !=, exists, not exists
8.联合
union和union all区别？
9.查找帮助
? content; ? Data Types;
第三章：数据类型
|
整数类型 |
字节 |
最小值 |
最大值 |
|
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 |
±1.175494351E-38 |
±3.402823466E+38 |
|
DOUBLE |
8 |
±2.2250738585072014E-308 |
±1.7976931348623157E+308 |
|
定点数类型 |
字节 |
述 |
|
|
DEC(M,D),DECIMAL(M,D) |
M+2 |
最大取值范围与 DOUBLE相同,给定 DECIMAL的有效取值范围由 M和 D决定 |
|
|
位类型 |
字节 |
最小值 |
最大值 |
|
BIT(M) |
1~8 |
BIT(1) |
|
|
日期和时间类型 |
字节 |
最小值 |
最大值 |
|
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个字节的定长字节字符串 |
insert into Persons (var, cha) values("ab ", "ab ");
select length(var), length(cha) from Persons;
枚举Enum:忽略大小写，不存在的值，插入枚举的第一值，只能选择一个值
集合set：可选多个值，重复成员只读一次，"a,v,a"，结果为"a,v"
第四章：运算符号
1.<=>:等于，安全比较，可以比较null
<>: 不等于
is null: 为null
is not null:不为null
like:通配符，是否含有该子串 a like "%12%"
2.位运算符
|
运算符 |
作用 |
|
& |
位与(位 AND) |
|
| |
位或 (位 OR) |
|
^ |
位异或(位 XOR) |
|
~ |
位取反 |
|
>> |
位右移 |
|
<< |
位左移 |
第五章：常用函数
1.字符串常用函数
|
函数 |
功能 |
|
CANCAT(S1,S2,...Sn) |
连接 S1,S2,...Sn为一个字符串， 注：任何字符串和null连接都是null |
|
INSERT(str,x,y,instr) |
将字符串 str从第 x位置开始,y个字符长的子串替换为字符串 instr |
|
LOWER(str) |
将字符串 str中所有字符变为小写 |
|
UPPER(str) |
将字符串 str中所有字符变为大写 |
|
LEFT(str ,x) |
返回字符串 str最左边的 x个字符, null则不返回任何字符串 |
|
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, 比较的是ascII码大小 |
|
TRIM(str) |
去掉字符串行尾和行头的空格 |
|
SUBSTRING(str,x,y) |
返回从字符串 str x位置起 y个字符长度的字串 |
2.数值函数
|
函数 |
功能 |
|
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位小数的结果 |
3.日期函数
|
函数 |
功能 |
|
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之间的天数 |
4.流程函数
|
函数 |
功能 |
|
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 |
第七章：表类型的选择
1.存储引擎，查看支持的引擎：SHOW ENGINES G
MySQL 5.0 支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB 供事务安全表,其他存储引擎都是非事务安全表。
建表会设置引擎，修改引擎：alter table account engine = innodb;
2.常用存储引擎的特性
|
特点 |
MyISAM |
InnoDB |
MEMORY |
MERGE |
NDB |
|
存储限制 |
有 |
64TB |
有 |
没有 |
有 |
|
事务安全 |
支持 |
||||
|
锁机制 |
表锁 |
行锁 |
表锁 |
表锁 |
行锁 |
|
B 树索引 |
支持 |
支持 |
支持 |
支持 |
支持 |
|
哈希索引 |
支持 |
支持 |
|||
|
全文索引 |
支持 |
||||
|
集群索引 |
支持 |
||||
|
数据缓存 |
支持 |
支持 |
支持 |
||
|
索引缓存 |
支持 |
支持 |
支持 |
支持 |
支持 |
|
数据可压缩 |
支持 |
||||
|
空间使用 |
低 |
高 |
N/A |
低 |
低 |
|
内存使用 |
低 |
高 |
中等 |
低 |
高 |
|
批量插入的速度 |
高 |
低 |
高 |
高 |
高 |
|
支持外键 |
支持 |
查看默认引擎：show variables like 'table_type';
查看支持的存储引擎：SHOW ENGINES G
问：我们用的引擎是什么，为什么？
3.如何选择存储引擎
MyISAM:默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 都是合适的选择。
MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据的环境下,可 供极快的访问。MEMORY 的缺陷是对表的大小有限制,太大的表无法 CACHE 在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表,用以快速得到访问结果。
第八章：选择合适的数据类型
1.char 和 varchar区别？
char:定长，取出时会trim
varchar：按需分配
innoDB：建议使用varchar类型，内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针)。
第十章：索引的设计和使用
1.索引的设计原则？
where或连接子句指定的列
使用唯一索引
能使用短索引就使用短索引，即前缀索引
不要过度索引，索引要占据额外的磁盘空间，降低写操作的性能，修改表内容，索引也会更新，甚至重构，且尽可能选择比较短的数据类型作为主键
2.BTEE索引和hash索引特征
hash索引：只能用=或<=>比较；不会加速order by； mysql不能确定两个值之间大约有多少行；必须使用整个关键字
B-TREE索引：使用>, <, >=, <=, between, !=, <>或者like都可以使用相关列上的索引
这点可以使用explain命令来查看你的语句是否使用了索引，是否可以优化
第十一章：视图
1.视图优势，不过目前我们好像还没有这种业务场景，以后要是想做某一列某一行的权限，可以试试
简单：不用关心后面对应的表的结构，关联条件，查询条件，只需要使用
安全：使用视图的用户只能访问他们被允许访问的结果集，可以对用户的权限设置到某行某列
数据独立：表的变化对视图的影响比较小
2.创建一个视图
CREATE OR REPLACE VIEW test_view AS
SELECT a.id, a.name, ap.nick_name
FROM `account` as a,`account_third_party` as ap
where a.id = ap.account_id ;
第十四章：锁定和事物
1.锁定和解锁
lock table test_hash read; 则其他session可以读此表，但是不能写此表
unlock tables; 解锁
第十七章：常用的sql技巧和常见问题
3.group by 的with rollup语句,带汇总的分组
select company_id, count(1) from test group by company_id with rollup;
4.数据库名，表明：window大小写不敏感，unix敏感
5.查看各种sql的执行频率
show status like 'Com_%';可以看到增删改查回滚执行次数，从而知道是以写还是读为主的表，从而进行优化
6.查询慢日志定位执行效率较低的sql
7.explain分析，分析完成之后，就可以对语句或者表进行优化
explain select * from `account` a
inner join company.company cc on a.company_id = cc.id;
主要参数：
type:表的连接类型,性能由好到不好，system(表中只有一行),const(单表最多有一个匹配), eq_ref(前面每一行，在此表只查询一条，也就是使用primary 或unique索引)， ref普通索引），range（单表范围查询），index（对于前面每一行，都是通过查询索引来得到数据），all（前面的每一行，都是通过全表扫描得到）
possible_keys:可能使用的索引
key:实际使用的索引
8.存在索引但不用索引
用or分隔开的条件，如果or中有一个条件的列没有使用索引，则整个搜索都不会用到索引
复合索引单独只使用第一列，则会使用，使用第二列则不会使用
对索引使用like时，如果%在前则不会使用索引，如果%在后则会使用索引 如like “%1” 和 like "1%"
如果索引类型是字符串，where 条件中要用引号引起来，不然不会使用索引，这是因为mysql把输入的常量值转换之后才进行检索
9.查看索引的使用情况
show status like 'Handler_read%';
得到以下几个变量：
Handler_read_first：索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
10.sql小优化
(1)操作能批量进行就批量进行，避免建立和释放连接
(2)group by col1, col2:其实有一个隐式的order by 跟在后面，浪费时间，要是不用排序，可以order by null;
优化order by语句： 可以使用索引的情况
(3) where 和order by使用相同的索引，并且order by的顺序与索引的顺序相同,order by字段都是升序或者是降序
(4)没有使用索引的子查询能被优化成使用索引的join最好
(5)聚合索引几列使用or连接不会使用索引
第19章：优化数据库对象
1.分析表 select * FROM overtime_record PROCEDURE ANALYSE(16,256);
会给出你现在每个字段的统计值，最大长度，最小长度，平均长度，null和0或empty的数量
2.大表和拆分小表优缺点？
大表：查询sql简单，数据位置唯一，好找，但是查询数据大（包含很多不需要的数据）,查询速度慢
拆分：
水平拆分：根据几列的值把数据行放到两个独立的表中，优：降低查询时需要读的数据和索引的页数；把常用数据和不常用数据分开；缺：起名，需要union，所以要考虑数据量的增加速度，增加的很快则可以考虑。
垂直拆分：把主码和一些列放到一张表，主码和另一些列放到另一张表上。优：数据行变小，一个数据页可放更多数据，查询时减少IO数，缺：管理冗余列，查询需要join
3.中间表
对数据量巨大的表做统计时，可以考虑从中间表统计，不会对线上表数据有影响，可以灵活的增加字段和索引