[Level 1] Testing notes for MySQL. (sql commands)
The following commands are my testing notes for MySQL.
regards,
Stanley Huang
set sql_mode:='';
-- set sql_mode='only_full_group_by'; -- with group by syntax, select columns must be included in group by list.
set sql_mode='traditional'; -- a set of modes, fail sql transaction while datatype error or out of range.
-- set sql_mode='pipes_as_concat'; -- set || from logic 'or' to string concatenation (standard sql).
-- set sql_mode='ansi_quote'; -- ' for string, " for column name
select @@sql_mode;
-- set sql_mode='no_auto_create_user'; -- for not auto create user when grant, except grant with identified by.
######################################### notes
## select @@query_cache_type
## @@query_cache_size
## show variable like 'query%';
## show status like 'qcache%';
## select sql_no_cache ...
/* mysql client */
/*
\c to cancle mysql statement.
*/
/* command */
/*
show processlist;
show full processlist;
show databases;
show tables;
use mysql;
show table status like 'user'
show table status like '%user%';
show full tables;
show tables from information_schema;
show columns from information_schema.character_sets;
show character set;
show global status;
show create function/procedure func_name/proc_name;
show function/procedure status;
show function/procedure status like 'xxx';
show function/procedure status where db='xxx' and name='yyy';
show triggers;
show collation; ## [like 'utf8%']
show errors;
select @@error_count;
show count(*) errors;
show warnings;
select @@warning_count;
show count(*) warnings;
show variables;
pager; -- pager on, default command is /usr/bin/less -ins.
pager /usr/bin/less -NJ10 ; -- show line no and jump 10 lines for once.
pager /usr/bin/more; -- set paging by command /usr/bin/more
nopager; -- pager off.
show variables like 'charset%';
check table mysql.user;
repair table mysql.user;
-- repair table support with engine
-- MyISAM
-- no support with engine
-- InnoDB, MEMORY
*/
/* create if not exists */
/*
create database if not exists test;
create table if not exists test (no int);
*/
/* special datatype */
/*
## enum max items: 65535
-- drop database christy; create database christy;
-- drop database if exists christy;
create database if not exists christy;
use christy;
create table mon_enum (
Mon enum('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
);
insert into mon_enum values (1),(2),(3),('ABC'),('May'),('JUN');
select * from mon_enum;
create table mon_set (
Mon set('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
);
insert into mon_set values('Jan');
insert into mon_set values(1),(2),(3),('ABC'),('May'),('JUN');
insert into mon_set values('Jan,Feb');
insert into mon_set values('Jan,Feb,Mar');
insert into mon_set values('Jan,XYZ,Mar');
insert into mon_set values('ABC');
insert into mon_set values('Jan, Feb,Mar, Apr,May'); -- result => 'Jan,Mar,May'
select * from mon_set;
## how many records can insert into testSetPK? if sql_mode is not strict_all_tables, strict_trans_tables
## => 4: '','a','b','a,b'
create table testSetPK (
a set('a','b'),
primary key(a)
) engine=MyISAM;
insert into testSetPK values ('a'),('b');
insert into testSetPK values ('b,a');
insert into testSetPK values ('a,b'); # ERROR 1062 (23000): Duplicate entry 'a,b' for key 'PRIMARY'
insert into testSetPK values ('ab'); # will insert empty '' into table.
select * from testSetPK;
# +-----+
# | a |
# +-----+
# | |
# | a |
# | b |
# | a,b |
# +-----+
# 4 rows in set (0.00 sec)
## how many records can insert into testEnumPK? if sql_mode is not strict_all_tables, strict_trans_tables
## => 3: '','a','b'
create table testEnumPK (
a enum('a','b'),
primary key(a)
) engine=MyISAM;
insert into testEnumPK values ('a'),('b'); # enum=1,2
insert into testEnumPK values ('abc'); # will insert empty '' into table. enum=0
select * from testEnumPK;
# +---+
# | a |
# +---+
# | |
# | a |
# | b |
# +---+
# 3 rows in set (0.00 sec)
*/
/* normal datatype */
/*
-- drop database chantelle; create database chantelle;
-- drop database if exists chantelle;
create database if not exists chantelle;
use chantelle;
set sql_mode='traditional';
-- auto_increment;
create table abc (i int);
insert into abc values (1),(2),(3),(4);
create table xyz (i int auto_increment, primary key(i));
insert into xyz values();
select last_insert_id(); -- => 1
insert into xyz values();
select last_insert_id(); -- => 2
insert into xyz values(),(),(),();
select last_insert_id(); -- => 3, not 6; get the first values generates for auto_increment.
delete from xyz where i between 2 and 5;
alter table xyz auto_increment=1;
select auto_increment from information_schema.tables
where table_schema='chantelle' and table_name='xyz'; -- => 6
delete from xyz where i >1;
alter table xyz auto_increment=1;
select auto_increment from information_schema.tables
where table_schema='chantelle' and table_name='xyz'; -- => 2
-- for testing zero fill
create table testZeroFill (
n int(5) zerofill
);
insert into testZeroFill values (1),(11),(111),(1111),(11111);
insert into testZeroFill values (-1),(-11),(-111),(-1111),(-11111);
create table integers (
n smallint unsigned
);
insert into integers values (-5);
select * from integers;
create table bits (
b bit(10)
);
insert into bits values (b'1'),(b'101'),(b'1000001');
select * from bits;
*/
/* temporal data type */
/*
create database if not exists chantelle;
use chantelle;
-- set sql_mode='traditional';
create table if not exists test_time (
z int,
a time,
b year,
c date,
d datetime,
e timestamp
);
insert into test_time(z) values (1);
insert into test_time(a) values ('23:59:59');
insert into test_time(b) values ('2008');
insert into test_time(b) values ('09');
insert into test_time(c) values ('2008-01-23');
insert into test_time(a) values ('23:59:59');
insert into test_time(d) values ('2008-01-23 23:59:59');
insert into test_time(e) values (NULL);
insert into test_time(d) values ('2008-01-23 23:59:59');
insert into test_time(a) values ('24:61:61'); -- error then stop
select * from test_time;
insert into test_time(c) values ('0000-00-00');
insert into test_time(c) values ('2008-02-31');
SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
-- datetime function:
-- ref:
-- http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
*/
/*************** mysql func() testing ***************/
/* sys */
/*
select version();
select database();
select user(), charset(user()), collation(user());
*/
/* date */
/*
select curdate(), current_date(),
curtime(), current_time(),
now(), current_timestamp();
select concat(year(now()),'-',month(now()),'-',day(now()),' ',hour(now()),':',minute(now()),':',second(now()));
select date_format(now(),get_format(datetime,'internal'));
select date_format(now(),get_format(datetime,'eur'));
select date_format(now(),'%Y-%m-%d %H.%i.%s');
select date_format('2003-10-03 23:59:59',get_format(datetime,'eur'));
select str_to_date(now(),get_format(datetime,'iso')), str_to_date(date_format(now(),get_format(datetime,'eur')),get_format(datetime,'eur'));
select str_to_date('10.31.2003',get_format(date,'usa'));
select makedate(2008,200), maketime(8,6,20);
*/
/* null */
/*
select isnull(null), isnull(0), isnull(1), isnull('');
select ifnull(null,'is null'), ifnull(null,'is null');
select concat('a','b'), concat('a',null,'b');
select if(null,'true','false'), if(isnull(null),'true','false');
*/
/* math */
/*
select truncate(1/7,9), 1/7, round(1/7,4), truncate(1/7,4), ceiling(1/7), floor(1/7), abs(-1/7);
select sign(1/7), sign(-1/7), sign(0);
select sin(0), cos(0), tan(0);
select pi(), degrees(pi()), radians(180);
create table test_std (
no int;
);
insert into t1 values (1),(2),(3);
select std(c1) from test_std;
## use with bulkInsert user defined function.
#A-Z, char(65)-char(90),
#a-z, char(97)-char(122),
truncate table t1;
truncate table t2;
call bulkInsert('t1','char(floor(rand()*26)+65),ceiling(rand()*27)',100);
call bulkInsert('t2','char(floor(rand()*26)+96),ceiling(rand()*27)',100);
*/
/* others */
/*
select repeat('*',20);
select least(1,3,-2,4,6,0), least('de','abc','x','a','yz','ghi');
select greatest(1,3,-2,4,6,0), greatest('de','abc','x','a','yz','ghi');
*/
/* flow control */
/*
select if(1>0,'yes','no');
select interval(5,2,3,7,8,3,2,0); -- ans. 2 => 7>5, then return the index of '3' (2);
*/
/* string */
/*
select instr('Alice and Bob', 'and'), locate('and', 'Alice and Bob'), position('and' in 'Alice and Bob');
select length('MySQL'), char_length('MySQL');
select length(convert('MySQL' using ucs2)), char_length(convert('MySQL' using ucs2));
select concat('to','see','is','to','believe');
select concat_ws(' ','to','see','is','to','believe');
select substring('George and Mary',0), substring('George and Mary',1), substring('George and Mary',8,3);
select substring('George and Mary',-4), substring('George and Mary',-8,3);
select substring_index('George and Mary','and',1), substring_index('George and Mary','and',0), substring_index('George and Mary','and',-1);
select substring_index('a b c d e',' ',1); -- a
select substring_index('a b c d e',' ',2); -- a b
select substring_index('a b c d e',' ',3); -- a b c
select substring_index('a b c d e',' ',-1); -- e
select substring_index('a b c d e',' ',-2); -- d e
select substring_index('a b c d e',' ',-3); -- c d e
select left('George and Mary',6), right('George and Mary',4);
select concat('<',trim(' and '),'>'), concat('<',ltrim(' and '),'>'), concat('<',rtrim(' and '),'>');
select trim(leading '0' from '01');
select replace('01', '0', '');
select insert('2359', 3, 0, ':'), insert('23____59', 3, 4, ':'); -- insert before posi 3, and 0 chars to be replaced; insert before posi 3, and 4 chars to be replaced.
select strcmp('abc','def'), strcmp('def','def'), strcmp('ghi','def');
select 'abc'='def', 'def'='def', 'ghi'='def';
*/
/*************** mysql func() testing ***************/
/*
-- drop database test; create database test;
-- drop database if exists test;
create database if not exists test;
use test;
create table test (
c nchar(10) default null,
a int default null
);
-- drop database joseph; create database joseph;
-- drop database if exists joseph;
create database if not exists joseph;
use joseph;
create table test (
c nchar(10) default null,
a int default null
);
insert into test.test values ('a',1);
insert into test.test values ('a',2);
insert into test.test values ('b',3);
insert into test.test values ('b',7);
insert into test.test values ('b',8);
insert into test.test values ('c',9);
insert into test.test values ('c',1);
insert into test.test values ('c',1);
insert into test.test values (NULL,NULL), (null,null);
select * from test.test;
insert into joseph.test values ('a',1);
insert into joseph.test values ('a',3);
insert into joseph.test values ('b',5);
insert into joseph.test values ('b',7);
insert into joseph.test values ('c',2);
insert into joseph.test values ('c',4);
insert into joseph.test values ('d',7);
insert into joseph.test values ('d',7);
insert into test.test values (NULL,NULL), (null,null);
select * from joseph.test;
select
*
from
test.test s,
joseph.test j
where s.a=j.a
order by 1
limit 0,5;
## limit not support sub-query or vairable.
## mysql > select * from t limit ( select count(*) from t where i='a' );
## mysql >
## mysql > set @a=1;
## mysql > select * from t limt @a;
select distinct
*
from
test.test s,
joseph.test j
where s.a=j.a
order by 1
limit 0,5;
select
c, a, count(1) -- count(1), psedo column with value 1; count(a) will lost count if the value of a is null.
from test
where 1=1
and c is not null
and a is not null
group by c,a
with rollup;
create database if not exists test;
create table test.fromTable (ino int);
insert into test.fromTable values (1),(2),(3);
create table test.toTable as select * from test.fromTable;
truncate table test.fromTable;
select * from test.fromTable;
insert into test.fromTable select * from test.toTable;
select * from test.fromTable;
select * from test.toTable;
*/
/* create database */
/*
create database test;
create database if exists test;
drop database test;
drop database if exists test;
create database Inno2 character set UTF8 collate UTF8_bin;
## test drop database fail...
create database dropDBFail;
system pfexec su - mysql -c "touch /usr/local/mysql/data/dropDBFail/dropDBFail.txt";
drop database dropDBFail;
show errors;
show databases like 'dropDBFail';
system pfexec su - mysql -c "rm usr/local/mysql/data/dropDBFail/dropDBFail.txt";
drop database dropDBFail;
show databases like 'dropDBFail';
*/
/* create table */
/*
drop database if exists test;
create database if not exists test;
use test;
-- enable MyISAM table to store checksum value,
-- to let "mysql> checksum table table_name;" to be faster.
-- Otherwise, it needs to do a full table scan to checksum table.
create table checkSumTable ( no int )
engine=MyISAM checksum=1;
-- temporary table
create temporary table tmpTable ( no int ); # visible only on current session and will be dropped when connection is closed.
drop temporary table tmpTable;
drop table if exists student;
create table student (
id int auto_increment,
name varchar(32) unique,
chi int not null,
eng int not null,
math int not null,
primary key (id)
);
insert into student (name,chi,eng,math) values ('Tom',100,100,100);
insert into student values (10,'Mary',90,90,90);
insert into student (name,chi,eng,math) values ('John',80,80,80);
insert into student values (5,'christy',70,70,70);
insert into student (name,chi,eng,math) values ('Mike',60,60,60);
select * from student;
delete from student where id = 12;
insert into student (name,chi,eng,math) values ('David',50,50,50);
select * from student;
create table selectStudent as select * from student; -- create table with default engine, and also insert data.
create table likeStudent like student; -- create table with the same engine.
alter table selectStudent add column location varchar(32) default 'Yilan' comment 'The location of students';
select * from selectStudent;
alter table selectStudent change location loc varchar(48); -- change column name and datatype.
alter table selectStudent modify loc varchar(32); -- change column datatype.
select * from selectStudent;
alter table selectStudent drop column loc;
alter table selectStudent rename to renameStudent;
rename table renameStudent to selectStudent;
rename table selectStudent to s, likeStudent to l;
select * from s,l;
-- eliminate duplicated data with create table as select
create table abc (i int);
insert into abc values (1),(2),(2),(3),(3),(3),(NULL),(NULL),(NULL),(NULL);
select * from abc;
create table cba
as select distinct * from abc;
drop table abc;
rename table cba to abc;
-- row format, for MyISAM engine
use test;
# create dynamic row
create table dynamicRow(id int, name varchar(32)) engine=MyISAM row_format=dynamic;
call bulkInsert('dynamicRow','rand()*100,repeat("a",rand()*32)',100);
show table status like 'dynamicRow';
# create fixed row
create table fixedRow(id int, name varchar(32)) engine=MyISAM row_format=fixed;
insert into fixedRow select * from dynamicRow;
show table status like 'fixedRow';
# create compressed table for dynamic row
create table compressedDynamicRow(id int, name varchar(32)) engine=MyISAM row_format=dynamic;
insert into compressedDynamicRow select * from dynamicRow;
system pfexec myisampack $MySQLDataDir/test/compressedDynamicRow;
system pfexec myisamchk -rq $MySQLDataDir/test/compressedDynamicRow;
# create compressed table for fixed row
create table compressedFixedRow(id int, name varchar(32)) engine=MyISAM row_format=fixed;
insert into compressedFixedRow select * from dynamicRow;
system pfexec myisampack $MySQLDataDir/test/compressedFixedRow;
system pfexec myisamchk -rq $MySQLDataDir/test/compressedFixedRow;
*/
/* primary key */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
id int auto_increment,
no int,
primary key (id)
);
create table t2 (
id int auto_increment,
no int
);
alter table t2 add primary key (id);
-- how to modify primary key: a. drop old one; b. create new one;
-- drop primary key: alter table table_name drop primary key;
insert into t1 (no) values (1),(2),(3);
insert into t2 (no) values (1),(2),(3);
explain t1\G
desc t1\G
explain t2\G
desc t2\G
explain select * from t1 where id=1;
desc select * from t1 where id=1;
explain select * from t1 where no=1;
desc select * from t1 where no=1;
*/
/* foreign key */
/*
mysql> set foreign_key_checks=0; -- It's useful while need to manage data and ignore constraints.
mysql> set unique_checks=0; -- It's useful while need to enhance performance to load mass data. -- unique check => check constraints row by row.
mysql> create table a(id int) engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> create table b(id int, p int, foreign key(p) a(id)) engine=innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a(id)) engine=innodb' at line 1
mysql> create table b(id int, p int) engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> alter table b add foreign key(p) references a(id);
ERROR 1005 (HY000): Can't create table 'test.#sql-5f3_3' (errno: 150)
mysql> show errors;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Error | 1005 | Can't create table 'test.#sql-5f3_3' (errno: 150) |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
=> 150, source column must be indexed!
mysql> alter table a add key(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table b add foreign key(p) references a(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
*/
/*
drop database if exists test;
create database if not exists test;
use test;
create table country (
code int not null auto_increment,
name varchar(32) not null,
sname char(2) not null,
primary key (code)
) engine=InnoDB; -- InnoDB implement the foreign key.
create table nPop (
pop int not null,
primary key (pop)
) engine=InnoDB; -- InnoDB implement the foreign key.
create table sCityName (
no int not null auto_increment,
name varchar(32) unique,
primary key (no)
) engine=InnoDB;
create table city (
id int not null auto_increment,
name varchar(32) not null,
cc int not null,
pop int default 0,
primary key (id),
foreign key (name)
references sCityName (name)
on delete no action,
foreign key (cc)
references country (code)
on delete cascade,
foreign key (pop)
references nPop (pop)
on delete set null
) engine=InnoDB; -- InnoDB implement the foreign key.
-- how to modify foreign key: a. drop old one; b. create new on;
-- drop foreign key: alter table table_name drop foreign key foreign_key_name;
insert into country values \
(null, 'Taiwan', 'TW') \
,(null, 'Japan', 'JP') \
,(null, 'China', 'CN') \
,(null, 'America', 'US') \
,(null, 'England', 'UK') \
,(null, 'French', 'FR');
insert into nPop values \
(100),(200),(300),(400),(500),(600);
insert into sCityName (name) \
values('Taipei'),('Tokyo'),('NewYork'),('Tainan'),('Washington'),('Paris');
insert into city values (null, 'Taipei', 1, 200);
insert into city values (null, 'Tokyo', 2, 400);
insert into city values (null, 'NewYork', 4, 600);
insert into city values (null, 'Tainan', 10, 200); // out of country code
insert into city values (null, 'Washington', 20, 800); // out of country code, nPop
insert into city values (null, 'Paris', 6, 1000); //out of country code, nPop,
select * from country;
select * from nPop;
select * from city;
delete from country where name='Taiwan';
delete from nPop where pop=400;
delete from sCityName where name='NewYork';
select * from country;
select * from nPop;
select * from city;
truncate table nPop;
select * from country;
select * from nPop;
select * from city;
*/
/* delete */
/*
drop database if exists test;
create database if not exists test;
use test;
create table delTable (
no int
);
insert into delTable values (1),(1),(2),(2),(3),(3),(4),(4);
select * from delTable;
delete from delTable where no=1 limit 1; -- delete from delTable order by no limit 1;
delete from delTable where no=2 limit 1;
delete from delTable where no=3 limit 1;
delete from delTable where no=4 limit 1; -- delete from delTable order by no desc limit 1;
select * from delTable;
*/
/* update */
/*
drop database if exists test;
create database if not exists test;
use test;
create table people (
id int not null,
name varchar(32),
age int,
primary key (id)
);
insert into people values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
update people set id=id-1; -- success.
select * from people;
update people set id=id+1; -- fail, when 1+1=2, 2 already exists, pk constraint error.
select * from people;
update people set id=id+1 order by id desc; -- success.
select * from people;
## update multi-table
use test;
drop table if exists t1,t2;
create table t1(n varchar(32),i int);
create table t2(n varchar(32),i int);
insert into t1 values('a',2);
insert into t1 values('b',4);
insert into t1 values('c',6);
insert into t2 values('a',7);
insert into t2 values('b',8);
insert into t2 values('d',9);
#
update t1 inner join t2 on t1.n=t2.n set t1.i=1, t2.i=1 where t1.n='a';
# update t1 inner join t2 using(n) set t1.i=1, t2.i=1 where t1.n='a'; # the same
select * from t1;
select * from t2;
*/
/* replace */
/*
drop database if exists test;
create database if not exists test;
use test;
create table people (
id int not null,
name varchar(32),
age int,
count int default 1,
primary key (id)
);
insert into people (id,name,age) values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
replace into people (id,name,age) values (2,'V2',25);
select * from people;
replace into people (id,name,age) values (5,'V3',28);
select * from people;
create table people2 as select * from people;
update people2 set id=id+10;
replace into people select * from people2;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
insert into people (id,name,age) values (2,'V2',25) on duplicate key update count=count+1;
select * from people;
*/
/* union */
/*
drop database if exists test;
create database if not exists test;
create table test_union1 (
id int increment,
no in
);
create table test_union2 (
id int increment,
no in
);
insert into test_union(no) values (1),(1),(2),(3),(4),(4),(5),(6),(7),(8),(9);
insert into test_union(no) values (2),(3),(8),(1),(4),(4),(5),(0),(0),(0),(0);
select * from t1 union selecct * from t2;
select * from t1 unionall selecct * from t2;
select * from t1 minus select * from t2; -- not supported
*/
/* transaction */
/*
-- save point
set @@autocommit=0;
drop database if exists test;
create database test;
use test;
create table a (
no int
) engine=InnoDB;
start transaction;
insert into a values (1);
select * from a;
savepoint s1;
insert into a values (2);
select * from a;
savepoint s2;
insert into a values (3);
select * from a;
savepoint s3;
rollback to savepoint s2;
select * from a;
rollback;
select * from a;
commit;
-- transaction
create table people (
id int not null,
name varchar(32),
age int,
count int default 1,
primary key (id)
) engine=InnoDB;
---- show engines information.
show engines;
create database if not exits test_engine;
use test_engine;
create table t1 (
id int
) engine=MEMORY;
create table t2 (
id int
);
create temporary table t3 (
id int
) engine=MEMORY;
create temporary table t4 (
id int
);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
insert into t3 values (1),(2),(3);
insert into t4 values (1),(2),(3);
---- set current autocommit off.
set autocommit = off;
set session autocommit = off;
set @@autocommit := 0;
---- start mysqld with default autocommit off.
---try ...
-- shell>mysqld -u root init-connect="set autocommit := off"
-- or modify my.conf ( not work for root )
insert into people (id,name,age) values (1,'Tom',10);
select * from people;
rollback;
select * from people;
start transaction;
insert into people (id,name,age) values (2,'Victor',21),(3,'Susan',15),(4,'Victor',31);
select * from people;
rollback;
select * from people;
*/
/* join */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
id int auto_increment,
no int,
name varchar(32),
primary key (id)
);
create table t2 (
id int auto_increment,
no int,
addr varchar(32),
primary key (id)
);
insert into t1 (no, name) values (1,'Tom'),(2,'Joseph'),(3,'Chantelle'),(4,'Christy');
insert into t2 (no, addr) values (1,'Taipei'),(2,'Taizhong'),(3,'Yilan'),(5,'Tainan');
-- caresian product.
select * from t1,t2;
select * from t1 cross join t2;
-- inner join.
select * from t1 join t2 on t1.no=t2.no;
select * from t1 inner join t2 using (no);
select * from t1,t2 where ( select no from t2 where t1.no=t2.no ) = ( select no from t1 where t2.no=t1.no);
select * from t1 inner join t2 on t1.no < t2.no;
select * from t1 inner join t2 on t1.id between t1.no and t2.no;
-- outer join.
select * from t1 left join t2 on t1.no=t2.no; -- major in left (must show left list), and join from right.
select * from t1 left outer join t2 on t1.no=t2.no; -- major in left (must show left list), and join from right.
select * from t1 right join t2 on t1.no=t2.no; -- major in right (must show right list), and join from left.
select * from t1 right outer join t2 on t1.no=t2.no; -- major in right (must show right list), and join from left.
-- joins in update and delete statements.
update t1
inner join t2
on t1.no = t2.no
set t1.id=t1.id+10,
t2.id=t2.id+100; -- can update t2, so rare!
select * from t1;
select * from t2;
select * from t1 join t2 using (no);
select * from t1 where exists (select null from t2 where t1.no=t2.no);
select * from t1 where t1.no in (select no from t2);
select * from t1 where t1.no = any (select no from t2);
select * from t1 where t1.no = some (select no from t2);
## test natural join
drop table if exists i,j,k;
create table t1(i int,j int,k int);
create table t2(j int,k int,l int);
insert into t1 values (1,2,3),(1,2,4);
insert into t2 values (2,3,7),(2,8,9);
select * from t1 natural join t2;
# => select * from t1 inner join t2 using(j,k);
# => select j, k, i, l from t1, t2 where t1.j=t2.j and t1.k=t2.k;
# result:
# +------+------+------+------+
# | j | k | i | l |
# +------+------+------+------+
# | 2 | 3 | 1 | 7 |
# +------+------+------+------+
# 1 row in set (0.00 sec)
## test cross join
select * from t1 cross join t2;
# result:
# +------+------+------+------+------+------+
# | i | j | k | j | k | l |
# +------+------+------+------+------+------+
# | 1 | 2 | 3 | 2 | 3 | 7 |
# | 1 | 2 | 4 | 2 | 3 | 7 |
# | 1 | 2 | 3 | 2 | 8 | 9 |
# | 1 | 2 | 4 | 2 | 8 | 9 |
# +------+------+------+------+------+------+
# 4 rows in set (0.00 sec)
*/
/* view */
/*
-- cannot link trigger on view.
drop database if exists test;
create database if not exists test;
use test;
create table city (
id int auto_increment,
name varchar(32),
pop int,
primary key (id)
);
create table cityMayer (
cityName varchar(32),
mayerName varchar(32),
primary key (cityName)
);
insert into city (name, pop) values ('Taipei',300);
insert into city (name, pop) values ('Tainan',200);
insert into city (name, pop) values ('Yilan',100);
insert into cityMayer values ('Taipei','John');
insert into cityMayer values ('Tainan','Mary');
insert into cityMayer values ('Taizhong','Tom');
-- create view.
create or replace view cityView (city, mayer, pop) as
select name, mayerName, pop
from city, cityMayer
where 1=1
and city.name=cityMayer.cityName
and pop > 100
with check option;
-- select view.
select * from cityView;
---- update view.
-- update cityView
-- set city='TaipeiCity'
-- where city='Taipei';
update cityView
set pop=10;
select * from city; -- update fail with check option.
update cityView
set pop=1000;
select * from city; -- success.
-- but how to create readonly view?????, grant read pri to user.
select * from cityMayer;
select * from cityView;
## test view with create/replace.
create view v as select * from m; # table m exists
drop table m;
show create view v; # view exist
create or replace v as select * from n; # table n exists
create or replace v as select * from t; # table t doesnot exist
show create view v; # view exist with old syntax
## with cascaded/local check option;
drop table if exists testCheckOption;
create table testCheckOption( t int );
create or replace view v1 as select * from testCheckOption where t<10;
create or replace view v2 as select * from v1 where t>0 with cascaded check option;
create or replace view v3 as select * from v1 where t>0 with local check option;
insert into v1 values(10); # success
insert into v2 values(20); # fail, even v1 doesnot have check option, v2 will cascaded checking v1 where cause.
insert into v3 values(30); # success
select * from testCheckOption;
select * from v1;
select * from v2;
select * from v3;
## priv
drop table if exists testViewPriv;
create table testViewPriv( n int );
create definer='christy'@'localhost' sql security definer view v_testViewPriv as select * from testViewPriv;
grant update on stanley.testViewPriv to 'christy'@'localhost';
grant update on stanley.v_testViewPriv to 'chantelle'@'localhost';
insert into testViewPriv values (1),(2),(3);
drop table if exists testViewPriv2;
create table testViewPriv2( n int );
create sql security invoker view v_testViewPriv2 as select * from testViewPriv2;
grant update on stanley.testViewPriv2 to 'chantelle'@'localhost';
grant update on stanley.v_testViewPriv2 to 'chantelle'@'localhost';
insert into testViewPriv2 values (1),(2),(3);
*/
/* prepare statement */
/*
-- prepare statement is session base.
drop database if exists test;
create database if not exists test;
use test;
create table city (
pop int
);
insert into city values (100),(200),(300),(400),(500);
prepare my_stmt from 'select * from city where pop > ?';
set @pop=200;
execute my_stmt using @pop;
deallocate prepare my_stmt;
*/
/* export/import file */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t (
id int auto_increment,
no int,
primary key (id)
);
insert into t (no) values (1), (2), (3), (4), (5), (6);
select * from test.t;
select * into outfile '/tmp/data/t.txt'
fields
terminated by ','
enclosed by '"'
lines terminated by '\n'
from test.t; -- default in /opt/mysql/mysql/data, if file exists then error return, how to overwrite it?????, no solution....
truncate table t;
load data infile '/tmp/data/t.txt'
into table t
fields
terminated by ','
enclosed by '"'
lines terminated by '\n';
select * from t;
*/
/*
shell>
mysqldump -uroot test > /tmp/mysql/data/test.sql
mysqldump -uroot test t > /tmp/mysql/data/test.t.sql
mysqldump -d -uroot test t > /tmp/mysql/data/test.t.schema_only.sql
mysql -u root < /tmp/mysql/data/test.sql
mysql -u root < /tmp/mysql/data/test.t.sql
shell>
mysql -u root <<EOF
truncate table test.t;
EOF
mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' \
--lines-terminated-by="\n" --ignore \
test /tmp/data/t.txt
## '--ignore/--replace' contain unique key values already in the table, 'test' is database name, 't' is the tablename.
*/
/* stored routines */
/*
## change stored routine characteristics, (3 characters)
## 1. SQL SECURITY:
## alter procedure p sql security [definer/invoker];
## 2. Data Use:
## alter procedure p [contains sql/no sql/reads sql data/modifies sql data];
## 3. COMMENT:
## alter procedure p comment 'This is comment...'
##
## create sample:
delimiter //
## cannot use the syntax: create procedure if not exists p()..., will fail!
drop procedure if exists p//
create procedure p()
no sql
sql security invoker
comment 'hello'
begin
select 'NULL';
end//
delimiter ;
*/
/* pattern of delimiter. */
/*
delimiter //
create procedure/function ...
begin
...
end//
delimiter ;
*/
/*
delimiter ;
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
id int auto_increment,
no int,
primary key (id)
);
create table t2 (
id int auto_increment,
no int,
primary key (id)
);
insert into t1 (no) values (1),(2),(2);
insert into t2 (no) values (1),(1),(2),(2),(3),(3);
delimiter //
create procedure test_t_count(out nReturn int, inout nIO int, in no2 int)
begin
select @nSes, nReturn, nIO, @no2 as no1;
if no2 = 1
then
select count(1) from test.t1;
elseif no2 = 2
then
select count(1) from test.t2;
else
select 'not match any case';
end if;
set @nSes=4, nReturn=5, nIO=6, no2=33;
end//
delimiter ;
-- test for update data with cursor
drop procedure if exists t;
delimiter //
create procedure t()
begin
declare done1 int default 0;
declare fet int default 0;
declare c cursor for select * from test.n;
declare continue handler for not found set done1=1;
open c;
lr:repeat
fetch c into fet;
if not done1 then
update test.n set n=fet+10 where n=fet;
end if;
until done1 end repeat;
close c;
end//
delimiter ;
drop procedure t;
-- test for use procedure to delete function/procedure, create succ, but call fail...
drop procedure if exists t;
delimiter //
drop procedure if exists t;
create procedure t()
begin
declare done1 int default 0;
declare db1 varchar(32) default '';
declare name1 varchar(32) default '';
declare type1 varchar(32) default '';
declare c cursor for select db,name,type from mysql.proc;
declare continue handler for not found set done1=1;
set @cmd2=''; -- must put after declare.
open c;
lr:repeat
fetch c into db1,name1,type1;
if not done1 then
select concat('drop ',type1,' ',db1,'.',name1) into @cmd2;
prepare my_stmt from @cmd2; -- drop function/procedure not support prepare statement yet.
execute my_stmt; -- prepare fail, so never reach this line
end if;
until done1 end repeat;
close c;
end//
delimiter ;
call t();
drop procedure t;
## call bulkInsert('m','1,1,1',0); # unlimited insert
## call bulkInsert('m','1,1,1',100); # insert 100 recoreds
## call bulkInsert('m','NULL,NULL,NULL',100); # insert 100 recoreds for all NULL
## call bulkInsert('test_partition_np','NULL,NULL,date_add(\'1995-01-01\', interval ceiling(rand()*5843) day)',1000000); ## sample
delimiter //
drop procedure if exists bulkInsert//
CREATE PROCEDURE bulkInsert(in sTableName varchar(32), in sValues varchar(256), in nCounts int)
begin
declare i int default 0;
#declare null_not_allowed condition for sqlstate '23000';
#declare null_not_allowed condition for 1048;
#declare continue handler for null_not_allowed begin end;
#declare continue handler for sqlstate '23000' begin end;
#declare continue handler for 1048 begin end;
#set @sPrepare='';
select concat('insert into ',sTableName,' values(',sValues,')') into @sPrepare;
#select @sPrepare;
prepare sCmd from @sPrepare;
repeat
execute sCmd;
set i=i+1;
-- select i as counter;
until i=nCounts end repeat;
end//
delimiter ;
## call bulkInsert2('m','id',0,0); # unlimited insert
## call bulkInsert2('m','id',1,100); # insert 100 recoreds, begin from 1
delimiter //
drop procedure if exists bulkInsert2//
CREATE PROCEDURE bulkInsert2(in sTableName varchar(32), in sColumn varchar(256), in nBegin int, in nCounts int)
begin
declare i int default 0;
#declare null_not_allowed condition for sqlstate '23000';
#declare null_not_allowed condition for 1048;
#declare continue handler for null_not_allowed begin end;
#declare continue handler for sqlstate '23000' begin end;
#declare continue handler for 1048 begin end;
foo:loop
if i>=nCounts then
leave foo;
else
set @sPrepare='';
select concat('insert into ',sTableName,'(',sColumn,') values(',nBegin,')') into @sPrepare;
prepare sCmd from @sPrepare;
execute sCmd;
set i=i+1;
set nBegin=nBegin+1;
-- select i as counter;
end if;
end loop;
end//
delimiter ;
## test recursive procedure
## mysql> set @@max_sp_recursion_depth=1000;
## mysql> set @maxLoop=100;
## mysql> call recursivePorcedure(@maxLoop);
delimiter //
drop procedure if exists recursiveProcedure;
create procedure recursiveProcedure(inout n int)
begin
#select 'a' into @a;
## select * from t; # can select
## call t(); # can call another procedure
## call recursiveProcedure(n); # can call resursive procedure, but have to set @@max_sp_recursion_depth first, default=0
.
select n;
set n=n-1;
if n > 0 then
call recursiveProcedure(n);
end if;
#if n=0 then
# return; #exit; #quit; #leave; ## not working, how to quit procedure??????
#end if;
#
#The generic way to exit a block is the LEAVE statement:
#create procedure myproc()
#MAIN_BLOCK: begin
# declare v_panic bool default false
# ..
# ..
# if v_panic then
# leave MAIN_BLOCK;
# end if;
# ..
# ..
#end MAIN_BLOCK;
end//
delimiter ;
show create procedure test.test_t_count;
show procedure status;
show procedure status where Db='test';
drop procedure if exists test_t_count;
set @nSes=1, @nReturn=2, @nIO=3, @no1=11, @no2=22;
select @nSes, @nReturn, @nIO, @no1, @no2;
call test_t_count(@nReturn, @nIO, @no1);
select @nSes, @nReturn, @nIO, @no1, @no2;
delimiter //
create function table_count(column_no int)
returns int
deterministic
begin
declare nReturn int default 0;
declare nCount1 int default 0;
declare nCount2 int default 0;
if column_no = 1
then
select count(distinct id) into nCount1 from t1;
select count(distinct id) into nCount2 from t2;
elseif column_no = 2
then
select count(distinct no) into nCount1 from t1;
select count(distinct no) into nCount2 from t2;
else
set nCount1=0;
set nCount2=0;
end if;
set nReturn=nCount1+nCount2;
set @nT=nReturn;
return @nT;
-- return nCount1+nCount2;
-- return nReturn;
end//
## function can call function, insert/update/delete table
## when use binlog, have to declare the function modify data or not, (use "NO SQL") or set global log_bin_trust_function_creators=1 (default 0)
delimiter //
drop function if exists upTable//
create function upTable()
returns int
deterministic
begin
insert into m values (2);
return 0;
end//
delimiter ;
delimiter //
drop function if exists seTable//
create function seTable()
returns int
no sql
deterministic
begin
declare nReturn int default 0;
select count(1) into nReturn from m;
return nReturn;
end//
create function seTable(i int) ## create fail, function name exist!
returns int
no sql
deterministic
begin
declare nReturn int default 0;
select count(1) into nReturn from m;
return nReturn;
end//
delimiter ;
delimiter //
drop function if exists t;
create function t()
returns int
deterministic
begin
set @n=1; -- set @n
return 2;
set @m=3; -- never reach here.
end//
delimiter ;
set @nT=0;
select table_count(3);
select table_count(2);
select table_count(1);
select @nT;
set @nT=null;
show create function test.table_count;
show function status;
show function status where Db='test';
drop function if exists table_count;
*/
/* flow control */
/*
delimiter ;
create database if not exists test;
use test;
*/
/* if */
/*
## select if
select if(i=0,0,1) from m;
drop function if exists testIF;
delimiter //
create function testIF(n int)
returns varchar(32)
deterministic
begin
if n=1
then
return 'true';
else
return 'false';
end if;
end//
delimiter ;
select testIF(0);
select testIF(1);
drop function testIF;
*/
/* case */
/*
## select case...
select
case month when "01" then "January"
when "02" then "February"
when "03" then "March"
when "04" then "April"
when "05" then "May"
when "06" then "June"
when "07" then "July"
when "08" then "August"
when "09" then "September"
when "10" then "October"
when "11" then "November"
when "12" then "December"
end # not 'end case' here, when use select case ...
from calendar where year = "2005" order by month;
drop function if exists testCase;
delimiter //
create function testCase1(n int)
returns varchar(32)
no sql
deterministic
begin
case n
when 1 then
return '1';
when 2 then
return '2';
else
return 'no match';
end case;
end//
delimiter ;
select testCase1(1);
select testCase1(2);
select testCase1(3);
drop function testCase1;
drop function if exists testCase2;
delimiter //
create function testCase2(n int)
returns varchar(32)
deterministic
begin
case
when n = 1 then
return '1';
when n = 2 then
return '2';
else
return 'not match';
end case;
end//
delimiter ;
select testCase2(1);
select testCase2(2);
select testCase2(3);
drop function testCase2;
*/
/* repeat */
/*
drop function if exists testRepeat;
delimiter //
create function testRepeat(n int)
returns int
deterministic
begin
declare nCount1 int default 0;
declare nCount2 int default 0;
repeat
set nCount1 = nCount1 + 1;
set nCount2 = nCount1 * nCount1;
-- until nCount1 >= n -- cannot left ';' here.
-- end repeat;
until nCount1 >= n end repeat; -- best practice.
return nCount2;
end//
delimiter ;
select testRepeat(1);
select testRepeat(2);
select testRepeat(3);
drop function testRepeat;
*/
/* while */
/*
drop function if exists testWhile;
delimiter //
create function testWhile(n int)
returns int
deterministic
begin
declare nCount int default 1;
while nCount < n
do
set nCount = nCount + 1;
end while;
return nCount;
end//
delimiter ;
select testWhile(1);
select testWhile(2);
select testWhile(3);
drop function testWhile;
*/
/* loop */
/*
drop function if exists testLoop;
delimiter //
create function testLoop(n int)
returns int
deterministic
begin
declare nCount int default 1;
-- loop usually within label, and use leave label to exit loop.
mylabel:loop
if nCount >= n
then
leave mylabel;
else
set nCount=nCount+1;
iterate mylabel;
end if;
-- never reach here...
set nCount=1000;
end loop mylabel;
return nCount;
end//
delimiter ;
select testLoop(1);
select testLoop(2);
select testLoop(3);
drop function testLoop;
*/
/* condition & handler */
/*
drop database if exists test;
create database test;
use test;
create table d_table (
n int,
primary key (n)
);
delimiter //
create procedure dohandler(in n int, out x int)
begin
declare nCount int default 1; -- variable and condition declaration must before cursor and handler.
declare dup_keys condition for sqlstate '23000'; -- variable and condition declaration must before cursor and handler.
-- declare c cursor for select * from test.testCursor1; -- must declare before handler -- sample only
-- declare continue handler for not found set done=1; -- sample only
declare continue handler for dup_keys set @garbage=1; -- declare handler for contidion.
truncate table test.d_table;
set @garbage=0;
while nCount <= n
do
set x = nCount;
insert into test.d_table values (1);
set nCount = nCount + 1;
end while;
end//
delimiter ;
call dohandler(1,@x);
select @garbage, @x;
call dohandler(2,@x);
select @garbage, @x;
call dohandler(3,@x);
select @garbage, @x;
drop procedure dohandler;
*/
/* cursor */
/*
drop database if exists test;
create database test;
use test;
create table test.testCursor1 (
no int
);
create table test.testCursor2 like test.testCursor1;
insert into test.testCursor1 values (1),(2),(3),(4),(5);
delimiter //
## test cursor scope.
drop procedure if exists testCursorScope//
create procedure testCursorScope()
begin
begin
declare dup_key condition for sqlstate '23000';
declare c cursor for select * from test.m;
declare continue handler for dup_key set @x=1;
open c;
end;
begin
declare n int default 0;
fetch c into n;
end;
end//
## result::
#mysql> source /tmp/t.sql
#Query OK, 0 rows affected, 1 warning (0.00 sec)
#ERROR 1324 (42000): Undefined CURSOR: c
## test cursor scope2.
## nothing to see, because open cursor not lock table? it's rare...
drop procedure if exists testCursorScope2//
create procedure testCursorScope2()
begin
begin
declare i int default 0;
declare j int default 0;
declare not_null_allowed condition for sqlstate '23000';
declare c cursor for select * from test.m;
declare d cursor for select * from test.n;
declare continue handler for not_null_allowed set @x=1;
## case 1:
open c;
fetch c into i;
open d;
fetch d into j;
select "case 1:";
select "use following command to check table status:";
select " show open tables;";
select " flush tables;";
select " show open tables;";
select sleep(20);
## case 2:
close c;
select "case 2:";
select "use following command to check table status:";
select " flush tables;";
select " show open tables;";
select sleep(20);
end;
## case 3:
select "case 3:";
select "use following command to check table status:";
select " flush tables;";
select " show open tables;";
select sleep(20);
end//
## test cursor insensitive
## steps:
## 1. create procedure testCursorInsensitive.
## > copy and paste with following procedure.
## 2. create table m with values 1,2,3.
## > create table m (i int); insert into m values(1),(2),(3); select * from m; # => 1,2,3
## 3. call procedure testCursorInsensitive.
## > call testCursorInsensitive();
## 4. open another session and modify data.
## > insert into m values(7); delete from m where i=2; update m set i=0 where i=1; select * from m; # => 0,3,7
## 5. wait the procedure return.
## => 1,2,3
## Result:
## The cursor does not see the change after the cursor is opened.
delimiter //
drop procedure if exists testCursorInsensitive//
create procedure testCursorInsensitive()
begin
declare v int default NULL;
declare done int default 0;
declare dup_key condition for sqlstate '23000';
declare my_not_found condition for sqlstate '02000';
declare c cursor for select * from test.m;
declare continue handler for dup_key begin end;
declare continue handler for not found set done=1; # shorthand
declare continue handler for my_not_found set done=1; # with my declare condition
open c;
select sleep(20);
repeat
fetch c into v;
if done != 1
then
select v;
end if;
until done = 1 end repeat;
close c;
end//
delimiter ;
delimiter //
drop procedure if exists testCursor//
create procedure testCursor()
begin
declare done int default 0;
declare n int default 0;
declare c cursor for select * from test.testCursor1; -- must declare before handler
declare continue handler for not found set done=1;
open c;
repeat
fetch c into n;
if not done then
insert into test.testCursor2 values (n);
-- if n=3 then
-- set done=1;
-- end if;
end if;
until done end repeat;
close c;
end//
delimiter ;
call testCursor();
select t1.no t1, t2.no t2
from test.testCursor1 t1
left join testCursor2 t2
on t1.no=t2.no;
drop table testCursor1;
drop table testCursor2;
drop procedure testCursor;
*/
/* trigger */
/*
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
id int auto_increment,
name varchar(32),
primary key (id)
);
create table i1 (
id int,
name varchar(32),
primary key (id)
);
create table d1 (
id int,
name varchar(32),
primary key (id)
);
create table u1 (
old_id int,
old_name varchar(32),
new_id int,
new_name varchar(32)
);
-- insert trigger
create trigger t1_i1
after insert
on t1
for each row
insert into i1 values (new.id, new.name);
-- delete trigger
create trigger t1_d1
after delete
on t1
for each row
insert into d1 values (old.id, old.name);
-- update trigger
create trigger t1_u1
after update
on t1
for each row
insert into u1 values(old.id, old.name, new.id, new.name);
-- PS. the foreign key with cascade action, not active trigger.
insert into t1 (name) values ('Tom'),('John'),('Mary');
update t1 set id=id+10;
delete from t1;
select * from t1;
select * from i1;
select * from d1;
select * from u1;
-- table declaration
drop table if exists p;
create table if not exists p (id int, no int);
drop table if exists c;
create table if not exists c (cid int, pid int, name varchar(32));
drop table if exists old_p;
create table if not exists old_p(id int, no int);
drop table if exists old_c;
create table if not exists old_c(cid int, pid int, name varchar(32));
-- end table declaration
-- trigger declaration
delimiter //
-- create succ, but run fail, ERROR 1442 (HY000): Can't update table 'p' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
--
-- drop trigger if exists ai_p//
-- create trigger ai_p after insert on p
-- for each row
-- begin
-- if new.id is null then
-- delete from p where id is null;
-- end if;
-- end//
drop trigger if exists ad_p//
create trigger ad_p after delete on p
for each row
begin
insert into old_p values (old.id, old.no);
delete from c where pid=old.id;
end//
drop trigger if exits ad_c//
create trigger ad_c after delete on c
for each row
begin
insert into old_c values (old.cid, old.pid, old.name);
end//
insert into p values (1,1),(2,2),(3,3);
insert into c values (1,1,'tom'),(2,2,'joseph'),(3,3,'christy'),(4,4,'chantelle');
delimiter ;
-- end trigger declaration
*/
/* lock table */
/*
set sql_mode='';
drop database if exists test;
create database if not exists test;
use test;
create table t1 (
no int
);
create table t2 (
no int
);
create table t3 (
no int
);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
insert into t3 values (1),(2),(3);
lock tables t1 write, t2 read, t3 write, t3 as t3_4r read;
select * from t1; -- success
insert into t1 values (4),(5),(6); -- success
select * from t1; -- success
select * from t2; -- success
insert into t2 values (4),(5),(6); -- fail. if run command by stdin, will exit program.
select * from t2; -- success
select * from t3;
insert into t3 select * from t3;
select * from t3;
insert into t3 select * from t3 as t3_4r;
select * from t3;
unlock tables;
*/
/* cache */
/*
use test;
set global test_cache.key_buffer_size=128*1024;
cache index t1,t2 in test_cache;
load index into cache t1,t2;
load index into cache t1,t2 ignore leaves;
set global test_cache.key_buffer_size=0;
*/
/* partitiion table */
/*
-- partitions must be integer or trsnfer to integer by func.
-- supports MyISAM, InnoDB, Archive storage engines.
show variables like '%have_partitioning%';
drop database if exists test;
create database if not exists test;
use test;
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
create table tl (
id int
) engine=MyISAM
partition by list(id)
(
partition p0 values in (0,2,4,6,8),
partition p1 values in (1,3,5,7,9)
);
create table tk1 (
id int,
tr_date date,
primary key (id)
) engine=InnoDB
partition by key()
partitions 10;
create table tk2 (
id int not null, -- must have not null
tr_date date,
unique key (id)
) engine=InnoDB
partition by key()
partitions 10;
create table tk3 (
id int,
tr_date date,
primary key (id)
) engine=InnoDB
partition by key(id)
partitions 10;
create table tk4 (
id int,
tr_date date
) engine=InnoDB
partition by linear key(id)
partitions 10;
explain partition select * from tk4 where id=1;
-- range
drop table tr1;
create table tr1 (
id int
) engine = InnoDB
partition by range (id)
(
partition p0 values less than (10),
partition p1 values less than (20),
partition pm values less than (maxvalue)
);
drop table tr2;
create table tr2 (
id int
) engine = InnoDB
partition by range (id)
(
partition p0 values less than (10),
partition p1 values less than (20)
);
mysql> insert into tr2 values (30);
ERROR 1526 (HY000): Table has no partition for value 30
mysql>
## convert from partition table to normal table
alter table tr2 remove partitioning;
## convert from normal table to partition table
alter table tr2
partition by range (id)
(
partition p0 values less than (10),
partition p1 values less than (20)
);
## drop partition data.
alter table tr2 drop partition p0;
*/
*/
/*
-- subpartition
drop table ts1;
create table ts1 (
id int,
no int
) engine=InnoDB
partition by list (mod(id,2))
subpartition by hash (no)
subpartitions 2 (
partition p0 values in (0),
partition p1 values in (1)
);
drop table ts2;
CREATE TABLE ts2 (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000)
(
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s4,
SUBPARTITION s5
-- ,SUBPARTITION s6 -- error with not mismatch sub partitions
)
)
;
*/
/* storage engine merge */
/*
########################### ??? create merge fail, recheck again...
## can use a mix of compressed and uncompressed tables.
#drop database if exists test;
create database if not exists test;
use test;
drop table t1,t2,t3,total1,total2;
CREATE TABLE t1 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(20)
) ENGINE=MyISAM;
CREATE TABLE t2 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(20)
) ENGINE=MyISAM;
CREATE TABLE t3 (
a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
message VARCHAR(20)
) ENGINE=MyISAM;
call bulkInsert('t1','NULL,\"t1\"',1000);
call bulkInsert('t2','NULL,\"t2\"',1000);
call bulkInsert('t3','NULL,\"t3\"',1000);
#INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
#INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
#INSERT INTO t3 (message) VALUES ('Testing'),('table'),('t3');
system pfexec myisampack $MySQLDataDir/test/t3
CREATE TABLE total1 (
a INT NOT NULL AUTO_INCREMENT,
message CHAR(20),
INDEX(a)
) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
CREATE TABLE total2 (
a INT NOT NULL AUTO_INCREMENT,
message CHAR(20),
INDEX(a)
) ENGINE=MERGE UNION=(t1,t3) INSERT_METHOD=LAST;
select * from total1;
select * from total2;
##ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
INSERT INTO total1 (message) VALUES ('Testing'),('table'),('total1');
INSERT INTO total2 (message) VALUES ('Testing'),('table'),('total2');
*/
/* case sensitive */
/*
drop database if exists test;
create database if not exists test;
use test;
create table case_cs_test (
word VARCHAR(10)
) CHARACTER SET latin1 COLLATE latin1_general_cs; -- use _cs to case sensitive.
insert into case_cs_test values ('A'),('a'),('B'),('b'),('C'),('c');
select *
from case_cs_test
where word rlike '[A-Z]';
*/
/* create user */
/*
-- if joseph not exists, create it.
-- if joseph exists, change his password to 'new_password'.
create user joseph@localhost identified by 'new_password';
grant select on world.* to joseph@localhost identified by '123';
grant all on test.* to 'joseph'@'localhost' identified by '123'
with
max_user_connections 1234
max_connections_per_hour 1235
max_queries_per_hour 1236
max_updates_per_hour 1237;
select * from world.City;
revoke select on world.* from joseph@localhost;
-- create user by clone someone priv.
drop user 'joe'@'localhost';
system rm /tmp/t.sql; ## \! rm /tmp/t.sql;
select * into outfile '/tmp/t.sql' from mysql.user where user='stanley' and host='localhost';
system perl -pi -e "s/localhost\tstanley/localhost\tjoe/" /tmp/t.sql;
load data infile '/tmp/t.sql' into table mysql.user;
*/
/* federated engine */
/*
-- not support by default, must start mysqld with option --federated
drop database if exists world;
create database if not exists world;
use world;
CREATE TABLE City (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT '',
CountryCode char(3) NOT NULL DEFAULT '',
District char(20) NOT NULL DEFAULT '',
Population int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (ID)
) ENGINE=InnoDB;
grant select on world.City to 'bob'@'localhost' identified by '123';
CREATE TABLE FedCity (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL DEFAULT '',
CountryCode char(3) NOT NULL DEFAULT '',
District char(20) NOT NULL DEFAULT '',
Population int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (ID)
) ENGINE=FEDERATED
CONNECTION='mysql://bob:123@localhost/world/City';
insert into City(Name,CountryCode,District,Population) values ('Tom','TW','Taipei',100);
insert into City(Name,CountryCode,District,Population) values ('Joseph','TW','Taipei',100);
insert into City(Name,CountryCode,District,Population) values ('Christy','TW','Taipei',100);
*/
/* create user */
/*
## create user with clear text password
create user bob identified by '123';
create user joseph@localhost identified by '123';
## update password with clear text
set password for 'bob'@'localhost' = password('789');
set password = password('abc'); -- set the password of non-anonymous client.
update mysql.user set password=password('456') where user='bob';
## update password with encypted text
set password for 'stanley'@'localhost' = '*2FCDC0F13666DF2E09612610C2987BE092AB473D'; -- set the password with encrypted password by command "set password".
update mysql.user set password='*2FCDC0F13666DF2E09612610C2987BE092AB473D' where user='stanley' and host='localhost'; -- set the password with encrypted password by update "mysql.user" data table.
###################################### priv_types:
## 1. table priv_type:
## alter, create view, create, delete, drop, grant option, index, insert, select, show view, and update
grant all on *.* to 'bob'@'%'; -- auto create user. use sql_mod='no_auto_create_user' to avoid auto create user.
grant all on *.* to 'bob'@'%' identified by 'abc'; -- although use sql_mod='no_auto_create_user', still create user. (***)
grant all on *.* to 'bob'@'localhost';
grant select on *.* to 'bob'@'localhost';
grant all on mysql.* to 'bob'@'localhost';
grant select on mysql.* to 'bob'@'localhost';
grant all on mysql.user to 'bob'@'localhost';
grant select on mysql.user to 'bob'@'localhost';
##
## 1.x show grants:
show grants for 'bob'@'localhost';
## 2. column priv_type:
## insert, select, and update
grant select (col1), insert (col1, col2) on mysql.user to 'bob'@'localhost';
## 3. routine priv_type:
## alter routine, create routine, execute, and grant option
grant create routine on mysql.* to 'bob'@'localhost';
grant execute on procedure on mysql.proc to 'bob'@'localhost';
## 4. with grant option:
grant select on world.* to 'bob'@'localhost' with grant option;
##
## 4.x revoke with grant option:
## revoke grant option on world.* from 'bob'@'localhost';
############ security.
## over ssl
grant all privileges on test.* to 'bob'@'localhost'
identified by 'bob123' require ssl;
## limit login host
#grant all privileges on test.* to 'bob'@
#1. '%'
#2. '%.sun.com'
#3. '192.168.1.0/255.255.255.0'
#3. '192.168.1._' -- .0~.9
## limit connect, query per hour
grant all on world.*
to 'bob'@'localhost'
with
max_user_connections 1
max_connections_per_hour 10
max_queries_per_hour 50
max_updates_per_hour 20;
show grants;
show grants for current_user();
## check user
ex.
mysql> select user(), current_user();
+----------------+----------------+
| user() | current_user() |
+----------------+----------------+
| bob@a.b.c.d | bob@% |
+----------------+----------------+
drop user test;
*/
/* create index */
/*
create table ttt (
id int,
no int,
grade int,
name varchar(32),
index idx_ttt_id (id) -- key idx_ttt_id(id)
) engine=MyISAM;
create index idx_ttt_id on ttt(id);
create index idx_ttt_no on ttt(no);
create index idx_ttt_grade on ttt(grade);
create index idx_per_name on ttt(name(10)); -- create prefix index.
create index idx_pre_name1 on ttt(left(name,10)); -- not work, not support functional index.
create index idx_pre_name2 on ttt(name(10)); -- only use the index with conditon name='test'; not left(name,8)='test.
show index from ttt;
-- using index ( like oracle hint )
SELECT * FROM ttt USE INDEX (idx_ttt_id,idx_ttt_no)
WHERE col1=1 AND col2=2 AND col3=3;
SELECT * FROM ttt IGNORE INDEX (idx_ttt_grade)
WHERE col1=1 AND col2=2 AND col3=3;
# syntax:
# create index index_name type on table_name(columns);
# alter table table_name add index type(columns);
# type:
# using btree|hash|rtree
create index hash1 using hash on t(i,j);
alter table t add index using hash(i,j);
drop table if exists testIndex;
create table testIndex(i int, j int, index idx1(i,j), index idx2(j));
insert into testIndex values (1,1),(2,2);
## use index
explain select * from testIndex use index (idx1)\G
explain select * from testIndex use index (idx2)\G
explain select * from testIndex use index (idx1,idx2)\G
## ignore index
explain select * from testIndex ignore index (idx1)\G
## ignore index is equivalent the combination of hints:
## 1. explain select * from testIndex ignore index for join (idx1)\G
## 2. explain select * from testIndex ignore index for order by (idx1)\G
## 3. explain select * from testIndex ignore index for group by (idx1)\G
explain select * from testIndex ignore index (idx2)\G
explain select * from testIndex ignore index (idx1,idx2)\G
## force index
explain select * from testIndex force index (idx1)\G
##However, it is an error to mix USE INDEX and FORCE INDEX for the same table:
##SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
-- predict prefix
create table t ( name varchar(32));
insert into t values ('this'),('is'),('a'),('dog'),('that'),('is'),('a'),('cat'),('good'),('morning'),('mr'),('li'),('good'),('morning'),('good'),('afternoon'),('good'),('evening'),('good'),('night');
select
count(*) as 'Total Rows',
count(distinct name) as 'Distinct Values',
count(*) - count(distinct name) as 'Duplicate Values'
from t;
set @n=2;
select
count(distinct left(name,@n)) as 'Distinct Prefix Values',
count(*) - count(distinct left(name,@n)) as 'Duplicate Prefix Values'
from t;
-- fulltext index
create table fulltext_sample (
id int,
name varchar(8),
addr varchar(32)
);
alter table fulltext_sample add fulltext fulltext_name(name);
#alter table t3 add fulltext tf(i);
select * from fulltext_sample where match(name) against('tom');
-- special index
create table lookup (id int) engine=memory;
create index idx_lookup_id using btree on lookup(id);
*/
/* max rows */
/*
## specifying MyISAM Maximum Row Count.
## Note that, despite the name MAX_ROWS does not enforce a maximun number of rows for table. The option value is only used to calculate the pointer size. Not force, but also no warning!!
## [mysqld]
## myisam_data_pointer_size=7 ## default: 6
## case 1:
## the fixed-length is not need avg_row_length.
create table t1(id int) engine=MyISAM max_rows=2;
alter table t1 max_rows=4;
insert into t1 values (1),(2),(3),(4),(5);
select * from t;
## case 2:
create table t2(name varchar(32) engine=MyISAM avg_row_length=16 max_rows=4000000;
alter table t2 avg_row_length=24 max_rows=3000000;
*/
/* table maintenance */
/*
check table t; -- for MyISAM, InnoDB. also update index status and view definition.
repair table t; -- for MyISAM only.
analyze table t; -- for MyISAM, InnoDB. analyze the key values of the table.
optimize table t; -- for MyISAM, InnoDB. clean up MyISAM by defragment, sort index pages, update index statistics. update InnoDB index statistics and free space in the clustered index.
checksum table t; -- for MyISAM, ???
*/
/* profiling */
/*
select @@profiling;
set profiling=1;
select * from mysq.user;
create database test_profiling;
use test_profiling;
create table p1 ( name varchar(32));
insert into p1 values ('Tom'),('Christy');
select * from p1 where name='Tom';
--
show profile;
show profiles;
show profile for query 1; -- get query id from show profiles.
show profile cpu for query 1;
--
set profiling=0;
*/
/* dangerous */
/*
create database if not exist test;
use test;
create table if not exist aInt ( n int );
create table if not exist bVar ( n varchar(2));
insert into aInt values (1),(2),(3);
insert into bVar values ('1a'),('2b'),('3c');
select * from aInt a, bVar b where a.n=b.n;
set sql_mode=oracle; -- not work.
select * from aInt a, bVar b where a.n=b.n;
set sql_mode=traditional; -- not work.
*/
/* tee */
/*
tee /tmp/tee.log;
select * from information_schema.tables
where table_name=user;
notee;
*/
/* group_concat */
/*
select 'call group_concat.................';
create database if not exists test;
use test;
create table if not exists testGroupConcat (i int, v varchar(32));
insert into testGroupConcat values (1,'a'),(1,'b'),(2,'z'),(2,'y'),(2,'x'),(3,'i'),(3,NULL),(3,'j'),(NULL,'p'),(NULL,'q'),(NULL,'r');
select * from testGroupConcat;
select i, group_concat(v) from testGroupConcat;
select i, group_concat(v order by (v)) from testGroupConcat;
select i, group_concat('-', v order by (v)) from testGroupConcat;
select i, group_concat('-', v order by (v) separator ':') from testGroupConcat;
*/
/* purge binary log */
/*
## purge binary logs
purge binary logs to 'mysql-bin.010';
purge binary logs before '2009-05-31 23:59:59';
*/
/* functions */
/*
## sleep
select sleep(10); # sleep 10 sec, then return 0.
*/
/* manipulating table data */
/*
## update multi-tables
update m inner join n using(i)
set m.i=4, n.i=4;
## delete multi-tables
delete m,n from m,n where m.i=n.i; # success deleting from m,n
delete from m,n using m,n where m.i=n.i; # success deleting from m,n
delete from m inner join n using(i); # success deleting from m,n
delete m,n from m join n on m.i=n.i; # success deleting from m,n
delete m from n where m.i=n.i; # ERROR 1109 (42S02): Unknown table 'm' in MULTI DELETE
delete t1,t3 from t1,t2,t3
where t1.id=t2.id and t3.id=t2.id; # success deleting from t1,t3
delete t1,t3 from t1
inner join t2 using(id)
inner join t3 using(id); # success deleting from t1,t3
use test;
drop table if exists gc;
drop table if exists c;
drop table if exists p;
create table p (id int primary key) engine=innodb;
create table c (id int primary key, pid int, foreign key (pid) references p(id)) engine=innodb;
create table gc (id int primary key, pid int, foreign key (pid) references c(id)) engine=innodb;
insert into p values (1),(2);
insert into c values (1,1),(2,1),(3,2);
insert into gc values (1,1),(2,1),(3,1),(4,2),(5,2),(6,3);
############################################### all delete methods fail...
delete p,c,gc
from p
inner join c on c.pid = p.id
inner join gc on gc.pid = c.id
;
delete gc,c,p
from p
inner join c on c.pid = p.id
inner join gc on gc.pid = c.id
;
delete gc,c,p
from gc
inner join c on gc.pid = c.id
inner join p on c.pid = p.id
;
############################################### all delete methods fail...
*/
/* replication */
/*
## refer to test_createReplication.txt
start/stop slave;
start/stop slave io_thread;
start/stop slave sql_thread;
## for my.cnf
####################################################### replication
## @master server, set bin log database
#binlog-do-db=db1
#binlog-do-db=db2
#binlog-do-db=db3
## @slaver server1, set replicated database
#replicate-do-db=db1
#replicate-do-db=db2
## @slaver server2, set replicated database
#replicate-do-db=db2
#replicate-do-db=db3
*/
/* event */
/*
set global event_scheduler=on; # default is off
grant event on stanley.* to 'bob'@'localhost';
create
definer='stanley'@'localhost' # defualt is CURRENT_USER
event if not exists event_name
on schedule every 10 second
on completion preserve # default is not preserve
enable # default is enable, enable/disable/disable on slave
comment 'put comments here'
do
select now() into outfile '/tmp/now.txt'
*/
/* grant */
/*
## the grant target is signature, so the target doesnot have to exist.
## ex. table ttt.yyy doesnot exist. but still can be granted,
## except funcion/procedure.
## mysql> grant all on ttt.yyy to 'guest'@'localhost';
## mysql> grant all on function db.func_must_exist to 'guest'@'localhost';
##
## grant/revoke all with grant option.
## ex.
## mysql> grant all on *.* to 'guest'@'localhost' with grant option;
## mysql> revoke all, grant option on *.* from 'guest'@'localhost';
##
## user level
#grant all on *.* to 'guest'@'localhost';
grant select,update on *.* to 'guest'@'localhost';
select *
from mysql.user
where 1=1
and user='guest'
and host='localhost'
and select_priv='y'
and update_priv='y'\G
## database level
#grant all on world.* to 'guest'@'localhost';
grant select,update on world.* to 'guest'@'localhost';
select *
from mysql.db
where 1=1
and user='guest'
and host='localhost'
and db='world'
and select_priv='y'
and update_priv='y'\G
## table level
#grant all on world.City to 'guest'@'localhost';
grant select,update on world.City to 'guest'@'localhost';
select *
from mysql.tables_priv
where 1=1
and user='guest'
and host='localhost'
and db='world'
and table_priv = 'select,update'\G
## column level, will also update mysql.tables_priv
grant select(ID,Name), update(Name) on world.City to 'guest'@'localhost';
select *
from mysql.columns_priv
where 1=1
and user='guest'
and host='localhost'
and db='world'
and table_name='City'
and (
(column_name='ID' and column_priv='select') or
(column_name='Name' and column_priv='select,update')
)\G
select *
from mysql.tables_priv
where 1=1
and user='guest'
and host='localhost'
and db='world'
and column_priv = 'select,update'\G
## proc level
delimiter //
drop procedure if exists world.testProc;
create procedure world.testProc()
no sql
begin
select '1';
end//
drop function if exists world.testFunc;
create function world.testFunc()
returns int
no sql
deterministic
begin
return 1;
end//
delimiter ;
#grant all on procedure world.testProc to 'guest'@'localhost';
grant execute on procedure world.testProc to 'guest'@'localhost';
#grant alter on procedure world.testProc to 'guest'@'localhost';
grant all on function world.testFunc to 'guest'@'localhost';
#grant execute on function world.testFunc to 'guest'@'localhost';
#grant alter on function world.testFunc to 'guest'@'localhost';
select *
from mysql.procs_priv
where 1=1
and user='guest'
and host='localhost'
and db='world'
and (
(routine_name='testProc' and routine_type='PROCEDURE' and proc_priv='execute') or
(routine_name='testFunc' and routine_type='FUNCTION' and proc_priv='execute,alter routine')
)\G
## the column "routine_type" is case sensitive,
## the column "proc_priv" value is
## 'execute' if only execute priv
## 'alter routine' if only alter priv
## drop user and also delete above priv data.
drop user 'guest'@'localhost';
select * from mysql.user where user='guest';
select * from mysql.db where user='guest';
select * from mysql.tables_priv where user='guest';
select * from mysql.columns_priv where user='guest';
select * from mysql.procs_priv where user='guest';
*/
/* change user password */
/*
grant usage on *.* to 'guest'@'localhost' identified by 'new_password';
grant usage on *.* to 'guest'@'localhost' identified by password '*0913BF2E2CE20CE21BFB1961AF124D4920458E5F'; # *0913... ( encrypted password )
set password for 'guest'@'localhost' = password ('new_password');
update mysql.user set password=password('new_password');
*/
/* status */
/*
## connection information.
show status like 'Connections';
show status like 'Aborted_connects';
show status like 'Aborted_clients';
show status like 'Threads_connected';
##
## 1. Connections
## 2. Aborted_connects
## 3. Connections - Aborted_connects
## 4. Aborted_clients
## 5. Connections - Aborted_connects - Aborted_clients
## 6. Threads_connected
##
## Com variables give you a breakdown of the number of statements
## that the server has executed by statement type.
show global status like 'Com%';
## open tables;
show status like 'Opened_tables';
## amount of traffic
show status like 'Bytes_received';
show status like 'Bytes_sent';
## server load
## case 1: Questions/Uptime, how many queries per second the server has processed.
show global status like 'Questions';
show global status like 'Uptime';
## case2: slow queries
show global status like 'slow_queries';
## case3: show process
show processlist;
*/
/* select */
/*
# default elements: 256, default memory: 8192
select * from table_name procedure analyse(max_elements, [max_memory]);
*/
/* character set */
/*
set names utf8;
CREATE TABLE a ( a VARCHAR(32)) CHARACTER SET='BIG5' COLLATE='BIG5_BIN';
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;
*/
/* connection, kill session */
/*
kill connection 5;
*/
/*
import data skip foreign key constraint check:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;
*/
Wish this helps. regards,
Stanley Huang
Comments
Post a Comment