Database (string function, Date function, data type conversion Cast,convert)
Use student
Go
CREATE TABLE Yuangong
(
code int,
Name varchar (20),
Sex varchar (20),
Age int,
Bumen varchar (20),
)
INSERT into Yuangong values (1, ' Week number ', ' Male ', 27, ' sales Department ')
INSERT into Yuangong values (1, ' King of the Week ', ' Male ', 27, ' sales Department ')
INSERT into Yuangong values (1, ' place ', ' Male ', 27, ' sales Department ')
INSERT into Yuangong values (1, ' Zhou Wen king ', ' Male ', 27, ' sales Department ')
INSERT into Yuangong values (1, ' See King ', ' Male ', 27, ' sales Department ')
INSERT into Yuangong values (1, ' Zhou Wang ', ' Male ', 36, ' sales Department ')
Go
Sp_renamedb xxx, FFF--Renaming the database! XXX changed into FFF
Alter table Xinxi Add [int] varchar (20)--Adds an int column to the Xinxi table, and the value type is varchar (20). The newly added column cannot be set to not be empty!
Alter table xinxi Drop column [int]--delete [int] Column
Update Xinxi set nianling=26 where Fenshu between and 100-modified. Change the score ~100 between the people and the age.
Select distinct name from xinxi– automatically removes duplicate name information!
################################################
ALTER TABLE Yuangong add CID varchar
Update Yuangong Set cid=370322198908120800
################################################
-- String Functions
--the first character of the returned string is ASCII encoded
Select ASCII (' ame ')--Returns an int value
Select ASCII (name) from Yuangong--note the application format
Select *from yuangong where ASCII (name) >200
--Convert characters to corresponding ASCII codes
Select CHAR (100)
Select CHAR (age) from Yuangong
--Check the string, return the qualifying Initial index, index from start, return representative not found
--(in C # IndexOf index from Start, return-1 means not found)
Select CHARINDEX (' EFG ', ' Abcdefghijklmnopqrstuvwsyz ')--this is displayed.
Select CHARINDEX (' 199 ', CID) from Yuangong--application to table usage
--string concatenation
Select ' 1 ' + ' abc ' + ' 2 ' + ' Def ' as by virtue of--execution, will be displayed (by: abc2def)
--Return similarity degree
Select difference (' abcdefgh ', ' abcdefgh ')--Returns the number ~4,4 represents exactly the same!
--truncate characters from left to right
Select Left (' ABCDEFG ', 3)--Indicates that a bit is intercepted from the right, showing ABC
Select Right (' ABCDEFG ', 3)--Indicates that the bit is intercepted
--Return string length
Select LEN (' A V ')--returns. The following spaces do not count, the front and the middle are counted!
--All small/uppercase
Select LOWER (' ASDFGCVX ')--Display ASDFGCVX
Select UPPER (' ABCDDDC ')--Display ABCDDDC
--from the left to the space
Select LTRIM (' 34332aaa ')
--when displayed, remove the left space! Display: 34332AAA
Select RTRIM (' 3234 ')
--when displayed, go to the right space! Showing: 3234
--Replacement
Select REPLACE (' 1234567890 ', ' 456 ', ' ABCD ')
--Show 123abcd7890. (The string to replace, the content to replace, the modified content)
Select REPLACE (CID, ' 606 ', ' 103 ') from Yuangong--formatting applied to the table
-Copy and paste
Select REPLICATE (' abc ', 3)--Copy ABC and paste secondary
--Sequential flipping
Select REVERSE (' ABCDE ')--Display EDCBA
Select REVERSE (name) from Yuangong--apply formatting to tables
--Space
Select ' A ' +space (a) + ' B '--splicing AB for easy observation! A space
-Intercept numbers
The Select STR (123456.789012345,10,9)--10 is the total number of digits that contain. 9 is the number that is retained after the decimal point. The previous number is the main.
--Replacement string
Select STUFF (' abcdefgh ', 4,2, ' hello ')--4 represents the beginning of the fourth index from left to right (starting from), and 2 represents the number of characters replaced. This displays the ABC Hello FGH
-Intercept string
Select SUBSTRING (' ABCDEFG ', 4,2)--intercepts the specified length string, starting at the specified index. This shows the de
--common: case, flip, charindex, substring, len
#################################################################
-- Date and time data types and functions
--show the day of the week
SELECT @ @DATEFIRST--Displays the day of the week and returns int
-Give a time to add some time
Select DATEADD (year,2, ' 2006-01-01 ')--show-01-01
--For the time difference (years, months, days, hours, minutes ...) is OK)
Select DATEDIFF (year, ' 2011-07-01 ', ' 2014-07-01 ')--display years.
Select DATEDIFF (Day, ' 2011-07-01 ', ' 2014-02-03 ')--show days.
--extracting years, months, days, hours, minutes, seconds, weeks, etc.
Select Datename (Weekday, ' 2014-11-02 ')--show ' Day ' is. ' Month ' is. ' Year ' Yes. The return value is Nvarchaer
Select DATEPART (WEEKDAY, ' 2014-11-02 ')--function ditto, return value int
Select year (' 2014-11-02 ')--extract years:, month, day. Note the format. function equals datepart
Select GETDATE () as current time--get database current time!
Select ISDATE (' 2013-06-31 ')--determine if the date format is correct, yes: 1, wrong: 0.
--Common IsDate, GETDATE, DATEPART,
Type conversions :cast,convert
Select CAST (123 as varchar (20))--The varchar type 123 is obtained.
Select CAST (1.23 as int)--the INT type 1 is obtained.
Select CAST (123 as Decimal (10,2))--The decimal type 123.00. This shows the total number of digits, 2 indicates the number of decimal places
Select CAST (12.3 as Decimal (18,2))--draws. 30.
Select CONVERT (int,123.45)
-Use the opposite of cast!
Select Name, (SUBSTRING (cid,7,4) + ' year ' +substring (cid,11,2)
+ ' Year ' +substring (cid,13,2) + ' month ') as birthday from Yuangong
########################################
CREATE TABLE Shengrii
(
Name varchar (29),
Birthday varchar (29),
)
Go
INSERT into shengrii values (' Zhang San ', ' 2014 Month Day ')
Select *from shengrii
Database string, date, and data type conversions (Cast/convert)