Basic MySQL Commands to Manage Databases (With Examples)

MySQL is an open-source relational database management system that is widely used across a variety of industries for software products ranging from simple web apps to complex enterprise-level systems. Known for its reliability, performance, and ease of use, MySQL is a cornerstone in the tech stack of countless companies.

MySQL uses Structured Query Language (SQL) to manage and manipulate data. You write a command (a.k.a. a query) in SQL, run it against your database, and get the corresponding output. The very basics of SQL are not that complex, yet they give everything a developer, DBA, or analyst might need for their everyday database management tasks like retrieving the required data, editing it, migrating it between different databases, backing it up, and much more.

This tutorial will give you a detailed overview of these basics, namely, the most common MySQL commands with real examples, syntax explanations, and use cases. Mastering them will help you cover all the essential operations and pave the way to more complex ones.

What are MySQL commands?

In simple words, MySQL commands are instructions that are written in SQL and used to interact with the MySQL server to manage databases, manipulate data, control user access, and maintain data integrity.

These commands can be divided into four main categories:

• DDL (Data Definition Language) commands are used to define and modify database structure. These include CREATE, ALTER, DROP, and TRUNCATE.
• DML (Data Manipulation Language) commands are used to retrieve and manage data in tables. These include SELECT, INSERT, UPDATE, and DELETE.
• DCL (Data Control Language) commands are used to control access to data. These include privilege management commands like GRANT and REVOKE.
• TCL (Transaction Control Language) commands are used to manage transactions and ensure data consistency. These include START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT.

Commands can be executed against databases using a number of tools that can be generally divided into two types: command-line tools (e.g., MySQL Command-Line Client) and GUI tools (e.g., MySQL Workbench or dbForge Studio for MySQL). Execution always happens on the MySQL server, regardless of where the command is written or triggered.

Below, we'll show you how these commands work. For better clarity, we will illustrate them with examples executed in a user-friendly GUI for MySQL.

MySQL environment setup

But before proceeding to the actual execution of commands, you will need to get some prerequisites ready. Let's have a look.

First and foremost, you need a MySQL server instance, local or remote, along with valid credentials to access it. At the very least, these credentials must include a host name, a user name, and a password.

Next, you need a database on your server to run commands against. In our case, we'll use the sakila test database.

Finally, you'll need a specialized database management tool acting as a layer that helps you communicate with your databases. Like we mentioned previously, it can be either a command-line tool or a more convenient and intuitive GUI-based tool. In our case, we'll opt for the latter and use dbForge Studio for MySQL, a high-end IDE that boasts a clean interface that is easy to use and understand.

dbForge Studio is available for a free 30-day trial, so you can just as well try it to practice interactions with your own databases. To do that:

• Download the Studio from the official website
• Open the downloaded file and follow the wizard's instructions to install it on your machine
• Open the installed Studio and connect to your MySQL server

This is what it's going to look like.

Basic MySQL command list with examples

Once you connect to a MySQL server via dbForge Studio (or any other tool of your choice), you can proceed to write and execute commands against the databases it contains. The basic list of commands is as follows.

Now let's conveniently group these commands and review them in detail with practical examples.

Database management commands

We will begin with commands that perform basic operations with databases, including creation, deletion, and selection.

CREATE DATABASE

CREATE DATABASE database_name; will create a new database on your server. If a database with such a name exists, your command will return an error.

Optionally, you can write a larger query that includes further details about the database you want to create; for instance, character set and collation.

CREATE DATABASE sakila_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

Now let's run this command in dbForge Studio to see how it works out.

DROP DATABASE

DROP DATABASE database_name; will delete the specified database and all of its contents from the server permanently. This operation cannot be undone, so make sure you are deleting the correct database. Also note that an attempt to drop a non-existent database will return an error; to avoid it, you can include an IF EXISTS clause as follows: DROP DATABASE IF EXISTS database_name;. It will make the operation successful even if your target database does not exist.

Let's drop our newly created database in the Studio.

USE

USE database_name; selects a specific database to run your commands/queries against. After you run USE, all subsequent operations will be performed on the selected database unless you specify otherwise. Also note that after selecting the database, you can create tables, insert data, and run queries without prefixing the database name.

SHOW DATABASES

Run SHOW DATABASES; as it is to retrieve a list of all databases available on your MySQL server. Let's run it against our server instance in dbForge Studio.

Table management commands

Now that we've learned how to create, drop, and show databases, let's take one step further and proceed to actual tables.

CREATE TABLE

If you need to create a table in your database, run the CREATE TABLE command. Its general syntax is as follows:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

An actual query may look like this.

DROP TABLE

Next, DROP TABLE table_name; will permanently delete the employees table and all of its data from your database. Drop tables with caution, because this operation cannot be undone.

ALTER TABLE

If you need to modify the structure of an existing table, use the ALTER TABLE command. Its general syntax may vary depending on the change you would like to introduce. For instance, here's how you add a column.

ALTER TABLE table_name ADD column_name datatype;

Here's how you drop a column.

ALTER TABLE table_name DROP COLUMN column_name;

And here's how you modify a column by changing its data type.

ALTER TABLE table_name MODIFY column_name new_datatype;

Let's modify a table by adding a new column.

SHOW TABLES

Next, you can run SHOW TABLES; as it is to list all the tables in your databases.

DESCRIBE

Finally, run DESCRIBE to view the structure of a table: columns, data types, nullability, keys, and suchlike.

DESCRIBE table_name;
-- or
DESC table_name;

Let's see how this query works in dbForge Studio. Note how the integrated autocompletion helps you as you type.

Now let's run it.

Data manipulation commands

These are commands that will help you retrieve and manage data.

SELECT

The SELECT command is used to retrieve data from one or more tables.

SELECT column1, column2 FROM table_name WHERE condition;

This is how it works in dbForge Studio.

INSERT

The INSERT command is used to add new records (rows) to a table.

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Let's insert a new record in the Studio. To keep it simple, we'll just add a new record to the film table.

UPDATE

The UPDATE command is used to modify existing records in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Now let's update the title of the same film in our table.

DELETE

The DELETE command is used to remove records from a table. Just like with UPDATE, if you omit the WHERE clause, the command will delete all rows in the table.

DELETE FROM table_name WHERE condition;

Let's remove the record we've just added.

Query filtering and sorting

Filtering and sorting commands help narrow down and organize the output in the optimal way. Let's see how to use them.

WHERE

The WHERE clause filters rows based on a condition before any grouping or aggregation. Its syntax is as follows.

SELECT * FROM table_name WHERE condition;

Let's get a list of films that were released in 2007.

ORDER BY

The ORDER BY clause sorts query results by one or more columns (ascending or descending). This is what it looks like.

SELECT * FROM table_name ORDER BY column1 [ASC|DESC];

See how it works in the Studio, with the results ordered by category_id, in ascending order.

LIMIT

This command limits the number of returned rows.

SELECT * FROM table_name LIMIT number;

Now, back in the Studio, let's reorder our table by film_id and retrieve 5 last rows (to do the latter, we'll apply the descending order).

GROUP BY

The GROUP BY clause groups rows that have the same values in specified columns for use with aggregate functions (COUNT(), SUM(), AVG(), etc.).

SELECT column, AGG_FUNC(column2) FROM table_name GROUP BY column;

We need another simple example. Let's count the number of rentals per customer.

HAVING

The HAVING clause filters results after grouping. It is typically used with GROUP BY.

SELECT column, AGG_FUNC(column2) FROM table_name
GROUP BY column
HAVING condition;

Let's find customers who have made more than 30 rentals.

User- and privilege-related commands

User management ensures that only authorized users have the right level of access to your database. Let's see the commands that will help you handle users and privileges in MySQL.

CREATE USER

The CREATE USER command is used to create a new user account.

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

Let's create a new user in the Studio. We will use '%' as the host to allow access from any host.

GRANT

The GRANT command is used to assign privileges to a user.

GRANT privileges ON database.table TO 'username'@'host';

Common privileges include the following:

• SELECT, INSERT, UPDATE, DELETE
• ALL PRIVILEGES
• CREATE, DROP, ALTER
• GRANT OPTION (allows the user to grant privileges to others)

Let's grant SELECT and INSERT privileges to our new user.

REVOKE

The REVOKE command is used to remove privileges from a user.

REVOKE privileges ON database.table FROM 'username'@'host';

For example, let's deprive our user of INSERT privileges on the address table.

SHOW GRANTS

The SHOW GRANTS command is used to display the privileges granted to a user.

SHOW GRANTS FOR 'username'@'host';

This is how the SHOW GRANTS command works for our user.

Note

If you're using dbForge Studio for MySQL, you can manage users in a more flexible and convenient manner. Namely, you can use the integrated Security Manager. Its intuitive interface will make your routine privilege management a breeze.

JOIN operations

JOINs in MySQL allow you to retrieve data from multiple tables based on related columns. There are several different types of JOINs.

• INNER JOIN returns rows with matching values across all specified tables.
• LEFT OUTER JOIN includes all rows from the left table and only matching rows from the right table. Non-matching rows from the right table are filled with NULLs.
• RIGHT OUTER JOIN returns all rows from the right table and only the rows from the left table that meet the JOIN condition. Non-matching rows from the left table are filled with NULLs.
• CROSS JOIN combines every row from one table with each row from another, resulting in a table with all possible row combinations.
• SELF JOIN allows comparisons within the same table or the extraction of hierarchical data. Table aliases are used here to avoid repeating the same table name.

It is also worth noting that MySQL does not directly support FULL OUTER JOIN, which would return both matching and non-matching rows from the joined tables. However, you can achieve this effect by combining LEFT and RIGHT OUTER JOINs.

We have a comprehensive guide called Types of JOINs in MySQL With Examples. Check it out to learn all about the usage and specifics of MySQL JOINs.

Backup and export commands

Let's proceed to the means of backing up your databases, with a brief overview of each.

mysqldump

First of all, you can use mysqldump, a stock command-line MySQL tool that creates a file containing SQL statements to recreate your databases, tables, and data. This is its general syntax.

mysqldump -u [user] -p [database_name] > [file_name]

For instance, here is an example that shows the root user exporting the employees database to an SQL file.

mysqldump -u root -p employees > employees_backup.sql

INTO OUTFILE

Alternatively, you can use the SELECT ... INTO OUTFILE command to export data into a specified file. This is what it looks like, with a few extra specified parameters.

Note

If you are using dbForge Studio for MySQL, you can set up wizard-aided import and export of MySQL data. It is much more flexible and intuitive than executing a command, with handy templates, CLI-powered automation, and up to 14 most popular file formats at your service.

MySQL syntax rules and best practices

It comes as no surprise that following the syntax specifics and best practices will help you avoid errors, improve readability, and maintain consistency in your SQL code. Here are probably the most important of these, covering case sensitivity, punctuation, and formatting.

Case sensitivity

When it comes to case sensitivity, you need to remember the following rules:

• SQL keywords (e.g., SELECT, FROM, WHERE) are not case-sensitive.
• Database and table names are case-sensitive on Unix/Linux but not on Windows (depends on the operating system and MySQL settings).
• Column names are generally not case-sensitive, but it's good to be consistent.

It is generally recommended to write SQL keywords in uppercase for clarity. You can also use lowercase for identifiers (e.g., table and column names).

Naming

Proper naming is vital for clarity and easier collaboration on your databases. To keep things right, adopt consistent naming conventions, be descriptive but concise in your naming, and avoid using special characters or spaces.

Semicolons

A semicolon ; typically ends an SQL statement. It is required when you need to run multiple statements in one execution or in scripts. The common best practice is to always end SQL statements with a semicolon to avoid unexpected behavior, especially in scripts.

Indentation

Use consistent indentation and line breaks to improve readability. Additionally, break long statements into multiple lines, especially for SELECT queries with multiple columns or JOIN clauses.

Quoting

Single quotes ' are used for string literals. Backticks ` are used for identifiers (e.g., table or column names), especially if they contain special characters, are reserved words, or include spaces.

The common best practices are to use single quotes for strings and avoid using backticks unless necessary.

Reserved words

Reserved words (e.g., ORDER, SELECT, GROUP) cannot be used as identifiers unless enclosed in backticks. Thus, it is better to avoid using reserved words for table or column names, but if you really must use them, backticks will do the trick.

Common syntax mistakes

Finally, let's briefly list the most common syntax mistakes to take note of.

• A missing semicolon may prevent the command from running properly; thus, make sure it's there.
• The misuse of quotes, most notably, mixing ' and ", will lead to errors. Thus, use ' for strings consistently.
• Misspelled keywords will cause errors as well. Double-check them or use code completion or AI-powered code generation to prevent such mistakes.
• Incorrect data types will cause errors if you try storing text in numeric columns. Make sure you are not mixing different types.
• The absence of a WHERE clause in DELETE or UPDATE queries can delete all records in your table. Make sure you always use a condition.

All in all, following these rules and best practices will help you write clean, error-free, and easily maintainable SQL code.

Tips for writing and executing MySQL commands faster

Now let's take a look at a concise yet useful checklist of tips you should take note of in order to handle your MySQL commands faster.

• Use AI to generate and optimize your queries; however, make sure you double-check those queries before running, just to know you're getting exactly what you need.
• Employ tools with SQL autocompletion to reduce accidental syntax errors and speed up your casual typing.
• Write and save reusable scripts for recurring tasks.
• Use code snippets for frequent query patterns.
• Use short and meaningful table aliases.
• Wrap commands in transactions for safety, especially when making multiple related changes.
• Adhere to clean, consistent formatting.
• Write comments to document your queries; this might save you lots of time later on.
• Use stored procedures for repetitive logic.
• Master keyboard shortcuts in the tool you are using.

Why use dbForge Studio for MySQL to run commands faster

All the examples we mentioned above were illustrated with dbForge Studio for MySQL, our all-encompassing IDE that helps you cover virtually every database-related task in MySQL and MariaDB. Let's have a brief recap of all the features of the Studio that are related to SQL development (in other words, will help you write MySQL commands effortlessly).

• Clean and intuitive SQL Editor to write and run your code from
• Context-aware code completion and relevant database object suggestions
• Instant syntax check
• Easy formatting with flexible settings and custom profiles
• Quick object information
• A library of predefined and custom code snippets
• Smart debugging of MySQL stored procedures, triggers, functions, and scripts
• Visual query building on diagrams, just in case you don't like manual coding
• Integration with multiple version control systems, invaluable for safe and transparent teamwork
• AI-powered query generation, optimization, and troubleshooting (via dbForge AI Assistant)

All of these features, taken together, make a compelling toolset for your effective daily management of MySQL commands. And that's only one facet of the Studio, which has much more to offer.

Conclusion

Now that you know everything about writing and running the most important MySQL commands, we gladly invite you to get some firsthand experience with them using dbForge Studio for MySQL. Download it for a free 30-day trial, connect to your MySQL database server, and explore the immense range of features and capabilities delivered by this IDE.

We'd be happy if our Studio helped you write better SQL with less effort. Give it a go, and you'll see the results right away.

FAQ

SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';