SQL Server Common functions, common statements

First, common functions

1. String functions:

CHARINDEX (': ', ' abc:123 ') --Find the position of a character starting in a string

Len (' Zhangsan ') --Get the length of a string

Left (' Ly, Gentleman's Yiu ',2) --Returns the character of a specified length from a string

< Span class= "Hljs-keyword" >right (char_expr,int_expr)  --Returns INT_EXPR characters to the right of the string

SUBSTRING (expression,start,length)--intercept string

Datalength (char_expr)--The return string contains the number of characters but does not contain the following spaces

Length (expression,variable)--Specifies the lengths of string or variable names

Concat (STR1,STR2,...) --Returns a string from a parameter link

Upper (' Yang ') --Converts a lowercase string to uppercase

LTrim (' Zhangsan ') --Remove the space to the left of a character

< Span class= "hljs-string" >rtrim ( ' zhang   san   ') < Span class= "hljs-comment" >--remove space to the right of a character

stuff ( ' ABCDEFG ', Span class= "Hljs-number" >2,4,< Span class= "hljs-string" > ' Zhang San ') - -Removes a string of the specified length from the specified location and replaces it with a new string

replace (< Span class= "hljs-string", "The Yiu of the Son", ' yang son ', ' June ') - -Replaces the string specified in a string with another string

 

2. Date, Time function

GETDATE () --Get current system time

Datename (datepart,date_expr) --A string format specifying the time period in a date string

DatePart (datepart,date_expr) --Gets the integer form of the specified date part

DateDiff (DATEPART,DATE_EXPR1.DATEEXPR2) --the interval portion specified in two time periods

DATEADD (datepart,number,date_expr) --Adds the specified value to the specified date period

3. System functions

Suser_name () User login name

USER_NAME () The name of the user in the database

User's name in the database

Show_role () rules that work for the current user

Db_name () database name

object_name (obj_id) database object name

Col_name (obj_id,col_id) column name

Col_length (objname,colname) column length

Valid_name (char_expr) is a valid identifier

Second, the commonly used statements-(column of additions and deletions to change)

1. Add columns

ALTER TABLE TableName add columnName varchar ($)

2. Modify the column type

ALTER TABLE tableName ALTER COLUMN columnName varchar (4000)

3. Modify the column name

EXEC sp_rename ' tablename.column1 ', ' column2 ' (Modify the Column1 column name of the table named TableName to Column2)

4. Delete Columns

　ALTER TABLE tableName drop column ColumnName

Iii. Common Statements-(duplicate table structure)

1: Copy table structure and data to a new table

SELECT * into destination database name. dbo. destination table name from the original table name

SELECT * Into My0735home.dbo.infoMianTest from Infomian

2: Back up part of the table column (do not write * and write out the list of columns)

Select Column Name 1, column name 2, column name 3 into destination database name. dbo. destination table name from the original table name

Select Id,title,mtype,stype,author,tel,nr to InfoMianTest2 from Infomian

3: Part of the backup table row (plus where condition)

SELECT * into destination database name. dbo. destination table name from the original table name where id<10

SELECT * into Infomiantest2 from Infomian where id<10

4: Back up part of a table column (do not write * and write out a list of columns) and a subset of rows (plus where condition)

Select Column Name 1, column name 2, column name 3 into destination database name. dbo. destination table name from the original table name where id<10

5: Duplicate the structure of the table only:

SELECT * Inot T1 from titles WHERE 1=2

6: Query results from multiple tables:

SELECT Title_id,title,pub_name into T3

From titles T INNER JOIN Publishers P

On t.pub_id=p.pub_id

SQL Server Common functions, common statements