用户操作
select * from v$tablespace;
create tablespace student
datafile 'C:\myoracle\oradata\orcl\stu01.dbf'
size 100m
reuse;
create user stu_oper
identified by stu_oper
default tablespace student;
select * from dba_users;
grant connect to stu_oper;
grant resource to stu_oper;
select * from dba_users;
表的操作
CREATE TABLE STUDENT (
STUID VARCHAR2(7) NOT NULL,
STUNAME VARCHAR2(10) NOT NULL,
GENDER VARCHAR2(3) NOT NULL,
AGE NUMBER(2) NOT NULL,
SEAT NUMBER(2) NOT NULL,
ENROLLDATE DATE,
STUADDRESS VARCHAR2(50) DEFAULT '地址不详',
CLASSNO VARCHAR2(4) NOT NULL
);
select * from student;
INSERT INTO "STU_OPER"."STUDENT" ("STUID", "STUNAME", "GENDER", "AGE", "SEAT", "ENROLLDATE", "STUADDRESS", "CLASSNO") VALUES ('S117301', '张三', '男', '19', '11', TO_DATE('2017-09-01 17:13:21', 'SYYYY-MM-DD HH24:MI:SS'), '地址不详', '2017');
INSERT INTO "STU_OPER"."STUDENT" ("STUID", "STUNAME", "GENDER", "AGE", "SEAT", "ENROLLDATE", "STUADDRESS", "CLASSNO") VALUES ('S117302', '李四', '女', '20', '12', TO_DATE('2017-09-01 17:14:30', 'SYYYY-MM-DD HH24:MI:SS'), '地址不详', '2017');
CREATE TABLE PERSON AS SELECT * FROM STUDENT;
select * from person;
CREATE TABLE PERSON1 AS SELECT * FROM STUDENT WHERE STUID='1' ;
select * from person1;
alter table person add phone varchar2(11);
alter table person add (
fatherName varchar2(16) not null,
fatherPhone varchar2(11)
);
alter table person1 add (
fatherName varchar2(16) not null,
fatherPhone varchar2(11)
);
select * from person1;
alter table student modify (age number(2) default 0);
INSERT INTO "STU_OPER"."STUDENT" ("STUID", "STUNAME", "GENDER", "SEAT", "ENROLLDATE", "STUADDRESS", "CLASSNO") VALUES ('S117303', '王五', '女', '13', TO_DATE('2017-09-01 17:14:30', 'SYYYY-MM-DD HH24:MI:SS'), '地址不详', '2017');
select * from STUDENT;
alter table person1 modify fatherName varchar2(12);
alter table person rename column gender to sex;
alter table person1 drop column fatherName;
alter table person1 drop column fatherPhone;
select * from person1;
select * from user_tables;
rename person to person_new;
drop table person_new;
drop table person1;