mysql主键操作
删除表主键:
alter table student drop primary key;
增加表主键:
alter table student add primary key(id);
重新设置主键自增(前提是需要先执行删除表主键语句):
alter table student modify id bigint(20) auto_increment primary key;
验证是否重名
加上limit 1 就是告诉mysql 找到即刻返回,不浪费后面的时间
select count(*) from student where name = 'tom' limit 1;
group by语句:按条件分成不同的组
新建customer表
id
name
cost
1
tom
1000
2
jojnny
2000
3
tom
350
4
bruce
730
5
mark
3000
6
ethan
2300
7
bruce
1740
查询每个客户花费的总额:
select name, sum(cost) from customer group by customer;
查询的结果为:
name
sum(cost)
bruce
2470
ethan
2300
johnny
2000
mark
3000
tom
1350
order by语句:按条件顺序排列
按花费从小到大查询
select * from customer order by cost;
查询结果为
id
customer
cost
3
tom
350
4
bruce
730
1
tom
1000
7
bruce
1740
2
johnny
2000
6
ethan
2300
5
mark
3000
group by order by组合:按分组条件分组后,再按排序条件排序
按客户总花费从小到大排列
select name, sum(cost) from customer group by customer order by sum(cost);
查询结果为:
name
sum(cost)
tom
1350
johnny
2000
ethan
2300
bruce
2470
mark
3000
distinct语句:去除重复项
查询出所有的客户名字
select distinct name from customer;
查询结果为:
name
tom
johnny
bruce
mark
ethan