SQL练习1——名言SQL练习

建表语句如下：

DROP TABLE IF EXISTS `quote`;
CREATE TABLE `quote` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(1024) DEFAULT '0',
  `author` varchar(32) DEFAULT '0',
  `tags` varchar(128) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=199 DEFAULT CHARSET=utf8 COMMENT='名人名言';

插入的数据（共插入数据198条，这里仅提供部分插入数据的语句示例）：

INSERT INTO `quote` VALUES ('1', '“The world as we have created it is a process of our thinking. It cannot be changed without changing our thinking.”', 'Albert Einstein', 'change/deep-thoughts/thinking/world');
INSERT INTO `quote` VALUES ('2', '“It is our choices, Harry, that show what we truly are, far more than our abilities.”', 'J.K. Rowling', 'abilities/choices');
INSERT INTO `quote` VALUES ('3', '“There are only two ways to live your life. One is as though nothing is a miracle. The other is as though everything is a miracle.”', 'Albert Einstein', 'inspirational/life/live/miracle/miracles');
INSERT INTO `quote` VALUES ('4', '“The person, be it gentleman or lady, who has not pleasure in a good novel, must be intolerably stupid.”', 'Jane Austen', 'aliteracy/books/classic/humor');
INSERT INTO `quote` VALUES ('5', '“Imperfection is beauty, madness is genius and it\'s better to be absolutely ridiculous than absolutely boring.”', 'Marilyn Monroe', 'be-yourself/inspirational');
INSERT INTO `quote` VALUES ('6', '“Try not to become a man of success. Rather become a man of value.”', 'Albert Einstein', 'adulthood/success/value');
INSERT INTO `quote` VALUES ('7', '“It is better to be hated for what you are than to be loved for what you are not.”', 'André Gide', 'life/love');
INSERT INTO `quote` VALUES ('8', '“I have not failed. I\'ve just found 10,000 ways that won\'t work.”', 'Thomas A. Edison', 'edison/failure/inspirational/paraphrased');
INSERT INTO `quote` VALUES ('9', '“A woman is like a tea bag; you never know how strong it is until it\'s in hot water.”', 'Eleanor Roosevelt', 'misattributed-eleanor-roosevelt');
INSERT INTO `quote` VALUES ('10', '“A day without sunshine is like, you know, night.”', 'Steve Martin', 'humor/obvious/simile');

具体数据如下：

select * from quote;

求：
1、名人名言的总数量。

select count(*) from quote;

2、发表名人名言最多的前3位作者都是谁？

select author, count(*) total  from quote group by author order by total desc limit 3;

查询结果如下：

3、最热门的10个标签是什么？

select tmp.tag,count(tag) amount from (
select q.id ,q.tags,ht.help_topic_id,
substring_index( substring_index(q.tags,'/',ht.help_topic_id+1) ,'/',-1) tag
from quote q left join mysql.help_topic ht on ht.help_topic_id < length (q.tags) - length (replace(q.tags,'/',''))+1
) tmp group by tag order by amount desc limit 10;

查询结果如下：