发布时间 : 星期日 文章銆婃暟鎹簱鎶�鏈強搴旂敤銆嬭绋嬪ぇ浣滀笟-2014_鍥炬枃. - 鐧惧害鏂囧簱更新完毕开始阅读2f37944ffd4ffe4733687e21af45b307e871f9fd
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;