List of MySQL Commands with Examples

In this tutorial, we are going to see a list of MySQL commands with examples that should be useful for working with databases.

---

1. To connect (from the Unix shell), use -h only if necessary.

mysql -h hostname -u root -p

---

2. To create an SQL database.

create database [databasename];

---

3. To display all databases on the SQL server.

show databases;

---

4. To select a database.

use [database];

---

 
5. To display all the tables in a database.

show tables;

---

6. To display the structure of a table:

describe [table];

---

7. To delete a database.

drop database [databasename];

---

8. To delete a table.

drop table [tablename];

---

9. To Display all the data in a table.

SELECT * FROM [tablename];

---

10. To Display all the columns and information about columns related to a table.

SHOW COLUMNS FROM [tablename];

---

 
11. To check which database is used:

select database();

---

12. To list all the indexes of a table:

show index from [table];

---

13. To create a new table with columns:

CREATE TABLE [tablename] ([column1] VARCHAR(50), [column2] DATETIME);

---

14. To add a column:

ALTER TABLE [tablename] ADD COLUMN [column3] VARCHAR(100);

---

15. To add a column with a unique ID:

ALTER TABLE [tablename] ADD COLUMN [column4] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

---

16. Insert a row in a SQL table:

INSERT INTO [tablename] ([column1], [column2]) VALUES ('[value1]', '[value2]');

---

17. MySQL function to display the current date:

NOW()

---

18. To display the execution plan of an SQL query:

EXPLAIN SELECT * FROM [tablename];

---

19. To select a part of a row in SQL table:

SELECT [column1], [column2] FROM [table];

---

 
20. To count the number of rows in a table.

SELECT COUNT([column]) FROM [table];

---

21. To select specific rows:

SELECT * FROM [table] WHERE [column] = [value];

Other selectors: <, >, !=; to combine several selectors use the operators AND and OR. Example:

SELECT * FROM users WHERE name = 'Alex' OR age > 30;

---

22. Select the rows that contain the value [val].

SELECT * FROM [table] WHERE [column] LIKE '%[val]%';

Example: Select all names that contains ‘al’

SELECT * FROM users WHERE name LIKE '%al%';

Output:

+--------+-----------+--------+
|  id    |    name   |  age   |
+--------+-----------+--------+
|  101   |   ali     |   25   |
|  102   |  Malis    |   15   |
|  103   |  Mokali   |   35   |
|  104   |  Manali   |   40   |
+--------+-----------+--------+

---

23. Select the rows that begin with the value [val].

SELECT * FROM [table] WHERE [column] LIKE 'val%';

Example: Select all names that start with ‘Yo’

SELECT * FROM users WHERE name LIKE 'Yo%';

Output:

+--------+-----------+--------+
|  id    |    name   |  age   |
+--------+-----------+--------+
|  115   |   Yohan   |   33   |
|  130   |   Youssa  |   56   |
|  109   |   Yonaka  |   15   |
|  144   |   Yoyo    |   20   |
+--------+-----------+--------+

---

24. Select the rows starting with ‘val1’ and ending with ‘val2’.

SELECT * FROM [table] WHERE [column] LIKE '[val1_val2]';

Example: Select all the descriptions that start with ‘T’ and end with ‘T’

SELECT * FROM product WHERE description LIKE 'T_T';

Output:

+--------+---------------+
|  id    |  description  |
+--------+---------------+
|  101   |      TiT      |
|  102   |      ToT      |
|  103   |      TaT      |
|  104   |      TuT      |
+--------+---------------+

---

 
25. Select a data interval.

SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Example:

SELECT * FROM users WHERE age BETWEEN 20 and 30;

Output:

+--------+-----------+--------+
|  id    |    name   |  age   |
+--------+-----------+--------+
|  115   |   Yohan   |   20   |
|  130   |   Thomas  |   25   |
|  109   |    Jean   |   29   |
|  144   |    Alex   |   21   |
+--------+-----------+--------+

---

26. Select with a custom order and only a limit:

SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value];

Order: DESC (Descendant) ↓, ASC (ascendant) ↑.
 
Example:

SELECT * FROM users ORDER BY age ASC LIMIT 3;

Output:

+--------+-----------+--------+
|  id    |    name   |  age   |
+--------+-----------+--------+
|  115   |   Yohan   |   20   |
|  130   |   Thomas  |   21   |
|  109   |    Jean   |   25   |
+--------+-----------+--------+

---

27. Update rows:

UPDATE [table] SET [column] = '[new_val]' WHERE [column] = '[old_val]';

Example:

UPDATE users SET age = '30' WHERE age = '25';

---

28. Delete rows:

DELETE FROM [table] WHERE [column] = [value];

---

29. Delete all rows from a table (without deleting the table itself)

DELETE FROM [table];

---

 
30. Delete all rows from a table:

truncate table [table];

---

31. Remove columns from a table:

ALTER TABLE [table] DROP COLUMN [column];

---

32. Delete a table:

DROP TABLE [table];

---

33. Delete a database:

DROP DATABASE [database];

---

34. Create an alias to temporarily rename a column:

SELECT [column] AS [col] FROM [table];

---

35. Export a database dump:

mysqldump -u [username] -p [database] > backup.sql

---

36. Import a database dump:

mysql -u [username] -p -h localhost [database] < backup.sql

---

37. Find the IP address of the Mysql host:

SHOW VARIABLES WHERE Variable_name = 'hostname';

---

38. SELECT from several tables:

SELECT [table1].[column], [table2].[column] FROM [table1], [table2];

---

39. List all users:

SELECT User FROM mysql.user;

---

 
40. Create a new user:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

---

41. Grant full user access for * tables:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

 

Aggregation functions

1. Select rows without duplicates:

SELECT distinct nom, adresse FROM employe WHERE nom = "Alex";

---

2. Calculate the sum of the rows:

SELECT SUM([column]) FROM [table];

---

3. Calculate the sum of the rows in [col] and group by [category]:

SELECT [column], SUM([col]) FROM [table] GROUP BY [category];

---

4. Get the largest value in a column.

SELECT MAX([column]) FROM [table];

---

5. Get the smallest value in a column.

SELECT MIN([column]) FROM [table];

---

6. Get the average of a column.

SELECT AVG([column]) FROM [table];

---

7. Get the rounded average of a column and group by [category]:

SELECT [column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category];

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More