哈喽，大家好！我是保护小周ღ，本期为大家带来的是 MySQL的常用函数，统计函数，分组查询和过滤，以及子查询，更多相关知识敬请期待：保护小周ღ *★,°*:.☆(￣▽￣)/$:*.°★*
一、函数
1.1 常用函数（了解）
--数学运算SELECTABS(-8) --绝对值SELECTCEILING(9.4) --向上取整SELECTFLOOR(9.4) --向下取整SELECTROUND(3.1415926,2) --重要SELECTrand() --生成一个随机数--字符串函数SELECTCHAR_LENGTH('你好') --字符串长度SELECTCONCAT('你','好') --字符串拼接SELECTLOWER('HELLO') --字符串转换成小写SELECTUPPER('hello') --字符串转换成大写--时间和日期函数SELECTCURRENT_DATE() --获取当前日期--重要SELECTSYSDATE() --获取当前日期时间
1.2 统计函数|聚合函数|分组函数（常用）(掌握)
--简单使用SELECTSUM(`sal`) AS总和FROMempSELECTAVG(`sal`) AS平均值FROMempSELECTMAX(`sal`) AS最大值FROMempSELECTMIN(`sal`) AS最小值FROMempSELECTCOUNT(`sal`) AS计数FROMemp; --同时显示多个SELECTSUM(`sal`) 和, AVG(`sal`) 平均值, MAX(`sal`) 最大值, MIN(`sal`) 最小值, COUNT(`sal`) 个数FROMemp; --聚合函数支持哪些类型SELECTSUM(ename), AVG(ename) FROMempSELECTSUM(hiredate), AVG(hiredate) FROMempSELECTMAX(ename),MIN(ename) FROMempSELECTMAX(hiredate),MIN(hiredate) FROMempSELECTCOUNT(ename) FROMemp; SELECTCOUNT(comm) FROMemp; --count() SELECTCOUNT(mgr) fromemp; --Count(指定列，字段)，会忽略所有的null值--本质都是计算行数，count*把所有列都计算一遍，count1是把所有的行都变成1返回一个结果SELECTCOUNT(*) fromemp; --不会忽略null值SELECTCOUNT(1) fromemp; --不会忽略null值
二、 分组查询和过滤(掌握)
--查出所有员工的平均工资selectavg(sal) fromemp--我想根据不同的部门看一下不同部门的平均工资
group by 字句语法
select列（要求出现在groupby的后面），分组函数from表where筛选条件groupby分组的列表orderby字句--注意：查询列表比较特殊，要求是分组函数和groupby后出现的字段
--简单的分组查询--查出每个工种的最高工资SELECTjob,MAX(sal) fromempGROUPBYjob; --查出每个部门的人数SELECTdeptno, COUNT(*) fromempGROUPBYdeptno--查询每个岗位的平均工资SELECTjob,avg(sal) FROMscott.empGROUPBYjob; --查询每个部门中每个岗位的人数和平均工资SELECTdeptno,job,count(*),avg(sal) FROMscott.empGROUPBYdeptno,job; --分组前查询--查出每个岗位名称带E的人数个数SELECTjob,count(*) FROMscott.empWHEREenamelike'%E%'GROUPBYjob--查询除去财务部门外不同部门名称的最高薪资，最低薪资，平均薪资SELECTdname, MAX(sal), MIN(sal),AVG(sal) fromdeptdINNERJOINempeond.deptno=e.deptnowheredname!='财务'GROUPBYd.dname--分组后查询--查询哪个部门的员工个数<6SELECTdeptno, count(*) fromempGROUPBYdeptnoHAVINGCOUNT(*) <6--查询每个部门的人数，只显示4人以上的部门SELECTdeptno,count(*) FROMscott.empGROUPBYdeptnoHAVINGcount(*)>4; --查询每个岗位中名字包含E的员工人数，只显示超过2人的岗位和人数SELECTjob,count(*) FROMscott.empWHEREenamelike'%E%'GROUPBYjobHAVINGcount(*)>2; --分组后查询，查询不同部门名称的最高薪资，最低薪资，平均薪资，平均薪资大于900SELECTdname, MAX(sal),MIN(sal),AVG(sal）fromdeptdINNERJOINempeond.deptno=e.deptnoGROUPBYd.dnameHAVINGMIN(sal) >900--总结：--聚合函数count、sum、avg、max、min等不能在where子句中使用。--where语句先执行过滤，having语句后执行过滤--WHERE后面不允许写分组函数，HAVING后面不允许写未分组的字段--查询除了财务部门外不同部门的最高薪资，最低薪资，平均薪资且平均薪资大于900SELECTdname, MAX(sal), MIN(sal),AVG(sal) fromdeptdINNERJOINempeond.deptno=e.deptnowheredname!='财务'GROUPBYd.deptnoHAVINGMIN(sal) >900--查询出各个分组里面的工作人员SELECTdname, GROUP_CONCAT(ename) fromdeptdINNERJOINempeond.deptno=e.deptnoGROUPBYd.dname
三、子查询(掌握)
- 1.一般来说，连接查询都可以用子查询替换。
- 2. 相对于表连接，子查询适合于作为查询的筛选条件。
- 3. where(值是固定的，这个值是计算出来的)
- 4. 本质：==在where语句中嵌套一个查询语句==
--子查询|嵌套查询--执行顺序由里到外--子查询返回一个值--innerjoin展示两个表的表头信息，子查询展示一个表的信息--查询销售的所有员工(员工编号，员工姓名) SELECTempno, ename, dnamefromempeINNERJOINdeptdone.deptno=d.deptnowhered.dname='销售'--查询销售的所有员工(员工编号，员工姓名) SELECTempno, enamefromempewheredeptnoin (SELECTdeptnofromdeptwheredname='销售') --查询在广州工作的员工中，哪些人的名字中包含有E，要求显示员工姓名和部门编号SELECTename, deptnofromempwheredeptno= (selectdeptnofromdeptwhereloc='广州') andenamelike'%E%'--查询SMITH的领导姓名SELECTenameFROMscott.empWHEREempno=(SELECTmgrFROMscott.empWHEREename='SMITH'); --查询编号是7521,7566,7654,7698,7782的员工，哪些是BLAKE的下属SELECT*FROMempWHEREempnoin (7521,7566,7654,7698,7782) andmgr= (selectempnofromempwhereename='blake') --查询KING的下属哪位在上海工作，要求显示员工编号，员工姓名，岗位，领导工号，部门编号SELECTempno, ename, job, mgr, deptnoFROMempWHEREdeptno= (selectdeptnofromdeptwhereloc='上海') andmgr= (selectempnofromempwhereename='king') --子查询返回多个值--查询北京和上海的所有员工信息SELECT*FROMscott.empWHEREdeptnoin(SELECTdeptnoFROMscott.deptWHERElocin('北京','上海')); --查询KING的工资占公司总工资的比例SELECT (SELECTsalFROMscott.empWHEREename='KING')/(SELECTsum(sal) FROMscott.emp) FROMdual; SELECTsal/(SELECTsum(sal) FROMscott.emp) FROMscott.empWHEREename='KING';
