MySQL Tutorial for Beginners to Learn SQL

We bring you a comprehensive MySQL tutorial to learn SQL commands for creating, dropping, altering, truncating, and renaming database objects. Also, this tutorial will teach you data manipulation commands such as select, insert, update, and delete commands. Moreover, you’ll know about data control commands like grant and invoke to manage rights and permissions.

MySQL tutorial to learn step-by-step

Let’s begin the tutorial by first knowing a bit about the history of MySQL. It was created by Michael Widenius, who named it after his daughter My. He also later founded Maria DB and again called it by his other daughter’s name Maria.

What is the purpose of MySQL?

MySQL is a lightweight, quick, and simple relational database software. Since it is open-source, it doesn’t incur any cost for use. It is quite popular in the database world and is often used with PHP and Apache Tomcat.

Nowadays, many small to medium companies use it to power up their backends. C and C++ are the programming languages used to develop MySQL.

Why is MySQL so popular?

The success of MySQL is time-proven. It has gained experts’ trust after delivering into the field. However, below are some points defining the reasons for its popularity.

• It is open-source, entirely free to use, and hence gets the impetus.
• It has a laudable set of features that put it ahead of the paid solutions.
• The design of MySQL is robust and capable of handling big data and complex queries.
• You can install it on a variety of platforms such as Windows, Linux, Mac OS X, etc.
• You can develop solutions around it using many languages such as C, C++, Java, PHP, etc.
• It complies with the most common SQL programming standards.
• Most WordPress (Best CMS) installations use MySQL as their backend. It is one of the biggest reasons for its rapid use in Web development.

MySQL has a tough competitor PostgreSQL. Both offer a wide range of features and benefits. Check out the differences between MySQL and PostgreSQL.

MySQL tutorial | DDL commands

In this section, you’ll learn about the following MySQL commands:

• Create, Alter, Drop, Truncate, and Rename

However, you should first know how to add comments while writing commands.

How to add comments in MySQL?

MySQL allows placing comments in the following two ways:

Comment out a single line

You can add a double hyphen (–) at the beginning of a statement to comment it out. The compiler won’t execute the code starting from a “–” till the end of the line (EOL).

Check out the below example:

--Creating a database using a DDL command
CREATE SCHEMA Employee;

Commenting multi-line

To add a multi-line comment or comment out a block of code, you can use a pair of “/*” and “*/” symbols. It is the same as a programmer does in C/C++. Go through the below example:

/* Query:
   Write a simple query that returns all records
   of the Employee table. */
SELECT * FROM Employee;

It is a good practice to attribute your code with useful comments. And you now know how to do that. After this, you can step on the DDL commands.

CREATE

You can use the CREATE statement for creating a schema, tables, or an index. Let’s find out how:

a) CREATE SCHEMA command

This statement results in the creation of a database.

--Syntax:
CREATE SCHEMA Name_of_Database;

--Example:
CREATE SCHEMA EmployeesInfo;

Note:- In MySQL, a schema is similar to a database. You can replace the DATABASE from the SCHEMA keyword and use CREATE SCHEMA in place of CREATE DATABASE.

You can check the previous command result using the following:

--Syntax
SHOW SCHEMAS;

b) CREATE TABLE command

This statement results in the creation of a table in a database.

--Syntax:
CREATE TABLE name_of_table (
field1 data_type,
field2 data_type,
field3 data_type,
....
fieldN data_type);

--Example:
CREATE TABLE Employees
(
EmpID int,
EmpFirstName varchar(255),
EmpLastName varchar(255),
SpouseName varchar(255),
Residence varchar(255),
PinCode int,
State varchar(255)
);

c) CREATE TABLE AS command

This statement results in the creation of a table from a pre-existing table. Hence, the output table will have a similar structure and fields. You can though specify which columns to copy or not.

--Syntax:
CREATE TABLE table_out AS
SELECT field1, field2,...,fieldN
FROM preexisting_table
WHERE ....;

--Example:
CREATE TABLE EmployeeNames AS
SELECT EmpID, EmpFname, EmpLname
FROM Employees;

Please note that it is important to make a correct choice of data type as it can improve or hurt the performance of queries and applications. Hence, you should have a good understanding of MySQL data types.

ALTER

The ALTER statement modifies a table structure. It usually does the following operations:

• Add,
• Modify, or
• Delete constraints or columns.

ALTER TABLE command

You can issue this command to change the properties of a table such as add, update, or remove constraints and fields from a table.

--Syntax:
ALTER TABLE name_of_table
ADD field_name data_type;

--Example:
ALTER TABLE Employees
ADD EmpAge int;

DROP

The DROP command can perform operations like deleting a database, tables, and fields.

DROP SCHEMA command

You can issue this command to drop an entire schema.

--Syntax:
DROP SCHEMA name_of_schema;

--Example:
DROP SCHEMA EmployeeDetails;

DROP TABLE command

You can issue this command to drop an entire table along with its data.

--Syntax:
DROP TABLE name_of_table;

--Example:
DROP TABLE Employees;

TRUNCATE

This statement performs operations such as clearing the data inside a table. However, it doesn’t remove the table altogether.

--Syntax:
TRUNCATE TABLE name_of_table;

--Example:
TRUNCATE TABLE EmployeesInfo;

RENAME

This statement allows us to change the name of a table or tables.

Note: It works such that you can rename multiple tables in one go.

--Syntax:
RENAME TABLE
table_one TO table_one_new
[, table_two TO table_two_new] ...

--Example:
RENAME EmployeesInfo TO EmpInfo;

Next, we are discussing the different database keys you should know while working with MySQL. So, let’s go ahead with this.

MySQL tutorial | Database keys used in tables

You should be familiar with the following five database keys:

Primary key

It represents a column in a table that has all non-null and unique values. You can choose such a field as the primary key. Also, there could be more than one such column, but you need to select one of them.

Candidate key

It is a key that has the potential to become a primary key. It means the candidate keys can also uniquely identify the rows of a table. There could be more than one such key.

Super key

This key has similar attributes as a candidate key; it should be able to distinctly identify a record.

In conclusion, we can say that a candidate key is a superkey, but the reverse is not valid.

Foreign key

A foreign key is one that exists as a primary key in another table. It could have null values and also have duplicates.

Alternate key

These are candidate keys that remain after selecting the primary key.

Database Constraints

Similar to database keys, they also have multiple constraints. You can enable them for columns in a table. Let’s check out some of the essential ones.

Not Null

It restricts a column not to have a null value.

Unique

It makes a column accept only unique values.

Check

The column will only accept values satisfying the check condition.

Default

If the column is empty, then it gets the default value.

Index

It enables faster access to database records.

The above two were must-know concepts to understand. Hope, you now have got the desired clarity. After this, let’s go through the DML commands.

MySQL tutorial | DML commands

In this section,  we are covering DML (data manipulation) commands. You can use these to view, modify, and delete records in a table. These are as follows:

• USE; INSERT; UPDATE; DELETE; SELECT

USE

The USE command sets up a database for any later use of DML commands.

--Syntax:
USE name_of_database;

--Example:
USE EmployeesDB;

INSERT

You can issue an INSERT command to add a new record to a table. You have two options to trigger this statement.

See below is the first option. It requires to specify the column names along with their values.

--Syntax1:
INSERT INTO target_table (field1, field2, field3, ..., fieldN)
VALUES (data1, data2, data3, ..., dataN);

The second option doesn’t require you to provide the column names. Check below:

--Syntax2:
INSERT INTO target_table
VALUES (data1, data2, data3, ..., dataN);

--Examples:
INSERT INTO Employees(EmpID, EmpFirstName, EmpLastName, SpouseName, Residence, State, PinCode, Salary)
VALUES (11011, 'John','Langer', 'Maria Langer', 'Wall Street', 'NewYork', 10005, 45000);

INSERT INTO Employees
VALUES (11011, 'Ben','Stokes', 'Sally Stokes', '10 Downing Street', 'LONDON', 6355, 55000);

UPDATE

This MySQL command makes you change a record or multiple records with new values in a table. Check out the below example.

--Syntax:
UPDATE target_table
SET field1 = data1, field2 = data2, ...
WHERE check_condition;

--Example:
UPDATE EmpInfo
SET EmpFirstName = 'James', PinCode= 10006
WHERE EmpID = 11011;

DELETE

This command removes one or more records matching the given condition in a table. You can use it to delete the tuples that have lost relevance.

--Syntax:
DELETE FROM target_table
WHERE check_condition;

--Example:
DELETE FROM EmpInfo
WHERE EmpFirstName='John';

SELECT

The select command fetches one or more records depending on the specified condition. Hence, you can run this statement to view a part of the table containing the tuples.

It provides two options to execute. The first one makes you customize the result set by specifying columns. Check the below MySQL Select examples.

--Syntax:
SELECT field1, field2, ..., fieldN
FROM target_table;

The second option is where the select command returns all records from the table. It uses the asterisk (*) symbol, which represents ALL.

--Syntax:
SELECT * FROM target_table;

--Examples:
SELECT EmpFirstName, Residence FROM EmpInfo;
SELECT * FROM EmpInfo;

The above were some of the usages of the Select statement. Besides, you can mix up the following keywords with the SELECT command.

• DISTINCT, ORDER BY, GROUP BY, HAVING Clauses

These SELECT clauses help you filter the result set. So, we’ll now demonstrate how to use these with SELECT.

a) SELECT DISTINCT command

This command ensures that the result set includes unique values excluding duplicates. So, it helps you view all the different tuples in a table.

--Syntax:
SELECT DISTINCT field1, field2, ..., fieldN
FROM target_table;

--Example:
SELECT DISTINCT Age FROM Employees;

b) ORDER BY command

When used with SELECT, the ORDER BY command forces the result to appear in ascending order by default. You can though make it return in descending order by appending DESC at the end.

Please note that you can order records by multiple columns one after the other.

--Syntax:
SELECT field1, field2, ..., fieldN
FROM target_table
ORDER BY field1, field2, ..., fieldN ASC|DESC;

--Examples:
SELECT EmpFirstName, EmpLastName FROM EmpInfo
ORDER BY State; 

SELECT * FROM EmpInfo
ORDER BY State DESC;

SELECT * FROM EmpInfo
ORDER BY State DESC, EmpLastName ASC;

c) GROUP BY command

The SELECT … GROUP BY command collects results from a set of rows and groups by one or more fields. You may need this when using aggregate functions (COUNT/MAX/MIN/SUM/AVG).

Please note that you can group tuples by multiple columns one after the other.

--Syntax:
SELECT field1, field2,...,fieldN
FROM target_table
WHERE condition
GROUP BY some_fields
ORDER BY some_fields;

--Example:
SELECT COUNT(EmpID), State
FROM Employees
GROUP BY State
ORDER BY COUNT(EmpID) ASC;

d) SELECT with HAVING clause

The HAVING clause works as a replacement of the WHERE for the SELECT statement when used with GROUP BY on some columns. You can provide the necessary conditions along with it.

Check out the below example.

--Syntax:
SELECT field1, field2,...,fieldN
FROM target_table
WHERE condition
GROUP BY some_fields
HAVING condition
ORDER BY some_fields;

--Example:
SELECT COUNT(EmpID), State
FROM EmpInfo
GROUP BY State
HAVING COUNT(Salary) > 50000;

In the next section, you’ll learn SQL commands like grant, revoke, commit, rollback, and savepoint.

MySQL Tutorial | DCL Commands

In this section, you will see the description and details of DCL (Data Control) commands. Their purpose is to set rights and privileges for MySQL schema. These are:

• GRANT, REVOKE

GRANT

The GRANT statement assigns privileges to users for accessing the database.

--Syntax:
GRANT privileges ON target_table TO user;

--Example:
GRANT CREATE ANY TABLE TO server_name;

REVOKE

The REVOKE statement withdraws privileges and prevents users from accessing the database.

--Syntax:
REVOKE privileges ON target_table FROM user;

--Example:
REVOKE DELETE ON *.* FROM EmpInfo;

Now, we’ve come to the final part of this MySQL tutorial. In this, we will describe the TCL Commands.

MySQL Tutorial | TCL Commands

In this section, we’ll cover the transaction commands of the database. These are:

• COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT, SET TRANSACTION

COMMIT

The COMMIT statement confirms all database transactions that are pending at that time.

--Syntax:
COMMIT;

--Example:
DELETE FROM EmpInfo WHERE Salary <= 5000;
COMMIT;

ROLLBACK

The ROLLBACK statement cancels all database transactions that were part of the most recent COMMIT or ROLLBACK.

--Syntax:
ROLLBACK;

--Example:
DELETE FROM EmpInfo WHERE Salary <= 5000;
ROLLBACK;

SAVEPOINT

The SAVEPOINT statement makes a TAG (a savepoint) for the set of transactions in which to ROLLBACK. After this, you can any time move back to the state by issuing ROLLBACK to the TAG.

--Syntax:
SAVEPOINT TAG_NAME; --Command for creating the SAVEPOINT
ROLLBACK TO TAG_NAME; --Command for rolling back to the tag

--Example:
SAVEPOINT TAG1;
DELETE FROM EmpInfo WHERE Salary <= 5000;
SAVEPOINT TAG2;

RELEASE SAVEPOINT

The RELEASE SAVEPOINT statement dismisses a savepoint created earlier.

--Syntax:
RELEASE SAVEPOINT TAG_NAME;

--Example:
RELEASE SAVEPOINT TAG2;

SET TRANSACTION

The SET TRANSACTION statement assigns an isolation level to a database transaction.

--Syntax:
SET [GLOBAL | SESSION] TRANSACTION [ READ WRITE | READ ONLY ];

The GLOBAL transaction enables the transaction level for all subsequent sessions, whereas the SESSION does it for the current session. If none is specified, then the isolation level defaults to the next transaction performed under the current session.

Note:

We believe that the above MySQL tutorial has provided you with enough material to get comfortable working with MySQL. If you are now interested in practicing MySQL and building queries, then we recommend trying these SQL queries for practice.