ch3SQL.ppt

第三章：SQL Chapter Data Definition 数据定义 Basic Query Structure 基本查询结构 Set Operations 集合操作 Aggregate Functions 聚集函数 Null Values 空值 Nested Subqueries 嵌套子查询 Derived Relations 派生关系 Views 视图 Modification of the Database 数据库更新 Joined Relations 连接关系 3.1 History IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory起源于IBM实验室 Renamed Structured Query Language (SQL) ANSI and ISO standard SQL:版本 SQL-86 SQL-89 SQL-92 SQL:1999 (language name became Y2K compliant!) SQL:2003 Schema Used in Examples 3.2 Data Definition Language (DDL) The schema for each relation.关系模式 The domain of values associated with each attribute.域 Integrity constraints 完整性 The set of indices to be maintained for each relations. 索引 Security and authorization information for each relation. 授权 The physical storage structure of each relation on disk. 物理存储 3.2.1 Domain Types in SQL 基本域类型 char(n). Fixed length character string, with user-specified length n.定长 varchar(n). Variable length character strings, with user-specified maximum length n.可变长 int. Integer (a finite subset of the integers that is machine-dependent). smallint. Small integer (a machine-dependent subset of the integer domain type). numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point. 定点数 3.2.1 Domain Types in SQL 基本域类型 real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.浮点数与双精度浮点数 float(n). Floating point number, with user-specified precision of at least n digits.精度为N的浮点数 Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute空 create domain construct in SQL-92 creates user-defined domain types create domain person-name char(20) not null Date/Time Types in SQL (Cont.) date. Dates, containing a (4 digit) year, month and date E.g. date ‘2001-7-27’ time. Time of day, in hours, minutes and seconds.