Oracle-SQL初级笔记

创建表：
　　create table 表名 (
　　　　字段名1 字段类型 默认值 是否为空 ，
　　　　字段名2 字段类型 默认值 是否为空，
　　　　字段名3 字段类型 默认值 是否为空，
　　　　…
　　);
　　创建一个user表：
　　create table user (
　　　　id number(6) primary key, —主键
　　　　name varchar(50) not null, —姓名 不为null
　　　　sex varchar2(6) default ‘男’ check ( sex in (‘男’,‘女’)) —性别 默认’男’
　　);
修改表名：
　　rename 旧表名 to 新表名；
　　rename user to newuser;
删除表：
　　delete from 表名；
　　delete删除数据是一条一条的删除数据，后面可以添加where条件，不删除表结构。注意：如果表中有identity产生的自增id列，delete from后仍然从上次的数开始增加。
　　truncate table 表名；
　　truncate是一次性删掉所有数据，不删除表结构。注意：如果表中有identity产生的自增id列，truncate后，会恢复初始值。
　　drop table 表名；
　　drop删除所有数据，会删除表结构。
修改表：
　　添加新字段：
　　alter table 表名 add(字段名 字段类型 默认值 是否为空);
　　alter table user add(age number(6));
　　alter table user add (course varchar2(30) default ‘空’ not null);
　　修改字段：
　　alter table 表名 modify (字段名 字段类型 默认值 是否为空);
　　alter table user modify((age number(8));
　　修改字段名：
　　alter table 表名 rename column 列名 to 新列名;
　　alter table user rename column course to newcourse;
　　删除字段：

alter table 表名 drop column 字段名；
　　alter table user drop column course;

表占空间大小
1、查询数据库中所有的表空间以及表空间所占空间的大小，直接执行语句就可以了：

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

2、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、查询所有表空间以及每个表空间的大小，已用空间，剩余空间，使用率和空闲率，直接执行语句就可以了：

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as “FREE%”, substr((total - free)/total * 100, 1, 5) as “USED%” from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

4、查询某个具体的表所占空间的大小，把“TABLE_NAME”换成具体要查询的表的名称就可以了：

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) “占用空间(M)”
from dba_segments t
where t.segment_type=‘TABLE’
and t.segment_name=‘TABLE_NAME’
group by OWNER, t.segment_name, t.segment_type;

5.多个表总空间
select sum(kongjian) from
(select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) kongjian
from dba_segments t
where t.segment_type=‘TABLE’
and t.segment_name like ‘GSP%’
group by OWNER, t.segment_name, t.segment_type);

插入
insert into 表名(字段1,字段2,字段3,) values (内容1,内容2,内容3) where 条件
insert into GSPUserConfig (ID,ConfigValue,StringValue) values (999,‘2020年’,‘4月17日’,‘BB7FCF67DC0E43CCB3FCB5E957A0330C’) where ConfigKey1 = ‘BIZDATEFORDRP’;
修改
update 表名 set 列名=新值 where条件;
去重
去掉数据里面除了ID外都相同的数据
delete from DKH_Information where DKH_ID not in (
select t.maxID from
(select max(DKH_ID) as maxID
from DKH_Information
group by DKH_CheckDate ,DKH_CheckPersonID ,DKH_CheckTD,DKH_CheckTypeID,DKH_CheckText,DKH_Demo,DKH_RuleType,DKH_CheckPersonName
) as t
);

复制表结构及数据
SqlServer中：#
　　目标表存在： INSERT INTO 目标表 SELECT * FROM 原表;
　　目标表不存在：SELECT * INTO 目标表 FROM 原表;
Oracle中：#
　　目标表存在：INSERT INTO 目标表 SELECT * FROM 原表;
　　目标表不存在：CREATE TABLE 目标表 AS SELECT * FROM 原表;

判断后再执行插入
审批流程节点添加
预付款单 -财务部负责人审批
insert into APPROVALCONFIG
select sys_guid(),‘YF’,’’,‘5’,‘110106’,‘7’,‘财务部经理’,‘cdd22a3c-8a85-4f28-bcda-bfb92693e39d’,‘T007’,‘财务部负责人’,‘0.00000000’,‘wgd’,‘16-MAY-19 11.03.04.000000 PM’,‘wgd’,‘16-MAY-19 11.03.04.000000 PM’,‘1’,’’
from APPROVALCONFIG where rownum = 1 AND NOT EXISTS
(select 1 from APPROVALCONFIG where DJLX = ‘YF’ and DWFW = ‘5’ and DWBH = ‘110106’ and SPJDXH = ‘7’ and JSID = ‘cdd22a3c-8a85-4f28-bcda-bfb92693e39d’ and JSBH = ‘T007’)

报错解决
无效月份
导入sql文件时报错
insert into LSBZDW (CREATEDTIME) values(‘29-NOV-14 09.25.24.726005 AM’);
1.这是因为客户端是中文环境，格式mon就不能用英文的月份写法，必须用中文的“六月”
如果不想修改sql语句运行的话，就需要在执行该语句之前，使用alter session 命令将nls_date_language修改为american，如下：
alter session set nls_date_language='american’以英语显示日期。
2若你的客户端是英文环境，那就是你没有用oracle数据的时间函数查询数据，应在sql语句中使用oracle数据库中的时间函数。
解决方法:1>执行sql语句报错时先执行:alter session set nls_date_language=‘american’;然后再执行sql语句
2>导入sql文件报错时,在文件的最前面加上alter session set nls_date_language=‘american’;
完成后执行alter session set nls_language=‘SIMPLIFIED CHINESE’;退出
方法2:(待验证)
今天将记录导出成sql文件，再导入到另一个数据库中相同的表，报错：无效的月份。查看发现：报错的字段类型是时间戳，导出的sql文件是字符串形式，找原因花费了很长时间，主要原因是plsql的字符集问题。
1.查询：查询字符集问题
select userenv(‘language’) from dual ; 和 select * from V$NLS_PARAMETERS;（第9行），查询结果应该一致，（我的结果分别是AMERICAN_AMERICA.AL32UTF8和AL32UTF8）。
2.修改：如果第1步结果不一致，修改环境变量（为防止乱码配置）NLS_LANG
（我的配置是：NLS_LANG = AMERICAN_AMERICA.UTF8）
3.重启
4.导入成功