oracle学习与总结

插入数据

--向可以为null的字段插入数据时，插入null与省略的结果是一样的
--类型为char或者varchar类型的字段插入空字符串显示为空白
insert into emp1 values (1,'李四',null)
insert into emp1 values (2,'李四')
insert into emp1 values (3,'')

显示结果

一条语句插入多条数据

--oracle语法
insert all
into dept(deptno,dname,loc) values(60,'CHENG','YANG')
into dept(deptno,dname,loc) values(70,'CHENG','YANG')
select 1 from dual--必须加

--mysql与pgsql都可以使用
insert into emp1(id,name,mgrid) values //VALUES列表中每个成员的长度必须相同
(1,'李四',null),    
(2,'李四',null),
(3,'',null)     

实现树形结构查询

--创建表
CREATE TABLE province(
id NUMBER(8) NOT NULL,
pid NUMBER(8) NOT NULL,
name VARCHAR(32) NOT NULL,
PRIMARY KEY (id) 
);
--插入数据
insert into province (ID, pid, NAME)values (1,0,'山东');
insert into province (ID, pid, NAME)values (2,1,'菏泽');
insert into province (ID, pid, NAME)values (3,1,'济南');
insert into province (ID, pid, NAME)values (4,1,'济宁');
insert into province (ID, pid, NAME)values (5,2,'成武');
insert into province (ID, pid, NAME)values (6,2,'巨野');
insert into province (ID, pid, NAME)values (7,2,'曹县');
insert into province (ID, pid, NAME)values (8,5,'田集');
insert into province (ID, pid, NAME)values (9,5,'南鲁');
insert into province (ID, pid, NAME)values (10,4,'金乡');
insert into province (ID, pid, NAME)values (11,4,'微山');
--查询
SELECT a.id,a.pid,sys_connect_by_path(a.NAME,'->')as "级别",NAME,level//level是伪列
FROM province a
START WITH id=1
CONNECT BY PRIOR a.id = a.pid 
ORDER  BY level,a.id
update province set pid=1 where id=5

查询结果