MySQL contd: Tables

So...here we are. I had said "tomorrow", but 3 days have passed since my last mysql post.

Databases basically consist of tables. Tables are by themselves just rows and columns but can be interpreted as whatever we want. Generally, columns specify various attributes or qualities of a particular class and rows are just records (or instances) of that class, each one having specific values for each of the attributes including NULL.

We will start again with some simple commands and move towards more involved ones. But today, mostly there are simple commands. Try them yourselves to know what they do...

1.mysql> SHOW TABLES;
2.mysql> DESCRIBE table_name;
3.mysql> CREATE TABLE table_name (id1 ID1_TYPE(SIZE), id2 ID2_TYPE(SIZE), ... );
example:
    mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
        -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

4.mysql> SHOW WARNINGS;
shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. Statements that do not use tables and do not generate messages have no effect on the message list.

5.mysql> ALTER TABLE tbl_name alter_specs

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL incorporates the alteration into the copy, then deletes the original table and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

example:
    mysql> ALTER TABLE pet RENAME TO petu;
    mysql> ALTER TABLE pet ADD (new_col_1 INT, new_col_2 CHAR) [FIRST|After col_name];

Using the FIRST clause with the ADD statement puts the newly added column at the beginning of the table. Similarly using the AFTER clause along with col_name as an argument, puts the newly added column after the column named col_name.

There is a whole lot of stuff on how to use the ONLINE keyword in this context i.e. adding columns online and its limitations. Frankly speaking, I didn't understand much of it. It is basically about how to use the alter table command without copying the whole table and thereby save time and energy. Read more about it here.

6.You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement.
example:
    mysql> ALTER TABLE tbl_name DROP COLUMN some_col, DROP COLUMN some_other_col;

7.CHANGE clause can be used to rename the table and/or changing the table_type
mysql> alter table tbl_name CHANGE old_col_name new_col_name col_definition
example:
    mysql> ALTER TABLE pet CHANGE sex gender CHAR(1);
    mysql> ALTER TABLE pet CHANGE sex sex INT;
    mysql> ALTER TABLE pet CHANGE sex gender INT;       

8.MODIFY clause can also be used to change the type of the column without altering its name.
 example:
    mysql> ALTER TABLE t1 MODIFY col1 BIGINT;


Sorry guys, not much today !! Next time, I'll write about how to insert data into tables, modify that data, etc.