Oracle CREATE TABLE methods and some common commands _oracle

1, primary key and foreign key
Primary key: A record in a relational database has several attributes, if one of the attribute groups (note is a group, can be one, can be multiple) can uniquely identify a record, then the attribute group is the primary key
FOREIGN key: A column in a relational database table or a combination of several columns whose value matches a column or columns in another table. and is the primary key of another table (that is, one or more columns of this table are the primary keys for another table, which is called a foreign key to another table)

Note 1: A table can have only one primary key, multiple foreign keys, and a unique index

Note 2:oracle database has 5 constraints: primary key, foreign key, non-null, unique, condition
Non-null: The value of this column cannot be null (NOT NULL)
Unique: The value of this column is unique in the table and cannot be duplicated, but can be null (NULL)
Conditions: You can set the value of a column within a range, such as a person's age can not be negative.

Note 3: Differences between primary keys and unique constraints
A table's primary key is a column with a unique identity in the table, cannot be a null value (NULL), and the table's unique constraint is that the value of the column exists only in the table and can be null (NULL)

2, the creation and deletion of the table
No constraint creation:

Copy Code code as follows:

CREATE TABLE Items (ItemNo number (2), ItemName varchar2 (20));

Delete:

Copy Code code as follows:

drop table Items;

A PRIMARY KEY constraint is created:

Copy Code code as follows:

CREATE TABLE Items (ItemNo number (2) constraint Pk_items primary key,itemname varchar2 () not NULL);
A FOREIGN KEY constraint was created:
[Code]
CREATE TABLE Business (Busino number (2) Constraint Pk_business primary key,
Businame VARCHAR2 (a) Not Null,itemno number (2), constraint fk_business
Foreign KEY (ItemNo) references Items (ITEMNO), starttime date);

NOTE: constraint: Defines the key words necessary for a constraint in a table
Primary key: PRIMARY KEY constraint keyword
Foreign key...references ... : Create a foreign key keyword for a table

3, an internal function of to_date:oracle, you can change the string into time

Copy Code code as follows:

Insert into Business (busino,businame,itemno,starttime)
VALUES (4, ' Supermarket ', 2,to_date (' 2008-08-08 ', ' yyyy-mm-dd '));

4. Create a table with "unique" and "conditional" constraints

Copy Code code as follows:

CREATE TABLE Computers (
Compno number (4) Constraint Pk_comp primary key,
Compmodel varchar2 () unique,
Buytime date,
Price number (7,2) constraint ch_price check (price>0 and price<=30000),
Owner VARCHAR2 (32));

Note: unique: Unique constraint keyword
Constraint...check: A keyword for a conditional constraint

5, create a new table business_copy, and copy the business table data
CREATE TABLE Business_copy as SELECT * from business;

Note: CREATE TABLE: Keywords for creating tables
As SELECT * FROM business: Copy all data in business to Business_copy (Do not copy table constraints)

6, the Backup table to import the data into the new table

Copy Code code as follows:

INSERT INTO Business (busino,businame,itemno,starttime) select * from Business_copy;

7, the commonly used field data types
Number (P,s): numeric type where the P minimum value is 1, the maximum is 38,s minimum is-84, the maximum is 124
Date: Dates type, used to record time
Char (size): fixed-length string type, know the length of the specified, can save a lot of space, such as gender, F said female, m represents male
varchar (size): variable long String type
Blob (Binary large object type): Used to store binary objects, such as photos, documents, etc.
Clob (character large object type): Large object data for storing bytes, such as resumes
Bfile (binary file): Storage of large objects, such as film films, etc.

8, modify the table structure
Add a table field

Copy Code code as follows:

ALTER TABLE items Add (Manager VARCHAR2 (6));

NOTE: Alter, which is used in the Oracle database to change database parameters, table structures, etc.
Add: Here is a keyword to add a column

To modify a table's field maximum value

Copy Code code as follows:

ALTER TABLE items modify (Manager varchar2 (8));

Delete a column of a table
[Code]
ALTER TABLE items drop column manager;