深入浅出mysql数据库开发（第二版）笔记

第一部分：【基础篇】
2.2.1：SQL分类
1)DDL(Data Definition Languages): 数据定义语言
常用语句：create，drop，alter 等
2)DML(Data Manipulation Language):数据操作语句
常用语句：insert，delete，update和select等
3)DCL(Data Control Language):数据控制语句，定义数据库，表，字段，用户的访问权限和安全级别
常用语句：grant，revoke

DDL:
tableName = emp
desc：查看表结构语句
例： desc emp；

alter：
1）修改字段类型
alter table emp modify column ename varchar（20）；
2）新增字段：默认加在最后，可以使用alter，first 控制字段位置
alter table emp add column age int（2）after ename；
3）删除表字段
aler table emp drop column age；
4）字段改名
alter table emp change age age int(10);
5）修改表明
alter table emp rename emp_new;
注意：change 和 modify 都可以修改表的定义，change可以修改列名，modify则不能；建议记住change用法：

DML:
1.limit:结合order by 语句可以实现分页
select * from emp order by ename limit 2,3; 

2.表连接：内连接和外连接
1）内连接：只是显示两张表匹配的内容；
select * from emp，dept where emp.deptId = dept.deptId
2）外连接：会把未匹配的内容也显示出来
左连接：显示所有左边表的记录，甚至是右边表中没有和它匹配
右连接：显示所有右边表的记录，甚至是左边表中没有和它匹配
左：
select ename，deptname from emp left join dept on emp.deptId = dept.deptId; 
3）union，unionall 记录联合
select deptno from emp union select deptno from dept;

DCL:
用户名：z1，密码：123，数据库：sakila

赋予用户名在z1对数据库sakila下所有表都可以进行select，insert
grant select,insert on sakila.* to 'z1'@'localhost' identified by '123'; 
撤销用户民z1对数据库sakila下所有表的insert权限
revoke insert on sakila.* from 'z1'@'localhost';

帮助文档查看：
？contents
"? 类型别名" 
例如查看数据库支持的数据类型："? data types"
"? create table" 查看数据库创建语句
"? show" show命令都可以查看什么东东

3.1数据类型

浮点数：float(M,D)（单精度） double(M,D)（双精度）  
有精度误差:float f = 7.22f;float c = 7.0f;f - c = 0.21999979;
定点数:decimal(M,D)  适合用来表示货币等精度高的数据
M:代表一共显示M位数字；
D：代表小数位数，超过小数位会按照四舍五入保存

3.2日期时间类型
DATE:年月日
TIME:时分秒
DATETIME:年月日时分秒
TIMESRAMP:如果需要经常插入日期或者更新当前日期使用;缺点：表示范围比较小（区分时区时间使用）；
表中的第一个TIMESTAMP列自动设置为系统时间，如果在一个TIMESTAMP列中插入NULL,则该列值将自动设置为当前日期和时间

3.3.1CHAR 和 VARCHAR类型
CHAR：定长字符（会自动删除字符串后面的空格）
VARCHAR：可变的字符，会根据实际字符长度进行调整；（保留原始字符，不做任何处理）

第二部分：【开发篇】
1.MySQL存储引擎：MyISAM,InnoDB,BDB,MEMORY,MERGE,NDB Cluster,ARCHIVE,CSV ...
  提供事物安全表：InnoDB,BDB；其他都为非事物 
引擎优缺点：
1）MyISAM：
优点：访问速度快
缺点：不支持事物，也不支持外键
2）InnoDB：（mysql默认存储引擎）
优点：支持事物,支持外键
缺点：写的效率低，并且会占用更多的磁盘空间以保留数据和索引
3）MEMORY: 基于内存来创建表
优点：读写速度快
缺点：不安全，数据量大小有限制（数据容易丢失）
4）MEGER: 存储引擎是一组MyISAM表的组合,顾名思义就是对多个表进行整合

8.2 TEXT 与 BLOB(保存较大文本时需要使用)
1）TEXT:只能存储字符，不能存储二进制
2）BLOB:可以存储二进制;比如：照片，视频
3）对大文本进行检索时，可以使用合成的（Synthetic）来提高文本的查询性能；（即：对文本内容建立散列码,并把该字段存储到数据表中，查询时通过散列码查询）
例：create table t（id varchar（100），context blob，hash_value varchar(40)）;
    insert into t(1,repeat('bj',2),md5(context));    
    hash_value字段即为散列码；
注意：在使用blob，text类型进行频繁删除操作时，需要及时进行碎片清理；optimize table tableName;

10.1索引的介绍
1.索引的分类：
1）btree
2）hash 
3）r-tree（空间索引），一般很少用到
4）fulltext(全文索引) 仅支持varchar， char，text类型
2.存储引擎支持的索引：
1）MyISAM存储引擎：btree（默认），fulltext,R-tree(空间索引)
2）innodb存储引擎：btree（默认）
3）memory存储引擎：hash（默认），btree
3.索引创建语句
  create index index_name on tablename (indexname);

10.2索引的设计原则
1）索引列要使用在条件查询列中（where 后面的列），不应该使用在select 列中
2）索引唯一性，这样能提高索引效率（索引基数越大，效果越好）
例如：记录性别的列只有：M,F,对此列创建索引时不管搜索哪个值，都会得出大约一半的列，索引用处不大（基数小）
3）索引简短性，较小的值减少磁盘io，较短的值比较更快，索引高速缓冲区能容纳更多的索引键值；
如果一个比较长的列要建立索引：要考虑是否可以建立前缀索引（要保证大多数的唯一性），以达到简短性
前缀索引：列类型为 char（200），可以取前10到20个字符作为索引；
4）不要过渡使用索引，过渡使用会造成性能问题；因为更新数据的同时，也要对索引维护更新；
5）利用最左索引，最左前缀是用在复合索引上的；
例如：创建索引name_age,mysql会对name进行排序，name相等时进行age排序，即order by name，age原则；
语句1：select * from student where name = "wg" and age = 28;
语句2：select * from student where age = 28 and name = "wg";
最左索引说的就是条件查询时先使用最左边的索引字段，例如：语句1，但是语句1和语句2的查询效果是一样的，那是因为mysql优化器对sql进行了优化，语句2优化后就是语句1；

10.3 btree与hash索引比较
hash： 只能进行关键字查询，即=或<=>；优化器不能使用hash索引来加速order by 操作；
btree：适合like，between，>= 或<= 操作时都能使用到索引

下列查询适合btree和hash
select * from key_col = 1 or key_col in(2,5,6) ;
只适合btree
select * from key_col >= 1 and key_col <= 6 ;
select * from key_col2 like 'txt%';

14.1 lock tables 与 unlock tables
当一个表被一个线程lock 时，其他线程可以进行select 操作，插入，修改操作会等待，直到锁表线程执行unlock tables；
当锁表线程执行另一个lock tables 或者关闭与服务器关闭连接时，会隐式解锁锁定的表
例子：
线程1：lock table emp read；
线程2：select * from emp； （可以查询）
线程2：insert into emp values('1111','2018-02-07',5000,2);(会等待)
线程2：update emp set ename = '2222' where ename = '2222';(会等待)
当线程1执行 unlock tables；或执行另一个lock table 时（包含 lock table emp），或线程关闭连接时， 线程2：执行插入更新操作； 
例子：
线程1：lock table emp write；
线程2：select * from emp；（可以查询）
线程2：select * from emp where ename = '111'；(会等待)

总结：读锁定会阻塞写操作，同理写操作会阻塞读操作

14.2 事物控制
mysql事物默认是自动提交的，即set autocommit = 1；
1）通过设置set autocommit = 0；可以改变提交模式，之后所有的事物都需要明确的命令(commit)提交；
2）如果只是某些语句需要手动提交，可以使用 start transaction 开启事物，执行sql操作...,commit；提交；
   start transactio 语句会开启一个事物，会切换到手动提交模式；直到执行commit命令，会再次切换到自动提交模式；
3）commit and chain；提交之后会再次开启一个事物；
4）rollback 结合savepoint使用;