mysql备忘录

--win,reset pwd
--kill mysqld.exe first
cmd1>cd c:\Program Files\MySQL\MySQL Server 5.6\bin
cmd1>mysqld --skip-grant-tables
cmd2>cd c:\Program Files\MySQL\MySQL Server 5.6\bin
cmd2>mysql
cmd2>use mysql;
cmd2>mysql
mysql>update user set password=password('123456') where user='root' and host='localhost';
cmd3>cd c:\Program Files\MySQL\MySQL Server 5.6\bin
cmd3>mysql -uroot -p123456

--start service
>mysqld --console
>net start mysql5
#service mysql start/restart

--stop service
>mysqladmin -uroot shutdown
>net stop mysql5
#service mysql stop

--DDL
>alter table emp add age int(2) after ename;
>alter table emp modify age int(2) first;
>alter table emp rename emp2;
>alter table tmp add partition (partition p3 values less than maxvalue);
>create table tb(dl int not null auto_increment,d2 varchar(10),index(d1,d2)/*primary key(d1)*/)engine=myisam
>partition by range(d1,d2)(
> partition p0 values less than (10,'3'), --list(d2) partition p0 values in ('1','3','5')
> partition p1 values less than (20,'2'),
> partition p2 values less than (30,'1')
>);
>create table tb(dl int not null,key idx_fk_country_id(d1)
> constraint fk_d1 foreign key(d1) references tb2(d1))engine=myisam default charset=utf8 collate=utf8_bin;
>create table tb engine=memory select * from dept;
>create table tb1 like tb2;
>create index idx_fk_age_desc on tb (d1,d2,d3);


--DML
>insert into dept values(1,'OA'),(2,'IT');
>select * from dept limit 2,3;
>select * from emp where deptno=(select deptno from dept limit 1);
>select deptno,count(1) from emp group by deptno with rollup;

--DCL
>grant select,insert on mydb.* to 'u1'@'localhost' identified by 'pwd123';
>grant execute on mydb.* to 'u1'@'localhost' identified by 'pwd123';
>revoke insert on mydb.* from 'u1'@'localhost';

--Help
>? contents
>? data types
>? show
>? create table
>? int

--Meta-data
>use information_schema;
>select * from tables where table_name like 'tmp%';
>select * from information_schema.routines where routine_name='p_test';
>show warnings;
>show tables;
>show tables like 'p%';
>show table status like 'tbname' \G;
>show index from tbname \G;
>show create view vRecord \G;
>show events \G;
>show functions;
>show variables like 'character_ser_server';
>show variables like 'character_ser_database';
>show variables like 'collation_server';


--Function
>select bin(f1),hex(f2) from emp;
>select insert('123456',3,2,'t'); --12t56
>select lpad('123',6,'0'); --000123
>select rpad('123',6,'0'); --123000
>select repeat('123,',3); --123,123,123,
>select now(); --2015-01-01 10:00:00
>select date_add(now(),interval 3 day); --2015-01-04 10:00:00
>select date_add(now(),interval -1 day); --2014-12-31 10:00:00
>select date_add(now(),interval '1_2' year_month); --2016-03-01 10:00:00
>select if(age>20,true,false);
>select ifnull(age,0);


--Procedure
>delimiter $$
>create procedure p_test(in p1 int,in p2 varchar(10),out p3 int)
>return int
>reads sql data
>begin
>select count(1) from dept where deptno=p1 or deptname=p2;
>select found_rows() into p3;
>--------------------
>declare cur_no int;
>declare cur cursor from select deptno from dept;
>declare exit handler for not found close cur;
>open cur;
>repeat fetch cur into cur_no;
> set @p3=cur_no;
>until 0 end repeat;
>close cur;
>--------------------
>ins:loop
> set @x=@x+1
> if @x=5 then iterate ins; --as continue 
> elseif @x=10 then leave ins; --exit loop
> end if;
>end loop ins;
>--------------------
>repeat set @x=@x+1;
>until @x>3 end repeat;
>--------------------
>while @x>0 do
> set @x=@x+1
>end while;
>
>return p3;
>end $$
>
>delimiter;
>
>call p_test(1,'it_',@cnt);
>select @cnt;

--Job
>create event myevent 
>on schedule
>every 5 second
>do
>insert into tb values(now());
>
>show variables like '%scheduler%%'; --job service status
>set global event_scheduler=1; --job service start
>show proccesslist \G;
>
>alter event myevent disable;
>drop event myevent;

--Lock
>show status like 'table_locks%';
>lock table tb read local; --enable insert
>lock table tb read/write,tb2 read/write; --disable insert/update
>unlock tables;
>set transaction; --commit the previous
>savepoint test;
>rollback to savepoint test;
>commit;
>select * from dept where deptno=10 lock in share mode; --for update
>show innodb status \G; --last deadlock status


--Partition
>select 
> partition_name name,
> partition_expression expr,
> partition_description descr,
> table_rows
>from information_schema.partitions
>where table_schema=schema() and table_name='dept';
>
>create table ...
>partition by range(d1,d2)(
> partition p0 values less than (10,'3'), --list(d2) partition p0 values in ('1','3','5')
> partition p1 values less than (20,'2'),
> partition p2 values less than (30,'1'));
>
>>create table ...
>partition by range(d1)
>subpartition by hash(d2)
>subpartition 2 (
> partition p0 values less than (10), 
> partition p1 values less than (20),
> partition p2 values less than  maxvalue);
>
>alter table dept reorganize partition p0,p1,p2 into (partition p0 values less than (20),partition p1 values less than (50)); --modify
>alter table dept add partition (partition p0 values less than (15)); --list partition modify

--Linux
$ du -sh tbname.* ###check table size
# more /etc/my.cnf --usr & pwd
>mysql -h 192.168.7.30 -P 3306 -uroot -p123456
$ mysql - uroot -p 123456 -e "select * from user;select * from dept" -E
$ mysql -uroot test -f -v < a.sql ###execute sql
$ mysqldump -uroot -p dbname tb1 tb2 -T ./bak ###backup to .sql/.txt

--Optimize
>optimize table dept; --Release space
>desc dept \G;
>desc select * from dept \G;
>desc select * from dept where deptno>5 \G; 
>explain select * from dept where deptno>5 \G; 
>show profiles; --after select
>show profile for query 3;
>show profile cpu for query 3;
>select * from dept use index (idx_fk_deptno) where deptno=5;
>select * from dept ignore index (idx_fk_deptno); --except the index

--manager
>check table tb; --or view or proc etc.
>alter table tb1 disable keys; --close index
>load data infile '/home/mysql/1.txt' into table tb1;
>select current_user();
>select * from dept into outfile '/tmp/dept.txt' fields terminated by "," enclosed by '"'; --export data
>system more /tmp/dept.txt --view data
>load data infile '/tmp/dept.txt' into table dept fields terminated by ',' enclosed by '"'; --import data