SQL SERVER Study Notes

--- Lowercase output: abcdefSELECT Lower ('abcdef'); --- uppercase output: abcdefselect upper ('abcdef '); -- remove the left space-> 'abcdef' select ltrim ('abcdef'); -- remove the right space-> 'abcdef' select rtrim ('abcdef '); -- truncates a string from the LEFT-> abcdeSELECT LEFT ('abcdef123456', 5); -- truncates a string from the LEFT-> 23456 select right ('abcdef123456', 5 ); --- the start position of the input character is 0 select charindex ('A', 'AB') --- truncates a string. For example, two lengths are truncated starting from the third character. The output is as follows: cdSELECT SUBSTRING ('abcdefghi', 3,2) --- replace string output: A4CDEFG4ACS Elect replace ('abcdefgbac ',' B ', 4); --- output the string value with the specified number of times repeated: ababselect replicate (' AB ', 2) --- return the REVERSE output of the string value: fedcbaselect reverse ('abcdef') --- data type conversion output: 13 select cast ('3' as int) + 10 ---- time: ------ get current time output: 16:17:27. 437 select GETDATE (); --- obtain the date select day (GETDATE (); --- obtain the year select year (GETDATE (); --- obtain the MONTH select MONTH (GETDATE ()); --- add time YEAR, DAY, HOUR, MONTH -- add time: 2023-03-19 16:20:06. 763 select Dateadd (YEAR, 10, GETDATE (); --- DATENAME (<datepart>, <date>) -- returns the specified part of the date in the form of a string. Specified by datepart. Select datename (year, GETDATE (); --- DATEPART (<datepart>, <date>) --- returns the specified part of the date in the form of an integer. This part is specified by datepart. Select datepart (yy, GETDATE (); -- equivalent to year (getdate () select datepart (mm, GETDATE (); -- equivalent to month (getdate ()) select datepart (dd, GETDATE (); -- equivalent to day (getdate () select datepart (hh, GETDATE (); ---- Select CONVERT (varchar (100 ), GETDATE (), 20); -- 2013-03-19 16: 34: 50 Select CONVERT (varchar (100), GETDATE (), 23); -- 2013-03-19Select CONVERT (varchar (100 ), GETDATE (), 8); -- 16:35:54 --- DateDIFF comparison time output: 6 select datediff (hh, '2017-03-19 12:34:50 ', '2017-03-19 18:34:50 ') --- common statistical function/* AVG ()-average returned value count ()-number of returned rows first ()-returns the first value last ()-returns the last value max () -min () returned maximum value-total () returned minimum value */--- row_numberselect row_number () over (order by field1) as row_number, * from t_table

Row-to-column conversion:

SELECT [DAY], sum (CASE result when' wins 'then 1 else 0 end) as wins, sum (CASE result when' negative 'then 1 else 0 end) as negative from test group by [day]

Cursor:

Transactions

BEGIN TRAN  update Blog set name=name+'Rhythmk'  where Id='04C65E2C-E604-4295-9F20-0004B72DE0B4'  select  CAST('107a'  as   INT)if  @@error<>0     BEGIN   SELECT 'A'   ROLLBACK  TRAN  ENDELSE    BEGIN  SELECT  'B'     COMMIT  TRAN  END

Method 2

BEGIN  TRAN      UPDATE BlogCate   SET name = name + 'Rhythmk'  WHERE   Id = '04C65E2C-E604-4295-9F20-0004B72DE0B4'     SELECT  CAST ('107' AS INT)    BEGIN  try      SELECT  'B' COMMIT TRAN  END try  BEGIN     catch    SELECT'A' ROLLBACK TRAN  END catch