sqlite3 Win shell
1. 下载 sqlite地址 :https://sqlite.org/download.html
2. 下载 sqlite-tools-win32-*.zip (或x64)和 sqlite-dll-win32-*.zip 压缩文件,解压后放入同一文件夹,如c:\sqlite3 。
3. 添加 C:\sqlite 到 PATH 环境变量,最后在命令提示符下,使用 sqlite3 命令。
sqlite3 python module (pip3 install sqlite3)
postgreSQL
下载 postgreSQL
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
windows 打开 pgAdmin 4
postgresql in python module
以通过anaconda安装pip,确保pip已升级最高版本
cmd(administrator)
18.1(2018.11)
通过pip3 安装psycopy2 module
以便 jupyter 或者 python3 中 使用
sqlite3
connect to database (python)
is aconnection instance,maintains the connection to the database.
While we use the Connection class to represent the database we’re working with, we use the Cursor class to:
1. Run a query against the database
2. Parse the results from the database
3. Convert the results to native Python objects
4. Store the results within the Cursor instance as a local variable
After running a query and converting the results to a list of tuples, the Cursor instance stores the list as a local variable.
creat a cursor instance
shortcut for runing a query
fetch a specific number of results
close database connection
practice again
GROUP BY & DISTINCT
The GROUP BY SQL statement allows us to compute summary statistics by “group,” or unique value. When we use this statement, SQL creates a group for each unique value in a column or set of columns (the same values we get when we use the DISTINCT statement), and then does the calculations for them.
When we want to filter on a column generated by a GROUP BY query, we can use the HAVING statement.
HAVING
ROUND
CAST
Subqueries run first
Subquery in SELECT
IN
Integrating a subquery with the outer query
领取专属 10元无门槛券
私享最新 技术干货