数据库MySql基础语句练习

客官°小女子只卖身不卖艺 2023-06-13 04:57 10阅读 0赞

\*\*

## 数据库MySql ##

\*\*-------------------------------------------------------------------------------------------------------------------------  
**– 一.单表查询**  
– 1.按部门求出该部门平均工资 分组查询  
SELECT AVG(sal) FROM emp GROUP BY deptno;  
– AVG() 为求平均数函数，输入将所求平均数的字段名  
– GROUP BY 为分组函数 此题是以部门来分组  
SELECT TRUNCATE (AVG(sal),0)‘平均工资’ FROM emp GROUP BY deptno;  
– TRUNCATE(列名，保留位数) 此函数保留数字的几位小数

--------------------

– 2.(继续)查询部门平均工资大于2000元的部门（分组之后的过滤和行过滤不一样分组之后的过滤使用having行过滤where）  
SELECT TRUNCATE(AVG(sal),0)‘平均工资’ FROM emp GROUP BY deptno HAVING TRUNCATE(AVG(sal),0)>2000;  
– HAVING 函数 它是在分组完成后可以再添加过滤条件

--------------------

– 3.(继续)按部门平均工资降序排列  
SELECT TRUNCATE (AVG(sal),0)‘平均工资’ FROM emp GROUP BY deptno ORDER BY TRUNCATE (AVG(sal),0) DESC;  
– ORDER RY 将查询出来的数据按给的列名进行排序，DESC为降序排列，不写默认是升序排列

--------------------

– 4.除10号部门外，查询部门平均工资大于2000元的部门  
– 方法一  
SELECT TRUNCATE(AVG(sal),0)‘平均工资’ FROM emp GROUP BY DEPTNO HAVING TRUNCATE(AVG(sal),0)>2000 AND deptno !=10  
– 这种方法先根据列名分组查询完后，再通过 HAVING函数过滤掉不需要的数据(数据多时效率低)  
– 方法二  
SELECT TRUNCATE(AVG(sal),0)‘平均工资’ FROM emp WHERE deptno !=10 GROUP BY DEPTNO HAVING TRUNCATE(AVG(sal),0)>2000  
– 这种方法是先通过where限制过滤掉不需要的数据，再进行分组查询效率较快（推荐使用）

--------------------

– 5.显示部门平均工资的最大值  
– 方法一  
SELECT TRUNCATE(AVG(sal),0)‘最高平均工资’ FROM emp GROUP BY deptno ORDER BY TRUNCATE(AVG(sal),0) DESC LIMIT 0,1;  
– 这种方法先分组查询，然后再按工资（列名）降序排列 然后再查询第一条数据（mysql的一种变通写法）  
– 方法二  
SELECT MAX(TRUNCATE(AVG(sal),0)) FROM emp GROUP BY deptno ;  
– 这种方法 是调用了max函数，但是mysql中使用会报错，oracle数据库可以使用

--------------------

– 6.显示部门平均工资的最大值和该部门编号?  
SELECT TRUNCATE(AVG(sal),0)‘最高平均工资’,deptno’部门编号’ FROM emp GROUP BY deptno ORDER BY TRUNCATE(AVG(sal),0) DESC LIMIT 0,1  
– 分组查询，降序排列，截取第一条，查询时再查一个部门编号列名（mysql此语句能运行，oracle数据库此语法行不通）

--------------------

**– 二.表的多表查询**  
– 7.查询员工的编号，姓名，部门名，使用表别名简化  
SELECT e.empno’员工编号’,e.ename’员工姓名’,d.dname’部门名称’ FROM emp e,dept d WHERE e.deptno=d.deptno;  
– 两个表联合查询 where条件必须是同一字段

--------------------

– 8.显示员工的编号，姓名，月薪，工资级别  
SELECT e.empno’员工编号’,e.ename’员工姓名’,e.sal’月薪’,s.grade’工资级别’ FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;  
– 查询工资级别用到 BETWEEN and 函数 ：相当于去一个区间 输入一个最小值和一个最大值包含在这个范围的数据

--------------------

– 9.按部门10,20,30,40号，统计各部门员工人数，要求显示部门号，部门名，人数  
– 多表内连接 根据条件只查询符合条件的数据  
SELECT d.deptno’部门编号’,d.dname’部门名’,COUNT(e.ename)‘部门人数’ FROM dept d, emp e WHERE d.`DEPTNO`= e.`DEPTNO` GROUP BY d.deptno;  
– 使用的外连接（左外链接） 根据一方的条件强制也查询另一方的数据  
SELECT d.deptno’部门编号’,d.dname’部门名’,COUNT(e.ename)‘部门人数’ FROM dept d LEFT JOIN emp e ON d.`DEPTNO`= e.`DEPTNO` GROUP BY d.deptno;

--------------------

– 10.显示每个员工的上级  
SELECT CONCAT(e1.ename,‘的上级是’,e2.ename) FROM emp e1 LEFT JOIN emp e2 ON e1.`MGR`=e2.`EMPNO`  
– 这是典型的使用外连接的案例，使用左外连接的最终的人是没有上级的，内连接查询就不会查询到最后一条(少一条数据)，  
– 外连接能查询出来最后一条只不过是null  
– 内连接查询：只能查询出符合条件的记录 只查询等值的部分要求显示两个或者多个表中都有的数据叫内连接 条件过滤使用where 表和表之间可以使用等号或者 and进行  
– 外连接查询：既能查询出符合条件的记录，也能根据一方强行将另一个方查询出来 条件过滤使用on 表和表之间使用left/right \[outer\] join xxx on 条件

--------------------

**– 三.子查询（当查询条件是未知事物的时候 考虑使用子查询）**  
– 11、查询员工薪资最高的员工信息  
SELECT \* FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);  
– 嵌套查询的方法先查询最大值然后再作为条件  
– 第一步：查询薪资最高的薪资  
– 第二步：根据查询的薪资进行条件匹配

--------------------

– 12、查询工资范海比高的员工信息  
SELECT \* FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=‘范海’);

--------------------

– 13、查询部门名为’市场’的员工信息  
– 方法一（子查询）  
SELECT \* FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname=‘市场’);  
– 方法二（多表查询）  
SELECT e.\* FROM emp e,dept d WHERE e.`DEPTNO`=d.`DEPTNO` AND d.`DNAME`=‘市场’;

--------------------

– 14、查询部门名为’市场’或’行政’的员工信息  
SELECT \* FROM emp WHERE deptno IN(SELECT DEPTNO FROM DEPT WHERE dname=‘市场’ OR dname=‘行政’);  
– 多行子查询 使用in 的关键字

--------------------

– 15、查询部门名不是’市场’或’行政’的员工信息  
SELECT \* FROM emp WHERE deptno NOT IN(SELECT deptno FROM dept WHERE dname=‘市场’ OR dname=‘行政’);  
– 多行子查询 使用not in关键字

--------------------

– 16、查询工资比20号部门【任意any】一个员工工资【低<】的员工信息  
SELECT \* FROM emp WHERE sal<ANY(SELECT sal FROM emp WHERE deptno=20 );  
– 使用any关键字的多行查询，<any就等于<集合中最大的那个值

--------------------

– 17、查询工资比30号部门【所有all】员工【低<】的员工信息  
SELECT \* FROM emp WHERE sal<ALL(SELECT sal FROM emp WHERE deptno=30);  
– 使用all关键字的多行查询 <all 就等于<集合中最小的那个值

--------------------

**– 四、集合查询**  
– 18、使用并集运算，查询20号部门或30号部门的员工信息  
SELECT \* FROM emp WHERE deptno=20  
UNION  
SELECT \* FROM emp WHERE deptno=30;  
– union：二个集合中，如果都有相同的，取其一  
– union all：二个集合中，如果都有相同的，都取

--------------------

– 19、使用交集运算\[intersect\]，查询工资在1000-2000和1500-2500之间的员工信息  
– 方法一  
SELECT \* FROM emp WHERE sal BETWEEN 1000 AND 2000  
intersect  
SELECT \* FROM emp WHERE sal BETWEEN 1500 AND 2500;  
– 方法二（使用where语句）  
SELECT \* FROM emp WHERE (sal BETWEEN 1000 AND 2000) AND (sal BETWEEN 1500 AND 2500;);

--------------------

– 20、使用差集运算\[minus\]，查询工资在1000-2000，但不在1500-2500之间的员工信息  
– 方法一  
SELECT \* FROM emp WHERE sal BETWEEN 1000 AND 2000  
minus  
SELECT \* FROM emp WHERE sal BETWEEN 1500 AND 2500;  
– 方法二（使用where语句）  
SELECT \* FROM emp WHERE (sal BETWEEN 1000 AND 2000) AND (sal NOT BETWEEN 1500 AND 2500);

--------------------

**– 集合查询的细节：**  
– 1）集合操作时，必须确保集合列数是相等  
– select empno,ename,sal,comm from emp where deptno = 20  
– union  
– select empno,ename,sal from emp where deptno = 30;错

– 2）集合操作时，必须确保集合列类型对应相同  
– select empno,ename,sal,comm from emp where deptno = 20  
– union  
– select empno,ename,sal,hiredate from emp where deptno = 30;错

– 3）A union B union C = C union B union A（交换律）  
– select \* from emp where deptno = 10  
– union  
– select \* from emp where deptno = 20  
– union  
– select \* from emp where deptno = 30;

– 4）当多个集合操作时，结果的列名由第一个集合列名决定  
– select empno “编号”,ename “姓名”,sal “薪水” from emp where deptno = 20  
– union  
– select empno,ename,sal from emp where deptno = 10;