SQL

create table t1(id1 int ,id2 int(5));

desc t1;

显示表结构

insert into t1 values(1,1);

select * from t1;

输出整个表

分别修改id1，id2字段类型，加入zerofill参数(用零填充数据类型)

alter table t1 modify id1 int zerofill;

自增列的定义，auto_increment属性，从1开始，每行增加1，每个表中只能有一个自增列，应该定义为not NULL，并且定义为primary key或者unique键以下三种形式都可以定义自增列

create table ai(id int auto_increment not NULL primary key);

create table ai(id int auto_increment not NULL ，primary key（id）);

create table ai(id int auto_increment not NULL , unique(id));

mysql可以建浮点型和定点型数据类型，用精度和标度表示如float（5,2），decimal（5,2）

create table "t1"('id1' default NULL, 'id2' default NULL, 'id3' default NULL);

表按照2位小数存入数据，长度大于两位的进行截断处理

alter table t1 modify id1 float;

alter table t1 modify id3 decimal;

MySQL定点数的默认是10,0没有小数，精度为10标度为0

如果定义bit类型，那么select读取不出来，要分别以bin和hex用二进制数和十六进制数读取出来

select bin(id),hex(id) from t1;

如果插入的数据大于bit（位数），那么插入失败

日期类型：date 年月日，datetime年月日分秒，time分秒，timestamp字符串的系统时间(1970~2018超过年限会变成0)，如果要获得数字值，应该在后面加上+0；year、只有年份

create table t(d date, t time ,dt datetime);

desc t;

insert into t values(now(),now(),now());

MySQL中的字符类型

char和varchar，char会自动删除数据中的空格，varchar不会

binary和varbinary，包含的是二进制字符串

enum类型忽略大小写，如果对于不在枚举内的数据的时候用第一个值插入，null会插入null

set和enum类似，但是可以一次取多个值

create table t (col set(a,b,c,d,e))

insert into t values(a,b),(c,d);

MySQL中的运算符

select 1+1,1-1,1*1,1/2;

比较运算符

= <> between in is null is not null like regexp或者rlike

逻辑运算符

not and or xor

常用函数

1、字符串函数

concat(s1,s2,...,sn);

insert(str,x,y,instr);

lower();

upper();

等等很多；

2、数值函数

abs,rand,等等

3、日期函数

curdate,curtime,now,等等

4、流程函数

if，case when()then()...when()then(); case(表达式)when()then...when()then()

5、其它常用函数

database返回数据库名字

version返回版本名字

user返回登录用户

inet_aton返回ip

password（str）返回字符串加密版本

存储引擎

默认引擎是myisam，5.5之后是innodb，修改默认引擎要在参数文件中修改table_type,查看默认引擎的sql是show variables like table_type

查看数据库支持的所有数据引擎

show engines

show variable like have%

可以在建立表的时候指定采用哪个引擎，还可以alter table t engine = innobd

最常用的四种存储引擎：MyISAM、InnoDB、MEMORY和MERGE

MyISAM不支持事物，不支持外键，优势是访问速度快，对事物的完整性没有要求或者以select和insert为主的应用可以采用

磁盘存储分为三个文件：.frm表结构 .MYD存储数据 .MYI存储索引，创建的时候最好用data directory 和index dirctory语句指定两个文件的路径，在不同的目录以平衡io

检查表健康状况的语句是check table，修复语句是repair table 

MyISAM支持三种不同的存储格式分别是静态表，动态表和压缩表，静态表固定字长占用空间大，用空格补全空间，但是在访问的时候丢失尾部空格，动态表用变字长，空间少但是容易产生碎片，需要定期清理，压缩表用myisampack工具建立，占据很小的磁盘空间，每个记录单独压缩。

InnoDB提供了具有提交、回滚和崩溃恢复的事物安全，但是比MyISAM写的处理效率要差一点，并且回占用更多的磁盘空间以保留数据和索引

InnoDB中自动增长列必须是索引，如果是组合索引则必须是第一列，但是MyISAM可以把自增列放在组合索引的任意一列

MySQL中支持外键的只有InnoDB，constrint xx foreign key xx reference xx on delete restrict on update cascade不允许删除，允许更新，restrict表示限制，cascade允许，set null表示父表删除的时候子表对应变成null

存储方式：共享表存储和多表空间存储，共享是数据和索引共同存储，多表空间是数据和索引分开存储,而且不需要设置文件大小限制

MEMORY在内存中建立，断电消失，默认hash存储，访问速度快

create table m engine=memory select city_id.w,x form t1 group by city_id;

而且在创建表索引的时候还能指定用hash还是btree索引

create index mm using hash on m (city_id);

drop index mm on m;

create index mm using btree on m (city_id);

可以指定最大内存和最大行数，存储那些变化不频繁的表，或者是中间结果。

MERGE是一组MyISAM表的组合，对其的查询是对内部组合操作，如果drop merge不会影响内部的表结构 创建表的时候

（）engine = merge union=(t1,t2) insert_method=last

insert_method定义表示从表的最后一个表进行插入，如果不写或者等于no表示不能进行插入操作

上述是四种默认存储引擎，还有一些常见的第三方存储引擎，比如列式存储引擎Infobright、高写性能高压缩的ToKuDB，

ToKuDB高性能、支持事物处理，高拓展高压缩率高性能的写入操作，支持大多数ddl

使用fractal树索引保证高效的插入性能，

优秀的压缩特性是innodb的十倍

使用bulk loader达到快速加载大量数据，

提供主从延迟消除技术

应用场景：插入频繁存储量大的日志数据，不需要改写的历史数据，应用其高压缩率，

视图

视图是一种虚拟存在的表，并不在数据库中真实存在，行和列数据来自定义视图中使用的表，在定义视图中动态生成的。

视图相对于普通表的优势：

1、简单：用户不用关心后面对应表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件结果集

2、安全：使用视图的用户只能访问他们被允许查询的结果集，对表的管理权限不能限制到某一行某一列，但是使用视图可以

3、数据独立：一旦视图结构确定了，可以屏蔽表结构变化对用户的影响

视图操作：

包括创建、修改视图、删除视图、查看视图定义

create or replace view s as select t.1,t.2,t.3 from staff as t where a.t1 = a.t2;

有些视图是不可更新的，

1、包含，聚合函数sum min max count 等， distinct ，group by， having ， union 

create or replace view p as select staff, sum(amount) from payment group by staff

2、常量视图 create or replace view pi as select 3 as pi

3、select包含子查询  create view c as select(select city from city where cityid = 1)

3、from一个不可更新的视图 

两个更新选项：with （local or cascaded）check option，一个是满足本视图的条件就可更新，第二个要满足所有条件才能更新

删除视图：drop view t1

show tables 不仅显示表的名字，还显示视图的名字， show table status 不但显示表的信息，还显示视图信息，show create view s查看视图定义

存储过程函数：

create procedure s把处理逻辑用函数的形式封装在数据库端，调用者不需要了解中间逻辑。

触发器：

触发器是与表有关的数据库对象，在满足定义条件时候触发，并执行触发器中定义的语句集合。create trigger name time 事件 on 表名 for each row 操作

事物控制：

将自动提交属性置零后，每次提交和回滚都需要手动进行

分布式事物：

n个资源管理器和1个事物管理器

注入：

在查询中使用注释使得后续条件失效，达到注入效果，绑定变量，使用转换函数，对特殊字符转换，防止生成程序不期望的语句，自定义校验程序

优化： show status 查看各种sql执行频率，explain查看低效sql，show profile 分析sql 执行过程的每个线程的每个状态消耗时间