Creating, modifying and droping tables

1. 
   CREATING, MODIFYING AND DROPING TABLES
   

We will be using a database named ebooks which will be storing ebooks details. Currently the database is empty as seen in the picture below.

We used “Show databases;” command to view the list of databases in our RDBMS. Then we’ve runed a command “use ebooks;” to select the ebooks as a database we are going to use. Finally, we check to see the list of tables in the database by using a command “show tables;” which in our case is returning empty set which means we have no table in this database.

1. 
   Creating a table
   

In order to create a table in this database, we will run the following command.

CREATE TABLE `the_ebooks` (
`ebook_id` bigint(20) NOT NULL,
`title` varchar(255) NOT NULL,
`author` varchar(255) NOT NULL,
`ebook_category` bigint(20) NOT NULL,
`filename` varchar(255) NOT NULL,
`mask` varchar(255) NOT NULL,
`date_modified` datetime NOT NULL,
`date_added` datetime NOT NULL,
`status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We are using a CREATE TABLE as the clause used to created database table and it followed by table name, in our case the_books. There after we have parentheses we define attributes of the table (columns) with each column separated by commas. Each attribute has an attribute name, data type, length, null value. More characters can be added to an attribute.

After running this command, the following are the outcome

The query return Ok, 0 rows affected. This means that the query has been executed successfully however no table row has been affected. We have also run “show tables;” to see the tables in our database and it shows us the_ebooks we just created. Finally we have used “describe the_ebooks;” that will show us the structure of our table.

The table has now being created.

2. 
   Modifying a table
   

One of rules of tables in a database need to have a primary key. In our table heir is no primary key. Apart from modifying table name and attributes and its types, we can also modify primary key either adding or dropping the primary key. We use Alter to modify the table. In our example we will add a primary key. Ebooks_id will be made primary_key and we will also do some modifications and make it auto increment.

Adding a primary key we’ve used “ALTER TABLE `the_ebooks` ADD PRIMARY KEY (`ebook_id`);” which has returned 0 records stating that the table is empty, 0 duplicate which means we don’t have no redundancy on the key field we selected and 0 warning showing that everything is fine.

Adding Auto Increment we’ve run “ALTER TABLE `the_ebooks` MODIFY `ebook_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=444;” which has returned the same modification results as primary key. Now after describing the table again you will see in key we have “PRI” and in Extra we have “auto_increment”.

Now we have modified our table.

3. 
   Dropping the table.
   

Dropping the table we run DROP TABLE table name;. in this example we run “drop table the_ebooks;” the query run Ok and when we show tables now it return empty set.

2. 
   INSERTING, MODIFYING AND DELETING ROWS
   

1. 
   Inserting
   

We are recreating the dropped table which we will keep using in this example, the_ebooks. TO insert into a table we use the following syntax.
INSERT INTO the_ebooks(column1, column2, column3,….) VALUES(1, “data1”, “data2”, ….);

If your adding more than 1 row, the the syntax is as follows.

INSERT INTO the_ebooks(column1, column2, column3,….) VALUES(1, “data1”, “data2”, ….), (1, “data1”, “data2”, ….),……………………………………………………………………;

In our example we added 26 rows at once but here we will show the code that add two rows