Restrictions on SQL statements of ORACLE databases-basics, oraclesql

Restrictions on SQL statements of ORACLE databases-basics, oraclesql

/* Change the user password:
1. Enter cmd to open the dos window
2. sqlplus/nolog
3. conn/as sysdba
4. alter user system identified by orcl;
*/

/**
Data Integrity indicates that all data values stored in the database are in the correct state.
If an incorrect data value is stored in the database, the database is deemed to have lost data integrity.
*/

Create table tb_student (
NAME VARCHAR2 (18 ),
Sex CHAR (3 ),
Age INT,
Address varcharacter 2 (100 ),
Phone VARCHAR2 (30)
)

SELECT * FROM tb_student;

-- Incorrect status data
Insert into tb_clazz (sex, age, address, phone) VALUES ('female ', '19', 'guangzhou', '123 ');
Insert into tb_clazz (NAME, sex, age, address, phone) VALUES ('Alice ', 'shel', '19', 'guangzhou', '123 ');
Insert into tb_clazz (NAME, sex, age, address, phone) VALUES ('Alice ', 'female', '19', 'guangzhou ', '123 ');

/**
The constraint is the data validation rule enforced on the table.
Constraints are mainly used to ensure the integrity of the database.
Most databases support the following five types of integrity constraints:
UNIQUE Key of UNIQUE Key
CHECK (not null is NOT empty)
*/

/** Key points:
PRIMARY KEY
Role: The primary key is the only field in the table that determines a row of data. The primary key function is equivalent to a non-empty and unique one.
1. Only one primary key is allowed in a table.
Federated primary key. The primary key field can be a single field or a combination of multiple fields.
2. for modern database modeling, it is recommended that a table must have a primary key and the primary key should be irrelevant to the business data. We recommend that you use a natural number that increases automatically.

Important:
FOREIGN KEY
Format: foreign key (foreign key column name) REFERENCES main table (reference column)
Purpose: The foreign key is built on the relationship between two fields or two fields in a table to solve the data redundancy problem.
Classroom example: Associate the tb_clazz table with the tb_student table using the clazz_id field.
*/

/** Notes for Foreign keys:
1. The value of [clazz_id] in the [tb_student] foreign key column of the child (slave) table must be within the range of [id] in the reference column of the parent (master) Table [tb_clazz.
Or it is empty (you can also add a non-empty constraint to force null)
2. The foreign key [clazz_id] can only refer to the primary key or unique key of the primary table [tb_clazz], ensuring that the subtable records can be accurately located to the referenced records.
3. When the record quilt table [tb_student] of the primary table [tb_clazz] is referenced, the records of the primary table cannot be deleted.
Solution 1: Delete the associated sub-table data before deleting the master table data.
*/
SELECT * FROM tb_student;
ALTER

-- Delete the ID of the primary table first
Delete from tb_student WHERE clazz_id = 1;
-- Delete the ID of the slave table
Delete from tb_clazz where id = 1;

-- Solution 2: Modify the associated sub-table data before deleting the master table data.
UPDATE tb_student SET clazz_id = 1 WHERE CLAZZ_ID = 3;
Delete from tb_clazz where id = 3;

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;

-- Solution 3: first set the associated sub-table data to null, and then delete the primary table data.
UPDATE tb_student SET clazz_id = null where clazz_id = 2;
Delete from tb_clazz where id = 2;

4. You can add the following settings when creating a table:
-- CASCADE Deletion
(1) on delete cascade: When the row in the parent table is deleted, the row in the child table is also deleted.
-- Convert the value of Table A from Table B to A null value.
(2) on delete set null: converts a foreign key value to a NULL value. The foreign key is SET to NULL.

/* When creating a table, create a constraint directly behind the column, which is called a column-level constraint. The database adds a unique constraint to the constraint by default.
SYS_C007217 is used for management **/
Drop table tb_student;

SELECT * FROM tb_student;

Create table tb_student (
Id int primary key, -- primary key constraint
NAME VARCHAR2 (30) not null, -- non-NULL Constraint
Sex CHAR (3) DEFAULT 'male' CHECK (sex = 'male' OR sex = 'female '), -- CHECK Constraints
Age int check (age> 6 AND age <149), -- CHECK Constraints
Phone VARCHAR2 (11) UNIQUE -- UNIQUE constraint UNIQUE
);

-- Test non-empty Constraints
Insert into tb_student (sex, age, phone) VALUES ('male', 23, '123 ');

-- Test check Constraints
-- Insert the data with the same gender as the demon, and the mobile phone number as the duplicate. The age is 200.
Insert into tb_student (ID, name, sex, age, phone) VALUES (1, 'admin', 'shid', 23, '123 ');
Insert into tb_student (ID, name, sex, age, phone) VALUES (2, 'admin', 'male', 2000, '123 ');

-- Test unique constraints
Insert into tb_student (ID, name, sex, age, phone) VALUES (1, 'admin', 'male', '20', '123 ');
Insert into tb_student (ID, name, sex, age, phone) VALUES (2, 'admin', 'male', '20', '123 ');

-- Why is there a primary key constraint? Observe and insert duplicate data
-- Primary keys are used to ensure data integrity and uniqueness.
Insert into tb_student (name, sex, age, phone) VALUES ('admin', 'male', '20', '123 ');
Insert into tb_student (name, sex, age, phone) VALUES ('admin', 'male', '20', '123 ');

-- Why is a foreign key required?
Drop table tb_student;
Drop table tb_clazz;

SELECT * FROM tb_clazz;

Create table tb_student (
Id int primary key,
NAME VARCHAR2 (18) not null,
Sex CHAR (3) DEFAULT 'male' CHECK (sex = 'male' OR sex = 'female '),
Age int check (age> 15 AND age <60 ),
Address VARCHAR2 (30) UNIQUE,
Phone VARCHAR2 (30) UNIQUE,
-- Class name, class code, class teacher
Clazz_name VARCHAR2 (30 ),
CODE VARCHAR2 (30 ),
Bzr VARCHAR2 (30)
);

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;
-- Data redundancy occurs when data is inserted.
-- Insert data
Insert into tb_student (ID, NAME, SEX, AGE, ADDRESS, PHONE, CLAZZ_NAME, CODE, bzr)
VALUES (1, 'admin', 'male', 23, 'guangzhou ', '000000', 'j1707', 'java employment class', 'instructor Xie ');

-- Create a foreign key Association for two tables

-- Main table class table
Create table tb_clazz (
Id int primary key, -- if you want to set a foreign KEY, the PRIMARY table must have a PRIMARY KEY
CODE VARCHAR2 (18 ),
NAME VARCHAR2 (18 ),
Bzr VARCHAR2 (18)
);

Insert into tb_clazz (id, CODE, NAME, bzr) VALUES (1, 'j1709', 'java employment class', 'instructor Xie ');
Insert into tb_clazz (id, CODE, NAME, bzr) VALUES (2, 'j1710', 'java employment class', 'instructor li ');
Insert into tb_clazz (id, CODE, NAME, bzr) VALUES (3, 'j1711', 'java basic class', 'Mr Huang ');

-- Column-level constraints
Create table tb_student (
Id int primary key,
NAME VARCHAR2 (18) not null,
Sex CHAR (3) DEFAULT 'male' CHECK (sex = 'male' OR sex = 'female '),
Age int check (age> 15 AND age <60 ),
Address varcharacter 2 (100 ),
Phone VARCHAR2 (30) UNIQUE,
-- Class foreign key
Clazz_id INT,
-- The foreign key (foreign key name) references the primary table (primary key)
Foreign key (clazz_id) REFERENCES tb_clazz (ID)
)

Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (1, 'admin', 'male', 23, 'guangzhou ', '123', 1 );
Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (2, 'admin2', 'male', 23, 'guangzhou ', '123', 2 );
Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (3, 'admin3', 'male', 23, 'guangzhou ', '123', 3 );

-- Insert data
Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (1, 'admin', 'male', 23, 'guangzhou ', '123', 1 );
Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (2, 'Rose ', 'female', 21, 'shenzhen ', '123', 2 );

-- Test: 1. The value of the subtable [tb_student] foreign key column [clazz_id] must be within the value range of the reference column [id] of the parent (primary) Table [tb_clazz]
-- Error message: the parent keyword is not found, because no class with id 9 exists in tb_clazz.
Insert into tb_student (ID, NAME, sex, age, address, phone, clazz_id)
VALUES (3, 'Rose ', 'female', 21, 'shenzhen ', '123', 9 );

-- Test: 2. The foreign key [clazz_id] can only refer to the primary key or unique key of the primary table [tb_clazz,
-- Ensure that the sub-table records can be accurately located to the referenced records.
Create table tb_clazz1 (
Id int,
CODE VARCHAR2 (18 ),
NAME VARCHAR2 (18 ),
Bzr VARCHAR2 (18)
);

Insert into tb_clazz1 (id, CODE, NAME, bzr) VALUES (1, 'j1509', 'java employment class', 'instructor Xie ');
Insert into tb_clazz1 (id, CODE, NAME, bzr) VALUES (1, 'j1508', 'java employment class', 'instructor li ');

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

-- Test: 3. When the [tb_clazz] record table [tb_student] is referenced, the record of the master table cannot be deleted.
-- Error message: the sub-record has been found.
Delete from tb_clazz where id = 1;

-- Solution 2: Modify the associated sub-table data before deleting the master table data.
-- The CLAZZ_ID value modified by the sub-table must be within the ID value range of the master table.
UPDATE tb_student SET clazz_id = 3 where id = 1;
Delete from tb_clazz where id = 2;

-- (1) on delete cascade: When the row in the parent table is deleted, the row dependent ON the child table is also deleted.
-- (2) on delete set null: converts a foreign key value to a NULL value.
-- Added during table Creation

Drop table tb_student;

Create table tb_student (
Id int primary key,
NAME VARCHAR2 (18) not null,
Sex CHAR (3) DEFAULT 'male' CHECK (sex = 'male' OR sex = 'female '),
Age int check (age> 15 AND age <60 ),
Address varcharacter 2 (100 ),
Phone VARCHAR2 (30) UNIQUE,
-- The foreign key (foreign key name) references the primary table (primary key)
Foreign key (clazz_id) REFERENCES tb_clazz (ID ),
-- When deleting the data in the primary table, you must leave the data associated with the sub-table empty.
Clazz_id int references tb_clazz (ID) ON DELETE SET NULL
);

Delete from tb_clazz where id = 1;

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;