模拟银行sql语句学习

来源：互联网发布：网络教育好毕业吗编辑：程序博客网时间：2024/04/28 19:41

--创建表空间

create tablespace bankspace datafile 'E:\bank\bank.dbf' size 10m autoextend on;

--创建用户bankuser

create user bankuser identified by bankuser default tablespace bankspace;

drop table tradeinfo;

drop table userInfo;

drop table cardInfo;

drop table Deposit;

--为用户授权

grant connect,resource to bankuser;

--创建用户信息表

create table userInfo --用户信息表

(

customerID number not null,

customerName varchar2(8),

PID varchar2(18) not null,

telephone varchar2(20) not null,

address varchar(50)

);

--创建银行卡信息表

create table cardinfo(

cardid char(19) NOT NULL,

curid varchar(10)NOT NULL,--币种

savingid number NOT NULL,

openDate date NOT NULL,

openMoney number NOT NULL,

balance number NOT NULL,

pass char(6)NOT NULL,

isreportloss number(1)NOT NULL,--判断卡是否挂失

customerID number NOT NULL

);

select * from userInfo;

--创建交易信息表

create table tradeInfo

(

tradeDate DATE NOT NULL,

tradeType Char(4) NOT NULL,

cardID CHAR(19) NOT NULL,

tradeMoney NUMBER NOT NULL,

remark LONG

);

select * from tradeInfo;

--创建存款类型表

create table deposit (

savingID NUMBER NOT NULL,

savingName varchar(20)NOT NULL,

descrip varchar(50)

);

/*为deposit表添加约束*/

alter table deposit add constraint pa_savingID

primary key(savingid);

/*为userInfo添加约束*/

--主键

alter table userinfo add constraint pk_customerID

primary key(customerID);

--check约束，身份证号长度

alter table userinfo add constraint ck_pid

check(length(pid)=18 or length(pid)=15);

--unique唯一约束，身份证号唯一

alter table userinfo add constraint uq_pid unique(pid);

--check约束，电话号码

alter table userinfo add constraint ck_telephone

check(regexp_like(telephone,'(^\d{3,4}-\d{7,8}$)|(^\d{11}$)'));

--查看创建的约束

select * from user_constraints where table_name='DEPOSIT';

select * from user_constraints where table_name='USERINFO';

/*为cardInfo添加约束*/

--主键

alter table cardinfo add constraint pk_cardid

primary key(cardid);

--check约束，卡号

alter table cardinfo add constraint ck_cardID

check(regexp_like(cardid,'1010 3576 \d{4} \d{4}'));

--修改表中字段

alter table cardInfo modify (curid varchar(10) default 'RMB');

alter table cardInfo modify (opendate varchar(10) default sysdate);

--预存金额大于等于1

alter table cardinfo add constraint ck_openmoney

check(openmoney>=1);

--check 约束，预存金额大于等于1

alter table cardinfo add constraint ck_balance

check(balance>=1);

--密码检查约束，密码必须是六位数字

alter table cardinfo add constraint ck_pass

check(regexp_like(pass,'^[0-9]{6}$'));

--密码默认6个8

alter table cardinfo modify (pass char(6) default '888888');

--是否挂失默认值0

alter table cardinfo modify (isreportloss number(1) default 0);

--外键约束

alter table cardinfo add constraint fk_customerid

foreign key(customerid)references userInfo(customerid);

--外键约束

alter table cardinfo add constraint fk_savingid

foreign key(savingid)references deposit(savingid);

/*为tradeinfo 表添加约束*/

--交易类型(存入、支取)

alter table tradeinfo add constraint ck_tradetype

check(tradetype in ('存入','支取'));

--外键

alter table tradeinfo add constraint fk_cardID

foreign key(cardid)references cardInfo(cardId);

--交易金额大于0

alter table tradeinfo add constraint ck_tradeMoney

check(tradeMoney>0);

--交易时间

alter table tradeinfo modify (tradeDate date default sysdate);

/***************************************************************************************************/

/* 插入测试数据 */

/***************************************************************************************************/

==========================

测试数据

==========================

--存款类型

INSERT INTO deposit (savingID,savingName,descrip) VALUES (1,'活期','按存款日结算利息');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (2,'定期一年','存款期是1年');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (3,'定期二年','存款期是2年');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (4,'定期三年','存款期是3年');

INSERT INTO deposit (savingID,savingName) VALUES (5,'定活两便');

INSERT INTO deposit (savingID,savingName) VALUES (6,'通知');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (7,'零存整取一年','存款期是1年');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (8,'零存整取二年','存款期是2年');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (9,'零存整取三年','存款期是3年');

INSERT INTO deposit (savingID,savingName,descrip) VALUES (10,'存本取息五年','按月支取利息');

SELECT * FROM DEPOSIT;

SELECT * FROM userinfo;

SELECT * FROM cardinfo;

SELECT * FROM tradeinfo;

INSERT INTO userInfo(customerID,customerName,PID,telephone,address )

VALUES(1,'张三','123456789012345','010-67898978','北京海淀');

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

VALUES('1010 3576 1234 5678',1,1000,1000,1);

INSERT INTO userInfo(customerID,customerName,PID,telephone)

VALUES(2,'李四','321245678912345678','0478-44443333');

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

VALUES('1010 3576 1212 1134',2,1,1,2);

INSERT INTO userInfo(customerID,customerName,PID,telephone)

VALUES(3,'王五','567891234532124670','010-44443333');

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

VALUES('1010 3576 1212 1130',2,1601,1601,3);

INSERT INTO userInfo(customerID,customerName,PID,telephone)

VALUES(4,'丁六','567891321242345618','0752-43345543');

INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)

VALUES('1010 3576 1212 1004',2,1,1,4);

张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。

说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，

同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）