Skip to content

hxgdzyuyi/tang_poetry

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

5 Commits
 
 
 
 
 
 

Repository files navigation

全唐诗数据库

去井盖讨论

爬取的全唐诗数据库

使用

  1. 新建数据库
mysql> create database tang_poetry;
mysql> exit;
  1. 导入数据
mysql -u root -p -h localhost tang_poetry < tang_poetry.sql

内容

有两张表,一张作者,一张古诗

%load_ext sql
%sql mysql+pymysql://root:12345678@127.0.0.1/tang_poetry
'Connected: root@tang_poetry'
%sql SHOW tables;
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
2 rows affected.
Tables_in_tang_poetry
poetries
poets
%sql DESCRIBE poetries
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
6 rows affected.
Field Type Null Key Default Extra
id int(11) NO PRI None auto_increment
poet_id int(11) YES None
content text YES None
title varchar(255) YES None
created_at datetime YES None
updated_at datetime YES None
%sql DESCRIBE poets
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
4 rows affected.
Field Type Null Key Default Extra
id int(11) NO PRI None auto_increment
name varchar(255) YES None
created_at datetime YES None
updated_at datetime YES None

例子

查看唐朝写诗歌最多的人

%%sql

SELECT
    poets.name,
    COUNT(poetries.id) AS poetries_count
FROM
    poetries
LEFT JOIN poets ON poets.id = poetries.poet_id
GROUP BY
    poets.id
ORDER BY
    poetries_count
DESC
LIMIT 10
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
10 rows affected.
name poetries_count
白居易 2643
杜甫 1158
李白 896
佚名 841
齐己 783
刘禹锡 703
元稹 593
李商隐 555
贯休 553
韦应物 551

全唐诗库的总数量

%%sql 

SELECT
    COUNT(*)
FROM
    poetries
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
1 rows affected.
COUNT(*)
43030
%%sql

SELECT
    poets.name,
    poetries.title,
    poetries.content
FROM
    poetries
LEFT JOIN poets ON poets.id = poetries.poet_id
WHERE
    poets.name = '杨玉环'
 * mysql+pymysql://root:***@127.0.0.1/tang_poetry
1 rows affected.
name title content
杨玉环 赠张云容舞 罗袖动香香不已,红蕖袅袅秋烟里。轻云岭上乍摇风,嫩柳池边初拂水。

About

全唐诗数据库

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages