MySQL Website - Lesson 04: Columns Maintenance

	Columns Maintenance

Introduction

We hafve already seen simple examples of creating a table. Here is one:

CREATE TABLE StaffMembers(EmployeeeNumber VARCHAR(10));

Column maintenance consists of reviewing or changing any of its aspects. This includes reviewing the structure of columns of a table, renaming a column, deleting a column, changing the data type or the nullity of a column, etc.

When making a change on a column, you are also said to alter the table. To support this operation, the SQL starts with the following formula:

ALTER TABLE table-name

One of the operations involved with modifying a column consists of changing its data type. When using this statement, the ALTER TABLE expression is required and it is followed by the name of the table.

Adding a New Column

The basic formula to add a new column to an existing table is:

ALTER TABLE table-name
ADD column-name properties

The column-name is required. In fact, on the right side of the ADD word, define the column by its name and followed by the options for columns. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("ALTER TABLE StaffMembers
              ADD Sex VARCHAR(20);");

$link->close();
?>

</body>
</html>

Changing the Data Type of a Column

To change the data type of a column, the formula to follow is:

ALTER TABLE table-name
MODIFY column-name new-data-type

Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("ALTER TABLE StaffMembers
              MODIFY Sex CHAR(1);");

$link->close();
?>

</body>
</html>

Renaming a Column

If you find out that the name of a column is not appropriate, you can change it. To change the name of a column, use the following formula:

ALTER TABLE table-name
CHANGE original-column-name new-column-name options

Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("ALTER TABLE StaffMembers
              CHANGE Sex Gender CHAR(1);");

$link->close();
?>

</body>
</html>

Deleting a Column

If you have an undesired column that you don't need anymore, you can remove it. To delete a column, use the following formula:

ALTER TABLE table-name
DROP COLUMN column-name

Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("ALTER TABLE StaffMembers
              DROP COLUMN Gender;");

$link->close();
?>

</body>
</html>
DROP COLUMN Sex;