数据库原理课件第三章.ppt

Chapter 3: SQL Chapter 3: SQL Data Definition Basic Query Structure Set Operations Aggregate Functions Null Values Nested Subqueries Complex Queries 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 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 Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features. Not all examples here may work on your particular system. 3.2 Data Definition Language 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. 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. Nchar(n), nVarchar(n) (for unicode ,2byte code,1-4000 chars) Text. Variable length character strings,1 to 2GB.=varchar(max) int. Integer (4B,a finite subset of the integers that is machine-dependent). smallint. Small integer (2B,a subset of the integer domain type). Bigint. Big integer (8B),tinyint(1B),bit(多个位占1B) numeric(p,d). Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point. Decimal(p,d). 1.Domain Types in SQL(cnt) 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. Datatime 8B:yyyy.mm.dd h:m:s,smalldatatime 4B Money 8B Xml (2GB)(不同表单可以存在xml类型的属性字段中) Binary(n) 1-8000B Image 2GB Timestamp 8B Sql_v