查询 / 修改指定条数记录
limit 关键字
-- 查找/修改头几条和指定条数记录
-- 修改头两条几率,序号从0到1的记录
UPDATE `user` set isvalid=1 limit 2;
-- 查询第一条isvalid不为1的记录
select * from user where isvalid <> 1 LIMIT 1;
-- 查询第一行到第四行的数据,同limit 4
select * from `user` LIMIT 0,4;
-- 查询第4到6行数据,索引在前(start,count)
SELECT * from student LIMIT 4,6;
-- 另一种写法,索引在后(count offset start)
SELECT * from student LIMIT 2 OFFSET 4;
判断空值
-- 选取email非空的记录
select * from student where !ISNULL(email);
select * from student where email is not null;
select * from student where not email is null;
注:可以用=null来更新某个字段为空,不能用=null来判断是否为空
-- temp表只插入1条记录,用以查询函数返回值
select ISNULL(''),null=null,isnull(null) from temp;
-- 也可以不用表直接查看
select ISNULL(''),null=null,isnull(null);
返回结果:0,null,1
排序
-- 年龄正向排序(默认asc可不写),身高反向排序
select * from student order by age,height desc;
-- 中文字段排序
select * from student order by convert(name using gbk)
分组
-- 查询每个班级男女人数
SELECT class,sex,count(*) from student group BY class,sex;
-- 查询每个班级女生人数(先分组再过滤)
SELECT class,sex,count(*) from student group BY class,sex having sex='女';
-- 另一种写法(先过滤再分组)
SELECT class,sex,count(*) from student where sex='女' group BY class;
注:where要与from配合使用,having要与group by配合使用,where要写在having之前
否则会报错:You have an error in your SQL syntax;
分页
-- 每页显示3条为例
-- 总记录数(记录总数10)
select count(*) from student;
-- 总页数 = (总记录数 + 每页数据大小 - 1)/每页数据大小
-- 每页选择条件 limit (pageNum-1)*pageSize,pageSize
-- 第一页
select * from student limit 0,3;
-- 第二页
select * from student limit 3,3;
-- 第三页
select * from student limit 6,3;
-- 第四页
select * from student limit 9,3;
【问题记录】
子查询不支持limit
UPDATE student set sex='女' where sid in
(SELECT s1.sid from student as s1 ORDER BY s1.height LIMIT 2);
报错:This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME
解决方法:子查询外再嵌套一层
UPDATE student set sex='女' where sid in
(SELECT tmp.sid from(SELECT s1.sid from student as s1 ORDER BY s1.height LIMIT 2) as tmp);
limit语句无法识别运算
select * from student limit (1-1)*3,3;
报错:You have an error in your SQL syntax;
解决方案:改用用动态语句
set @sql=concat('select * from student limit ',(1-1)*3,',3');
prepare page from @sql;
execute page;
order by 与 limit 的顺序问题
order by与limit的执行顺序是:先执行order by,然后执行limit
order by与limit的写顺序是:先写order by,再写limit
如下写法,会报错: You have an error in your SQL syntax;
SELECT * from student LIMIT 3 order by age;
正确写法
SELECT * from student order by age LIMIT 3;
批量更新指定行数据出错
只更新前N行没有问题
update student SET class='一班' LIMIT 3;
如果是指定从第m到第n则会报错
update student SET class='二班' LIMIT 4,6;
解决方法:改用in + limit 操作
update student SET class='二班' where sid in
(SELECT tmp.sid from (select * from student LIMIT 4,6) as tmp);
关于length(),char_length()函数的疑问
SELECT DISTINCT LENGTH('asd123'),LENGTH('中国'),LENGTH('中国asd123') FROM user;
得到的结果是:6,6,12
SELECT DISTINCT CHAR_LENGTH('asd123'),CHAR_LENGTH('中国'),CHAR_LENGTH('中国asd123') FROM user;
得到的结果是:6,2,8
length()返回字符串str的长度,以字节为单位,字母占用一个字节,中文占3个字节
char_length()返回字符串str的长度,以字符为单位。 多字节字符算作单个字符。
结论:若要查询某个字段长度超过3的记录,应该采用如下方式
select * from student where char_LENGTH(name)>=3;
或者
select * from student where name LIKE '___%';