SQL Data DefinitionSQL数据定义.ppt

* SQL data definition using Oracle * SQL Data Definition using Oracle SQL data definition using Oracle * DDL SQL = DDL + DML Oracle has its own dialect of SQL DDL (Data definition language) Part of SQL Some DDL statements create table … alter table … drop table … SQL data definition using Oracle * Naming conventions Naming conventions for table names and attributes names Illegal Spaces “ “ Hyphens ‘-’ Legal letters [a-z A-Z, not ???] + digits _, #, $ first character must be a letter no reserved words in SQL [no attribute called by‘ or ‘table’] SQL data definition using Oracle * Data types Slightly different from standard SQL Strings Numbers Dates Other no Boolean data type SQL data definition using Oracle * Data types, strings varchar2 (max_length) varying length, like names, etc. Varchar2(10) Max length 10 bytes Varchar2(10 char) Max length 10 chars Normally 1 char ~ 1 byte, but it depends on the character table (like UNICODE) char (fixed_length) fixed length, like cpr, phone, etc Length in bytes or chars, like varchar2 SQL data definition using Oracle * Data types, numbers Numbers number (digits, decimals) fixed number of digits and decimals, like price, salary, etc. (with number(4,2) - the max number is 99.99) number (digits) integer number floating point (high precision) SQL data definition using Oracle * Data types, dates date store date and time! no special time data type Oracle SQL has special functions to do arithmetic on dates more on that later. advice dont make an attribute called age, as it changes frequently. make an attribute called birthday. SQL data definition using Oracle * Data types, other CLOB character large object texts up to 4 gigabytes BLOB binary large object up to 4 gigabytes, pictures, sounds, etc. NCLOB like CLOB, but using 2 bytes pr. character BFILE binary file reference to a binary file outside the DBMS SQL data definition using Oracle * Constraints, types Types of constraints integrity constraints primary key foreign key valu