Objective: To learn how to use a SELECT statement to retrieve one or more data columns from a table. Retrieve a single column: Select prod_namefrom products; Case: SQL statements are case insensitive. However, many SQL developers prefer to use uppercase letters for all SQL keywords and lowercase letters for all columns and table names. This makes the code easier to read and debug. Space: when processing SQL statements, all spaces are ignored. SQL statements can be given on one line or divided into many lines, most SQL developers think that it is easier to read and debug SQL statements by dividing them into multiple lines. Retrieve multiple columns: Select prod_id, prod_name, prod_pricefrom products; Retrieve all columns: Select * from products; Use wildcard (*) to return all columns in the table. The general sequence of columns is the sequence in which columns appear in Table definitions. But sometimes this is not the case. Changes in the table mode (such as adding or deleting columns) may lead to changes in the sequence. Generally, unless you really need each column in the table, it is best not to use *, because columns that do not need to be retrieved will reduce the performance of retrieval and applications. Another function is to retrieve unknown columns (columns without names) to retrieve different rows. Use the distinct Keyword: Select distinct vend_idfrom products; to limit the results, and use the keyword limit, to return the first row or the first few rows of the Table: Select prod_namefrom products; limit 5; limit5, 5; # indicates that 5 rows starting from row 5th are returned. The first number is the start position, and the second number is the number of rows to be retrieved. If the row is 0, the first row is 0 instead of 1. Therefore, limit 1 and 1 retrieve the second row instead of the first row. When the number of rows is insufficient, MySQL returns only the rows that can be returned. For example, there are only 13 rows, and the limit value is 10 or 5. MySQL 5 limit Syntax: limit 4 offset 3 = Limit 3, 4 from the third row to take 4 rows. Use the fully qualified table name: select products. prod_namefrom mysql_bizhibihui.products;
Chapter 4 Data Retrieval