Video Tutorial 3 SQL. Tables (create, alter, drop table) with mysql workbench

CREATE TABLE STATEMENT

The CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

SQL CREATE TABLE Example:

Now we want to create a table called "publisher", in the schema "eli", that contains four columns: idpublisher, name, address, and phone.

With the MySQL Workbench we write the following CREATE TABLE statement:

 

The idpublisher column is of type int and will hold an integer. This is the Primary Key; the unique identifier. The "name, address, and phone" columns are of type varchar and will hold characters, and the maximum length for these fields is 45 characters.

ALTER TABLE STATEMENT

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name

In our example, we delete the "address" column with the following statement;

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

In our example, we are going to modify the table "books" we had from the last tutorials and we are going to add a new column "idpublisher" so we know who the publisher of each book is, with the following statement;

We can do the same in the Command-line interface;

First with the DROP statement;

Example 1: In the table "publisher" we delete the column "address";

Example 2; In the table "books" we delete the column "idpublisher":

Then with the ADD statement;

We add the column "idpublisher" in the table "books";

<< Previous

Next >>