Inserting and Deleting Rows in MySQL

Implementing Database in MySQL – Part 9

Foreword: In this part of the series, we see how to manipulate data in a MySQL database.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 9 of my series, Implementing Database in MySQL. In this part of the series, we see how to manipulate data in a MySQL database. You should have read the different parts of the series before reaching here, as this is a continuation.

Data Manipulation
Data manipulation means inserting a table row or deleting a table row or selecting table rows or updating (modifying data) table 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 or double 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:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
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 (DataBase Administrator). 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, close the connection and quit the tool with the following commands:

    close
    quit

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

Chrys

Broad Network

Related Articles

Inserting and Deleting Rows in MySQL

Implementing Database in MySQL – Part 9

Introduction

Related Links

Comments