SQLServer from beginner to proficient
1. Data paging
OFFSET: Specifies the number of rows to skip before returning rows from a query expression.
FETCH: Specifies the number of rows to return after the OFFSET clause.
OFFSET is a synonym for page number, and FRTCH stands for the number of lines displayed per page.
select ProductID, ProductNumber, Name AS ProductName, ListPricefrom Production.Productorder by ProductIDoffset 0 rowsfetch next 10 rows only
2. Use of variables
Declare variable: declare @variable int
There are three ways to assign: you can use the SET keyword, which is the best choice; you can assign a value to a variable in a SELECT statement; or you can assign a value to a variable during declaration.
declare @variable intset @variable = select @variable = from declare @variable int =
3. Change the database name
alert database test_dbmotify name=company
4. Query the first three data
select TOP 3 * from test_table
5. Gender is not male
select * from test_table where not S_sex ='male '
6. User rights
granted this permission
grant update,delete on test_tableto sa with grant option
Cancel permission
deny update on test_table to sa cascade
7. Case statement
select s_id,s_name,case s_name when 'MCA' then 'monitor' when 'Xu San' then 'Study Commissioner' when 'Liu Jie' then 'Sports Commissioner' else 'None'endas 'Position'from stu_info
8. Delay of 10 seconds
declare @name varchar(50);set @name='admin';beginwaitfor delay '00:00:10';print @name;end;
9. Functions
select ASCII('s'),ASCII('sql'),ASCII('1');
Output: 115 115 49
select char(115),char(49);
Output: s 1
select left('football',4); //right, take the four on the right
Output: foot
select '('+' book '+')','('+LTRIM(' book ')+')'; //Delete the space to the left of the string
Output: ( book )(book )
select STR(3141.55,6,1),STR(123.45,2,2) //Convert numbers to strings
Output: 3141.6**
select REVERSE('abc') //string reverse order
Output: cba
SELECT LEN ('no '),LEN ('date'),LEN(12345) //string length
Output: 2 2 5
SELECT CHARINDEX('a','banana'),CHARINDEX('a','banana',4),CHARINDEX('na','banana',4) //Specify the character start position
Output: 2 4 5
SELECT LOWER('BEA'),LOWER('Well') //uppercase to lowercase, UPPER--uppercase
Output: bea well
SELECT REPLACE('xxx.sqlserver2012.com','x','w') //string substitution
Output: www.sqlserver2012.com
SELECT ABS(2),ABS(-3.3),ABS(-33) //take absolute value
Output: 2 3.3 33
SELECT PI() //pi
Output: 3.14159265358979
SELECT SQRT(9),SQRT(40) //Find the square root
Output: 36.32455532033676
SELECT RAND(),RAND(),RAND() //Generate a random number between 0 and 1 SELECT ROUND(1.38,1),ROUND(1.38,0),ROUND(232.38,-1),ROUND(232.38,-2) //rounded
Output: 1.40 1.00 230.00 200.00
SELECT SIGN(-21),SIGN(0),SIGN(21) //sign function
Output: -101
SELECT CEILING(-3.35),CEILING(3.35) //Round up
Output: -34
SELECT FLOOR(-3.35),FLOOR(3.35) //Round down
Output: -43
SELECT POWER(2,2),POWER(2.00,-2) //exponentiation
Output: 40.25
SELECT EXP(3),EXP(-3),EXP(0) //Power operation with e as base
Output: 20.08553692318770.04978706836786391
10. Data type conversion function
SELECT CAST('121231' AS DATE),CAST(100 AS CHAR(3)),CONVERT(TIME,'2016-12-13 10:57:00') //Data conversion
Output: 2012-12-31100 10:57: 00.000000
Date and Time Functions
select getdate() //get current date SELECT GETUTCDATE() //Get world standard date value select day ('2016 -12-13 01:01: 01') //get day select month ('2016 -12-13 01:01: 01') //get month select year ('2016 -12-13 01:01: 01') //get year select datename(weekday,'2016-12-13 01:01:01') //select day of week datename(dayofyear,'2016-12-13 01:01:01') //day of the year
increase time
SELECT DATEADD(YEAR,1,'2016-12-13 01:01:01'),DATEADD(MONTH,2,'2016-12-13 01:01:01'),DATEADD(HOUR,1,'2016-12-13 01:01:01')
2017- 02 -13 01:01:01.000 2017-02-13 01:01:01.000 2016- 02 -13 02:01:01.000
12. System Functions
