数据库操作实例
时间：2021-07-01 10:21:17
帮助过：2人阅读
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;
以上是数据库操作的实例，希望能以此加深记忆。
数据库操作实例
标签：
