WEEK5 Databases

summary

Data Manipulation Language - DML

SELECT attribute_1, attribute_2, …
FROM table
WHERE condition_1 AND|OR conditions_2
ORDER BY attribute ASC|DESC
LIMIT number_of_rows;

Commenting

– inline comment
/* multiline
comment
*/

Data Definition Language - DDL

• INSERT records 插入
  INSERT INTO table (attribute_1, attribute_2, attribute_3, …)
  VALUES (value_1, value_2, value_3, …);

• UPDATE records 更新
  UPDATE table
  SET attribute_1 = value_1, attribute_2 = value_2, …)
  WHERE condition;

for example：
UPDATE customers
SET postcode = 6289
WHERE id = 5;
– 意思就是 给customers表格中，id是5的这个人，更新一下postcode（原来是别的，更新成了6289）

• DELETE records 删除
  DELETE FROM table
  WHERE condition;

Joins
JOIN TYPE:
JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

SELECT table.attribute_1, table.attribute_2, …
FROM table_1
JOIN TYPE table_2
ON table_1.attribute = table_2.attribute;

Aliases 别名 :由于一次又一次地键入表名可能会很累人，因此我们可以通过在表名后面指定别名来为表命名（理解为昵称）。

SELECT alias.attribute_1, alias.attribute_2, …
FROM table_1 alias_1
JOIN TYPE table_2 alias_2
ON alias_1.attribute = alias_2.attribute;

SELECT *
FROM students s
JOIN marks m
ON s.student_id = m.id;

Python和SQL
过程：
import sqlite3

conn = sqlite3.connect(database)
c = conn.cursor()

c.execute(sql_query)

result = c.fetchall()

for row in result:
print(row)

Using Pandas
过程：
import sqlite3
import pandas as pd

conn = sqlite3.connect(database)

result = pd.read_sql(sql_query, conn)

print(result.head())

import sqlite3
conn = sqlite3.connect('shopping.db')

import pandas as pd
result = pd.read_sql('''SELECT item
                        FROM products p
                        JOIN transactions t
                        ON t.barcode = p.barcode
                        WHERE quantity >=4 AND pricing = 'each'
                        ORDER BY item''', conn)

print(result.head())