Time-date functions, type conversions, subqueries, paged queries

1. Time-Date function:

Set Datefirst 1--setting Monday as the first day
--datepart function to return a part of a time date
--parameter 1 refers to which part is returned, and DW represents DayOfWeek
--Parameter 2 refers to the time and date in which to return
--datefirst is a system constant, which needs to be added when used @@
SELECT @ @DATEFIRST as ' 1st day ', DATEPART (DW, GETDATE ()) as ' Today '
--getdate refers to getting the current system time at execution time
SELECT GETDATE ()--takes the current system time at execution time

--dateadd how much time to add to or subtract from a section
--Parameter 1 is the part to be added or reduced
--Parameter 3 is the time date on which to increase or decrease
--Parameter 2 is how much to add or subtract
Select DATEADD (year,-2, ' 2015-11-20 ')
Select DATEADD (month,3, ' 2015-11-20 ')
Select DATEADD (day,5, ' 2015-11-20 ')

--datediff calculated time difference different different, difference
--parameter 1 refers to which part is the algorithm
--Parameter 2 is the start time date
--Parameter 3 is the end date
Select DATEDIFF (year, ' 2011-1-2 ', ' 2015-2-2 ')--years Difference
Select DATEDIFF (Month, ' 2011-1-2 ', ' 2015-2-2 ')--month difference (count middle all months)
Select DATEDIFF (Day, ' 2011-1-2 ', ' 2015-2-2 ')--diurnal difference (counting the middle of all days)

--Counting the days of love
DECLARE @startday varchar (50)--you need to add a data type when declaring a variable, before the variable name plus @
Set @startday = ' 2008-8-8 '--sets the value of the variable
--getdate is the time to get the current system
Select DATEDIFF (Day, @startday, GETDATE ()) as Love days

--datepart returns a portion of a time date
--Parameter 1 is which part of the return
--the date on which the parameter 2 is calculated
Select DATEPART (year, ' 2008-8-8 ')--return years
Select DATEPART (DY, ' 2008-8-8 ')--return to dayofyear the day of the year
Select DATEPART (QQ, ' 2008-8-8 ')--return to quarterly total of four quarters

--day Month is equivalent to the datepart above
--Return day, month, year
Select Day (' 2008-8-8 ')
Select year (' 2008-8-8 ')
Select month (' 2008-8-8 ')

--isdate determine if the time date format is correct
--The return value of 1 indicates correct
--A return value of 0 indicates an error
Select ISDATE (' 2012-2-29 ')
Select ISDATE (' 2008-80-8 ')

--sysdatetime system Precise Time
Select Sysdatetime ()
SELECT GETDATE ()--the difference is accurate or not

2. Type conversion:

--Data type conversion cast convert
--cast writes the converted value + as + the type converted to
Select CAST (1.73 as int)
Select CAST (1.73 as varchar (50))
Select CAST (1.73333333 as Decimal (18,2))
Select cast (CAST (' 1.78 ' as Decimal (18,2) as int)
Select CAST (' 1.78 ' as Decimal (18,2))
The--convert parameter 1 is the type that needs to be converted, and parameter 2 is the value that needs to be converted
Select CONVERT (int, ' 87 ')
Select CONVERT (Decimal (18,2), ' 87.8787 ')--automatically rounded when the exact number of digits is later
Select CONVERT (varchar (50), 342523.234)

3. Sub-query:

Use Student
Go
--Need to build two tables, a departmental table, a staff table
--Department: Department's number (primary key), department name, Department's responsibilities
--personnel: Number of personnel, name, age, gender, CID, department number

CREATE TABLE Bumen
(
Bcode int PRIMARY KEY,
Bname varchar (50),
Bzhi varchar (50)
)
Go
CREATE TABLE Ren
(
code int identity (+),
--Represents the self-growth column, starting at 1 and increasing by 1 each time
--You don't need to add this column when you add values
Name varchar (50),
Age int,
Sex char (10),
CID varchar (50),
bumen int
)
Go

INSERT into bumen values (1001, ' Personnel Department ', ' responsible for interview assessment ')
INSERT into bumen values (1002, ' marketing ', ' responsible for market development ')
INSERT into bumen values (1003, ' sales department ', ' responsible for product sales ')
INSERT into bumen values (1004, ' production department ', ' responsible for product production ')
INSERT into bumen values (1005, ' QC Department ', ' responsible for product quality inspection ')
Go

Insert into ren values (' Zhang San ', 24, ' Male ', ' 370303199903044440 ', 1002)
Insert into ren values (' John Doe ', 25, ' female ', ' 370303199905044440 ', 1001)
Insert into ren values (' Harry ', 26, ' Male ', ' 370303199907044440 ', 1002)
Insert into ren values (' Zhao Liu ', 27, ' female ', ' 370303199908044440 ', 1001)
Insert into ren values (' von VII ', 28, ' Male ', ' 370303199909044440 ', 1003)
Insert into ren values (' Big money ', 29, ' female ', ' 370303199901044440 ', 1001)
Insert into ren values (' Zhao er ', 22, ' Male ', ' 370303199911044440 ', 1003)
Insert into ren values (' Chenhe ', 31, ' Male ', ' 370303199912044440 ', 1001)
Insert into ren values (' Franklin Wang Blue ', 32, ' Female ', ' 370303199904044440 ', 1004)
Insert into ren values (' Baby ', 33, ' Male ', ' 370303199903034440 ', 1004)
Insert into ren values (' Li Chen ', 34, ' female ', ' 370303199903224440 ', 1004)
Insert into Ren values (' Fan ye ', 35, ' Male ', ' 370303199903234440 ', 1005)
Insert into ren values (' Ningquan ', 36, ' Male ', ' 370303199903254440 ', 1002)
Insert into ren values (' Li Bingbing ', 37, ' Male ', ' 370303199903264440 ', 1003)
Insert into ren values (' Feng Xiaogang ', 38, ' female ', ' 370303199903274440 ', 1005)
Insert into ren values (' Guo Degang ', 39, ' Male ', ' 370303199903284440 ', 1004)
Insert into ren values (' Zhao Benshan ', 40, ' female ', ' 370303199903294440 ', 1004)
Go

Select *from bumen
SELECT * FROM Ren
--see who is in the sales department
Select Bcode from bumen where bname= ' sales department '
select * from Ren where bumen =1003
--subquery (nested query)
SELECT * from-ren where bumen = (select Bcode from bumen where bname= ' sales Department ')

--View the department responsibilities of Zhang San
Select Bumen from ren where name = ' Zhang San '
Select Bzhi from Bumen where Bcode =1002
--Sub-query
Select Bzhi as department responsibility from bumen where Bcode = (select Bumen from ren where name = ' Zhang San ')

--All information about the oldest person in the department with the largest number of departments
Select top 1 Bumen, COUNT (*) from the Ren Group by bumen ORDER by COUNT (*) desc
Select top 1 * from Ren where bumen =1004 order BY age DESC
--Sub-query
Select top 1 * from-ren where bumen = (select top 1 bumen from Ren Group by bumen ORDER by COUNT (*) desc) Order BY age Desc

--Sort by age, from small to large, not the first five people
SELECT * from-ren where code not in (select Top 5 code from-ren order by age)

--Get all the oldest information in a male clerk
Select *from ren where code = (select top 1 code from ren where sex= ' man ' ORDER BY age DESC)

4. Paging query:

--Paging query five as a page display
--the first five articles
Select Top 5*from ren
--第6-10条
Select Top 5*from ren where code not in (select Top 5 code from ren)
--第11-15条
Select Top 5*from ren where code not in (select Top ten code from ren)
--16th, 17 (The last time you can remove the previous top 5)
Select *from ren where code not in (select Top all code from ren)

--Want to know in the beginning can be divided into several pages (five pages)
Select CEILING (COUNT (*)/5.0) from ren--note to add. 0

Select *from bumen
SELECT * FROM Ren
---Convert the code of the Department within the personnel table into the department name query.
Select Code,name, Age,sex,cid, (select Bname from bumen where Bcode = ren.bumen) as department from Ren

--Find out all information about people older than 30 years in the sales department
SELECT * from-Ren where age>30 and bumen = (select Bcode from bumen where bname= ' sales Department ')
--exists exist. Consistent with the previous code, not commonly used
SELECT * from-Ren where exists (select * from Bumen where bcode=ren.bumen and bname= ' Sales Department ') and AGE>30

Time-date functions, type conversions, subqueries, paged queries