doc/heuristic.md
[toc]
select name from tbl t1 where id < 1000
select tbl.* as c1,c2,c3 from tbl where id < 1000
select name from tbl as tbl where id < 1000
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
ALTER TABLE tbl ADD COLUMN col int, ADD INDEX idx_col (`col`);
ALTER TABLE tbl DROP COLUMN col;
ALTER TABLE tbl DROP PRIMARY KEY;
select c1,c2,c3 from tbl where name like '%foo'
select c1,c2,c3 from tbl where name like 'foo'
SELECT * FROM sakila.film WHERE length >= '60';
SELECT * FROM tb WHERE col IN (NULL);
select id from t where num in(1,2,3)
select id from t where num is null
select c_id,c2,c3 from tbl where c2 like 'test%'
SELECT c1,c2,c3 FROM tbl WHERE c1 = 14 OR c1 = 17
SELECT 'abc '
SELECT * FROM t1 USE INDEX (i1) ORDER BY a;
select id from t where num not in(1,2,3);
INSERT INTO tb (a) VALUES (1), (2)
CREATE TABLE tb (a varchar(10) default '“”'
select id from t where id in(1, 2, id)
select id from tbl
select name from tbl where id < 1000 order by rand(number)
select c1,c2 from tbl where name=xx order by number limit 1 offset 20
select col1,col2 from tbl group by 1
select id from test where id=1 order by id
select tb1.col, tb2.col from tb1, tb2 where id=1 group by tb1.col, tb2.col
select c1,c2,c3 from t1 where c1='foo' group by c2
select description from film where title ='ACADEMY DINOSAUR' order by length-language_id;
select description from film where title ='ACADEMY DINOSAUR' GROUP BY length-language_id;
CREATE TABLE `test1` (`ID` bigint(20) NOT NULL AUTO_INCREMENT,`c1` varchar(128) DEFAULT NULL,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
这是一条很长很长的 SQL，案例略。
SELECT s.c_id,count(s.c_id) FROM s where c = test GROUP BY s.c_id HAVING s.c_id <> '1660' AND s.c_id <> '2' order by s.c_id
delete from tbl
update tbl set col=1
update tbl set col=1
select * from tbl where id=1
insert into tbl values(1,'name')
create table test(`id` int(11) NOT NULL AUTO_INCREMENT)
CREATE TABLE tbl (col int) ENGINE=InnoDB;
CREATE TABLE tbl (col int) ENGINE=InnoDB;
CREATE TABLE tbl ( cols ....);
CREATE TABLE tbl ( cols ....);
create table t1(id int,name char(20),last_time date)
CREATE TABLE tab2 (p_id BIGINT UNSIGNED NOT NULL,a_id BIGINT UNSIGNED NOT NULL,hours float not null,PRIMARY KEY (p_id, a_id))
create table tab1(status ENUM('new','in progress','fixed'))
select c1,c2,c3 from tbl where c4 is null or c4 <> 1
CREATE TABLE `tb`(`c` longblob NOT NULL);
CREATE TABLE tbl( `id` bigint not null, `create_time` timestamp);
CREATE TABLE `tb2` ( `id` int(11) DEFAULT NULL, `col` char(10) CHARACTER SET utf8 DEFAULT NULL)
CREATE TABLE `tbl` (`c` blob DEFAULT NULL);
CREATE TABLE tab (a INT(1));
CREATE TABLE tab (a varchar(3500));
CREATE TABLE tab (a BOOLEAN);
CREATE TABLE t1 (t TIME(3), dt DATETIME(6));
SELECT DISTINCT c.c_id,count(DISTINCT c.c_name),count(DISTINCT c.c_e),count(DISTINCT c.c_n),count(DISTINCT c.c_me),c.c_d FROM (select distinct id, name from B) as e WHERE e.country_id = c.country_id
SELECT COUNT(DISTINCT col, col2) FROM tbl;
SELECT DISTINCT * FROM film;
select id from t where substring(name,1,3)='abc'
SELECT c3, COUNT(*) AS accounts FROM tab where c2 < 10000 GROUP BY c3 ORDER BY num
select c1 || coalesce(' ' || c2 || ' ', ' ') || c3 as c from tbl
SELECT SYSDATE();
SELECT COUNT(1) FROM tbl;
SELECT SUM(COL) FROM tbl;
CREATE TRIGGER t1 AFTER INSERT ON work FOR EACH ROW INSERT INTO time VALUES(NOW());
CREATE PROCEDURE simpleproc (OUT param1 INT);
CREATE FUNCTION hello (s CHAR(20));
select film_id, title from film where release_year='2006' group by release_year
select c1,c2,c3 from t1,t2 join t3 on t1.c1=t2.c1,t1.c3=t3,c1 where id>1000
select tb1.col from (tb1, tb2) join tb2 on tb1.id=tb.id where tb1.id=1
select c1,c2,c3 from t1 left outer join t2 using(c1) where t1.c2=2 and t2.c3=4
select c1,c2,c3 from t1 left outer join t2 on t1.c1=t2.c1 where t2.c2 is null
select bp1.p_id, b1.d_d as l, b1.b_id from b1 join bp1 on (b1.b_id = bp1.b_id) left outer join (b1 as b2 join bp2 on (b2.b_id = bp2.b_id)) on (bp1.p_id = bp2.p_id ) join bp21 on (b1.b_id = bp1.b_id) join bp31 on (b1.b_id = bp1.b_id) join bp41 on (b1.b_id = bp1.b_id) where b2.b_id = 0
SELECT s,p,d FROM tbl WHERE p.p_id = (SELECT s.p_id FROM tbl WHERE s.c_id = 100996 AND s.q = 1 )
UPDATE users u LEFT JOIN hobby h ON u.id = h.uid SET u.name = 'pianoboy' WHERE h.hobby = 'piano';
SELECT s,p,d FROM tbl WHERE p.p_id = (SELECT s.p_id FROM tbl WHERE s.c_id = 100996 AND s.q = 1 )
create table test(`id` int(11) NOT NULL PRIMARY KEY (`id`))
create table test(col varchar(5000))
CREATE TABLE tab2 (p_id BIGINT UNSIGNED NOT NULL,a_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY (p_id, a_id),FOREIGN KEY (p_id) REFERENCES tab1(p_id),FOREIGN KEY (a_id) REFERENCES tab3(a_id))
create index idx1 on tbl (last_name,first_name)
CREATE TABLE tbl ( a int, b int, c int, KEY idx_a (`a`),KEY idx_b(`b`),KEY idx_c(`c`));
CREATE TABLE tbl ( a int, b int, c int, PRIMARY KEY(`a`,`b`,`c`));
CREATE TABLE tbl (a int);
SELECT * FROM tbl ORDER BY a DESC, b ASC;
CREATE UNIQUE INDEX part_of_name ON customer (name(10));
CREATE TABLE `tb` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`), FULLTEXT KEY `ip` (`ip`) ) ENGINE=InnoDB;
select SQL_CALC_FOUND_ROWS col from tbl where id>1000
CREATE TABLE tbl ( `select` int )
CREATE TABLE tbl ( `books` int )
select col as 列 from tb
update tb set status = 1 where id = 1;
INSERT INTO tbl SELECT * FROM tbl2;
INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
insert into tbl (IP,name) values('10.20.306.122','test')
select col1,col2 from tbl where time < 2018-01-10
select c1,c2,c3,c4 from tab1 where col_id REGEXP '[[:<:]]12[[:>:]]'
USE db
select c1,c2,c3 from t1 where c2='foo' group by c2
select col1,col2 from tbl where name=xx limit 10
UPDATE film SET length = 120 WHERE title = 'abc' LIMIT 1;
UPDATE film SET length = 120 WHERE title = 'abc' ORDER BY title
update tbl set col = 1 and cl = 2 where col=3;
select * from tbl where 1 != 1;
select * from tbl where 1 = 1;
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
SELECT * FROM tbl WHERE col = col = 'abc'
CREATE TABLE category (category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(25) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (category_id)
UPDATE category SET name='ActioN', last_update=last_update WHERE category_id=1
TRUNCATE TABLE tbl_name
create table test(id int,name varchar(20) not null,password varchar(200)not null)
delete from table where col = 'condition'
SELECT BENCHMARK(10, RAND())
select col1,col2 from tbl where type!=0
select col from sakila. film
select col from now where type!=0
CREATE TABLE ` abc` (a int);
select col1,col2,col3 from table1 where col2 in(select col from table2)
select teacher_id as id,people_name as name from t1,t2 where t1.teacher_id=t2.people_id union select student_id as id,people_name as name from t1,t2 where t1.student_id=t2.people_id
SELECT DISTINCT c.c_id, c.c_name FROM c,e WHERE e.c_id = c.c_id
SELECT * from tb where id in (select id from (select id from tb))
SELECT * FROM staff WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1)
SELECT * FROM staff WHERE name IN (SELECT max(NAME) FROM customer)
(SELECT * FROM tb1 ORDER BY name LIMIT 20) UNION ALL (SELECT * FROM tb2 ORDER BY name LIMIT 20) LIMIT 20;
CREATE TABLE trb3(id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE(YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005) );
create table test(`id` int(11) NOT NULL AUTO_INCREMENT)
create table dual(id int, primary key (id));
CREATE TABLE tbl (a int) AUTO_INCREMENT = 10;
CREATE TABLE tbl (a int) DEFAULT CHARSET = latin1;
create view v_today (today) AS SELECT CURRENT_DATE;
CREATE TEMPORARY TABLE `work` (`time` time DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE tbl (a int) DEFAULT COLLATE = latin1_bin;