第02章 表格操作及事务处理

select * from v$nls_parameters t where t.PARAMETER='NLS_CHARACTERSET'--AL32UTF8(汉字3个字节)  、 ZHS16GBK(汉字2个字节）


--插入数据行 （insert的语法，使用values时，一次只能插入一条记录。）
--(char类型插入值时如果不加引号括起来时值001显示的结果是1，当成数值显示)
--1
INSERT INTO TBL_STUDENTINFO
    (STUNO,STUNAME,STUBIRTH,STUSEX,STUADDR,STUTEL) 
VALUES 
    ('05001','李四',sysdate,'0','江苏南京','12345');
    
select * from tbl_studentinfo;
    
--2
INSERT INTO TBL_STUDENTINFO 
   (STUNO,STUNAME,STUBIRTH,STUSEX,STUADDR,STUTEL)
VALUES 
    ('05002','张三',to_date('2005-12-25 12:25:59','yyyy-mm-dd hh:mi:ss'),0,NULL,NULL);
commit;
--3
INSERT INTO TBL_STUDENTINFO
    (STUNO,STUNAME,STUBIRTH,STUSEX,STUADDR,STUTEL) 
VALUES 
    ('05003','王五',sysdate,DEFAULT,'江苏南京','12345');
--4
INSERT INTO TBL_STUDENTINFO
VALUES 
    ('05004','五二',sysdate,1,'江苏南京','123456',NULL);
--5
select * from tbl_classinfo;


select * from tbl_scoreinfo;


INSERT INTO tbl_classinfo
VALUES('001','英语');


--6
INSERT INTO tbl_classinfo
VALUES('002','语文');
--7
INSERT INTO tbl_scoreinfo
VALUES('05001',001,55);    
--8
INSERT INTO tbl_scoreinfo
VALUES('05002',002,75);
--9
INSERT INTO tbl_scoreinfo
VALUES('05002',001,95);
--10
INSERT INTO tbl_scoreinfo
VALUES('05003',002,65);


select * from tbl_scoreinfo; 
select * from tbl_classinfo; 
select * from tbl_studentinfo  
--插入多行
create table tbl_studentinfo_new(
name varchar2(200),
address varchar2(200),
birthday varchar2(200)
);


select * from tbl_studentinfo_new


INSERT INTO tbl_studentinfo_new (name,address,birthday) 
SELECT             stuname,stuaddr,stubirth
FROM tbl_studentinfo


INSERT INTO tbl_studentinfo (stuno,stuname,stuaddr,stubirth) 
SELECT      '1004', name,address,birthday      
FROM tbl_studentinfo_new


--复制包含表结构和全部的数据
create table tbl_studentinfo_new2 as select * from tbl_studentinfo
--只复制表结构
create table tbl_studentinfo_new3 as select * from tbl_studentinfo where 1=0


select * from tbl_studentinfo_new3


select * from tbl_studentinfo_new;


select *from tbl_studentinfo


drop table tbl_studentinfo_new3
--更新数据行
--1
UPDATE tbl_studentinfo SET stusex = 1
--2
update tbl_studentinfo
set stuaddr='南京市秦淮区'
where stuaddr is null


UPDATE tbl_studentinfo 
SET stuaddr ='南京市白下区' 
WHERE stuaddr = '江苏南京' and stutel=12345
--3
update tbl_studentinfo
set stutel =stutel+2
where stuno=5002


UPDATE tbl_scoreinfo
SET score = score + 5
WHERE score <= 95


update tbl_studentinfo
set stuno=1002 
where stubirth=to_date('2005/12/25 12:25:59','yyyy/mm/dd hh:mi:ss')




select * from tbl_scoreinfo; 
select * from tbl_classinfo; 
select * from tbl_studentinfo  


--删除数据行 
DELETE FROM tbl_scoreinfo 
WHERE stuno ='05001';


delete from tbl_studentinfo
where stuno=1005 and stusex=1;


delete from tbl_scoreinfo
where classno=2 and score<70;


--删除表中所有的数据
delete from tbl_studentinfo


DELETE score from tbl_studentinfo;