sql example 8 -- select

user1; # 清空数据库 select * from user1; insert into user1 (id, username) values (0, ‘test0‘); insert into user1 (id, username) values (1, ‘test1‘); insert into user1 (id, username) values (2, ‘test2‘); insert into user1 (id, username) values (3, ‘test3‘); insert into user1 (id, username) values (4, ‘test4‘); insert into user1 (id, username) values (5, ‘test5‘); insert into user1 (id, username) values (6, ‘test6‘); insert into user1 (id, username) values (7, ‘test7‘); insert into user1 (id, username) values (8, ‘test8‘); insert into user1 (id, username) values (9, ‘test9‘); insert into user1 (id, username) values (100, ‘test100‘); insert into user1 (id, username) values (111, ‘test111‘); select * from user1;

<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> (id < 5) <span style="color: #00ffff;">OR</span> (id > 90);
<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">between</span> 5 <span style="color: #00ffff;">and</span> 90;            # [5, 90]
<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">not</span> <span style="color: #00ffff;">between</span> 5 <span style="color: #00ffff;">and</span> 90;        # ![5, 90]
<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">not</span> <span style="color: #00ffff;">in</span> (1, 2, 3, 4, 5);

<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">is</span> <span style="color: #00ffff;">not</span> <span style="color: #00ffff;">null</span>;
<span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">is</span> <span style="color: #00ffff;">null</span>;

• LIKE 通配符
  

  _: 单个字符
  %: 0 个或者多个
  

  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    |
  | 100 | test100  |
  | 111 | test111  |
  

---

<span style="color: #00ffff;">select</span> * <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> id <span style="color: #00ffff;">like</span> <span style="color: #ffa07a;">‘1__‘</span>;

| id  | username |
+-----+----------+
| 100 | test100  |
| 111 | test111  |

<span style="color: #00ffff;">select</span> * <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> username <span style="color: #00ffff;">like</span> <span style="color: #ffa07a;">‘test___‘</span>;

| id  | username |
+-----+----------+
| 100 | test100  |
| 111 | test111  |

<span style="color: #00ffff;">select</span> * <span style="color: #00ffff;">from</span> user1 <span style="color: #00ffff;">where</span> username <span style="color: #00ffff;">like</span> <span style="color: #ffa07a;">‘test1%‘</span>;

| id  | username |
+-----+----------+
|   1 | test1    |
| 100 | test100  |
| 111 | test111  |

• order
  

  <span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> users <span style="color: #00ffff;">order</span> <span style="color: #00ffff;">by</span> username;
  <span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> users <span style="color: #00ffff;">order</span> <span style="color: #00ffff;">by</span> username <span style="color: #00ffff;">asc</span>, id <span style="color: #00ffff;">asc</span>;
  <span style="color: #00ffff;">select</span> id, username <span style="color: #00ffff;">from</span> users <span style="color: #00ffff;">order</span> <span style="color: #00ffff;">by</span> id <span style="color: #00ffff;">desc</span>;
  

  默认是 asc (升序), 降序 (DESC)
  

• limiit
  
  • php 分页的实现
    

  limit 3         (只显示 3 位数)
  limit 10, 10    (返回排名 11 到 20)
  limit 1, 1      (表示第二位)
  

• 别名
  

  select id as userId, username as userName from user;
  select id, username from user;                        # 两个字段
  select id username from user;                         # 一个字段, username 是 id 的别名
  select id as username from user;                      # 一个字段, 别名
  

• distinct
  

  truncate <span style="color: #00ffff;">table</span> user1;
  <span style="color: #00ffff;">insert</span> <span style="color: #00ffff;">into</span> user1 (id, username) <span style="color: #00ffff;">values</span> (1, <span style="color: #ffa07a;">‘test‘</span>);
  <span style="color: #00ffff;">insert</span> <span style="color: #00ffff;">into</span> user1 (id, username) <span style="color: #00ffff;">values</span> (1, <span style="color: #ffa07a;">‘test‘</span>);
  <span style="color: #00ffff;">insert</span> <span style="color: #00ffff;">into</span> user1 (id, username) <span style="color: #00ffff;">values</span> (1, <span style="color: #ffa07a;">‘test‘</span>);
  <span style="color: #00ffff;">insert</span> <span style="color: #00ffff;">into</span> user1 (id, username) <span style="color: #00ffff;">values</span> (1, <span style="color: #ffa07a;">‘test‘</span>);
  <span style="color: #00ffff;">select</span> <span style="color: #00ffff;">distinct</span> * <span style="color: #00ffff;">from</span> user1;
  

sql example 8 -- select

标签：