Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.