MySQL SNIPPETS

mysql shell 脚本

#!/bin/bash
cmd="
TRUNCATE TABLE tbRank;
INSERT INTO tbRank SELECT iUin,iArea,0,num,num,(@curRank := @curRank+1) as rankNum FROM tbAccount,(select @curRank := 0) tmp_tb ORDER BY tbAccount.num DESC;
"
cnt=$(mysql -uroot -ppassword -h127.0.0.1 -P dbname -s -e "${cmd}")
echo ${cnt}
exit

 

创建一个测试库,包含学生表

CREATE TABLE student(
id   int(10) NOT NULL AUTO_INCREMENT,
name char(50) NOT NULL,
age  tinyint(4) NULL,
class_id int(10) NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

LOAD DATA INFILE

LOAD DATA INFILE '/Users/zhangxiaobin/Develop/php/data.txt' INTO TABLE user2 FIELDS TERMINATED BY ' ';
 LOAD DATA LOCAL INFILE '/data/home/xxx/import/data2/import/data2.txt' INTO TABLE tbBlackList FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (`iUin`,`iAreaId`);
 mysql --local-infile -uxxx -pxxx -hxx.xx.xx.xx -P0000

ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

赋予权限 创建用户

grant all on `user` to `db`@'localhost' identified by 'passwd';

用同表数据更新

update song_news a,song_news b set 
a.NewsContent = b.NewsContent,
a.NewsLang=b.NewsLang,        
a.NewsRela=b.NewsRela,
a.NewsTitle=b.NewsTitle,
a.NewsTitleColor=b.NewsTitleColor,
a.NewsExternal=b.NewsExternal,
a.NewsContent=b.NewsContent,
a.NewsPhoto=b.NewsPhoto,
a.NewsSource=b.NewsSource,
a.NewsRecommended=b.NewsRecommended,
a.NewsPublished=b.NewsPublished,
a.NewsKeyWord=b.NewsKeyWord,
a.NewsAudit=b.NewsAudit,
a.NewsClick=b.NewsClick,
a.NewsAgree=b.NewsAgree,
a.NewsDisagree=b.NewsDisagree,
a.NewsSort=b.NewsSort,
a.NewsTime=b.NewsTime
where b.ID=15;

排序显示对应名次

select id,score,(@rowno:=@rowno+1) as rowno from test,(select (@rowno:=0)) b order by score desc;

1

更新排名

#!/bin/bash
cmd="SELECT act_table FROM team_bat_info LIMIT 1"
cnt=$(mysql -uoss -poss_da -h -P3396 -s -e "${cmd}")
next_table="team_rank_$[(${cnt}+1)%2]"
cmd2="
TRUNCATE TABLE ${next_table};
INSERT INTO ${next_table} SELECT Fuin,Fzoneid,(@rowno:=@rowno+1) AS Rank FROM team_score,(select (@rowno:=0)) tmp_tb ORDER BY TeamScore DESC;
UPDATE team_bat_info SET total=(SELECT MAX(Rank) FROM ${next_table});
UPDATE team_bat_info SET act_table=act_table+1;

建个存储过程造数据

create procedure rankdata(IN num INT)
begin
declare i int;
set i=0;
while i<num do
insert into rank(`name`,`score`) values('rank',floor(rand()*10000));
set i=i+1;
end while;
end;

1、当前日期

select DATE_SUB(curdate(),INTERVAL 0 DAY) ;

2、明天日期
select DATE_SUB(curdate(),INTERVAL -1 DAY) ;

3、昨天日期

select DATE_SUB(curdate(),INTERVAL 1 DAY) ;

4、前一个小时时间

select date_sub(now(), interval 1 hour);

5、后一个小时时间

select date_sub(now(), interval -1 hour);

6、前30分钟时间

select date_add(now(),interval -30 minute)

7、后30分钟时间

select date_add(now(),interval 30 minute)

过滤条件为纯数字的条件

select count(*) IntCount,(select count(*) from tablename) rowCount
from tablename
WHERE CHAR_LENGTH(name) = CHAR_LENGTH(CAST(name AS UNSIGNED INTEGER)) — 为数字
or ISNULL(name)     — 为null值
or name=”;        — 为空