MySQL多表练习（一）

创建表

-- 名人
CREATE TABLE celebrity(
		sid int,
		sname varchar(255),
		sage int,
		ssex varchar(255)
)
-- 代表
CREATE TABLE works(
		wid int,
		wwork varchar(255),
		wdynasty varchar(255)
)
--  总结
CREATE TABLE summary(
		sid int,
		wid int,
		sassess varchar(255)
)

插入数据

--  插入代表数据
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (1, '将进酒', '唐代');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (2, '蜀道难', '唐代');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (3, '夜雨寄北', '唐代');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (4, '静夜思', '唐代');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (5, '望岳', '唐代');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (6, '钱塘湖春行', '南宋');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (7, '念奴娇赤壁怀古', '北宋');
INSERT INTO `works`(`wid`, `wwork`, `wdynasty`) VALUES (8, '水调歌头', '唐代');

--  插入名人数据
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (1, '李白', 34, '男');
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (2, '杜甫', 24, '男');
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (3, '白居易', 31, '男');
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (4, '李商隐', 40, '女');
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (5, '苏轼', 26, '男');
INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `ssex`) VALUES (6, '辛弃疾', 22, '男');

--  插入总结数据
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (1, 1, '天生我材必有用');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (1, 2, '危乎高哉！');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (6, 8, '明月几时有');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (3, 6, '乱花渐欲迷人眼');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (4, 3, '却话巴山夜雨时');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (5, 7, '大江东去');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (2, 5, '一览众山小');
INSERT INTO `summary`(`sid`, `wid`, `sassess`) VALUES (1, 4, '举头望明月');

1.查询名人表中性别是男的作者

select * from celebrity where ssex='男'

2.查询静夜思的作者

select celebrity.sname,works.wdynasty,summary.sassess from summary inner join celebrity on summary.sid=celebrity.sid inner join works on summary.wid = works.wid where works.wwork = '静夜思'

3.查询李白和杜甫的年纪

select sage from celebrity where  sname='李白' or sname ='杜甫'

4.查询名人表中男女的个数

select count(*) from celebrity where ssex='男'
select count(*) from celebrity where ssex='女'
select count(*),ssex from celebrity group by ssex having ssex='男' or ssex='女'

5.查询白居易写的作品名称和对于的名句

select celebrity.sname,works.wwork,summary.sassess from summary inner join celebrity on celebrity.sid = summary.sid inner join works on works.wid = summary.wid where celebrity.sname = '白居易'

6.查询年纪在25到30岁之间的个数

select count(*) from celebrity where sage>25 and sage<30

7.查询名人表中最后两条的数据信息

select * from celebrity  LIMIT 4,2;

8.查询李白的作品名称，名句和年纪

select celebrity.sage,works.wwork,summary.sassess from summary inner join celebrity on summary.sid = celebrity.sid inner join works on summary.wid = works.wid where celebrity.sname = '李白'

9.将作品为望岳的朝代修改为北宋

update works set wdynasty = '北宋' where wwork = '望岳'

10.新增名人表中的一个作者王维，年纪25，性别男

insert into celebrity values (7,'王维',29,'男')