MySQL primer - Using a database

Source
Read before 

We've already created our employees database. Now let's start the mysql client program and select our database. Once at the mysql prompt, issue the command:

SELECT DATABASE();

The system responds with

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
|            |
+------------+
1 row in set (0.01 sec)

The above shows that no database has been selected. Actually, everytime we work with mysql client, we have to specify which database we plan to use. There are several ways of doing it.

Specifying the database name at the start; type the folowing at the system prompt:
mysql employees (under Windows)
mysql employees -u manish -p (under Linux)

Specifying the database with the USE statement at the mysql prompt:
mysql>USE employees;

Specifying the database with \u at the mysql prompt:
mysql>\u employees;

It's necessary to specify the database we plan to use, else MySQL will throw an error.

Creating tables

Once you've selected the employees database, issue the CREATE TABLE command at the mysql prompt.

CREATE TABLE employee_data
(
emp_id int unsigned not null auto_increment primary key,
f_name varchar(20),
l_name varchar(20),
title varchar(30),
age int,
yos int,
salary int,
perks int,
email varchar(60)
);

Note: When you press the enter key after typing the first line, the mysql prompt changes to a -> (hyphen and greater than symbols). This means that mysql understands that the command is not complete and prompts you for additional statements. 

Remember, each mysql command ends with a semi-colon and each column declaration is separated by a comma. Also, you can type the entire command on one line if you so want.
You screen should look similar to:

mysql> CREATE TABLE employee_data
    -> (
    -> emp_id int unsigned not null auto_increment primary key,
    -> f_name varchar(20),
    -> l_name varchar(20),
    -> title varchar(30),
    -> age int,
    -> yos int,
    -> salary int,
    -> perks int,
    -> email varchar(60)
    -> );
Query OK, 0 rows affected (0.01 sec)

Okay, we just made our first table.

Now that we've created our employee_data table, let's check its listing.
Type SHOW TABLES; at the mysql prompt. This should present you with the following display:

mysql> SHOW TABLES;
+---------------------+
| Tables in employees |
+---------------------+
| employee_data       |
+---------------------+
1 row in set (0.00 sec)

MySQL Table details - describe table command

MySQL provides up with a command that displays the column details of the tables.
Issue the following command at the mysql prompt:

DESCRIBE employee_data;

The display would be as follows:

mysql> DESCRIBE employee_data;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| emp_id | int(10) unsigned |      | PRI | 0       | auto_increment |
| f_name | varchar(20)      | YES  |     | NULL    |                |
| l_name | varchar(20)      | YES  |     | NULL    |                |
| title  | varchar(30)      | YES  |     | NULL    |                |
| age    | int(11)          | YES  |     | NULL    |                |
| yos    | int(11)          | YES  |     | NULL    |                |
| salary | int(11)          | YES  |     | NULL    |                |
| perks  | int(11)          | YES  |     | NULL    |                |
| email  | varchar(60)      | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

DESCRIBE lists all the column names along with their column types of the table.
Now let's see how we can insert data into our table.

 Inserting data into tables

The INSERT SQL statement impregnates our table with data. Here is a general form of INSERT.

INSERT into table_name (column1, column2....)
values (value1, value2...);

where table_name is the name of the table into which we want to insert data; column1, column2 etc. are column names and value1, value2 etc. are values for the respective columns. This is quite simple, isn't it?

The following statement inserts the first record in employee_data table.

INSERT INTO employee_data
(f_name, l_name, title, age, yos, salary, perks, email)
values
("Manish", "Sharma", "CEO", 28, 4, 200000, 
50000, "manish@bignet.com");

As with other MySQL statements, you can enter this command on one line or span it in multiple lines.
Some important points:

• The table name is employee_data
• The values for columns f_name, l_name, title and email are text strings and surrounded with quotes.
• Values for age, yos, salary and perks are numbers (intergers) and without quotes.
• You'll notice that we've inserted data in all columns except emp_id. This is because, we leave this job to MySQL, which will check the column for the largest value, increment it by one and insert the new value.

Once you type the above command correctly in the mysql client, it displays a success message.

mysql> INSERT INTO employee_data
    -> (f_name, l_name, title, age, yos, salary, perks, email)
    -> values
    -> ("Manish", "Sharma", "CEO", 28, 4, 200000,
    -> 50000, "manish@bignet.com");
Query OK, 1 row affected (0.00 sec)

Inserting additional records requires separate INSERT statements. In order to make life easy, I've packed all INSERT statements into a file. Click to download the file, employee.dat.
Once you download the file, open it in a text editor. You'll notice that it's a plain ASCII file with an INSERT statement on each line.
Inserting data into employee_data table with employee.dat file 

On Windows

1. Move the file to c:\mysql\bin.
2. Make sure MySQL is running.
3. Issue the following command
           mysql employees

On Linux
Migrate to the directory that contains the downloaded file.

1. Issue the following command
           mysql employees
2. Enter your password.

Our table contains 21 entries (20 from employee.dat file and one from the INSERT statement we issued at the beginning). You can view the table here. (This opens another browser window).

 Querying MySQL tables

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) 

Selecting data using conditions

In this section of the MySQL tutorial we'll look at the format of a SELECT statement we met in the last session in detail. We will learn how to use the select statement using the WHERE clause.

SELECT column_names from table_name [WHERE ...conditions];

Now, we know that the conditions are optional (we've seen several examples in the last session... and you would have encountered them in the assignments too).
The SELECT statement without conditions lists all the data in the specified columns. The strength of RDBMS lies in letting you retrieve data based on certain specified conditions.
In this session we'll look at the SQL Comparision Operators.
The = and != comparision operators for MySQL Select

SELECT f_name, l_name from employee_data where f_name = 'John';

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
+--------+------------+
2 rows in set (0.00 sec)

This displays the first and last names of all employees whose first names are John. Note that the word John in the condition is surrounded by single quotes. You can also use double quotes. The quotes are important since MySQL will throw an error if they are missing. Also, MySQL comparisions are case insensitive; which means "john", "John" or even "JoHn" would work!

SELECT f_name,l_name from employee_data where title="Programmer";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| Fred   | Kruger     |
| John   | MacFarland |
| Edward | Sakamuro   |
| Alok   | Nanda      |
+--------+------------+
4 rows in set (0.00 sec)

Selects the first and last names of all employees who are programmers.

SELECT f_name, l_name from employee_data where age = 32;
+---------+--------+
| f_name  | l_name |
+---------+--------+
| John    | Hagan  |
| Ganesh  | Pillai |
| Alok    | Nanda  |
| Arthur  | Hoopla |
| Kim     | Hunter |
| Shahida | Ali    |
+---------+--------+
6 rows in set (0.00 sec)

This lists the first and last names of all empoyees 32 years of age. Remember that the column type of age was int, hence it's not necessary to surround 32 with quotes. This is a subtle difference between text and integer column types.
The != means 'not equal to' and is the opposite of the equality operator.
The greater than and lesser than operators
Okay, let's retrieve the first names of all employees who are older than 32.

SELECT f_name, l_name from employee_data where age > 32;
+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | MacFarland |
| Hassan | Rajabi     |
| Paul   | Simon      |
| Roger  | Lewis      |
| Danny  | Gibson     |
| Mike   | Harper     |
| Peter  | Champion   |
+--------+------------+
7 rows in set (0.00 sec)

How about employees who draw more than $120000 as salary...

SELECT f_name, l_name from employee_data where salary > 120000;
+--------+--------+
| f_name | l_name |
+--------+--------+
| Manish | Sharma |
+--------+--------+
1 row in set (0.00 sec)

Now, let's list all employees who have had less than 3 years of service in the company.

SELECT f_name, l_name from employee_data where yos < 3;
+--------+----------+
| f_name | l_name   |
+--------+----------+
| Mary   | Anchor   |
| Edward | Sakamuro |
| Paul   | Simon    |
| Arthur | Hoopla   |
| Kim    | Hunter   |
| Roger  | Lewis    |
| Danny  | Gibson   |
| Mike   | Harper   |
| Hal    | Simlai   |
| Joseph | Irvine   |
+--------+----------+
10 rows in set (0.00 sec)

The <= and >= operators for selecting MySQL data Used primarily with integer data, the less than equal (<=) and greater than equal (>=)operators provide additional functionality.

select f_name, l_name, age, salary
from employee_data where age >= 33;

+--------+------------+------+--------+
| f_name | l_name     | age  | salary |
+--------+------------+------+--------+
| John   | MacFarland |   34 |  80000 |
| Hassan | Rajabi     |   33 |  90000 |
| Paul   | Simon      |   43 |  85000 |
| Roger  | Lewis      |   35 | 100000 |
| Danny  | Gibson     |   34 |  90000 |
| Mike   | Harper     |   36 | 120000 |
| Peter  | Champion   |   36 | 120000 |
+--------+------------+------+--------+
7 rows in set (0.00 sec)

Selects the names, ages and salaries of employees who are more than or equal to 33 years of age..

select f_name, l_name from employee_data where yos <= 2;
+--------+----------+
| f_name | l_name   |
+--------+----------+
| Mary   | Anchor   |
| Edward | Sakamuro |
| Paul   | Simon    |
| Arthur | Hoopla   |
| Kim    | Hunter   |
| Roger  | Lewis    |
| Danny  | Gibson   |
| Mike   | Harper   |
| Hal    | Simlai   |
| Joseph | Irvine   |
+--------+----------+
10 rows in set (0.00 sec)
Displays employee names who have less than or equal to 2 years of service in the company.

Pattern Matching with text data We will now learn at how to match text patterns using the where clause and the LIKE

operator in this section of the MySQL reference guide.
The equal to(=) comparision operator helps is selecting strings that are identical. Thus, to list the names of employees whose first names are John, we can use the following SELECT statement.

select f_name, l_name from employee_data where f_name = "John";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
+--------+------------+
2 rows in set (0.00 sec)

What if we wanted to display employees whose first names begin with the alphabet J?
SQL allows for some pattern matching with string data. Here is how it works.

select f_name, l_name from employee_data where f_name LIKE "J%";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
| Joseph | Irvine     |
+--------+------------+
3 rows in set (0.00 sec)

You'll notice that we've replaced the Equal To sign with LIKE and we've used a percentage sign (%) in the condition.
The % sign functions as a wildcard (similar to the usage of * in DOS and Linux systems). It signifies any character. Thus, "J%" means all strings that begin with the alphabet J.
Similarly "%S" selects strings that end with S and "%H%", strings that contain the alphabet H.
Okay, let's list all the employees that have Senior in their titles.

select f_name, l_name, title from employee_data
where title like '%senior%';

+--------+--------+----------------------------+
| f_name | l_name | title                      |
+--------+--------+----------------------------+
| John   | Hagan  | Senior Programmer          |
| Ganesh | Pillai | Senior Programmer          |
| Kim    | Hunter | Senior Web Designer        |
| Mike   | Harper | Senior Marketing Executive |
+--------+--------+----------------------------+
4 rows in set (0.00 sec)

Listing all employees whose last names end with A is very simple

mysql> select f_name, l_name from employee_data
where l_name like '%a';

+--------+--------+
| f_name | l_name |
+--------+--------+
| Manish | Sharma |
| Alok   | Nanda  |
| Arthur | Hoopla |
+--------+--------+
3 rows in set (0.00 sec)

Logical Operators In this section of the SQL primer we look at how to select data based on certain conditions presented through MySQL logical operators.

SQL conditions can also contain Boolean (logical) operators. They are

• AND
• OR
• NOT

Their usage is quite simple. Here is a SELECT statement that lists the names of employees who draw more than $70000 but less than $90000.

SELECT f_name, l_name from employee_data
where salary > 70000 AND salary < 90000;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| Mary   | Anchor     |
| Fred   | Kruger     |
| John   | MacFarland |
| Edward | Sakamuro   |
| Paul   | Simon      |
| Arthur | Hoopla     |
| Joseph | Irvine     |
+--------+------------+
7 rows in set (0.00 sec)

Let's display the last names of employees whose last names start with the alphabet S or A.

SELECT l_name from employee_data where
l_name like 'S%' OR l_name like 'A%';

+----------+
| l_name   |
+----------+
| Sharma   |
| Anchor   |
| Sakamuro |
| Simon    |
| Sehgal   |
| Simlai   |
| Ali      |
+----------+
7 rows in set (0.00 sec)

Okay here is a more complex example... listing the names and ages of employees whose last names begin with S or P and who are less than 30 years of age.

SELECT f_name, l_name , age from employee_data
where (l_name like 'S%' OR l_name like 'A%') AND
age < 30;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Manish | Sharma   |   28 |
| Mary   | Anchor   |   26 |
| Edward | Sakamuro |   25 |
| Hal    | Simlai   |   27 |
+--------+----------+------+
4 rows in set (0.00 sec)

Note the usage of parenthesis in the statement above. The parenthesis are meant to separate the various logical conditions and remove any abiguity.
The NOT operator helps in listing all non programmers. (Programmers include Senior programmers, Multimedia Programmers and Programmers).

SELECT f_name, l_name, title from employee_data
where title NOT LIKE "%programmer%";

+---------+----------+----------------------------+
| f_name  | l_name   | title                      |
+---------+----------+----------------------------+
| Manish  | Sharma   | CEO                        |
| Anamika | Pandit   | Web Designer               |
| Mary    | Anchor   | Web Designer               |
| Kim     | Hunter   | Senior Web Designer        |
| Roger   | Lewis    | System Administrator       |
| Danny   | Gibson   | System Administrator       |
| Mike    | Harper   | Senior Marketing Executive |
| Monica  | Sehgal   | Marketing Executive        |
| Hal     | Simlai   | Marketing Executive        |
| Joseph  | Irvine   | Marketing Executive        |
| Shahida | Ali      | Customer Service Manager   |
| Peter   | Champion | Finance Manager            |
+---------+----------+----------------------------+
12 rows in set (0.00 sec)

A final example before we proceed to the assignments.
Displaying all employees with more than 3 years or service and more than 30 years of age.

select f_name, l_name from employee_data 
where yos > 3 AND age > 30;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| Ganesh | Pillai     |
| John   | MacFarland |
| Peter  | Champion   |
+--------+------------+
4 rows in set (0.00 sec) 

IN and BETWEEN This section of the tutorial MySQL looks at the In and BETWEEN operators.

To list employees who are Web Designers and System Administrators, we use a SELECT statement as

SELECT f_name, l_name, title from               
    -> employee_data where 
    -> title = 'Web Designer' OR
    -> title = 'System Administrator';

+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.01 sec)

SQL also provides an easier method with IN. Its usage is quite simple.

SELECT f_name, l_name, title from
    -> employee_data where title 
    -> IN ('Web Designer', 'System Administrator');

+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.00 sec)

Suffixing NOT to IN will display data that is NOT found IN the condition. The following lists employees who hold titles other than Programmer and Marketing Executive.

SELECT f_name, l_name, title from
    -> employee_data where title NOT IN
    -> ('Programmer', 'Marketing Executive');

+---------+----------+----------------------------+
| f_name  | l_name   | title                      |
+---------+----------+----------------------------+
| Manish  | Sharma   | CEO                        |
| John    | Hagan    | Senior Programmer          |
| Ganesh  | Pillai   | Senior Programmer          |
| Anamika | Pandit   | Web Designer               |
| Mary    | Anchor   | Web Designer               |
| Hassan  | Rajabi   | Multimedia Programmer      |
| Paul    | Simon    | Multimedia Programmer      |
| Arthur  | Hoopla   | Multimedia Programmer      |
| Kim     | Hunter   | Senior Web Designer        |
| Roger   | Lewis    | System Administrator       |
| Danny   | Gibson   | System Administrator       |
| Mike    | Harper   | Senior Marketing Executive |
| Shahida | Ali      | Customer Service Manager   |
| Peter   | Champion | Finance Manager            |
+---------+----------+----------------------------+
14 rows in set (0.00 sec)

BETWEEN is employed to specify integer ranges. Thus instead of age >= 32 AND age <= 40, we can use age BETWEEN 32 and 40.

select f_name, l_name, age from
    -> employee_data where age BETWEEN
    -> 32 AND 40;

+---------+------------+------+
| f_name  | l_name     | age  |
+---------+------------+------+
| John    | Hagan      |   32 |
| Ganesh  | Pillai     |   32 |
| John    | MacFarland |   34 |
| Alok    | Nanda      |   32 |
| Hassan  | Rajabi     |   33 |
| Arthur  | Hoopla     |   32 |
| Kim     | Hunter     |   32 |
| Roger   | Lewis      |   35 |
| Danny   | Gibson     |   34 |
| Mike    | Harper     |   36 |
| Shahida | Ali        |   32 |
| Peter   | Champion   |   36 |
+---------+------------+------+
12 rows in set (0.00 sec)

You can use NOT with BETWEEN as in the following statement that lists employees who draw salaries less than $90000 and more than $150000.

select f_name, l_name, salary
    -> from employee_data where salary
    -> NOT BETWEEN
    -> 90000 AND 150000;
    
+---------+------------+--------+
| f_name  | l_name     | salary |
+---------+------------+--------+
| Manish  | Sharma     | 200000 |
| Mary    | Anchor     |  85000 |
| Fred    | Kruger     |  75000 |
| John    | MacFarland |  80000 |
| Edward  | Sakamuro   |  75000 |
| Alok    | Nanda      |  70000 |
| Paul    | Simon      |  85000 |
| Arthur  | Hoopla     |  75000 |
| Hal     | Simlai     |  70000 |
| Joseph  | Irvine     |  72000 |
| Shahida | Ali        |  70000 |
+---------+------------+--------+
11 rows in set (0.00 sec)

Ordering data This section of the online MySQL tutorial looks at how we can change the display order of the data extracted from MySQL tables using the ORDER BY clause of the SELECT statement.

The data that we have retrieved so far was always displayed in the order in which it was stored in the table. Actually, SQL allows for sorting of retrieved data with the ORDER BY clause. This clause requires the column name based on which the data will be sorted. Let's see how to display employee names with last names sorted alphabetically (in ascending order).

SELECT l_name, f_name from
employee_data ORDER BY l_name;

+------------+---------+
| l_name     | f_name  |
+------------+---------+
| Ali        | Shahida |
| Anchor     | Mary    |
| Champion   | Peter   |
| Gibson     | Danny   |
| Hagan      | John    |
| Harper     | Mike    |
| Hoopla     | Arthur  |
| Hunter     | Kim     |
| Irvine     | Joseph  |
| Kruger     | Fred    |
| Lewis      | Roger   |
| MacFarland | John    |
| Nanda      | Alok    |
| Pandit     | Anamika |
| Pillai     | Ganesh  |
| Rajabi     | Hassan  |
| Sakamuro   | Edward  |
| Sehgal     | Monica  |
| Sharma     | Manish  |
| Simlai     | Hal     |
| Simon      | Paul    |
+------------+---------+
21 rows in set (0.00 sec)

Here are employees sorted by age.

SELECT f_name, l_name, age
from employee_data
ORDER BY age;

+---------+------------+------+
| f_name  | l_name     | age  |
+---------+------------+------+
| Edward  | Sakamuro   |   25 |
| Mary    | Anchor     |   26 |
| Anamika | Pandit     |   27 |
| Hal     | Simlai     |   27 |
| Joseph  | Irvine     |   27 |
| Manish  | Sharma     |   28 |
| Monica  | Sehgal     |   30 |
| Fred    | Kruger     |   31 |
| John    | Hagan      |   32 |
| Ganesh  | Pillai     |   32 |
| Alok    | Nanda      |   32 |
| Arthur  | Hoopla     |   32 |
| Kim     | Hunter     |   32 |
| Shahida | Ali        |   32 |
| Hassan  | Rajabi     |   33 |
| John    | MacFarland |   34 |
| Danny   | Gibson     |   34 |
| Roger   | Lewis      |   35 |
| Mike    | Harper     |   36 |
| Peter   | Champion   |   36 |
| Paul    | Simon      |   43 |
+---------+------------+------+
21 rows in set (0.00 sec)

The ORDER BY clause can sort in an ASCENDING (ASC) or DESCENDING (DESC) order depending upon the argument supplied.
To list employee first names in descending order, we'll use the statement below.

SELECT f_name from employee_data
ORDER by f_name DESC;

+---------+
| f_name  |
+---------+
| Shahida |
| Roger   |
| Peter   |
| Paul    |
| Monica  |
| Mike    |
| Mary    |
| Manish  |
| Kim     |
| Joseph  |
| John    |
| John    |
| Hassan  |
| Hal     |
| Ganesh  |
| Fred    |
| Edward  |
| Danny   |
| Arthur  |
| Anamika |
| Alok    |
+---------+
21 rows in set (0.00 sec)

Note: The ascending (ASC) order is the default.
Limiting data retrieval

This section of the online MySQL lesson looks at how to limit the number of records displayed by the SELECT statement.
As your tables grow, you'll find a need to display only a subset of data. This can be achieved with the LIMIT clause.
For example, to list only the names of first 5 employees in our table, we use LIMIT with 5 as argument.

SELECT f_name, l_name from
employee_data LIMIT 5;

+---------+--------+
| f_name  | l_name |
+---------+--------+
| Manish  | Sharma |
| John    | Hagan  |
| Ganesh  | Pillai |
| Anamika | Pandit |
| Mary    | Anchor |
+---------+--------+
5 rows in set (0.01 sec)

These are the first five entries in our table.
You can couple LIMIT with ORDER BY. Thus, the following displays the 4 senior most employees.

SELECT f_name, l_name, age from
employee_data ORDER BY age DESC
LIMIT 4;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Paul   | Simon    |   43 |
| Mike   | Harper   |   36 |
| Peter  | Champion |   36 |
| Roger  | Lewis    |   35 |
+--------+----------+------+
4 rows in set (0.00 sec)

Cool, yeh?
Similarly, we can list the two youngest employees.

SELECT f_name, l_name, age from
employee_data ORDER BY age
LIMIT 2;

+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Edward | Sakamuro |   25 |
| Mary   | Anchor   |   26 |
+--------+----------+------+
2 rows in set (0.01 sec)

Extracting Subsets Limit can also be used to extract a subset of data by providing an additional argument.
The general form of this LIMIT is:
SELECT (whatever) from table LIMIT starting row, Number to extract;

SELECT f_name, l_name from
employee_data LIMIT 6,3;

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | MacFarland |
| Edward | Sakamuro   |
| Alok   | Nanda      |
+--------+------------+
3 rows in set (0.00 sec)

This extracts 3 rows starting from the sixth row.

the DISTINCT keyword In this section of the online MySQL guide, we will look at how to select and display records from MySQL tables using the DISTINCT keyword that eliminates the occurences of the same data.

To list all titles in our company database, we can throw a statement as:

select title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Senior Programmer          |
| Senior Programmer          |
| Web Designer               |
| Web Designer               |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Senior Web Designer        |
| System Administrator       |
| System Administrator       |
| Senior Marketing Executive |
| Marketing Executive        |
| Marketing Executive        |
| Marketing Executive        |
| Customer Service Manager   |
| Finance Manager            |
+----------------------------+
21 rows in set (0.00 sec)

You'll notice that the display contains multiple occurences of certain data. The SQL DISTINCT clause lists only unique data. Here is how you use it.

select DISTINCT title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.00 sec)

This shows we have 11 unique titles in the company.
Also, you can sort the unique entries using ORDER BY.

select DISTINCT age from employee_data
ORDER BY age; 

+------+
| age  |
+------+
|   25 |
|   26 |
|   27 |
|   28 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   43 |
+------+
12 rows in set (0.00 sec)

DISTINCT is often used with the COUNT aggregate function, which we'll meet in later sessions.

Finding the minimum and maximum values MySQL provides inbuilt functions to find the minimum and maximum values.

SQL provides 5 aggregate functions. They are:

• MIN(): Minimum value
• MAX(): Maximum value
• SUM(): The sum of values
• AVG(): The average values
• COUNT(): Counts the number of entries

In this session of the online MySQL course, we'll look at finding the minimum and maximum values in a column.

MySQL MIN() - Minimum value

select MIN(salary) from employee_data;

+-------------+
| MIN(salary) |
+-------------+
|       70000 |
+-------------+
1 row in set (0.00 sec)

MySQL MAX() - Maximum value

select MAX(salary) from employee_data;

+-------------+
| MAX(salary) |
+-------------+
|      200000 |
+-------------+
1 row in set (0.00 sec)

Finding the average and sum Totalling column values with MySQL SUM

The SUM() aggregate function calculates the total of values in a column. You require to give the column name, which should be placed inside parenthesis.
Let's see how much Bignet spends on salaries.

select SUM(salary) from employee_data;

+-------------+
| SUM(salary) |
+-------------+
|     1997000 |
+-------------+
1 row in set (0.00 sec)

SImilarly, we can display the total perks given to employees.

select SUM(perks) from employee_data;

+------------+
| SUM(perks) |
+------------+
|     390000 |
+------------+
1 row in set (0.00 sec)

How about finding the total of salaries and perks?

select sum(salary) + sum(perks) from employee_data;

+-------------------------+
| sum(salary)+ sum(perks) |
+-------------------------+
|                 2387000 |
+-------------------------+
1 row in set (0.01 sec)

This shows a hidden gem of the SELECT command. You can add, subtract, multiply or divide values. Actually, you can write full blown arithemetic expressions. Cool!

MySQL AVG() - Finding the Average The AVG() aggregate function is employed for calculating averages of data in columns.

select avg(age) from employee_data;
+----------+
| avg(age) |
+----------+
|  31.6190 |
+----------+
1 row in set (0.00 sec)

This displays the average age of employees in Bignet and the following displays the average salary.

select avg(salary) from employee_data;
+-------------+
| avg(salary) |
+-------------+
|  95095.2381 |
+-------------+
1 row in set (0.00 sec)

Naming Columns

MySQL allows you to name the displayed columns. So instead of f_name or l_name etc. you can use more descriptive terms. This is achieved with AS.

select avg(salary) AS
'Average Salary' from
employee_data;

+----------------+
| Average Salary |
+----------------+
|     95095.2381 |
+----------------+
1 row in set (0.00 sec)

Such pseudo names make will the display more clear to users. The important thing to remember here is that if you assign pseudo names that contain spaces, enclose the names in quotes. Here is another example:

select (SUM(perks)/SUM(salary) * 100)
AS 'Perk Percentage' from
employee_data;

+-----------------+
| Perk Percentage |
+-----------------+
|           19.53 |
+-----------------+
1 row in set (0.00 sec)

Counting The COUNT() aggregate functions counts and displays the total number of entries. For example, to count the total number of entries in the table, issue the command below.

select COUNT(*) from employee_data;

+----------+
| COUNT(*) |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

As you have learnt, the * sign means "all data"
Now, let's count the total number of employees who hold the "Programmer" title.

select COUNT(*) from employee_data
where title = 'Programmer';
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

The MySQL GROUP BY clauseThe GROUP BY clause allows us to group similar data. Thus, to list all unique titles in our table we can issue

select title from employee_data          
GROUP BY title;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.01 sec)

You'll notice that this is similar to the usage of DISTINCT, which we encountered in a previous session.
Okay, here is how you can count the number of employees with different titles.

select title, count(*)
from employee_data GROUP BY title;

+----------------------------+----------+
| title                      | count(*) |
+----------------------------+----------+
| CEO                        |        1 |
| Customer Service Manager   |        1 |
| Finance Manager            |        1 |
| Marketing Executive        |        3 |
| Multimedia Programmer      |        3 |
| Programmer                 |        4 |
| Senior Marketing Executive |        1 |
| Senior Programmer          |        2 |
| Senior Web Designer        |        1 |
| System Administrator       |        2 |
| Web Designer               |        2 |
+----------------------------+----------+
11 rows in set (0.00 sec)

For the command above, MySQL first groups different titles and then executes count on each group.
Sorting the data in MySQLNow, let's find and list the number of employees holding different titles and sort them using ORDER BY.

select title, count(*) AS Number
from employee_data
GROUP BY title 
ORDER BY Number;

+----------------------------+--------+
| title                      | Number |
+----------------------------+--------+
| CEO                        |      1 |
| Customer Service Manager   |      1 |
| Finance Manager            |      1 |
| Senior Marketing Executive |      1 |
| Senior Web Designer        |      1 |
| Senior Programmer          |      2 |
| System Administrator       |      2 |
| Web Designer               |      2 |
| Marketing Executive        |      3 |
| Multimedia Programmer      |      3 |
| Programmer                 |      4 |
+----------------------------+--------+
11 rows in set (0.00 sec)

HAVING clause

To list the average salary of employees in different departments (titles), we use the GROUP BY clause, as in:

select title, AVG(salary)
from employee_data
GROUP BY title;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Customer Service Manager   |  70000.0000 |
| Finance Manager            | 120000.0000 |
| Marketing Executive        |  77333.3333 |
| Multimedia Programmer      |  83333.3333 |
| Programmer                 |  75000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
| System Administrator       |  95000.0000 |
| Web Designer               |  87500.0000 |
+----------------------------+-------------+
11 rows in set (0.00 sec)

Now, suppose you want to list only the departments where the average salary is more than $100000, you can't do it, even if you assign a pseudo name to AVG(salary) column. Here, the HAVING clause comes to our rescue.

select title, AVG(salary)
from employee_data
GROUP BY title 
HAVING AVG(salary) > 100000;

+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Finance Manager            | 120000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
+----------------------------+-------------+
5 rows in set (0.00 sec)

A little more on the MySQL SELECT statementThe MySQL SELECT command is something like a print or write command of other languages. You can ask it to display text strings, numeric data, the results of mathematical expressions etc.
Displaying the MySQL version number

select version();

+-----------+
| version() |
+-----------+
| 3.22.32   |
+-----------+
1 row in set (0.00 sec)

Displaying the current date and time

select now();

+---------------------+
| now()               |
+---------------------+
| 2001-05-31 00:36:24 |
+---------------------+
1 row in set (0.00 sec)

Displaying the current Day, Month and Year

SELECT DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+--------------------------+
|                       28 |
+--------------------------+
1 row in set (0.01 sec)

SELECT MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

SELECT YEAR(CURRENT_DATE);
+--------------------+
| YEAR(CURRENT_DATE) |
+--------------------+
|               2001 |
+--------------------+
1 row in set (0.00 sec)

Displaying text strings

select 'I Love MySQL';

+--------------+
| I Love MySQL |
+--------------+
| I Love MySQL |
+--------------+
1 row in set (0.00 sec)

Obviously you can provide pseudo names for these columns using AS.

select 'Manish Sharma' as Name;

+---------------+
| Name          |
+---------------+
| Manish Sharma |
+---------------+
1 row in set (0.00 sec)

Evaluating expressions in MySQL

select ((4 * 4) / 10 ) + 25;

+----------------------+
| ((4 * 4) / 10 ) + 25 |
+----------------------+
|                26.60 |
+----------------------+
1 row in set (0.00 sec)

Concatenating in MySQL 

With SELECT you can concatenate values for display. CONCAT accepts arguments between parenthesis. These can be column names or plain text strings. Text strings have to be surrounded with quotes (single or double).

SELECT CONCAT(f_name, " ", l_name)
from employee_data 
where title = 'Programmer';

+-----------------------------+
| CONCAT(f_name, " ", l_name) |
+-----------------------------+
| Fred Kruger                 |
| John MacFarland             |
| Edward Sakamuro             |
| Alok Nanda                  |
+-----------------------------+
4 rows in set (0.00 sec)

You can also give descriptive names to these columns using AS.

select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';

+---------------+
| Name          |
+---------------+
| Monica Sehgal |
| Hal Simlai    |
| Joseph Irvine |
+---------------+
3 rows in set (0.00 sec)