第六周 例题解析

CREATE TABLE t_people(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
sex ENUM('男','女'),
birthday DATE

);
INSERT INTO t_people(pname,sex,birthday)
VALUES('张浩','男','1995-01-02'),
('张合','女','1988-05-01'),
('李恒','男','2017-12-23'),
('雷敏','女','2011-04-17'),
('刘军','男','1945-06-08'),
('小明','男','1999-08-22'),
('花花','女','2017-04-29');

SELECT *FROM t_people;

CREATE TABLE t_bike(
id INT PRIMARY KEY AUTO_INCREMENT,
bikeType VARCHAR(20),
price INT,
peopleId INT
);
ALTER TABLE t_bike ADD CONSTRAINT fk_2 FOREIGN KEY (peopleId)
REFERENCES t_people(id);

INSERT INTO t_bike(bikeType,price,peopleId)
VALUES('永久','600','1'),
('凤凰','300','1'),
('膜拜','200','2'),
('捷达','2000','3'),
('屌丝牌','10','3'),
('飞行牌','10000','4'),
('潜水牌','10000','5'),
('遁地牌','10000','6');
SELECT *FROM t_bike;

-- 列出出所有自行车，并显示该车的车主名字
SELECT b.*, p.pname FROM t_bike b JOIN t_people p ON b.peopleId =p.id;
SELECT b.*, p.pname FROM t_people p JOIN t_bike b ON b.peopleId =p.id;
-- 或者上面一行的代码可以写为
SELECT b.* ,p.pname FROM t_bike b,t_people WHERE b.peopleId=p.id ;
-- 查询张浩拥有的车子信息（下面2行是2种方法）
SELECT b.*FROM t_bike b,t_people p WHERE b.peopleId=p.id AND p.pname='张浩';-- 一种

SELECT b.*FROM t_bike b JOIN t_people p ON b.peopleId =p.id WHERE p.pname='张浩';-- 第二种

-- 删除小明

UPDATE t_bike SET peopleId =NULL WHERE peopleId=
(SELECT id FROM t_people WHERE pname='小明');

DELETE FROM t_people WHERE pname='小明';

-- 查询没有主人的自行车

SELECT * FROM t_bike WHERE peopleId IS NULL;

-- 统计每个人有几个自行车（这是一个外连接）(where 只能是内连接)
-- bikeNum FROM t_people和bike b JOIN t_people 有什么区别么
SELECT p.*, COUNT(b.id) bikeNum FROM t_people p LEFT JOIN t_bike b ON
p.id=b.peopleId GROUP BY p.id;

-- 列出所有自行车，以及该车的主人
-- IFNULL(p.pname,'没有主人')当p.pname不为空时返回p.pname的值
-- 如果p.pname为空，返回‘没有主人’
SELECT b.*,IFNULL(p.pname,'没有主人') FROM t_bike b LEFT JOIN t_people p
ON b.peopleId=b.id;
-- 统计拥有直行车的用户，有几辆自行车，并统计价格(没写对照同学的看看)
SELECT p.pname,COUNT(b.id) bikeNum ,SUM(b.price) bilePrice FROM
t_bike b,t_people p WHERE b.peopleId=p.id GROUP BY p.id;

 

----------------------------------------------------------------------------------------------------------------------------

-- 显示电影id=1中演员名单

SELECT a.NAME FROM actor a,casting c WHERE a.id=c.actorid AND c.movieid=1;
-- 显示电影'Alien'中演员名单

SELECT a.*FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND m.title='Alien';
-- 显示有'Harrison Ford'参演的电影名称

SELECT m.title FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND a.NAME='Harrison Ford';
-- 显示有'Harrison Ford'参演的电影名称，但'Harrison Ford'角色排名不是第一
SELECT m.title FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND a.NAME='Harrison Ford' AND c.movieord>1;
-- 显示1962年发行的电影，及该影片的主角(排名第一)
SELECT m.title ,a.NAME FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND m.yr=1962 AND c.movieord=1;

-- 显示'John Travolta'每年出演的电影数量

SELECT m.yr,COUNT(m.id) FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid

AND a.NAME= 'John Travolta' GROUP BY m.yr ;
-- 列出'Julie Andrews'参演的所有电影名称以及该片的主角

SELECT m.title,a.NAME FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND c.movieord=1 AND n.id IN
(SELECT FROM actor a1,casting c1 WHERE a1.id=c1.actorid AND a1.NAME='Julie Andrews');
-- 列出在10部电影中出任过主角的演员
SELECT a.NAME FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND c.movieord =1 GROUP BY a.NAME HAVING COUNT(m.id)=10;

-- 按演员人数的多少依次列出1978的电影以及演员数。
SELECT m.title,COUNT(a.id) num FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND m.yr=1978 GROUP BY m.title ORDER BY num DESC;
-- 列出所有和'Art Garfunkel'合作过的演员
SELECT a.NAME FROM movie m,actor a,casting c WHERE a.id=c.actorid AND m.id =c.movieid
AND m.id IN
(SELECT c.movieid FROM actor a,casting c WHERE a.id=c.actorid AND a.NAME='Art Garfunkel')
AND a.NAME !='Art Garfunkel';