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;

select id, username from user1 where (id < 5) OR (id > 90);
select id, username from user1 where id between 5 and 90;            # [5, 90]
select id, username from user1 where id not between 5 and 90;        # ![5, 90]
select id, username from user1 where id not in (1, 2, 3, 4, 5);

select id, username from user1 where id is not null;
select id, username from user1 where id is null;

• 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  |
  

---

select * from user1 where id like ‘1__‘;

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

select * from user1 where username like ‘test___‘;

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

select * from user1 where username like ‘test1%‘;

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

• order
  

  select id, username from users order by username;
  select id, username from users order by username asc, id asc;
  select id, username from users order by id desc;
  

  默认是 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 table user1;
  
  insert into user1 (id, username) values (1, ‘test‘);
  insert into user1 (id, username) values (1, ‘test‘);
  insert into user1 (id, username) values (1, ‘test‘);
  insert into user1 (id, username) values (1, ‘test‘);
  
  select distinct * from user1;
  

sql example 8 -- select

标签：