Shulou(Shulou.com)06/01 Report--

Basic format of query (select):

Select from [where]

Explanation:

The command body of the select query

Column name: the column to be queried, with multiple columns with commas and separated asterisks (*) to represent all columns

Where: optional. The result of an empty query is the data of the entire column.

Example:

Select score from report form where name = 'Zhang San'

Meaning: query the score of Zhang San in the transcript, and only show his score. If you do not add where, all the values of the score column in the score table will be displayed.

In SQL server, the syntax of the select statement:

Select [into New Table name] from

[where query condition] [group by grouping condition] [having query condition] [order by sorting condition [asc | desc]]

Explanation:

Into: optional parameter to save the query contents to a new table without creating a new table in the database.

The query condition field of where is made up of conditional or logical expressions.

Group by: optional parameter that specifies the grouping criteria for query results, usually column names, but not aliases.

Having: optional parameter that specifies grouping search criteria, usually used with group by sentences.

Order by: an optional parameter that specifies how the query results are sorted. The sort condition asc represents ascending order, desc represents descending order, and the default sort is asc ascending order.

Examples of various queries:

1. Query all columns in the score sheet.

Select * from score sheet

two。 Query the name and the content of the score column in the transcript.

Select name, score from report form

3. Query the score of Zhang San in the transcript, and the result shows the name and score.

Select name, grade from report form where name = 'Zhang San'

4. Query the scores of all personnel with scores between 90 and 100 in the transcript.

Select * from transcript where score between 90 and 100

5. Query all the information in the score table with a score of 80,90,90,95.

Select * from report form where score in (80 grade 90 no 95)

6. Query all the information of the student surnamed Zhang in the score sheet.

Select * from report form where name like 'Zhang%'

7. Query all the information of Zhang San in Class 1 in the score sheet.

Select * from report form where name = 'Zhang San' and class = 1

8. Query all the information about people whose comments are not empty in the score sheet.

Select * from transcript where remarks is not null

9. Use the top keyword to limit the number of rows returned by the query and query the first 10 rows of data in the score table.

Select top 10 * from score sheet

10. Change the name of the query result: query the name of the two columns of data in the score table and display the name of the data column as name,grade in English.

Select name as name, grade as grade from report form

11. Query the total scores of all people in the score table, and the column name is displayed as' total score'.

Select sum (grade) as total score from score table

Note: sum is a summation function. As can also be omitted and can be written as select total score = sum (grade) from score table.

twelve。 Query all the information in the score sheet and display the results according to the results from high to low.

Select * from report form order by score desc

13. Query the GPA of all the people in the transcript.

Select avg (grade) as grade point average (GPA) from chart

Note: avg is an average function.

14. Query the highest score (max) and the lowest score (min) in the score table

Select max (grade) as highest score, min (grade) as lowest score from score table

15. Query the number of all rows in the score sheet.

Select count (*) total number of rows from score table

Note: count is a statistical function that returns the count of non-null values in an expression. Use * to count all rows without specifying a specific column.

16. Query the total score of each class in the score sheet.

Select class, sum (grade) as total score from grade sheet group by class

17. Inquire about the classes with a total score of more than 200 in the score table.

Select class, sum (grade) as total score from grade table group by class having sum (grade) > 200

Note: functions cannot be followed by where. Having can add functions.

18. Insert all the data in the score sheet into the new table "copy of the score sheet"

Copy of select * into transcript from transcript

19. Insert the results of Class 1 in the score sheet into the new table "Class one score sheet".

Select * into Class 1 report form from report form where Class = 1

Note: the new table inserted cannot exist in the database before.

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.