表管理

/*
  目前主流数据库:
  微软:SQL Server和Access
  瑞典MySQL AB公司mysql
  IBM公司:db2
  美国Sybase公司:Sybase
  IBM公司:informix
  美国Oracle公司:oracle
*/

--查询所有表
select * from tab ;

--创建表person
create table person
(
       id varchar2(18) not null,
       name varchar(10) not null,
       sex varchar2(2) default('男'),
       age number(3) ,
       birthday date
);
--default写法有两种
create table person2
(
       id varchar2(18) not null,
       name varchar(10) not null,
       sex varchar2(2) default '男',
       age number(3) ,
       birthday date
);
--插入数据
insert into person(id,name,sex,age,birthday) values(100011,'张三','女',20,to_date('2009-10-10','yyyy-mm-dd'));
insert into person(id,name,sex,age,birthday) values(100012,'李四',default,28,to_date('2009-10-10','yyyy-mm-dd'));
insert into person(id,name,age,birthday) values('100013','王五',30,to_date('2009-10-10','yyyy-mm-dd'));

--插入数据 (可以从其它表中将数据复制插入)
/*
       当前用户为dboy/pass,复制scott/tiger中的emp表中一条记录
*/
insert into emp select * from scott.emp where empno=7369;

--查询表
select * from emp ;
select * from person;

--删除表
drop table person ;

--复制表
create table temp as select * from person2 ;

--修改表
alter table temp add address varchar(50) ;

select * from temp ;

alter table temp modify address varchar(100)  ;

alter table person2 add address varchar(100) default '暂时无地址';
alter table person2 drop column address ;

--重命名表
rename temp to newtemp;

select * from tab ;

--初始化表
truncate table newtemp ;

--约束
--1 主键约束 primary key
create table t1(name varchar(10) primary key);
insert into t1(name) values('person');
insert into t1(name) values(null);       --默认不为空
insert into t1(name) values('person');   --违反了唯一约束条件

create table t2(name varchar(10),constraint pk_name primary key(name) ) ;
create table t3(name varchar(10));
alter table t3 add constraint pk_name2 primary key(name);

--2 非空约束 not null
create table t4(name varchar(10) not null,sex varchar(2) not null);

insert into t4(name,sex) values(null,'男');
insert into t4(sex) values('女');

--3 唯一约束 unique

create table t5(name varchar(10) not null unique,tel varchar(12) not null unique);
insert into t5(name,tel) values('zs','13218102560');
insert into t5(name,tel) values('zs','13218102560');            --违反了唯一约束条件

create table t6(name varchar(10) not null,tel varchar(12) not null,constraint uk_name unique(name));
insert into t6(name,tel) values('zs','13218102560');
insert into t6(name,tel) values('zs','13218102560');

--4 检查约束 check
create table t7(age int not null,sex varchar(2) not null);

alter table t7 add constraint chk_age check(age between 0 and 120);
alter table t7 add constraint chk_sex check(sex in('男','女'));

insert into t7(age,sex) values(150,'男');
insert into t7(age,sex) values(50,'中');

--5 外键约束 foreign key
drop table t8 ;
create table t8 (id int not null primary key,addres varchar2(50));
create table t9 (id int not null ,infoid int not null,name varchar2(50),constraint fk_infoid_t8 foreign key(infoid) references t8(id));

ALTER TABLE t10 ADD CONSTRAINT FK_REFERENCE_1 FOREIGN KEY (PRODUCTID)
      REFERENCES PRODUCT (PRODUCTID);

insert into t8(id,addres)
select 1,'苏州' from dual union all
select 2,'浙江' from dual union all
select 3,'安徽' from dual ;

insert into t9(id,infoid,name)
select 1,1,'好' from dual union all
select 2,2,'较好' from dual union all
select 3,3,'最好' from dual  ;

select * from t9;

--查看约束
select constraint_name from user_constraints;
select constraint_name,table_name from user_constraints where lower(constraint_name) like 'fk_%';

--删除约束
alter table t9 drop constraint fk_infoid_t8 ;

--查看表结构
--desc table_name只能用在sqlplus/sqlplusw中
select column_name,data_type,data_length,nullable,data_default from all_tab_columns where lower(table_name)='t9' ;

--查看当前用户有多少表
select object_name,object_type from user_objects where lower(object_type)='table' ;

--查看数据库全部表
select * from all_tables;

--查看所有用户
select * from all_users;