SQL Server: Reading notes from the book of "The Must-know-all" (ii)

2nd lesson: Retrieving data

2.1 SELECT Statement
　　Purpose : Retrieving data information from one or more tables
　　 Keywords: reserved words as part of SQL. The keyword cannot be used as the name of a table or column.
To retrieve table data using Select, you must give at least two messages:
What to choose,
Choose from somewhere.

2.2 Retrieving a single column

SELECT from Products ; --     Analysis: Use the SELECT statement to retrieve a column named Prod_name from the Products table.  -- the     required column name is written after the SELECT keyword, and the FROM keyword expense retrieves data from that table.

"Prompt" to use ";" And splitting a statement into multiple lines can enhance readability
The prompt SQL statement is case-insensitive, that is, "select" and "select", but table names, column names, and values may vary.

2.3 Retrieving multiple columns
Retrieve multiple columns from one table, and still use the same SELECT statement. The only difference is that more than one column name must be given after the SELECT keyword, and the column names are separated by commas

SELECT prod_id, Prod_name, Prod_price  from Products ; --     Analysis: Select 3 columns of data from table products using the SELECT statement

2.4 Retrieving all Columns
In addition to specifying the columns you want, the SELECT statement can retrieve all the columns without having to list them individually. Use the asterisk (*) wildcard character at the location of the actual column name to do

SELECT *  from  Products --     Parse: If given a wildcard character (*), all columns in the table are returned. The order of the columns is typically the physical order in which the columns appear in the table definition. 

"Note" It is best not to use the * wildcard character unless you do need each column in the table. Retrieving unwanted columns typically reduces the performance of the retrieval and the application.

2.5 Retrieving different values

The SELECT statement returns all matching rows, but we don't want each value to appear every time, what should I do?
If you want to retrieve the IDs of all the product suppliers in the Products table

SELECT vend_id  from Products

　　

Found duplicate value, how to solve it? method is to use the DISTINCT keyword

SELECT DISTINCT vend_id  from  Products --     Analysis: SELECT DISTINCT vend_id tells the DBMS to return only (unique) vend_id rows. If you use the DISTINCT keyword, you must precede the column name directly. 

　　

Note The DISTINCT keyword is used for all columns, except that the two columns are exactly the same, and all rows are retrieved

2.6 Limiting results
The SELECT statement returns all matching rows in the specified table. What if we just want to return to the first row or a certain number of rows?
--Using the TOP keyword

SELECT TOP 5 Prod_name  from  Products --     Analysis: Use the SELECT Top 5 statement to retrieve only the first 5 rows of data

2.7 Using annotations
In-line comments: Use "--"

SELECT *    -- in-line comments  from Products;

Multiline Comment: Use "/* */"

/* Multi-line Comment SELECT *    from the products; */

SQL Server: Reading notes from the book of "The Must-know-all" (ii)