Database Operation Instance

Database Operation Instance

1 DROP TABLEMSP;2 DROP TABLEParty ;3 4 CREATE TABLEParty (PartycodeVARCHAR(Ten) not NULL5, PartynameVARCHAR( -)6, leaderVARCHAR( -)7,PRIMARY KEY(Partycode)8)DEFAULTCHARSET=UTF8;9     Ten CREATE TABLEMSP (MspnameVARCHAR( -) not NULL One, partyVARCHAR(Ten) A, constituencyVARCHAR( -) -,PRIMARY KEY(Mspname) -,FOREIGN KEY(Party)REFERENCESParty (Partycode) the)DEFAULTCHARSET=UTF8; -  - CREATE INDEXMsp_party onMSP (Party); -  + INSERT  intoPartyVALUES('Com','Communist',NULL); - INSERT  intoPartyVALUES('Con','Conservative','McLetchie MSP, David'); + INSERT  intoPartyVALUES('Green','Green',NULL); A INSERT  intoPartyVALUES('Lab','Labour','Dewar MSP, Rt Hon Donald'); at INSERT  intoPartyVALUES('LD','Liberal Democrat','Wallace QC MSP, Mr Jim'); - INSERT  intoPartyVALUES('NLP','Natural Law Party',NULL); - INSERT  intoPartyVALUES('SNP','Scottish National Party','Salmond MSP, Mr Alex'); - INSERT  intoPartyVALUES('SSP','Scottish socialist Party',NULL); - INSERT  intoPartyVALUES('SWP','Socialist Workers Party',NULL); -  in SELECT *  fromParty ; - SELECT *  fromMSP; to  + --to find a member without a political party - SELECTMspname fromMspWHEREParty is NULL; the  * --list all the political parties and leaders $ SELECTPartyname,leader fromParty ;Panax Notoginseng  - --list all political parties with a leader the SELECTPartyname fromPartyWHERELeader is  not NULL; +  A --List of Parties with at least one member the SELECTP.partyname,COUNT(M.mspname) fromParty P,msp MWHEREP.partycode=M.partyGROUP  byP.partyname having COUNT(M.mspname)>0; +   - --list the names of all the members and the party they belong to $ SELECTP.partyname,m.mspname fromParty P,msp MWHEREP.partycode=M.partyGROUP  byM.mspname; $  - --list of all political parties and the number of members of each political party - SELECT COUNT(M.mspname), P.partyname fromParty P,msp MWHEREP.partycode=M.partyGROUP  byP.partyname;

The above is an example of database operations, hoping to deepen the memory.

Database Operation Instance