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;
--插入数据行 (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;