Oracle SQL 基础
Oracle 中常用的功能语句
1 | --查询特定表中不允许为 null 的列 |
Oracle 触发器相关
1 | --查看本账户下的所有触发器 |
Oracle 锁相关
1 | --查看死锁1 |
Oracle 时间格式相关语句
1 | --查看当前的默认时间格式 (必须以管理员登录) |
Oracle 用户相关的操作语句
1 | --创建新用户 |
Oracle 表空间操作语句
1 | --创建新的表空间 |
Oracle impdp 和 expdp 的使用
1 | --创建自定义路径 |
[注]
- 有的时候也会使用 imp, exp 进行少量数据的导入导出.
imp scott/scott123@数据库服务名 file=f:/001.dmp log=f:/001.log full=y ignore=y
exp scott/scott123@数据库服务名 file=f:/001.dmp log=f:/001.log full=y
exp 不能指定版本号, expdp 可以.
imp 和 exp 可以加路径, impdp 和 expdp 不可以直接加路径, 只能通过自定义路径来更改导入导出的位置.
imp 导入时, 如果出现表已经存在的错误, 可以加入
ignore=y
参数, 加入此参数后, 已存在的表会进行数据追加, 不加此参数, 新导入的数据会被跳过!table_exists_action
参数用于指定导入操作对表的处理行为, 一共有四种:
SKIP
: 这是默认行为, 如果表已存在则直接跳过, 同时报错!
APPEND
: 如果表已存在则向表中追加数据.
REPLACE
: 如果表已存在则 DROP 表, 然后创建表, 最后导入数据.
TRUNCATE
: 如果表已存在则 TRUNCATE 表, 然后导入数据.
SQL 的大小写
sql 中的关键字, 字段名, 表名都是大小写不敏感的.
sql 中的字符串是大小写敏感的,
like 'a%'
和like 'A%'
是完全不一样的.
SQL 中的运算符
>
,<
,>=
,<=
这些运算符是进行范围比较的, 因此规定实际使用时, 尽量只用作数值类型的比较. 数值类型比较的时候不能加引号.where a < 3 and a > 1
而不是where a < '3' and a > '1'
=
,<>
,!=
相等和不相等这两种运算符既可以运算字符串, 也可以运算数值.<>
比!=
用得更多一些. sql 中等于运算符是=
不是==
between and
运算符本质上也是范围比较运算符, 因此规定实际使用时, 也尽量只用作数值类型的比较. 语法格式是:a between 1 and 2
- 比较运算符也适用于其他类型的比较, 比如字母, 日期. 比如:
where between 'A' and 'H'
where between '2019-01-01' and '2019-12-31'
[注意] 在不同的数据库中,
between
操作符会产生不同的结果! 在某些数据库中,between
选取介于两个值之间但不包括两个测试值的字段. 在某些数据库中,between
选取介于两个值之间且包括两个测试值的字段. 在某些数据库中,between
选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段. 因此, 请检查您的数据库是如何处理between
操作符!
SQL 中的 order by
order by
作用是将结果表中的数据进行排序.order by asc
为升序排序, 即第1条数据最小, 向后数据会越来越大.asc
可以省略.order by desc
为降序排序, 即第1条数据最大, 向后数据越来越小.order by
可以同时对多列进行排序, 但是实际使用时不要超过两列. 如order by a,b
, 数据会先根据 a 进行升序排序, 之后对具有相同 a 值的数据进行按照 b 的升序排序.
SQL 中的 insert into
insert into
的作用是向表中插入一行数据, 每执行一次insert into
只能插入一行数据.insert into
的语法格式是insert into tablename (columnname1, columnname2, ...) values (value1, value2, ...)
, 前面的括号写要赋值的字段名, 后面写对应的值. 要对一行的所有字段赋值的时候, 前面的括号可以省略, 此时后面的括号必须写上全部的数据.insert into tablename
后面也可以直接跟一个 select 语句, 作用是将查询出的数据保存到一个已存在的表中, 但是这里的tablename
必须是已存在的表.使用 select 的时候语法格式是
insert into tablename (columnname1, columnname2, ...) (select * from table)
, 不能加values
关键字, 否则报错.
SQL 中的 update set
update set
的作用是更新表中已经存在的数据.update set
可以一次更新多行, 这取决于where
条件.update set
可以一次更新多个字段, 只要使用,
隔开即可.update set
的语法格式是:update table set a=1, b=2, c=3 where ()
, set 后面可以跟多个赋值语句, 中间使用逗号隔开, 更新语句的数量使用 where 来决定, 实际使用时必须加 where 条件.
SQL 中的 delete from
delete from
的作用是删除表中数据.delete from
可以一次删除多行, 这取决于where
条件.delete from
的语法格式是:delete from table where ()
, delete 的语法格式基本是固定的, 只是后面的 where 条件不同.
SQL 中三种删除 (drop, delete, truncate) 的不同之处
delete: 删除表内容, 不删除表结构, 不释放空间, 可以回滚.
truncate: 删除表内容, 不删除表结构, 释放空间, 默认不能回滚.
drop: 删除表内容, 同时删除表结构, 释放空间, 不能回滚.
执行速度: drop > truncate > delete
SQL 中的各种 join on
join on
不是用来单纯取交集并集的, 它更多的是用来做数据拼接的.inner join on
可以省略写作join on
inner join on
是在两个表之间取交集. 只有符合 on 条件的数据才会被拼接并返回.from tableA join tableB on tableA.a = tableB.b
只有符合tableA.a = tableB.b
的数据才会被拼接并返回.left join on
是在两个表之前取左集, 符合on
条件的会被拼接并返回, 不符合on
条件的左表数据也会返回, 当然这些数据是没有办法作拼接的.right join on
是在两个表之前取右集, 符合on
条件的会被拼接并返回, 不符合on
条件的右表数据也会返回, 当然这些数据是没有办法作拼接的.
SQL 中的 union 和 union all
join on
可以理解为横向拼接,union all
可以理解为纵向拼接. 2X2 的两个表使用join on
拼接后是 2X4,union all
拼接后是 4X2.union
拼接自带去重, 如果想要将全部的数据保留下来, 则需要使用union all
.union all
返回表的列名使用的是第一个 select 语句的列名.
SQL 中的 select into
select into
的作用是将查询出来的数据保存到一个新的临时表中.select into
必须跟新表, 不允许跟已存在的表.语法格式是
select () into table_new from table_old where ()
.Oracle 和 PL/SQL 中不支持此语句! 可以使用 create table as select 进行代替.
在 Sql Server 中这条语句可以正常使用!
SQL 中的 create
create database
用来创建一个新数据库.create table
用来创建一个新表.create table
的语法格式:1
2
3
4
5
6
7create table table_name
(
column_name_1 data_type(size),
column_name_2 data_type(size),
......
column_name_n data_type(size)
); --创建新表, 字段使用 () 括起来, 字段和类型之间使用空格隔开, 最后一个字段后没有 , 号, 语句最后加 ; 号.
SQL 中 select 时只返回前 n 条数据
为了达到这个效果, Oracle 中可以使用
rownum
, SQL Server 中可以使用select top
, My SQL 中可以使用limit
. 在这一方面, Oracle 中有一个很大的缺点,rownum
不能直接直接写rownum = 2
, 返回第二条数据,rownum
必须是从 1 开始的一个序列.1
2
3
4
5select () from table where () and rownum <= 2; -- Oracle, 返回前 2 条数据
select () from table where () and rownum <= 10; -- Oracle, 只返回前 10 条数据
select top 2 () from table where (); --Sql Server, 返回前 2 条数据
select top 50 percent () from table where (); --Sql Server, 返回前 1/2 的数据
select () from table () limit 2; --My Sql, 返回前 2 条数据
SQL 中的约束
SQL 中的约束用来规范列中的数据, 如果有违反约束的数据行为, 一般为 insert
和 update
, 那么这个行为会被禁止.
SQL 中主要的约束有: not null
, unique
, primary key
, foreign key
, check
, default
.
not null
not null
约束是强制某一列不接受空值.在进行
insert
或者update
的时候如果没有给这一列赋值, 语句就会执行失败.
unique
unique
约束是将某一列或者某些列作为表中数据的唯一标识.unique
约束可以在一张表中设置多个.unique
约束中,null
值是允许存在的.
primary key
primary key
约束被称为主键. 唯一标识表中的每一条数据.primary key
约束的列在一张表中只能有一个.primary key
中不允许出现null
值.每一张表中都应该有一个主键.
foreign key
foreign key
约束被称为外键.一个表中的
foreign key
指向另一个表中的primary key
.一个表中的
foreign key
也可以指向另一个表中的unique
, 但是实际使用中不要这样使用. 即外键指向主键.foreign key
所在的表称为子表, 被指向的primary key
所在的表称为父表.foreign key
同样可以约束insert
,update
行为. 当要插入的值或者更新后的值在父表被指向的列中不存在时, 语句执行失败.
check
check
约束用来限制列中值的范围.
default
default
约束用来设置列的默认值.
SQL 中的自动增加 (auto_increment)
Oracle 中并没有直接提供
auto_increment
, 而是使用序列sequence
实现了自动增加.先创建一个新的序列
sequence
. 最后的循环一般不要设置, 因为自动增加多用于主键, 而主键是唯一标识的, 不允许重复.maxvalue
一般也不用设置, 创建序列时默认会设置一个非常非常大的数字, 就是这个:9999999999999999999999999999
, 够大了吧?1
2
3
4
5
6
7create sequence sequence_new
minvalue 1 --最小值
--maxvalue 5 --最大值
start with 1 --开始值
increment by 1 --增量
cache 2 --缓存大小
--cycle; --循环使用序列中的
nextval
属性实现自动增加. 每次调用nextval
序列中的值就会自动增加.1
2select sequence_new.nextval from dual;
insert into tableA (ID) values (sequence_new.nextval);
SQL 中常用的日期操作
获取系统的当前日期:
select sysdate from dual;
获取月的开始:
select trunc(sysdate, 'MM') from dual;
获取月的结束:
select last_day(sysdate) from dual;
获取周日:
select trunc(sysdate, 'd') from dual;
[注] 上面这条语句其实是获取一周的开始, 但是西方国家中 "周日" 是一周的开始😅
获取周一:
select trunc(sysdate, 'd') + 1 from dual;
[注] trunc() 函数返回的是 "date" 类型, 可以直接做加减运算. 加减运算的单位是 "天".
获取小时的开始:
select trunc(sysdate, 'hh') from dual;
获取分钟的开始:
select trunc(sysdate, 'mi') from dual;
增加一个月:
select add_months(sysdate, 1) from dual;
减少一个月:
select add_months(sysdate, -1) from dual;
增加一年:
select add_months(sysdate, 12) from dual;
减少一年:
select add_months(sysdate, -12) from dual;
trunc
方法也可以截取数字:1
2
3
4
5
6select trunc(123.458 ) from dual; --123
select trunc(123.458, 0) from dual; --123
select trunc(123.458, 1) from dual; --123.4
select trunc(123.458, -1) from dual; --120
select trunc(123.458, -4) from dual; --0
select trunc(123.458, 4) from dual; --123.458
SQL 中常用的函数
avg()
返回数值列的平均值. 括号中的列必须是数值列. 多用于比较条件中的参数.select student_id from table_student group by student_id having avg(student_score) > 80
查出平均分大于 80 分的学生的 id.count()
有多种用法.count(*)
和count(1)
等效, 获取查询数据的条数.count(column_name)
获取 column_name 这一列中非空的值. (null 不计算, 重复值全部计算在内)count(distinct column_name)
获取 column_name 这一列中非空的且唯一的值的数目. (null 不计算, 重复值只计算一次)
max()
用于取出列中数据的最大值.min()
用于取出列中数据的最小值.max()
函数不仅可以取出数值列的最大值, 还可以用于字符串, 日期列.select max(student_age) from table_student;
年龄 (数值列)select max(student_id) from table_student;
学号 (字符串列)select max(student_birth) from table_student;
出生日期 (日期列)min()
函数同样具有以上性质.
sum()
返回数值列的和.select sum(score) from table_student group by student_id;
求出每个学生的总分.group by
根据一个或多个列, 对查询出来的结果集进行分组.where
关键字无法对分组后的结果集进行筛选, 分组之前可以.group by
常常搭配avg()
,count()
,max()
,min()
,sum()
,having
一起使用.
having
关键字的出现就是为了解决 "where
不能处理分组后的结果集" 这个问题.having
可以对group by
分组后的数据进行筛选.exists()
对括号中的子句进行判断, 如果子句中有数据, 返回 true, 没有数据返回 false.substr(parame1, parame2, parame3)
截取字符串parame1: 待处理字符串
parame2: 起始位置, 0 1 都代表第一个字符
parame3: 截取长度
substr(parame1, parame2) : 取出 parame1 中 parame2 位置之后的整个字符串
instr(parame1, parame2)
检索字符串parame1: 元字符串
parame2: 待查找字符串
parame3 (可选) : 查找的起始位置, 正数为从左向右检索, 负数为从右向左检索
parame4 (可选) : 返回第几次的检索位置
replace(parame1, parame2, parame3)
替换字符串
replace(parame1, parame2, parame3): 将 parame1 中所有的 parame2 全部替换成 parame3.
replace(parame1, parame2) : 将 parame1 中所有的 parame2 全部删除.
1 | --举例: 取出 name 字段中 '/' 后的字符串 |
SQL 中的大小写转换
upper()
方法可以将查询出来的字符串数据全部转为大写字母.lower()
方法可以将查询出来的字符串数据全部转为小写字母.上面两个方式是将查询结果集中的数据转换大小写, 并不会修改数据库中真正的数据.
Oracle 中的字符串操作函数
substr(column_name, startPositon, length)
方法是 Oracle 数据库中的字符串截取函数.column_name: 是要操作的列名.
startPosition: 是要截取的开始位置, 其中 0 和 1 都表示从第一个字符开始截取.
length: 是要截取的字符串的长度.
length()
函数返回字符串数据的长度.
Oracle 中的保留位数操作函数
trunc()
函数, 可以截取数字, 达到控制保留位数的效果, 但是不会对数据进行四舍五入.round()
函数, 可以对数字进行四舍五入, 达到控制保留位数的效果.
1 | select round(3456.3456, -4) from dual; --0 |
Oracle 查看最近执行的语句
1 | select * from v$sql where sql_fulltext like '% %' order by last_active_time desc; |
Oracle 拼接整列数据为一个以逗号分割的字符串
wm_concat()
方法可以将整列数据为一个以逗号
分割的字符串.chr(39)
表示单引号, 这样可以将单引号拼接到字符串中.在外层套用一个
replace()
方法, 可以将逗号
替换为想要使用的分隔符.1
select '(' || chr(39) || replace(wm_concat(distinct mkbh), ',', chr(39)||','||chr(39)) || chr(39) || ')' from mkb where systype='dwb';
Windows 删除无用的 Oracle 服务
在 Power Shell 中输入 sc delete 服务名
命令可以删除 Windows 服务. 如:
1 | sc delete OracleDBConsolesdzc |
Oracle 还原数据库包
由于经常要还原数据库包, 所以记录一下常用的语句.
1 | --创建新用户 |
Oracle 中递归查询
Oracle 中的递归查询使用 START WITH
和 CONNECT BY PRIOR
实现.
START WITH
后面跟开始条件.CONNECT BY PRIOR
后面跟递归条件.
1 | --递归查询 |
第一次查询为: 上级单位是 '000007' 的单位, 比如结果为: '7000001' 和 '7000002';
第二次查询为: 上级单位是 '7000001' 的单位, 查询结果为空, 则继续查询上级单位是 '7000002' 的单位, 若查询结果依然为空, 则终止查询.
使用的是 广度优先遍历
.
SQL Server 中递归查询
SQL Server 中的递归查询使用 WITH
实现. WITH
后面跟树形结构的创建语句, 之后对树形结构查询即可.
1 | --递归查询 (bjdw: 本级单位, sjdw: 上级单位) |
PL/SQL 中 DECODE() 方法的使用
基础用法
语法: DECODE(条件, 值1, 返回值1, 值2, 返回值2, ... 值n, 返回值n, 缺省值);
1 | SELECT ztbz, DECODE(ztbz, '55', '未提交', '00', '提交', '92', '审核中', '91', '退回', '99', '审核通过', '缺省状态') AS ztmc |
搭配 SIGN() 函数
SIGN(value) 函数: value 为 0 返回 0, 大于 0 返回 1, 小于 0 返回 -1;
1 | --判断一列和固定值的大小关系 |
实现类三目运算符
DECODE() 函数如果只写 4 个参数, DECODE(value, 值1, 值2, 值3), 此时的含义就可以解释为: 计算 value 的值, 如果返回值等于 值1, 则返回 值2, 否则返回 值3. (当然这其实还是 DECODE() 的本质用法)
1 | SELECT DECODE(dj, 100, '等于 100', '不等于 100') FROM zjb; |
搭配 SUM() 函数, 一次求出多个数量
1 | SELECT SUM(DECODE(INSTR(saasdm, '1'), 0, 0, 1)) AS "saasdm 中含有 1", |
搭配 ORDER BY, 对字符列进行特定的排序
将资产按照 "未提交, 已提交, 审核中, 审核完成, 其他" 的顺序进行排序.
1 | SELECT * |
Oracle 中简单的分页查询实现方式
使用 MINUS
实现. MINUS 为差集, 返回左侧集合中在右侧集合中不存在的部分.
1 |
|