mysql数据库教程外联_MySQL--外联语句练习

-- createholy, 1, 50

CREATE DATABASE wl;

DROP TABLE msp;

DROP TABLE party;

CREATE TABLE party (

partyCode VARCHAR(10) NOT NULL, -- 编号

partyName VARCHAR(50), -- 名称

leader VARCHAR(50), -- 领导者

PRIMARY KEY (partyCode)

)DEFAULT CHARSET=utf8;

CREATE TABLE msp (

mspName VARCHAR(50) NOT NULL -- 编号

party VARCHAR(10), -- 政党

constituency VARCHAR(50), -- 支持者

PRIMARY KEY (mspName),

FOREIGN KEY (party) REFERENCES party(partyCode),

)DEFAULT CHARSET=utf8;

SELECT * FROM party;

SELECT * FROM msp;

-- 找出没有政党的议员

SELECT * FROM msp WHERE party IS NULL

-- 列出所有的政党和领导者

SELECT partyName,leader FROM party

-- 列出所有有领导者的政党

SELECT * FROM party WHERE leader IS NOT NULL

-- 列出至少有一个议员的政党

SELECT p.* FROM party p,msp m WHERE p.partyCode=m.party GROUP BY p.partyCode

-- 列出所有议员名字和他们所属政党

SELECT m.mspName,p.partyName FROM msp m LEFT JOIN party p ON p.partyCode=m.party

-- 列出所有政党和每个政党议员人数

SELECT p.partyName,COUNT(m.mspName) FROM party p JOIN msp m ON p.partyCode=m.party GROUP BY p.partyCode