Oracle 基础知识汇总

一、创建和管理表(DDL)
总：crate table /truncate table /alter table /reanme…to/drop table …操作完以后，自动commit;所以，rollback对其操作，没有效果
1、查询数据字典
select table_name from user_tables --查看用户定义的表
select DISTINCT object_type from user_objects --查看用户定义的各种数据库对象
select * from user_catalog --查看用户定义的表，视图，同意词和序列
2、表名和列名命名规则

• 必须以字母开头
• 必须在1-30个字符之间
• 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle 的保留字
  3、创建表
  create table 表名 (列名+数据类型/约束属性,列名+数据类型/约束属性,列名+数据类型/约束属性)
  4、数据类型
  VARCHAR2(size) --可变长字符数据
  CHAR(size) --定长字符数据
  NUMBER(p,s) --可变长数值数据
  DATE --日期型数据
  LONG --可变长字符数据，最大可达到2G
  CLOB --字符数据，最大可达到4G
  BLOB --二进制数据，最大可达到4G
  BFILE --存储外部文件的二进制数据，最大可达到4G
  ROWID --行地址
  5、子查询创建表
  create table 新建表 as select * from 表2；
  –新建表的同时，把表2的列名、属性和数据一起复制到新建表中。
  create table 新建表 as select * from 表2 where 1=2；
  –新建表时，只复制列名和属性。不复制数据。
  6、修改数据
  Alter table表名 add (列名 属性);
  –使用add 子句追加一个新列。
  alter table 表名 modify (原列名 属性));
  –修改一个列。
  alter table 表名 drop column 列名；
  –删除一个列。
  alter table 表名 rename column 原列名 to 改名；
  –重命名一个列。
  7、删除语句
  drop table 表名；
  ①数据和结构都被删除
  ②所有正在运行的相关事务被提交
  ③所有相关索引被删除
  ④不能回滚
  truncate table 表名；
  –删除表中所有的数据，不能回滚。
  delete from 表名；
  –在未提交前，可以回滚。
  8、重命名表
  rename 表1 to表2；
  –重命名表。
  9、添加数据
  insert into 表 values()；
  –给表添加数据，不能为空的列，不可以插入null值。

二、数据处理(DML)
1、增
insert into 表名 values
–添加一条记录
insert into 表1
select from 表2
Where
–从其它表拷贝数据
2、改
update表 set where；
–修改表数据。
3、删
delete from 表 where；
–删除表数据。
4、查
select 表；
–查询表数据。
三、数据控制(DCL)
DCL用来控制数据库的访问，包括如下SQL语句：
(grant)GRANT：授予访问权限
(revoke)REVOKE：撤销访问权限
(commit)COMMIT：提交事务处理
(rollback)ROLLBACK：事务处理回退
(savepoint)SAVEPOINT：设置保存点
(lock)LOCK：对数据库的特定部分进行锁定

四、约束
对创建的表的列属性，字段进行的限制。诸如：not null /unique/primanry key/foreign key/check
1、表级约束 & 列级约束
Unique：唯一性约束，允许出现null值，不允许重复值
primary key：主键约束，不允许空值
foreign key：指定约束
Check：自定义约束
not null：非空约束，只能定义在列级
2、添加和删除约束
在创建表以后，只能添加和删除，不能修改；
当定义或激活unique 或 PRIMARY KEY 约束时系统会自动创建unique 或 PRIMARY KEY索引。
alter–add constaint emp1_sal_ck check(salary > 0)；
–添加一条记录。
alter–modify(salary not null)；
–对于not null 来讲，不用add，需要使用modify。
alter–drop constaint emp1_sal_ck；
–删除表约束。
alter–disable constraint emp1_email_uk；
–使指定约束失效，但还存在表中。
alter–enable constraint emp1_email_uk；
–使当前约束激活。
五、子查询
1、子查询：
select列名
from 表名
where 列名 > (select 列名
from 表名
where 条件);
2、在子查询中使用组函数：
①子查询要包含在括号内
②将子查询放在比较条件的右侧
③单行操作符对应单行子查询，多行操作符对应多行子查询
select 列名 from 表名
where 列名 = (select min(列名) from 表名);
3、HAVING 子句：
①首先执行子查询
②向主查询中的having字句返回结果
select min(列名)
from 表名
group by 列名
having min(列名) >(select min(列名)
from 表名
where 条件;
4、非法使用子查询：
select 列名
from 表名
where 列名 = (select min(列名)
from 表名
group by 列名);
–在子查询中，不能使用group by 语句返回多行数据。

5、子查询中的空值问题：
在子查询中如果子查询条件查询到的信息为空的话，子查询不返回任何行。
6、ALL 操作符：
select 列名
from 表名
where 列名 < all (select 列名
fron 表名
where 条件)
–在子查询中使用all操作符，可以返回多行数据。

六、Oracle查询
1、查询语句
1.查询语句
select * from table --查询整个表的数据（查询这个表的所有数据）
select 列名，列名 from table --查询这个表的特定的列
select 列名+数字 from table --可以计算这个字段的加减乘除
select 列名“别名” * from table --可以把列名改为别名在查询结果中显示
select 列名 || 列名 AS “别名” from table --可以把列名中的字段合并在设定的别名的列名里面显示（将列合并在设置的别名里面）
select 列名 || ‘字符串’ || as “别名” from table --可以把字符串穿插在两个字段中间显示在别名中
select DISTINCT 列名 from table --在列名的前面加入DISTINCT，可以把整个表中这个列名中重复的行删除显示
2.过滤
select * from table where ID = 90 --查询这个表中ID等于90的数据
select * from table where 列名 = ‘字符串’ --查询这个表中这个列名的某个字符串
select * from table where ID IN (10,11,12) --只查询括号中限定的ID
select * from table where 列名 like ‘K%’/‘%k%’ --k%'查询这个列名中以K开头的字段or 模糊查询有K的字段
select * from table where 列名 like ‘_K%’ --加入下划线，查询这个字符前面的一个字符
select * from table where 列名 LIKE ‘K_%’ escape ‘’ --把_本身的作用转换成了单纯的一个字符
select * from table where id is null --查询这个ID为空的行
select * from table where id is not null --查询这个ID不为空的行
select * from table where ID >10 AND 列名 = ‘’ --AND 并且，查询这个条件为什么，并且为什么。两个条件（查询这个条件>10,并且列名=""）
select * from table where 列名 between 5000 and10000 --between 5000 and 10000 在五千到一万之间
select * from table where ID >10 OR 列名 = ‘’ --OR 或者，查询这个条件为什么，或者为什么。（查询这个条件>10,或者列名=""）
select * from table where NOT IN（‘列名’，‘字符串’）–not in 否，查询所有列名中字符串不是这些的行（查询不是这个列和字符串的行）
3.排序
select * from table ORDER BY 列名 ASC --asc升序，以A或者1开头。由上到下
select * from table ORDER BY 列名 DESC --desc降序，以A或者1开头。由下到上
select 列名 A别名 from table ORDER BY 别名 DESC --把列名别名为A，并且排序

按多个列排序: 先按第一列排序, 若第一列中有相同的, 再按第二列排序.
格式： order by 一级排序列 asc/desc,二级排序列 asc/desc；
2、Oracle函数
1.字母大小写控制函数
select 列名, LOWER (列名) from table
–lower()变小写，把这个列名中所有行值字母改为小写（用于行值为字母）
select 列名, UPPER (列名) from table
–upper()变大写，把这个列名中行值的所有字母改为大写（用于行值为字母）
select 列名, INITCAP (列名) from table
–initcap()首字改为大写，把这个列名中行值首字改为大写（用于行值为字母）
2.字符控制函数
select CONCAT(列名1/列值1,列名2/列值2) from table
–concat(只能用两个列名和列值)合并
select SUBSTR(‘HelloWorld’,1,5) from table
–substr(‘字符串’,‘1’,‘5’)显示第一到第五个字符串
select LENGTH(‘HelloWorld‘) from table
–length(‘字符串’)显示有几个字符串
select INSTR(‘HelloWorld‘,‘w’) from table
–instr(‘字符串’,‘串’)显示串是第几个字符
select LPAD(列名,数值,‘字符’) from table
–lpad(‘列名’,‘数值’,‘符号’)填充符号左边显示
select RPAD(列名,数值,‘字符’) from table
–rpad(‘列名’,‘数值’,‘符号’)填充符号左边显示
select TRIM (‘H’ from ‘HelloWorldH’) from table
–trim(‘H’ from ‘HelloWorldH’)减去首尾字母H(中间有则不减去）
select REPLACE(‘abcd’,’b’,’m’) from table
–replace(‘Hello’,‘e’,‘o’)将e换成o（换多少都行）
3.数字函数
select ROUND (55.56,数值) from table
–结果四舍五入到选定数值的位数中
select TRUNC (55.56,数值) from table
–结果截断到选定数值的位数中
select MOD (数值1，数值2) from table
–数1/数2 求余数
4.ROUND 函数
SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)
FROM DUAL;
–DUAL 是一个‘伪表’，可以用来测试函数和表达式
5.MOD 函数
select MOD (列名，数值) from table
–查询列名属性为数字型，再设定一个数值。列的行值就会除以设定的数值，能整除的结果为0，不能的话就会显示出多余的数值
6.日期函数
select(SYSDATE+属性为日期的列名)/7 AS 别名 from table
–用列名属性为日期的列的行值/7,结果为行值日期有几个星期
select sysdate from table
–获取当前时间
select MONTHS_BETWEEN(to_date(‘2000-01-01’,‘yyyy-dd-mm’),时间列名) from table
months_between(’’,’’)
–计算相差有几个月份
select extract(month from to_date(‘日期’
,‘yyyy-mm-dd’)) from table
select extract(month from to_date(‘日期属性的列’
,‘yyyy-mm-dd’)) from table
–显示这个日期是几月份
select to_char(日期属性列名,‘DAY’) from table
–显示这个日期是星期几
select ADD_MONTHS(to_date(‘2018-1-1’,‘yyyy-mm-dd’),‘月数’) from table
–显示指定的日期加上指定的月数
select last_day(sysdate) from table
–查看本月的最后一天
select last_day(sysdate) -1 from table
–查看本月的倒数第二天
select ROUND(sysdate) from table
–查看日期的四舍五入
7.转换函数
select to__char(sysdate,‘yyyy-mm-dd hh:mi:ss’)from table
-转换日期
9.数字，0零，$美元符，L本地货币符号，. 小数点，，千位符
-TO_CHAR 函数中经常使用的几种格式
select to__date(‘2018-1-1’,‘yyyy-mm-dd’)from table
-对指定日期的转换
select TO_NUMBER(‘￥1,234,90.00’, ‘L9,999,99.99’) from table
-对数字转换字符串
select ‘1234567.89’ + 100 from table
-字符串转数字
select TO_DATE(‘2012年10月29日 08:10:21’,‘yyyy"年"mm"月"dd"日"hh:mi:ss’)From table
-对字符转换
–转换函数: to_char(), to_number(), to_date() ********* cast
10.NVL函数
SELECT NVL(列名, 0) FROM table
–对这个列为空的情况下显示为0
SELECT salary, NVL(to_char(列名),‘sd’ ) FROM table
–对这个列为空的情况下显示为sd
11.NVL2函数
SELECT NVL2(列名,‘A’, ‘B’) income FROM table
–当查询的列中行值不为空的时候返回A，不为空的时候返回B
12.NULLIF函数
SELECT LENGTH(salary) “expr1”,LENGTH(last_name) “expr2”,NULLIF(LENGTH(salary),LENGTH(last_name)) result FROM employees
–对比两个列名中行值的字符串是否长度相等，相等返回NULL，不等返回expr1
12.COALESCE函数
SELECT COALESCE(列名, A, B) FROM 表名
–如果第一个表达式为空，则返回下一个
13.CASE表达式
select case
when 条件 then 就什么
end
from 表名
–if-then-else逻辑
decode(ID1,列名*，ID2，列名*，ID3，列名*)
–如果是ID1，则*，如果是ID2，则*
14.多表查询
select a.列名,a.列名,b.列名
from 表名 a,表名 b
where a.ID = b.ID
–多表查询，可查询多个表的信息。但是必须两个表之间要有关联。
–查询的时候必须要加where条件，不然会产生笛卡尔集
15.外连接语法
select a.列名,a.列名,b.列名
from 表名 a,表名 b
where a.ID(+) = b.ID
–右外连接(将右表的所有数据显示，包括为空的)
select a.列名,a.列名,b.列名
from 表名 a,表名 b
where a.ID = b.ID(+)
–左外连接((将左表的所有数据显示，包括为空的)
select a.列名,a.列名,b.列名
from 表名 a left join /right join 表名 b
where a.ID = b.ID(+)
–右外连接right join on
–左外连接left join on
16.AVG、SUM函数
SELECT AVG(salary) 平均, SUM(salary)合计
FROM employees
–AVG平均值，SUM合计
17.MIN，MAX函数
SELECT MAX(hire_date) 最大, MIN(hire_date) 最小 FROM employees
–MIN最小，MAX最大
18.COUNT（计数）函数
SELECT COUNT(列名)FROM 表名
–查询这个列不为空的有多少行
19.DISTINCT 关键字
SELECT COUNT(DISTINCT 列名) FROM 表名
–返回列不为空，而且不重复的记录总数
20.组函数中使用NVL
SELECT AVG(NVL(列名, 0))FROM 表名
–加上NVL后除以所有行，不加的除以不为空的行
21.GROUP BY 子句
SELECT 列名1, AVG(列名2)FROM 表名 GROUP BY 列名1
–所有查询的列都包含在GROUP BY 子句
SELECT 列名1, AVG(列名2)FROM 表名 GROUP BY 列名1 ,列名2
–在 GROUP BY 字句中可以包含多个列
22.非法使用组函数
–所有包含在select 列表中，而未包含在组函数中的列都必须包含在 GROUP BY 子句中
SELECT 列名 FROM 表名 WHERE AVG(salary) > 8000
GROUP BY 列名;
–where 字句中不能使用组函数
23.HAVING 子句
SELECT 列名1, MAX(列名2)FROM 表名
GROUP BY 列名1
HAVING MAX(列名2)>数值
–使用HAVING过滤分组：
①行已经被分组，
②使用了组函数，
③满足HAVING子句中条件的分组将被显示
24.嵌套函数
SELECT MAX(AVG(列名)) FROM 表名
GROUP BY 列名
–可以使用两个或者两个以上的函数进行组合使用
感谢您的阅读！
猜您喜欢的文章：
如何使用Oracle视图？
2018UI课程总结（UI理论篇）
什么是Java算数运算符？
令程序员泪流满面的瞬间，实在忍不住笑了
Oracle约束怎么写？
AE基础界面设置和旋转加载案例
《酒店管理系统——桑拿、沐足模块》项目研发阶段性总结
PS快捷键