数据库的查询与统计

——JY数据库查询与统计的使用——-

1、SELECT用法

<!--查询数据表制定列-->
1.USE JY
  GO
  SELECT reader_name, reader_department
  FROM reader
  GO

<!--取别名显示-->
2.USE JY
  GO
  SELECT reader_name AS '姓名', reader_department'院系'
  FROM reader
  GO

<!--返回查询结果的前n(%)行-->
3.USE JY
  GO
  SELECT TOP 5 *
  FROM book
  GO

<!--消除查询结果的重复行-->
4.USE JY
  GO
  SELECT DISTINCT book_publisher
  FROM book
  GO

<!--在查询结果中增加要显示的字符串-->
5.USE JY
  GO
  SELECT '总借阅次数:',SUM(interview_times)
  FROM book
  GO

<!--使用聚合函数-->
6.USE JY
  GO
  SELECT COUNT(*)
  FROM reader
  GO

2、WHERE子句的使用

<!--使用LIKE进行模糊查询-->
1.USE JY
  GO
  SELECT *
  FROM reader
  WHERE reader_name LIKE '李%' --模糊查询
  GO

<!--查询列值为空的数据行-->
2.USE JY
  GO
  SELECT *
  FROM record
  WHERE notes IS NULL
  GO

3、使用ORDER BY子句重新排序查询的结果

<!--查询并且由低到高排序-->
1.USE JY
  GO
  SELECT *
  FROM book
  ORDER BY interview_times DESC
  GO

4、使用GROUP BY子句分组或统计查询结果

<!--统计出版社的出版图书的总数-->
1.USE JY
  GO
  SELECT book_publisher, COUNT(book_publisher) AS '图书总数'
  FROM book
  GROUP BY book_publisher
  GO

<!--查询图书借阅次数在20次以上，而且出版图书总数大于1的出版社-->
2.USE JY
  GO
  SELECT book_publisher
  FROM book
  WHERE interview_times > 20
  GROUP BY book_publisher HAVING COUNT(book_publisher) > 1
  GO

<!--查询读者表中专业的男、女人数-->
3.USE JY
  GO
  SELECT reader_department, reader_sex, COUNT(*) AS '人数'
  FROM reader
  GROUP BY reader_department, reader_sex
  GO

5、使用子查询

<!--使用比较运算符的子查询-->
1.USE JY
  GO
  SELECT reader_name
  FROM reader
  WHERE reader_id
  <>(SELECT reader_id
    FROM record
    WHERE book_id = 'b0005')
  GO

<!--使用IN关键字的子查询-->
2.USE JY
  GO
  SELECT reader_name
  FROM reader
  WHERE reader_id
  IN(SELECT reader_id
    FROM record
    GROUP BY reader_id HAVING COUNT(reader_id) >=2)
  GO

<!--使用ANY、SOME、ALL关键字的子查询-->
3.USE JY
  GO
  SELECT book_id
  FROM book
  WHERE interview_times
  *any(SELECT MAX(interview_times) FROM book
        UNION
        SELECT MIN(interview_times) FROM book)
  GO
<!--ANY与SOME类似,接在一个比较运算符后面,只要满足其中一个条件就返回TRUE。ALL表示都满足才返回TRUE-->

<!--使用EXISTS关键字查询-->
4.USE JY
  GO
  SELECT reader_id, reader_name
  FROM reader
  WHERE EXISTS
    (SELECT *
    FROM record
    WHERE reader_id = reader.reader_id AND book_id = 'b0001')
  GO

6、子查询

<!--IN嵌套查询-->
1.USE JY
  GO
  SELECT book_name, book_author, book_publisher
  FROM book
  WHERE book.book_id IN
    (SELECT record.book_id
    FROM record
    WHERE record.reader_id = 'r0006')
  GO

<!--比较查询-->
2.USE JY
  GO
  SELECT book_name, book_author, book_publisher
  FROM book
  WHERE book_price >
    (SELECT AVG(book_price)
    FROM book
    WHERE book_publisher = '电子工业出版社')
  GO

<!--查询借阅次数大于40和图书编号为'b0006'的图书书名-->
3.USE JY
  GO
  SELECT book_name
  FROM book
  WHERE interview_times > 40
  UNION
  SELECT book_name
  FROM book
  WHERE book_id = 'b0006'
  GO

7、连接查询

<!--将所有的情况按record_id(默认右边的)进行排序列出(不包括NULL值)-->
1.USE JY
  GO
  SELECT book.book_id, record.reader_id, record.borrow_date
  FROM book JOIN record ON book.book_id = record.book_id
  GO

<!--将所有的情况按book_id(声明最左边的)进行排序列出(包括NULL值)-->
2.USE JY
  GO
  SELECT book.book_id, record.reader_id, record.borrow_date
  FROM book LEFT JOIN record ON book.book_id = record.book_id
  GO