1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
-- source include/not_embedded.inc
let $MYSQLD_DATADIR= `select @@datadir`;
let $PORT= `select @@port`;
--copy_file $MTR_SUITE_DIR/std_data/employee.dat $MYSQLD_DATADIR/test/employee.dat
CREATE TABLE employee (
serialno CHAR(5) NOT NULL,
name VARCHAR(12) NOT NULL FLAG=6,
sex TINYINT(1) NOT NULL,
title VARCHAR(15) NOT NULL FLAG=20,
manager CHAR(5) DEFAULT NULL,
department CHAR(4) NOT NULL FLAG=41,
secretary CHAR(5) DEFAULT NULL FLAG=46,
salary DOUBLE(8,2) NOT NULL FLAG=52
) ENGINE=connect TABLE_TYPE=fix FILE_NAME='employee.dat' ENDING=1;
SELECT * FROM employee;
--replace_result $PORT PORT
--eval CREATE TABLE occurs (name CHAR(12), sex CHAR(1), title CHAR(15), department CHAR(4), salary DOUBLE(8,2), id_of CHAR(12), id CHAR(5) NOT NULL) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=employee OPTION_LIST='OccurCol=ID,RankCol=ID_OF,Colist=serialno;manager;secretary,port=$PORT';
SELECT * FROM occurs;
DROP TABLE occurs;
DROP TABLE employee;
CREATE TABLE pets (
name VARCHAR(12) NOT NULL,
dog INT NOT NULL DEFAULT 0,
cat INT NOT NULL DEFAULT 0,
rabbit INT NOT NULL DEFAULT 0,
bird INT NOT NULL DEFAULT 0,
fish INT NOT NULL DEFAULT 0) ENGINE=MYISAM;
INSERT INTO pets(name,dog) VALUES('John',2);
INSERT INTO pets(name,cat) VALUES('Bill',1);
INSERT INTO pets(name,dog,cat) VALUES('Mary',1,1);
INSERT INTO pets(name,rabbit) VALUES('Lisbeth',2);
INSERT INTO pets(name,cat,bird) VALUES('Kevin',2,6);
INSERT INTO pets(name,dog,fish) VALUES('Donald',1,3);
SELECT * FROM pets;
--replace_result $PORT PORT
--eval CREATE TABLE xpet (name VARCHAR(12) NOT NULL, race CHAR(6) NOT NULL, number INT) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=pets OPTION_LIST='OccurCol=number,RankCol=race,Colist=dog;cat;rabbit;bird;fish,port=$PORT'
SELECT * FROM xpet;
SELECT name FROM xpet;
SELECT name FROM xpet WHERE race = 'cat' AND number = 0;
SELECT name, SUM(number) pets FROM xpet GROUP BY name;
ALTER TABLE xpet MODIFY number INT NOT NULL;
SELECT * FROM xpet;
SELECT * FROM xpet WHERE number > 1;
SELECT DISTINCT name FROM xpet WHERE number > 1;
SELECT name FROM xpet;
SELECT name, race FROM xpet;
SELECT name, count(*) FROM xpet GROUP BY name, LEAST(number,1);
SELECT name, number, count(*) FROM xpet GROUP BY name, number;
DROP TABLE xpet;
DROP TABLE pets;
--remove_file $MYSQLD_DATADIR/test/employee.dat
|
