銆婃暟鎹�搴撴妧鏈�鍙婂簲鐢ㄣ�嬭�剧▼澶т綔涓�-2014_鍥炬枃. - 鐧惧害鏂囧簱

5.1嵌套查询

实例1:查询与李明一个专业的同学 SELECT name,stu_id,major From student WHERE major IN( SELECT major From student WHERE name='李明';

实例2:查询与数据库系统概论属于相同类别的图书SELECT book_id,title,category,press,price From book

WHERE category IN( SELECT category

From book

WHERE category='计算机';

5.2连接查询

实例1:查询所有信息管理与电子政专业学生借的图书SELECT name,major,title From book,student,borrow

WHERE student.major='信息管理与电子政务'AND student.stu_id=borrow.stu_id AND

book.book_id=borrow.book_id;

实例2:查询所有小说当前的借阅记录

SELECT title,name,major,borrow_date,return_date From book,student,borrow WHERE book.category='小说'AND book.book_id=borrow.book_id;

5.3聚合查询

实例1:求各个类别图书借出数量的总数

SELECT category as类别,COUNT(distinct nameas总数From book,borrow,student

WHERE student.stu_id=borrow.stu_id AND book.book_id=borrow.book_id GROUP BY category;

实例2:求各个类别图书借出书籍的总价钱

SELECT category as类别,SUM(distinct priceas总价From book,borrow,student WHERE student.stu_id=borrow.stu_id AND book.book_id=borrow.book_id GROUP BY category;