插入数据
--向可以为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
查询结果