Oracel学习笔记-2：常用查询语句

最新推荐文章于 2022-03-29 21:31:55 发布

小电鼠

最新推荐文章于 2022-03-29 21:31:55 发布

阅读量105

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/bea5yeah/article/details/80616636

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、大体格式：
SELECT [DISTINCT] 列列表 [AS 列别名]
FROM 表名列表
WHERE 条件
GROUP BY 分组列表
HAVING 分组后的筛选条件 -- 分组函数或者其他字段
ORDER BY 排序字段列表 -- asc升序，desc降序

2、方式
--模糊查询          like结合通配符 _ 单个字符，% 0到任意多个字符
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.ename LIKE 'S%';
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.ename LIKE '_____';
--区间查询          between and        []
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.sal BETWEEN 2000 AND 3000;        -- 相当于闭区间[],等价于 >= and <=
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.sal NOT BETWEEN 2000 AND 3000;
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.sal>=2000 AND e.sal<=3000
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.sal<2000 OR e.sal>3000
--多值查询          in(值列表)     等价于 = or = or ...
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.ename IN ('SMITH','WARD');
SELECT e.ename,e.job,e.hiredate,e.sal FROM emp e WHERE e.ename='SMITH' OR e.ename='WARD';
--空值查询          is null /is not null
SELECT * FROM emp WHERE mgr IS NULL;

3、基本函数
--链接字符串
SELECT CONCAT(job,ename) FROM emp;
-- 首字母大写，小写，大写
SELECT INITCAP(job), LOWER(job), UPPER(job) FROM emp;
-- 截取（索引从1开始）
SELECT ename,SUBSTR(ename,2,3) FROM emp;
-- 第一次出现的位置
SELECT INSTR('student','u') FROM stu;
-- 去掉指定字母
SELECT TRIM('u' FROM 'student') FROM stu;
-- 替换
SELECT REPLACE(ename,'SC','SS') FROM emp;
-- 替换字符串，并不要求SH连续，S替换为A，H替换为B（不管你怎么查询select，实际表中的数据不会发生变化，提取数据形成一张虚拟表呈现给用户看）
SELECT ename,translate(ename,'SHE','ABC') FROM emp;

4、数学函数（符号，绝对值、取整，取余，截取，计算）
-- 取符号位
SELECT SIGN(-3),SIGN(3),SIGN(0) FROM dual;
SELECT ABS(-3),ABS(3),ABS(0) FROM dual;
-- floor地板，下取整 (-4,3)
SELECT FLOOR(-3.14), FLOOR(3.14) FROM dual;
-- ceil天花板，下取整(-3,4)
SELECT ceil(-3.14), ceil(3.14) FROM dual;
-- 四舍五入，下取整 (-3,3,4)
SELECT round(-3.14), round(3.14), round(3.54) FROM dual;
-- mod(被除数，除数)，结果为余数，余数的符号与被除数一致
SELECT MOD(10,3),MOD(10,-3),MOD(-10,3),MOD(-10,-3) FROM dual;
-- trunc截取，第一个参数是要截取的数字，第二个参数是截取的位置（小数点算作第0个位置，往右数为正数位置，左负数位置）
SELECT TRUNC(123.99),TRUNC(123.99,1),TRUNC(123.99,-1) FROM dual;
--根号，次方
SELECT SQRT(9),POWER(2,3),POWER(3,2) FROM dual;
-- 正弦余弦
SELECT SIN(3.1415926/2),SIN(3.1415926/4),COS(3.1415926/2),COS(3.1415926/4),TAN(3.1415926/4) FROM dual;

5、日期时间函数
-- 日期函数
SELECT hiredate,add_months(hiredate,-2) FROM emp;
-- 计算员工入职首月工作天数
SELECT hiredate,last_day(hiredate),(last_day(hiredate)-hiredate+1) AS "首月工作天数" FROM emp;
-- 计算各员工首次领工资的日期（入职的下一个月1号）
SELECT hiredate,last_day(hiredate),(last_day(hiredate)+1) AS "首次领工资的日期" FROM emp;
-- 计算员工的入职年限
SELECT hiredate,floor(months_between(SYSDATE,hiredate)/12) FROM emp;
-- 试用期是3个月，计算转正日期   (+90)
SELECT hiredate,add_months(hiredate,3) FROM emp;
-- 从时间中提取年月日
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;
-- date，timestamp 邮戳，时间戳
SELECT EXTRACT(HOUR FROM TIMESTAMP '2018-06-05 21:30:45') FROM dual;
-- 转换函数
SELECT to_date('1999-12-31','yyyy-mm-dd') FROM dual;
SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss day') FROM dual;
SELECT to_char(SYSDATE,' day') FROM dual;

6、常用方法
-- 【空值的查询显示转换问题】NVL(列,null值的替值)（没有奖金的显示为0）
SELECT ename,job,sal,comm,NVL(comm,0) FROM emp;
-- NVL2(列名,不为空替换值,为空替换值) 布尔表达式? 表达式1：表达式2
SELECT ename,comm,NVL2(comm,1,0) FROM emp;
-- NULLIF(exp1,exp2)exp1和exp2相等，则返回null，找员工表中谁是老大，管理者id和自身员工id一致，表示他没有上级
SELECT empno,mgr,NULLIF(mgr,empno) FROM emp;

-- 批量插入(确保多条数据同时插入成功或同时插入失败)
INSERT ALL INTO DEPT VALUES(1,'董事会',1)
INTO DEPT VALUES(2,'行政部',1)
INTO DEPT VALUES(3,'市场部',2)
INTO DEPT VALUES(4,'后勤部',2)
SELECT 1 FROM dual;

7、聚合函数
-- 1、统计emp表中有多少员工
SELECT COUNT(1),COUNT(*),COUNT(comm) FROM emp;
-- count(常量，*)和count(列名)的区别？【面试题】 count(列名)，此列null不计算在内部
SELECT COUNT(1) FROM emp;
SELECT COUNT(*) FROM emp;
-- 2.员工表中职位个数job
SELECT COUNT(DISTINCT job) FROM emp;
SELECT * FROM emp ORDER BY job DESC;
-- 3、求员工的最高工资和最低工资分别是多少
SELECT MAX(sal) 最高工资,MIN(sal) 最低工资,AVG(sal) 平均工资,SUM(sal)+SUM(comm) FROM emp;
SELECT FROM emp;
-- 4 查询最高工资的员工的名字和岗位名称
SELECT ename,job,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
-- 5.查询工资高于平均水平的员工名字和薪水
SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

8、分组查询
-- 查询每个部门员工总数
SELECT deptno,COUNT(1) FROM emp GROUP BY deptno
-- group by 语法注意事项:
-- 【切记】一旦使用了group by进行分组统计，select后面只能是group by后面分组字段的子集或者分组函数
-- 通俗点说，在group by后面没有的字段，绝对不允许出现在select后面
SELECT * FROM emp ORDER BY job DESC;