数据库中多表查询
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/*查询数据库两表的所有数据*/
select * from users as u,role as r where u.roleid=r.id;
/*查询数据库两表的总数量*/
SELECT count(*) from users as u,role as r where u.roleid=r.id;
/*查询users表中的userName字段值和role表中roleCoad字段和roleName字段*/ SELECT erName,r.roleCode,r.roleName from users as u,role as r where u.roleid=r.id;
/*内连接(inner join)查询语句，连接的条件使用on而不是where*/
SELECT erName,r.roleCode,r.roleName from users as u inner join role as r on u.roleid=r.id;
/********************************外*连*接***********************************/
/*left join左外连接*/
SELECT erName,r.roleCode,r.roleName from users as u Left join role as r on u.roleid=r.id;
/* right join右外连接。有连接时左连接的反向连接，如左表中没有相匹配的行左表中将返回空值*/
SELECT u.id,erName,r.id,r.roleCode,r.roleName from users as u right join role as r on u.roleid=r.id;
/**/
SELECT u.id,erName,u.roleid,r.id,r.roleCode,r.roleName from users as u inner join role as r on u.roleid=r.id and u.roleid=1;
/*按role表中的id进行排序*/
SELECT u.id,erName,u.roleid,r.id,r.roleCode,r.roleName from users as u inner join role as r on u.roleid=r.id order by r.id;