61-Day5.md

61-Day5

1 Data System intro

• Operations of database: Read / Learn / Modify

• Goals of data systems: store current data, optimize historical data, run batch workloads

• ER: relational model

2 SQL

2.1 Basic

• SQL: Structured Query Language
• Data Types in SQL:
  • Characters: CHAR, VARCHAR
  • Numbers: INT, BIGINT,...
  • Others: MONEY, DATETIME
• Table Schemas: name, attribute, type
• Key: unique, underlined, minimal subset of attribute to identify an element.
• SQL Query: SELECT FROM WHERE
• SQL commands are case insensitive, while values are not. Use ‘ ’ for constants but not “ ”.
• LIKE: string matching %: any sequences; _: single character
• DISTINCT: deduplicate (SELECT DISTINCT)
• ORDER BY: Sorting the results.
• Foreign Key constraints: declared by FOREIGN KEY

2.2 Advanced

• JOIN: combine two tables. INNER JOIN: 取两表的交集. OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
• UNION: union with deduplication; UNION ALL: no deduplication
• Nested queries: 嵌套查询
• Aggregation: functions include SUM, COUNT, MIN, MAX, MIN, AVG; COUNT applies to duplicate, unless DISTINCT is added.
• GROUP BY: classified by deduplicated attributes, after which HAVING can be applied to select certain attributes.