数据库操作实例
数据库操作实例
1 DROP TABLE msp; 2 DROP TABLE party; 3 4 CREATE TABLE party (partyCode VARCHAR(10) NOT NULL 5 ,partyName VARCHAR(50) 6 ,leader VARCHAR(50) 7 ,PRIMARY KEY (partyCode) 8 )DEFAULT CHARSET=utf8; 9 10 CREATE TABLE msp (mspName VARCHAR(50) NOT NULL 11 ,party VARCHAR(10) 12 ,constituency VARCHAR(50) 13 ,PRIMARY KEY (mspName) 14 ,FOREIGN KEY (party) REFERENCES party(partyCode) 15 )DEFAULT CHARSET=utf8; 16 17 CREATE INDEX msp_party ON msp(party); 18 19 INSERT INTO party VALUES ('Com','Communist',NULL); 20 INSERT INTO party VALUES ('Con','Conservative','McLetchie MSP, David'); 21 INSERT INTO party VALUES ('Green','Green',NULL); 22 INSERT INTO party VALUES ('Lab','Labour','Dewar MSP, Rt Hon Donald'); 23 INSERT INTO party VALUES ('LD','Liberal Democrat','Wallace QC MSP, Mr Jim'); 24 INSERT INTO party VALUES ('NLP','Natural Law Party',NULL); 25 INSERT INTO party VALUES ('SNP','Scottish National Party','Salmond MSP, Mr Alex'); 26 INSERT INTO party VALUES ('SSP','Scottish Socialist Party',NULL); 27 INSERT INTO party VALUES ('SWP','Socialist Workers Party',NULL); 28 29 SELECT * FROM party; 30 SELECT * FROM msp; 31 32 -- 找出没有政党的议员 33 SELECT mspName FROM msp WHERE party IS NULL; 34 35 -- 列出所有的政党和领导者 36 SELECT partyName,leader FROM party; 37 38 -- 列出所有有领导者的政党 39 SELECT partyName FROM party WHERE leader IS NOT NULL; 40 41 -- 列出至少有一个议员的政党 42 SELECT p.partyName,COUNT(m.mspName) FROM party p,msp m WHERE p.partyCode=m.party GROUP BY p.partyName HAVING COUNT(m.mspName)>0; 43 44 -- 列出所有议员名字和他们所属政党 45 SELECT p.partyName,m.mspName FROM party p,msp m WHERE p.partyCode=m.party GROUP BY m.mspName; 46 47 -- 列出所有政党和每个政党议员人数 48 SELECT COUNT(m.mspName),p.partyName FROM party p,msp m WHERE p.partyCode=m.party GROUP BY p.partyName;
以上是数据库操作的实例，希望能以此加深记忆。
