Oracle Learning Summary 3

1:DDL statements
Create a student table

CREATE TABLE Stu (
ID Number (6) is not NULL,
Name VARCHAR2 (20),
Age Number (3),
Gender char (1) Default ' m ',
Grade number (1),
Class VARCHAR2 (Constraint STU_CLASS_FK foreign key references class (ID),
Email VARCHAR2 (50)
);

CREATE TABLE Class (
ID Number (4),
Name VARCHAR2 (30),
Constraint CLASS_ID_PK primary key (ID)
);

Default ' m ' This field defaults to M, and if the INSERT statement does not insert a value into the field, the default is M

2: Five Types of constraints:
Not null a non-null constraint, which is a value that must be given when data is inserted into a table.
Unique uniqueness constraint, when inserting data, the value of the field cannot be repeated
Primary key PRIMARY key: Non-null constraint, uniqueness constraint, logic above has meaning.
Foreign key foreign Key: Refer to a field in another table (the field must be a primary key), and when inserting data, it must be within the data range of the referenced field.
If the referenced field data does not exist, it cannot be plugged in.
The table in which the referenced field is located cannot be deleted until the reference table has not been deleted.
Check

Note: Primary key concept: A field that uniquely represents a row of records

3: Table-level constraints and field-level constraints

Field-level constraints, which can only work on one field:
CREATE TABLE Stu (
ID number (6) Constraint Stu_id_uni Unique,--Give ID field uniqueness constraint
Name VARCHAR2 (20)
);

Table-level constraint, if it is a two-field combination constraint:
CREATE TABLE Stu (
ID Number (6),
Name VARCHAR2 (20),
Email VARCHAR2 (50),
Constraint Stu_uni Unique (name,email)-The combination of two fields, name and email, to constrain
);

4: Modify table structure alter
--Add a field to the table
ALTER TABLE Stu Add (addr varchar2 (20));
--Modify the fields in the table
ALTER TABLE Stu Modify (addr varchar2 (50));
--Delete a field from a table
ALTER TABLE Stu drop (addr);

5: Modify the constraints in the table constraint
ALTER TABLE STU drop constraint stu_class_fk; --Delete the foreign KEY constraint in the table
ALTER TABLE STU Add constraint STU_CLASS_FK foreign key (class) references class (ID);

6: Modify the name of the table
Rename Old_name to New_name;

7:oracle, Data dictionary table, all tables under current user are in dictionary
select * from dictionary;
Only two fields: table_name comments

--User table user_tables, all the tables under that user are in this table
--User View User_view
--User Index User_indexes
--User constraint User_constraint

8: Create index
Indexes can improve query efficiency, but they are less efficient when inserting data because inserting the index table while inserting the data
When you set a primary key or uniqueness constraint on a field, the index is created automatically.
Create index idx_stu_id on Stu (ID); --Create an index
Drop index idx_stu_id; --Delete Index

Disadvantages:
1: Not easily indexed, build a large number of indexes will occupy the table space.
2: Reduce the efficiency of modifying data and insert the index table when inserting data.

9: View Virtual table
Concept: is a virtual table, a SELECT statement,

CREATE VIEW V$_stu as select * from Stu; --Create a view
Drop View V$_stu; --Delete a view

Advantages:
1: Convenient query, simplify the query.
2: Protect the data in the table from being viewed and protect privacy.

Insufficient:
Maintenance costs increase, and if you modify the original table structure, then the data in the view should be modified accordingly.
Modify the data in the view, and actually modify the data in the original table.

10: Sequence
Create sequence seq1;
Insert into Stu values (Seq1.nextval, ', ');--Inserts an index, typically adds a value to the primary key

Create sequence seq2 start with 1 increment by 1; --Starting from 1, the step size is 1

Mysql:auto increment;

Oracle Learning Summary 3