SQL Server Common Advanced Grammar Notes

Since the use of EF rarely write SQL and stored procedures, today need to write a more complex report, turned out the previous notes as a reference, feel this note is still very useful, so sent to share with the park friends.

1, Case...end (the specific value)
The case is followed by a value, equivalent to the switch case in C #
Note: You must have a condition after a case, and when after must be a value cannot be a condition.

-----------------case--end---syntax structure---------------------select name,--note the comma case level   --case followed by condition when       1 Then ' Ashes ' when       2 Then ' prawns ' when       3 Then ' rookie '     end as ' from [user]

2, Case...end (range)
There is no value behind case, equivalent to If...else if...else in C # ....
Note: No root condition after case

------------------Case---End--------------------------------select StudentID       between 80 And then  ' excellent ' when       中文版 between and then ' good  '       Else '    end from score------------------case ---End--------------------------------Select StudentID, case when       中文版 >=80 then  ' excellent '       when 中文版 >=60 then  ' good '       Else ' bad '    end from Score-----------------------------------------------------SELECT *, case when         english>=60 and math >=60 Then ' pass '         Else ' fail '   

3, If...eles

IF (conditional expression)  BEGIN--equivalent to {    statement 1  in C # ... END--equivalent to the}else BEGIN statement in C #    1 ...  end--calculates the average score and outputs, if the average score exceeds the score of the three students with the highest scores in the output, the three students declare @avg INT--Define variable Select @avg = AVG (中文版) from score  -- Assign a value to a variable select ' Average score ' +convert (varchar, @avg)--Prints the value of the variable    if @avg <60           begin               Select ' Top three '              select Top 3 * From score order BY 中文版 Desc          end    Else         begin             select ' After three '             select Top 3 * from score ORDER by E Nglish           End

4. While loop

while (conditional expression)  BEGIN--equivalent to the {    statement    in C # ...  break END-equivalent to}--in C # if the number of people who fail to pass is more than half (the exam problem is difficult), then give everyone a select * from Scoredeclare @conut int, @failcount int,@i int=0  --Define variable Select @conut =count (*) from score--Total number of statistics select @failcount =count (*) from score where english<100--the person who failed to count Number while (@failcount > @conut/2)   begin       Update score set english=english+1       select @failcount =count (*) from Score where english<100       set @[email protected]+1   endselect @iupdate score set english=100 where 中文版 >1 00

5. Index
Using an index can improve query efficiency, but the index also occupies space, and when adding, updating, deleting data, you also need to update the index synchronously, thus reducing the speed of INSERT, UPDATE, delete. Indexes are created only on fields that are frequently retrieved (where).
1) Clustered index: the corresponding data in the index directory and in the directory is sequential.
2) Nonclustered indexes: The index directory is sequential but the stored data is not sequential.

--Creating a nonclustered index create nonclustered index [Ix_student_sno] on Student (    [SNo] ASC)

6, sub-query
A query statement is used as a result set for other SQL statements, just as a query statement that is treated as a result set is called a subquery, as with a normal table. Almost all places where you can use a table can use subqueries instead.

SELECT * FROM (SELECT * from student where sage<30) as T  --the child table being queried must have an alias where t.ssex = ' male '--filter columns in the child table

Convert to two decimal places: convert (numeric (10,2), AVG (中文版))
Only subqueries that return and return only one row or column of data can be considered as single-valued subqueries.

Select ' Average ', (select AVG (中文版) from score)--can successfully execute select ' name ', (select SName from student)-error because ' name ' has only one row, and the name in the child table has multiple lines SELECT * FROM student where Sclassid in (the Select CID from class where cName in (' High class one by one ', ' High 21 classes ')--when a subquery has multiple values, use in

7. Paging

--page 1select Top 3 * from student where [Sid] not in (select Top (4-1) [SID] from Student)--4 represents the number of pages select *, Row_number () Over (order by [Sage] desc) from student--row_number () through (order by.) Get line number--paging 2select * FROM  (SELECT *, Row_number () over (order by [SID] Desc) as num from    student) as Twhere num betwee N (Y-1) *t+1 and Y*torder by [SID] Desc

--Pagination 3
SELECT * FROM (select Row_number () over (order BY [UnitPrice] ASC) as num,* from [Books] where [publisherid]=1) as T where T.num between 1  and--number of starting and ending bars to query

8. Connection

Select Sname,sage, case when      中文版 <60 Then ' failed ' when      中文版 is null then ' missing '       else CONVERT (nvarch AR, 中文版)   end as ' English score ' from student as Sleft join score as C in S.sid =c.sid inner connection  inner        join...on ... The query satisfies the on-behind condition of the data    outside the connection            left            join...on ...            First find out all the data in the left table            and then use the conditions on the back of the data filter        right to connect to            Join...on ...            First find out all the data in the right table            and then use the conditions on the back of the data filter        full            join ... on ...    (*) Cross-connect crosses         join no            conditions on each row of the first table and on each row of the subsequent table            . Is the basis of other connections

9. View
Advantages:

• Filter rows in a table
• Prevent unauthorized users from accessing sensitive data
• Reduce the complexity of your database

Create a View

Create View V_demoasselect ...

10. Local Variables

---------------------------------a local variable----------------------------declare a variable: Using the Declare keyword, and the variable name is at the beginning of the @, the @ directly connects the variable name with no spaces in the middle. You must indicate the type of the variable, and you can declare multiple variables of different types. Declare @name nvarchar, @age int--variable assignment:--1, assigning a variable with set, assigning only one variable to a set @age =18set @name = ' Tianjia ' select @age @name  

11. Global Variables

--------------------------global variable (System variable)----------------------------------SELECT * from student0 Select  @ @error-- Error number of the last T-SQL error SELECT @ @max_connections--Gets the maximum number of simultaneous connections created SELECT @ @identity--Returns the number of the most recently inserted

12. Business
Transaction: Die
A Program execution unit (unit) that accesses and potentially updates various data items in a database-that is, consists of multiple SQL statements that must be executed as a whole
These SQL statements are submitted to the system as a whole, either executed or not executed

Syntax steps:

• Start transaction: Begin TRANSACTION
• Transaction commit: Commit TRANSACTION
• Transaction rollback: ROLLBACK TRANSACTION

To determine if a statement execution is error:
global variable @ @ERROR;
@ @ERROR can only judge whether the execution of the current T-SQL statement is wrong, in order to determine whether all T-SQL statements in the transaction are wrong, we need to accumulate the error;

---------------------------Analog transfer----------------------------declare @sumError int=0--declaring variable begin TranUpdate Bank set balance=balance-1000 where cid= ' 0001 ' set @[email protected][email protected] @error update Bank set balance=balance+1000 where cid= ' 0002 ' set @[email protected][email protected] @errorif (@sumError =0) Commit Tran--commit successfully, COMMIT transaction else rollback TRAN- -Commit failed, rollback transaction

13. Stored Procedures

Stored procedure---Like running a method (function) in a database
As in C #, a stored procedure name/stored procedure parameter/can have a return result.
Previously learned if else/while/variable/insert/select, etc., can be used in stored procedures
Advantages:

• Faster execution-Stored procedure statements stored in the database are compiled
• Allow modular programming-reuse of similar methods
• Improve system security-prevent SQL injection
• Reduce network liquidity-as long as the name of the transfer stored procedure

System stored Procedures
Defined by the system and stored in the master database
The name begins with "sp_" or "xp_"

To create a stored procedure:

 define stored procedure syntax CREATE proc[edure] Stored procedure name @ Parameter 1 data type = default value output, @ parameter n data type = default output as SQL statement parameter description: Parameter optional parameters are divided into input parameters, output parameter input parameters allow default value of Exec procedure name [parameter]----------------------example--------------------------if exists (SELECT * FROM sys . Objects where name= ' Usp_groupmainlist1 ') drop proc usp_groupmainlist1gocreate proc usp_groupmainlist1 @pageIndex Int,- -Number of pages @pageSize int,--@pageCount int output--output Total number of pages as declare @count INT--How many data select @count =count (*) from [Mainlist]--Gets the total number of bars in this table set @pageCount =ceiling (@count *1.0/@pageSize) SELECT * FROM (select *,row_number () over (or Der by [Date of booking] desc) as ' num ' from [mainlist]) as T where num between (@pageSize * (@pageIndex-1) +1) and @pageSiz e* @pageIndex ORDER BY [date of booking] DESC---------------------------------------------------------------------------------------------Call DECLARE @ Page intEXEC usp_groupmainlist1 1,100, @page outputselect @page 

14. Common functions

1) ISNULL (expression,value) Returns the value of the expression value if expression is not null

2) aggregation function

AVG ()  --The average statistic is noted that NULL is not counted, plus isnull (column name, 0) sum ()--summation count ()-------------------------------------Minimum Max ()

3) string manipulation function

  LEN ()--Calculate string length  LOWER ()--Turn lowercase UPPER ()--  uppercase  LTRIM ()--the space to the left of the string is removed   RTRIM ()--the space to the right of the string is removed   LTRIM ( RTRIM ('         bb        ')) left  (), right ()--intercept string  SUBSTRING (string,start_position,length)-- The argument string is the main string, start_position is the starting position of the substring in the main string (starting at 1), and length is the maximum length of the substring. SELECT  SUBSTRING (' abcdef111 ', 2,3)   REPLACE (STRING,OLDSTR,NEWSTR) Convert (Decimal (18,2), num)--Retains two decimal places

4) Date correlation function

GETDATE ()--Gets the current datetime  DATEADD (datepart, number, date)-the date after which the calculation increases. The parameter date is the day to be calculated, the parameter number is increment, the parameter datepart is the unit of measurement, and the optional value is shown in remarks. DATEADD (day, 3,date) is the date 3 days after the date of calculation, and DATEADD (month, -8,date) is the date DATEDIFF (datepart, StartDate, 8 months before the date of calculation). EndDate)-Calculates the difference between two dates. DatePart is the unit of measure, the desirable value reference DateAdd.  --Get a part of the date:  DATEPART (datepart,date)--Returns a specific part of a date integer  datename (datepart,date)--Returns the specified part of the date of the string year  ()  MONTH ()  Day ()

15. SQL Statement Execution Order

  5>, ..... Select 5-1> Selection column, 5-2>distinct,5-3>top  1> .... from table  2> Where condition  3> Group by column  4> Having a screening condition  6> Order by column

---------------------The following is based on the recommendation of the Garden friends to follow up, some of the actual code in the project (no time to write the direct paste source)---------------------------

16, group query group by...having

Filtering data after group by grouping in a grouped query, the column name of the query must appear after group by or in an aggregate function

--Query for departments with average salary greater than 2000 Yuan  select DEPARTMENT_ID,AVG (wages) from    employee     where department_id is not  null       GROUP BY DEPARTMENT_ID have          avg (wages) >2000

17. Temporary table [turn]

Method One:
CREATE Table #临时表名 (Field 1 constraints,
Field 2 Constraints,
.....)
CREATE TABLE # #临时表名 (Field 1 constraint,
Field 2 Constraints,
.....)
Method Two:
SELECT * into #临时表名 from your table;
SELECT * into # #临时表名 from your table;
Note: The above # represents a local temporary table, # #代表全局临时表

drop table #Tmp-   -Delete temp table #tmpcreate table #Tmp--Create temporary table #tmp (    ID   int IDENTITY () not     null,--Create column ID, And each new record adds 1    wokno                varchar (       primary key (ID)      --The primary key that defines the ID as the temporary table #tmp      ); Select * FROM #Tmp-    -query data truncate TABLE #Tmp--Clears all data and constraints for temporary tables

Detailed Description: http://www.cnblogs.com/Hdsome/archive/2008/12/10/1351504.html

18. Table-Valued function

Create FUNCTION [dbo]. [GETUPR] (    @upr varchar (2)  --parameters in the incoming function) RETURNS @tab table (  UPR varchar (2)--Returns the field of the table, there is only one field) Asbegin    if (@ Upr= ' 0 ')    begin      Insert @tab         select ' U '         Union select ' P '         Union select ' R '    end    Else     begin       Insert @tab          select @upr     end    RETURN; END

19. Scalar value function

--=============================================--to get sales Clerk 1 's email according to the order number--============================================= Create FUNCTION [dbo]. [Getsalmanaemailbyorderno] (    @orderNo varchar ()) RETURNS varchar (asbegin)  declare @salManAEmail varchar (+)           SELECT @ Salmanaemail=emaila from UserDB.dbo.EmployeeInfo where EmployeeID on        (            select employeeinfoid from Salesmaninfo where Salesmancode in            (                 select Salesmana from OrderInfo where [email protected]            )        )                    RETURN (@ Salmanaemail)    END

20. Trigger [Turn]

CREATE TRIGGER trigger_name on {table_name | view_name} {for | After | Instead of} [insert, Update,delete]as               

Detailed Description: http://www.cnblogs.com/yank/p/4193820.html

SQL Server Common Advanced Grammar Notes