sql 备忘

-- create table prize_match_info

-- (

-- match_type int not null default  0,

-- match_name varchar(128) not null default '',

-- begin_time varchar(128) not null default '',

-- end_time varchar(128) not null default '',

-- price int  not null default 0,

-- player_count int not null default 100,

-- multiple int not null default 100,

-- is_open int not null default 1

-- );

--

--

--

-- create table prize_sign_up

-- (

--  match_type int,

-- date varchar(128) not null default '',

-- player_id varchar(128),

-- score bigint not null default 0

-- );

--

--

--  create index score on prize_sign_up(score);

--  create index date on prize_sign_up(date);

--

-- insert into prize_sign_up(match_type,date,player_id) values(7,curdate(),'46');

-- select * from prize_sign_up;

-- alter table prize_match_info add cur_session varchar(128) not null default '' after multiple;

-- select player_id,score from prize_sign_up where date=curdate() and match_type=7 order by score desc limit 50;

--

--  create table bag

-- (

-- player_id varchar(128) not null default '',

-- cards_recorder_expired_time datetime

-- );

--

-- alter table bag add UNIQUE(player_id);

-- select * from bag where cards_recorder_expired_time >= now();

-- alter table player auto_increment=100000;

-- create table friend_application_list

-- (

-- src varchar(128),

-- dst varchar(128),

-- tm TIMESTAMP not null default CURRENT_TIMESTAMP

-- );

--

-- create table friend_system

-- (

-- self varchar(128),

-- friend varchar(128),

-- tm TIMESTAMP not null default CURRENT_TIMESTAMP

-- );

--

-- create index self on friend_system(self);

--

-- select * from friend_application_list;

-- select * from friend_system;

-- select count(src) as count from friend_application_list where src='' and dst='';

-- alter table player add UNIQUE(id);

-- select id,nickname,head_icon_url,account from (select * from friend_application_list where dst='100002') as a left join player on player.id=a.dst;

-- select id,nickname,head_icon_url,account from (select * from friend_system where self='100000') as a left join player on player.id=a.friend;

-- select * from player;

-- select head_icon_url,nickname,account from player where account like '%te%' limit 50;

-- select id,head_icon_url,nickname,account from player where account like '%%' limit 50;

-- select * from emails where TO_DAYS(now()) - TO_DAYS(tm) < 4;

-- alter table player_email add COLUMN tm TIMESTAMP not null default CURRENT_TIMESTAMP;

-- insert into emails(email_type,title,content,with_item,email_item_id_1,email_item_num_1,email_id) values(0,'test7','content7',1,4,1,'E26D9D966C17451C86F66060791D0107');

-- drop table farm;

-- create table farm

-- (

-- player_id int,

-- growth_stage int UNSIGNED not null default 0,

-- humidity int UNSIGNED not null default 100,  -- 湿度

-- fertilization_times int UNSIGNED  not null default 0,  -- 施肥次数

-- fertilizer_number int UNSIGNED  not null default 0, -- 肥料数量

-- big_water_number int UNSIGNED  not null default 0, -- 大桶水数量

-- small_water_number int UNSIGNED  not null default 0, -- 小桶水数量

-- weed int UNSIGNED  not null default 2,  -- 是否有杂草 1 有  2 无

-- pest int UNSIGNED  not null default 2 -- 是否有虫子

-- );

--

-- alter table farm add UNIQUE(player_id);

-- create table farm_friend_fertilization_recorder

-- (

-- self varchar(128),  -- 自己

-- friend varchar(128), -- 帮助的好友

-- help_times int,  -- 帮助次数

-- tm TIMESTAMP not null default CURRENT_TIMESTAMP

-- );

--

-- create index self on farm_friend_fertilization_recorder(self);

-- desc sign_in;

-- create table farm_sign_in

-- (

-- player_id varchar(128),

-- day1 TINYINT not null default 2,

-- day2 TINYINT not null default 2,

-- day3 TINYINT not null default 2,

-- day4 TINYINT not null default 2,

-- day5 TINYINT not null default 2,

-- day6 TINYINT not null default 2,

-- day7 TINYINT not null default 2,

-- the_last_sign_in_day varchar(128)

-- );

-- alter table farm_sign_in add UNIQUE(player_id);

-- desc farm_sign_in;

--  insert IGNORE into farm(player_id) values('123');

-- update farm set growth_stage=(

-- case

-- when growth_stage>=7 then 0

-- else growth_stage + 1

-- end

-- ) where player_id='100000';

-- select * from farm;

-- create table charge

-- (

-- player_id varchar(128),

-- goods_id int,

-- tm TIMESTAMP not null default CURRENT_TIMESTAMP

-- );

--

-- create index player_id on charge(player_id);

--

-- select score,rank,nickname,head_icon_url from (select b.* from (select t.* ,@rank := @rank + 1 as rank from(select @rank := 0) r,

-- (select player_id,score from prize_sign_up where date=curdate() and match_type=7 order by score desc) as t)

-- as b where b.player_id='100001') as a left join player on a.player_id = player.id;

select * from prize_sign_up;

select * from player;