一、写法
- 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