Basic SELECT statement (i)--study notes

The query operation of the database can be realized by using the SELECT statement, and it can also use various clauses to group statistics, sum up, sort the results of the query. The SELECT statement can also generate another table (a temporary table or a permanent table) for the query results.

The syntax format for the SELECT statement:

SELECT [all| DISTINCT]select_list[into [New_table_name]][From {table_name,table_name2,... | View_name,view_name2,...}][WHERE clause][GROUP BY clause (clause)][Having clause (clause)][ORDER BY clause (clause)][COMPUTE clause (clause)][For BROWSE]

In use, clauses can be omitted, but are listed in the order above.

1. Simple query

A simple query consists of a select list, a FROM clause, and a WHERE clause that indicate the column being queried, the Query object (table or view), the search condition, and so on.

For example: Query the Tongfuhotel table for men younger than 30 years of age

1 SELECT Tongfuhotel.name,tongfuhotel.job 2  from Tongfuhotel 3 WHERE Tongfuhotel.age< tongfuhotel.sex=' male ' 

(1) SELECT list statement

The SELECT list statement (select_list) specifies the selected column, which can consist of a list of column names, asterisks, expressions, variables, and so on. When represented by an asterisk, selects all columns in the specified table or view.

SELECT *  from Tongfuhotel

When using a SELECT list statement, multiple specified column names are separated by commas. The select list can also perform arithmetic operations on numeric columns (including add, subtract, multiply, divide, modulo, and so on). where addition, subtraction, multiplication, and other operations apply to any numeric type (such as int, smallint, tinyint, decimal, numeric, float, money, smallmoney, etc.), The modulo operation cannot be used for money and smallmoney data types. In addition, you can specify string constants or variables to change their output.

Using all| in a SELECT statement The DISTINCT option displays all rows (all) or deletes duplicate rows (DISTINCT), which by default is all. When you use the DISTINCT option, the value of the select list is displayed only once for all data duplicates.

SELECT DISTINCT  from Tongfuhotel

(2) FROM clause

The FROM clause specifies that the SELECT statement queries the related table or view. You can specify up to 16 tables or views, separated from each other by a comma. If these tables or views belong to a different database, you can specify a table or view by using the database. Owner name. Object Format.

You can specify an alias for each table or view in the FROM clause, immediately following the object name, separated by spaces, allowing aliases to be used to refer to columns in the table.

1 SELECT Name,job 2  from Tongfuhotel T 3 WHERE t.age< t.sex=' male '

(3) WHERE clause limits search conditions

①where statements can contain conditional operators

A, Comparison operators >, <, >=, <=, =, <>,!>,!< (size comparison)

b, range operator Bwtween ... And, not between ... and (determines whether the expression value is within the specified range)

C, list operator in, not in (determines whether the expression value is a specified item in the list)

D, pattern match like, not as (determine if the wildcard match the specified character format)

E, NULL judge is NULL, not is null (determines whether the expression value is empty)

F, logical operators and, or, not (for multi-conditional logical connections)

②where statements can contain pattern-matching characters

A,%: can match any type of length of characters

B, _: matches a single arbitrary character, often used to limit the character length of an expression

C, []: Specifies a character, string, or range that requires matching objects to be any of their characters

D, [^]: takes the same value as [], but requires that the matched object be any character other than the specified character

Reference: Proficient in C # database programming. Wang Huajie, Huangshan--Beijing: Science Press, 2003.10

Basic SELECT statement (i)--study notes