SQL Example 8--Select

<title>SQL Example 8–select</title> SQL Example 8–select

• SQL Create
  
• SQL Delete
  

TruncateTableUser1; # Clear DatabaseSelect* fromUser1;Insert  intoUser1 (ID, username)Values(0,' Test0 ');Insert  intoUser1 (ID, username)Values(1,' test1 ');Insert  intoUser1 (ID, username)Values(2,' Test2 ');Insert  intoUser1 (ID, username)Values(3,' Test3 ');Insert  intoUser1 (ID, username)Values(4,' test4 ');Insert  intoUser1 (ID, username)Values(5,' Test5 ');Insert  intoUser1 (ID, username)Values(6,' Test6 ');Insert  intoUser1 (ID, username)Values(7,' Test7 ');Insert  intoUser1 (ID, username)Values(8,' Test8 ');Insert  intoUser1 (ID, username)Values(9,' Test9 ');Insert  intoUser1 (ID, username)Values(100,' test100 ');Insert  intoUser1 (ID, username)Values(111,' test111 ');Select* fromUser1;

SelectID, username fromUser1where(ID < 5)OR(ID > 90);SelectID, username fromUser1whereIdbetween5 and90; # [5, 90]SelectID, username fromUser1whereId not between5 and90; # ! [5, 90]SelectID, username fromUser1whereId not inch(1, 2, 3, 4, 5);

Select  from where  is  not null; Select  from where  is null;

• Like wildcard characters
  

  _: Single character%: 0 or more

  SELECT * from user1;| ID  | username |+-----+----------+|   0 | Test0    | |   1 | Test1    | |   2 | Test2    | |   3 | Test3    | |   4 | Test4    | |   5 | Test5    | |   6 | Test6    | |   7 | Test7    | |   8 | Test8    | |   9 | Test9 | | | |    test100  | | 111 | test111  |

Select  from where  like ' 1__ ';

| ID  | username |+-----+----------+| | test100  | | 111 | test111  |

Select  from where  like ' test___ ';

| ID  | username |+-----+----------+| | test100  | | 111 | test111  |

Select  from where  like ' test1% ';

| ID  | username |+-----+----------+|   1 | test1 | | | |    test100  | | 111 | test111  |

• Order
  

  Select  from Order  by username; Select  from Order  by ASC ASC; Select  from Order  by desc;

  The default is ASC (ascending), descending (DESC)
  

• Limiit
  
  • Implementation of PHP paging
    

  Limit 3         (3 digits only) limit, ten    (return to rank 11 to) limit 1, 1      (for second digit)

• Alias
  

  Select ID as userId, username as username from User;select ID, username from user;                        # Two fields Select ID username from user;                         # A field, username is the alias of the ID select ID as username from user;                      # a field, alias

• Distinct
  

  TruncateTableUser1;Insert  intoUser1 (ID, username)Values(1,' Test ');Insert  intoUser1 (ID, username)Values(1,' Test ');Insert  intoUser1 (ID, username)Values(1,' Test ');Insert  intoUser1 (ID, username)Values(1,' Test ');Select distinct* fromUser1;

SQL Example 8--Select