以下练习命令:
SELECT * FROM dang.`d_book` GROUP BY publishing;
SELECT publishing,GROUP_CONCAT(author) FROM dang.`d_book` GROUP BY publishing;
结果限定 limit
SELECT * FROM dang.`d_category` WHERE turn=1 LIMIT 0,5;
SELECT * FROM dang.`d_category` WHERE turn=1 LIMIT 2,4;
聚合函数;计数,求和,求平均值,最大值,最小值
SELECT COUNT(*),SUM(dang_price),AVG(dang_price),MAX(dang_price),MIN(dang_price) FROM dang.`d_product`;
SELECT product_name,dang_price FROM dang.`d_product` ORDER BY dang_price ASC LIMIT 0,1;
查询工作岗位是manager的员工的最高薪水是多少
SELECT MAX(sal) FROM bjpowernode.`emp` WHERE job='manager';
查询每种工作岗位的最高薪水分别是多少
SELECT job,MAX(sal) FROM bjpowernode.`emp` GROUP BY job;
查询工作岗位的最高薪水超过2500的有哪些工作岗位
SELECT job,MAX(sal) FROM bjpowernode.`emp` WHERE sal>2500 GROUP BY job ;
having对分组结果再次筛选,不能单独使用
SELECT job,MAX(sal) FROM bjpowernode.`emp` GROUP BY job HAVING MAX(sal)>2500;
单表查询练习:
1、取得薪水最高的第6到第10名员工信息
2、取得最早入职的5名员工信息
3、取得平均薪水大于2000的工作岗位
4、列出各个部门clerk岗位的最低薪水
5、列出所有员工的年收入,并按从高到底排序
SELECT * FROM emp ORDER BY sal DESC LIMIT 5,5;
SELECT * FROM emp ORDER BY hiredate LIMIT 0,5;
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING MAX(sal)>2000;
SELECT deptno,MIN(sal) FROM emp WHERE job='clerk' GROUP BY deptno;
SELECT ename,(sal + IFNULL(comm,0))*12 AS p FROM emp ORDER BY p DESC;