SELECT and CREATE tables 
Previous Top Next

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