Software Developer's Blog

Introduction to the ALTER, INSERT INTO, UPDATE & DELETE Statements:

Today, we will try to see the:

ALTER, INSERT, UPDATE and DELETE SQL keywords and we will try to illustrate their use with example.

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

If you want to change/modify the data-type of a column of an existing table, you can use the ALTER key word as follows:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Now let us see the above syntax in use with an example. Assume you just created a table called ‘tbl_student’ using the following CREATE TABLE statement…

CREATE TABLE tbl_student(

            id int auto_increment,

            name varchar(50) not null,

            id_number varchar(20) not null,

            city varchar(30) not null,

            primary key(id)

);

Then you realize that you forgot to add one column called ‘CGPA’ to the table. So to add a new column to the existing table ‘tbl_student’ you can use:

ALTER TABLE tbl_student
ADD cgpa int;

When you execute this statement, the table will have a new column ‘cgpa’ appended to it. But then you again saw one error. A student CGPA is a floating number. E.g student can have 3.45 as a cgpa. But the data type of cgpa in tbl_student is int. So to change the ‘int’ to a ‘float’ data type, you need to do the following:

ALTER TABLE tbl_student
ALTER COLUMN cgpa float

If you want to change and decide that you don’t want to store the cgpa column in tbl_student, you need to only drop/delete the ‘cgpa’ column with out affecting the rest of the columns in the table. To do this, you need to use the DROP key word with ALTER. See the example given below:

ALTER TABLE tbl_student
DROP COLUMN cgpa

Executing the above statement will eliminate the ‘cgpa’ column and leave the rest of the table intact.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This key word is used to add/insert a new record to the table specified in the statement. The INSERT INTO SQL statement has two forms (syntax). These are given below:

Version One: (Here you don’t have to specify the order of the columns of the table. You just give the name of the table where the new record is supposed to be added followed by the actual data values to be inserted.)

INSERT INTO table_name
VALUES (value1,value2,value3,...);

Version Two: (In the second version, you need to specify the order of the columns followed by the actual data values you want to add to the table.)

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Let us assume the example table we have created in the ALTER TABLE section. We will use the tbl_student schema (table structure) to insert data to it. In the beginning, the table contains no data, hence the table is empty.

First we will try to use the version one approach, and next we will use the version two approaches to insert new record to the table.

Version One:

INSERT INTO tbl_student

VALUES(0,’Alemu Teshome’,’STD/1234/02’,’Addis Ababa’);

Version Two:

INSERT INTO tbl_student (Name, Id_Number, City)

VALUES(‘Aster Degu’, ‘STD/2345/02’,’Jimma’);

As you can see from the above two examples, we are adding two new records to tbl_student but using two different versions of the INSERT INTO statement.

The first version always assumes to insert data to all columns of the table. Hence you need to pass a value to all columns of the table (full record value). You might have seen we have used 0 as the id value for the table. This column is defined as auto increment, so the value you pass will not be saved to the table. Rather, an automatically generated PK will be saved. All you need to do in such cases is to use a data value that has the same data type as the auto incremented value. In this case we are using int data type.

The second version of the INSERT INTO statement is used to specifically determine the columns you want to add data to. If you see the example again, we have left the id (auto incremented) value out and we have started from the ‘Name’ of the student table. Hence, data insertion will begin from the specified column name.

Therefore, you need to understand when to use the two different versions of the INSERT INTO statement. (N.B: You need to check if you have specified the columns as NOT NULL when creating them. In such cases, executing such an insert statement might generate an error.)

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This statement is used to manipulate the content of a table (data only). You should remember that UPDATE is only used to modify an existing record. If there is no record, update statement will NOT insert a new record. The syntax of the update statement is given below:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

In the SET clause, you are expected to specify which column’s content you would like to modify in the WHERE clause, you are expected to specify the condition that will identify which records from the table to update. If you leave out the WHERE clause, all the records of the table will be updated regardless of any condition. Let us see an example here:

UPDATE tbl_student

SET Name = ‘Solomon Teka’

WHERE Id_Number = ‘STD/1234/02’

Here only a record with Id_Number = ‘STD/1234/02’ will be modified. That is, the name value will be replaced with the new data value ‘Solomon Teka’. Executing this statement will not have any effect to the rest of the records of tbl_student. But if you leave out the WHERE Id_Number = ‘STD/1234/02’ part, then all records of tbl_student will have same name. i.e, ‘Solomon Teka’.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. The DELETE statement is used to delete rows in a table. The syntax of the delete statement is given below:

DELETE FROM table_name
WHERE some_column=some_value;

Just like the UPDATE statement, if you leave the WHERE clause, all the records of the specified table will be deleted. Let us see an example where we try to remove the record with Id_Number ‘STD/2345/02’ from tbl_student.

DELETE FROM tbl_student

WHERE Id_Number = ‘STD/2345/02’

Executing the above statement will remove the record with a student Id_Number ‘STD/2345/02’. Only this record is expected to be deleted. If there is no student record with the given Id_Number, then the delete will not affect the other records of the table. But if you omit the WHERE condition, all the records of the table will be deleted. So you need to always be extra careful when dealing with DELETE statements.

Introduction to the CREATE SQL statement:

The CREATE statement is one of the DDL (Data Definition Language) in that it is used to define/create database objects like, database, table, user etc.

We will use the CREATE statement to do the following things:

•   To create a database

•   To create a table structure

•   To create a database user

Today, We will see the how the CREATE statement is used to achieve above points in MySQL.

The CREATE DATABASE Statement:

The CREATE DATABASE statement is used to create a database in a DBMS. As you we have discussed in Day 1, a DBMS can have a number of databases in it. So if you want to create a new database in your DBMS, you will have to use the CREATE DATABASE statement. The syntax is given as follows:

CREATE DATABASE dbname;

N.B: dbname is the name of your database. Remember that you need to end the statement with a semi-colon (;). This is important when you are writing statements in a MySQL console window.

Eg.

CREATE DATABASE db_school;

This statement will create a blank database (no tables in it) named db_school in your DBMS. Unless you select the newly created database and execute a CREATE TABLE statement, the database will contain no tables in it.

The CREATE TABLE Statement:

The CREATE TABLE Statement is used to create table in the selected database. This statement is also one of the DDL of SQL in that we use the CREATE TABLE statement to create a new database structure, in this case a TABLE.

A database table is a collection of rows/tuples. Each row represents a record/an entity we are persisting (saving) to the database. In turn, a row is a collection of fields/columns. Continuing from our first example, if you take the object/instance Student, you know that a student object should have name, id_number, sex, department etc.

Each attributes we have listed as name, id_number, sex etc. are known as fields/columns. When you actually merge this together, they will form a row/record. A record is one instance/object of the table Student in our example.

If you consider this example:

STUDENT

As you can see from the above table each column represents fields of the object student.

If you view horizontally (records/tuples) it represents a single student object.

It might be note worthy to remember this relationship:

Table = Class = File

Tuple/Record = Object

Column = Field/attribute

These points will be used when we do mapping later on in our journey.

The CREATE TABLE Syntax:

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

Example:

CRATE DATABASE db_school;

USE db_school;

CREATE TABLE tbl_student(

   Name varchar(50) not null,

   Id_Number varchar(20) not null,

   Sex varchar(10) not null,

   Department varchar(50) not null,

   PRIMARY KEY(Id_Number)

);

Assuming you created the database db_school, you need to select the specific database you wanted the new table to reside upon. Remember we have said a DBMS can contain many databases. For selecting which database to use, we use the ‘USE’ statement as listed in the example.

The above SQL code creates a table named ‘tbl_student’. The table has four columns with their data type and size specified. For eg. You can see that name is given only 50 characters. If you try to give a name value exceeding this size, the data value will be truncated. The ‘Not Null’ value in each column shows that empty value /null value for that filed/column is not allowed and will have to always contain a data value. Finally we have added the PRIMARY KEY information in the table.

It is not a must that a table shall have a primary key. But it is always a good idea to have a primary key defined for your tables because that is the only way you can create relationship b/n tables. Of course you can have flat table (a table with no relationship at all).

So how do you determine which column of a table should be a PK (Primary key)? The following are the criteria you should check:

The CREATE USER Statement:

Since we are using MySQL DBMS, we will see how to create and grant a privilege to a user. By default MySQL has a user called ‘root’. This user has all the privilege so you can consider this as a super user.

Using this account, we can create our own users with different level of privilege.

The syntax for creating a user is:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

N.B:

‘newuser’  is the username that you want it to be. It should be a valid variable name.

‘password’ is the password of the new user you are just creating.

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. 

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables in the DBMS.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

How To Grant Different User Permissions

Here is a short list of other common possible permissions

·       ALL PRIVILEGES- as we saw previously, this would allow    a MySQL user all access to a designated database (or if no database is selected, across the system)

To provide a specific user with a permission, you can use this syntax:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name and each time you update or change a permission be sure to use the Flush Privileges command to make the changes.
If you need to revoke permission, the structure is almost identical to granting it:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER ‘newuser@‘localhost’;