深入简出mysql

第二章：

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个字节的定长字节字符串

char和varchar区别？

  char长度固定，前面补空格存，去除trim()，速度快，空间大

  varchar长度不固定，空间小，速度稍慢

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类型，内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针)。

2.连这里都提示尽量不要用double和float，用 BigDecimal 

第十章：索引的设计和使用

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技巧和常见问题

1.正则表达式

select count(1) from test where email REGEXP "@qq.com$";

2.rand()随机提取行

select * from test order by rand() limit 5; #随机排序

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.中间表

 对数据量巨大的表做统计时，可以考虑从中间表统计，不会对线上表数据有影响，可以灵活的增加字段和索引