数据库查询

like字句

以某某开头

select * from member WHERE regName like "小%"

以某某结尾

select * from member WHERE regName like "%小"

包含某某

select * from member WHERE regName like "%小%"

限制字符长度(一个下划线代表一个字符)

select * from member WHERE regName like "小_"

---

范围查询

20000到30000之间（包含关系）

SELECT * FROM member WHERE leaveAmount BETWEEN 20000 and 30000

不在某某之间

SELECT * FROM member WHERE leaveAmount NOT BETWEEN 20000 and 30000

查询所有的中产阶级

注：leaveAmount BETWEEN 20000 and 30000满足条件返回1，不满意返回0
SELECT regName,leaveAmount,leaveAmount BETWEEN 20000 and 30000 FROM member

---

去重

SELECT DISTINCT memberID FROM invest ORDER BY memberID desc

---

分组group by

select memberID,AVG(amount),max(amount),min(amount),sum(amount) from invest GROUP BY memberID

分页

limit m n
m：从m+1条记录开始取；
n：代表要查询多少记录

偏移量 offset

注：取10条数据，从0开始偏移
select * from member LIMIT 10 OFFSET 0

---

连接查询

-- is not null 非空查询
-- is null 查询为空的

内连接-inner join

显示左表及右表符合连接条件的记录

select * from girls INNER JOIN boys on girls.matchNum=boys.matchNum

using:使用using关联两个表必须要有共同的字段

select * from girls inner join boys USING(matchNum)

左外连接-left join

显示左表的全部记录及右表符合连接条件的记录，右表不符合条件的显示null

SELECT * from girls LEFT JOIN boys on girls.matchNum=boys.matchNum

右外连接-right join

显示右表的全部记录及左表符合连接条件的记录，左表不符合条件的显示null
SELECT * from girls RIGHT JOIN boys on girls.matchNum=boys.matchNum