Basic Language "MySQL Query" #2 : Change Data Tables, Delete Data Tables, And Display Data Table

What will I learn?

• Changing Table Data
• Delete Data Tables
• Display Data Table

Requirements

• MySQL
• You have basic about MySQL

Difficulty

• Intermediate

Tutorial Contents

Changing Table Data

There are times when we want to correct the wrong data or want to update the old data. For the purpose of changing the data already in the table, available UPDATE query. Here's the basic MySQL query UPDATE format:


UPDATE table_name SET column_name = new_name WHERE condition


The key of the UPDATE query is in the condition section. This condition will limit which data you want to change. For example, I want to change the name of Jakarta province to DKI Jakarta. This means I will change the data in column name_prov become DKI Jakarta where the column has id_prov = 1. Here is the query:

Forgot to write WHERE condition for UPDATE query, can be fatal. Because the entire column will change.

Delete Data Tables
To delete the data contained in a table, there is a DELETE query. Here's the format
essentially:


DELETE FROM name_table WHERE conditions


Just like the UPDATE query, the removal process key is in the condition. For example, I want to delete rows of tables that have the condition of capital_city = Banjarmasin. Here's the command:

Visible, the data of South Kalimantan province with ibu_kota = "Banjarmasin" has been erased from the provincial table.

Display Data Table

Of the many queries in MySQL, the command to display data is the most commonly used, ie SELECT query. The process of creating tables is usually only necessary once at the beginning, but the process of displaying data is constantly being called during the application is used. To display data, we need at least 3 things:

• What columns you want to show.
• The name of the table to be displayed.
• Condition to display data.

SELECT queries need these three things. Here is the basic format of SELECT query writing:


SELECT column1, column2 FROM name_table WHERE conditions;


Especially for conditions, if not written then all data table will be displayed.
For example, to display column name_prov and ibu_kota from provincial table, can use the following query:

If you want to display the entire column, the column names can be replaced with a star (*):

To limit which rows you want to display, can write a section of conditions. For example, I want to display data for id_prov 4 and 5. The query is:

Here I use the OR operator to retrieve data with id_prov 4 and 5. In addition there are many other operators that can be used to create conditions. We will discuss it in a later chapter. SELECT query also provides an optional ORDER BY command to sort the data. The basic format of writing is as follows:


SELECT column1, column2 FROM table_name
WHERE condition ORDER BY nama_kolom_urut [ASC or DESC]


The column_name section is the ordering column. This option can be adjusted with the addition of ASC instruction (short for ascending) for sorting from small to large, and DESC (short for descending) for large to small order. If it is not written, by default ORDER BY will use ASC.

For example, I want to display the entire province data sorted by column nama_prov:

What if sorted by population starting from the largest? add DESC command:

SELECT queries also have additional instructions to limit how many rows to show, ie LIMIT commands.
Here's the basic format:


SELECT column1, column2 FROM table_name
WHERE condition LIMIT line_awal, sum_baris


Where line_early is the beginning of the row you want to display, and the_number number is the number of rows sorted from line_early. The MySQL line number begins with the number 0. Suppose I want to display the data of the top 2 provinces that are based on nama_prov, then the query is:

If we only give 1 number behind the LIMIT instruction, then MySQL assumes that the number is calculated from the top row (row to 0). So the previous view can also be obtained with the following command:

MariaDB [indonesia]> SELECT * FROM province ORDER BY nama_prov LIMIT 2;

Curriculum

Basic Language "MySQL Query" : Creating Tables, Removing Tables, And Adding Data Into Tables.

---

Finished already the tutorial above, may be useful.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---