openGauss database development guide

Posted by hasek522 on Wed, 09 Feb 2022 06:58:45 +0100

This chapter will introduce the school data model and the operation of the school data model table.

1.4 school data model

1.4.1 about this experiment

Taking the school database model as an example, this paper introduces the openGauss database, tables, tablespaces, users and other objects, as well as the use of SQL syntax.
Suppose that school B in city A introduced Huawei openGauss database in order to strengthen the management of the school. In school B, the main objects involved are students, teachers, classes, departments and courses. This experiment assumes that in the database of School B, teachers will teach courses, students will take elective courses, departments will hire teachers, classes will form departments, and students will form classes. Therefore, according to this relationship, this experiment gives the corresponding relationship mode and ER diagram, and carries on the basic database operation.

1.4.2 relationship model

For the five objects in B calibration, the attribute set belonging to each object is established respectively. The specific attribute description is as follows:
● student (student number, name, gender, date of birth, date of enrollment, home address)
● teacher (teacher number, teacher name, professional title, gender, age, entry date)
● class (class number, class name, head teacher)
● Department (department number, department name, department head)
● course (course number, course name, course type, credits)

The number corresponding to the above attributes is:
● student(std_id，std_name，std_sex，std_birth，std_in，std_address)
● teacher(tec_id，tec_name，tec_job，tec_sex，tec_age，tec_in)
● class(cla_id，cla_name，cla_teacher)
●school_department(depart_id，depart_name，depart_teacher)
● course(cor_id，cor_name，cor_type，credit)

Relationships between objects:
● one student can choose multiple courses, and one course can be selected by multiple students
● one teacher can choose multiple courses, and one course can be taught by multiple teachers
● a department can be composed of multiple classes
● one department can employ more than one teacher
● a class can consist of more than one student

1.4.3 E-R diagram

Figure 1-1 E-R diagram

1.5 school data model table operation

1.5.1 table creation

According to the scene description of School B, this experiment creates corresponding tables for student s, teacher s, class es, school_department s and course s. The specific experimental steps are as follows:

Step 1 create a student information table.

DROP TABLE IF EXISTS student;
CREATE TABLE student
(
        std_id INT PRIMARY KEY,
        std_name NCHAR(20) NOT NULL,
        std_sex NCHAR(6),
        std_birth DATE,
        std_in DATE NOT NULL,
        std_address VARCHAR(100)
);

Step 2 create a teacher information table.

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher
(
        tec_id INT PRIMARY KEY,
        tec_name CHAR(20) NOT NULL,
        tec_job CHAR(15),
        tec_sex CHAR(6),
        tec_age INT,
        tec_in DATE NOT NULL
);

Step 3 create class information table.

DROP TABLE IF EXISTS class;
CREATE TABLE class
(
        cla_id INT PRIMARY KEY,
        cla_name CHAR(20) NOT NULL,
        cla_teacher INT NOT NULL
);

Step 4: create department information table.

DROP TABLE IF EXISTS school_department;
CREATE TABLE school_department
(
        depart_id INT PRIMARY KEY,
        depart_name NCHAR(30) NOT NULL,
        depart_teacher INT NOT NULL
);

Step 5 create a course information table.

DROP TABLE IF EXISTS course;
CREATE TABLE course
(
        cor_id INT PRIMARY KEY,
        cor_name NCHAR(30) NOT NULL,
        cor_type NCHAR(20),
        credit numeric
);

1.5.2 insertion of table data

Step 1 insert data into the student table.

INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (1,'Zhang Yi','male','1993-01-01','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (2,'Zhang Er','male','1993-01-02','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (3,'Zhang San','male','1993-01-03','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (4,'Zhang Si','male','1993-01-04','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (5,'Zhang Wu','male','1993-01-05','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (6,'Zhang Liu','male','1993-01-06','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (7,'Zhang Qi','male','1993-01-07','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (8,'Zhang Ba','male','1993-01-08','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (9,'Zhang Jiu','male','1993-01-09','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (10,'Li Yi','male','1993-01-10','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (11,'Li Er','male','1993-01-11','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (12,'Li San','male','1993-01-12','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (13,'Li Si','male','1993-01-13','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (14,'Li Wu','male','1993-01-14','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (15,'Li Liu','male','1993-01-15','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (16,'Li Qi','male','1993-01-16','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (17,'Li ba','male','1993-01-17','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (18,'Li Jiu','male','1993-01-18','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (19,'Wang Yi','male','1993-01-19','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (20,'WangTwo ','male','1993-01-20','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (21,'Wang San','male','1993-01-21','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (22,'Wang Si','male','1993-01-22','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (23,'Wang Wu','male','1993-01-23','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (24,'Wang Liu','male','1993-01-24','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (25,'Wang Qi','male','1993-01-25','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (26,'bastard','male','1993-01-26','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (27,'Wang Jiu','male','1993-01-27','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (28,'Qian Yi','male','1993-01-28','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (29,'Qian Er','male','1993-01-29','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (30,'Qian San','male','1993-01-30','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (31,'Qian Si','male','1993-02-01','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (32,'Qian Wu','male','1993-02-02','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (33,'Qian Liu','male','1993-02-03','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (34,'Qian Qi','male','1993-02-04','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (35,'Qian Ba','male','1993-02-05','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (36,'Qian Jiu','male','1993-02-06','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (37,'Wu Yi','male','1993-02-07','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (38,'Wu er','male','1993-02-08','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (39,'Wu San','male','1993-02-09','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (40,'Wu Si','male','1993-02-10','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (41,'Wu Wu','male','1993-02-11','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (42,'Wu Liu','male','1993-02-12','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (43,'Wu Qi','male','1993-02-13','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (44,'Wu Ba','male','1993-02-14','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (45,'Wu Jiu','male','1993-02-15','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (46,'Liu Yi','male','1993-02-16','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (47,'Liu er','male','1993-02-17','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (48,'Liu San','male','1993-02-18','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (49,'Liu Si','male','1993-02-19','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');
INSERT INTO student(std_id,std_name,std_sex,std_birth,std_in,std_address) VALUES (50,'Liu Wu','male','1993-02-20','2011-09-01','Yuhuatai District, Nanjing City, Jiangsu Province');

Step 2 insert data into the teacher table.

INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (1,'Zhang Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (2,'Zhang Er','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (3,'Zhang San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (4,'Zhang Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (5,'Zhang Wu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (6,'Zhang Liu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (7,'Zhang Qi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (8,'Zhang Ba','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (9,'Zhang Jiu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (10,'Li Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (11,'Li Er','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (12,'Li San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (13,'Li Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (14,'Li Wu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (15,'Li Liu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (16,'Li Qi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (17,'Li ba','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (18,'Li Jiu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (19,'Wang Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (20,'WangTwo ','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (21,'Wang San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (22,'Wang Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (23,'Wang Wu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (24,'Wang Liu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (25,'Wang Qi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (26,'bastard','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (27,'Wang Jiu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (28,'Qian Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (29,'Qian Er','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (30,'Qian San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (31,'Qian Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (32,'Qian Wu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (33,'Qian Liu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (34,'Qian Qi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (35,'Qian Ba','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (36,'Qian Jiu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (37,'Wu Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (38,'Wu er','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (39,'Wu San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (40,'Wu Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (41,'Wu Wu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (42,'Wu Liu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (43,'Wu Qi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (44,'Wu Ba','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (45,'Wu Jiu','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (46,'Liu Yi','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (47,'Liu er','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (48,'Liu San','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (49,'Liu Si','lecturer','male',35,'2009-07-01');
INSERT INTO teacher(tec_id,tec_name,tec_job,tec_sex,tec_age,tec_in) VALUES (50,'Liu Wu','lecturer','male',35,'2009-07-01');

Step 3 insert data into the class table.

INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (1,'computer',1);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (2,'automation',3);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (3,'Aircraft design',5);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (4,'College Physics',7);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (5,'Advanced mathematics',9);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (6,'College Chemistry',12);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (7,'perform',14);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (8,'clothing design',16);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (9,'industrial design',18);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (10,'Finance',21);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (11,'Medical Science',23);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (12,'civil engineering',25);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (13,'Mechanics',27);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (14,'architecture',29);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (15,'economics',32);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (16,'financial management',34);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (17,'human resources',36);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (18,'Dynamics',38);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (19,'artificial intelligence',41);
INSERT INTO class(cla_id,cla_name,cla_teacher) VALUES (20,'accounting',45);

Step 4 report to school_ Insert data into the Department table.

INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (1,'school of computing',2);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (2,'Automation college',4);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (3,'Institute of Aeronautics and Astronautics',6);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (4,'college of art',8);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (5,'College of Science',11);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (6,'College of artificial intelligence',13);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (7,'engineering institute',15);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (8,'School of management',17);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (9,'Agricultural College',22);
INSERT INTO school_department(depart_id,depart_name,depart_teacher) VALUES (10,'medical college',28);

Step 5 insert data into the course table.

INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (1,'Introduction to Database System ','Compulsory',3);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (2,'Introduction to art design','Elective course',1);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (3,'Mechanical drawing','Compulsory',4);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (4,'Aircraft design history','Elective course',1);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (5,'Marxism','Compulsory',2);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (6,'University History','Compulsory',2);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (7,'Human resource management theory','Compulsory',2.5);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (8,'linear algebra','Compulsory',4);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (9,'JAVA Programming','Compulsory',3);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (10,'operating system','Compulsory',4);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (11,'Principle of computer composition','Compulsory',3);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (12,'Automation design theory','Compulsory',2);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (13,'Emotional performance','Compulsory',2.5);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (14,'Tea history','Elective course',1);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (15,'Art theory','Compulsory',1.5);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (16,'machine learning','Compulsory',3);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (17,'data mining ','Elective course',2);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (18,'image recognition','Compulsory',3);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (19,'anatomy','Compulsory',4);
INSERT INTO course(cor_id,cor_name,cor_type,credit) VALUES (20,'3D max','Elective course',2);

1.5.3 data query

1.5.3.1 single table query

Query all course information of school B.

postgres=# SELECT * from course;
 cor_id |            cor_name            |      cor_type      | credit
--------+--------------------------------+--------------------+--------
      2 | Introduction to art design                   | Elective course               |      1
      3 | Mechanical drawing                       | Compulsory               |      4
      4 | Aircraft design history                 | Elective course               |      1
      5 | Marxism                     | Compulsory               |      2
      6 | University History                       | Compulsory               |      2
      7 | Human resource management theory               | Compulsory               |    2.5
      8 | linear algebra                       | Compulsory               |      4
      9 | JAVA Programming                   | Compulsory               |      3
     10 | operating system                       | Compulsory               |      4
     11 | Principle of computer composition                 | Compulsory               |      3
     12 | Automation design theory                 | Compulsory               |      2
     13 | Emotional performance                       | Compulsory               |    2.5
     14 | Tea history                       | Elective course               |      1
     15 | Art theory                         | Compulsory               |    1.5
     16 | machine learning                       | Compulsory               |      3
     17 | data mining                        | Elective course               |      2
     18 | image recognition                       | Compulsory               |      3
     19 | anatomy                         | Compulsory               |      4
     20 | 3D max                         | Elective course               |      2
      1 | C Language programming                  | Compulsory               |    3.5
(20 rows)

1.5.3.2 query criteria

Query the enrollment year of teachers with teacher number greater than 45 in the teacher information table.

postgres=# SELECT tec_id, tec_in FROM teacher WHERE tec_id>45;
 tec_id |       tec_in
--------+---------------------
     46 | 2009-07-01 00:00:00
     47 | 2009-07-01 00:00:00
     48 | 2009-07-01 00:00:00
     49 | 2009-07-01 00:00:00
     50 | 2009-07-01 00:00:00
(5 rows)

Query the information of all elective courses in school B.

postgres=# SELECT * FROM course WHERE cor_type = 'elective';
 cor_id |            cor_name            |      cor_type      | credit
--------+--------------------------------+--------------------+--------
      2 | Introduction to art design                   | Elective course               |      1
      4 | Aircraft design history                 | Elective course               |      1
     14 | Tea history                       | Elective course               |      1
     17 | data mining                        | Elective course               |      2
     20 | 3D max                         | Elective course               |      2
(5 rows)

1.5.4 modification and deletion of data

1.5.4.1 modify data

Modify / update course information table data.

postgres=# UPDATE course SET cor_name='C language programming ', cor_type = 'compulsory', credit=3.5 WHERE cor_id=1;
UPDATE 1
postgres=# SELECT * FROM course WHERE cor_id=1;
 cor_id |         cor_name         |      cor_type      | credit
--------+--------------------------+--------------------+--------
      1 | C Language programming            | Compulsory               |    3.5
(1 row)

1.5.4.2 delete specified data

Delete the departments managed by teacher numbers 8 and 15 in school B.

postgres=# DELETE FROM school_department WHERE depart_teacher=8 OR depart_teacher=15;
DELETE 0
postgres=# SELECT * FROM school_department;
 depart_id |         depart_name         | depart_teacher
-----------+-----------------------------+----------------
         1 | school of computing                  |              2
         2 | Automation college                  |              4
         3 | Institute of Aeronautics and Astronautics                |              6
         5 | College of Science                      |             11
         6 | College of artificial intelligence                |             13
         8 | School of management                    |             17
         9 | Agricultural College                      |             22
        10 | medical college                      |             28
(8 rows)

The experiment is over.

2 Appendix I: basic operation of openGauss database

2.1 viewing database objects

● view help information:

postgres=# \?

● switching database:

postgres=# \c dbname

● list databases:
Use the \ l metacommand to view the database list of the database system.

postgres=# \l

Use the following command to enter PG from the system table_ Database query database list.

postgres=# SELECT datname FROM pg_database;

● list:

postgres=# \dt

● list all tables, views and indexes:

postgres=# \d+

● use the \ d + command of gsql to query the attributes of the table.

postgres=# \d+ tablename

● check the table structure:

postgres=# \d tablename

● list schema:

postgres=# \dn

● view index:

postgres=# \di

● query table space:
Use the meta command of gsql program to query the tablespace.

postgres=# \db

Check pg_tablespace system table. The following commands can find all table spaces defined by the system and users.

postgres=# SELECT spcname FROM pg_tablespace;

● view the list of database users:

postgres=# SELECT * FROM pg_user;

● to view user attributes:

postgres=# SELECT * FROM pg_authid;

● view all roles:

postgres=# SELECT * FROM PG_ROLES;

2.2 other operations

● view all SQL statements supported by openGauss.

postgres=#\h