You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This section helps you get started with MySQL. We will start installing MySQL, downloading a sample database, and loading data into the MySQL server for practicing.
Downloading MySQL sample database – introduce you to a MySQL sample database named classicmodels. We also provide you links for downloading the sample database and its diagrams.
This section helps you learn how to query data from the MySQL database server. We will start with a simple SELECT statement that allows you to query data from a single table.
SELECT – show you how to use simple SELECT statement to query the data from a single table.
SELECT DISTINCT – learn how to use the DISTINCT operator in the SELECT statement to eliminate duplicate rows in a result set.
SELECT
Introduction to MySQL SELECT statement
The SELECT statement allows you to get the data from tables or views. A table consists of rows and columns like a spreadsheet. Often, you want to see a subset rows, a subset of columns, or a combination of two. The result of the SELECT statement is called a result set that is a list of rows, each consisting of the same number of columns.
See the following employees table in the sample database. It has eight columns: employee number, last name, first name, extension, email, office code, reports to, job title and many rows.
syntax of the SELECT statement:
SELECT
column_1, column_2, ...
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;
The SELECT statement consists of several clauses as explained in the following list:
SELECT followed by a list of comma-separated columns or an asterisk (*) to indicate that you want to return all columns.
FROM specifies the table or view where you want to query the data.
JOIN gets related data from other tables based on specific join conditions.
The SELECT and FROM clauses are required in the statement. Other parts are optional.
MySQL SELECT statement examples
The SELECT statement allows you to query partial data of a table by specifying a list of comma-separated columns in the SELECT clause. For instance, if you want to view only first name, last name, and job title of the employees, you use the following query:
SELECT
lastname, firstname, jobtitle
FROM
employees;
Even though the employees table has many columns, the SELECT statement just returns data of three columns of all rows in the table as highlighted in the picture below:
If you want to get data for all columns in the employees table, you can list all column names in the SELECT clause. Or you just use the asterisk (*) to indicate that you want to get data from all columns of the table like the following query:
SELECT * FROM employees;
It returns all columns and rows in the employees table.
You should use the asterisk (*) for testing only. In practical, you should list the columns that you want to get data explicitly because of the following reasons:
The asterisk (*) returns data from the columns that you may not use. It produces unnecessary I/O disk and network traffic between the MySQL database server and application.
If you explicit specify the columns, the result set is more predictable and easier to manage. Imagine when you use the asterisk(*) and someone changes the table by adding more columns, you will end up with a result set that is different from what you expected.
Using asterisk (*) may expose sensitive information to unauthorized users.
In this tutorial, you’ve learned about the basic MySQL SELECT statement to query data from a table in MySQL.
SELECT DISTINCT
Introduction to MySQL DISTINCT clause
When querying data from a table, you may get duplicate rows. In order to remove these duplicate rows, you use the DISTINCT clause in the SELECT statement.
The syntax of using the DISTINCT clause is as follows:
SELECT DISTINCT
columns
FROM
table_name
WHERE
where_conditions;
MySQL DISTINCT example
Let’s take a look a simple example of using the DISTINCT clause to select the unique last names of employees from the employees table.
First, we query the last names of employees from the employees table using the SELECT statement as follows:
Some employees have the same last name Bondur,Firrelli etc.
To remove the duplicate last names, you add the DISTINCT clause to the SELECT statement as follows:
SELECT DISTINCT
lastname
FROM
employees
ORDER BY lastname;
The duplicate last names are eliminated in the result set when we used the DISTINCT clause.
MySQL alias – introduce you to table alias and aliases to improve the readability of complex queries.
MySQL Join – give you an overview of joins supported in MySQL including cross join, inner join, left join, and right join.
INNER JOIN – use inner join clause to query data from multiple related tables.
LEFT JOIN – learn how to apply left join clause in various contexts.
RIGHT JOIN – show you how to use right join to query data from two or more tables.
CROSS JOIN – make a Cartesian product of rows from multiple tables.
Self-join – joins a table to itself using table alias, and connects rows within the same table using other kinds of joins including inner join and left join.
MySQL subquery – show you how to nest a query (inner query) within another query (outer query) and use the result of the inner query for the outer query.
MySQL derived table – introduce you the derived table concept and show you how to use it to simplify complex queries.
MySQL CTE – explain you the common table expression concept and show you how to use CTE for querying data from tables.
Recursive CTE – guide you how to use the recursive CTE to traverse the hierarchical data.
In this section, you will learn how to insert, update, and delete data from tables using various MySQL statements.
INSERT – learn how to use various forms of the INSERT statement to insert data into database tables.
INSERT IGNORE – explain you the INSERT IGNORE statement that inserts rows into a table and ignore rows that cause errors or exceptions.
UPDATE – learn how to use UPDATE statement and its options to update data in database tables.
UPDATE JOIN – show you how to perform cross table update using UPDATE JOIN statement with INNER JOIN and LEFT JOIN.
DELETE – show you how to use the DELETE statement to remove data from one or more tables.
ON DELETE CASCADE – learn how to use ON DELETE CASCADE referential action for a foreign key to delete data from a child table automatically when you delete data from a parent table.
DELETE JOIN – show you how to delete data from multiple tables.
REPLACE – learn how to insert or update data depends on whether data exists in the table or not.
Prepared Statement – show you how to use the prepared statement to execute a query.
MySQL transaction – learn about MySQL transactions, and how to use COMMIT and ROLLBACK to manage transactions in MySQL.
MySQL table locking – learn how to use MySQL locking for cooperating table access between sessions.
Introducing to MySQL Transaction
To understand what a transaction in MySQL is, let’s take a look at an example of adding a new sales order in our sample database. The steps of adding a sales order are as described as follows:
Query the latest sales order number from the orders table, and use the next sales order number as the new sales order number.
Insert a new sales order into the orders table for a given customer.
Insert new sales order items into the orderdetails table.
Get data from both table orders and orderdetails tables to confirm the changes
Now imagine what would happen to your data if one or more steps above fail because of database failure such as table lock security? If the step of adding order items into orderdetails table failed, you would have an empty sales order in your system without knowing it. Your data may not be integrity and the effort you have to spend to fix it is tremendous.
How do you solve this problem? That’s why the transaction processing comes to the rescue. MySQL transaction enables you to execute a set of MySQL operations to ensure that the database never contains the result of partial operations. In a set of operations, if one of them fails, the rollback occurs to restore the database. If no error occurred, the entire set of statements is committed to the database.
This section shows you how to manage the most important database objects in MySQL including database and tables.
Managing database in MySQL – you will learn various statements to manage MySQL databases including creating a new database, removing an existing database, selecting a database, and listing all databases.
Understanding MySQL Table Types – it is essential to understand the features of each table type so that you can use them effectively to maximize the performance of your databases.
CREATE TABLE – show you how to create new tables in a database using CREATE TABLE statement.
MySQL sequence – show you how to use a sequence to generate unique numbers automatically for the primary key column of a table.
ALTER TABLE – learn how to use the ALTER TABLE statement to change existing table’s structure.
Renaming table – show you how to rename a table using RENAME TABLE statement.
Removing a column from a table – show you how to use the ALTER TABLE DROP COLUMN statement to remove one or more columns from a table.
Adding a new column to a table – show you how to add one or more columns to an existing table using ALTER TABLE ADD COLUMN statement.
DROP TABLE – show you how to remove existing tables using DROP TABLE statement.
MySQL temporary table – discuss MySQL temporary table and show you how to manage temporary tables.
TRUNCATE TABLE – show you how to use the TRUNCATE TABLE statement to delete all data in a table fast.
MySQL data types – show you various data types in MySQL so that you can apply them effectively in designing database tables.
INT – show you how to use integer data type. We also show you how to use ZEROFILL and display width attributes of the integer column.
DECIMAL – show you how to use DECIMAL data type to store exact values in decimal format.
BIT – introduce you BIT data type and how to store bit values in MySQL.
BOOLEAN – explain to you how MySQL handles Boolean values by using TINYINT(1) internally.
CHAR – guide to CHAR data type for storing the fixed-length string.
VARCHAR – give you the essential guide to VARCHAR data type.
TEXT – show you how to store text data using TEXT data type.
DATE – introduce you to the DATE data type and show you some date functions to handle the date data effectively.
TIME – walk you through the features of TIME data type and show you how to use some useful temporal functions to handle time data.
DATETIME – introduce you to the DATETIME data type and some useful functions to manipulate DATETIME values.
TIMESTAMP – introduce you to TIMESTAMP and its features called automatic initialization and automatic update that allows you to define auto-initialized and auto-updated columns for a table.
JSON – show you how to use JSON data type to store JSON documents.
ENUM – learn how to use ENUM data type correctly to store enumeration values.
NOT NULL constraint – introduce you to the NOT NULL constraint and show you how to define a NOT NULL constraint for a column or add the NOT NULL constraint to an existing column.
Primary key constraint – guide you how to use primary key constraint to create the primary key for a table.
Foreign key constraint – introduce you to the foreign key and show you step by step how to create and drop foreign keys.
UNIQUE constraint – show you how to use UNIQUE constraint to enforce the uniqueness of values in a column or a group of columns in a table.
CHECK constraint emulation – walk you through various ways to emulate the CHECK constraint in MySQL.
