Mysql 基础
SQL分类：
DDL—数据定义语言(create，alter，drop)
DML—数据操纵语言(select，delete，update，insert)
DCL—数据控制语言(grant，revoke)
DDL操作:
数据库连接：
# mysql -h 127.0.0.1 -u root -p
常用参数：
-A 不预读数据库信息，提高连接和切换数据库速度,使用--disable-auto-rehash代替
--default-character-set 使用的默认字符集
-e 执行命令并退出
-h 主机地址
-p 连接到服务器时使用的密码
-P 连接的端口号
创建数据库:
create database test1 ;
查看数据库:
show databases;
选择数据库:
use mysql;
删除数据库:
drop database test1;
创建表:
create table emp( ename varchar(20), hiredate date, sal decimal(10,2), deptno int(2) );
查看表定义:
desc emp;
查看创建表SQL:
show create table emp ;
更新表名:
alter table emp rename users;
删除表:
drop table emp;
修改表字段:
alter table emp modify ename varchar(30);
增加表字段:
alter table emp add column age int(3);
修改表字段:
alter table emp change age age int(4);
删除表字段:
alter table emp drop column age;
change和modify:
1 前者可以修改列名称,后者不能.
2 change需要些两次列名称.
字段增加修改 add/change/modify/ 添加顺序:
1 add 增加在表尾.
2 change/modify 不该表字段位置.
3 修改字段可以带上以下参数进行位置调整(frist/after column_name);
alter table emp change age age int(2) after ename;
alter table emp change age age int(3) first;
DML语句:
插入记录:
//指定字段,
//自增,默认值等字段可以不用列出来,没有默认值的为自动设置为NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);
//可以不指定字段,但要一一对应
insert into emp values ('lisa','2010-01-01','8000',2);
批量记录:
insert into emp values ('jack chen','2011-01-01','18000',2),('andy lao','2013-01-01','18000',2);
更新记录:
update emp set sal="7000.00" where ename="jack";
update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";
删除记录:
//请仔细检查where条件,慎重
delete from emp where ename='jack';
查看记录:
//查看所有字段
select * from emp;
//查询不重复记录
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;
//条件查询
//比较运算符: > < >= <= <> != ...
//逻辑运算符: and or ...
select * from emp where sal="18000" and deptno=2;
排序
//desc降序,asc 升序(默认)
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;
限制记录数:
select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;
聚合:
函数:count():记录数 / sum(总和); / max():最大值 / min():最小值
select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;
group by
分组
select * from emp group by deptno;
//分组统计
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;
having
对分组结果二次过滤
select count(deptno) as count,deptno from emp group by deptno having count > 2;
with rollup
对分组结果二次汇总
select count(sal),emp.* from emp group by sal, deptno with rollup ;
表连接:
- 内连接:关联表都要有匹配记录
- 外连接:关联表会选出对应不匹配的记录
内连接:
select * from emp as e,dept as d where e.deptno=d.deptno;
select * from emp as e inner join dept as d on e.deptno=d.deptno;
左外连接:包含左表的全部记录和右表不存在的记录
select * from emp as e left join dept as d on e.deptno=d.deptno;
右外连接:包含右表的全部记录和左表不存在的记录
select * from emp as e right join dept as d on e.deptno=d.deptno;
子查询:
//=, !=
select * from emp where deptno = (select deptno from dept where deptname="技术部");
select * from emp where deptno != (select deptno from dept where deptname="技术部");
//in, not in
//当需要使用里面的结果集的时候必须用in();
select * from emp where deptno in (select deptno from dept where deptname="技术部");
select * from emp where deptno not in (select deptno from dept where deptname="技术部");
//exists , not exists
//当需要判断后面的查询结果是否存在时使用exists();
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);
记录联合:
union:返回去重之后的结果
select ename from emp union select ename from emp;
union all:返回所有结果
select ename from emp union all select ename from emp;
DCL语句:
添加权限:
grant select,insert on test.* to 'db_user_1'@'localhost' identified by '123456';
flush privileges;
回收权限:
revoke insert on test.* from 'db_user_1'@'localhost';
后面会具体描述
Mysql数据类型
整数类型
指定宽度:
指定显示的宽度为5,不影响实际数据
create table t1 (id int , id2 int(5));
zerofill:
采用零填充,不足5位采用0填充
配合数据宽度
create table t2 (id int , id2 int(5) zerofill);
unsigned:
create table t3 (id int , id2 int(5) unsigned);
auto_increment:
只用于整数类型
产生唯一标识
值从1开始,逐行增加
一个表中最多只能存在一个自增列
自增列应该定义为not null
自增列应该这只为 primary key 或者 unique
id int not null auto_increment primary key
浮点数类型
定点数类型
定点数类型.png
float , double , decimal 特点:
1.(m,d)表示方式:m指的是整数位,d指的是小数位(又称作精度和标度)
2.float/double四舍五入丢失精度,decimal会截断数据并输出warning
3.如果不指定精度,float/double采用操作系统默认,decimal则是(10,0)
位类型
位类型.png
1 存放位字段值
2 指定存放多位二进制的长度,默认为1(范围:1~64)
3 读取需要bin()/hex(),普通的select读取结果为null
4 插入的值会转化为二进制码,如果长度运行则正常处理,否则插入失败
create table t6 (id bit(1));
select bin(id) from t6;
日期和时间类型
当前系统日期
timestamp:返回yyyy-mm-dd hh:mm:ss 宽度19
timestamp:不适合存放久远日期,超出范围则会采用零值填充
//不同格式的显示零值格式
d date, t time,dt datetime
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2016-11-25 | 14:52:44 | 2016-11-25 14:52:44 |
+------------+----------+---------------------+
//默认值的体现
id1 timestamp
+---------------------+
| id1 |
+---------------------+
| 2016-11-25 14:55:45 |
+---------------------+
//timestamp字段只能有一个"CURRENT_TIMESTAMP"
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| id2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-----------------------------+
//timestamp和时区相关:SYSTEM 指的是和主机时区保持一致
show variables like "%_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
//修改时区
set time_zone="+9:00"/在 [mysqld] 之下加 default-time-zone=timezone
年份
year:默认为4位格式.1901~2155和0000. 2位的已经不推荐,高版本已经不支持了.
timestamp和datetime区别:
- timestamp支持范围小(1970-01-01 08:00:01到2038年某个点)
- 表中第一个timestamp字段,会默认采用当前系统时间.如果更新其他字段,该字段没有赋值的话,则该字段会自动更新.如果指定字段不满足规格,则采用零值填充
- timestamp查询和插入都会受到当地时区影响
datetime支持范围宽度大(1000-01-01 00:00:00到9999-12-31 23:23:59)
字符串类型
char和varchar:
- char定长，效率高,在创建字段的时候就已经指定,一般用于固定长度的表单提交数据存储
- char在检索的时候回去掉尾部的空格
- varchar是动态长度
- varchar在检索的时候回保留尾部的空格
- varchar将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度(长度>255时需要2个字节)
- 字符类型若为gbk，每个字符最多占2个字节，字符类型若为utf8，每个字符最多占3个字节。
枚举类型:
- 枚举在集合中取值,只能取一个
- 如果值不存在则采用第一个值为默认
- 如果插入NULL不会出错,会将NULL写入表
- 成员个数在1255占用1个字节,在25565535占用2个字节,节约资源
create table `t8` (
`gender` enum('m','f') default null
) engine=innodb default charset=utf8
集合类型
集合类型和枚举很像,但是支持多值选择
最多可以保存64个成员,每8个成员占1个字节
重复值只会插入一次,如果查出集合范围则插入为null
create table t9 (col set ('a','b','c','d'));
Mysql运算符
算术运算符
比较运算符
- NULL值不能参与"=","!=","<==>","<>"等场景比较
select * from t1 where id is null;
select * from t1 where id is not null;
- between 比较为闭区间
a between min and max ==> a >=min and a<=max
select * from emp where deptno between 1 and 3;
- in 在集合中匹配
select * from emp where deptno in (1,2,3);
- like 这个在大量数据的情况下慎重选择，会影响查询性能。
select id2 from t1 where id2 like "%03%";
- regexp 这个在大量数据的情况下慎重选择，会影响查询性能。
select * from t1 where id2 regexp '0001';
逻辑运算符
位运算符
运算符优先级
很多情况下，我们可直接采用“（）”进行运算符调整，逻辑清晰，阅读性强。
常用函数
字符串函数
数值函数
日期和时间函数
日期间隔类型：
流程函数
其他函数
MySQL管理工具和应用程序
工欲善其事，必先利其器。几乎每个开发人员都有最钟爱的 MySQL 管理工具，它帮助开发人员在许多方面支持包括 PostgreSQL，MySQL，SQLite，Redis，MongoDB 等在内的多种数据库；提供各种最新的特性，包括触发器、事件、视图、存储过程和外键，支持导入、数据备份、对象结构等多种功能。
这篇文章收集了15款最佳的 MySQL 管理工具和应用软件，帮助你轻松快速完成工作。
Induction
Induction是一款用于理解数据关系的开源管理工具，它可用来探索行/列，运行查询和数据可视化等方面。该工具支持多种数据库，包括PostgreSQL，MySQL，SQLite，Redis以及MongoDB。此外，Induction还可以通过编写添加其他新的适配器。
Pinba
Pinba 是一种MySQL存储引擎，用于PHP实时监控和数据服务器的MySQL只读接口。它整理并处理通过UDP发送的数据，并以可读的简单报告的形式统计显示多个PHP进程。为了获取下一代更为复杂的报告和统计数据，Pinba提供了原始数据的只读接口。
DB Ninja
DbNinja是一款先进的基于Web的MySQL数据库管理与开发应用程序。它是远程访问托管服务器的必然之选。DbNinja支持所有最新的功能，包括触发器、事件、视图、存储过程和外键等。此外,它还可以导入和备份数据、MySQL对象结构以及管理用户等。DbNinj的用户界面功能完备且清新美观，可安全地运用于任何浏览器及任何操作系统中。
DB Tools Manager
DBManager是一款功能强大的数据管理应用程序。作为最先进的应用程序，DBManager内置支持MySQL、PostgreSQL、Interbase/Firebird、 SQLite,DBF表、MSAccess,MSSQL服务器,Sybase,Oracle和ODBC数据库引擎等一些新特性。DBManager目前拥有个人和企业两个版本，用户可按需选择使用。
Dbeaver
DBeaver是一款免费的数据库管理应用程序，可运用于多种不同的引擎，包括MySQL，MSSQL，Oracle、SQLite、Sybase和Firebird等等。由Java编写而成，该应用程序适用于所有主流操作系统(Windows、Mac和Linux)。它能处理包括元数据编辑(表、列、键、索引)、自定义SQL执行、用户管理、多连接等在内的所有主要任务。
SqlWave
SQLWave是一种简单、快速且易用的MySQL客户端。用户可通过该工具轻松地连接到远程主机。SqlWave支持所有MySQL的最新版本，包括它用来管理数据库结构的所有最新功能，如工作表、视图、存储过程、函数、事件、外键和触发器等。
MyWebSQL
MyWebSQL主要用于管理基于Web的MySQL数据库。与桌面应用程序的借口工作流程相似，用户无需切换网页即可完成一些简单的操作。如果您正在操作桌面，只用登数据库，就可以管理您的数据库了。
MyWebSQL主要用于管理基于Web的MySQL数据库。与桌面应用程序的借口工作流程相似，用户无需切换网页即可完成一些简单的操作。如果您正在操作桌面，只用登数据库，就可以管理您的数据库了。
Navicat
Navicat是MySQL和MariaDB数据库管理与开发理想的解决方案。它可同时在一个应用程序上连接MySQL和MariaDB数据库。这种兼容前端为数据库提供了一个直观而强大的图形界面管理、开发和维护功能，为初级MySQL和MariaDB开发人员和专业开发人员都提供了一组全面的开发工具。
SQLyog
SQLyog是一款功能最强大的MySQL管理工具，它综合了MySQL工作台、php MyAdmin和其他MySQL前端及MySQL GUI工具的特点。该款应用程序可以同时连接任意数量级的MySQL服务器，用于测试和生产。所有流程仅需登录MySQL root以收集数据，用户无需安装在MySQL服务器上。
Sequel Pro
Sequel Pro是基于MySQL数据库的一种快速易用的Mac数据库管理应用程序。用户可通过Sequel Pro在本地和远程服务器上直接访问MySQL数据库。不过，Sequel Pro的最新版本开始添加全屏支持内置。
HeidiSQL
HeidiSQL是一种专为web开发人员设计的有用且可靠的工具，可用于当前最受欢迎的MySQL服务器，微软SQL数据库和PostgreSQL。该工具可提供浏览和编辑数据、创建和编辑表格、视图、过程、触发器和日志安排等事件。此外，您还可以导出结构和数据至SQL文件、剪贴板或其他服务器。
MyDB Studio
/)
MyDB Studio是一款免费的MySQL数据库管理器应用程序。该工具强大到您几乎可以获取到任何想要的功能，并能够连接到无限量级的数据库。通过创建、编辑或删除数据库、表格和记录，就可以备份/恢复并导出为多个格式。
SQL Lite Manger
SQL Lite Manager是一款基于web的开源应用程序，用于管理无服务器、零配置SQL Lite数据库。该程序是用PHP写成，除了记录和应用表格格上的每一步操作，还可以控制多个数据库。SQL Lite Manager可用于查询数据，将MySQL查询转化为兼容SQL Lite数据库，并能创建和编辑触发器。SQL Lite Manager有多种皮肤选项，是一个含现成语言文件的多语言资源。
Database Master
Database Master是一个现代的、强大的、直观且易用的数据库管理程序。它以一个一致而现代的界面适用于MongoDB、MySQL、PostgreSQL、FireBird、 SQL Lite、MS SQL Server、SQL Azure、Oracle、IBM DB2、IBM Informix、Netezza、Ingres以及EffiProz等数据库。Database Master简化了管理、查询、编辑、可视化、设计和报告数据库系统。用户可以通过ODBC与OleDB连接任何数据库系统，也可以访问MS Access，MS FoxPro Database、Dbase和XML文件。
Chive
Chive由PHP搭建而成，是一款基于web的开源MySQL管理应用程序。此款应用程式有一个内置的编辑器，当运行复杂的查询或易用的界面时，可用于快速浏览。
Mysql存储引擎
Mysql默认支持多种存储引擎，来适应不同的场景需求。
默认存储引擎：
在5.5以前的版本为MyISAM，在5.5之后为InnoDB
查看存储支持的引擎：
show engines\G
show variables like "have_%";
创建表指定引擎：
create table `ai` (
`i` bigint(20) not null auto_increment,
primary key (`i`)
) engine=myisam default charset=utf8
修改表引擎:
alter table ai engine=innodb;
存储引擎对比:
各种存储引擎特性:
MyISAM
特点:
1 不支持事务,不支持外键
2 访问速度快
存储结构:
存在3个文件,文件名和表名相同,扩展名分别为:
.frm 存储表定义
.myd 存储数据
.myi 存储索引
检测修复:
check table 检查表
repair table 修复表
表存储格式:
静态表,动态表,压缩表
静态表:
字段都是非变长字段,存储快,容易缓存,易恢复;占用空间多;丢失尾部空格
动态表:
包含变长字段,记录不固定,节省空间;频繁更新和删除会产生碎片,需要进行定期整理
optimize table 整理碎片
[扩展1](http://blog.csdn.net/andybbc/article/details/50562355)
压缩表:
由myisampack工具创建,占用空间小.
使用场景：
1.选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。
InnoDB
特点:
1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列auto_increment属性，并且必须是索引。如果是组合索引，也必须是第一列（myisam可以是其他列或者组合列）。
//设置自增列的初始值
alter table autoincre_demo auto_increment=10;
//查看当前线程最后插入自增记录值
select last_insert_id();
//在多表数据导入的时候，可以关闭外键约束提高速度。
set foreign_key_checks=0;//关闭
set foreign_key_checks=1;//开启
存储方式:
1 共享表空间：
- 表结构保存在.frm文件中
- 数据保存在innodb_data_home_dir
- 索引保存在innodb_data_file_path。
2 多表空间：
- 表结构保存在.frm文件中。
- 数据和索引单独保存在.ibd中。
- 分区表每个分区对应单独的.ibd文件，文件名为“表明+分区名”，可以在创建* 分区的时候指定数据文件位置。数据文件没有大小限制，不需要设置初始大小。
Memory
特点：
- Memory存储引擎的处理速度比较快。采用的逻辑存储介质是系统内存。
- 当mysqld守护进程崩溃时，数据都会丢失。
- 它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。
索引：
Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。
使用场景：
目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。
Merge
特点：
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同。
Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。
对于插入操作，是通过INSERT_METHOD字句定义插入的表。
对于表删除，只是删除该merge的定义，对内部表无影响。
merge表会产生两个文件，文件以表名字开始，.frm存放表定义，.mrg文件包含组合表信息。
CREATE TABLE `payment_2006` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `payment_2007` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `payment_all` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`payment_2006`,`payment_2007`)
区别：
merge和分区表类似，但是merge引擎不能智能的将数据写到某个数据表中，而分区表可以。
使用场景：
对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。
TokuDB
特点：
1 高性能，支持事务处理。
2 高扩展，高压缩比，高效写入。
3 支持大多数在线DDL操作
使用场景：
1 日志数据，插入频繁，存储量大
2 历史数据，后期不会在有写入操作
3 在线DDL频繁的情况
ARCHIVE
特点：
1 仅支持最基本的插入和查询两种功能。
2 在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。
3 Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。
总结
选择合适的数据类型
char与varchar
char是固定长度，varchar可变长度。
char插入的时候去去掉尾部空格，varchar不会。
使用建议：
text和blob
text：保存字符数据
blob：保存二进制数据。
BLOB和TEXT值在执行了大量的删除或更新操作的时候，会在数据表中留下很大的"空洞"，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.
使用合成的（synthetic）索引。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符 是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。(如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。) 用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。
在不必要的时候避免检索大型的BLOB或TEXT值。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或 TEXT值。
把BLOB或TEXT列分离到单独的表中。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。
浮点数与定点数
浮点数：包含小数部分的数值，如果插入数据超过该列的实际精度，会被四舍五入插入。
定点数：是以字符串的形式存放的，可以更加精准的确保存数据。
提示：
- 浮点数存在误差问题；
- 对货币等对精度敏感的数据，应该用定点数表示或存储；
- 编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
- 要注意浮点数中一些特殊值的处理。
日期类型
1 根据实际需要选择满足的最小存储日期类型
2 如果记录YYYY-mm-dd H:i:s类型，最好使用datetime
3 如果记录的日期需要让不同时区的用户使用，最好采用timestamp
mysql 字符集
字符集比较
选择合适的字符集
1 选择合适的字符集，如果支持多种语言，就应该采用Unicode字符集(常见UTT-8)。
2 涉及数据导入，则应该充分考虑字符集对于现有数的兼容性。
3 如果只支持中文，数据量大，性能要求高，则选用中文编码。这样可以减少磁盘IO,数据库Cache以及网络传输时间
4 如果数据库做大量字符运算，例如比较，排序等，选择定长的字符集比变长的字符集处理速度快。
支持的字符集
查看所有字符集：
show charset ;
mysql> show charset ;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
Mysql的字符集包括字符集（character）和校对字符集（collation）两个概念。每种字符串至少有一个校对规则。
字符集：存储字符串的方式
校对字符集：定义比较字符串的方式
show collation like "%utf8%";
mysql> show collation like "%utf8%";
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
+--------------------------+---------+-----+---------+----------+---------+
校对规则命名：以相关的字符集名开始，通常为语言名，并且以_ci（大小写不敏感），_cs(大小写敏感)或_bin（二元，即基于字符编码的值比较）结束。
字符集设置
字符集和校对字符集有4个级别的默认设置：服务器级别，数据库级别，标级别，字段级别。
服务器级别：
1 可在my.cnf中设置
[mysqld]
character-set-server=gbk
2 启动选项中
mysqld --character-set-server=gbk
3 编译的时候
cmake . -DDEFAULT_CHARSET=gbk
默认字符集：show variables like '%character_set_server%';
默认校对字符集：show variables like '%collation_server%';
mysql> show variables like '%character_set_server%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | utf8 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%collation_server%';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
数据库级别:
可以在创建数据库的时候指定字符集或者通过ALTER命令修改。如果已经存在数据，则只对新增数据生效。
- 如果指定了字符集和校对规则，则使用指定的设置。
- 如果指定了字符集没有指定校对规则，则使用指定的字符集的默认的校对规则。
- 如果指定了校对规则但没有指定字符集，则字符集使用与该校对规则关联的字符集。
- 如果没有指定字符集和校对规则，则使用服务器级别的设置为默认配置。
表级别:
和数据库级别类似
创建的时候指定字符集
CREATE TABLE `users` (
`ename` varchar(30) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
字段级别
和数据库级别类似，字段字符集修改的可能比较小。
character-set-server/default-character-set：服务器字符集，默认情况下所采用的。
character-set-database：数据库字符集。
character-set-table：数据库表字符集。
连接字符集和校对规则
客服端和服务器段交采用的字符集
相关参数：
character_set_client：客户端的字符集。
character_set_results：结果字符集。
character_set_connection：连接字符集。
方式1：
可以采用以下命令同时修改，但是每次的连接数据库都要执行改命令：
set names utf8;
方式2：
[mysql]
character-set-server=utf8
字符集修改
/*这些只针对新纪录生效*/
alter database character set ***
alter table tablename character set ***
索引设计和使用
Myisam和InnoDB默认为rtree索引。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。
Mysql设计索引的原则
搜索的索引列，不一定是所要选择的列。
换句话说，最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。使用惟一索引。
考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。使用短索引。
如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。利用最左前缀。
在创建一个n 列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。不要过度索引。
每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。只保持所需的索引有利于查询优化。考虑在列上进行的比较类型。
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时（如STRCMP( )），对其进行索引没有价值。
btree索引与hash索引
** Hash索引一些其它特征： **
1 它们只用于使用=或<=>操作符的等式比较(但很快)。
2 优化器不能使用hash索引来加速ORDER BY操作。
3 MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。
4 只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行)。
** BTREE索引 **
当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。
下列范围查询适用于 btree索引和hash索引：
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范围查询适用于btree索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';
视图
视图
什么是视图
视图是一张虚拟的表。视图并不在数据库存在，真正的数据来源自所查询的表，而且是在查看的时候动态生成的。
视图是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图优势：
- 简单：使用者不用关心视图对应的表结构，关联和筛选条件，用户接受到的是已经处理完成的结果集合。
- 安全：使用者只能够访问被允许的结果集，数据信息可以严格控制到列。同时grant语句可以针对视图进行授予权限。
- 数据独立：一旦视图结构确定，可以屏蔽表结构变化对使用者的影响。
创建视图
视图的限制：from关键字后面不能包含子查询。
以下的类型的视图是不可能新的：
- 包含以下关键字的SQL语句：聚合函数（sum，min，max，count等），distanct，group by,having,union或者union all
- 常量视图
- select包含子查询
- join
- from一个不能更新的视图
- where字句的子查询引用了from字句中的表
聚合函数：
create or replace view payment_sum as select country_id,sum(amount) from payment_2006 group by country_id;
常量视图：
create or replace view pi as select 10000 as 1w;
子查询：
create view city_view as select (select country_id from payment_2006 where country_id=1);
with [casaded|local] check option 是否允许更新数据使记录不再满足视图的条件。
- local 满足本视图
- cascded 则必须满足所有针对该视图的条件才可以更新。
删除视图
drop view user_view2;
查看视图
show tables；
show tables like "%view%";
查看详情：
mysql> show table status like "user_view" \G
*************************** 1. row ***************************
Name: user_view
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
查看创建语句：
mysql> show create view user_view;
或者：
mysql> select * from information_schema.views where table_name="user_view"\G
事务控制和锁定
11 事务控制和锁定语句
锁。
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
加锁： lock tables;
释放锁： unlock tables;
锁标记 分为 read 和 write 下面是 两种 锁的区别
//如 将 table1 设为read锁, table2 设为write锁
lock tables [table1] read,[table2] write;
事务
原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
一致性: 确保数据库正确地改变状态后，成功提交的事务。
隔离性: 使事务操作彼此独立的和透明的。
持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。
开启事务
start transaction 或 begin
提交事务（关闭事务）
commit
放弃事务（关闭事务）
rollback
保存点：
savepoint p1
rollback to p1
发生在折返点 p1 之前的事务被提交，之后的被忽略
事务的终止
设置"自动提交"模式 set autocommit = 0
持续更新中...
