The data-driven world of today requires a lot of data to function. A majority of the data is stored as databases, which is an organized collection of data. A database management system or DBMS in short acts as a bridge between the database and end-user to ensure that the data is well organized and easily accessible. A DBMS assists in optimizing, storing, retrieving, and managing data in databases.
MySQL is the most widely used database management system software for managing relational databases today. It is an open-source database that is supported by the Oracle Corporation. When compared to Microsoft SQL Server and Oracle Database, it is a quick, scalable, and simple to use database management system. It’s frequently combined with backend languages to build powerful and dynamic server-side or web-based enterprise applications. MySQL AB, a Swedish business, created, marketed, and supported it, and it was written in the C and C++ programming languages. Many small and large businesses use MySQL. It supports a variety of operating systems, including Windows, Linux, macOS, and others. MySQL is a Relational Database Management System (RDBMS) that offers a variety of features, including:
- It allows us to use tables, rows, columns, and indexes and to perform database operations on them.
- Tables (collection of rows and columns), also known as relations, are used to construct database relationships.
- It ensures that the Referential Integrity (referential integrity is an RDBMS concept which states that any foreign key field must agree with the primary key that is referenced by the foreign key) between rows or columns of different tables is maintained.
- It automatically updates the table indexes as soon as a record is changed.
- It employs a number of SQL queries and integrates useful data from many columns and rows for end-users.
This article covers all the most frequently used commands and statements. With this handy collection of MySQL commands, you’ll be able to operate more efficiently and effectively with your MySQL databases. You’ll see at a glance the most commonly used commands and statements you need for creating and managing the databases in MySQL.
MySQL commands
Working with databases
A database is a collection of organized records that the user can conveniently access and manage. It organizes the data into tables, rows, columns, and indexes to make it easier to retrieve the information we need quickly. Let’s try to build a student database using the following command.
The following syntax can create a database. It also verifies whether the database name is already in use.
CREATE DATABASE IF NOT EXISTS db_name;
Use the following syntax to replace the current database with another database you’re working on:
mysql> USE db_name;
Using the following syntax, we can permanently destroy a database and its associated files. All the tables that are a part of that database will be deleted if we use the following command.
DROP DATABASE IF EXISTS db_name;
Use the following syntax to display all databases on the current server:
mysql> SHOW DATABASES;
As you can see in the snapshot above, we created a database named ‘students’ using create command, and deleted a database named class using DROP command.
Working with tables
A table in a database is a collection of related data organized in a table structure. It’s made up of columns and rows. A table is a collection of data components in relational and flat-file databases that uses a model of vertical columns and horizontal rows, with the cell being the unit where a row and column intersect.
Let’s create the below table:
MARKS TABLE
| ID | Name | Marks |
| 001 | Ashish | 94 |
| 002 | Bharat | 81 |
| 003 | Deepak | 78 |
| 004 | Fatima | 92 |
This command displays all tables in a current database.
SHOW TABLES;
This command creates a new table:
CREATE TABLE [IF NOT EXISTS] table_name(
Column_name1 datatype, Column_name2 datatype……);
The datatype can be integer, char (fixed length sring), varchar (variable length string), binary etc. The data which is going to be stored in a column decides the datatype of the column. For example, if the column is going to store numerals, integer datatype can be used or if the column name is going to store a string of variable length, varchar can be used. For example, to create the above Marks table, type the following code:
CREATE TABLE Marks(ID integer, Name varchar (100), Marks integer);
To insert values into a table type the following command:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
The values should correspond to the column name in which the value is to be stored.
For example, to insert first column of the students table, you have to type the following command:
INSERT INTO Marks
VALUES (001, ‘Ashish’,94);
In a table, add a new column:
ALTER TABLE table
ADD column_name datatype;
For example to add Mentor’s Name column, you have to type the following command:
ALTER TABLE Marks
ADD Mentor varchar(100);
To remove a column from a table, do the following:
ALTER TABLE table_name
DROP column_name;
Add an index to a table on a column with a specified name:
ALTER TABLE table
ADD INDEX [name](column, …);
To add a primary key to a table, do the following
ALTER TABLE table_name
ADD PRIMARY KEY (column_name,…);
To remove the primary key of a table, do the following:
ALTER TABLE table_name
DROP PRIMARY KEY;
You can drop a table using the following command:
DROP TABLE [IF EXISTS] table_name;
To show the columns of a table, type the following code:
DESCRIBE table_name;
To show the information of a column of a table, type the following command:
DESCRIBE table_name column_name;
Working with indexes
Assume we have a contact book with the users’ names and phone numbers. We’re looking for a phone number in this contact book. If the contact book is in an unordered manner, which means the names aren’t organized alphabetically, we’ll have to go through all the pages and read every name until we don’t locate the requested name. Sequential searching is the name for this form of search. While this approach is correct, applying this in a large database will consume a lot of time. In this situation, database indexing aids in the retrieval of the desired result and increases the query’s overall performance.
Indexes are used to quickly locate records with certain column values. It is similar to the index given in a book, you can easily locate a chapter using the index of the book.
To create an index with the specified name on a table:
CREATE INDEX index_name
ON table_name (column,…);
So, to create index of column Name of the above Marks table, we can write the following command:
CREATE INDEX new_index
ON Marks(Name);
Drop an index:
DROP INDEX index_name;
To drop the above created index, write the following command:
DROP INDEX new_index;
A unique index ensures that the index key includes no duplicate values, ensuring that each entry in the table is distinct in some sense. To create a unique index, use the following syntax:
CREATE UNIQUE INDEX index_name
ON table_name (column,…);
Read more – Indexing in SQL
Working with views
A view is a database object that doesn’t have any data in it. Its contents are based on the table that serves as the foundation. It has the same rows and columns as a genuine table. In MySQL, a View is a virtual table that is produced by connecting one or more tables in a query. It works in the same way as the base table, but it doesn’t have any data of its own. The fundamental distinction between a view and a table is that views are definitions constructed on top of other tables (or views).
Create a new view by following these steps:
CREATE VIEW [IF NOT EXISTS] view_name
AS Select query ;
Let’s try to understand it with an example. We are going to create a view from the Marks table mentioned above.
We can use SELECT to see the actual view, just like what we do with tables.
With the WITH CHECK OPTION, create a new view.
CREATE VIEW [IF NOT EXISTS] view_name
AS select_statement
WITH CHECK OPTION;
Make a new view or replace an existing one:
CREATE OR REPLACE view_name
AS
select_statement;
Consider this to drop a view:
DROP VIEW [IF EXISTS] view_name;
To drop the above view (new_view) created, we can use the command:
DROP VIEW new_view;
To drop multiple views:
DROP VIEW [IF EXISTS] view1, view2, …;
Rename a view:
RENAME TABLE view_name
TO new_view_name;
Show views from a database:
SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_type = ‘VIEW’;
Working with stored procedures
A stored procedure is a piece of prepared SQL code that you can save and reuse over and over. So, if you have a SQL query that you create frequently, save it as a stored procedure and then call it to run it. You can also pass parameters to a stored procedure so that it can act based on the value(s) of the parameter(s) passed.In the normal computing language, it is a subroutine or a subprogram. MySQL version 5 was the first to include it. It is currently supported by nearly all relational database systems.
Create a stored procedure, use syntax:
DELIMITER $$
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
body
END $$
DELIMITER;
To call a procedure in MySQL, use the syntax:
CALL procedure_name;
Let’s try to create a procedure. We will create a procedure that shows all the students who have scored an A grade (90+ marks).
First, open MySQL workbench and select the database (students database in our case). Then we have to store our procedure.
Now we can call the created procedure.
Drop a stored procedure:
DROP PROCEDURE [IF EXISTS] procedure_name;
Show stored procedures:
SHOW PROCEDURE STATUS
[LIKE ‘pattern’ | WHERE search_condition];
You can also create and store functions with parameters using the syntax:
DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
— statements
END $$
DELIMITER ;
Drop a stored function:
DROP FUNCTION [IF EXISTS] function_name;
Querying data from tables
A query is any command used to retrieve data from a table in relational database management systems. The SELECT statement is most often used in Structured Query Language (SQL) queries. Select is a type of DQL (Data Query Language) command. DQL statements are used to perform queries.
For example, to query the entire contents of a table, use the following command:
SELECT * FROM table_name;
The above query will return each record of the given table.
For example:
If you want MySQL to show only some columns of the table, you can use the following syntax:
SELECT
column1, column2, …
FROM
Table_name;
Remove duplicate rows from a query’s result:
SELECT
DISTINCT (column)
FROM
Table_name;
The above syntax will return distinct elements of the column specified. For example, if we have a column ‘Country’ in a table, it will have a lot of duplicate values, i.e. there is a chance that more than one person belongs to a country. If we want to see what are the distinct countries present in the table, we can use distinct clause.
We can filter records using WHERE clause. It returns only those records which fulfill a specified set of condition.
SELECT column_list
FROM table_name
WHERE condition;
For example, if we want to write a query to retrieve the name of students whose score is less than 80, it will look something like this:
SELECT Name from Marks
WHERE Marks <80;
You can change the output of the column name using column alias:
SELECT
column1 AS alias_name,
expression AS alias,
…
FROM
Table_name;
The name of the column will be changed to alias_name in the result (not in the actual table).
Counting the number of rows in a table:
SELECT COUNT(*)
FROM table_name;
You can sort a result set in ascending and descending order by writing the following code::
SELECT
select_list
FROM
table_name
ORDER BY
column1 ASC [DESC],
column2 ASC [DESC];
Group rows using the GROUP BY clause.
SELECT select_list
FROM table_name
GROUP BY column_1, column_2, …;
Join
In a join, you can get records from two (or more) logically connected tables in one result set. JOIN clauses are used to return the rows of two or more queries that use two or more tables that have a meaningful relationship based on a set of values in common. These values are normally the same column name and datatype that occur in both of the connected tables. The join key is usually, but not always, the primary key of one table and a foreign key in another table. As long as the data in the columns matches, the join can be executed.
Let’s look at the following tables to understand join better:
| members_id | name |
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
| committee_id | name |
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
Inner Join
Each row from the first table is compared to every record from the second table in the inner join clause. If the join condition is met in both rows, the inner join clause creates a new row with a column that contains all columns from both tables and includes it in the result set. In other words, only matched rows from both tables are included in the inner join clause. Each row from the first table is compared to every record from the second table in the inner join clause.
To query data from multiple tables using inner join, use the following command:
SELECT select_list
FROM table1
INNER JOIN table2 ON condition;
Consider the following syntax to inner join the above tables:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
INNER JOIN committees c ON c.name = m.name;
It will produce the following result:
| member_id | member | committee_id | committee |
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
Left join
A join predicate is required for a left join, just as it is for an inner join.The left join selects data from the table on the left. The left join compares every row in the left table to every row in the right table. The left join clause creates a new row whose columns comprise all columns of the rows in both tables if the values in the two rows satisfy the join condition, and includes this row in the result set. The left join clause still creates a new row whose columns contain columns from the left table and NULL for columns from the right table if the values in the two rows do not match.
You can query data from multiple tables using left join by using the following syntax:
SELECT select_list
FROM table1
LEFT JOIN table2 ON condition;
In our case, if we type the following syntax in command line:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name);
The resulting table will look like this:
| members_id | member | committee_id | committee |
| 1 | John | 1 | John |
| 2 | Jane | NULL | NULL |
| 3 | Mary | 2 | Mary |
| 4 | David | NULL | NULL |
| 5 | Amelia | 3 | Amelia |
The right join clause is similar to the left join clause, with the exception that the left and right tables are treated differently. Instead of selecting data from the left table, the right join selects data from the right table.
You can query data from multiple tables using the right join:
SELECT select_list
FROM table1
RIGHT JOIN table2 ON condition;
For example, the following syntax right joins our table:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c on c.name = m.name;
| mambers_id | members | committee_id | committee |
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
| NULL | NULL | 4 | Joe |
A Cartesian product of rows from the joined tables is created by the cross join. To create the result set, the cross join joins every row from the first table with every row from the right table. Let’s say there are n rows in the first table and m rows in the second table. The nxm rows will be returned by the cross join that joins the tables.
Syntax:
SELECT select_list
FROM table1
CROSS JOIN table2;
In order to cross join our two tables, the syntax will look like this:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
CROSS JOIN committees c;
Modifying data in tables
To add a new row to a table, do the following:
INSERT INTO table_name(column_list)
VALUES(value_list);
Create a table with several rows:
INSERT INTO table_name(column_list)
VALUES(value_list1),
(value_list2),
(value_list3),
…;
To update all rows in a table, do the following:
UPDATE table_name
SET column1 = value1,
…;
Update data for a set of rows defined by a WHERE clause criteria:
UPDATE table_name
SET column_1 = value_1,
…
WHERE condition
To delete all rows in a table, the syntax is:
DELETE FROM table_name;
To delete rows specified by a condition:
DELETE FROM table_name
WHERE condition;
Searching
LIKE and RLIKE clauses are used to search desired records from the table.
The SQL LIKE clause compares a value to other values that are similar using wildcard operators. With the LIKE operator, there are two wildcards that can be used.
- The percentage sign (%)
- The underscore (_)
The % sign can be used to indicate zero, one, or more characters. A single number or letter is represented by the underscore. These symbols can be mixed and matched.
The syntax for LIKE clause
SELECT select_list
FROM table_name
WHERE column LIKE ‘%pattern%’ (or ‘_ABC’);
E.g, ‘S%’ will fetch all values that start with S.
‘_AB’ will fetch all values that have A and B at second and third places respectively.
In MySQL, this operator is used to pattern match a string expression against a pattern.
Syntax:
SELECT select_list
FROM table_name
WHERE column RLIKE ‘regular_expression’;
MySQL command-line client commands
mysql -u [username] -p;
To connect MySQL server using MySQL command-line client with a username and password (MySQL will prompt for a password).
mysql -u [username] -p [database];
To connect MySQL Server with a specified database using a username and password.
exit;
To exit MySQL command line.
mysql> system clear;
On Linux, type the above command to clear the MySQL screen console window.
On Windows OS, there is currently no command to clear the MySQL screen console.
Conclusion
MySQL is a very popular open-source database that is commonly used for building websites. The default database system for various CMS (content management systems) like WordPress and Joomla, MySQL, also runs many of the biggest sites on the internet, including Facebook and YouTube. With such large adoption, it’s no wonder that lots of people will run into MySQL from time to time. In this article, we discussed MySQL commands, their different types, and how to use them. You should have at least some basic understanding of databases before learning the MySQL commands.
Frequently Asked Questions
How do I query a MySQL database?
To get data from the MySQL database, use the SELECT command. The SELECT command is used with other clauses like WHERE, ORDER BY, HAVING, JOIN to facilitate the query.
How does the MySQL command line work?
Select the option to run MySQL as a service. Use the MySQL Command-Line Client to get started. Enter the following command in a Command Prompt window to start the client: mysql -u root -p. If MySQL has a root password, the -p option is required.
What is the difference between SQL and MySQL?
MySQL is a relational database that employs SQL to query a database, whereas SQL is a query language. SQL is a language for accessing, updating, and manipulating data in databases. MySQL, on the other hand, is a database that organizes existing data in a database.
What is syntax in MySQL?
The syntax is a set of rules and guidelines that SQL follows. An SQL statement generally begins with one of the following keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW, and terminates with a semicolon (;).
