此博客续小M开发_MySQL零基础_day170628。
下面我们将对这两张表进行操作:
emp
表
dept表
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
GROUP BY 语句
GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法
语法:
SELECT COLLS_1 , COLLS_2 ,FUNCTION(?)....FROM TABLE_NAME GROUP BY COLLS
example:
/***************day20170629*****************************/
/**对分组的过滤语法:select col1,col2,col3,...FROM table_name WHERE 条件 GROUP BY col having 条件(可以是分组函数)*/
SELECT *,COUNT(*) FROM emp GROUP BY deptno; /*以deptno分组,并显示每一组的人数*/
SELECT*FROM emp;
查询结果:
SQL INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT * FROM TABLE_NAME1 INNER JOIN TABLE_NAME2
example:
/**表链接*/
/*SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
*INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName*/
SELECT emp.job ,dept.dname FROM emp;
INNER JOIN dept ON emp.deptno = dept.deptno; /*INNER JOIN table_name ON ;这里我们用deptno链接了emp,和dept两个表*/
/*面是SQL的新写法,等同于上面。*/
SELECT emp.job ,dept.dname FROM emp , dept WHERE emp.deptno=dept.deptno;/*SQL升级语句*/
查询结果:
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法:
SELECT * FROM TABLE_NAME1 LEFT JOIN TABLE_NAME2
example:
/*
INNER JOIN:只能查出两个表都用的
*LEFT JOIN:左表有的右表没得
*RIGHT JOIN:右表有的左表没得
*/
SELECT *FROM dept LEFT JOIN emp
ON emp.deptno = dept.deptno;/*dept中有deptno为40的字段,但是emp中没有,所以deptno为40连接表后为空*/
查询结果:
SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
RIGHT JOIN 关键字语法
SELECT * FROM TABLE_NAME1 RIGHT JOIN TABLE_NAME2
example:
SELECT *FROM emp right JOIN dept
ON emp.deptno = dept.deptno;/*dept中有deptno为40的字段,但是emp中没有,所以deptno为40连接表后为空*/
example连表分组查询(先连接表在分组查询):
/*连接表分组查询*/
SELECT dept.deptno AS '部门编号',SUM(sal) AS '总工资',COUNT(empno) AS '部门人数',dept.dname AS '部门名字' FROM emp
INNER JOIN dept ON emp.deptno=dept.deptno
GROUP BY dept.deptno;
查询结果:
HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
example:
/***/
/****HAVING后面可以加函数****************/
SELECT xin.deptno AS '部门编号',dept.dname AS '部门名字',xin.sum AS'总和' FROM
(SELECT deptno ,SUM(sal) AS sum FROM emp GROUP BY deptno) AS xin
INNER JOIN dept ON dept.deptno=xin.deptno
HAVING xin.sum between 1000 AND 7000;
查询结果:
SQL LIMIT关键字
LIMIT 关键字会在表中指定返回某些的行。
LIMIT 关键字语法:
SELECT * FROM TABLE_NAME1 LIMIT POS( 起始位置 ) , LEN(查找多少条)
example:
/**分页**/
/*语法:select * from emp LIMIT int1,int2*/
SELECT * FROM emp ORDER BY sal LIMIT 4,5 ;/*当前页码*页码容量-1,页面容量*/
查询结果:
代码笔记:
/***************day20170629*****************************/
/**对分组的过滤语法:select col1,col2,col3,...FROM table_name WHERE 条件 GROUP BY col having 条件(可以是分组函数)*/
SELECT *,COUNT(*) FROM emp GROUP BY deptno; /*以deptno分组,并显示每一组的人数*/
SELECT*FROM emp;
/**表链接*/
/*SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName*/
SELECT emp.job ,dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; /*INNER JOIN table_name ON ;这里我们用deptno链接了emp,和dept两个表*/
/*
INNER JOIN:只能查出两个表都用的
*LEFT JOIN:左表有的右表没得
*RIGHT JOIN:右表有的左表没得
*/
SELECT *FROM dept LEFT JOIN emp ON emp.deptno = dept.deptno;/*dept中有deptno为40的字段,但是emp中没有,所以deptno为40连接表后为空*/
SELECT *FROM emp right JOIN dept ON emp.deptno = dept.deptno;/*dept中有deptno为40的字段,但是emp中没有,所以deptno为40连接表后为空*/
SELECT * FROM dept;
SELECT * FROM emp,dept WHERE dept.deptno=emp.deptno; /*SQL升级语句*/
SELECT emp.job ,dept.dname FROM emp,dept WHERE dept.deptno=emp.deptno;
SELECT e.job ,d.dname FROM emp AS e,dept AS d WHERE d.deptno=e.deptno;/*去个别名链接*/
/**/
SELECT deptno AS '部门编号',SUM(sal) AS '总工资',COUNT(empno) AS '部门人数' FROM emp GROUP BY deptno;
SELECT dept.deptno AS '部门编号',SUM(sal) AS '总工资',COUNT(empno) AS '部门人数',dept.dname AS '部门名字' FROM emp
INNER JOIN dept ON emp.deptno=dept.deptno
GROUP BY dept.deptno;
SELECT dept.deptno,dept.dname FROM emp ,dept WHERE emp.deptno=dept.deptno GROUP BY dept.deptno;
SELECT xin.deptno AS '部门编号',dept.dname AS '部门名字',xin.sum AS'总和' FROM (SELECT deptno ,SUM(sal) AS sum FROM emp GROUP BY deptno) AS xin INNER JOIN dept ON dept.deptno=xin.deptno;
/****HAVING后面可以加函数****************/
SELECT xin.deptno AS '部门编号',dept.dname AS '部门名字',xin.sum AS'总和' FROM
(SELECT deptno ,SUM(sal) AS sum FROM emp GROUP BY deptno) AS xin
INNER JOIN dept ON dept.deptno=xin.deptno
HAVING xin.sum between 1000 AND 7000;
SELECT * FROM dept;
/*
*mysql:利用关键字limit
*sqlserver:利用关键字top
*oracle:利用关键字rownum
*/
/**分页**/
/*语法:select * from emp LIMIT int1,int2*/
SELECT * FROM emp ORDER BY sal LIMIT 4,5 ;/*当前页码*页码容量-1,页面容量*/
SELECT * FROM emp WHERE ename IN( 'SMITH','WARD');