Oracle常用表操作

//创建表，设置主键和外键
create table product(
id INTEGER CONSTRAINT PK_ID PRIMARY KEY,
name varchar(20),
typeid INTEGER,
FOREIGN KEY(typeid) references produc_types(PRODUCT_TYPE_ID)
)
//创建表，设置联合主键，外键
create table orderitem(
c_id integer,
p_id integer,
count integer,
price integer,
primary key(c_id,p_id),
foreign key(c_id) references customers(customer_id),
foreign key(p_id) references product(id)
)
//修改表，增加列
alter table product add(column_name TYPE);
//修改表 删除列
alter table product drop column column_name;
//修改表 修改列类型
alter table product modify price varchar(4);
//修改表 修改列名
alter table product rename column price to pro_price;

//修改表 修改表名

rename old_table_name to new_table_name

//添加虚拟列，引用表中其他已有的列（11g新特性）
alter table price add (avg_price as ((low_price+high_price)/2));

//修改列的默认值

 alter table price modify available default '1';

//修改表约束
alter table customers add(constraint c_sex check(sex='f' or sex='m'))
//修改表约束，增加主键
alter table customers add(constraint "pk_id" primary key(customer_id))

//插入数据
 insert into product values(1,'c++',1,10)
//更新列值
update orderitem set price=30 where c_id=1 and p_id=1
//对列求和
select sum(price) from orderitem
//对列求平均值
select avg(pro_price) from product
//子查询
//where条件
select * from orderitem where p_id=(select id from product where typeid=1)

select * from orderitem where price>(select avg(pro_price) from product)
//查询除平均价格最高商品类的其他类别商品以及平均价格。
//having条件
select typeid,
avg(pro_price) from
product
group by typeid
having avg(pro_price)<
(select max(avg(pro_price)) from product group by typeid) order by typeid
//from条件
select id from(select id from product where id<2)
//使用表别名
select pa.id,pro_price,item.count from product pa,(select p_id,count from orderitem) item where pa.id = item.p_id

//获得表的信息

describe table_name

//活的表中列的信息

select * from user_tab_colums