mysql语句

Posted on Posted in mysql
Tips: 本文创建于2014年4月28日,已超过 2 年,内容或图片可能已经失效!

目录

group by

//count函数对null的记录不计数
select gender, count(*) from students group by gender;

统计函数

//count函数对null的记录不计数
select count(*) from studentinfo;
select sum(score) as total from students;
select avg(score) as avg from students;
select max(score) from students;
select min(score) from students;
//group_concat把此字段的记录的值拼接成一个字符串
select group_concat(name) from students;
select * from students where score in (select max(score) from students);
//group by 对查询结果进行分组
select gender,classname,count(*) from students group by gender,classname;
select gender,classname,count(*) from students group by gender desc,classname asc;

with rollup

//对查询的列(gender classname)进行统计,然后一起输出
select gender,classname,count(*) from students group by gender desc, classname asc with rollup;

having语句

作用:对查询结果进行再次过滤,常常和where进行组合
select * from students where score > 200 group by gender having id < 4; select gender,classname,count(*) as num from sutdents group by gender desc,classname asc having num > 1;

order by

select * from students order by score desc;
select * from students order by gender desc,classname;

limit

最大作用:分页
举例:limit 5, 10,从第5条开始,取10条
举例:limit 10 从第一条开始取10条
说明:记录的条数是从0开始计算的
select * from students limit 0,3;

SELECT 选项

选项:all | distinct
All是默认选项,保留所有查询得到的记录
Distinct 此选项将把重复的记录剔除掉

主键的增加与删除

主键如果有auto_increment属性时,是不允许直接删除的
alter table students modify id int unsigned,drop primary key;
alter table students add primary key (id);
alter talbe students modify id int unsigned auto_increment;

唯一索引

alter table students add unique key (name);
drop index name on students; //删除唯一索引

联合查询,union

联合查询要求两个查询语句的结果的列数要一样
select id,name,gender,classname from students where id > 5 union select * from studentinfo where classid is not null;
select studentname from studentinfo where classid is not null union select name from students where id <=5;

Union 选项
Distinct 去除重复记录,默认选项
All 保留所有的记录
union distinct = union
select studentname from studentinfo where classid is not null union all select name from students where id <=4;

» 转载请注明来源:若我若鱼 » mysql语句

Leave a Reply

Your email address will not be published. Required fields are marked *

eleven − 10 =