入门MySQL语句(2)
查询num行数:
select count(num) from info;
查询num去重复后的行数:
1.select instinct num from info; (给num去重)
2.select count (instinct num) from info; (查询去重后num的行数)
查找数据最大值和最小值:
select max(字段名) as 字段名MAX,min(字段名) as 字段名MIN;
计算平均值:select avg(score) from info;
计算总值:select sum(score) from info;
group by:按照...进行分组
select num,sname,gender group by gender; (按照gender进行分组)
加一列:alter table info add column 字段名 数据类型(字节);
减一列:alter table info drop column 字段名;
创建自增列:
create table info(id int auto_increament primary key,sname char(8));
两表连接:
首先要求两个表相引用的字段数据类型和约束相同
alter table stu add constraint foreign key(sid) references info(num);
两表拼接:
select * from info inner join stu on info.num=stu.sid; 两表相对应的列