SQL serever Learning 9--Base query statement

SQL language Overview

SQL is a Structured Query language (Structure query Language), proposed in 1974, 1979 by IBM, the SQL language has become a relational database standard language.

Including:

1. DDL Data Definition Language
   1. Statements have create, ALTER, DROP, action table, view, trigger, stored procedure
2. DML Data Manipulation language
   1. Statements have SELECT, INSERT, UPDATE, DELETE, for retrieving and manipulating data
3. DCL Data Control Language
   1. Statements have grant, DENY, REVOKE, only sysadmin, database creator, owner, security administrator has the right to execute, to set or change database user or role permissions
4. Process Control
   1. Common statements have begin ... END, IF ... ELSE, while, break, GOTO, WAITFOR, return, etc.

logical operators

• and
• OR
• Not
• All, which is true for all expressions
• Any, true if one of the expressions is true
• Between, true within a range
• EXISTS
• In, the operand is true for one of the expression lists.

Statement basic format

SELECT * FROM table name where Condition Group By field having expression order by field asc| DESC

　　

Description: After a GROUP BY clause, you can use the having phrase to filter after grouping, and having to follow the ORDER BY clause.

By default, the title of the query structure table can be either a table field name or untitled, and you can use as to modify the field header

Use Sales management goselect product name, model, sales price-price as spread, inventory from product list go

Use a query to generate a new table, or a temporary table

Use Sales management goselect product name, model, sales price-Price as difference, inventory into commodity table schedule from product list go

Results

Use of temporary tables

Temporary tables are valid during this server connection, and once the server is disconnected, the temporary table fails and is deleted.

Use Sales management goselect product name, model, sales price-price as spread, stock into #临时商品表 from product list go

Quickly generate a data table structure (blank OH)

Because where 1=2 is not valid, it does not retrieve the data that matches the condition, and generates a blank table with no data.

Use Sales management goselect product name, model, sales price-price as spread, stock into #商品表副本 from commodity table WHERE 1=2goselect * from #商品表副本

SQL Rollup Query aggregation function

There are 6 commonly used aggregate functions:

1. Count (*), counts all records
2. count[DISTINCT] field, number of values in the statistics field
3. Sum field, summation of the specified field (numeric type)
4. Avg field, averaging for the specified field (numeric type)
5. Max field, ask for a field maximum value
6. Min field, ask for a field minimum value

Group query statements

Sometimes the total amount of sales for each commodity is counted, and the sales amount in the sales table needs to be summarized and then manipulated, which is the group query.

The GROUP by clause is implemented,

Filter criteria expression after group By field has a group

Note: By fields are grouped by the specified fields, records with the same field values are placed in a group, and each group is summarized with only one piece of data.

The having filter is the filtering of the results after grouping, not the original table.

The list of fields after the SELECT clause must be an aggregate function or a field in the GROUP BY clause.

SQL serever Learning 9--Base query statement