Development Summary (1)-Database

This article is a summary of some database knowledge I encountered during my daily work.
1. Multi-condition Query
Multi-condition query is used in many practical applications. On some pages, you are required to query data by time, category, or other keywords, and these conditions are not necessarily input. In the past, I used to spell the where condition string in the C # code based on the conditions selected, or used the if statement to judge the passed parameters in the stored procedure, but later I learned that using or in the early stored procedure is the simplest, as shown below:

create Table Users(    id int,    name nvarchar(20),    age int)create proc sp_SGetUserInfo(  @Name nvarchar(20),  @Age int)asselect     *from    Userswhere    (@Name='' or name=@Name)and    (@Age='' or age=@Age)

2. Use isnull
Isnull is useful when the input parameter may be null or null. This can usually be used to determine

Isnull (@ name, '') -- if the @ name parameter is null,'' is returned''

3. Several common date format conversions in the database

Date Format Conversion is also frequently used. Generally, 2008-04-12 and 20080412 are used in many formats. The following lists some commonly used formats:

Convert (varchar (10), getdate (), 120) -- returns-04-12convert (varchar (10), getdate (), 20) -- returns-04-12convert (varchar (10 ), getdate (), 112) -- returns convert (varchar (10), getdate (), 111) -- returns/04/12

4. Take the string between two characters in the specified string

Declare @ str1 nvarchar (20) Declare @ str2 nvarchar (20) Declare @ str3 nvarchar (20) set @ str1 = 'abcdefgh' set @ str2 = 'AB' set @ str3 = 'F' declare @ A int, @ B INT set @ A = charindex (right (@ str2, 1), @ str1) -- index set @ B = charindex (left (@ str3, 1), @ str1) of the last character of the second string) -- index of the first character of the third string -- the result is obtained as the second string and? Select substring (@ str1, @ A + 1, @ B-@ A-1) in the middle of the third string)

5. A function to split strings separated by special characters

Create   FUNCTION split(  @StrAll varchar(8000),  @StrSeprate varchar(10))RETURNS @temp TABLE(F1 VARCHAR(100))    ASBEGIN    DECLARE @i INT    SET @StrAll =rtrim(ltrim(@StrAll ))    SET @i=charindex(@StrSeprate,@StrAll )    WHILE @i>=1    BEGIN        INSERT @temp VALUES(left(@StrAll ,@i-1))        SET @StrAll =substring(@StrAll ,@i+1,len(@StrAll )-@i)        SET @i=charindex(@StrSeprate,@StrAll )    END    IF @StrAll <>''    INSERT @temp VALUES(@StrAll )    RETURNEND

6. retrieve all the table names in the database

select name as  tablename from sysobjects where type='U' and name<>'dtproperties'

7. Two methods for obtaining random numbers in sqlserver
A. Create a table rand with the field randomnum, which stores data ranging from 0 to 9.

Use the following SQL statement to generate a random number:

select top 1 RandomNum from Rand order by NewID()

B. Use the rand () function provided by sqlserver

Select cast (floor (RAND () * n) as INT) -- generates a random number between the N-1 select cast (ceiling (RAND () * n) as INT) -- generate a random number between N

8. Set Operations in the database (intersection, and difference)
Create an example table first

create table T1(    id int)create table T2(    id int)insert T1select 1 union allselect 2 union allselect 3 union allselect 4 insert T2select 3 union allselect 4 

Operation Code:

-- Intersection ---------------------------------------------- method select * From t1intersectselect * From T2 -- Method select distinct * From t1where t1.id in (select ID from T2) -- Method select distinct * From t1where exists (select ID from T2 where t2.id = t1.id) -- the difference between in and exists is that in can only judge unique columns, while exists can judge multiple columns of 'distinct' Union select * From t1union allselect * From T2 -------------------------------------------------- difference set select * From t11_tselect * From T2

The returned results will not be written.

9 implement division in sqlserver

Generally, Division by using SQL statements is an integer.

Select 29/3 -- the result is

Use the following method to obtain the decimal number.

-- Define the result variable as the decimal type declare @ result decimal (1.0) -- the key is to multiply the divisor by 1.0 set @ result = cast (29 *)/3 as decimal )) select @ result -- result 9.67

10 sqlserver get the week according to the time

Select datename (weekday, '2017-06-19 ') -- result: Select datepart (DW, '2017-06-19') on Friday -- result 6 -- because Sunday is the first day of a week by default, so the value corresponding to Friday is

11 use SQL statements to set the table primary key

alter table TableName alter column ColumnName int not null  go  alter table TableName add constraint pk_ColumnName primary key(ID)

12 scope_identity @ identity ident_current

-- Scope_identity and @ identity both return the value of the identity column in the last row of the data table in the preceding operation -- however, scope_identity is limited by scope, @ identity is not restricted by the scope./* Are there T1 and T2? Table ?, In? Table? There is an insert trigger in T1. When a piece of data is inserted in Table T1, the trigger is triggered and a piece of data is inserted in Table T2. In this case, there are two scopes: T1 and T2, in this case, if scope_identity () is executed after the insert Statement of T1 () and @ identity will return different values. scope_identity returns T1's identity @ identity returns T2's identity */select scope_identity () Select @ identity -- what is the returned meaning? Select ident_current ('tablename ')

13 Differences Between truncate and delete

-- The roles of Delete and truncate are to clear the data in the specified table, but there is a big difference between the two methods-the delete statement is DML, and this operation will be placed in? In rollback segement-the transaction takes effect only after being committed. If a trigger exists, it will be triggered during execution. -- data deleted using Delete can be retrieved. Delete from tablename -- truncate is DDL, the operation takes effect immediately. -- the original data is not placed in rollback segment and cannot be rolled back. trigger is not triggered. Therefore, be careful when using truncate table tablename.

14. Randomly retrieve data from sqlservr

-- MSSQL random data select top 10 * From tablename order by newid () -- access random data select top 10 * From tablename order by RND (ID) -- MySQL random fetch data select * From tablename order by rand () limit 10

15. Obtain the date of January 1, 1st day of last month based on the current time.

select dateadd(month,-1,getdate())-             day(dateadd(month,-1,getdate()))+1

Updating ........