Performing Operations on Table Data

Insert:

When a table is created for the very first time it is an empty structure. The next thing to do is to load this empty structure with business data to be manipulated later.

Loading table data can be done using the SQL INSERT statement which:

Creates a new empty row in the database table
Loads the values embedded within the SQL Insert statement into the empty row

The following is the syntax for inserting the data or value into the table.
Syntax:
INSERTINTO <TableName> (<ColumnName1>, <ColumnName2>)
VALUES (<Expression1>, <Expression2>);

Example:
INSERT INTO ClientMaster (CustomerID, Name, CompanyName, Address, Telephone) VALUES ('cl1', 'Amit Sen', 'null', 'A-1,Mahavir Society, Kailash road, Mulund(E)', 23234567);

Output
Query OK, 1 row affected (0.03 sec) as shown in Diagram 1.

Diagram 1: Inserting single row of data from the command prompt

Character expressions used within the INSERT INTO statement must be enclosed in single quotes (').

Table columns and values are related in any INSERT INTO SQL statement. The first value specified is inserted into the first column, the second value is inserted in the second column and so on.

Hence, in an INSERT INTO SQL sentence if there are exactly the same numbers of values as there are columns and the values are sequenced exactly in accordance with the table columns, there is no need to specify column names.

However, if there are lesser values specified in the INSERT INTO SQL statement than there are columns in the table, then it is mandatory to indicate both the table column name and its corresponding value in the INSERT INTO SQL sentence.

In the absence of a specific map between table column names and values the MySQL engine will insert data linearly, as it always does. Thus it’s quite possible that the wrong data will be inserted into the wrong column and the table data would lose its integrity.

MySQL also allows inserting multiple rows into a table using a single INSERT INTO statement.

The following is the syntax for inserting multiple data or value into the table.
Syntax:
INSERT INTO <TableName> (<ColumnName1>, <ColumnName'N'>,...)
VALUES (...), (...), (...);

Example:
INSERT INTO ClientMaster (CustomerID, Name, CompanyName, Address, Telephone) VALUES ('cl2', 'null', 'AMCS', 'jolapur, Marol', 23456743), ('cl3', 'Alfred Dsouza', 'null', 'kurla, Andheri(e)', 24345634);

Output
Query OK, 2 rows affected (0.01 sec) as shown in Diagram 2.
Records: 2 Duplicates: 0 Warnings: 0

Diagram 2: Inserting multiple row of data from the command prompt

Update:

The UPDATEcommand is used to change or modify data values in a table.

The UPDATE verb in SQL is used to either update:

All the rows in a table

A specific set of rows in a table

Updating All Rows

The UPDATE statement updates all the columns specified, in all the rows that exist in the table. The SET clause indicates which column data should be modified and the new values that they should hold. The WHERE clause, if given, defines particular rows that should be updated. Otherwise, all table rows are updated.

The following is the syntax for updating all rows of data in the table.
Syntax:
Update <TableName>
SET <ColumnName1>=<Expression1>, <ColumnName2>=<Expression2>;

Example:
UPDATE ClientMaster SET CompanyName = ‘AFC Tech’;

Output:
Query OK, 3 rows affected (0.03 sec) as shown in Diagram 3.
Rows matched: 3 Changed: 3 Warnings: 0

Diagram 3: Updating multiple row of data from the command prompt

Update Specific Row(s)

The following is the syntax for updating specific row of data in the table.
Syntax:
Update <TableName>
SET <ColumnName1>=<Expression1>, <ColumnName2>=<Expression2>
WHERE <Condition>;

Example:
UPDATE ClientMaster SET CompanyName =‘SEW Tech Ltd’
WHERE Name=‘Alfred Dsouza’;

Output:
Query OK, 1 row affected (0.05 sec) as shown in Diagram 4
Rows matched: 1 Changed: 1 Warnings: 0

Diagram 4: Updating single row of data from the command prompt

Delete:

The DELETEcommand deletes rows from the table that satisfies the condition specified in its WHERE clause and returns the number of records deleted.
If a DELETE statement without a WHERE clause is fired then all rows in the table are deleted.

The verb DELETE in SQL is used to remove either: