Broad Network


Querying the MySQL Database

Implementing Database in MySQL Part 10

Foreword: In this part of the series, I start explaining how you can retrieve data from a MySQL database.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 10 of my series, Implementing Database in MySQL. In this part of the series, I start explaining how you can retrieve data from a MySQL database. Retrieving data means querying the database. You should have read the previous parts of the series before reaching here, as this is a continuation.

What to Retrieve
In this part of the series you will see how to retrieve all the data from a table. We shall take this as an opportunity to retrieve, (see) all the data in each of the table we have created. In the following parts, we shall see how to write queries to retrieve data of your choice from one table or from different tables.

The main SQL Statement
The main SQL statement to query a database is called the SELECT Statement. You retrieve all the data (in all columns and all rows) from a particular table with the following statement:

    SELECT * FROM table-name;

The asterisk (*) in the statement means everything. Replace table-name with the name of the table you are interested in.

Illustration
The rest of this tutorial feeds in data to the tables and then displays (retrieves) them. We shall feed in data to the tables that do not have foreign keys first before we feed in data to the table with foreign keys. This is because a foreign key must exist first as a primary key elsewhere before you can have it in a table, in order to respect referential integrity.

We shall begin by adding more rows to the Products table.

- 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;

- Type and execute the following sets of SQL Statements, (you may copy and paste); the complete set may take time to execute:

INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('TV Set', 'Entertainment', '425', 26, '25', '30');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('VCD', 'Entertainment', '330',  25, '20', '25');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Clothe Box', 'Household', '345', 40, '16', '21');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Perfume', 'Beauty', '475', 40, '2', '3');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Banana', 'Fruit', '500', 45, '5', '7');
INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pear', 'Fruit', '525', 40, '3', '4');

INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Great Lakes Food Shop', '2732 Baker Blvd', 'Eugene', 'OR', 'USA', '514 555-7555');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('John Steel', 'Sing Terrace', 'Walla', 'WA', 'USA', '509-555-6221');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Start and Shop', '87 Polk St.Suite 5', 'San Francisco', 'CA', 'USA', '415-655-5938');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('Lonesome Gold Restaurant', 'Chiaroscuro Rd.', 'Portland', 'OR', 'USA', '503-555-9573');
INSERT INTO Customers (Name, Address, City, State, Country, Phone) VALUES ('New World Delicatessen', 'Bering St.', 'Anchorage', 'AK', 'USA', '907-555-7584');

INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Specialty Biscuits, Ltd', '29 Kings Way', 'Manchester', 'M14', 'USA', '161-555-4448');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('New England Cannery', '2200 Paul Revere Blvd.', 'Boston', 'MA', 'USA', '617-555-3267');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Bigfoot Breweries', '3400 - 8th Avenue Suite 210', 'Bend', 'OR', 'USA', '503-555-9931');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('Grandma Kellys Homestead', '707 Oxford Rd.', 'Ann Arbor', 'MI', 'USA', '313-555-3349');
INSERT INTO Suppliers (Name, Address, City, State, Country, Phone) VALUES ('New Orleans Cajun Delights', 'P.O. Box 25934', 'New Orleans', 'LA', 'USA', '100-555-4822');

INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('John Smith', '2817 Milton Dr.', 'Boston', 'MA', 'USA', '505-555-5939', 1);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Mary Jones', '187 Suffolk Ln.', 'Boston', 'MA', 'USA', '208-555-8097', 1);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('John Jackson', 'P.O. Box 555', 'Boston', 'MA', 'USA', '307-555-6525', 1);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Roy M Gild', '89 Jefferson Way Suite 2', 'Boston', 'MA', 'USA', '503-555-3612', 2);
INSERT INTO Employee (Name, Address, City, State, Country, Phone, Manager) VALUES ('Grace Peterson', '55 Grizzly Peak Rd.', 'Boston', 'MA', 'USA', '406-555-8083', 2);

INSERT INTO Sales (EmployeeID, CustomerID) VALUES (2, 1);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (1, 3);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (5, 1);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (3, 4);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (1, 2);

INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (1, 1, 3, 0.75);
INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (1, 3, 2, 3);
INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (1, 5, 5, 1.25);
INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (2, 7, 10, 21);
INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (2, 9, 15, 3);

INSERT INTO Orders (EmployeeID, SupplierID) VALUES (2, 1);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (1, 3);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (5, 1);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (3, 4);
INSERT INTO Orders (EmployeeID, SupplierID) VALUES (1, 2);

INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 1,  0.5, 0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 3, 2, 0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (1, 5, 1, 0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (2, 7, 20, 0);
INSERT INTO OrderDetails (OrderID, ProductID, BoughtPrice, Discount) VALUES (2, 9, 2, 0);

You will now read all the data from each table.

- Type and execute each of the following statements in the mysql command prompt. All the data in each table should be displayed after you have typed its SQL SELECT statement. Some results may not be properly displayed (may be wrapped), but you should see all the result data.

SELECT * FROM Products;

SELECT * FROM Customers;

SELECT * FROM Suppliers;

SELECT * FROM Employee;

SELECT * FROM Sales;

SELECT * FROM SaleDetails;

SELECT * FROM Orders;

SELECT * FROM OrderDetails;

- Now, close the connection, and quit the tool with the following command sequence:

    close
    quit

Well, we have just known what the SELECT Statement is dealing with. We shall see more about it in the following parts of the series.

Chrys

Related Links

Implementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course

BACK NEXT

Comments

Become the Writer's Fan
Send the Writer a Message