ORACLE数据库常用操作命令

ORACLE数据库常用操作命令

ORACLE数据库各个操作命令语句示例
创建表CUSTOMER
create table customer
(
 customerName varchar2(20) primary key,
 sex       varchar2(20),
 birthday   date ,
 address    varchar2(20)
)
创建数据表BUY
create table buy 
(
buyId int  primary key ,
buyName varchar2(20),
amount int ,
customerName varchar2(20)
)


 在BUY表中加入外键
alter table buy add constraint buy_customer_fk foreign key (customerName) references customer (customerName);
在customer表中新添加一个AGE的字段
alter table customer add age varchar(20);
在customer表中更改一个AGE的字段使它的数据类型改成VARCHAR
alter table customer modify age varchar2(20);
在customer表中删除一个AGE的字段
alter table customer drop column age;

截断表customer表中的所有数据但是保留表的结构不产生日志文件
（例如表中有数据NAME（张三）AGE（22）那么会将张三和22删除NAME和AGE结构保留）
方法 truncate table customer;
向customer表中插入数据(三种插入方法）
insert into customer values ('张三','男',to_date('2004-03-23','yyyy-mm-dd'),'深圳市罗湖区');
insert into customer (customName,sex,birthdaay,address) values ('李四','男',to_date('2005-07-22',yyyy-mm-dd')，‘广州市越秀区');
insert into customer ('&a1','&a2',&a3,'&a4');
insert into customer values ('赵五','男',sysdate,'深圳市罗湖区');//systdate代表系统当前日期

向buy表中插入数据
insert into buy values ('101','buyName1','1000','aa');
insert into buy values ('102','buyName2',1000,'aa');
insert into buy values ('103','buyName2',1000,'aa');
insert into buy values ('104','buyName2',1000,'bb');

insert into buy values ('105','buyName2',1000,'cc');
insert into buy values ('106','buyName2',1000,'cc');
dual(傀儡):oracle的所有select必须有from，用dual代替 具体用法见后面

主外键
主表有从表才有同时可以是空值（NULL）但是从表删除主表才可以删除
-修改customer表将customerName=aa对应的name的值为AAA
update buy set customerName=aa where customerName='aa' ;
update customer set customerNmae='AAA' where customerName='aa';
update buy set customerName='AAA' where customerName is null;
用select语句实现联接、分组、排序、分组的结果中筛选
注意连接查询是将两个表的列合并在一起
select c.*,b.* from customer c join buy b on  c.customerName=b.customerName;
select c.*,b.* from customer c,buy b where c.customerName=b.customerName;

--左联接(左边中所有数据查出，右表中数据匹配的查出)
select c.*,b.* from customer c left join buy b on c.customerName=b.customerName;
insert into customer values('dd','男',to_date('2004-01-01','yyyy-mm-dd'),'a1');
--右联接(左表中数据匹配查出，右表中数据全部查出）
insert into buy values('107','buyName2',1000,null);
insert into buy values('108','buyName2',1000,null);
select c.*,b.* from customer c right join buy b on c.customerName=b.customerName;
--select 字段 from 表 where 条件 group by having order by ;
--分组处理
select sum(amount),customer.customerName from
 buy join customer on buy.customerName=
customer.customerName group by customer.customerName;
--分组的结果加条件
select sum (amount),customer.customerName from
buy join customer on buy.customerName=
customer.customerName group by customer.customerName having
sum(amount)>=2000 order by customer.customerName;
--子查询(子查询的结果是前一查询的条件）
select * from buy where customerName in(select customerName form customer where 
customerName in('张三','李四')and buy.customerName=customer.customerName)
--删除buy表中buyid在是101或1022或103的数据 in
delete from buy where buyID in(101,102,103);
--like (模糊匹配查询）
   select * from buy where buyName like 'buyName';//查找和表中buyName一样的数据
select * from buy where buyName like 'buy%';//查找表后三位相同的数据
select * from buy where buyName like '%buy';//查找表前三位相同的数据
select * from buy where buyName like '%N%';//查找表中包含有N（字母）的数据
--between的用法
select * from buy where buyID between 101 and 104;//查找购买编号在101-104之间的数据
select * from buy where buyID>=101 and buyID<=104;//查找购买编号大于101小与104之间数据
--产生一个新表且有数据
create table customer_1 as select customerName,sex from customer;
--产生一个新表结构，但没有数据
create table customer_2 as select * from customer where 1=2;
--向一个新表结构批量插入值
insert into customer_2(customerName,sex) select customerName,sex from customer;
--联合运算(是将两个表的行合并在一起）
     --并集(union)
-(唯一）-
select customerName,sex from customer_1 union select customerName,sex from customer_2;
-(重复）-
select customerName,sex from customer_1 union all select customerName,sex from customer_2;
-(交集)(Intersect)
select customerName,sex from customer_1 intersect select customerName,sex from customer_2;
--在第一个中有，但没有在第二个结果中(minus)
select customerName,sex from customer_1 minus select customerName,sex from customer_2;
--两个字符连合在一下||
select customerName||'的性别是'||sex||' 生日是'||birthday from customer;
--日期处理
--增加几个月后的日期
select add months(sysdate,6)from dual;//系统当前日期往后加六个月
--一个月后的最后一天(last_day)
select last_day ( to_date('2004-03-23','yyyy-mm-dd'))from dual;
--两个日期之间的月份数(months_between)
select months_between(to_date(2003-02-01','yyyy-mm-dd'),sysdate) from dual;//所给日期和系统日期之间的月数
--四舍五入
select round(to_date('2004-07-01','yyyy-mm-dd'),'year') from dual;
select round(to_date('2004-07-12','yyyy-mm-dd'),'month') from dual;

--最近的星期天
select round(sysdate,'day') from dual;
--星期(日是第一天)
--截取日期(year,day,month)
select trunc(to_date('2004-07-01','yyyy-mm-dd'),'year') from dual;
select trunc(to_date('2004-07-12','yyyy-mm-dd'),'month') from dual;
--上一个星期天
select trunc(sysdate+4,'day') from dual;
--得到最大日期(greatest)
select greatest(sysdate,sysdate-100,sysdate+400000) from dual;
--extract(year from sysdate)
select to_char(sysdate,'mm') from dual;
select extract(month from sysdate) from dual;
--下一个星期几的日期(next_day)
select next_day(sysdate,3) from dual;
--字符处理
--第一个字符转为大写
select initcap('aaaa') from dual;
--全部转化为大写
select upper('aaaa') from dual;
--全部转化为小写
select lower('AAAA') from dual;
--截取左边的一些字符(ltrim)
select ltrim('aaaaacccccccccccc','a') from dual;
--截取右边的一些字符(rtrim)
select rtrim('aaaaacccccccccccc','c') from dual;
--替换(replace)
select replace('aaaaaIIIIIIcccccccccccc','IIIIII','************') from dual;//将原本III的地方替换成*号
--小于等于原长度替换(translate)
select translate('aaaaaabcdefcccccccccccc','abcdef','123456******') from dual;
--查找(instr)
select instr('abcdef','c') from dual;
--取字符(substr)从第几个位置取几
select substr('abcdef',3,2) from dual;//从第三个位置开始取取两个得结果是CD
--合并两个字符(concat)
select concat('a','***') from dual;//得到结果是a***
--左边填充
select lpad('1',15,'0') from dual;
--右边填充
select rpad('1',15,'0') from dual;
--decode
select decode('kk','男','male','女','fmale','不清楚') from dual;
select decode(sex,'男','male','女','fmale','不清楚') from customer;//如果性别是男则显示MALE是女则显示FMALE
--chr
select chr(97) from dual;
--length(字符数)
select length('张三') from dual;//字符数一个汉字一个字符则有3个。
--vsize(字节数，如果utf-8格式一个汉字3个字节，如果unicode一个汉字2个字节)
select vsize('张三') from dual;//默认一般都是UTF-8那么返回则为6个字节

--日期转化为字符
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;//傀儡用法
--数字转化为字符
select to_char(99.99,'＄99,9999.99') from dual;

--字符转化为日期
select to_date('2004-01-01 12:30:33','yyyy-mm-dd hh24:mi:ss') from dual;
--字符转化为数字
select to_number('12345') from dual;

--其它函数
--得到当前登陆用户(uid)
select uid from dual;

--得到当前登陆用户名(user)
select user from dual;

--常规函数
--如果为空，用一个字符代替(nvl)
select customerName,nvl(address,'是空的') from customer;

--如果不为空，用一个字符代替，为空用另一个字符代替(nvl2)
select customerName,nvl2(address,'不是空的','是空的') from customer;
--相等返回为空(nullif)
select nullif(address,address) from customer;
--分组函数(不计空值)
--avg
--min
--max
--sum
--count
select count(address) from customer;
select avg(amount) from buy;
select count(buyID) from buy;
--ROWID：当前行对应的地址
select rowid from customer;
--rownum:返回一个数值代表行的次序，最大的作是分页。
select * from buy where rownum<=3;
--分页的实现，每页2行
--第一页
select * from buy where rownum <=(1)*2
minus
select * from buy where rownum <=(1-1)*2;
--第二页
select * from buy where rownum <=(2)*2
minus
select * from buy where rownum <=(2-1)*2;
--第三页
select * from buy where rownum <=(3)*2
minus
select * from buy where rownum <=(3-1)*2;
--第五页
select * from buy where rownum <=(5)*2
minus
select * from buy where rownum <=(5-1)*2;

--分析涵数
--唯一的排序值row_number() over (order by sal desc)
--不分组
select ENAME,sal,DEPTNO,row_number()over (order by sal desc) as 名次 from emp;

--分组(partition by deptno order by sal desc)
--值相等序号相同，其后的序号不连续 rank() over (order by sal desc)
--不分组
select ENAME,sal,DEPTNO,rank() over (order by sal desc) as 名次 from emp;
--分组
--值相等序号相同，其后的序号连续dense_rank() over (order by sal desc)
--不分组
select ENAME,sal,DEPTNO,dense_rank()over (order by sal desc) as 名次 from emp;
--分组
select ename,sal,deptno,dense_rank()over (partition by deptno order by sal desc)from emp;



数据库增删改查方法事例
create table woman
(manId int primary key,
 manName varchar(10),
 mansex varchar(10)
);

delete from woman  where manId='3' or manId=4;//同时删除两个行
update woman set manName='kkkk', mansex='bb' where manId=3;//一行里同时删除两个字段
insert into woman values(1,'ddd','男');//增加的两种写法
insert into woman (manId,manName,mansex) values (2,'cccd','男');