mysqlselect查询

distinct 去重

举例:select distinct studentname from a;

between  and 范围查询

举例:select studentname from a where  num between 2 and 5;

in 集合查询

举例:select studentname from a where num in(2,3,4) ;

like 模糊查询

%匹配任意字符select studentname from a where studentname like '盛%' ;

_匹配任意单个字符select studentname from a where studentname like '_%' ;

如果想要查询的目标中有%或_使用escape进行转义

select studentname from a where studentname like 'a_' escape 'a';

使用正则表达式查询 rlike regexp

select studentname from a where studentname rlike '.*';

对查询结果排序 order by 升序asc 降序desc

select * from a order by num desc;

限制limit 第一个表示从第几个开始第2个表示选出几个

select * from a order by num desc limit 2,3;

聚合查询

count 返回行数 select count(*) from a;

max 最大select max(num) from a

min 最小select min(num) from a;

sum 求和select sum(num) from a;

avg 平均 select avg(num) from a ;

分组聚合查询 主要使用 group by having

select sex ,count(num) from a group by sex;

having 在分组后按条件查询不能用where

select num,sex,studentname from a group by num having studentname like '_';

复制insert into copy select num,sex,studentname from a;

replace插入

删除记录

1delete from

2truncate 表名（直接删除在新建表）