Shulou(Shulou.com)05/31 Report--

SQL query methods, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Simple query

A simple Transact-SQL query consists only of select lists, FROM clauses, and WHERE clauses. They describe the column to be queried, the table or view of the query, and the search criteria, respectively. For example, the following statement queries the nickname and email fields in the testtable table with the name "Zhang San".

SELECT nickname,email FROM testtable WHERE name=' Zhang San'

(I) selection list

The select_list refers to the queried column, which can be composed of a list of column names, asterisks, expressions, variables (including local and global variables), and so on.

1. Select all columns

For example, the following statement displays data for all columns in the testtable table:

SELECT * FROM testtable

2. Select some columns and specify their display order

The data in the query results collection is arranged in the same order as the column names specified in the selection list. For example:

SELECT nickname,email FROM testtable

3. Change the column header

In the selection list, you can reassign the column title. Define the format as: column title = column name column title if the specified column title is not in the standard identifier format, use quotation mark delimiters, for example, the following statement displays the column title in Chinese characters:

SELECT nickname = nickname, email = email FROM testtable

4. Delete duplicate lines

The ALL or DISTINCT option is used in the SELECT statement to display all eligible rows in the table or to delete duplicate rows. The default is ALL. When using the DISTINCT option, only one row is left in the result set returned by SELECT for all duplicate rows of data.

5. Limit the number of rows returned

Use the TOP n [PERCENT] option to limit the number of rows returned, TOP n indicates that n rows are returned, while TOP n PERCENT indicates that n represents a score of 100, specifying that the number of rows returned is equal to a few percent of the total number of rows. For example:

SELECT TOP 2 * FROM testtable SELECT TOP 20 PERCENT * FROM testtable

(II) FROM clause

The FROM clause specifies the SELECT statement query and the table or view associated with the query. Up to 256 tables or views can be specified in the FROM clause, separated by commas. When the FROM clause specifies multiple tables or views at the same time, if a column with the same name exists in the selection list, you should use the object name to qualify the table or view to which these columns belong. For example, if the cityid column exists in both the usertable and citytable tables, you should use the following statement format to query the cityid in both tables:

SELECT username,citytable.cityid FROM usertable,citytable WHERE usertable.cityid=citytable.cityid

Aliases for tables or views can be specified in the FROM clause in the following two formats: table name as alias table name alias (2) FROM clause

The FROM clause specifies the SELECT statement query and the table or view associated with the query. Up to 256 tables or views can be specified in the FROM clause, separated by commas. When the FROM clause specifies multiple tables or views at the same time, if a column with the same name exists in the selection list, you should use the object name to qualify the table or view to which these columns belong. For example, if the cityid column exists in both the usertable and citytable tables, you should use the following statement format to query the cityid in both tables:

SELECT username,citytable.cityid FROM usertable,citytable WHERE usertable.cityid=citytable.cityid

Aliases for tables or views can be specified in the FROM clause in the following two formats: table name as alias table name alias, for example, the alias format of the table available in the above statement is expressed as:

SELECT username,b.cityid FROM usertable a,citytable b WHERE a.cityid=b.cityid

Not only can SELECT retrieve data from tables or views, it can also query data from the set of results returned by other query statements.

For example:

SELECT a.au_fname+a.au_lname FROM authors a moment titleauthor ta (SELECT title_id,title FROM titles WHERE ytd_sales > 10000) AS t WHERE a.au_id=ta.au_id AND ta.title_id=t.title_id

In this case, the result set returned by SELECT is given an alias t, and then data is retrieved from it.

(3) use the WHERE clause to set query conditions

The WHERE clause sets the query condition to filter out unwanted rows of data. For example, the following statement queries data older than 20:

SELECT * FROM usertable WHERE age > 20

The WHERE clause can include various conditional operators: comparison operators (size comparison): >, > =, =, = 10 AND age, > =,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.