Oracle数据库的SQL语法学习笔记（一）

最新推荐文章于 2024-07-01 10:26:34 发布

若灬离灬

最新推荐文章于 2024-07-01 10:26:34 发布

阅读量823

点赞数

文章标签： oracle sql

本文链接：https://blog.csdn.net/woruochengwang/article/details/106129514

版权

本人近期开始巩固复习了Oracle数据库的SQL语句语法

这是本人的笔记，发布出来1、是便于查找 2、是希望帮助初学者找到可以借鉴的东西

由于记得匆忙不排除字符错误，有的符号会使用中文记录，复制改为英文即可。仅供参考，以后我也会继续分享自己的学习笔记用于督促自己和大家共享知识，欢迎大家支持点赞和参与讨论，愿与诸君共勉！

1.DDL语句
1.DEFAULT 'M' 给默认为空的字段默认值为M
DATE DEFAULT SYSDATE 默认插入当前系统时间
1.NOT NULL 字段不能为空
2.system系统
3.DESC 表名查看表结构
4.CREATE TABLE 表名（字段名类型）创建表
5.RENAME 原表名 TO 新表名修改表名字
6.修改表结构1：添加新字段 2：修改现有字段3：删除现有字段
1：ALTER TABLE 表名 add（字段名类型）只能在末尾追加
2：ALTER TABLE 表名 DROP （字段名）
删除现有字段
3：ALTER TABLE 表名 MODIFY（字段名类型）字段名不能改，可修改类型，长度默认值，是否非空有数据时尽量不要修改类型，修改长度尽量增大避免减小，可能失败
7.DML语句对表中数据进行操作，伴随事物控制：增、删、改
INSERT 插入数据
INSERT INTO 表名（字段名）VALUES（数值）
8.COMMIT 提交插入数据
9.插入日期格式 TO_DATE（'2019-09-01','YYYY-MM-DD'）
10.可不写VALUES前字段，但会按顺序全列插入
11.UPDATE 表名 SET 字段名=值 WHERE 条件修改表中数据要使用where 添加过滤条件，这样才会只将满足条件的记录进行修改，否则是全表数据都修改
12.DELETE 删除表中数据，删除数据通常也要添加WHERE 语句来限定要删除数据的条件否则就是清空操作
13.DELETE FROM 表名 WHERE 字段=值
14.TRUNCATE DDL语言清空表数据效率高不受事务影响，没有办法恢复
TRUNCATE TABLE 表名
DELETE FROM 表名
删除全部记录
15.CHAR 定长 VARCHAR不定长（VARCHAR2）
10 BYTE 10个字节
10 CHAR 10个字符
CHAR最高2000字节
VARCHAR最高4000字节
CHAR 可不写字节
VARCHAR需要敲字节
（7,2）2为精度
16.LONG 2个G 限制多无法作为主键
17.CLOB 4个G
18. SELECT 语句
用于查询表中数据
SELECT 子句后面跟的是要查询的字段，可以包括句中的具体字段，函数或者表达式.
FROM 子句用来指定数据来源的表
WHERE 子句用来添加过滤条件，这样做的结果是只将满足条件的记录查出来
19.字段可以在查询时进行加、减、乘、除
20. CONCAT（）拼接字段 CONCAT（1,2）
CONCAT(CONCAT(1,','),2)
1||’,’||2
21.LENGTH 查看字符串长度对CHAR无用
22.UPPER，LOWER，INITCAP
将字符串转换为全大写，全小写，以及首字母大写
对于INITCAP而言，可以使用空格隔开多个单词，那么每个单词首字母都会大写
23.伪表只会查出一条记录，为了满足语法要求
24. TRIM，LTRIM，RTRIM
去除当前字符串中两边的指定重复字符，LRTRIM仅去除左侧的，RTRIM则仅去除右侧的
select TRIM（’e’ from ’eoehdhejejke’）from 伪表仅能去除一个字母
select LTRIM（ ’eoehdhejejke’,’e'）from 伪表
select RTRIM（ ’eoehdhejejke’,’e'）from 伪表。可去除多个字符，直到不是其中一个字符为止
25.LPAD，RPAD补位函数
select LPAD（字段，位数，’补齐内容’） FROM 表名左边补齐
select RPAD（字段，位数，’补齐内容’） FROM 表名右边补齐
位数是死的，只显示输入的位数
26.SUBSTR截取字符串
数据库中的下标都是从1开始的
SELECT
SUBSTR（’thinking in JAVA’，13，4）from 伪表 JAVA 4不写就是一直截到末尾
截取的位置可以是负数，若是则表示从倒数第几个字符开始截取
SELECT
SUBSTR（’thinking in JAVA’，-4，4）from 伪表 4不能是负数
25.INSTR（char1，char2[,n,m]）函数
查找char2在char1中的位置
n为从第几个字符开始检索
m为第几次出现
n，m不写则默认都是1
SELECT
INSTR（’thinking in JAVA’，’in’，4，1）from 伪表
26.数值类型
NUMBER（P，S）表示浮点数 38位
27.ROUND （n，m）四舍五入
select RONUD（45.68，2） from DUAL 后两位
select RONUD（45.678，0） from DUAL 个位
select RONUD（45.68，-1） from DUAL 十位
28.TRUNC （n，m）直接截取
select TRUNC（45.68，2） from DUAL 后两位
select TRUNC（45.678，0） from DUAL 个位
select TEUNC（45.68，-1） from DUAL 十位
29.MOD（m，n）求余数
n可以为零返回m
SELECT ename,sal ,MOD(sal,1000) from emp 千位以下的余数
30.CETIL和FLOOR
向上取整和向下取整
SELECT CELL（45.678）FROM DUAL
SELECT FLOOR（45.678）FROM DUAL
31.SELECT ename，sal，deptno FROM emp WHERE ename=UPPER（’scoot’）

32.select ename，sal，deptno from emp WHERE LENGTH（ename）=5
查找名字只有5个字母的员工的名字，工资，部门号
33.select * from emp where INSTR （ename，’A’）=3
查看第三个字母是A的员工信息
select * from emp where SUBSTR（ename，3，1）=’A’
34.DATE 7个字节
35.TIMESTAMP时间戳 11个字节
36.sysdate date类型的系统时间
37.sysTIMESTAMP 时间戳类型系统时间
38.select sysdate from dual
select sysTIMESTAMP from dual
格林尼治时间北京时间加8
39.日期转换函数 TO_DATE
SELECT TO_DATE（’2018-08-08 20：08：08’，’YYYY-MM-DD HH24：MI：SS’）FROM dual
双引号标注特殊字符
SELECT TO_DATE（’2018年08月08日 20：08：08’，’YYYY“年”MM”月”DD“日” HH24：MI：SS’）FROM dual
40.日期的计算
日期可以与一个数字进行加减法，这相当于加减指定的天数
两个日期可以进行减法，差为相差的天数
41.查看每个员工至今入职多少天
select ename，SYSDATE-hiredate from emp
42.select sysdate-TO_date-（’1992-08-02’，’YYYY-MM-DD’）from dual
43.TO_CHAR按一定的格式转换为字符串
SELECT TO_CHAR（SYSDATE，’YYYY-MM-DD HH24：MI：SS’）FROM dual
44.RR关注世纪
YY不关注世纪
45.LAST_DAY
返回给定日期所在月的月底日期
查看月底
SELECT LAST_DAY（SYSDATE） FROM DUAL
46.ADD_MONTHS（date，1）
对给定的日期加上指定的月，若i为负数则是减去
查看每个员工入职20周年纪念日
select ename，ADD_MONTHS（hiredate，12*20） FROM DUAL
47.MONTHS_BETWEEN（date1，date2）
FROM DUAL
1-2得到
计算两个日期之间相差的月
48.NEXT_DAY（date，char）
（date，i）
1表示礼拜天
select NEXT_DAY（sysdate，4）FROM DUAL
一周之内的星期4是几号
49.LEAST，GREATEST
求最小值与最大值
日期越晚越大
SELECT LEAST（SYSDATE，TO_DATE（’2008-08-05’，’YYYY-MM-DD’））FROM DUAL
50.EXTRACT （）提取给定日期中指定时间分量的值
SELECT EXTRACT（YEAR FROM SYSDATE） FROM DUAL
查看1980年入职的员工
SELECT ename，hiredate FROM emp WHERE EXTRACT（YEAR FROM hiredate）=1980
51.空值操作
NULL的含义
插入NULL
显式插入写对应的字段为NULL
隐式插入不写字段默认为NULL
52.更行成NULL
UPDATE student SET gender=NULL where id=1000
53.判断字段的值是否为NULL
判断要用IS NULL 或者is not null
DELETE FROM student WHERE gender is NULL
54.NOT NULL 建表时非空
NULL值的运算操作
NULL与任何数字运算结果为NULL
NULL与字符串拼接等于什么都没干
55.SELECT ename ，sal，comm，sal+NVL（comm，0） FROM emp
查看每个员工的收入
56.空值函数NVL（arg1，arg2）
当arg1为NULL，函数返回arg2的值
若不为NULL，则返回gra1本身.
所以该函数的作用是将NULL值替换为一个非NULL值.
57.NVL2（arg1，arg2，arg3）
当arg1不为NULL 则函数返回arg2
当arg1为NULL，则函数返回arg3
该函数是根据一个值是否为NULL来返回两个不同结果
有绩效，则显示为有绩效
绩效为NULL，显示为没有绩效
SELECT ename，comm，NVL2（comm，’有奖金’，’没有奖金’）from emp
NVL2能实现NVL功能
58.select ename，sal，comm，NVL2（comm，sal+comm，sal）FROM emp
select ename，sal，comm，sal+NVL2（comm，comm，0）FROM emp
59.DQL查询语句
使用别名 AS 不能是SQL语句的关键字
别名不区分大小写不可以出现空格想出现添加“”双引号
SELECT 子句中可以使用函数或表达式，那么结果集中对应的该字段名就是这个函数或表达式，可读性差，为此可以为这样的字段添加别名，那么结果集会以这个别名作为该字段的名字
SELECT ename，sal*12 sal from emp
60.WHERE 子句
查询部门10下的员工信息
SELECT * FROM emp WHERE deptno=10
查询职员表中职位是’SALESMAN’的职员
SELECT ename，sal，job from emp WHERE job=’SALEMAN’
61.！=不等于
<>不等于
SELECT ename，sal from emp WHERE sal<2000
SELEct ename,sal,job From emp Where dept no <>10
Select ename,sal,hiredate, from emp Where hiredate >to_date('1982-1-1','YYYY-MM-DD')
62.AND，OR
AND两个都为真是为真
OR两个都为假时为假
63.SELECT ename，sal，job from emp WHERE sal >1000 And job ='Clerk'
SELECT ename，sal，job from emp WHERE sal >1000 Or job ='Clerk'
SELECT ename，sal，job from emp WHERE sal >1000 And job ='Clerk' Or job='Salesman'
OR优先级低于AND 需要加（）进行提高
64.LIKE 像模糊匹配（模糊查询）
%任意字符
_一个字符
查看名字第二个字母是A最后一个字母是N的
SELECT ename FROM emp WHERE ename LIKE ’_A%N’
65.IN ，NOT IN
是不是在列表里
SELECT ename，job FROM emp
WHERE job IN （’MANAGER’，’CLERK’）；
SELECT ename，job FROM emp WHERE deptno NOT IN （10，20）；
66.IN和NOT IN 常用来判断子查询的结果
67.BETWEEN…AND…
判断在一个区间范围内
工资在1500到3000之间的员工
SELECT ename，sal FROM emp WHERE sal BETWEEN 1500 AND 3000
68.IS NULL 和IS NOT NULL 判断字段是否为空
SELECT ENAME，SAL，COMM FROM EMP
WHERE COMM IS NULL
69.ANY和ALL
>ALL(List)大于最大
<ALL(List)小于最小
>ANY(List)大于最小
<ANY(List)小于最大
与>， <搭配使用
常用判断子查询的
70.SELECT empno，ename，job，sal，deptno FROM emp WHERE sal >ANY(3500,4000,45000)
SELECT empno，ename，job，sal，deptno FROM emp WHERE sal >ALL(3500,4000,45000)
SELECT empno，ename，job，sal，deptno FROM emp WHERE sal <ANY(3500,4000,45000)
SELECT empno，ename，job，sal，deptno FROM emp WHERE sal <ALL(3500,4000,45000)
71.使用函数或表达式作为过滤条件
SELECT ename，sal，job FROM emp WHERE ename = UPPER（’scoot’）
SELECT ename，sal，job FROM emp WHERE sal*12>50000
72.DISTINCT 关键字
对结果集中指定字段值重复的记录进行去重
查看公司有哪些职位？
Select DISTINCT job From job
无法在DISTINCT 前写字段
后面书写是对多字段去重，组合去重
73.排序 ODER BY 必须写在最后一个子句上
可以根据其后指定的字段对结果集按照该字段的值进行升序或者降序排列.
ASC：升序，不写默认就是升序
DESC 降序
74.查看公司的工资排名
SELECT ename，sal FROM emp ORDER BY sal DESC 按工资降序排列
75.多字段排序有优先级，第一字段有重复按照下一个字段排序
76.每个字段都可以单独指定升序、降序
77.SELECT ename，deptno，sal FROM emp ORDER BY deptno DESC， sal DESC
78.排序的字段中含有NULL值，NULL被作为最大值
79.聚合函数、（分组函数、多行函数）一个意思
聚合函数是对结果集某些字段的值进行统计的
80.MAX，MIN
求给定字段的最大值与最小值
SELECT MAX（sal），MIN（sal）
FROM emp
81.AVG，SUM
求平均值和总和
SELECT AVG（sal），SUM（sal）
FROM emp
82.COUNT 函数
COUNT 函数不是对给定的字段值进行统计的，而是对给定字段不为NULL的记录数统计的.
实际上所有聚合函数都忽略NULL值统计
SELECT COUNT（comm）FROM emp
SELECT COUNT（*）FROM emp
总共多少条记录
SELECT COUNT（NVL（comm，0））FROM emp
83.SELECT AVG（NVL（comm，0）），SUM（NVL（comm，0））FROM emp
不忽略NULL值记录数
84.分组
GROUP BY 子句配合聚合函数
GROUP BY 可以将结果集按照其后指定的字段值相同的记录看做一组，然后配合聚合函数进行更细分的统计工作。
除聚合函数以外字段在group by中写一遍
不一定反着写
select AVG（sal），deptno FROM emp GROUP BY deptno
查看每个职位的最高工资？
SELECT MAX （sal），job FROM emp GROUP BY job
85.GROUP BY也可根据多个字段分组
分组原则为这几个字段值都相同的记录看做一组
查看同部门同职位的工资
SELECT AVG（sal），job，deptno FROM emp GROUP BY job，deptno
86.查看部门的平均工资，前提是该部门的平均工资高于2000
SELECT AVG（sal），deptno FROM emp WHERE AVG（sal）>2000 group by deptno
WHERE 中不能使用聚合函数作为过滤条件，原因是过滤时机不对，WHERE是在数据库检索表中数据时，对数据逐条过滤以决定是否查询出该数据时使用的，所以WHERE用来确定结果集的数据
使用聚合函数的结果作为过滤条件，那么一定是数据从表中查询完毕（WHERE在查询过程中发挥作用）得到结果集，并且分组完毕才进行聚合函数统计结果得到后才可以对分组进行过滤，由此可见，这个过滤，时机是在WHERE之后进行的
聚合函数的过滤条件要在HAVING子句中使用必须跟在GROUP BY 子局之后.HAVING是用来过滤分组的
87.SELECT AVG（sal），deptno FROM emp group by deptno HAVING AVG（sal）>2000
88.查看平均工资高于2000的部门的最高工资和最低工资分别是多少？
SELECT MAX（sal），MIN（sal），deptno FEOM emp GROUP BY deptno HAVING AVG（sal）>2000