mysql查漏补缺

字符集

show character set
str varchar(20) character set utf8
create database db character set utf8

浮点

float(p,s) p:总位数 s:小数点后有效位数
double(p,s)

时间

date YYYY-MM-DD
datetime YYYY-MM-DD HH:HI:SS
timestamp YYYY-MM-DD HH:HI:SS 自动更新
year YYYY
time HH:MI:SS 耗费时间，直接插入整型数据

表

create table tab (永久表)
select * from (select * from tab) as t (临时表)
create view t as select * from tab (虚拟表)

关联表

create table refer
(
id int unsigned auto_increment not null,
person_id smallint unsigned,
food varchar(20),
constraint pk_id primary key (id),
constraint fk_person_id foreign key (person_id) references person (person_id)
);

约束

mysql会为主键约束和外键约束主动创建索引
- 创建约束
alter table refer add constraint pk_id primary key (id);
alter table refer add constraint fk_person_id foreign key (person_id) references person(person_id)
- 删除约束
alter table refer drop primary key;
alter table refer drop foreign key fk_person_id;
- 级联约束

alter table refer add constraint fk_person_id foreign key (person_id) references  person(person_id)
on update cascade
on delete cascade;

查看表结构

desc refer;
show create table refer;

内建函数

- UPPER()
- YEAR()
- RIGHT(val,offset) LEFT(val,offset)
- COUNT() AVG() SUM()
- quote()
- concat('goo',char(98)) => good
update tab set ftext = concat(ftext,' add something');
- length()
- position('val' IN fval) 查找字符串位置，从1开始  locate('val',fval,3) 指定起始位置
- 比较：
strcmp()                                        <-1,0,1> 
select name,name LIKE '%bee' rename  from tab   <0,1>
select name REGEXP '---' from tab               <0,1>
- insert()
select insert('goodbye world',9,0,'cruel '); goodbye cruel world
select insert('goodbye world',1,7,'hello'); hello world
- mod(10,4) 取余
- substring() 截取
- 精确度
ceil(10.09) 11
floor(10.09) 10
round(10.09) 10 round(10.09,1) 10.1
truncate(10.09,1) 10.0
- 处理符号
sign(val) <-1,0,1>  
abs(val)
- 时间
select @@global.time_zone,@@session.time_zone <全局时区，会话时区：system代表所在地时区>
current_date()  current_timestamp()  current_time()
last_day('2017-01-01')  2017-01-31
DAYNAME('2017-01-20') Friday
EXTRACT(YEAR FROM '2017-07-07') 
datediff('2017-01-02','2017-01-01')
- 聚集
count() max() min() avg() 

匹配条件

_ 单个字符 like ‘_’
% 任意数目的字符 like ‘%’
REGEXP 正则匹配 REGEXP ‘^[A-Z]’

NULL

select * from tab where param is NULL
select * from tab where val != 1 OR val is NULL

连接

using:
select a.name,b.city from a inner join b using (id)
不等连接：
select a.*,b.* from a inner join b on a.date<b.end_date and a.date>b.start_date

集合

union ： 并操作
union 去除结果重复数据
union all
intersect : 交操作 (mysql不支持)
except : 差操作 (mysql不支持)

分组

- select count(\*) from tab group by id,,,;
- 表达式： select count(\*) from tab group by extract(YEAR from start_date);
- 合计： select aid,bid,sum(balance) total_balance from tab group by aid,bid with rollup; 对sum结果进行合计
- Having
where子句在分组前执行即子句不能包含聚集函数，having分组之后执行
//可以在having语句中包含未在select语句中出现的聚集函数
select id,sum(balance) from tab group by id having min(balance)>1000 and max(balance) <1000; 

运算符

子查询

• 非关联查询<子查询独立于包含语句>
  select a,b from tab where id in (select id from tab1 where name = ‘bee’)
• 关联查询<子查询依赖于包含语句一列或者多列tab.id,关联查询不是在包含语句执行前一次性执行完毕，而是为每个候选行执行一次>
  select a,b from tab where 2 = (select count(*) from tab1 where tab1.id = tab.id)
  select a,b from tab where (select count(*) from tab1 where tab1.id = tab.id) between 1 and 10;

运算符 exists | not exists

exists 是构造包含关联子查询条件的最常用运算符,检查子查询至少返回一行
no exists 检查是否返回0行
- select tab.* from tab where exists (select 1 from tab1 where tab1.id = tab.id)
- update tab t set t.latest_date = (select max(z.date) from tab1 z where z.tab_id = t.id ) where exists ( select 1 from tab1 z where z.tab_id = t.id)
- delete from tab where not exists (select 1 from tab1 where tab1.tab_id = tab.id)

使用

• 数据源
  select tab.name, alias.id from tab inner join (select * from tab1 group by tab_id) alias on alias.tab_id = tab.id
• 数据加工
  select groups.name, count(*) number from (select sum(money) balance from tab group by name) as a1 inner join (select ‘small’ name, 0 low_limit, 100 hight_limit union all select ‘average’ name, 101 low_limit, 500 hight_limit union all select ‘hight’ name, 501 low_limit, 1000 hight_limit) groups ON a1.balance between groups.low_limit and groups.hight_limit group by groups.name;
• 面向任务
  select a.name, b.class, c.school from (select aid, bid, cid from referTab group by aid, bid, cid) groups inner join ta on ta.id = groups.aid inner join tb on tb.id = groups.bid inner join tc on tc.id = groups.cid;
• 过滤
  select count(*) num from tab group by var_id having count(*) = (select max(tab1.num) from (select count(*) num from tab group by var_id) tab1)

---

连接

• inner join
  select a.,b. from a inner join b on a.id = b.aid; //存在列为为null的不输出
• left join
  select a.,b. from a left join b on a.id = b.aid; // 左边表a决定结果集行数
• right join
  select a.,b. from a right join b on a.id = b.aid; // 右边表b决定结果集行数
• cross join 笛卡儿积

//输出2017每一天
select DATE_ADD('2017-01-01', INTERVAL(ones.num+ tens.num+ hunders.num) DAY) dt
from(
select 0 num union all
select 1 num union all
select 3 num union all
select 4 num union all
select 5 num union all
select 6 num union all
select 7 num union all
select 8 num union all
select 9 num union all
) ones cross join
(
select 0 num union all
select 10 num union all
select 20 num union all
select 30 num union all
select 40 num union all
select 50 num union all
select 60 num union all
select 70 num union all
select 80 num union all
select 90 num union all
) tens cross join
(
select 0 num union all
select 100 num union all
select 200 num union all
select 300 num union all
)
where DATE_ADD('2017-01-01', INTERVAL(ones.num+ tens.num+ hunders.num) DAY) < '2018-01-01' order by 1;

• natural join
  select a.name, b.name from a natural join b; //数据库服务器决定使用什么样的连接条件，若表a,b存在相同列名相当于内外连接，反之为笛卡儿积

---

条件逻辑 Case

case 
when C1 then E1
when C2 then E2
else 'nothing'
end

select id, case when name = 'bee' then concat('editor: ',name) when name = 'hat'  then concat('reader: ',name) end name from tab;

select tab.id,tab.roleID,case when tab.role = 'editor' then (select concat('editor: ',e.name) from tEditor e where e.id = tab.roleID) when tab.role = 'reader' then (select concat('reader: ',r.name) from tReader r where r.id = tab.roleID) else 'nothing' end name from tab;

case V0
when V1 then E1
when V2 then E2
else 'nothing'
end

select id,case name when 'bee' then concat('editor: ',name) when 'hat' then concat('reader: ',name) end name from tab;

使用

• 结果集行列变换

select YEAR(start_date) year, count(*) from tab where start_date > '2015-01-01' and start_date < '2017-01-01' group by YEAR(start_date)

select 
sum(case when extract(YEAR from start_date) = 2015 then 1 else 0) year_2015,
sum(case when extract(YEAR from start_date) = 2016 then 1 else 0) year_2016,
sum(case when extract(YEAR from start_date) = 2017 then 1 else 0) year_2017
from  from tab where start_date > '2015-01-01' and start_date < '2017-01-01'

• 选择性聚合

select sum(
case when avail_date > current_timestamp() then balance*2 else balance*-1 end
) from tab

• 存在性检查

select tab.id, tab.name, case
when exists(select 1 from account a where a.tid = tab.id and a.money > 0) then 'Y' else 'N' end
has_money from tab;

select tab.id,tab.name,case (
select count(*) from account a where a.tid = tab.id
) when 0, then 'none', when 1 then '1', when 2 then '2',else '2+' end
account_nums from tab;

• 除0错误
• null值处理

- 有条件更新

事务

start transaction;
update***;
savepoint up_after;
delete***;
rollback to savepoint up_after;
commit || rollback;

视图

• 数据安全（隐藏某些字段）
  create view tab_view (a,b,d) as select a,b,d from tab;
• 隐藏复杂性，数据聚合
  create view tab_view (a,b,c) as select a,(select count() from tab) tab_num,(select count() from tab1) tab1_num