About the SQL collation used

SQL Server:

Defining variables

DECLARE @num int

Set @num =1

Select @num = count (*) from table

Define a parameter @num to type int

Two ways to assign a value to a variable

There is no for loop but there are while and if

while (@num <5)
Begin

Set @[email protected]+1
End

< tips > Field names do not appear as ' PM2.5 ' instead of ' PM25 '

If @Pollutant_Name = ' PM2.5 '
Begin
Set @Pollutant_Name = ' PM25 '
End

About the default three-way connection

Table1

INNER JOIN table2 on Table1.id=table2.id

It is

From Table1,table2 where table1.id=table2.id

Left join is to find out all the values of the left table, NULL also appear other similar

Print @sql statement

PRINT @SQL

Execute @sql statement
EXEC (@SQL)

About querying data row columns

Select A.positionname,c.timepoint,
Max (case is pollutant_name= ' SO2 ' then Forecastvalue end) as SO2,
Max (case is pollutant_name= ' NO2 ' then Forecastvalue end) as NO2,
Max (case is pollutant_name= ' PM10 ' then Forecastvalue end) as PM10,
Max (case is Pollutant_name= ' co ' then Forecastvalue end) as CO,
Max (case is pollutant_name= ' O3 ' then Forecastvalue end) as O3,
Max (case when Pollutant_name= ' PM2.5 "then Forecastvalue end) as PM25

From Ec_station A
INNER JOIN ec_aqi_day_forecast_station_1 b on a.stationid = B.stationid
INNER JOIN ec_aqi_hour_forecast_station_1 c on b.id = c.day_forecast_id
INNER JOIN Ec_pollutant_info D on c.pollutant_id = d.pollutant_id
where [email protected]_forecast_id and c.timepoint> (24* @Index_Day -24) and c.timepoint<= (24* @Index_ Day)
GROUP BY A.positionname,c.timepoint

Form 1:

Case field Name 1 where ' the value of field name 1 that you want to query ' then field Name 2 end

Form 2:

Case when field name 1 = ' Value of field name 1 ' that you want to query ' then field Name 2 end

Because the above example uses the group by all requires the Max aggregation function, because there is only one value, so Max does not play the area maximum effect

Exit and in

Bottom line: Functionally similar, if two tables in a small, one is a large table, then the subquery table large with exists, sub-query table smallwith in:

difference between in and =
Select name from student where name in (' Zhang ', ' Wang ', ' Li ', ' Zhao ');
And
Select name from student where Name= ' Zhang ' or name= ' li ' or name= ' Wang ' or name= ' Zhao '
The result is the same.

Not-in and not-exists if the query statement uses not-in to perform a full-table scan of the outer surface, the index is not used, and the index on the table is still used by not Extsts's subquery. So no matter the table is large, using not exists is faster than not.

Note values that contain null and NULL comparisons all get NULL

correlated subqueries and nested subqueries

Class number Book name publishing house price
--------------------------------------------------------
2 C # advanced app San Tong Publishing 23.00
2 JSP Development and application Machinery publishing house 45.00
3 Advanced Mathematics Jinan Publishing house 25.00
3 Crazy English Tsinghua University Press 32.00

Nested subqueries SELECT *
From Readers
WHERE Reader number in
(
SELECT Reader Number
from [borrow]
)
GO
Correlated subquery: Equivalent to dynamic table Aselect book name, publishing house, class number, price
From Books as a
WHERE Price >
(
SELECT AVG (Price)
From Books as B
WHERE A. Class number =b. Class number
)
GO
EXCEPT, Intersect usage

Null	Null
Null	2
1	3
1	4
2	5
3	5
4
5

A: (SELECT * from TableA) EXCEPT (SELECT * from TableB)

Results: 1

b

SELECT * FROM TableA INTERSECT select * from TableB

Results: 2

3

4

5

Make a wake up: CTE, recursive query, merge, should be a separate column

http://blog.csdn.net/zhs954838550/article/details/8159417

Local temporary table CREATE TABLE #tbName (column information);table name prefix # Valid only in the current session and cannot be accessed across connectionsScope scopes are similar to C # : if created directly in a connection session, the current connection is dropped and deleted after the current stored procedure is executed if it was created in a stored procedureGlobal temp Table CREATE TABLE # #tbName (column information), table name prefix # #多个会话可共享全局临时表当创建全局临时表的会话断开, and no user is deleting a table variable when a global temporary table is being accessed: Declare @varT1 table (col1 int , col2 char (2));//Store a smaller amount of data, with more restrictions than temporary tables. Temporary data is stored in tempdb, and when the service restarts, tempdb is rebuilt. Temporary table application: Temporary tables can be used to reduce memory consumption in order to improve execution efficiency while making complex subqueries on large data volumes. View Viewis a piece Virtual Tables, which represents a table of partial data or multiple tables of comprehensive data, the structure and data is based on the query of the table on the basis of ViewOn the operational and Data SheetThere is no difference, but the difference between the two is its Nature is different: The data table is where the record is actually stored, but the view does not save any records. The same Data Sheet, depending on the different needs of different users, you can create different views (different query statements) view is easy to query, it is generally not possible to make the views on the advantages of pruning: Filter rows in a table \ Reduce database complexity to prevent unauthorized users from accessing sensitive data

About derived tables:

Find a customer who ordered a mini pump and ordered a baseball cap with AWC logo (SQL SERVER2008 advanced design P40)

Because you can't where p.name=a and p.name=b

Instance:

SELECT DISTINCT pp. Firstname,pp. LastName

From Person.person as pp

Join (select SC. PersonID from Sales.Customer SC

Join Sales.SalesOrderHeader as Soh

On SC. Customerid=soh. CustomerID

Join Sales.SalesOrderDetail as Sod

Onsoh. Salesorderid=sod. SalesOrderID

Join Production.Product as P

On Sod. Productid=p.productid

where p.name= ' minipump ') pumps

On pp. Businessentityid=pumps. PersonID

Join (select SC. PersonID from Sales.Customer SC

Join Sales.SalesOrderHeader as Soh

On SC. Customerid=soh. CustomerID

Join Sales.SalesOrderDetail as Sod

Onsoh. Salesorderid=sod. SalesOrderID

Join Production.Product as P

On Sod. Productid=p.productid

where P.name= ' AWC Logo Cap ') caps

On pp. Businessentityid=caps. PersonID

About the SQL collation used