Skip to Content
202506MySQL

MySQL

Introduction

This tutorial is my note of Sams Teach Yourself SQL in 10 Minutes (Fifth Edition)  book. Initialize your MySQL database using the sample data from this download link .

Login to MySQL

Input

sudo mysql -u root -p
# Press Enter because no password

Output

You will see the MySQL prompt:

mysql>

Show Databases

Input

SHOW DATABASES;

Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

Explanation

Shows all available databases on the MySQL server.

  • SHOW DATABASES lists all database schemas
  • Default system databases: information_schema, mysql, performance_schema, sys
  • Each database acts as a container for tables and data

Create Database

Input

CREATE DATABASE study;

Output

Query OK, 1 row affected (0.04 sec)

Explanation

Creates a new database named “study” for storing tables and data.

  • CREATE DATABASE syntax creates a new database schema
  • Database name must be unique on the server
  • Success message shows “1 row affected” meaning one database was created

Verify Database Created

Input

SHOW DATABASES;

Output

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Explanation

Confirms the new database “study” appears in the database list.

  • Previous result had 4 databases, now shows 5
  • The “study” database now appears in alphabetical order
  • Verification step ensures the CREATE command succeeded

Use Database

Input

USE study;

Output

Database changed

Explanation

Switches the active database context to “study” for subsequent operations.

  • USE command sets the default database for queries
  • All table operations will now target the “study” database
  • Required before creating tables or querying data

Basic SELECT

Input

SELECT prod_name FROM Products;

Output

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set (0.01 sec)

Explanation

Retrieves data from a single column of the Products table.

  • SELECT is the fundamental command for querying data
  • column_name specifies which field to retrieve
  • FROM table_name specifies the source table
  • Results show data in insertion order (no sorting applied)

Select Multiple Columns

Input

SELECT prod_id, prod_name, prod_price FROM Products;

Output

+---------+---------------------+------------+
| prod_id | prod_name           | prod_price |
+---------+---------------------+------------+
| BNBG01  | Fish bean bag toy   |       3.49 |
| BNBG02  | Bird bean bag toy   |       3.49 |
| BNBG03  | Rabbit bean bag toy |       3.49 |
| BR01    | 8 inch teddy bear   |       5.99 |
| BR02    | 12 inch teddy bear  |       8.99 |
| BR03    | 18 inch teddy bear  |      11.99 |
| RGAN01  | Raggedy Ann         |       4.99 |
| RYL01   | King doll           |       9.49 |
| RYL02   | Queen doll          |       9.49 |
+---------+---------------------+------------+
9 rows in set (0.00 sec)

Explanation

Retrieves multiple specific columns from the table.

  • Comma-separated column names specify which fields to return
  • Column order in SELECT determines display order
  • More efficient than SELECT * when you only need specific data
  • Results show related data across multiple columns

Select All Columns

Input

SELECT * FROM Products;

Output

+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
| BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
| RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
| RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
9 rows in set (0.02 sec)

Explanation

Retrieves all columns from the table using the wildcard selector.

  • * wildcard represents all columns in the table
  • Displays complete table structure: prod_id, vend_id, prod_name, prod_price, prod_desc
  • Useful for exploration but can be inefficient for large tables
  • Column order matches the table definition order

Select Distinct Values

Input

SELECT vend_id FROM Products;

Output

+---------+
| vend_id |
+---------+
| BRS01   |
| BRS01   |
| BRS01   |
| DLL01   |
| DLL01   |
| DLL01   |
| DLL01   |
| FNG01   |
| FNG01   |
+---------+
9 rows in set (0.00 sec)

Input

SELECT DISTINCT vend_id FROM Products;

Output

+---------+
| vend_id |
+---------+
| BRS01   |
| DLL01   |
| FNG01   |
+---------+
3 rows in set (0.01 sec)

Explanation

Demonstrates how DISTINCT eliminates duplicate values from results.

  • First query shows all vend_id values including duplicates (9 rows)
  • DISTINCT keyword filters out duplicate values
  • Second query returns only unique vend_id values (3 rows)
  • Useful for finding unique values in columns with repeated data

Limit Results

Input

SELECT prod_name FROM Products;

Output

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set (0.01 sec)

Input

SELECT prod_name FROM Products LIMIT 5;

Output

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
+---------------------+
5 rows in set (0.00 sec)

Explanation

Controls the number of rows returned from a query.

  • Full query returns all 9 rows from the table
  • LIMIT 5 restricts results to only the first 5 rows
  • Useful for testing queries or paginating large result sets
  • Returns rows in their natural table order (insertion order)

Limit with Offset

Input

SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

Output

+--------------------+
| prod_name          |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann        |
| King doll          |
| Queen doll         |
+--------------------+
4 rows in set (0.00 sec)

Explanation

Combines LIMIT with OFFSET to skip rows and implement pagination.

  • OFFSET 5 skips the first 5 rows
  • LIMIT 5 then takes up to 5 rows from the remaining data
  • Results in rows 6-9 (only 4 rows remain after skipping first 5)
  • Essential for implementing pagination in applications

Comments in SQL

Input

SELECT prod_name   -- this is a comment
FROM Products;

Input

# This is a comment
SELECT prod_name
FROM Products;

Input

/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name
FROM Products;

Output

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set (0.00 sec)

Explanation

Shows three different ways to add comments to SQL code.

  • -- creates single-line comments (everything after — is ignored)
  • # also creates single-line comments (MySQL extension)
  • /* */ creates multi-line block comments (can span multiple lines)
  • Comments help document code and temporarily disable statements

Sort Data

Input

SELECT prod_name FROM Products;

Output

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set (0.00 sec)

Input

SELECT prod_name FROM Products ORDER BY prod_name;

Output

+---------------------+
| prod_name           |
+---------------------+
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
+---------------------+
9 rows in set (0.00 sec)

Explanation

Demonstrates sorting data using ORDER BY clause.

  • First query shows data in natural table order (insertion order)
  • ORDER BY prod_name sorts results alphabetically by product name
  • Default sort order is ascending (A-Z for text, low-high for numbers)
  • Compare the two outputs to see how ORDER BY changes row sequence

Sort Multiple Columns

Input

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

Output

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)

Explanation

Sorts data by multiple columns in priority order.

  • First sorts by prod_price (ascending, lowest to highest)
  • Then sorts by prod_name (ascending, A-Z) for rows with same price
  • Notice products with price 3.49 are sub-sorted alphabetically
  • Multiple column sorting creates hierarchical ordering

Sort by Column Position

Input

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

Output

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)

Explanation

Uses column position numbers instead of column names for sorting.

  • ORDER BY 2, 3 means sort by 2nd column (prod_price), then 3rd column (prod_name)
  • Column positions are numbered from left to right starting at 1
  • Results are identical to the previous query
  • Useful shorthand but less readable than column names

Sort Descending

Input

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

Output

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)

Explanation

Reverses sort order using DESC keyword.

  • DESC changes default ascending order to descending
  • Products now sorted from highest price (11.99) to lowest (3.49)
  • Products with same price maintain their natural table order
  • Default is ASC (ascending) when no direction specified

Sort Mixed Direction

Input

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

Output

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.01 sec)

Explanation

Combines different sort directions for multiple columns.

  • prod_price DESC sorts price from high to low
  • prod_name (no DESC) sorts name A-Z within same price groups
  • Notice 3.49 price products are now sorted alphabetically: Bird, Fish, Rabbit
  • Each column can have independent sort direction (ASC or DESC)

Filter Data with WHERE

Input

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

Output

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
+---------------------+------------+
3 rows in set (0.00 sec)

Explanation

Filters data using WHERE clause with equality condition.

  • WHERE clause filters rows based on specified conditions
  • prod_price = 3.49 returns only rows where price equals exactly 3.49
  • Equality operator = performs exact match comparison
  • Results show only the 3 products with price 3.49

Comparison Operators

Input

SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;

Output

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| 8 inch teddy bear   |       5.99 |
| 12 inch teddy bear  |       8.99 |
| Raggedy Ann         |       4.99 |
| King doll           |       9.49 |
| Queen doll          |       9.49 |
+---------------------+------------+
8 rows in set (0.00 sec)

Input

SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;

Output

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| 8 inch teddy bear   |       5.99 |
| 12 inch teddy bear  |       8.99 |
| Raggedy Ann         |       4.99 |
| King doll           |       9.49 |
| Queen doll          |       9.49 |
+---------------------+------------+
8 rows in set (0.00 sec)

Explanation

Demonstrates less-than and less-than-or-equal comparison operators.

  • < operator finds values strictly less than the specified value
  • <= operator finds values less than or equal to the specified value
  • Both queries return 8 rows since no products cost exactly 10.00
  • Useful for finding products within price ranges

Not Equal Operators

Input

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

Output

+---------+--------------------+
| vend_id | prod_name          |
+---------+--------------------+
| BRS01   | 8 inch teddy bear  |
| BRS01   | 12 inch teddy bear |
| BRS01   | 18 inch teddy bear |
| FNG01   | King doll          |
| FNG01   | Queen doll         |
+---------+--------------------+
5 rows in set (0.00 sec)

Input

SELECT vend_id, prod_name
FROM Products
WHERE vend_id != 'DLL01';

Output

+---------+--------------------+
| vend_id | prod_name          |
+---------+--------------------+
| BRS01   | 8 inch teddy bear  |
| BRS01   | 12 inch teddy bear |
| BRS01   | 18 inch teddy bear |
| FNG01   | King doll          |
| FNG01   | Queen doll         |
+---------+--------------------+
5 rows in set (0.00 sec)

Explanation

Shows two equivalent ways to express “not equal” conditions.

  • <> is the standard SQL not-equal operator
  • != is an alternative not-equal operator (MySQL extension)
  • Both exclude products from vendor ‘DLL01’
  • Results are identical - choose based on preference or standard

Range Filtering

Input

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

Output

+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 8 inch teddy bear  |       5.99 |
| 12 inch teddy bear |       8.99 |
| King doll          |       9.49 |
| Queen doll         |       9.49 |
+--------------------+------------+
4 rows in set (0.00 sec)

Explanation

Uses BETWEEN operator for range filtering.

  • BETWEEN 5 AND 10 finds values from 5 to 10 inclusive
  • More readable than WHERE prod_price >= 5 AND prod_price <= 10
  • Both boundary values (5 and 10) are included in results
  • Useful for finding products within specific price ranges

NULL Value Testing

Input

SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

Output

Empty set (0.00 sec)

Input

SELECT cust_name
FROM Customers
WHERE cust_email IS NULL;

Output

+---------------+
| cust_name     |
+---------------+
| Kids Place    |
| The Toy Store |
+---------------+
2 rows in set (0.01 sec)

Explanation

Demonstrates testing for NULL values using IS NULL operator.

  • IS NULL finds rows where the column contains no value
  • Cannot use = NULL - must use IS NULL for NULL testing
  • First query shows no products have NULL prices
  • Second query finds customers without email addresses
  • Essential for handling missing or unknown data
Last updated on
LearnMySQL Tutorial Content Instructions
Do not shoot this.