Experiment No :9 Date: / / 2023

Title: Managing MYSQL databases and tables

Aim: To study the implementation of MYSQL databases and tables

Theory:

Creating Databases

Before doing anything else with the data, you need to create a database. A database is a container of
data. It stores contacts, vendors, customers or any kind of data that you can think of.

To create a database in MySQL, you use the CREATE DATABASE statement as follows

Removing Databases

Removing database means deleting all the tables contained in the database and the database itself
permanently. Therefore, it is very important to execute this query with extra cautions.

To delete a database, you use the DROP DATABASE statement as follows

MySQL CREATE TABLE syntax

The CREATE TABLE statement allows you to create a new table in a database.
The following illustrates the basic syntax of the CREATE TABLE statement:

First, you specify the name of the table that you want to create after the CREATE TABLE keywords. The
table name must be unique within a database. The IF NOT EXISTS is optional. It allows you to check if the
table that you create already exists in the database. If this is the case, MySQL will ignore the whole
statement and will not create any new table.
78
  Thecolumn_name specifies the name of the column. Each column has a specific data type and
optional size e.g.,VARCHAR(255)
 The NOT NULL constraint ensures that the column will not contain NULL. Besides the NOT
NULL constraint, a column may have additional constraint such as CHECK, and UNIQUE.
 The DEFAULT specifies a default value for the column.
 The AUTO_INCREMENT indicates that the value of the column is incremented by one
automatically whenever a new row is inserted into the table. Each table has a maximum
one AUTO_INCREMENT column.
After the column list, you can define table constraints such as UNIQUE, CHECK, PRIMARY
KEY and FOREIGN KEY.

For example, if you want to set a column or a group of columns as the primary key, you use the
following syntax:

MySQL CREATE TABLE statement examples

Let’s take some examples of creating new tables.

1) MySQL CREATE TABLE simple example

The following statement creates a new table named tasks

The tasks table has the following columns:

79
  The task_id is an auto-increment column. If you use the INSERT statement to insert a new row
into the table without specifying a value for the task_id column, MySQL will automatically
generate a sequential integer for the task_id starting from 1.
 The title column is a variable character string column whose maximum length is 255. It means
that you cannot insert a string whose length is greater than 255 into this column. The NOT
NULL constraint indicates that the column does not accept NULL. In other words, you have to
provide a non-NULL value when you insert or update this column.
 The start_date and due_date are DATE columns. Because these columns do not have the NOT
NULL constraint, they can store NULL. The start_date column has a default value of the current
date. In other words, if you don’t provide a value for the start_date column when you insert a
new row, the start_date column will take the current date of the database server.
 The status and priority are the TINYINT columns which do not allow NULL.
 The description column is a TEXT column that accepts NULL.
 The created_at is a TIMESTAMP column that accepts the current time as the default value.
The task_id is the primary key column of the tasks table. It means that the values in the task_id column
will uniquely identify rows in the table.

2) MySQL CREATE TABLE with a foreign key primary key example

Suppose each task has a checklist or to-do list. To store checklists of tasks, you can create a new table
named checklists as follows:

The table checklists has a primary key that consists of two columns. Therefore, we used a table
constraint to define the primary key:

80
In addition, the task_id is the foreign key column that references to the task_id column of the
table tasks, we used a foreign key constraint to establish this relationship

This picture illustrates the checklists table and its relationship with the tasks table

MySQL ALTER TABLE

Setting up a sample
table

Let’s create a table named vehicles for the demonstration:

MySQL ALTER TABLE – Add columns to a table

The ALTER TABLE ADD statement allows you to add one or more columns to a table.
1) Add a column to a table

To add a column to a table, you use the ALTER TABLE ADD syntax:

81
In this syntax:

 table_name – specify the name of the table that you want to add a new column or columns after
the ALTER TABLE keywords.
 new_column_name – specify the name of the new column.
 column_definition– specify the datatype, maximum size, and column constraint of the new
column
 FIRST | AFTER column_name specify the position of the new column in the table. You can add a
column after an existing column (ATER column_name) or as the first column (FIRST). If you omit
this clause, the column is appended at the end of the column list of the table.
The following example uses the ALTER TABLE ADD statement to add a column at the end of
the vehicles table:

This statement shows the column list of the vehicles table

2) Add multiple columns to a table

To add multiple columns to a table, you use the following form of the ALTER TALE ADD statement:

82
For example, this statement adds two columns color and note to the vehicles table

This statement shows the new structure of the vehicles table:

MySQL ALTER TABLE – Modify columns

1) Modify a column

Here is the basic syntax for modifying a column in a table:

Suppose that you want to change the note column a NOT NULL column with a maximum of 100
characters.

83
2) Modify multiple columns

The following statement allows you to modify multiple columns

MySQL ALTER TABLE – Rename a column in a table

To rename a column, you use the following statement:

84
MySQL ALTER TABLE – Drop a column
To drop a column in a table, you use the ALTER TABLE DROP COLUMN statement:

MySQL ALTER TABLE – Rename table

To rename a table, you use the ALTER TABLE RENAME TO statement

Conclusion: Studied & implemented MYSQL databases and tables.

85