查询:
(1)
SELECT COLUMN1,COLIMN2 FROM TB_NAME WEHRE ...
SELECT * ...... // *表示全查;
(2)
LIMIT N,M //表示从第N条记录开始查询连续M条记录,可实现分页功能;
SELECT * FROM TB_NAME LIMIT (pageNo - 1)*pageSize , pageSize
//pageNo表示页数,pageSize表示每页记录数;
(3)
联合查询:
JOIN属于内连接(Inner),关键字Inner可不写;
SELECT TB1_NAME.COLUMN1 FROM TB1_NAME JOIN TB2_NAME ON ... AND ...
SELECT TB1_NAME.COLUMN1 FROM TB1_NAME JOIN TB2_NAME ON ... AND ... JOIN TB3_NAME ON ... ...
//ON后为条件,条件中一般都有 “子表外键列==主表对应列名”
LEFT/RIGHT JOIN属于外链接(outer),关键字outer可不写;
SELECT TB1_NAME.COLUMN1 FROM TB1_NAME LEFT JOIN TB2_NAME ON ... AND ...;
SELECT TB1_NAME.COLUMN1 FROM TB1_NAME LEFT JOIN TB2_NAME ON ... AND ...
LEFT JOIN TB3_NAME ON ... ...;
//此时,以左边表为准,条件只对右边表有效,右边表若缺少与左边表对应数据则用null补齐;
RIGHT JOIN与LEFT JOIN相反;
(4)
GROUP BY +字段名:
SELECT * FROM TB_NAME GROUP BY COLUMN1;
//将该字段中重复数据合一,一般取第一行数据;
ORDER BY +字段名:
SELECT * FROM TB_NAME ORDER BY COLUMN1 ASC/DESC;
//默认ASC,即按该字段升序排列;DESC,按该字段降序排列;实现排序功能
COUNT(*):
SELECTCOUNT (*) FROM TB_NAME;
//该表中记录数,* 可加可不加;实现计数功能;最好加上,不然可能会出错