Beginner SQL statements

Introduction to Beginners SQL statements

1. Retrieve records with SELECT clause

The Select clause is the core of each query that retrieves the data. It tells the database engine what fields to return.

The common form of the SELECT clause is:

Select *

This clause means "return all fields that can be found in the specified record source." This form of command is convenient because you do not need to know the name of the field retrieved from the table. However, retrieving all the columns in the table is inefficient. Therefore, you can greatly improve the efficiency of your query by retrieving only the fields that you need.

2. Use the FROM clause to specify the record source

The FROM clause describes the record source for a query to retrieve records, which can be a table or another store query.

You can also retrieve records from multiple tables, which are described in later chapters.

Example:

Select * FROM students retrieves all records in the students table

3. Describe the condition with the Where clause

The Where clause tells the database engine to qualify the records that it retrieves based on one or more of the conditions provided. Condition is an expression, can have true and false two kinds of judgment.

Example:

SELECT * FROM students Where name= "shadow"

Returns a list of Shadows for the name field in students, which returns no specific order unless you use an ordered by clause. The clause is described in later chapters.

Note: The text string bounds in the Where clause are double quotes, which are changed to single quotes in VB because in VB the strings are delimited by double quotes.

Add:

Using and and or logic can link two or more conditions together to create a more advanced Where clause.

Example:

SELECT * FROM students Where name= "shadow" and number>100

Returns the list with name Shadow number greater than 100.

Example:

Select * FROM students Where name= "Shadow" and (number>100 Or number<50)

Returns a list with Name of shadow, number greater than 100, or less than 50.

Operator used in the Where clause

operator function

< less than

<= less than or equal to

> Greater than

>= is greater than or equal to

= equals

<> Not equal to

Between within a range of values

Like to match a pattern

In contained in a list of values

The equal and not equal operator in SQL is the same as the meaning and use in VB

Example:

(1). Between operator

Use Cust

Select * FROM students

Where number Between 1 and 100

The Between operator returns all the record values located within the indicated bounds. This example returns all records from the Number field 1 through 100.

(2). Like operators and wildcard characters

Use Cust

Select * FROM students

Where name like "% Shadow%"

The LIKE operator matches the record to a pattern that you describe. This example returns any string containing the shadow.

The meaning of the four wildcard characters

Wildcard description

% represents 0 or more arbitrary characters

_ (underline) represents an arbitrary character

[] Any single character within the specified range

[^] Any single character not in the specified range

All examples are as follows:

Like "br%" returns any string starting with "BR"

Like "br%" returns any string starting with "BR"

Like "%een" returns any string that ends with "een"

Like "%en%" returns any string containing "en"

Like "_en" returns three strings ending with "en"

Like "[ck]%" returns any string starting with "C" or "K"

Like "[s-v]ing" returns a four-character string ending with "ing", beginning with S to V.

Like "m[^c]%" returns any string that starts with "M" and the second character is not "C".

4. Sort results by using order BY

The ORDER BY clause tells the database engine to sort the records it retrieves. You can sort any field, or sort on more than one field, and you can sort in ascending or long order.

After a formal Select query, include an ORDER BY clause followed by a field that you want to sort (can have more than one) to describe a sort sequence.

Example:

Use Cust

Select * FROM students

Where name like "% Shadow%"

ORDER BY number

The returned results are sorted by number.

Sort in descending order

To sort by a long order, simply use the DESC keyword after the sorted field.

Example:

Use Cust

Select * FROM students

Where name like "% Shadow%"

ORDER BY number Desc

5. Use top to display the first or last record of a range.

Use the top keyword to display only a few records before or after a large record. In a query, the top keyword, together with a sort clause, restricts the result set to a few records or a percentage of the entire result record collection.

Example:

Select Top 3 * from students returns the first 3 records in the students table

Select Top Percent * Students returns the previous 10% records in students table

Select Top 3 * from students ORDER BY number DESC returns the largest (last) 3 records in the students table

6. Alias the field name with AS

Why do you alias, or rename, a field in a query for two reasons:

☆ The field name of the table involved is very long and you want to make the field more manageable in the result set.

☆ Create a query that produces some calculation or total columns that need to be named.

Whatever the reason for the alias of the field, you can easily use the AS clause in SQL.

Example:

Select number as, name as name from students

7. Consolidated Query

Merge queries (Union query) are used to merge the contents of two tables that have the same field structure, which is useful if you want to display unrelated records from multiple record sources in one result set.

Example:

Select *

From students

Union

Select *

From Students1

The query result set merges the records in students and students1 into one result, and the output is exactly the same as before the original table was archived.

Note: By default, a merge query does not return duplicate records (this is useful if the record-filing system does not delete the records after they are copied to the archive), and you can add the all keyword to the merge query to display duplicate records.

Example:

Select *

From students

Union All

Select *

From Students1

When the merge query displays the contents of the students table and the Students1 table, no duplicate records are processed

Add:

The Union operator allows two or more query results to be merged into one query result set. If the union and join two operators are compared, the union operator increases the number of rows, and the join operator increases the number of columns. When using Union, it should be noted that the structure of the columns in the two results must match, the data types must be compatible, and so on.

The syntax form of the Union operator is as follows:

Select select_list

FROM clause

Where clause

Group BY clause

HAVING clause

Union [All]

Select select_list

FROM clause

Where clause

Group BY clause

HAVING clause

ORDER BY clause

Compute clause

For the Union operator, you have the following points to note:

• By default, the Union operator deletes all redundant rows. If you use the all option, the redundant rows are not deleted.

• All select_list in a Union statement must have the same number of columns, compatible data types, and appear in the same order.

• In the result set, the column name comes from the first Select statement.

8. Connection Query

It is often necessary to retrieve data from two tables or more than two tables in the process of actual use. A connection is a permission to retrieve data from two tables or more than two tables at the same time, specifying one or more of these tables as a join condition. In SQL Server, you can use two types of connection syntax, one in the form of Ansi connection syntax, where the connection is used in the FROM clause and the other is the form of a SQL Server connection statement, which is used in the WHERE clause.

The Ansi connection syntax forms the following:

Select Table_name.column_name,table_name.column_name,...

from {table_name [JOIN_TYPE] join table_name on search_conditions}

Where [search_conditions]

In the ANSI syntax form, you can use the ANSI connection keyword to determine the form of connection used. For example:

☆ using the Inner Join keyword, the result set contains only the rows that meet the criteria.

☆ using the Cross Join keyword, the result set contains a combination of all the rows in two tables.

☆ using the Outer Join keyword, the result set contains both the rows that meet the criteria and the entire rows of one of those tables.

The SQL Server connection syntax forms the following:

Select Table_name.column_name,table_name.column_name,...

From [Table_name,tab