SELECT and CREATE tables
It is sometimes useful to create new tables from subsets (queries) of an existing table (or
even by consolidating data from several tables). We will cover this subject briefly here:
The simplest approach is demonstrated in this query:
create table b109cluster4 select * from b109clustered where cluster=4 limit
9999999;
creates a new table, called b109cluster4, that includes only records of syllable type 4.
Note that if you do not use the limit keyword, mySQL will create
a table using some
default limit (usually 1000 records) so we advise that you always use limit.
Now, a slightly more sophisticated case is when you need to create a new table from
certain fields, combining data from a few different tables. For example, say that you have
a raw feature table that is a bit faulty (my_faulty_table) and you suspects that some of the
records in that table has Wiener entropy values which are positive (Wiener entropy must
be negative!). Now detecting those records is easy:
SELECT entropy FROM my_faulty_table where entropy>0;
Of course, you can now create a new table excluding the faulty records:
create table my_right_table SELECT * FROM my_faulty_table where entropy<0
limit 999999;
However, you also want to identify the wave file that those faulty data belongs to, so as
to figure out what happened. The problem is that the name of the wave file is not
included in the raw features table. In that table you only have the file_index field. This
field, however, is pointing at the appropriate wave file name in another linked table,
called file_table. This data structure saves much space since we have thousands of
records from each file in the raw features table. So we now need to join
information
across those related table, and mySQL makes it very easy. All you need to do is identify
the table where the field comes from by the syntax table_name.field_name.
For example, the query
SELECT entropy, file_table.file_name FROM my_faulty_table, file_table where
entropy>0 and my_faulty_table.file_index=file_table.file_index limit 99999;
returns the positive entropy values side by side with the file names that relate to those
values.
The simple examples above demonstrated how a new table that include only some of the
table records (row) and fields (columns) can be generated from an already existing table
using the Select command combined with other commands such as create table.
Created using Helpmatic Pro HTML