catalogue

I. multiple data structures in oracle Database

2, Table structure

3, Create syntax

3.1 basic operation

3.1. 1 syntax:

3.1. 2 naming rules

3.1. 3. Data types supported by Oracle:

3.1.4 default: set the default value

3.1. 5 constraints

3.2. Subquery

1, Multiple data structures in oracle Database

1. The table structure stores data

2. Bytes of data in one or more tables of the view

3.sequence is mainly used to generate primary key values

4. Improve retrieval performance

We need to learn to create data structures

2, Table structure

1. Table structure can be created at will

2. The table structure does not need to apply for space in advance

3. It can be modified online.

3, Create syntax

There are two ways to create tables: basic operations and subqueries

3.1 basic operation

3.1. 1 syntax:

create table [schema.]tb_name(
            col_name datatype [default value] [colum_constraints],
            ...,
            [table_constraint],
            ...
        );

explain:

1.create table keyword, fixed writing method,

schema, which represents the user name in the oracle database

2.tb_name stands for table name, which can be customized: but the naming rules need to be followed (see 3.1.2 naming rules for details):

3. Generally, explicit rules are also required for listing (see 3.1.2 naming rules for details)

4.dataType, the data type to which the column belongs. See 3.1.3 data types supported by Oracle for details

3.1. 2 naming rules

1. Start with a letter

2. The length is 1-30

3. Only upper and lower case English, numbers and $#

4. The object name under the same user cannot be duplicate

5. Keywords cannot be used as table names (e.g. select group, etc.)

3.1. 3. Data types supported by Oracle:

explain:

1.char，varchar2，varchar

Usage: char(size),varchar2(size) varchar(size)

size is used to indicate the maximum number of character values that can be saved.

difference:

1.char: fixed length character

That is, once the number of characters in () is determined, when saving data, no matter how many characters you save, the space occupied is the number of characters in () fixed.

For example, char(2): saving a or ab takes up 2 character spaces

Varchar, VARCHAR2: variable length characters

That is, when saving data, the number of characters will be determined first, and then the corresponding space will be allocated for saving.

Such as varchar(2)

Saving a takes up 1 character space

Saving ab takes up two 2 character spaces

In oracle database, specify varchar2 as the preferred variable length string

2.number(p,s):

p determines the number of significant digits of a number

s determines the number of decimal places of the number

What are the maximum and minimum values of number(4,2)?

-99.99~99.99

3.date: date type

System default date type: 'DD-MON-YY'

When operating character type and date type data, be sure to put them in the middle of ''

3.1.4 default: set the default value

1. Function: set the default inserted value when inserting data into the table if the value of this column is not specified.

2. The default value can be a legal literal value (assigned according to the data type of the defined column), an expression, or a legal sql function such as sysdate and user.

for example

create table test(
start_date date default sysdate);

3. The default value cannot use columns of other tables or nonexistent columns / pseudo columns

3.1. 5 constraints

definition:

The so-called constraint is to enforce some rules that the data columns in the table must follow. Moreover, if there are dependency constraints in the table, some unreasonable deletion operations can be prevented.

Classification:

Table level constraint: a constraint defined at the table level (that is, a constraint defined only after the complete definition of the column is completed)

column dataType ,

unique(column)

Column level constraints: constraints directly following the column integrity definition

column dataType unique

Type:

Created on:

1. Define constraints while creating tables

2. After the table is created, modify the table structure (described later)

Create syntax:

Column level:

column [CONSTRAINT constraint_name] constraint_type, Column name constraint takes a constraint name constraint type

Table level:

column,... (end of column full definition)

[CONSTRAINT constraint_name] constraint_type (column, ...),....

Details:

1.not Null:

Value is not allowed to be null, null value input is blocked

note: can only be column level constraints

For example:

create table test( id number constraint test_nn_id not null);
create table test( id number not null);

2.unique:

The unique value constraint requires that the value must be unique and cannot be repeated. Can be empty

explain:

1. Single column uniqueness or combined column uniqueness can be set

2. If unique constrains a single column, this column can be null

3. It can be column level or table level constraint

4. For the unique column, oracle will automatically create a unique value index.

For example:

create table test(id number constraint test_un_id unique);

create table test(
id number,
constraint test_un_id unique(id)
);

create table test(id number unique);

create table test(
id number,
name varchar2(10),
constraint test_un_id_name unique(id,name)
);

create table test(
id number,
name varchar2(10),
unique(id,name)
);

3.Primary key: primary key

explain:

1. The primary key is used to set a unique identifier for each row of data in the table. There can only be one primary key.

2. The primary key can be a single column or a combined column.

3. It is mandatory to be non empty and unique. If it is composed of multiple columns, the combination is unique and each part of the column cannot be null.

4. It can be table level or column level.

5. Automatically create a unique value index.

For example:

 create table test(id number constraint test_pk_id primary key);

create table test(
id number,
constraint test_pk_id primary key(id)
);

create table test(id number primary key);

create table test(
id number,
name varchar2(10),
constraint test_pk_id_name primary key(id,name)
);

create table test(
id number,
name varchar2(10),
primary key(id,name)
); 

4.foreign key: foreign key

Generally, when designing the relationship between tables, in order to reduce data redundancy, the general operation is to set a column (combined column) in one table. The value of this column (combined column) can uniquely determine the row of data associated with the current table in another table. Then this column is called a foreign key.

explain:

1. It can be single column or combined column

2. Reference the primary key column or unique column in the current table or other tables (as long as the table that wants to establish a relationship with the current table)

3. It can be table level / column level

4. The value must be the value of the referenced column or null

5. When there are foreign key constraints, if you want to delete a piece of data in the parent table (referenced table), you must ensure that there is no data associated with this data in the child table (referenced table).

6.ON DELETE CASCADE, indicating that the data in the child table can be deleted cascade when deleting the data in the parent table

For example:

 create table emp(id number primary key);---->Parent table

1:m/m:1 (one to many, many to one)

create table test(
id number constraint test_fk_emp_id references emp(id)
);

1: 1 (one to one)

create table test(
id number references emp(id) unique
);

create table test(
id number,
constraint test_fk_emp_id foreign key(id) references emp(id)
);

create table test(id number references emp(id));

create table emp(
id number,
name varchar2(10),
primary key(id,name)
);

create table test(
id number,
name varchar2(10),
constraint test_fk_emp_id_name foreign key(id,name) 
references emp(id,name)
);

create table test(
id number,
name varchar2(10),
foreign key(id,name) references emp(id,name) on delete cascade 
);

5.check :

Define the rules that each row must follow

explain:

1. It can be table level / column level constraint

For example:

create table test(
gender varchar2(2) constraint test_check_gender check(gender in ('F','M')),
age number check (age>=15 and age<=20)
);

create table test(
gender varchar2(2),
constraint test_check_gender check(gender in ('F','M'))
);

create table test(
gender varchar2(2),
check(gender in ('F','M'))
);

3.2. Subquery

Generally, sub queries are used to create tables. Some data in another table should be stored in a new table. (it is equivalent to directly defining the information printed on the console into a new table.)

Syntax:

create table tb_name[(column,...)]
    as
 select ...

explain:

1. When creating a table with a subquery, only the not Null constraint will be copied.

2. When creating a table, you can specify the column name or not, but you must not specify the data type of the column

3. The number of columns in the created table should be consistent with that in the sub query table.