mysql基础语句练习

-- 条件查询
SELECT * FROM `student` WHERE score<60;
-- 不匹配查询    !=   <>都是不等于
SELECT * FROM `student` WHERE class <> 2;
-- 范围检查
SELECT * FROM `student` WHERE score BETWEEN 70 AND 80;
-- 空值检查
SELECT * FROM `student` WHERE score is NULL
-- 组合条件语句
SELECT * FROM `student` WHERE score > 70 AND class = 2;
-- OR语句测试
SELECT * FROM `student` WHERE class = 1 OR class=3;
-- in语句测试
SELECT * FROM `student` WHERE score in ('22','99','88','77');
-- not语句的使用
SELECT * FROM `student` WHERE NOT class = 1;
-- %通配符测试 不区分大小写
SELECT * FROM `student` WHERE subjecta LIKE 'a%'
SELECT * FROM `student` WHERE namea LIKE '%b%'
-- 下划线通配符，只匹配一个字符
SELECT * FROM `student` WHERE namea LIKE 'aabbc_';
-- 方括号通配符[] 
SELECT * FROM `student` WHERE namea LIKE '[ab]%';
-- upper的使用
SELECT namea,UPPER(namea),subjecta FROM `student`;
-- 各种文本处理函数
-- 日期查询
SELECT * FROM `student` WHERE joinschool < '2014-01-01';
-- 聚合函数   求某列行数
SELECT COUNT(namea) FROM student;
-- 聚合函数   求某列最大值
SELECT MAX(score) FROM student;
-- 聚合函数   求某列最小值
SELECT MIN(score) FROM student;
-- 聚合函数   求某列平均值  AVG函数忽略值为NULL的行
SELECT AVG(score) FROM student;
-- 聚合函数   求某列和
SELECT SUM(score) FROM student;
-- 分组查询
SELECT COUNT(namea),subjecta FROM student GROUP BY subjecta;
-- where过滤列，having过滤分组
-- where在分组前进行过滤
-- having在分组后进行过滤
SELECT COUNT(namea) p,subjecta FROM student WHERE subjecta <> '物理' GROUP BY subjecta HAVING p>1 ORDER BY p;
-- 子查询  mysql4.1版本开始支持 子查询通常是在where条件中的in操作符里面显示的

-- 关联表练习集
SELECT * FROM student s,province p WHERE s.wai = p.sid;
-- 左外连接
SELECT * FROM student s LEFT JOIN province p ON s.wai = p.sid; 
-- 右外连接
SELECT * FROM student s RIGHT JOIN province p ON s.wai = p.sid;