Data Manipulation in MySQL

Implementing Database in MySQL – Part 9

Division 4

Forward: In this part of the series, we see how to manipulate data in a MySQL. Remember we are dealing with MySQL 5.1

By: Chrysanthus Date Published: 6 Aug 2012

Introduction

This is part 9 of my series, Implementing Database in MySQL. I assume you have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to manipulate data in a MySQL. Remember we are dealing with MySQL 5.1

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Data Manipulation
Data manipulation in database means to insert data rows into a table, update (modify the data) the rows and to delete the rows.

Inserting a Row
To insert a row of data, you have to be connected to the database first, and you need to have been given the permission to do that. In simple terms, the syntax is:

INSERT INTO table-name (column-name, column-name, column-name, …) VALUES (value, value, …)

A string value should be in quotes (single quotes). We shall put in values into the Products table of the wholesale database.

- Start and connect to the server and choose the wholesale database with the following commands:

cd c:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql" -u root -p
Enter password: sql
USE wholesale;

- Read and type the following commands at the mysql prompt; avoid making a mistake (maybe you can copy and paste) pressing the Enter key just after the semicolon of each SQL statement;

INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pen', 'Office', '300', 25, '0.5', '0.75');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Books', 'Office', '500', 30, '3', '4');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Bowl', 'Household', '400', 35, '2', '3');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Spoon', 'Household', '350', 40, '.1', '.2');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Plate', 'Household', '450', 45, '1', '1.25');

Any SQL statement must end with a semicolon. You should have a positive feedback for any row inserted. Note that the ProductID column is autonumber and so it is not in the INSERT statements. The money is in dollars, but the dollar sign is not type. Amount given is for unit price.

You must be given the permission to update a row in a table before you can do so. You could insert the above rows since you are the DBA. You must also be given the permission to insert or delete a row in a table. As DBA that you are, you will be able to do these as shown below.

Updating a Table
To update a table means to modify the rows. We shall modify just one row in the Products table. We shall change the string, “Office” in the Category column to “School” in the second row of the table, where the ProductID is 2.

A simplified syntax for the UPDATE SQL Statement is:

    UPDATE table-name SET column-name = value WHERE search-condition;

The words in uppercase are reserved words. We want to set Category (column) to “School” where ProductID = 2.

- So, read, type and execute (press Enter) the following Update SQL statement:

    UPDATE Products SET Category = 'School' WHERE ProductID = 2;

You should have a positive feedback. In the statement, UPDATE, SET and WHERE are reserved words.

Deleting Rows
Let us delete the fourth row of the Products table, where the ProductID = 4. A DELETE simplified SQL syntax is:

DELETE FROM table-name WHERE search-condition;

DELETE means delete at least one row. We want to delete from the table, Products, where ProductID = 4.

- So, read, type and execute the following SQL statement:

DELETE FROM Products WHERE ProductID = 4;

You should have a positive feedback. Note: when an autonumber such as 4 above is deleted, it is generally not replaced the next time you add (insert) rows into the table. Assume that you insert a new row to the above table using the INSERT statement as above, the new autonumber will be 6 and not 4.

- Now, drop the connection, stop the database, and stop the server with the following command:

QUIT

That is it for this part of the series. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Data Manipulation in MySQL

Implementing Database in MySQL – Part 9

Division 4

Introduction

Related Links

Comments