Writing queries in MySQL using basic commands

SQL is often referred to as a query, and practical SQL language is used as the standard language for Relational Database Management Systems (RDBMS). SQL commands in all RDBMS are almost similar, having a standard SQL syntax, as well as software-specific functions. This article gives MySQL introduction on how to write MySQL query using basic commands.

MySQL Query

MySQL query is a term used to declare various MySQL commands. Each command does several things to the MySQL database. For example, the MySQL CREATE query command will create a MySQL table and other database objects, while the MySQL SELECT query will display all records from a table using the SQL FROM clause.

MySQL query has a lot of flexibility in terms of writing. Multiple spaces and line breaks (Enter) are not restricted to a specific format. In general, MySQL queries can be used for all SQL-based databases. However, some types of databases have several commands that are unique to each database.

How to write a MySQL query

Some commands require curly brackets ({}), such as entering commands. To mark the end of each command, MySQL uses a semicolon (;). The following is a list of common MySQL query commands. A query in MySQL usually starts with these keywords:

What is * in SQL?

The asterisk (*) has a special use in SQL. It is used to specify all fields and simplify query length, rather than writing all fields or column names. Unless we want specific column names to be returned, we can use the * character.

Examples of how to write MySQL query

We will need a database named firstDB and firstTB table to be created first before we can run INSERT, UPDATE, and DELETE statements. The following syntax step is to create a database and table in MySQL using Windows Command Prompt:

> mysql -u root > CREATE DATABASE firstDB; Query OK, 1 row affected (0.01 sec) > use firstDB; Database changed > CREATE TABLE firstTB (number INT, name VARCHAR(50)); Query OK, 0 rows affected (0.07 sec)

Example of writing MySQL query to insert new data in a table

There is a firstTB table that is created but does not have any data yet. The following syntax is to insert multiple data in the table firstDB:

> INSERT INTO firstTB VALUES (1,’A’); Query OK, 1 row affected (0.03 sec) > SELECT * FROM firstTB; +--------+------+ | number | name | +--------+------+ |      1 | A  | +--------+------+ 1 row in set (0.03 sec) > INSERT INTO firstTB VALUES(2,’B’),(3,’C’),(4,’D’),(5,’E’); Query OK, 4 rows affected (0.03 sec) Records: 4  Duplicates: 0  Warnings: 0 > SELECT number, name FROM firstTB; +--------+------+ | number | name | +--------+------+ |      1 | A  | |      2 | B  | |      3 | C  | |      4 | D  | |      5 | E  | +--------+------+ 5 rows in set (0.00 sec)

As the query shown above, we have inserted several data and selected rows that exist in table firstTB. The SELECT statement provides options if we want to filter data displayed in various conditions, one of which can be with SQL WHERE and OR clauses. The syntax is as follows:

> SELECT * FROM firstTB WHERE number=1 AND name=’A’; +--------+------+ | number | name | +--------+------+ |      1 | A  | +--------+------+ 1 row in set (0.00 sec) > SELECT * FROM firstTB WHERE number=1 OR name=’B’; +--------+------+ | number | name | +--------+------+ |      1 | A  | |      2 | B  | +--------+------+ 2 rows in set (0.00 sec)

Example of writing MySQL query to update existing data in a table

Updating data in a table will have options with or without conditions in the data or row using an UPDATE statement. The options using conditions WHERE clause are for a row or several rows, but it should be noted if the UPDATE statement without condition or the WHERE clause will change the overall data and this is not recommended.

The following is the UPDATE statement using condition WHERE clause to update existing data:

> UPDATE firstTB SET name=’AX’ WHERE number=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1 Warnings: 0 > SELECT * FROM firstTB WHERE number=1; +--------+------+ | number | name | +--------+------+ |      1 | AX  | +--------+------+ 1 row in set (0.00 sec) > UPDATE firstTB SET name=CONCATE(name,’X’) WHERE number=2 OR name LIKE ’%C%’; Query OK, 2 rows affected (0.01 sec) Rows matched: 2  Changed: 2 Warnings: 0 > SELECT * FROM firstTB WHERE number=2 OR name LIKE '%C%'; +--------+------+ | number | name | +--------+------+ |      2 | BX  | |      3 | CX  | +--------+------+ 2 rows in set (0.00 sec)

Example of writing MySQL query to delete existing data in a table

By default, performing a DELETE statement without any additional keyword will delete all records in a table. It is recommended to specify a condition using the WHERE clause to avoid losing data that we need to keep. The following syntax is used to delete row in table firstTB.

> DELETE FROM firstTB WHERE number=5; Query OK, 1 row affected (0.03 sec) > SELECT * FROM firstTB WHERE number=5; Empty set (0.00 sec) > DELETE FROM firstTB WHERE number=3 and number=4; Query OK, 2 rows affected (0.04 sec) > SELECT * FROM firstTB; +--------+------+ | number | name | +--------+------+ |      1 | AX  | |      2 | BX  | +--------+------+ 2 rows in set (0.00 sec)