Lecture 18 – SQL I

Recording

• recording

Slides

• slides

Type

Lecture

基础操作关于SQL 一些query语句基础语句 WHERE OR ORDER BY LIMIT OFFSET GROUPBY DISTINCT LIKE CAST SQL 和 pandas 示例 SQL Join 总结

使用 sqlalchemy 包来实现在notebook中连接本地数据库（.db文件）

基础操作

加载sql模块：

%load_ext sql

连接本地数据库：

连接远程数据库：

注：在notebook中，%sql是值行魔法，仅有当前行为SQL命令；%%sql是cell 魔法，当前cell为sql命令

关于SQL

每一列都有三个属性：列名称，列类型，零个或多个类内容限制条件。

列类型

INT: Integers.

REAL: Real numbers.

TEXT: Strings of text.

BLOB: Arbitrary data, e.g. songs, video files, etc.

DATETIME: A date and time.

一些query语句

记住常见的关键词：


WHERE
LIMIT
ORDER BY
OFFSET
GROUP BY
HAVING

基础语句

WHERE

OR

ORDER BY

LIMIT

限制返回几行数据

OFFSET

从第几行开始拿LIMIT行数据

GROUPBY

对group by的过滤

💡

单独对行过滤，使用WHERE，对GROUP过滤使用的是HAVING；WHERE在HAVING之前

DISTINCT

LIKE

字符匹配，和正则表达式类似功能，但是要比正则表达式简单很多。可以用在where上来选择一些行

CAST

对某列的数据类型进行转换

SQL 和 pandas

使用 sqlalchemy 包来连接pandas 和 sql数据库，进而可以绘图等操作。或者，用pandas来操作数据库

示例


import sqlalchemy
# create a SQL Alchemy connection to the database
engine = sqlalchemy.create_engine("sqlite:///data/lec18_basic_examples.db")
connection = engine.connect()

pd.read_sql("""
SELECT type, MAX(cost)
FROM Dish
GROUP BY type;""", connection)

返回的是一个pd.DataFrame

根据已有的csv文件创建一个sql数据库：


from os.path import exists

imdb_file_exists = exists('./data/imdb.db')
if not imdb_file_exists:
    !(cd data; sqlite3 imdb.db ".mode tabs" ".import titles.tsv titles" ".import names.tsv names") 2> /dev/null

SQL Join

有点类似 pandas 的merge，有 cross join, inner join, left outer join, right outer join, full outer join,

总结

本节课讲了在notebook中写sql语句以及一些常用的sql命令：


SELECT [DISTINCT] 
    {* | expr [[AS] c_alias] 
    {,expr [[AS] c_alias] ...}}
FROM tableref {, tableref}
[[INNER | LEFT ] JOIN table_name
    ON qualification_list]
[WHERE search_condition]
[GROUP BY colname {,colname...}]
[HAVING search condition]
[ORDER BY column_list]
[LIMIT number]
[OFFSET number of rows];