cmu15-445笔记一 Introduction

本节课主要介绍数据库系统的一些概念以及SQL语句。

Lec01 Introduction

database

• organized data
• core component of most computer applications

database management system

• software that allows applications to store and analyze information in a database
• Definition,creation,querying,update and administration of a database with some data model

Data models

• concepts for describing the data in database

Relation model

• Physical storage, store database in simple data structures(relations)

• Dbms for logits, access data through high-level language

• Primary keys uniquely identifies a single tuple.

Relational algebra

• konw the basic operations and its symbol about relational algebra

• defines the primitives for processing queries on a relational database

conclusion

1. Relational algebra defines the primitives for processing queries on a relational database.

Lec02 Modern SQL

RELATIONAL LANGUAGES

• Data Manipulation Language (DML)
• Data Definition Language (DDL)
• Data Control Language (DCL)

SQL语言是一种声明式语言，你只需要告诉计算机你需要什么样的数据，计算机会自动帮你找出来。

SQL based on bags not sets。bags可以允许重复，sets就是不重复集合。

各种SQL操作：

• Aggregates（min、max、count、distinct、sum）
• Group by
• Having
• String operation
• Data/time operation
• Output redirection
• Output control（order by、limit）
• Nested queris
• Window function

Performs a “sliding” calculation across a set of tuples that are related.

Like an aggregation but tuples are not grouped into a single output tuples.

比如

SELECT *, ROW_NUMBER() OVER () AS row_num FROM enrolled

SELECT cid, sid,
ROW_NUMBER() OVER (PARTITION BY cid)
FROM enrolled ORDER BY cid

The OVER keyword specifies how to group together tuples when computing the window function.

Use PARTITION BY to specify group.

• Common table expression

Think of it like a temp table just for one query。就像是查询时额外有一张临时表。

WITH cteName (col1, col2) AS ( SELECT 1, 2
)
SELECT col1 + col2 FROM cteName

CTE还能嵌套使用

WITH RECURSIVE cteSource (counter) AS ( 
  (SELECT 1)
	UNION ALL
	(SELECT counter + 1 FROM cteSource	
	WHERE counter < 10) 
)
SELECT * FROM cteSource

Conclusion：

1. SQL是一个标准，各家厂商有各自实现，不尽相同。SQL标准也在修改变动。
2. 明白SQL语言的操作。