全唐诗数据库
爬取的全唐诗数据库
使用
- 新建数据库
mysql> create database tang_poetry;
mysql> exit;
- 导入数据
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'
* mysql+pymysql://root:***@127.0.0.1/tang_poetry
2 rows affected.
| Tables_in_tang_poetry |
| poetries |
| poets |
* 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 |
|
* 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.
%%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 |
| 杨玉环 |
赠张云容舞 |
罗袖动香香不已，红蕖袅袅秋烟里。轻云岭上乍摇风，嫩柳池边初拂水。 |
