- Frog Frog Recommendation: Recently, I learned T-SQL notes, some of them may be written, and some things have not yet column, such as Return, accept the output parameters of the stored procedure. - Write this mainly I quickly recalled this knowledge. I have done a comment in the necessary places. - I feel that I will listed common T-SQL usage.
- Execute the following, but put all statements together may not be executed, because some statements cannot be performed in a batch: - OSQL-E-I mysql1.sql -o myresult.txt - aggregation Function USE Pubsgoselect Avg (Distinct Price) - Average Average from Titleswhere Type = 'Business'go
Use Pubsgoselect Max (YTD_SALES) - Maximum from Titlesgo
Use pubsgoselect min (ytd_sales) - Minimum from Titlesgo
Use Pubsgoselect Type, SUM (Price), SUM (Advance) - Squire from Titlesgroup by Typeorder by Typego
Use Pubsgo Select Count (DistINCT City) - Summary from Authorsgo
Use Pubsgoselect stdev (Royalty) - Returns the statistical deviation from all values in a given expression from Titlesgo
Use Pubsgoselect stdevp (Royalty) - Returns Fill Statistics Deviation from Ownership in Expression from Titlesgo
Use pubsgoselect var (royal) - Returns all values of statistics from Titlesgo
Use pubsgoselect varp (Royalty) - Return to all values of the statistical variance from Titlesgo
- Mathematical functions
Select Sin (23.45), Atan (1.234), Rand (), PI (), SIGN (-2.34) - where RAND is a random number - configuration function select @@ version - Get the current database version Select @@ Language - Current Language - Time Function Select getdate () AS 'WAWA_GETDATE' - Current Time Select Getutcdate () AS 'WAWA_GETUTCDATE' - Get UTC Time Select Day (GetDate ()) AS 'WAWA_DAY' - Take out the day SELECT Month (GetDate ()) AS 'WAWA_MONTH' - Take out the month Select Year (getdate ()) as' wawa_year '- Remove the Year Select Dateadd (D, 3, Getdate ()) as WAWA_DATEADD - Add three days, pay attention' D 'means the day,' m 'means the month,' yy 'means the year, the same SELECT dateDiff (d,' 2004-07-01 ',' 2004-07-15 ') as WAWA_DATEDIFF - calculate two times difference Select Datename (D, '2004-07-15') AS WAWA_DATENAME - Take a part of the time Select DatePart (D, getDate ()) as Wawa_datePart - Take a part of the time, and the above - string SELECT ASCII (123) AS '123', ASCII ('123') AS '"123"', ASCII ('ABC') AS '"ABC" - Convert to ASCII code Select Char (123), char ( 321), Char (-123) - Convert to the character Select Lower ('ABC'), Upper ('ABC'), Upper ('ABC') - Convert SELECT STR (123.45, 6, 1), STR (123.45, 2, 2) - convert numeric values into strings Select LTRIM ('"no space" on the left "') - Go to Space SELECT RTRIM ('" No space in the right "') - Go to Space Select Ltrim (RTRIM ('"there is no space" ')) - Go to Space Select Left (' SQL Server ', 3), Right (' SQL Server ', 6) - Take the left or right use pinselect au_lname, substring (au_fname, 1, 1) - tap String from authorsorder by au_lname
SELECT Charindex ('123', 'Abc123Def', 2) - Returns the starting position of the specified expression in the string Select Patindex ('123', 'Abc123def'), Patindex ('% 123%', 'ABC123DEF') - Returns a first time in the expression Select Quotename ('ABC', '{'), Quotename ('ABC') - Returns String Select Reverse expanded by the specified character ('ABC '), Reverse (' Shanghai ') - Upside down Sequence Select Replace (' AbcdefghicDe ',' CDE ',' XXXX ') - Returns Replacing the String Select Space (5), Space (5), Space -2) - System function select host_name () as 'host_name', host_id () as 'host_id', user_name () as 'user_name', user_id () as 'user_id', db_name () AS 'DB_NAME' - Variable Definition Using - Declaration Local Variable Declare @mycounter INTDECLARE @Last_Name Varchar (30), @ FName Varchar (20), @ State Varchar (2) - Declare Multiple Variables - Give Variables Use NorthwindgodeClare @FirstNameVariable Varchar ( 20), @regionvariable varchar (30) set @ firstnamevariable = 'anne' - You can use SET, you can also assign a value to the variable, Microsoft recommends using SET, but SELECT is useful when selecting a value to assign a value. @Regionvariable = 'WA'
select lastname, firstname, title - returns the version of the database select @@ error - build a Select statement with an assignment statement and had variable and queries from employeeswhere firstname = @firstnamevariable or region = @ regionvariablego-- global variable select @@ version - - Returns the last script error Select @@ identity - Returns the last automatic growth of the ID
- WHILE, BREAK, Continue - First calculate the average price of all numbers, if it is less than 30, enter the loop to let all Price doubles, and there is an IF to determine if the maximum unit price is greater than 50 Exit the loop, otherwise continue to circulate, know the maximum unit price is greater than 50 on the Break loop, huh, - I should analyze it. ISE Pubsgowhile (SELECT AVG (Price) from titles <$ 30 begin update titles set price = price * 2 SELECT MAX (Price) from Titles IF (SELECT MAX (Price) from Titles> $ 50 Break Else ContinueEndprint 'Too Much for the Marker To Bear'
- Transaction programming classic example - Begin Transaction is the start of the transaction, and the commit transaction is a submission. Rollback Transaction is a rollback transaction - this example is to insert a record, if the error is wrong, roll back the transaction, that is, cancel, and Direct RETURN (return), if it is correct, you will submit this transaction. - This return can also be used in the stored procedure, available with EXEC @ Return_Status = Pro_Name to get this value Use PubsgobeGin TRAN MyTRAN Insert Into Stores (Stor_ID, Stor_name) Values ('333', 'My Books') Go Insert Into Discounts (discounttype, stor_id, discount) VALUES ('clearance ",' 9999, 50.00) IF @@ error <> 0 Begin Rollback TRANMITRAN PRINT 'Insert Discounted error' return endcommit TRAN MyTRAN - Transaction Save Save Sample Example - You can Rollback to the specified save point after doing your business, you can't use the use of use pubsgoselect * from StoreSbegin Transaction TestsaveTran Insert Into Stores (Stor_ID, Stor_Name) VALUES ('1234', ' WZD Book ') Save Transaction Before_Insert_data2 Go Insert Into Stores (STOR_ID, STOR_NAME) VALUES (' 5678 ',' Forecent Books') Gorollback Transaction Before_INSERT_DATA2SELECT * FROM Store
- procedure stored in the storage use pubsif exists (select name from sysobjects where name = 'proc_calculate_taxes' and type = 'P') drop procedure proc_calculate_taxesgocreate procedure proc_calculate_taxes (@ p1 smallint = 42, @ p2 char (1), @ p3 varchar (8 ) = 'char') assselect * from Titles - Execution Process Execute Proc_calculate_taxes @ p2 = 'a'
