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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
--RANDOM SQL QUERIES THAT DO NOTHING INTERESTING
--Copyright (C) 2009 - Keith Pitt <keith@keithpitt.com>
--This program is free software: you can redistribute it and/or modify
--it under the terms of the GNU General Public License as published by
--the Free Software Foundation, either version 3 of the License, or
--(at your option) any later version.
--This program is distributed in the hope that it will be useful,
--but WITHOUT ANY WARRANTY; without even the implied warranty of
--MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
--GNU General Public License for more details.
--You should have received a copy of the GNU General Public License
--along with this program. If not, see <http://www.gnu.org/licenses/>.
-- Comment: Drop table
DROP TABLE IF EXISTS `general_lookups`;
-- Create table
CREATE TABLE `general_lookups` (
`name` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Drop table again
DROP TABLE IF EXISTS customer;
-- Create customers
CREATE TABLE customer (
first_name char(50),
last_name char(50),
address char(50),
city char(50),
country char(25),
birth_date date,
created_at timestamp, -- Differnt sort of date here
updated_at timestamp
)
-- Create business
CREATE TABLE business (
compant_name char(50),
address char(50) default 'Address Unknown', -- Oohh, defaults..
city char(50) default 'Adelaide',
country char(150) default 'Australia'
)
-- Some random table
DROP TABLE IF EXISTS customer_statuses;
CREATE TABLE `customer_statuses` (
-- Auto incrementing IDs
`id` smallint(6) unsigned NOT NULL auto_increment,
`customer_id` int(10) unsigned NOT NULL default '0',
`customer_client_code` varchar(15) default NULL,
`entry_date` date default NULL,
`status_id` smallint(6) unsigned default NULL,
`comments` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Try creating an index.
CREATE INDEX customer_status_status_id ON customer_statuses (status_id)
/* Now lets try and make a really big table */
DROP TABLE IF EXISTS `legacy_clients`;
CREATE TABLE `legacy_clients` (
`id` int(10) unsigned NOT NULL auto_increment,
`client_code` varchar(15) default NULL,
`first_name` varchar(20) NOT NULL default '',
`other_name` varchar(20) default NULL,
`surname` varchar(30) NOT NULL default '',
`address` varchar(50) default NULL,
`suburb` varchar(50) default NULL,
`postcode` varchar(10) default NULL,
`location_id` smallint(3) default NULL,
`home_phone` varchar(15) default NULL,
`work_phone` varchar(15) default NULL,
`fax` varchar(15) default NULL,
`mobile` varchar(15) default NULL,
`email` varchar(50) default NULL,
`date_of_birth` date default NULL,
`business_id` int(11) default NULL,
`comments` varchar(100) default NULL,
`state` char(3) default NULL,
`sex` char(1) default NULL,
`location_temp` varchar(50) default NULL,
`employer_temp` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
