353 lines
6.0 KiB
Plaintext
353 lines
6.0 KiB
Plaintext
DROP TABLE IF EXISTS t1, t2;
|
|
CREATE TABLE t1 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
|
|
INSERT INTO t1 (a,b) VALUES (100,'foobar'),(1,'z'),(200,'bar');
|
|
CREATE TABLE t2 (a INT, b CHAR(8), pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
|
|
INSERT INTO t2 (a,b) SELECT a, b FROM t1;
|
|
INSERT INTO t1 (a,b) SELECT a, b FROM t2;
|
|
SELECT * FROM t1;
|
|
a b pk
|
|
1 z 2
|
|
1 z 5
|
|
100 foobar 1
|
|
100 foobar 4
|
|
200 bar 3
|
|
200 bar 6
|
|
SELECT DISTINCT a FROM t1;
|
|
a
|
|
1
|
|
100
|
|
200
|
|
SELECT ALL b, a FROM t1;
|
|
b a
|
|
bar 200
|
|
bar 200
|
|
foobar 100
|
|
foobar 100
|
|
z 1
|
|
z 1
|
|
SELECT STRAIGHT_JOIN t1.* FROM t2, t1 WHERE t1.a <> t2.a;
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
1 z 5
|
|
1 z 5
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 4
|
|
100 foobar 4
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 6
|
|
200 bar 6
|
|
SELECT SQL_SMALL_RESULT t1.a FROM t1, t2;
|
|
a
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
100
|
|
100
|
|
100
|
|
100
|
|
100
|
|
100
|
|
200
|
|
200
|
|
200
|
|
200
|
|
200
|
|
200
|
|
SELECT SQL_BIG_RESULT SQL_CALC_FOUND_ROWS DISTINCT(t2.a)
|
|
FROM t1 t1_1, t2, t1 t1_2;
|
|
a
|
|
1
|
|
100
|
|
200
|
|
Warning 1287 SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead.
|
|
Warnings:
|
|
SELECT FOUND_ROWS();
|
|
FOUND_ROWS()
|
|
3
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
SELECT a+10 AS field1, CONCAT(b,':',b) AS field2 FROM t1
|
|
WHERE b > 'b' AND a IS NOT NULL
|
|
GROUP BY 2, field1
|
|
HAVING field1 < 1000
|
|
ORDER BY field2, 1 DESC, field1*2
|
|
LIMIT 5 OFFSET 1;
|
|
field1 field2
|
|
11 z:z
|
|
110 foobar:foobar
|
|
SELECT SUM(a), MAX(a), b FROM t1 GROUP BY b WITH ROLLUP;
|
|
SUM(a) MAX(a) b
|
|
2 1 z
|
|
200 100 foobar
|
|
400 200 bar
|
|
602 200 NULL
|
|
SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
|
|
INTO OUTFILE '<DATADIR>/select.out'
|
|
CHARACTER SET utf8
|
|
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''';
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
200,'bar'
|
|
200,'bar'
|
|
100,'foobar'
|
|
100,'foobar'
|
|
1,'z'
|
|
1,'z'
|
|
SELECT t1.a, t2.b FROM t2, t1 WHERE t1.a = t2.a ORDER BY t2.b, t1.a
|
|
INTO DUMPFILE '<DATADIR>/select.dump';
|
|
ERROR 42000: Result consisted of more than one row
|
|
SELECT t1.*, t2.* FROM t1, t2 ORDER BY t2.b, t1.a, t2.a, t1.b, t1.pk, t2.pk LIMIT 1
|
|
INTO DUMPFILE '<DATADIR>/select.dump';
|
|
1z2200bar3
|
|
SELECT MIN(a), MAX(a) FROM t1 INTO @min, @max;
|
|
SELECT @min, @max;
|
|
@min @max
|
|
1 200
|
|
SELECT t1_1.*, t2.* FROM t2, t1 AS t1_1, t1 AS t1_2
|
|
WHERE t1_1.a = t1_2.a AND t2.a = t1_1.a;
|
|
a b pk a b pk
|
|
1 z 2 1 z 2
|
|
1 z 2 1 z 2
|
|
1 z 5 1 z 2
|
|
1 z 5 1 z 2
|
|
100 foobar 1 100 foobar 1
|
|
100 foobar 1 100 foobar 1
|
|
100 foobar 4 100 foobar 1
|
|
100 foobar 4 100 foobar 1
|
|
200 bar 3 200 bar 3
|
|
200 bar 3 200 bar 3
|
|
200 bar 6 200 bar 3
|
|
200 bar 6 200 bar 3
|
|
SELECT alias1.* FROM ( SELECT a,b FROM t1 ) alias1, t2 WHERE t2.a IN (100,200);
|
|
a b
|
|
1 z
|
|
1 z
|
|
1 z
|
|
1 z
|
|
100 foobar
|
|
100 foobar
|
|
100 foobar
|
|
100 foobar
|
|
200 bar
|
|
200 bar
|
|
200 bar
|
|
200 bar
|
|
SELECT t1.a FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a+10 };
|
|
a
|
|
1
|
|
1
|
|
100
|
|
100
|
|
200
|
|
200
|
|
SELECT t1.* FROM t2 INNER JOIN t1;
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
1 z 2
|
|
1 z 5
|
|
1 z 5
|
|
1 z 5
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 4
|
|
100 foobar 4
|
|
100 foobar 4
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 6
|
|
200 bar 6
|
|
200 bar 6
|
|
SELECT t1_2.* FROM t1 t1_1 CROSS JOIN t1 t1_2 ON t1_1.b = t1_2.b;
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
1 z 5
|
|
1 z 5
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 4
|
|
100 foobar 4
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 6
|
|
200 bar 6
|
|
SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 WHERE t1.b > t2.b;
|
|
a b
|
|
1 bar
|
|
1 bar
|
|
1 foobar
|
|
1 foobar
|
|
100 bar
|
|
100 bar
|
|
SELECT t1.a, t2.b FROM t2 STRAIGHT_JOIN t1 ON t1.b > t2.b ORDER BY t1.a, t2.b;
|
|
a b
|
|
1 bar
|
|
1 bar
|
|
1 foobar
|
|
1 foobar
|
|
100 bar
|
|
100 bar
|
|
SELECT t2.* FROM t1 LEFT JOIN t2 USING (a) ORDER BY t2.a, t2.b LIMIT 1;
|
|
a b pk
|
|
1 z 2
|
|
SELECT t2.* FROM t2 LEFT OUTER JOIN t1 ON t1.a = t2.a WHERE t1.a IS NOT NULL;
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
100 foobar 1
|
|
100 foobar 1
|
|
200 bar 3
|
|
200 bar 3
|
|
SELECT SUM(t2.a) FROM t1 RIGHT JOIN t2 ON t2.b = t1.b;
|
|
SUM(t2.a)
|
|
602
|
|
SELECT MIN(t2.a) FROM t1 RIGHT OUTER JOIN t2 USING (b,a);
|
|
MIN(t2.a)
|
|
1
|
|
SELECT alias.b FROM t1 NATURAL JOIN ( SELECT a,b FROM t1 ) alias WHERE b > '';
|
|
b
|
|
bar
|
|
bar
|
|
bar
|
|
bar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
z
|
|
z
|
|
z
|
|
z
|
|
SELECT t2.b FROM ( SELECT a,b FROM t1 ) alias NATURAL LEFT JOIN t2 WHERE b IS NOT NULL;
|
|
b
|
|
bar
|
|
bar
|
|
foobar
|
|
foobar
|
|
z
|
|
z
|
|
SELECT t1.*, t2.* FROM t1 NATURAL LEFT OUTER JOIN t2;
|
|
a b pk a b pk
|
|
1 z 2 1 z 2
|
|
1 z 5 NULL NULL NULL
|
|
100 foobar 1 100 foobar 1
|
|
100 foobar 4 NULL NULL NULL
|
|
200 bar 3 200 bar 3
|
|
200 bar 6 NULL NULL NULL
|
|
SELECT t2_2.* FROM t2 t2_1 NATURAL RIGHT JOIN t2 t2_2 WHERE t2_1.a IN ( SELECT a FROM t1 );
|
|
a b pk
|
|
1 z 2
|
|
100 foobar 1
|
|
200 bar 3
|
|
SELECT t1_2.b FROM t1 t1_1 NATURAL RIGHT OUTER JOIN t1 t1_2 INNER JOIN t2;
|
|
b
|
|
bar
|
|
bar
|
|
bar
|
|
bar
|
|
bar
|
|
bar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
foobar
|
|
z
|
|
z
|
|
z
|
|
z
|
|
z
|
|
z
|
|
SELECT ( SELECT MIN(a) FROM ( SELECT a,b FROM t1 ) alias1 ) AS min_a FROM t2;
|
|
min_a
|
|
1
|
|
1
|
|
1
|
|
SELECT a,b FROM t2 WHERE a = ( SELECT MIN(a) FROM t1 );
|
|
a b
|
|
1 z
|
|
SELECT a,b FROM t2 WHERE b LIKE ( SELECT b FROM t1 ORDER BY b LIMIT 1 );
|
|
a b
|
|
200 bar
|
|
SELECT t2.* FROM t1 t1_outer, t2 WHERE ( t1_outer.a, t2.b ) IN ( SELECT a, b FROM t2 WHERE a = t1_outer.a );
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
100 foobar 1
|
|
100 foobar 1
|
|
200 bar 3
|
|
200 bar 3
|
|
SELECT a,b FROM t2 WHERE b = ANY ( SELECT b FROM t1 WHERE a > 1 );
|
|
a b
|
|
100 foobar
|
|
200 bar
|
|
SELECT a,b FROM t2 WHERE b > ALL ( SELECT b FROM t1 WHERE b < 'foo' );
|
|
a b
|
|
1 z
|
|
100 foobar
|
|
SELECT a,b FROM t1 WHERE ROW(a, b) = ( SELECT a, b FROM t2 ORDER BY a, b LIMIT 1 );
|
|
a b
|
|
1 z
|
|
1 z
|
|
SELECT a,b FROM t1 WHERE EXISTS ( SELECT a,b FROM t2 WHERE t2.b > t1.b );
|
|
a b
|
|
100 foobar
|
|
100 foobar
|
|
200 bar
|
|
200 bar
|
|
SELECT t1.* FROM t1, t2 ORDER BY ( SELECT b FROM t1 WHERE a IS NULL ORDER BY b LIMIT 1 ) DESC;
|
|
a b pk
|
|
1 z 2
|
|
1 z 2
|
|
1 z 2
|
|
1 z 5
|
|
1 z 5
|
|
1 z 5
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 1
|
|
100 foobar 4
|
|
100 foobar 4
|
|
100 foobar 4
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 3
|
|
200 bar 6
|
|
200 bar 6
|
|
200 bar 6
|
|
SELECT a, b FROM t1 HAVING a IN ( SELECT a FROM t2 WHERE b = t1.b );
|
|
a b
|
|
1 z
|
|
1 z
|
|
100 foobar
|
|
100 foobar
|
|
200 bar
|
|
200 bar
|
|
SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION DISTINCT SELECT a,b FROM t1;
|
|
a b
|
|
1 z
|
|
100 foobar
|
|
200 bar
|
|
SELECT a,b FROM t1 UNION SELECT a,b FROM t2 UNION ALL SELECT a,b FROM t1;
|
|
a b
|
|
1 z
|
|
1 z
|
|
1 z
|
|
100 foobar
|
|
100 foobar
|
|
100 foobar
|
|
200 bar
|
|
200 bar
|
|
200 bar
|
|
DROP TABLE t1, t2;
|