SQL Base of Learning Oracle Database(1)

// system is username , oracle is the password of system
sqlplus system/oracle  
// current db login user
show user;
// print the dba_users
desc dba_users;
// print the username of dba_users;
select username from dba_users;

// table space
forever table space
temp table space
UNDO table space

// print tablespace_name
select tablespace_name from dba_tablespaces;
// print the user tablespaces;
desc user_tablesapces;
desc dba_users;
desc user_users;

// change user system tablespace is system table space
alter user system default tablesapce system;

// create tablespace 
create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;

// print tablespace files path in os
desc dba_data_files
select file_name from db_data_files where tablespace_name='TEST1_TABLESPACE';

// set tablespace online or offline
alert tablespace test1_tablespace offline;
desc dba_tablespaces;
select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

alter tablespace test1_tablespace add datafile 'test2file.dbf' size 10m;
alter tablespace test1_tablespace drop datafile 'test2file.dbf';

// delete tablespace 
drop tablespace test1_tablespace including contents;

// create table
create table userinfo (id number(6,0),username varchar2(20),userpwd varchar2 (20),email varchar2 (30),regdate date);
desc userinfo;
// print data
select * from userinfo;
// add column remark
alter table userinfo add remarks varchar2 (500);

alter table userinfo modify remarks varchar2 (400);

alter table userinfo modify userpwd number(6,0);

alter table userinfo drop column remarks;

alter table userinfo rename email new_email;

rename userinfo to user_info;

// clear the table datas in table
truncate table user_info;
// delete table datas and table 
drop table user_info;

insert into userinfo values (1,'xxx','123','xxx@126.com',sysdate);
select username,userpwd from userinfo;

// copy table and create a new table 
create table userinfo_new as select * from userinfo;

// 修改所有字段
update userinfo set userpwd = '111111';
update userinfo set userpwd='111',email='111@126.com';
update userinfo set userpwd = '123456' where username='xxx';

// delete table 
delete from userinfo where username = 'xxx';


alter table user_info modify username varchar2(20) not null;
alter table user_info modify username varchar2(20) null;

// set primary key 
create table userinfo (id number(6,0) primary key ,username varchar2(20),userpwd varchar2 (20),email varchar2 (30),regdate date);

create table userinfo (id number(6,0),username varchar2(20),userpwd varchar2 (20),email varchar2 (30),regdate date , constraint pk_id pk_username primary key (id,username));

alter table userinfo add constraint pk_id primary key (id);
// change the primary key constraint name 
alter table userinfo rename constraint pk_id to new_pk_id;

// enable and disable the constraint 
alter table userinfo disable constraint pk_id;
alter table userinfo enable constraint pk_id;
select constraint_name,status from user_constraints where table_name = "USERINFO";
// delete constraint pk_id
alter table userinfo drop constraint pk_id;