Oracle&JPA第一天作业2

/*********************************TableCreation**********************************************************/
drop table login cascade CONSTRAINTS;
drop table Customer cascade CONSTRAINTS;
drop table Account cascade CONSTRAINTS;
drop table Transaction cascade CONSTRAINTS;

万一有表存在，所以先要删除掉可能会存在的表。

//创建第1个表Login
Create Table Login(
 UserID varchar2(8) Primary Key,
 Password varchar2(20) Not Null,
 Role char(1) Check (Role IN('M','C','S'))
);

需要注意的地方：

1.怎么设置主键

2.怎么设置不为空

3.怎么设置默认可选值

//创建第2个表Customer
Create Table Customer(
 CustomerID Number(5) Primary Key,
 UserID varchar2(8) References Login(UserID),
 Name varchar2(25) Not Null,
 Gender Char(1) Check(Gender in ('M','F')),
 DateOfBirth Date, 
 Address Varchar2(30), 
 City Varchar2(10), 
 State Varchar2(10), 
 Pin Number(6),
 Telephone Number(12),
 Fax Number(12),
 Email Varchar2(30)
);

需要注意的地方：

1.设置主键

2.设置外键

3.不为空

4.默认可选值

//创建第3个表Account
Create Table Account(
 AccountNo Number(4) Primary Key,
 CustomerID  Number(5) References Customer(CustomerID ),
 AccountType Char(1) Check(AccountType in ('S','F','C')),
 DateOfOpening Date Not Null,
 DateOfMaturity Date,
 CurrentBalance Number(9,2) Not Null,
 Status  Char(1) Check(Status in ('C','F'))
);

需要注意的地方：

1.主键

2.默认可选值

3.不为空

//创建第4个表Transaction
Create Table Transaction(
 TransactionID Number(7) Primary Key,
 AccountNo Number(12) References Account(AccountNo),
 DateOfTransaction Date,
 Amount Number(9,2),
 TransactionType Char(1) Check(TransactionType in ('D','W')),
 Description Varchar2(25)
);

需要注意的地方：

1.主键

2.外键

3.默认可选值

/*********************************Insert Data**********************************************************/

Insert into Login Values('Jo01Jo','pass1','C');
Insert into Login Values('Tom2To','pass2','M');
Insert into Login Values('Jess33ca','pass3','S');

Insert into Customer Values(10001,'Jo01Jo','John','M','25-11月-1979','#113, x-block','Bangalore','India',123456,08157123451,NULL,'john@nyus.co.us');
Insert into Customer Values(10002,'Tom2To','Tom Cruise','M','29-7月-1972','#028, k-block','Mysore','India',567890,987654765,112-234-555,'tom@yahoo.com');
Insert into Customer Values(10003,'Jess33ca','Jessica','F','19-1月-1982','#1021, yk-block','Mysore','India',567890,08219876543,NULL,'jessica@mecus.co.us');

Insert into Account Values(2000,10002,'C','12-1月-2009',Null,40000,'C');
Insert into Account Values(2001,10001,'S','12-1月-2009',Null,42936,'F');
Insert into Account Values(2002,10003,'S','13-2月-2009',Null,10000,'F');
Insert into Account Values(2003,10002,'F','13-3月-2009','13-3月-2013',500000,'F');
Insert into Account Values(2004,10001,'S','12-5月-2009',Null,53000,'F');

Insert into Transaction Values(7000000,2000,'12-7月-2005',5000,'D','From Tom');
Insert into Transaction Values(7000001,2001,'12-7月-2005',500,'D','Self');
Insert into Transaction Values(7000002,2001,'13-7月-2005',1500,'D','Self');
Insert into Transaction Values(7000003,2002,'13-7月-2005',1500,'W','From jack');
Insert into Transaction Values(7000004,2002,'13-7月-2005',3500,'D','From Tim');

commit;

关键