Querying MySQL Database
Implementing Database in MySQL – Part 10
Division 4
Forward: In this part of the series, we start seeing how you can retrieve data from a MySQL database.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
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.
What to Retrieve
In this part of the series we shall 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:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -u root -p
Enter password: sql
USE wholesale;
- Type and execute the following sets of SQL Statements, (you may copy and paste); a 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);
- 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, drop the connection, stop the database server, and stop the server, with the following command:
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
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT