oracle 建表、主键、分区

1.创建表：

create table student(

	s_name nvarchar2(20),

	s_sex nchar(2),

	s_age int);

消除重复

select distinct

删除表

drop table student;

查看表

select * from  student;

插入数据

insert into student values('张三','男',12);或者student(字段名)

查询插入

多表插入

查看表结构

desc student;

删除数据

delete from student where s_name='张三';

修改表名

rename student to stt;

删除字段

alter table student drop column s_name;

修改数据

update student set name='李四' where name='张三'

修改表中的字段名

alter table student rename column s_name to s_name2;

给表加备注

comment on table student is '你是谁';

查看表的备注信息

select *from user_tab_comments where TABLE_NAME='STUDENT';

添加字段

alter table student add address nvachar2(10);

修改字段

alter table student modify address nvachar2(10);

复制表

create table stud3 as select * from student;

2.列操作

**给表salary_grades添加虚拟列，虚拟列

   ALTER TABLE salary_grades ADD (average_salary AS ((low_salary + high_salary)/2));

修改列的大小

ALTER TABLE order_status2 MODIFY status VARCHAR2(15);

修改数字列精度

ALTER TABLE order_status2 MODIFY id NUMBER(5);

修改数据类型

ALTER TABLE order_status2 MODIFY status CHAR(15);

修改默认值

ALTER TABLE order_status2 MODIFY last_modified DEFAULT SYSDATE - 1;

3.主键和外键

--为表添加主键

create table student31

(s_id int primary key,  --字段类型后直接加上主键关键词即可

 s_name nvarchar2(20),

 s_age int

);

insert into student31 values(1,'zhang',18);

insert into student31 values(2,'li',20);

--表建立好后，如何添加主键

--alter table student31 add constraint 主键约束名  主键关键词(字段名);

alter table student31 add constraint pk_s_id primary key(s_id);

--举例：学生表和课程表建立外键

create table stu1

(s_id  int,

 s_name nvarchar2(20),

 c_id int 

);

create table course1 

(c_id int,

 c_name varchar2(20)

);

--给course表添加主键

alter table course1 add constraint pk_c_id1 primary key(c_id);

--给student表添加主键

alter table stu1 add constraint pk_s_id primary key(s_id);

--在学生表中建立一个外键，通过去引用课程表中的主键

alter table stu1 add constraint fk_c_id foreign key(c_id) references course1(c_id);``

4.分区表
4.1oracle创建非分区表:

create table student31
(s_id int primary key,
 s_name nvarchar2(20),
 s_age int
);

4.2oracle创建分区表:

create table p_range_test(id number,name varchar2(100))
partition by range(id)(
partition t_p1 values less than (10),
partition t_p2 values less than (20),
partition t_p3 values less than (30)
);

--查创建好分区表的信息：

select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position;

--添加一个分区
alter table p_range_test add partition t_p4 values less than(40);

--删除表

drop table p_range_test purge;

--创建带有maxvalue的分区表

create table p_range_maxvalue_test (id number,name varchar2(100))
partition by range(id)(
partition t_p1 values less than (10),
partition t_p2 values less than (20),
partition t_p3 values less than (30),
partition t_pmax values less than (maxvalue));

--添加分区会报错

alter table p_range_maxvalue_test add partition t_p4 values less than(40);

--使用split完成上面没有完成的分区任务

alter table p_range_maxvalue_test split partition t_pmax at (40) into (partition, partition t_pmax);