MYSQL知识点复习

发布于 2021-04-15  681 次阅读

×

1.消除重复

DISTINCT

select distinct 字段名 from empt;
字段名相同的就去除，保留一个
多个字段，多个字段都相同才会去重

2.算数运算符

select sal*12 from empt; //通过月工资查询年薪
select sal*12 '年薪' from empt;//使用别名

3.别名

列的 SQL 别名语法

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

注意：别名不能作为非空判断

select *,(comm+sal)*12 as 'money' from emp WHERE (comm+sal)*12;
  //正确

select *,(comm+sal)*12 as 'money' from emp WHERE  'money'; //错误

4.过滤查询

select * from dept where binary dname='selas' //binary大小写敏感

5.ifnull

ifnull(99,10)  如果第一个参数为null，就取第二个值，不为null取第一个值
select * from emp WHERE  ISNULL(COMM);   //查询COMM是null的
select * from emp WHERE  COMM;   //查询COMM是有值的

6.like匹配字符串 -单个字符，%多个字符

select * from emp where sal between 2000 and 4000;
select * from emp where 日期 like '1981%';
select * from emp where emp where 字段 like 'A%';
select * from emp where emp where 字段 like '_M%';
select * from emp where sal not between 2000 and 4000;

7.排序

asc 升序 缺省

desc 降序

order by 出现在select之后查询

select * from emp order by sal;//sal不加引号，加引号能查询，但没效果
select * from emp order by deptno ,(sal*12); 
select * from emp order by deptno DESC,(sal*12) DESC;

8.连接

内连接

外连接

左连接

右连接

on后面接连接条件

9.分组函数

函数分类：

单行函数：将每条数据进行独立的计算，然后每条数据得到一条结果；
多行函数：多条数据同时计算，最终得到一条结果数据。也成为聚集函数、分组函数，主要用于完
成一些统计功能等等。

多行函数：

AVG()：求出平均值；
SUM()：求和；
MAX()：求出最大值；
MIN()：求出最小值。
统计函数忽略空值

10.分组查询

group by

不能在 WHERE 子句中对分组限定，限制组须使用 HAVING 子句；
不能在 WHERE 子句中使用统计函数，而在 HAVING 子句可使用统计函数。

SELECT YEAR(HIREDATE) , COUNT(EMPNO)
FROM emp
GROUP BY YEAR(HIREDATE)
HAVING y BETWEEN '1980' AND '1982'
SELECT MGR, AVG(SAL), MIN(SAL) AS minsal
FROM emp
GROUP BY MGR
HAVING MGR IS NOT NULL AND minsal >= 1300

11.单行函数

  • NOW()：获取当前时间；
  • DAY(date)：获取日期中的天数，范围是从 1 到 31；
  • HOUR(time)：返回 time 对应的小时数，范围是从 0 到 23；
  • MINUTE(time)：返回 time 对应的分钟数，范围是从 0 到 59；
  • MONTH(date)：返回 date 对应的月份，范围时从 1 到 12；
  • YEAR(date)：返回 date 对应的年份，范围是从 1000 到 9999；
  • LAST_DAY(date)：获取一个日期或日期时间值，返回该月最后一天对应的值。

12.练习

-- 查询所有员工的年薪((月薪 + 奖金) * 12)
select (sal+IFNULL(comm,0))*12 'money' from emp;
-- 查询有奖金的员工信息
select * from emp WHERE COMM;
-- 查询公司的老板
select * from emp WHERE ISNULL(mgr);
-- 查询出基本工资高于 1500 的所有员工信息
SELECT * FROM emp WHERE SAL > 2000
-- 查询名字叫 SCOTT 的员工所从事的工作
SELECT JOB FROM emp WHERE ENAME = 'scott'
-- 查询 1981 年入职的员工信息
SELECT  * FROM emp WHERE YEAR(HIREDATE) = 1981
-- 查询年薪小于 3W 的员工
SELECT * FROM emp WHERE (sal+IFNULL(comm,0))*12 < 30000
-- 查询所有不是销售人员的员工信息
SELECT * FROM emp WHERE JOB != 'SALESMAN'
-- 查询工资在 2000-3000 之间的员工信息
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000
-- 查询 1981 年入职的员工
SELECT  ENAME FROM emp WHERE YEAR(HIREDATE) = 1981
-- 查询工资为 800 或 1600 或 3000 的员工
SELECT  ENAME FROM emp WHERE SAL = 800 or SAL = 1600 or SAL = 3000
-- 查询出所有雇员姓名是以 A 开头的全部雇员信息。
SELECT  * FROM emp WHERE ENAME LIKE 'A%'
-- 查询出雇员姓名第二个字母是 M 的全部雇员信息。
SELECT  * FROM emp WHERE ENAME LIKE '_M%'
-- 查询出雇员姓名任意位置上包含字母 A 的全部雇员信息。
SELECT  * FROM emp WHERE ENAME LIKE '%A%'
-- 查询姓名中有 e 或者 a 的员工姓名
SELECT  ENAME FROM emp WHERE ENAME LIKE '%e%' OR ENAME LIKE '%a%'
-- 查询工资在 1500~3000 之间的全部员工信息
SELECT  * FROM emp WHERE SAL BETWEEN 1500 AND 3000
-- 查询工资不在 2000-3000 之间的员工信息
SELECT  * FROM emp WHERE SAL NOT BETWEEN 1500 AND 3000
-- 查询工资不为 800 或 1600 或 3000 的员工
SELECT  * FROM emp WHERE SAL != 800 AND  SAL != 1600 AND SAL != 3000
-- 查询出职位是办事员 (CLERK) 或者是销售人员 (SALESMAN) 的全部信息，且工资在 1000 以上
SELECT  * FROM emp WHERE (JOB = 'CLERK' OR  JOB = 'SALESMAN') AND SAL > 1000
-- 查询所有员工信息，按照工资排序
SELECT  * FROM emp ORDER BY SAL DESC
-- 查询所有员工信息，按照年薪降序排序
SELECT  * FROM emp ORDER BY (sal+IFNULL(comm,0))*12 DESC
SELECT  *,(sal+IFNULL(comm,0))*12 money FROM emp ORDER BY money DESC
-- 查询所有员工信息，按照部门和年薪降序排序
SELECT  * FROM emp ORDER BY DEPTNO DESC, (sal+IFNULL(comm,0))*12 DESC
-- 查询员工编号，员工名称，员工所属部门的编号和名称
SELECT  EMPNO,ENAME,emp.DEPTNO,dept.DNAME FROM emp,dept
-- 查询员工的姓名，工资，所在部门的名称，以及工资的等级
SELECT ENAME,SAL,dept.DNAME,salgrade.GRADE FROM emp,dept,salgrade WHERE emp.DEPTNO=dept.DEPTNO AND SAL BETWEEN salgrade.LOSAL AND salgrade.HISAL
-- 查询所有员工每个月的平均工资及总工资
SELECT AVG(SAL),SUM(SAL+COMM) FROM emp
-- 查询月薪在 2000 以上的员工总人数
SELECT  COUNT(*) FROM emp WHERE (sal+IFNULL(comm,0))>2000
-- 查询员工最高工资和最低工资差距
SELECT  MAX(SAL)-MIN(SAL) FROM emp
-- 按照职位分组，求出每个职位的最高和最低工资
SELECT  JOB,MAX(SAL),MIN(SAL) FROM emp GROUP BY JOB
-- 查询出每一个部门员工的平均奖金
SELECT  dept.DNAME,AVG(IFNULL(COMM,0)) FROM emp INNER JOIN dept ON emp.DEPTNO=dept.DEPTNO GROUP BY dept.DNAME
-- 查询出每一个部门员工的平均工资
SELECT  dept.DNAME,AVG(IFNULL(sal,0)) FROM emp INNER JOIN dept ON emp.DEPTNO=dept.DEPTNO GROUP BY dept.DNAME
-- 查询各个部门和岗位的平均工资
   SELECT DEPTNO,JOB,AVG(IFNULL(SAL,0)) FROM emp GROUP BY DEPTNO,JOB
-- 查询部门平均工资高于 2000 的部门及其平均工资
SELECT DEPTNO,AVG(IFNULL(SAL,0)) money FROM emp GROUP BY DEPTNO HAVING AVG(IFNULL(SAL,0))>2000
-- 查询在 80, 81, 82 年各进公司多少人
SELECT YEAR(HIREDATE) y, COUNT(EMPNO) FROM emp GROUP BY YEAR(HIREDATE) HAVING y BETWEEN '1980' AND '1982'
-- 查询各个管理人员下员工的平均工资，其中最低工资不能低于 1300，不计算老板
SELECT MGR, AVG(SAL), MIN(SAL) AS minsal FROM emp GROUP BY MGR HAVING MGR IS NOT NULL AND minsal >= 1300
-- 查询出工资比 MARTIN 还要高的全部雇员信息
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'MARTIN')
-- 查询平均工资高于公司平均工资的部门信息
SELECT e.deptno, d.dname, AVG(sal)
FROM emp e JOIN dept d ON e.deptno = d.deptno
GROUP BY deptno HAVING AVG(sal) >= (SELECT AVG(sal) FROM emp)
-- 查询工资等于部门经理（职位是 MANAGER）的员工信息。
SELECT * FROM emp WHERE SAL IN (SELECT SAL FROM emp WHERE JOB='MANAGER') AND JOB != 'MANAGER'

欢迎欢迎~热烈欢迎~