4、查询（1）

一、写法

• select  *  from  表名；　　（G）
• select  col1，col2，col3  from  表名   where  id=1；
• select cid as id,caption as c from class；只影响结果集，不影响原始集（物理上）
• select cid,caption ,1 from class; 结果集中多一列，列名及内容都是1

mysql> select * from lx.class; 　　#没有选择数据库
+-----+----------+
| cid | caption  |
+-----+----------+
|   1 | 材料一班 |
|   2 | 材料二班 |
|   3 | 材料三班 |
|   4 | 任丘     |
|   5 | 李三     |
+-----+----------+

 where后：

• and、or、not   值  is   值
• > 、 <、  !=、  =
• （not）in  （1，2，5）或in （select  col  from  表）
• between  3  and  8注：闭区间

偏移：可用于分页

mysql> select cid as id,caption as c from class;
+----+----------+
| id | c        |
+----+----------+
|  1 | 材料一班 |
|  2 | 材料二班 |
|  3 | 材料三班 |
|  4 | 任丘     |
|  5 | 李三     |
+----+----------+

mysql> select * from class limit 3;　　#前3行
+-----+----------+
| cid | caption  |
+-----+----------+
|   1 | 材料一班 |
|   2 | 材料二班 |
|   3 | 材料三班 |
+-----+----------+

mysql> select * from class limit 1,3;　　#第一行后的三行数据
+-----+----------+
| cid | caption  |
+-----+----------+
|   2 | 材料二班 |
|   3 | 材料三班 |
|   4 | 任丘     |
+-----+----------+

mysql> select * from class limit 3 offset 1;　　#同上
+-----+----------+
| cid | caption  |
+-----+----------+
|   2 | 材料二班 |
|   3 | 材料三班 |
|   4 | 任丘     |
+-----+----------+

排序：

mysql> select * from students order by id ;　　#默认是升序或...order  by  id  asc
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   | 
      | 1991-01-01 00:00:00 |          |
|  3 | ee     |        | 2017-01-01 00:00:00 | 
        |
|  4 | mike   |        | NULL                |          |
|  5 | ldh    | 
      | 2016-05-09 00:00:00 | 
        |
|  6 | res    |        | 2016-08-09 00:00:00 |          |
+----+--------+--------+---------------------+----------+
mysql> select * from students order by id desc;　　#降序
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  6 | res    |        | 2016-08-09 00:00:00 |          |
|  5 | ldh    | 
      | 2016-05-09 00:00:00 | 
        |
|  4 | mike   |        | NULL                |          |
|  3 | ee     |        | 2017-01-01 00:00:00 | 
        |
|  1 | 鲤鱼   | 
      | 1991-01-01 00:00:00 |          |
+----+--------+--------+---------------------+----------+

消除重复行：

select distinct * from students ;　　#注意区分重复行和不重复行的区别

条件：select *  from  表名  where  条件；where后条件为真的行显示在结果集中

逻辑运算符：not，and，or

#接上
mysql> select * from students where id>3 and not birthday is null;
+----+------+--------+---------------------+----------+
| id | name | gender | birthday            | isDelete |
+----+------+--------+---------------------+----------+
|  5 | ldh  | 
      | 2016-05-09 00:00:00 | 
        |
|  6 | res  |        | 2016-08-09 00:00:00 |          |
+----+------+--------+---------------------+----------+

模糊查询：like，%，_

like：关键字

%：匹配任意多个任意字符

_：匹配一个任意字符

mysql> select * from students where name like 'e%';
+----+------+--------+---------------------+----------+
| id | name | gender | birthday            | isDelete |
+----+------+--------+---------------------+----------+
|  3 | ee   |        | 2017-01-01 00:00:00 | 
        |
+----+------+--------+---------------------+----------+

范围查询：in，between....and....

in：非连续范围

between...and...：连续范围

mysql> select * from students where id in(1,3,6);
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   | 
      | 1991-01-01 00:00:00 |          |
|  3 | ee     |        | 2017-01-01 00:00:00 | 
        |
|  6 | res    |        | 2016-08-09 00:00:00 |          |
+----+--------+--------+---------------------+----------+

mysql> select * from students where id between 2 and 5;　　#原始集中没有id=2
+----+------+--------+---------------------+----------+
| id | name | gender | birthday            | isDelete |
+----+------+--------+---------------------+----------+
|  3 | ee   |        | 2017-01-01 00:00:00 | 
        |
|  4 | mike |        | NULL                |          |
|  5 | ldh  | 
      | 2016-05-09 00:00:00 | 
        |
+----+------+--------+---------------------+----------+

空判断：is null，is  not  null

注意：null不同于‘’

mysql> select * from students where birthday  is null;
+----+------+--------+----------+----------+
| id | name | gender | birthday | isDelete |
+----+------+--------+----------+----------+
|  4 | mike |        | NULL     |          |
+----+------+--------+----------+----------+

mysql> select * from students where birthday  is not null;
+----+--------+--------+---------------------+----------+
| id | name   | gender | birthday            | isDelete |
+----+--------+--------+---------------------+----------+
|  1 | 鲤鱼   | 
      | 1991-01-01 00:00:00 |          |
|  3 | ee     |        | 2017-01-01 00:00:00 | 
        |
|  5 | ldh    | 
      | 2016-05-09 00:00:00 | 
        |
|  6 | res    |        | 2016-08-09 00:00:00 |          |
+----+--------+--------+---------------------+----------+

优先级：小括号，not，比较运算符，and，or