Oracle 使用表

1./*============创建Customer表==========*/ 2.create table Customer 3.( 4. Customer_id number(6) not null, 5. Customer_name varchar2(50) not null, 6. Password varchar2(20) not null, 7. True_name varchar2(20), 8. Email_address varchar2(50) not null, --唯一 9. Password_question varchar2(50) not null, 10. Password_anwser varchar2(50) not null, 11. Status char(1), --默认是1，取值0或1 12. Customer_level char(1), --默认是1，取值1，2，3 13. Score number(6), 14. Register_date date, --默认为系统时间 15. Login_time timestamp, 16. Login_count number(6), 17. Login_ip char(6) 18.); 19./*===========创建Orders表==========*/ 20.create table Orders 21.( 22. Order_id varchar2(10) not null, 23. Order_Customer_id number(6) not null, 24. Order_date date not null, 25. Order_price number not null 26.); 27./*==========查询Customer表===========*/ 28.select * from Customer 29. 30./*==========向表中添加数据===========*/ 31.insert into Customer values 32.(220077,'wantingqiang','wtq','万廷强','lovezhqj@qq.com','你是哪个?','wtq','1','3',150,sysdate,sysdate,15789,'172.26') 33. 34./*==========修改表Customer===========*/ 35.alter table Customer 36. modify Login_ip char(16); 37. 38. /*=========修改第一条记录中的ip=====*/ 39. update Customer set Login_ip='172.26.3.145' where Customer_id='220077';--ip地址修改成功 40. 41. /*=========添加一个列===============*/ 42. alter table Customer 43. add LoginOut_time date; --新列增加成功 44. 45. /*=========删除一个列LoginOut_time========*/ 46. alter table Customer 47. drop column LoginOut_time; --列删除成功 48. 49. /*========给列添加注释===========*/ 50. comment on column 51. Customer.Customer_Name is '客户姓名'; 52. 53. /*========给表添加注释==========*/ 54. comment on table Customer is '客户表，为了保持与客户的联系'; 55. 56. /*========重新命名表============*/ 57. alter table Customer rename to Customer_Change; 58. 59. select * from Customer_Change 60. 61. alter table Customer_Change rename to Customer; 62. 63. /*==========添加非空约束========*/ 64. alter table Customer 65. modify status not null; --非空约束添加成功 66. 67. /*==========添加主键约束========*/ 68. alter table Customer 69. add constraint customer_id_pk primary key(Customer_id); --主键添加成功 70. 71. /*==========添加外键约束========*/ 72. --向Orders表中添加外键，与Customer表关联 73. --在下面的列子末尾加上： 74. --on delete 表示允许级联删除 75. --on update 表示允许级联更新 76. alter table Orders 77. add constraint Orders_Customer_fk foreign key(Order_Customer_id) references Customer(Customer_id); 78. --外键添加成功 79. /*==========删除外键约束=========*/ 80. alter table Orders 81. drop constraint "ORDERS_CUSTOMER_FK"; --外键删除成功，这个要注意大小写哈 82. 83. /*=========添加唯一约束========*/ 84. alter table Customer 85. add constraint un_email unique(Email_address); --添加唯一约束成功 86. 87. 88. /*=========修改默认约束========*/ 89. alter table Customer 90. modify Status default('1'); 91. 92. /*=========添加检查约束========*/ 93. alter table Customer 94. add constraint ck_status check(Status in ('1','0')); --检查约束添加成功 95. 96. 97. /*==========禁止检查约束=======*/ 98. alter table Customer 99. disable constraint ck_status; --禁止成功 100. 101. /*==========激活检查约束=======*/ 102. alter table Customer 103. enable constraint ck_status; --激活成功 104. 105. /*==========删除检查约束========*/ 106. alter table Customer 107. drop constraint ck_status; --删除检查约束成功 108. 109./*==========最后事删除表========*/ 110. drop table Customer; --删除表成功 111.