mysql-cookbook

-- get row number
select @n:=@n+1 as row_num, column_name from table, (select @n:=0) t2;

-- mysql
mysql -h host_name -p -u user_name;

-- tee
mysql --tee=tmp.out cookbook;
mysql> \T tmp.out -- Loggin to file 'tmp.out'
mysql> \t         -- Outfile disabled.

-- show columns form table_name\G <=> describe table_name\G < show full columns from table_name;

-- clean output
echo 'select * from table_name' | mysql db_name;

-- copy data from db_1 to db_2
mysqldump db_name [table] | mysql -h host_name -p -u user_name db_name;

-- create view
create view view_name as
select * from table_name

-- create user
create user user_name@host_name identified by 'password'
-- show grants
show grants for 'user_name'@'host_name';
-- grants
grant ALL ON table_name@table_name to 'user_name'@'host_name';

-- copy form one table to another
create table new_table select * from old_table;
insert into existing_table select * from old_table;

-- create temp table
create temporary table_name;

-- alter table engine
alter table table_name ENGINE = InnoDB;

-- use regex expresssion
select * from table_name where column_name regexp 'regex_expression';

-- show table status
show table status where name = 'table_name';

-- alter table engine
alter table table_name engine = engine_name;

-- user fulltext
alter table table_name add Fulltext [index_name](column_name);
select * from table_name where match(column_name) against('key_word');

-- rebuild index
repair table table_name;

-- timestamp
ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- time stamp
create table (created default current_timestamp, updated default current_timestamp on update current_timestamp);

-- substract datetime
select year(t), month(t), dayofmonth(t), hour(t), minute(t), second(t), date(t), time(t) from table_name;

-- time to second or second to time
select time_to_sec(t), sec_to_time(t);

-- date diff / time diff (there is function named to_days(datetime) )
select datediff(@date_1, @date_2) as 'date_1 - date_2', timediff(@t1, @t2) as 't1 - t2';

-- caculate age
select name, timestampdiff(unit, birthday, curdate()) as age from table_name;

-- get dayname (星期几), weekday(0-6, begin with mon), dayofweek(1-7, begin with sunday)
select dayname(curdate()) from table_name;

-- date_sub, date_add
select date_sub(curdate(), interval 1 day), date_add(curdate(), interval 1 day);

-- enum
create table weekday(day ENUM('sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday'));
select day, day + 1 from weekday;

-- specific order  ENUM 类型因为对应的是整数，所以不需要使用filed指定排序顺序
select * from table_name order by filed(column_name, 'column_value_1', 'column_value_2', ...);

-- count as criteria (use having)
select count(*）from table_name group by column_name having count > number;

-- split ip
SELECT ip,
	substring_index(full_ip, '.', 1) AS leftside,
	substring_index(substring_index(full_ip, '.', 2), '.', -1) AS leftside,
	substring_index(full_ip, '.', -1) AS leftside
FROM (
	SELECT
		ip,
		substring_index(concat('..', ip), '.', -3) AS full_ip
	FROM
		host) AS t1;


-- show table create sql
show create table table_name;

-- db information
select version();
select database(); --default db name;
select user(); --current user;
select current_user(); --privilege of current user;
show global status();
show variables;

-- mysql default params
/etc/mysql/my.cnf
.my.cnf
c:\program files\mysql\my.ini --windows
mysql --print-defaults <=> my_print_defaults client mysql

[client]
host = host_name
user = user_name
password = password


-- excute sql from file
shell> mysql db_name < filename

-- select into outfile
grant file on *.* to root@localhost;
select * into outfile '/home/ryan/mysql/output.txt' from table_name; (chmod 777 mysql)

-- load data
load data local infile '/home/ryan/mysql/output.txt' into table table_name [ (name, create_at, update_at) ]; 
load data local infile '/home/ryan/mysql/output.txt' into table host fields terminated by ',' enclosed by '"';

load data local infile 'data.csv' into table tabe_name
ignore 1 lines
(@data, @time, @name, @weight, @state)
set dt = contact(@data, '', @time),
    first_name = substring_index(@name, ' ' , 1),
    last_name = substring_index(@name, ' ', -1),
    weight_kg = @weight * .454,
    st_abbrev = (select abbrev from states where name = @state);
 

mysqlimport [options] db_name textfile1 [textfile2 ...] (textfile_name should be the same as the table_name)
[options]
--delete	delete	Empty the table before importing the text file
--force	force	Continue even if an SQL error occurs
--ignore	ignore	See the description for the --replace option
--replace	replace	The --replace and --ignore options control handling of input rows that duplicate existing rows on unique key values
--fields-terminated-by=string	fields-terminated-by	-- This option has the same meaning as the corresponding clause for LOAD DATA INFILE


Delete all Duplicate Rows except for One in MySQL
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

-- mysqldump
mysqldump -h host_name -p -u user_name db_name> filename;
mysqldump --no-create-info --tab=/tmp db_name table_name;


-- mysql 
mysql -e 'select * from host' [-s] db_name > shells.csv;

-- select column type
select column_type from information_schema.columns where table_schema = 'db_name' and table_name = 'table_name' and column_name = 'column_name'

-- we could make mysql auto truncated date by setting the sql_mode]
SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'

-- truncate table
turncate table table_name <=> delete from table_name;

-- rename column
alter table table_name change old_cloumn_name new_cloumn_name type;

-- rename table
RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2]

-- reserize auto_increment id
alter table table_name drop id;
alter table add cloumn id bigint(20) unsigned primary key auto_increment;

-- minximent auto inrement
alter table table_name auto_increment = 1;

-- add column at first
alter table table_name add column_name not null auto_increment first;

-- set auto_increment start value
create table t ( id int unsigned not null primary key auto_increment) auto_increment = 100;

-- insert into table_name (primary_key_col) value('value') on duplate key update copies = last_insert_id(copies + 1);

-- edit /etc/my.cnf and in the [mysqld] section, add the line:
federated

create table tb(
id int unsigned primary key not null auto_increment,
name varchar(60) not null
)
ENGINE=FEDERATED
CONNECTION = 'mysql://user_name:passwd@host_name/db_name/tb_name';


-- 用insert ignore 代替 insert, 忽略重复行
-- 用replace into 代替 insert into, 替代重复行

-- copy table struct
create table new_table_name like old_table_name;

-- show triggers
show triggers where `table` = 'table_name';

-- create trigger

CREATE TRIGGER <触发器名称>  --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER }  --触发器有执行的时间设置：可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE }  --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
ON <表名称>  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW  --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
<触发器SQL语句>  --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。

--你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER），如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。

-- example
CREATE TRIGGER trigger_name before [insert, update] ON table_name
FOR EACH ROW SET NEW.column_name = value;

DELIMITER $$

DROP TRIGGER IF EXISTS trigger_name;
CREATE TRIGGER trigger_name AFTER INSERT ON tb1
FOR EACH ROW
BEGIN
	insert into tb1(...) values (...);
END;

$$
DELIMITER ;


-- user defined function

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
func_parameter:
    param_name type
type:
    Any valid MySQL data type
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

 -- example
DELIMITER $$
drop function if exists get_cool;
create function get_cool(user varchar(8))
returns varchar(200)
begin
	if user is null then
		return ('null');
	else
		return ('not null');
	end if;
end;
$$

DELIMITER ;

-- drop primary key
ALTER TABLE table_name MODIFY id INT NOT NULL; -- remove auto_increment;
ALTER TABLE table_name DROP primary key;


-- event
create event mark_insert
on schedule every 5 minute
do insert into log(msg) values ('-- mark --')

set global event_scheduler = 1;
show variables like 'event_scheduler';
-- vi my.cnf, add event_scheduler = 1 under [mysqld]

create event mark_expire
on schedule every 1 day
do delete from log where ts < now() - interval 2 day;

-- drop event
drop event event_name;
-- disable and enable event
alter event event_name disable;
alter event event_name enable;


-- excute .sql
source 'path/to/sql'
\. 'path/to/sql'