How to Read Info From a Mysql Table

March 09, 2022 Post a Comment

The article covers the basic syntax of a MySQL SELECT argument and provides examples that show how to utilize a MySQL SELECT statement to think information from tables.

The main task in database management is writing and executing queries – commands for retrieving and manipulating information in databases. Users describe the tasks via these queries, and the database management system performs all the physical operations.

Among the nearly widely used MySQL queries, one would certainly name CREATE, UPDATE, and DELETE. Nonetheless, when we need to apply whatever changes to the database, nosotros kickoff must define the "target". For that, we plow to the SELECT statement, which is 1 of the MySQL operation pillars.

Introduction to MySQL SELECT

MySQL SELECT statement queries the database according to the criteria ready by the operator and returns the rows/columns that match those criteria. With the aid of this MySQL query data method, database administrators can retrieve, group, summarize and clarify information. An absolute majority of all MySQL queries begin with the SELECT query.

This query is neutral – its performance does not produce effects on the database, except for some specific cases which we'll expose further.

MySQL SELECT Statement Syntax

The SELECT query follows the standard syntax:

          SELECT column_to_select FROM table_to_select WHERE weather;

Or, you lot may see the post-obit format:

          SELECT column_to_select  FROM table_to_select  WHERE conditions;

Information technology does not thing if yous write this query in one line or column, with each keyword starting a new line. It is a matter of personal preferences and convenience just. Many specialists prefer the "column" format considering it makes the query easier to read and understand.

Let's dig deeper into the argument syntax to understand how to write the SELECT query in MySQL.

SELECT is the keyword that starts the query and instructs MySQL on what chore it must perform.
FROM is a mandatory clause that specifies the table or tables to select the data from (if you lot employ to multiple tables, you must split their names by commas or utilise the Bring together method).
WHERE is an optional condition, though it is very frequent in MySQL SELECT statements – information technology specifies the criteria to define the necessary data and return it.

When MySQL evaluates the SELECT command, it always starts from evaluating the FROM clause and and so proceeds to the SELECT clause.

At the end of the statement, you take to put a semicolon (;) equally the statement delimiter in MySQL. Oft, queries consist of several commands. So, you separate them by semicolons, thus telling MySQL to execute all those commands individually.

Notation: SQL is not instance-sensitive, then you lot may write the entire query in low case. The code volition work correctly. Writing clauses keywords in upper case is a formatting method to improve the lawmaking readability for users.

Besides the FROM and WHERE clauses, the MySQL SELECT query can include other optional clauses:

Grouping BY organizes the retrieved data by grouping them by columns.
HAVING relates to Grouping Past. When this clause is applied, it selects and returns merely those rows having the specified atmospheric condition Truthful.
Guild Past sorts the records in the returned result-set.
Singled-out removes duplicates from the event-gear up.
LIMIT controls the number of rows retrieved by the query.
ALL returns all matching rows.
HIGH_PRIORITY determines that MySQL must execute the SELECT argument before all the UPDATE operators waiting for the same resource.

SELECT * – "asterisk" – is a replacement for the ALL clause and quite a popular method. Withal, it also has its catches.

MySQL SELECT * returns all data fifty-fifty from those columns that you don't apply and don't need for this query. This may overload the network traffic and produce unnecessary I/O.

One more problem, if the table gets inverse, for example, someone adds new columns without notifying yous, SELECT * will bring you the information you did non expect, spoiling your plans and results.

The solution is to ever specify the target column names to think the information from.

MySQL SELECT Examples

The simplest example of using the MySQL SELECT command is retrieving all data in a table according to a particular benchmark. Take a expect at the following example:

          SELECT * FROM book_prices WHERE quantity >= xv;

We use the "asterisk" option to become all fields from the book_prices table with the quantity greater or equal to 15.

If we want to select specific fields from the table, we must specify them in the SELECT statements:

          SELECT book_title, quantity, author_name, book_price FROM books WHERE price < 15 Social club By price ASC;

This command returns the data on the book titles, quantities of items, and names of the authors for the books with a price less than $15. The results will be present in ascending order.

MySQL SELECT query with JOIN

In virtually cases, databases comprise multiple tables with different data. In your piece of work, you often demand to think the information from several tables simultaneously. The SELECT query in MySQL offers two options. The first one is to define which tables the command should refer to. You specify the column names after the FROM clause and separate them by commas. The second option is to use the JOIN clause.

JOIN combines several tables to remember their data with a single query. When you include Join into the MySQL SELECT statement, the syntax is the following:

          SELECT table1.column1, table2.column2 FROM table1 Bring together table2  ON table1.related_column=table2.related_column;

Assume we desire to know the names of certain customers and which orders they made. Have a wait at the code:

          SELECT order_details.order_id, customers.customer_name FROM customers INNER JOIN order_details ON customers.customer_id = order_details.customer_id;

The query joins the tables containing the order details with the table containing the customers' names. The condition determining which data to give is the customer_id value. This SELECT command will return the rows from the two joined tables where the customer_id values match. Also, yous can lookout this video tutorial:

MySQL SELECT INTO – a specific example of usage

As mentioned at the get-go of this article, using the SELECT clause in MySQL does not touch the database similar changing the stored data. However, there is a example when using this command has a more substantial impact.

The SELECT … INTO statement serves to create new tables and populate tables with data.

The standard syntax of this MySQL argument is every bit follows:

          SELECT column1, column2, column3, ... INTO table1  FROM table2 WHERE condition;

In this statement, annotation the following components:

The list of columns afterwards the SELECT clause in the MySQL query specifies which columns we want to have and insert into a new tabular array.
A table1 should be the name of a table that we'll create and populate with the information from columns after the SELECT keyword. Note that we won't supplant any existing tabular array with this method – we create a new table with a unique name.
A table2 stands for the "source" table where nosotros'll take the data to insert into the new table. Also, with the help of JOINs, data from multiple tables tin can exist retrieved and inserted into the new table.
WHERE is an optional condition for filtering retrieved records.

To retrieve all columns from a table and re-create them into a new one, use the following syntax:

          SELECT * INTO table1 FROM table2 WHERE status;

To recall some columns from the table and copy them into a new table, utilise the following syntax:

          SELECT column1, column2, column3, ... INTO table1 FROM table2 WHERE condition;

Use Bring together to copy the data from several tables into a new tabular array:

          SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2021 FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

A specific example of using the SELECT … INTO command is writing the selected data into files. Information technology helps to save some data separately for further reference. Besides, this statement works every bit a part of Events processes, creating error logs and awarding upshot logs this style.

SELECT … INTO OUTFILE writes the information to a file in a specific output format. This method suggests using columns and line terminators to ensure the specified format. Note: Y'all need the FILE privilege to utilize this command, as it creates a file on the server host.

SELECT … INTO DUMPFILE writes a single row to a file without using any formatting.

A standard case of the SELECT … INTO syntax is as follows:

          SELECT order_id, unit_price, customer_id FROM order_details WHERE quantity < 200 ORDER By customer_id INTO OUTFILE 'orders.txt'      FIELDS TERMINATED By ','      LINES TERMINATED BY '\northward';

This query selects particular information from the tabular array containing details of orders – it returns the data on the orders, prices, and customers' IDs where quantity is less than 200. The results sorted by the customers' IDs will be stored in a new file called orders.txt.

Write and execute MySQL SELECT statements with dbForge Studio for MySQL

Writing queries to work with MySQL databases is a routine, but nevertheless a tiresome task, allow alone all other necessary database-related activities. Fortunately, it does not have to be manual. Lots of professional tools assistance in doing all those tasks. Among them, it is worth mentioning the Devart tools from the dbForge for MySQL product line. The multi-functional dbForge Studio for MySQL does everything you demand if your telescopic is MySQL databases.

Assume nosotros need to retrieve some information about a customer (for example, Mary Smith) from the customer database. In this instance, nosotros volition include the WHERE clause into the SELECT argument. In dbForge Studio for MySQL, nosotros can apply theGenerate Script Equallycharacteristic to become information from the MySQL table.

To retrieve data about the customer, do the following:

1. InDatabase Explorer, correct-click the required table and selectGenerate Script As > SELECT > To New SQL Window. The SQL lawmaking editor opens containing the automatically generated SELECT script.

Using the Generate Script as feature to retrieve information with the SELECT statement from the MySQL table

2. In the SQL code editor, add the WHERE status with the specified value to filter the retrieved data and click Execute.

Adding the WHERE condition to the SELECT statement in dbForge Studio for MySQL

The statement outputs data about Mary Smith that is stored in the MySQL table and displays information technology on the grid.

Output result after using the SELECT statement in dbForge Studio for MySQL

Conclusion

In the article, nosotros have reviewed a MySQL SELECT statement, its syntax, and provided examples that evidence how to write and use a SELECT query in dbForge Studio for MySQL.

Download a gratuitous 30-mean solar day trial version of dbForge Studio for MySQL to evaluate all features and capabilities the tool provides.