Oracle插入数据、查询数据、更新数据、删除数据

一、插入数据

注意：

（1）字符串引用用的是英文状态的单引号'xx'；
（2）与mysql不同的是insert into tablename [(field1,field2,...)]values(v1,v2,...)一次只能插入一条记录；
（3）多条记录的插入（借助表dual）：
    insert into tablename (field1,field2,...)
　　      select v1,v2,... from dual
    union all select v1,v2,... from dual
    union all select v1,v2,... from dual
    ...

（1）、单条数据插入

-- 单条数据插入
-- insert into tablename [(field1,field2,...)]values(v1,v2,...)
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901001', 'jalen', '1', 27, 'C201901', '广东省广州市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224221993XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901002', 'momo', '1', 26, 'C201901', '贵州省毕节市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224201994XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901003', 'jingjing', '1', 27, 'C201901', '广东省广州市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224221993XXXXXXXX');
insert into jalen.stuinfo (stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber) values 
('SC201901004', 'liangliang', '1', 26, 'C201901', '贵州省毕节市XXX号', '2019',to_date('01-09-2019', 'dd-mm-yyyy'),'5224201994XXXXXXXX');
 
-- insert into tablename [(field1,field2,...)]values(v1,v2,...)
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber) values 
('1025001','liwei','1',50,'教授','数学','523429xxxxxxxx2356');
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber) values 
('1025002','wangli','1',48,'教授','语文','552429xxxxxxxx2356');

（2）多条数据插入

-- 多条数据插入
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber)
          select '1025003','liucha','1',48,'讲师','英语','520427xxxxxxxx3009' from dual            
union all select '1025004','huanghuang','0',46,'讲师','英语','520250xxxxxxxx5201' from dual
union all select '1025005','jiya','0',46,'教授','语文','250250xxxxxxxx2501' from dual

（3）查询结果的备份(存储)

create table jalen.teacher01 as 
select * from jalen.teacher;

（4）查询并插入他表

-- 清空表
truncate table jalen.teacher;
 
-- 查询并插入新标
insert into jalen.teacher (teacherid,terchername,sex,age,post,subject,idnumber)
select teacherid,terchername,sex,age,post,subject,idnumber from  jalen.teacher01;

二、简单查询

select * from jalen.stuinfo;
select * from  jalen.teacher;
select * from jalen.teacher01;

三、更新数据

-- update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件
update jalen.teacher set post = '副教授',subject ='历史' where teacherid = '1025002';
 
-- update 利用另外一张表关联更新本表数据的命令结构如下：
update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） 
       where exists (select 1 from 表2 where 表1.列名=表2.列名)

四、删除（清空）表

-- delete from 表名 where 条件 
 delete from jalen.teacher01 where teacherid = '1025005'; 
 
 -- truncate table 表名 
 truncate table jalen.teacher01; 
 
 -- drop table 表名
 drop table jalen.teacher01;

---