Our employee_data table now contains enough data for us to work with. Let us see how we can extract (query) it. Querying involves the use of the MySQL SELECT command.
Data is extracted from the table using the SELECT SQL command. Here is the format of a SELECT statement:
SELECT column_names from table_name [WHERE …conditions];
The conditions part of the statement is optional (we'll go through this later). Basically, you require to know the column names and the table name from which to extract the data.
For example, in order to extract the first and last names of all employees, issue the following command.
SELECT f_name, l_name from employee_data;
The statement tells MySQL to list all the rows from columns f_name and l_name.
mysql> SELECT f_name, l_name from employee_data;
+———+————+
| f_name | l_name |
+———+————+
| Manish | Sharma |
| John | Hagan |
| Ganesh | Pillai |
| Anamika | Pandit |
| Mary | Anchor |
| Fred | Kruger |
| John | MacFarland |
| Edward | Sakamuro |
| Alok | Nanda |
| Hassan | Rajabi |
| Paul | Simon |
| Arthur | Hoopla |
| Kim | Hunter |
| Roger | Lewis |
| Danny | Gibson |
| Mike | Harper |
| Monica | Sehgal |
| Hal | Simlai |
| Joseph | Irvine |
| Shahida | Ali |
| Peter | Champion |
+———+————+
21 rows in set (0.00 sec)
On close examination, you'll find that the display is in the order in which the data was inserted. Furthermore, the last line indicates the number of rows our table has (21).
To display the entire table, we can either enter all the column names or use a simpler form of the SELECT statement.
SELECT * from employee_data;
Some of you might recognize the * in the above statement as the wildcard. Though we don't use that term for the character here, it serves a very similar function. The * means 'ALL columns'. Thus, the above statement lists all the rows of all columns.
Querying tables with MySQL Select – Another example
SELECT f_name, l_name, age from employee_data;
Selecting f_name, l_name and age columns would display something like:
mysql> SELECT f_name, l_name, age from employee_data;
+———+————+——+
| f_name | l_name | age |
+———+————+——+
| Manish | Sharma | 28 |
| John | Hagan | 32 |
| Ganesh | Pillai | 32 |
| Anamika | Pandit | 27 |
| Mary | Anchor | 26 |
| Fred | Kruger | 31 |
| John | MacFarland | 34 |
| Edward | Sakamuro | 25 |
| Alok | Nanda | 32 |
| Hassan | Rajabi | 33 |
| Paul | Simon | 43 |
| Arthur | Hoopla | 32 |
| Kim | Hunter | 32 |
| Roger | Lewis | 35 |
| Danny | Gibson | 34 |
| Mike | Harper | 36 |
| Monica | Sehgal | 30 |
| Hal | Simlai | 27 |
| Joseph | Irvine | 27 |
| Shahida | Ali | 32 |
| Peter | Champion | 36 |
+———+————+——+
21 rows in set (0.00 sec)
Assignments:
1.Write the complete SQL statement for creating a new database called addressbook
2.Which statement is used to list the information about a table? How do you use this statement?
3.How would you list all the databases available on the system?
4.Write the statement for inserting the following data in employee_data table
First name: Rudolf
Last name: Reindeer
Title: Business Analyst
Age: 34
Years of service: 2
Salary: 95000
Perks: 17000
email: rudolf@bugnet.com
5.Give two forms of the SELECT statement that will list all the data in employee_data table.
6.What will select f_name, email from employee_data; display?
7.Write the statement for listing data from salary, perks and yos columns of employee_data table.
8.How can you find the number of rows in a table using the SELECT statement?
9.What will select salary, l_name from employee_data; display?
Possible Answers:
1.create database addressbook;
OR
CREATE DATABASE addressbook;
Note: SQL statements are case-insensitive, though table names and database names might be sensitive to case depending on the platform.
2.The DESCRIBE statement as in
DESCRIBE employee_data;
3.SHOW DATABASES; (at the mysql prompt)
4.INSERT INTO employee_data
(f_name, l_name, title, age, yos, salary, perks, email)
values
("Rudolf", "Reindeer", "Business Analyst", 34, 2, 95000, 17000, "rudolf@bignet.com");
Note: The text strings are enclosed in quotes.
5.SELECT emp_id, f_name, l_name, title, age, yos, salary, perks, email from employee_data;
OR
SELECT * from employee_data;
The second form is better and easier to type.
6.This displays the data from columns f_name and email.
mysql> select f_name, email from employee_data;
+———+———————–+
| f_name | email |
+———+———————–+
| Manish | manish@bignet.com |
| John | john_hagan@bignet.com |
| Ganesh | g_pillai@bignet.com |
| Anamika | ana@bignet.com |
| Mary | mary@bignet.com |
| Fred | fk@bignet.com |
| John | john@bignet.com |
| Edward | eddie@bignet.com |
| Alok | alok@bignet.com |
| Hassan | hasan@bignet.com |
| Paul | ps@bignet.com |
| Arthur | arthur@bignet.com |
| Kim | kim@bignet.com |
| Roger | roger@bignet.com |
| Danny | danny@bignet.com |
| Mike | mike@bignet.com |
| Monica | monica@bignet.com |
| Hal | hal@bignet.com |
| Joseph | joseph@bignet.com |
| Shahida | shahida@bignet.com |
| Peter | peter@bignet.com |
+———+———————–+
21 rows in set (0.00 sec)
7.SELECT salary, perks, yos from employee_data;
mysql> SELECT salary, perks, yos from employee_data;
+——–+——-+——+
| salary | perks | yos |
+——–+——-+——+
| 200000 | 50000 | 4 |
| 120000 | 25000 | 4 |
| 110000 | 20000 | 4 |
| 90000 | 15000 | 3 |
| 85000 | 15000 | 2 |
| 75000 | 15000 | 3 |
| 80000 | 16000 | 4 |
| 75000 | 14000 | 2 |
| 70000 | 10000 | 3 |
| 90000 | 15000 | 3 |
| 85000 | 12000 | 2 |
| 75000 | 15000 | 1 |
| 110000 | 20000 | 2 |
| 100000 | 13000 | 2 |
| 90000 | 12000 | 1 |
| 120000 | 28000 | 2 |
| 90000 | 25000 | 3 |
| 70000 | 18000 | 2 |
| 72000 | 18000 | 2 |
| 70000 | 9000 | 3 |
| 120000 | 25000 | 4 |
+——–+——-+——+
21 rows in set (0.00 sec)
8.The last line of any SELECT statement gives the number of resultant rows. Thus, if you list all the data in any column (or all columns), the last line will indicate the number of rows in the table.
9.mysql> select salary, l_name from employee_data;
+——–+————+
| salary | l_name |
+——–+————+
| 200000 | Sharma |
| 120000 | Hagan |
| 110000 | Pillai |
| 90000 | Pandit |
| 85000 | Anchor |
| 75000 | Kruger |
| 80000 | MacFarland |
| 75000 | Sakamuro |
| 70000 | Nanda |
| 90000 | Rajabi |
| 85000 | Simon |
| 75000 | Hoopla |
| 110000 | Hunter |
| 100000 | Lewis |
| 90000 | Gibson |
| 120000 | Harper |
| 90000 | Sehgal |
| 70000 | Simlai |
| 72000 | Irvine |
| 70000 | Ali |
| 120000 | Champion |
+——–+————+
21 rows in set (0.00 sec)