不等于:<>
判断为空的条件:null和空格(空字符串)
判断是否为null:xxxx is not null / xxxx is null
判断null:
SELECT * FROM student WHERE address IS NULL ;
判断空字符串:
SELECT * FROM student WHERE address='';
包括null和空字符串:
SELECT * FROM student WHERE address IS NULL OR address='';
模糊查询:like
SELECT * FROM score WHERE sname LIKE '大%';(%控制多个字符)
SELECT * FROM score WHERE sname LIKE '大_';(_控制一个字符)
SELECT * FROM score WHERE sname LIKE '__';(两位字符)
聚合查询:sum(求和)avg(平均数)max(最大值)min(最小值)count(总记录数)
SELECT SUM(js) FROM score WHERE sclass='j1018';
SELECT AVG(html) FROM score WHERE sclass='JAVA-1018';
SELECT MIN(html+js+jquery) FROM score;
SELECT COUNT(*) FROM score;(统计总共有做少数据:每一列都count一下,取最大值)
判空函数
IFNULL(html,0);
SELECT MIN(IFNULL(html,0)+js+jquery) FROM score;
分页查询
limit 起始行,查询几行【limit(当前页-1*每页显示的条数),每页显示的条数】
SELECT * FROM score LIMIT 0,2;(从第1页开始查询,查询两行)
SELECT * FROM score LIMIT 2,2;(从第3页开始查询,查询两行)
查询排序(order by)写在最后
正序 ase;倒序 desc;
默认按正序
SELECT * FROM score ORDER BY js ASC; 正序
SELECT * FROM score ORDER BY js DESC; 倒序
多个排序条件
SELECT * FROM score ORDER BY js DESC,jquery ASC;(以js为主,jquery为次,当主排序数值相同时,执行次排序)
分组查询(group by)----- 先分组,后查询
SELECT sex FROM score GROUP BY sex;(按sex分组)
SELECT sex,COUNT(*) FROM score GROUP BY sex;(按sex分组,并且显示有多少列)
SELECT sex,SUM(js) FROM score GROUP BY sex;(查询每个性别的js的总成绩)
分组前查询、筛选 (where)
SELECT sex,SUM(js) FROM score WHERE sclass='java' GROUP BY sex;(查询java班每个性别的js总成绩)
分组后查询、筛选(having)
SELECT sex,SUM(js) FROM score GROUP BY sex HAVING SUM(js)>60;(查询哪个性别的js总成绩大于60)
SELECT sex FROM score GROUP BY sex HAVING COUNT(*)>1;(查询那个性别的人数大于1)