Python34-02_数据库----数据库条件查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Python34-02_数据库----数据库条件查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1879字，纯文字阅读大概需要3分钟。

内容图文

数据库条件查询

1. 分页查询

1. limit 查询

select * from students limit 5;

2. limit分页查询(页面+ 显示个数)

select * from students limit 0, 2;

(从小到大排列)select * from students order by age desc, limit 0, 2;

2. 聚合函数

1. count()计算总数

select count(*) from students;

2. 聚合函数, 默认跳过null值

select count(ifnull(height, 0)) from students where gender = 1;

3. max, min,sum, avg 最大值, 最小值, 求和, 平均值

select max(age) from students;

4. round() 四舍五入

select round(avg(age) ,2) from students;

3. 分组查询

1. group by(按类分组)

select gender from students group by gender;

2. group by + group_concat()  (按类分组, 并查询其他属性)

select gender, group_concat(name) from students group by gender;

3. group by + 聚合函数

select gender, count(age) from students group by gender;

4. group by + having (having相当于where, 用于group by中)

select gender, group_concat(name) from students group by gender having gender in (1, 2);

5. group by + with rollup  (在最后的记录后面新增一行, 计算总数和统计结果)

select gender, count(*) from students group by gender with rollup;

4. 连接查询

1. 内连接

select * from students inner join classes;

select s.name, c.name fromstudents as s inner join classes as c on s.cls_id = c.id;

(不使用inner join表示内连接) select s.name, c.name from students s, classes c where s.cls_id = c.id;

2. 左连接

select * from students s left join classes c where s.cls_id = c.id;

3. 右连接

select * from students s right join classes c where s.cls_id = c.id;

4. 自连接

select a.id, a.title from areas a inner join areas c on a.id = c.id where c.title = '陕西省';

select r.id, r.title from areas r inner join area.a on r.id = a.id where a.title = '西安市';

5. 子查询

(查出高于平均身高的信息)  select * from students where height > (select avg(height) from students);

5. 外键(为字段添加约束)

alter table students add foreign key(cls_id) references classes(id);

内容总结

以上是互联网集市为您收集整理的Python34-02_数据库----数据库条件查询全部内容，希望文章能够帮你解决Python34-02_数据库----数据库条件查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端