全唐诗数据库

爬取的全唐诗数据库

使用

1. 新建数据库

mysql> create database tang_poetry;
mysql> exit;

2. 导入数据

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
杨玉环	赠张云容舞	罗袖动香香不已，红蕖袅袅秋烟里。轻云岭上乍摇风，嫩柳池边初拂水。