实例表结构:
+----------+-------------+------+-----+-----------------------+----------------+
| Field | Type | Null | Key | Default | Extra
|+----------+-------------+------+-----+-----------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment || sex | tinyint(4) | NO | | 1 | || username | varchar(20) | YES | | NULL | || age | tinyint(4) | NO | | 18 | || userinfo | varchar(50) | NO | | 我是帅气的lucky老师啊 |
|+----------+-------------+------+-----+-----------------------+----------------+
>
将id大于5 的性别 更改为0 年龄改为20岁
update user set sex=0,age=20 where id>5;<
将id小于3 的性别 更改为0 年龄改为23岁
查看id小于4的 性别和用户名的字段数据
select sex,username from user where id<4;
update user set sex=0,age=23 where id<3;>=
删除 id大于等于6的数据
delete from user where id>=6;<=
查询年龄小于等于23的数据
select * from user where age<=23;!=/<>
查询 用户名不等于lucky的所有数据
select * from user where username!='lucky'; select * from user where username<>'lucky';select * from user where age between 18 and 20;
4.等同于
select * from user where age>=18 and age<=20;
5.not between and 不在...之间
查询年龄不在18~20之间的所有数据
select * from user where age not between 18 and 20;
6.等同于
select * from user where age<18 or age>20;
7.in 在...里
查询 年龄在18,20的数据
select * from user where age in(18,20);
8.等同于
select * from user where age=18 or age=20;
9.not in 不在...里
查询 年龄在18,20的数据
select * from user where age not in(18,20);
等同于
select * from user where age!=18 and age!=20;
升序
查询数据 按照年龄升序(默认)
select * from user order by age;
select * from user order by age asc;
查询数据 按照年龄降序
select * from user order by age desc;
结构:
limit x 取出x条数据
limit x,y 从x的位置取出y条数据
取出3条数据
select * from user limit 3;
取出年龄最大/最小的一条数据
select * from user order by age desc limit 1;
select * from user order by age limit 1;
从0开始取出3条数据
select * from user limit 3; 等同于 select * from user limit 0,3;
分页实例:
数据一共100条
每页10条数据
第一页 limit 0,10
第二页 limit 10,10
第三页 limit 20,10
公式:
(nowpage-1)*10
查询username为null的数据
select * from user where username is null;
查询username不为null的数据
select * from user where username is not null;
注意:
因为null是特殊的值 所有不能使用 = 或者 !=进行查询
select distinct userinfo from user;
select distinct 字段名 from 表名;
select * from 表名 where 字段名 in(SQL语句)
实例:
select * from user where age in(select age from user where sex=0);