常用Oracle表操作语句
/*============创建Customer表==========*/ create table Customer ( Customer_id number(6) not null, Customer_name varchar2(50) not null, Password varchar2(20) not null, True_name varchar2(20), Email_address varchar2(50) not null, --唯一 Password_question varchar2(50) not null, Password_anwser varchar2(50) not null, Status char(1), --默认是1，取值0或1 Customer_level char(1), --默认是1，取值1，2，3 Score number(6), Register_date date, --默认为系统时间 Login_time timestamp, Login_count number(6), Login_ip char(6) ); /*===========创建Orders表==========*/ create table Orders ( Order_id varchar2(10) not null, Order_Customer_id number(6) not null, Order_date date not null, Order_price number not null ); /*==========查询Customer表===========*/ select * from Customer /*==========向表中添加数据===========*/ insert into Customer values (220077,'wantingqiang','wtq','万廷强','lovezhqj@qq.com','你是哪个?','wtq','1','3',150,sysdate,sysdate,15789,'172.26') /*==========修改表Customer===========*/ alter table Customer modify Login_ip char(16); /*=========修改第一条记录中的ip=====*/ update Customer set Login_ip='172.26.3.145' where Customer_id='220077';--ip地址修改成功 /*=========添加一个列===============*/ alter table Customer add LoginOut_time date; --新列增加成功 /*=========删除一个列LoginOut_time========*/ alter table Customer drop column LoginOut_time; --列删除成功 /*========给列添加注释===========*/ comment on column Customer.Customer_Name is '客户姓名'; /*========给表添加注释==========*/ comment on table Customer is '客户表，为了保持与客户的联系'; /*========重新命名表============*/ alter table Customer rename to Customer_Change; select * from Customer_Change alter table Customer_Change rename to Customer; /*==========添加非空约束========*/ alter table Customer modify status not null; --非空约束添加成功 /*==========添加主键约束========*/ alter table Customer add constraint customer_id_pk primary key(Customer_id); --主键添加成功 /*==========添加外键约束========*/ --向Orders表中添加外键，与Customer表关联 --在下面的列子末尾加上： --on delete 表示允许级联删除 --on update 表示允许级联更新 alter table Orders add constraint Orders_Customer_fk foreign key(Order_Customer_id) references Customer(Customer_id); --外键添加成功 /*==========删除外键约束=========*/ alter table Orders drop constraint "ORDERS_CUSTOMER_FK"; --外键删除成功，这个要注意大小写哈 /*=========添加唯一约束========*/ alter table Customer add constraint un_email unique(Email_address); --添加唯一约束成功 /*=========修改默认约束========*/ alter table Customer modify Status default('1'); /*=========添加检查约束========*/ alter table Customer add constraint ck_status check(Status in ('1','0')); --检查约束添加成功 /*==========禁止检查约束=======*/ alter table Customer disable constraint ck_status; --禁止成功 /*==========激活检查约束=======*/ alter table Customer enable constraint ck_status; --激活成功 /*==========删除检查约束========*/ alter table Customer drop constraint ck_status; --删除检查约束成功 /*==========最后删除表========*/ drop table Customer;