mysql数据库—表的查询排序操作大全
表的查询操作
select * from 表 *效率低
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1 as相当于取别名,别名为gg
select count(id) from userinfo5; 查询表内有多少条数据
select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';
select name,age,11 from tb12; 多加了一列11
其他:
select * from tb12 where id != 1
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11)
select * from tb12 where id between 5 and 12; 闭区间
通配符:
select * from tb12 where name like "a%" 以a开头的所有(多个字符)
select * from tb12 where name like "a_" 以a开头的所有(一个字符)
分页:
select * from tb12 limit 10;
select * from tb12 limit 0,10; 0开始位置行,10查看的行数
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20; 20开始位置行,10查看的行数
排序:
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc; 按优先级安序排列
取后10条数据
select * from tb12 order by id desc limit 10;
分组:
select count(id),part_id from userinfo5 group by part_id; 统计数量
select max(id),part_id from userinfo5 group by part_id; part_id重合时,id取max
select nin(id),part_id from userinfo5 group by part_id;
聚合函数(去除重复)
count
max
min
sum
avg 平均值
**** 如果对于聚合函数结果进行二次筛选时?必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作:
select * from userinfo5,department5 where userinfo5.part_id = department5.id
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
# 左连接:userinfo5右边允许有空
select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# 右连接:department5左边允许有空
select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id
# 内连接:不会出现null
select * from userinfo5 full join department5 on userinfo5.part_id = department5.id
# 完全连接:左右都可能出现null
g、组合(上下连表)
组合,自动处理重合
select nickname from A union select name from B
组合,不处理重合
select nickname from A union all select name from B