Retrieval Data of Database series (ii)

Last summary record what is a database, and in this section will write what is retrieving data

Focus: SELECT statement

The most frequently used statement is the SELECT statement

Its purpose is to retrieve information from one or more tables.

In order to retrieve table data using the SELECT statement, you must give at least two messages-what to choose and where to choose.

Focus One: Retrieving a single column

Input: SELECT prod_name from Products; Retrieving a column named Prod_name from a Products table

Output:

Prod_name

---------------

Fish Bean Bag Toy

Bird Bean Bag Toy

Rabbit Bean Bag Toy

8 inch teddy Bear

King Doll

Key two: Retrieving multiple columns

Still using the same SELECT statement, the only difference is that multiple column names must be given after the SELECT keyword

Input: SELECT prod_id,prod_name,prod_price from Products; As in the previous one, except that in this example, three column names are specified, and the column names are separated by commas

Output:

prod_id Prod_name Prod_price

--------                   --------------                          -------------

BNBG01 Fish Bean bag Toy 3.490000

BNBG02 Bird Bean bag Toy 3.490000

BNBG03 Rabbit Bean bag Toy 3.490000

Focus Three: Retrieving all the columns

In addition to specifying the required column names, the SELECT statement can retrieve all the columns and use the (*) wildcard character in the location of the actual column name to do this

Input: SELECT * FROM Products

Key four: Retrieving different values

How to retrieve return different values, then use the DISTINCT keyword, as the name implies, it indicates that the database only returns a different value

Input: SELECT DISTINCT vend_id from Products;

Output:

vend_id

---------

BNBG01

DLL01

FNG01

Note: Distinct cannot be partially used: this keyword is used for all columns, not just the column following it.

Focus Five: Limiting results

If you want to return only one row or a certain number of rows, the result is feasible, but the various databases are not used the same way

When you use select in SQL Server and access, you can use the top keyword to limit how many rows are returned

Input: SELECT TOP 5 prod_name from Products;

Output:

Prod_name

---------------

Fish Bean Bag Toy

Bird Bean Bag Toy

Rabbit Bean Bag Toy

8 inch teddy Bear

King Doll

Analysis: Use Top 5 statements to retrieve only the first 5 rows of data

If you use MySQL, you need to use the Limit keyword

Example: SELECT prod_name from the products LIMIT 5;

To get the data from the next 5 rows, you need to specify where to start and the number of rows to retrieve

Example: SELECT prod_name from the products LIMIT5 OFFSET 5;

Indicates that 5 rows of data are returned from line 5th

Key Six: Using annotations

Input: SELECT prod_name---This is a comment

from the products;

Use--(two hyphens) to nest within a row.

Retrieval Data of Database series (ii)