SQL Getting Started Classic (ii) Database Basic query, add, update and delete

Use SQL query:

SQL query Basic syntax:

SELECT [all| DISTINCT] [TOP (<expression>) [PERCENT] [with TIES]] <column list> [from <source table (s)/view>] [wher E <restrictive Condition>]

[Groud by<column name or expression using a column in the SELECT List>]

[Have <restrictive condition based on the GROUP by Results>]

[ORDER by <column List>]

[For XML {row| auto| Explicit| path[(<element>)]},xmldata][,elements][,binary Base 64]]

[OPTION (<query hint>,[,..... n])]

The SQL statement above looks very complex. Now we are going to explain.

SELECT tells SQL what to do. From the table to be manipulated (note: * cannot be used as a field in a query.) How many fields are queried, and how many field names are written?

SELECT *  from --I'm going to check this table in Person.Contact .

WHERE Query Condition Filtering

SELECT *  from WHERE  like ' M% ' -- I'm going to look under the Person.Contact table for a contact that's FirstName start with M

The following operators are available in the WHERE clause:

The GROUP by clause aggregates data functions:

As a boss, you want to know the sales and the total price of each item. You need to use aggregate functions

 select  salesorderid,sum  ( UnitPrice) as  totalprice from  Sales.SalesOrderDetail group  by  Salesorderid--  aggregate function  select  salesorderid,sum  (UnitPrice) as  totalprice from  sales.salesorderdetail  Group  by  SalesOrderID having  Span style= "color: #ff00ff;" >sum  (UnitPrice) >  10000  

Note: Filter out products with sales less than 10000. I don't know why totalprice cannot be filtered as an aggregate function. can only be filtered with sum (UnitPrice). You can use the where filter before the aggregation function. The Where filter cannot use aggregate functions. Easy error point: Use the where filter after group by. GROUP by can only be filtered with having. Having the aggregate functions such as filtering is complete before filtering.

The aggregate function has: count () sums the sum () sum AVG () averages min () min Max () max () These are more commonly used aggregate functions that can be used alone or with group by.

Now introduce the count () aggregate function: see 2 codes and results first

Select Count (1from-- result 19,972 data Selectcount  from -- 11,473 piece of data

The above 2 data results are different, this need not be careful. The following blog will say this question (it is recommended not to take the column name as the Count aggregate function)

Order by Sort query:

Boss asked to see the product sales Top 10: Here also put top this keyword introduced. Order BY that field Desc is sorted in descending order by that field, ASC ascending row. Top to take the number of previous data

SELECT TOP Ten   SalesOrderID,sumas fromGroupby hassum  >10000ORDERby  DESC--  Sort Top 10 data by sales from high to bottom

Predicates for DISTINCT and all: If the database has 100 identical data. All loads the same data 100 times. Distinct loads only one piece of data. The remaining 99 are discarded.

The FOR XML is not introduced here. There is a special blog to introduce this query later.

INSERT Add Data

Basic syntax: INSERT [TOP (<expression>) [PERCENT] [into] <table object>[(<column listt>)][output <output Clause>] {VALUES (<data value>) [, (DataValues)] [,.... n]|<table source>| EXEC <procedure>| DEFAULT VALUES}

The above syntax is not as complex as it seems, simplifying syntax: INSERT into<tables object>[(<column list>)] VALUES (<data value>) [, (DataValues) [,.... N]

[Write here temporarily, I want to learn English]

SQL Getting Started Classic (ii) Database Basic query, add, update and delete