MySQL must know-4th chapter-Retrieving data

Chapter Fourth Retrieving data

This chapter describes how to use the SELECT statement to retrieve one or more data columns from a table.

4.1 SELECT statement

As described in the 1th Chapter, SQL statements are made up of simple English words. These words are called keywords, and each SQL statement is made up of one or more keywords. Probably, the most frequently used SQL statement is the SELECT statement. Its purpose is to retrieve information from one or more tables. In order to retrieve the table data using SELECT, you must give at least two messages-what to choose and where to choose.

4.2 Retrieving a single column

We'll start with a simple SQL SELECT statement, which looks like this:

The preceding statement uses the SELECT statement to retrieve a column named Prod_name from the Products table. The required column names are given after the Select keyword, where the FROM keyword indicates the name of the table from which the data is retrieved.

unsorted data if the reader tests the query himself, it may find that the data order of the displayed output is different. This behavior is normal. If the query results are not explicitly sorted, the order of the returned data has no special meaning. The order in which the data is returned may or may not be the order in which the data is added to the table. As long as the same number of rows is returned, it is normal. A simple SELECT statement above returns all rows in the table. The data is not filtered (filtering will produce a subset of the result set), and there is no sort.

End SQL statement Multiple SQL statements must be separated by semicolons (;). MySQL, like most DBMS, does not need to add semicolons after a single SQL statement. However, a particular DBMS may have to append a semicolon to a single SQL statement. Of course, you can always add semicolons if you want. In fact, even if not necessary, it is certainly not bad to add semicolons. If you are using the MySQL command line, you must add a semicolon to end the SQL statement.

SQL statement and case note that the SQL statement is not case-sensitive, so select is the same as select. Similarly, it doesn't matter if you write a select. Many SQL developers prefer to use uppercase for all SQL keywords and lowercase for all columns and table names, making the code easier to read and debug. However, it is important to realize that although SQL is case-insensitive, some identifiers, such as database names, table names, column names, may be different: the best way to do this is by using casing conventions, and consistency when used.

When you process an SQL statement with a space, all of the spaces are ignored. SQL statements can be given on a single line, or they can be broken into many rows. Most SQL developers think it is easier to read and debug SQL statements into multiple lines.

4.3 Retrieving multiple columns

To retrieve more than one column from a table, use the same SELECT statement. The only difference is that multiple column names must be given after the SELECT keyword, and the column names must be separated by commas.
Beware of commas when selecting multiple columns, be sure to add a comma between the column names, but not after the last column name. If you add a comma after the last column name, an error occurs.

The following SELECT statement selects 3 columns from the Products table:

As in the previous example, this statement uses the SELECT statement to select data from the table products. In this example, 3 column names are specified, and the column names are separated by commas.

The data representation can be seen from the above output, and SQL statements generally return raw, unformatted data. Formatting data is a matter of presentation, not a retrieval problem. Therefore, it is generally stated in the application that displays the data by means of (aligning and displaying the above price value, representing its amount with a currency symbol and a comma). The raw data that is actually retrieved is rarely used (no format provided by the application).

4.4 Retrieving all Columns

In addition to specifying the required columns (as described above, one or more columns), the SELECT statement can also retrieve all the columns without having to list them individually. This can be achieved by using the asterisk (*) wildcard character at the location of the actual column name, as follows:

If given a wildcard character (*), all columns in the table are returned. The order of the columns is typically the order in which the columns appear in the table definition. But sometimes this is not the case, changes in the schema of the table (such as adding or removing columns) can cause the order to change. Using wildcards generally, it is best not to use the * wildcard character unless you really need each column in the table. Although using wildcards may make it easier for you not to explicitly list the required columns, retrieving the columns you don't need often reduces the performance of your search and applications.

Retrieving unknown columns using wildcards has a big advantage. Columns with unknown names can be retrieved because the column names are not explicitly specified (because asterisks retrieve each column).

4.5 Retrieving different rows

As you can see, select returns all matching rows. But what if you don't want each value to appear every time? For example, if you want to get all the vendor IDs of the products in the product table:

The SELECT statement returns 14 rows (even if there are only 4 vendors in the table) because 14 products are listed in the Product table. So, how do you retrieve a list with different values? The workaround is to use the DISTINCT keyword, which, as the name implies, indicates that MySQL only returns a different value.

SELECT DISTINCT vend_id tells MySQL to return only the different (unique) vend_id rows, so only 4 rows are returned. If you use the DISTINCT keyword, it must be placed directly in front of the column name.

You cannot partially use distinct, the DISTINCT keyword applies to all columns, not just the columns that are predecessor to it. If you give select DISTINCT Vend_id,prod_price, all rows will be retrieved unless the specified two columns are different.

4.6 Limiting results

The SELECT statement returns all matching rows, which may be each row in the specified table. You can use the Limit clause to return the first or previous rows. Here's an example:

This statement uses the SELECT statement to retrieve a single column. LIMIT 5 indicates that MySQL returns no more than 5 rows.
To arrive at the next 5 rows, you can specify the start row and number of rows to retrieve, as follows: LIMIT 5, 5 indicates that MySQL returns 5 rows starting at line 5. The first number is the start position, and the second number is the number of rows to retrieve.

Therefore, a limit with a value always starts at the first line and the number given is the number of rows returned. A limit with two values allows you to specify a position starting at the first value of the line number. Line 0 Retrieves the first behavior of line 0 instead of Line 1. Therefore, LIMIT 1, 1 will retrieve the second row instead of the first row.
Specifies the maximum number of rows to retrieve in limit when the number of rows is insufficient. If there are not enough rows (for example, given limit 10, 5, but only 13 rows), MySQL will only return as many rows as it can return.
The limit syntax for MySQL 5 is limit 3, does 4 mean 3 rows starting with line 4 or 3 lines starting with row 4? As mentioned earlier, it means 4 lines starting at line 3, which is easy to confuse people. For this reason, MySQL 5 supports another alternative syntax for limit. LIMIT4 OFFSET 3 means to take 4 lines from line 3, just like Limit 3, 4.

4.7 Using a fully qualified table name

The SQL example used so far only references columns by column names. You may also use a fully qualified name to refer to the column (using both the table name and the column word). Take a look at the following example:

This SQL statement is functionally equivalent to the one that was used at the beginning of this chapter, but a fully qualified column name is specified here. The table name can also be fully qualified, as follows:

This statement is functionally equivalent to the one that was just used (assuming, of course, that the Products table is indeed in the CrashCourse database). As described in later chapters, there are situations that require a fully qualified name. Now, you need to pay attention to this syntax so that you know what it does when you encounter it.

4.8 Summary

This chapter has learned how to use the SQL SELECT statement to retrieve a single table column, multiple table columns, and all table columns. The next chapter teaches you how to sort the retrieved data.

MySQL must know-4th chapter-Retrieving data