create table `tb_article`( `id` int not null primary key auto_increment comment '主键', `author_id` int not null comment '作者id', `category_id` int not null comment '文章类别id', `views` int not null comment '阅读量', `comments` int not null comment '评论量', `title` varchar(200) not null comment '文章标题', `content` text not null comment '文章内容' ) comment '文章表'; insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,1,1,1,1); insert into tb_article(author_id,category_id, views, comments, title, content) values(2,2,2,2,2,2); insert into tb_article(author_id,category_id, views, comments, title, content) values(1,1,3,3,3,3,);
需求:
查询出 类别id为1 且 评论量大于1的情况下,阅读量最多的那篇文章的作者id。
SQL写法如下:
select id, author_id from tb_article where category_id = 1 and comments > 1 order by views desc limit 1;
create table `tb_novel`( `id` int not null primary key auto_increment comment '主键', `title` varchar(100) not null comment '小说名' ) comment '小说';
create table `tb_character`( `id` int not null primary key auto_increment comment '主键', `name` varchar(100) not null comment '人物名', `novel_id` int not null comment '归属于的小说id' ) comment '人物';
create table `tb_kongfu`( `id` int not null primary key auto_increment comment '主键', `kongfu_name` varchar(100) not null comment '功夫的名字', `novel_id` int not null comment '小说的id' )comment '功夫';
insert into tb_novel(title)values('天龙八部'); insert into tb_novel(title)values('射雕英雄传'); insert into tb_novel(title)values('神雕侠侣'); insert into tb_novel(title)values('倚天屠龙记');
insert into tb_character(name, novel_id) values('乔峰',1); insert into tb_character(name, novel_id) values('扫地僧',1); insert into tb_character(name, novel_id) values('洪七公',2); insert into tb_character(name, novel_id) values('郭靖',2); insert into tb_character(name, novel_id) values('金轮法王',3); insert into tb_character(name, novel_id) values('小龙女',3); insert into tb_character(name, novel_id) values('赵敏',4); insert into tb_character(name, novel_id) values('灭绝老尼',4);
insert into tb_kongfu(kongfu_name, novel_id) values('北冥神功', 1); insert into tb_kongfu(kongfu_name, novel_id) values('六脉神剑', 1); insert into tb_kongfu(kongfu_name, novel_id) values('落英神剑掌', 2); insert into tb_kongfu(kongfu_name, novel_id) values('北斗七星阵', 2); insert into tb_kongfu(kongfu_name, novel_id) values('黯然销魂掌', 3); insert into tb_kongfu(kongfu_name, novel_id) values('龙翔般若功', 3); insert into tb_kongfu(kongfu_name, novel_id) values('乾坤大挪移', 4); insert into tb_kongfu(kongfu_name, novel_id) values('九阴白骨爪', 4);
需求:
查询出属于同一部小说的人物名和功夫的名字。
SQL写法如下:
select c.name,f.kongfu_name from tb_character c left join tb_kongfu f on c.novel_id = f.novel_id;