北邮数据库课程讲义chapter4.pptVIP

Built-in Data Types in SQL date: Dates, containing a (4 digit) year, month and date Example: date ‘2005-7-27’ time: Time of day, in hours, minutes and seconds. Example: time ‘09:00:30’ time ‘09:00:30.75’ timestamp: date plus time of day Example: timestamp ‘2005-7-27 09:00:30.75’ interval: period of time Example: interval ‘1’ day Subtracting a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values Can extract values of individual fields from date/time/timestamp Example: extract (year from r.starttime) Can cast string types to date/time/timestamp Example: cast string-valued-expression as date Example: cast string-valued-expression as time User-Defined Types create type construct in SQL creates user-defined type create type Dollars as numeric (12,2) final create domain construct in SQL-92 creates user-defined domain types create domain person_name char(20) not null Types and domains are similar. Domains can have constraints, such as not null, specified on them. Not Null Constraint Declare branch_name for branch is not null branch_name char(15) not null Declare the domain Dollars to be not null create domain Dollars numeric(12,2) not null The Unique Constraint unique ( A1, A2, …, Am) The unique specification states that the attributes A1, A2, … Amform a candidate key. Candidate keys are permitted to be null (in contrast to primary keys). The check clause check (P ), where P is a predicate The check clause (Cont.) The check clause in SQL-92 permits domains to be restricted: Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value. create domain hourly_wage numeric(5,2) constraint value_test check(value = 4.00) The domain has a constraint that ensures that the hourly_wage is greater than 4.00 The clause constraint value_test is optional; useful to indicate which c