Mysql查询数据第二讲之单表查询

查询所有字段

SELECT * from bookinfo （mysql默认对大小写不敏感）

查询指定字段

SELECT bookTitle from bookinfo

SELECT bookTitle,bookId from bookinfo

查询指定记录

SELECT * from bookinfo WHERE bookId =1

SELECT * from bookinfo WHERE bookId <=10

SELECT * from bookinfo WHERE bookId !=10

1 带IN关键字查询

SELECT * from bookinfo WHERE bookId IN (10,13)

SELECT * from bookinfo WHERE bookTitle NOT IN (‘跨界’,’疯传’)
（字符需要用单引号标注）

2 带BETWEEN AND范围查询

SELECT * from bookinfo WHERE bookId BETWEEN 10 AND 13

3 带LIKE字符匹配查询

SELECT * from bookinfo WHERE bookTitle LIKE ‘%跨%’

SELECT * from bookinfo WHERE bookTitle NOT LIKE ‘跨%’
“%”表示模糊匹配，表示其他字符

4 查询空值

SELECT * from bookinfo WHERE bookPrices IS NULL
注意这里面的”IS”不能修改为“=”，否则的话无法查询

5 带AND的多条件查询

SELECT * from bookinfo WHERE bookPrices IS NULL AND bookId = 176

6 带OR的多条件查询

SELECT * from bookinfo WHERE bookPrices IS NULL OR bookId = 12

7 查询结果不重复

SELECT DISTINCT bookPrices from bookinfo WHERE bookPrices <100000

8 对查询结果排序

SELECT * FROM bookinfo ORDER BY bookId DESC
升序降序分别是ASC和DESC

9 限制数量

SELECT * FROM bookinfo LIMIT 3

SELECT * FROM bookinfo LIMIT 2，4（初始位置，记录数）
注意第一条数据的下标是0，所以这里其实是第三到第七条记录

关于单表查询的知识点大部分就到这里啦，可能有人会问？WTF?GROUP BY怎么没有说？不要着急，我们到下一节集合函数中再深入了解啦！