Simple recording of several useful SQL queries

In the following example, the queried data table is referenced in the person table.

I,Limit the number of returned rows

1. SQL Server

 
Select top 10 ID, firstname, lastnamefrom person
 

2. Oracle

 
Select ID, firstname, lastnamefrom personwhere rownum <= 10
 

3. DB2

 
Select ID, firstname, lastnamefrom person fetch first 10 rows only
 

4. MySQL

Select ID, firstname, lastnamefrom person limit 10
 

5. PostgreSQL

 
Select ID, firstname, lastnamefrom person limit 10
 

Summary: Query statements are very basic. MySQL and PostgreSQL are written in the same way. We can see that the SQL statements of each DBMS are quite readable, and the user (developer) experience is very important.

 

2. query date by specific format (SQL Server Edition)

In actual development, the results are usually directly queried and then usedProgramming LanguageOutput The Date Format (such as C # and Java), but the database also provides the conversion processing capability. The following describes the common time format queries and corresponding output results of SQL Server:

Select convert (varchar (100), getdate (), 0) -- 10 17 2010 4: 51 pmselect convert (varchar (100), getdate (), 1) -- 10/17/10 select convert (varchar (100), getdate (), 2) -- 10.10.17 select convert (varchar (100), getdate (), 3) -- 17/10/10 select convert (varchar (100), getdate (), 4) -- 17.10.10select convert (varchar (100), getdate (), 5) -- 17-10-10 select convert (varchar (100), getdate (), 6) -- 17 10 10 select convert (V Archar (100), getdate (), 7) -- 10 17, 10 select convert (varchar (100), getdate (), 8) -- 16: 52: 13 select convert (varchar (100), getdate (), 9) -- 10 17 2010 4: 52: 13: 960 pmselect convert (varchar (100), getdate (), 10) -- 10-17-10 select convert (varchar (100), getdate (), 11) -- 10/10/17 select convert (varchar (100), getdate (), 12) -- 101017 select convert (varchar (100), getdate (), 13) -- 17 10 2010 16: 53: 39: 403 select C Onvert (varchar (100), getdate (), 14) -- 16: 53: 39: 403 select convert (varchar (100), getdate (), 20) -- 16: 53: 39 select convert (varchar (100), getdate (), 21) -- 16:54:55. 100 select convert (varchar (100), getdate (), 22) -- 10/17/10 4:54:55 PM select convert (varchar (100), getdate (), 24) -- 16:54:55 select convert (varchar (100), getdate (), 25) -- 16:54:55. 100 select convert (varchar (100), getdate (), 100) -- 10 17 2010 PM select convert (varchar (100), getdate (), 101) -- 10/17/2010 select convert (varchar (100 ), getdate (), 102) -- 2010.10.17 select convert (varchar (100), getdate (), 103) -- 17/10/2010 select convert (varchar (100), getdate (), 104) -- 17.10.2010 select convert (varchar (100), getdate (), 105) -- 17-10-2010 select convert (varchar (100), getdate (), 106) -- 17 10 2010 select Conv ERT (varchar (100), getdate (), 107) -- 10 17,201 0 select convert (varchar (100), getdate (), 108) -- 16:56:36 select convert (varchar (100), getdate (), 109) -- 10 17 2010 4: 56: 36: 370pm select convert (varchar (100), getdate (), 110) -- 10-17-2010select convert (varchar (100), getdate (), 111) -- 2010/10/17 select convert (varchar (100), getdate (), 112) -- 20101017 select convert (varchar (100), getdate (), 113) -- 17 10 2010 16: 57: 51: 713 select convert (varchar (100), getdate (), 114) -- 16: 59: 19: 640 select convert (varchar (100 ), getdate (), 120) -- 2010-10-17 16: 59: 19 select convert (varchar (100), getdate (), 121) -- 16:59:19. 640 select convert (varchar (100), getdate (), 126) -- 2010-10-17t16: 59: 19.640 select convert (varchar (100), getdate (), 130) -- 10 ?? ?????? 1431 4: 59: 19: 640pm

Summary: MS is really considerate. What other formats does it help us implement?

III,Returns N records randomly from the table.

1. SQL Server

 
Select top 10 ID, firstname, lastnamefrom person (nolock) order by newid ()
 

2. Oracle

 
Select ID, firstname, lastname from (select ID, firstname, lastname from personorder by dbms_random.value () where rownum <= 10
 

3. DB2

Select ID, firstname, lastname from personorder by rand () Fetch first 10 rows only
 

4. MySQL

 
Select ID, firstname, lastname from personorder by rand () limit 10
 

5. PostgreSQL

 
Select ID, firstname, lastname from personorder by random () limit 10
 

Summary:

(1) comparing the SQL query writing methods of database products (DBMS), we can find that their similarities all need to use the order by clause to randomly sort rows, all random functions use their built-in functions. The interesting thing is that, although the random functions of each DBMS are similar or identical, no query is the same.

(2) The Oracle query method is slightly wordy and slightly less readable than other methods, but we can see its ideas and understand its implementation principles, which is very beneficial to developers.

 

IV,Convert null values to actual values

Generally, we can use case when to convert null values to actual values:

 
Select top 10id, firstname, case when lastname is null then ''else lastnameend as lastname fromperson (nolock)
 

However, a more concise way is to useCoalesceFunction:

Select top 10id, firstname, coalesce (lastname, '') as lastnamefromperson (nolock)
 

Finally, writing concise and efficient SQL statements in development has always been my goal. Every time I encounter hundreds or even thousands of rows of SQL statements or stored procedures that require maintenance, I have a headache, especially those with complicated business logic, I don't know if you have had such experiences in development. How can we avoid complicated SQL statements and systems that are easy to maintain in Stored Procedures? Do you have any ideas or good solutions? Welcome to the discussion.