--
-- create table
--
create table if not exists `text` (
    `id` int unsigned primary key auto_increment,
    `title` varchar(100) not null,
    `text` varchar(1000) not null,
    `author` varchar(100) not null,
    `update_time` timestamp ,
    unique key `authorIndex` (`author`),
) engine=innodb default charset=utf8;

--
-- drop table
--
drop table `text`;

--
-- insert table
--
insert into table `text` (title,text,update_time) values ("cest","babababbalalla",now())

--
-- select table
--
select * from `text` where id = 2;

--
-- update table
--
update `text` set `title` = "test" where `id` = 1;

--
-- delete table
--
delete from `text` where `id` = 1;

--
-- like
--
select * from `text` where `title` like "te%";

--
-- union
--
select * from `text` where `update_time` < unix_timestamp(now()) union all select * from `text2` where `update_time` < unix_timestamp(now()); 

--
-- order by
--
select * from `text` order by `update_time` asc;

--
-- group by
--
select `author`,count(*) from `text` group by `author`;

--
-- inner join
--
select a.title,a.author,b.author from `text` a inner join `text2` b on a.author = b.author;

--
-- left join
--
select a.title,a.author,b.author from text a left join text2 b on a.author = b.author;

--
-- right join
--
select a.title,a.author,b.author from text a right join text2 b on a.author = b.author where a.unix_timestamp < unix_timestamp(now());

--
-- is null
--
select * from text where update_time is not null;

--
-- transcation
--
begin;
insert into text (title,text,author) values("a","dddd","ga");
insert into text(title,text,author) values("b","ddd","dd");
commit;
-- rollback;

--
-- alter
--
alter table text drop update_time;
alter table text add update_time date;
alter table text modify text varchar(2000);
alter table text change update_time update_time timestamp;
alter table text change author author varchar(100) not null default 'galaxyobe';
alter table text alter text set default 'galaxyobe';
alter table text alter text drop default;
alter table text engine=myisam;
alter table text rename to text2;

--
-- index
--
create index authorIndex on text(author);
alter table text add index authorIndex(author);
alter table text drop index author;
drop index authorIndex on text;
alter table text modify author varchar(1000) not null;
alter table text add primary key (author);

show index from text;

--
-- mysqldump
--
mysqldump -uroot -ppassword -hhostname -database text | gzip > backup-text.sql.gz;
gunzip < backup-text.sql.gz | mysql -uroot -ppassword text;

mysqldump -uroot -ppassword -hhostname text | mysql -host=*.*.*.* -C text;