cardmanagement数据库创建及数据输入语句- 数据集.docx

CREATEDATABASEcardmanagement;

USEcardmanagement;

CREATETABLEcard(

CIDchar(6)PRIMARYKEY,

passwordvarchar(6)NOTNULL,

balancedecimal(10,2)NOTNULLCHECK(balance=0),

statechar(1)NOTNULLCHECK(statein(0,1,2)));

CREATETABLEstudent(

SIDchar(12),

CIDchar(6),

snamevarchar(20),

genderchar(1)DEFAULTMCHECK(genderin(M,F)),

collegevarchar(20)NOTNULL,

CONSTRAINTSID_pkPRIMARYKEYNONCLUSTERED(SIDDESC),

FOREIGNKEY(CID)REFERENCEScard(CID)ONDELETECASCADE);

CREATETABLEbusiness(

BIDchar(4)PRIMARYKEY,

bnamevarchar(20)UNIQUE);

CREATETABLEsalebill(

numberintAUTO_INCREMENTPRIMARYKEY,

CIDchar(6)NOTNULL,

BIDchar(4)NOTNULL,

payamountdecimal(10,2)NOTNULL,

saledatedateNOTNULL,

CONSTRAINT`salebill_CIDfk`FOREIGNKEY(CID)REFERENCEScard(CID)ONDELETECASCADE,

FOREIGNKEY(BID)REFERENCESbusiness(BID)ONDELETECASCADE);

INSERTINTOcardVALUES(C00001,135,500,0);

INSERTINTOcardVALUES(C00002,459,300,1);

INSERTINTOcardVALUES(C00003,123456,32,2);

INSERTINTOcardVALUES(C00004,888,107.6,1);

INSERTINTOcardVALUES(C00005,159,75,0);

INSERTINTOcardVALUES(C00006,123321,800,0);

INSERTINTOcardVALUES(C00007,666666,12,2);

INSERTINTOcardVALUES(C00008,1111,209,0);

INSERTINTOcardVALUES(C00009,123,58.2,0);

INSERTINTOcardVALUES(C00010,12_456,400,0);

INSERTINTOcardVALUES(C00011,12%12,500,0);

INSERTINTOstudentVALUES(201901010001,C00001,张伟,M,管理学院);

INSERTINTOstudentVALUES(201901010002,C00002,周萍,F,管理学院);

INSERTINTOstudentVALUES(201901010003,C00003,孙琦,M,管理学院);

INSERTINTOstudentVALUES(201901010004,C00004,李子欣,F,管理学院);

INSERTINTOstudentVALUES(20190201001,C00005,陈亮,M,机械学院);

INSERTINTOstudentVALUES(20190201002,C00006,赵榕,F,机械学院);

INSERTINTOstudentVALUES(20200301001,C00007,黄磊,M,经济学院);

INSERTINTOstudentVALUES(202003010002,C00008,刘杰,M,经济学院);

INSERTINTOstudentVALUES(202003010003,C00009,刘杰,M,计算机学院);

INSERTINTOstudentVALUES(202003010004,null,张萌,M,经济学