SQL--DQL

最新推荐文章于 2024-07-03 11:49:36 发布

code good

最新推荐文章于 2024-07-03 11:49:36 发布

阅读量125

点赞数 1

分类专栏： sql 文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/weixin_62901109/article/details/126329573

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

DQL

一：简单查询

①：DISTINCT关键字(用于DISTINCT后面的指定字段值全部重复的去重)

SELECT DISTINCT job FROM emp;
SELECT DISTINCT job , empno FROM emp; #即当job和empno重复时才去重

②：使用别名字段和表

SELECT
	e.empno,
	e.ename,
	e.sal * 12 year_salary, # 起别名year_salary
	e.sal / 30 day_salary, # 起别名day_salary
	'元' salary unit # 起别名salary unit
FROM emp e;   #给表emp起别名为e

③：sql字符串拼接(||)

SELECT
	'雇员编号是: '||e.empno||'雇员姓名是：'||e.ename||'基本薪资是：'||e.sal||'元' emp_info #起别名emp_info
FROM emp e;

二：限定查询

①：BETWEEN AND

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000; #工资在1000和2000间

②：IN（相当于or）

SELECT * FROM emp WHERE deptno IN(10,20); #部门在10和20的员工信息

③：判断空(NULL)

SELECT * FROM emp WHERE comm IS NULL; #判断奖金为null的员工信息

④：模糊查询(LIKE)

1.% : 表示匹配任意多个字符（0个或多个）

2._ : 表示匹配一个任意字符，用于限定查询字符串的长度

SELECT * FROM emp WHERE ename NOT LIKE '%M%'; #员工名字不包含M信息
SELECT * FROM emp WHERE ename LIKE '______%'; #员工名字长度大于等于6

⑤：综合条件查询

#需求：找出部门10中所有经理，部门20中所有业务员，和既不是经理又不是业务员但工资大于2000的员工信息，并且要求这些员工的姓名中包含       字母H和K
#条件一：deptno =10 AND job = 'manager'
#条件二：deptno =20 AND job = 'clerk'
#条件三：job NOT IN ('manager','clerk') AND sal > 2000
#条件四：ename LIKE '%s%' OR ename LIKE '%K%'
#(条件一 or 条件二 or 条件三) AND 条件四
SELECT * FROM emp
WHERE ((deptno =10 AND job = 'manager')
      OR (deptno =20 AND job = 'clerk')
      OR (job NOT IN ('manager','clerk') AND sal > 2000)
      AND (ename LIKE '%s%' OR ename LIKE '%K%'));

三：排序查询

ORDER BY（DESC 降序，ASC 升序）默认升序

#需求：查询员工信息先按照工资升序 再按照日期降序
SELECT * FROM emp ORDER BY sal ASC,hiredate DESC;

四：单行函数（输入是一行，输出也是一行）

1.字符串函数

①：LOWER（将字符串出转成小写）, UPPER（将字符串转成大写）, INITCAP(首字母大写其余小写)

SELECT ename,LOWER(ename) lower_ename,INITCAP(ename) initcap_ename FROM emp;

②：REPLACE

SELECT ename,REPLACE(ename,'A','_') ename2 FROM emp;# 将ename中的A替换位_

③：LENGTH

SELECT * FROM emp WHERE LENGTH(ename) = 5;# 查询名字长度为5的函数

④：SUBSTR（0和1效果一样都表示第一个字符）（字符串，截取开始下标，【截取长度】）

SELECT ename ,SUBSTR(ename , LENGTH(ename)-2) FROM emp;# 查询名字后三个字母

⑤：INSTR（找出子字符串在父字符串的位置）

SELECT * FROM emp WHERE SUBSTR(job,INSTR(job,'MAN'),3)='MAN';# 查询job中包含man的员工信息

⑥：RPAD 和 LPAD （如果字符串长度不够进行左右补充）

⑦：TRIM（去掉字符串的左右空格）（RTRINM LTRIM）

2.数值函数

①：ROUND（四舍五入指定保留小数的位数）（小数位数可以是负数）

SELECT
	e.empno,
	e.ename,
	e.sal * 12 year_salary, 
	e.sal / 30 day_salary,
    ROUND (e.sal / 30 day_salary,2), #保留2位小数
	'元' salary unit 
FROM emp e;  

SELECT ROUND (e.sal / 30 day_salary,-1) FROM emp e; #(789.2,-1) 790

②：TRUNC（与round的区别是TRUNC不进行四舍五入）

3.日期函数

①：sysdate（当前时间）

SELECT emp.* , TRUNC(sysdate - hiredate) FROM emp; #查询每个员工从入职到现在的天数

②：ADD_MONTHS（对指定日期月数进行加和减）

MONTHS_BETWEEN（求两个指定日期之间的天数）

③：NEXT_DAY（查询从当前日期到下一个星期X的日期）

SELECT NEXT_DAY(sysdate,'星期二') FROM dual; #查询下一个星期二的日期

④：LAST_DAY（查询当前月份的最后一天的日期）

#需求：查询所有是在其雇佣所在月的倒数第三天被公司雇佣的员工的信息
SELECT * FROM emp WHERE hiredate = LAST_DAY(hiredate)-2;

⑤：EXTRACT（提取当前日期的年月日时分秒）

SELECT 
	hirdate,
	EXTRACT(YEAR FROM hiredate) year 
FROM emp; #提取员工雇员日期的年份

4.转换函数

TO_CHAR（日期字段，一定的格式）

TO_DATE （字符串日期，对应的格式日期）

5.通用函数

①：NVL（表达式，默认值）如果表达式不为null的话，那么返回表达式的值，否则返回默认值

NVL2（表达式1，表达式2，默认值）如果表达式1不为null的话，那么返回表达式2的值，否则返回默认值

NULLIF（表达式1，表达式2）如果表达式1和表达式2相等的话返回null，否则返回表达式1的值

SELECT
	empno , ename , job,
	sal * 12 + NVL(comm,0) year_sal
FROM emp; #如果奖金为null则返回0，不为null则直接返回

SELECT
	empno , ename , job,
	NVL2（comm,sal*12+comm,sal*12）year_sal
FROM emp; #如果奖金为null则返回表达式2，否则返回表达式3

SELECT 
	ename ， job
	LENGTH(ename),LENGTH(job),
	NULLIF(LENGTH(ename),LENGTH(job))
FROM emp;

②：DECODE（字段，判断值1，返回结果1，判断值2，返回结果2，…[默认值]）（只有oracle才有）

SELECT
	*,
	DECODE(job,'CLERK','业务员','SALESMAN','销售人员','职员') ch_job
FROM emp;

③：CASE WHEN

等值判断（DECODE也可以实现）

范围判断

SELECT 
	ename,sal,
	CASE
		WHEN sal>0 AND sal<1500 THEN 'level1'
		WHEN sal>1500 AND sal<3000 THEN 'level2'
		WHEN sal>3000 AND sal<4000 THEN 'level3'
	END sal_level
FROM emp
ORDER BY sal DESC;

五：多表查询

1.内连接（JOIN ON）

--左表没有的数据在连接查询结果中不会出现

--右表没有的数据在连接查询结果中也不会出现

--只有左表和右表都存在的数据才会显示到连接查询的结果中

2.外连接

①：左外连接（左表数据全部显示，右表数据如果不存在则显示为null）

SELECT *
FROM emp e LEFT JOIN dept d ON e.deptno=d.depton;

②：右外连接（右表数据全部显示，左表数据如果不存在则显示为null）

SELECT *
FROM emp e RIGHT JOIN dept d ON e.deptno=d.depton;

③：全外连接（左表，右表数据全部显示，如果没有数据就显示为null）

SELECT *
FROM emp e FULL JOIN dept d ON e.deptno=d.depton;

3.自身关联

SELECT
	e.empno,e.ename,
	m.empno,m.ename
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno; #将所有员工全部显示，没有领导则显示为null

4.并集，交集和差集

①：UNION：求两个查询SQL的并集，不包括重复的记录

②：UNION ALL：求两个查询SQL的并集，包括重复的记录

③：INTERSECT：求两个查询SQL的交集

④：MINUS：求两个查询SQL的差集

5.总结练习

①：查询SQL中子句的执行顺序

--1.先执行FROM子句：确定需要查询的表

--2.再执行WHERE子句：过滤掉不符合条件的数据记录

--3.再执行GROUP BY子句，对数据按照指定字段进行分组

--4.再执行HAVING子句：对分组之后的数据进行过滤

--5.再执行SELECT子句：查询出指定的字段的值

--6.最后执行ORDER BY子句：对查询出来的结果进行排序

②：练习

#需求：查询在1981年雇佣的全部员工的编号，姓名，日期，工作，领导姓名，年月工资及工资等级，部门编号名称位置，并且这些员工工资在1500-3500间，将最后结果按年工资降序排，如果相等则按照工作时间排

1.需要查询的表 emp dept salgrade
2.确定关联字段 emp和emp emp.mgr=emp.empno
             emp和dept emp.deptno=dept.deptno
             emp和salgrade emp.sal BETWEEN salgrade.losal AND salgrade.hisal
             
步骤一：在1981雇佣的员工及工资在1500-3500间信息
SELECT
	e.empno,e.ename,job,sal,(sal*12+NVL(comm,0)) year_sal,TO_CHAR(e.hiredate,'yyyy-mm-dd') hiredate
FROM emp e
WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500;

步骤二：关联emp表，查询领导姓名
SELECT
	e.empno,e.ename,job,sal,(sal*12+NVL(comm,0)) year_sal,TO_CHAR(e.hiredate,'yyyy-mm-dd') hiredate,m.ename
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno
WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500;

步骤三：关联dept表，查询部门编号，名称，位置
SELECT
	e.empno,e.ename,job,sal,(sal*12+NVL(comm,0)) year_sal,
	TO_CHAR(e.hiredate,'yyyy-mm-dd') hiredate,m.ename,d.deptno,d.dname,d.loc
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno 
JOIN dept d ON e.deptno=d.deptno
WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500;

步骤四：关联salgrade表，查询雇员工资等级
SELECT
	e.empno,e.ename,job,sal,(sal*12+NVL(comm,0)) year_sal,
	TO_CHAR(e.hiredate,'yyyy-mm-dd') hiredate,m.ename,d.deptno,d.dname,d.loc,s.grade
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno 
JOIN dept d ON e.deptno=d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500;

步骤五：排序
SELECT
	e.empno,e.ename,job,sal,(sal*12+NVL(comm,0)) year_sal,
	TO_CHAR(e.hiredate,'yyyy-mm-dd') hiredate,m.ename,d.deptno,d.dname,d.loc,s.grade
FROM emp e LEFT JOIN emp m ON e.mgr=m.empno 
JOIN dept d ON e.deptno=d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE TO_CHAR(e.hiredate,'yyyy')='1981' AND e.sal BETWEEN 1500 AND 3500
ORDER BY year_sal DESC,hirdate ASC;

6.多组聚合

聚合函数（输入是多行，输出是一行）

分组聚合要求：

1.如果没有分组地话，在select子句后面不能同时出现字段和聚合函数。

2.有分组地情况下，在select子句后面不能出现分组字段之外的字段

3.聚合函数可以嵌套，但在使用了嵌套之后，就不能在select子句中有任何字段

①：COUNT(查询总记录数包括重复) AVG(查询平均数) SUM(查询总和) MAX(查询最大值) MIN(查询最小值)

②：GROUP BY(分组)

SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job; #查询每种职位地最低和最高工资

③：HAVING（对分组之后的条件进行过滤）

#需求：查询出所有平均工资大于2000元的职位信息，平均工资，雇佣人数
SELECT
	job,ROUND(AVG(sal),2) avg_sal,COUNT(empno) cnt
FROM emp
GROUP BY job
HAVING AVG(sal)>2000;

7.子查询

就是在查询语句中的某个子句中的SELECT查询

在语法上，子查询是可以放在：

--1.WHERE 子句

--2.HAVING子句

--3.FROM子句

①：WHERE子句中的子查询

#查询薪资大于任何一个经理的员工信息
SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE job='MANAGER');

②：HAVING子句中的子查询

#需求：查询出每个部门平均工资最高的部门名称及平均工资

#步骤一：查询出部门平均工资最高
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

#步骤二：
SELECT
	d.dname,
	AVG(e.sal)
FROM emp e JOIN dept d ON e.deptno=d.deptno
GROUP BY d.dname
HAVING AVG(e.sal)=(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);

③：FROM子句中的子查询

需求：查询所有在部门SALES工作的员工的编号，姓名，基本工资，奖金，职位，雇佣日期，所在部门最高和最低工资

#1.查询名字为SALES部门的部门编号
SELECT deptno FROM dept WHERE dname='SALES';

#2.查询所有在部门SALES工作的员工的编号，姓名，基本工资，奖金，职位，雇佣日期
SELECT
       e.empno,e.sal,e.ename,e.comm,e.job,e.hiredate
FROM emp e
WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');

#3.查询部门的最高和最低工资
SELECT
      deptno,MAX(sal) max_sal,MIN(sal) min_sal
FROM emp e
GROUP BY deptno;

#4.最终查询
SELECT
      e.empno,e.sal,e.ename,e.comm,e.job,e.hiredate,temp.max_sal,temp.min_sal
FROM emp e JOIN (SELECT
                      deptno,MAX(sal) max_sal,MIN(sal) min_sal
                 FROM emp e
                 GROUP BY deptno;) temp ON e.deptno=temp.deptno
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='SALES');