"Code Learning" SQL statement design

1. Search criteria based on null value (NULL)

1 Select * from user where null; // query for all users with an age of NULL

2. Using in for range comparison query

1 Select *  from User whereIdinch('1','3','5');//The query user ID is 1,3, 5 of all users2 Select *  from User whereId not inch('1','3','5');//Query user ID is not 1,3, 5 of all users

3. Using between and for range comparison query

1 Select *  from User whereIdbetween 1  and 4;//Query ID for all users between 1 and 42 Select *  from User whereId not between 1  and 4;//Query ID is not available for all users between 1 and 4

4. Using like for fuzzy matching

Percent "%": denotes 0 or any number of characters

Underline "_": represents any single character

 1  select  *  from  user  where  username like   " li%      "; //  2  select  *  from  user  where  username like   "  Seven _ year%    "; // query query seven year 

5. Sort query results using ORDER BY

1 Select *  from User Order  byIdASC;//sort Ascending by ID2 Select *  from User Order  byId//sort Ascending by ID3 Select *  from User Order  byIddesc;//Sort in descending order by ID

6. Limit the number of result rows using limit

1 Select *  from User 5;      // Query Top 5 users 2 Select *  from User 0,5;    // query Top 5 users

7. Using Statistical functions

1 Select Count (*fromuser;      Number of records in // statistics

8. Use GROUP by to group query results

1 SelectAgeCount(*) from User Group  byAge//count the number of people in each age group in the user's table2 SelectAgeCount(*) from User Group  byAge having Count(*)> 1;//requires a total number of people greater than 1, having to filter the results after grouping3 SelectAgeCount(*) c from User Group  byAge havingC> 1;//Requires a total number of people greater than 1, having to filter the results after grouping

"Code Learning" SQL statement design