Database common knowledge points

Recently in the writing of some interfaces, summed up some of the points to be aware of the current thought, that is not the difference between SQL Server and other database statements, just think about the written down, the usual after the thought of adding in

NULL

　　Null in the database indicates that no value has been assigned to this column of data

querying a null value in the database is not possible with "=null" or "=" "(the length of the null that needs to be raised is null, the" "is 0), but the is null/is not null

page Out

SQL Server(limit is not supported)

First Kind

　Select Top (pagenum) field 1, Field 2
From table name where ID not in (
Select Top (page*pagenum) field 1, Field 2 from table name ORDER by ID
)  ORDER BY ID        
========== "Need page current page number, pagenum the current page display the line of thought is: through not in the data, need a sort of field Example: Select Top * from users where PID is not in (select top 0 PID from the users order by PID) ORDER by PID　

The second Kind

Select Row_number () over (order by PID) RowNumber,  *    the From table name =====> get all the data    ) A where ro Wnumber > (page*pagenum); = = = "The RowNumber of this whole data is greater than this number Example: Select Top ten from (select Row_number () over (order by PID) rownumber,* from users) A where rownumber>0　

Mysql

　　MySQL's paging is generally through the limit (does not support top)

　SELECT * FROM table name limit M,n ===>m means starting from the first, n means the current page shows the number of bars     m= (current page-1) * Example of the number of current pages : Select /c3>* from users limit 0,10　

use of the covert () function in SQL Server

　　The specific reload format can be seen in the following example

　　Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am SELECT CONVERT (varchar), GETDATE (), 1): 05/16/06 SE Lect CONVERT (varchar), GETDATE (), 2): 06.05.16 select CONVERT (varchar), GETDATE (), 3): 16/05/06 Select CONVERT (v Archar (+), GETDATE (), 4): 16.05.06 select CONVERT (varchar), GETDATE (), 5): 16-05-06 select CONVERT (varchar), G  Etdate (), 6): 7-select CONVERT (varchar (+), GETDATE (), 8):, select CONVERT (varchar (), GETDATE (), (+): 10:57:46 select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827AM Select CONVERT (varchar), GETDATE (),　　: 05-16-06 Select CONVERT (varchar), GETDATE (), one): 06/05/16 select CONVERT (varchar), GETDATE (), 12): 060516 Select CONVERT (varchar), GETDATE (): 2006 10:57:46:937 SELECT CONVERT (varchar), GETDATE (), 14): 10:57: 46:967 select CONVERT (varchar), GETDATE (), 2006-05-16 10:57:47 Select CONVERT (varchar), GETDATE (), 21): 200 6-05-16 10:57:47.157 SELect CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM SELECT CONVERT (varchar, GETDATE (), 23): 2006-05-16 Select CONVERT (varchar), GETDATE (): 10:57:47 select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.2 101 Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am SELECT CONVERT (varchar (+), GETDATE () (): 05/16/　　2006 Select CONVERT (varchar), GETDATE (), 102): 2006.05.16 select CONVERT (varchar), GETDATE (), 103): 16/05/2006  Select CONVERT (varchar), GETDATE (), 104): 16.05.2006 select CONVERT (varchar), GETDATE (), Max.): 16-05-2006 Select CONVERT (varchar), GETDATE (), 106): 2006 Select CONVERT (varchar), GETDATE (), 107): 2006 Select CONV ERT (varchar), GETDATE (), 108): 10:57:49 SELECT CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am Sele CT CONVERT (varchar), GETDATE (), (): 05-16-2006 select CONVERT (varchar), GETDATE (), 111): 2006/05/16 Select CON VERT (varchar), GetdatE (), 20060516 select CONVERT (varchar), GETDATE (), 113): 2006 10:57:49:513 Select CONVERT (varchar), G Etdate (): 10:57:49:547 select CONVERT (varchar), GETDATE (), (): 2006-05-16 10:57:49 Select CONVERT (varchar (10 0), GETDATE (), 121): 2006-05-16 10:57:49.700 SELECT CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827 Selec T CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am SELECT CONVERT (varchar (+), GETDATE (), 131): 18/04/1427 10:57:49:920am

self-growing ID

MySQL ID int auto_increment PRIMARY key NOT NULL

SQL Server ID int identity (primary) key NOT NULL

Oracl < Span style= "COLOR: #33cccc" >e (through sequence to achieve self-growth increment by 1 (increments per increment) Start with 1 (starting from the first few) )

CREATE table users (ID int primary key NOT NULL, name varchar (+)  insert into users (Users_id.nextval, ' whiteme '); 

Database common knowledge points