日常收集整理常见的mysql sql技巧

2024-07-24 12:45:17

字体：大中小

来源：转载

供稿：网友

废话不多说了，直接给大家贴代码了。

1，数字辅助表

//创建表create table test(id int unsigned not null primary key);delimiter //create procedure pnum(cnt int unsigned)begindeclare i int unsigned default 1;insert into num select i;while i*2 < cnt doinsert into num select i+id from num ;set i=i*2;end while;end//delimiter ;#####列值不连续问题：表a中id值为1，2,3,100,101,110，111set @q=0;select id,@q:=@q+1 as cn from a;#####对不连续的进行分组set @a=0;select min(id) as start_v,max(id) as end_v from (select id,cn,id-cn as diff from (select id,@a:=@a+1 as cn from pi) as p ) as pp group by diff;#####对不连续的值填充use test;DROP TABLE if EXISTS pincer;create table pincer(a int UNSIGNED);insert into pincer values(1),(2),(5),(100),(101),(103),(104),(105);select a+1 as start ,(select min(a)-1 from pincer as ww where ww.a>qq.a) as end from pincer as qq where not exists (select * from pincer as pp where qq.a+1=pp.a)and a<(select max(a) from pincer);################select id,num,ranknum,diff from (select id,num,ranknum,num-ranknum as diff from (select id,num,if(@id=id,@rownum:=@rownum+1,@rownum:=1) ranknum,@id:=id from tt,(select @rownum:=0,@id:=null) a ) b) c group by id,diff having count(*)>=2;################

2,生日问题

select name,birthday,if(cur>today,cur,next) as birth_dayfrom(select name,birthday,today,date_add(cur,interval if(day(birthday)=29 && day(cur)=28,1,0) day)as cur, date_ad(next,interval if(day(birthday)=29 && day(next)=28,1,0) day) as nextfrom(select name,birthday,today, date_add(birthday,interval diff year) as cur, date_add(birthday,interval diff+1 year) as next,from(select concat(laster_name,'',first_name) as name, birth_date as birthday, (year(now())-year(birth_date) )as diff, now() as today from employees) as a) as b) as c

3,日期问题----计算工作日

create table sals(id int ,date datetime ,cost int,primary key(id);select date_add('1900-01-01', interval floor(datediff(date,'1900-01-01')/7)*7 day) as week_start, date_add('1900-01-01', interval floor(datediff(date,'1900-01-01')/7*7+6 day) as week_end, sum(cost) from sales;计算工作日（指定2个日期段有多少工作日）create procedure pgetworkdays (s datetime,e datetime)beginselect floor(days/7)*5+days%7case when 6 between wd and wd+days%7-1 then 1 else 0 endcase then 7 between wd and wd+days%7-1 then 1 else 0 endfrom (select datediff(e,s)+1 as days,weekday(s)+1 as wd) as a;end;

mysql sql语句大全

1、说明：创建数据库
CREATE DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server
--- 创建备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'

上一篇：MySQL中Binary Log二进制日志文件的基本操作命令小结

下一篇：MySQL触发器运用于迁移和同步数据的实例教程