Use of database in Linux Environment

Keywords: Linux

####Step 1: create a database
Enter the database program directory. Here I am / opt/tmp/desktop/client, as shown in the figure below. The three programs Manager, DBCA and disql are mainly used. Double click 666 to run it. First run the DBCA program

The following interface appears. We can see that this product is called Dameng database configuration assistant in Chinese. Create a database through this graphical interface. According to the default "create database instance", click "start".

Warning, multi picture warning! A picture is more reassuring!
Specify the database template. We don't do any big things. The default is "general purpose". Click "next"

The database directory is the place where the data will be stored in the future. By default, click "next"

Database ID: the default database name, instance name and port number when creating for the first time are shown in the figure below. Continue to click next

Since I have created a database instance according to this default configuration for the first time, I have to change the name, as shown in the following figure:

Click "next" to the database file by default

Initialization parameters are still default, click "next"

Password management. For convenience, I select the second "all users agree to use password", then enter the password and click "next"

Create an instance library. I didn't check it here. I'll add my own mode and table and directly click "next"

Create a summary and click "finish"

This is a bit of a pit. I can't see the content of the execution script... Never mind it

Click "finish" and then "OK". OK, the creation of a database is completed

####Step 2: use DM Service Viwer to view database services

DM Service Viwer is located in the upper level directory of DBCA, which is a bit like the "service" program under Windows. You can see all database services here. The first four are the database management system built by Dameng to manage and monitor the whole database. Ignore it. Among them, DMServiceDMSERVER is the database I just established, and DMServiceTMPSERVER is the database I just established, We can also see that TMPSERVER is in the "running" state at the beginning. Right click to close the database service.

####Step 3: operate the database with Manager
Double click the Manager program to open the program interface, as shown in the figure below. Click to pop up the "login" interface

Here, the port number and password are supplemented according to the information just filled in when creating the database. My port number is 5237, and your default is 5236, and then click OK. If there is no problem, we will connect to the database we just created!

Step 1: add schema and table
First, create a file, homework1.sql, as shown below. Create the schema SPJPro and four tables S, P, J and SPJ

	create schema SPJPro authorization SYSDBA;
	create table SPJPro.S
	CITY   CHAR(10)
	create table SPJPro.P
	create table SPJPro.J
	create table SPJPro.SPJ

Click "file" - > "open" - > select "homework1.sql" in the upper left corner of the Manager interface, load the file into the Manager, and then click the green triangle arrow to run. The results are as follows:

Step 2: add data
Then create the homework2.sql file and insert data into the table, as shown below

insert into SPJPro.S(SNO,SNAME,STATUS,CITY) values('S1','Lean',20,'Tianjin');
insert into SPJPro.S(SNO,SNAME,STATUS,CITY) values('S2','Sheng Xi',10,'Beijing');
insert into SPJPro.S(SNO,SNAME,STATUS,CITY) values('S3','Oriental red',30,'Beijing');
insert into SPJPro.S(SNO,SNAME,STATUS,CITY) values('S4','Fengtaisheng',20,'Tianjin');
insert into SPJPro.S(SNO,SNAME,STATUS,CITY) values('S5','For the people',30,'Shanghai');

insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P1','Nut','red',12);
insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P2','Bolt','green',17);
insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P3','bolt driver','blue',14);
insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P4','bolt driver','red',14);
insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P5','Cam','blue',40);
insert into SPJPro.P(PNO,PNAME,COLOR,WEIGHT) values('P6','gear','red',30);

insert into SPJPro.J(JNO,JNAME,CITY) values('J1','Three construction','Beijing');
insert into SPJPro.J(JNO,JNAME,CITY) values('J2','FAW','Changchun');
insert into SPJPro.J(JNO,JNAME,CITY) values('J3','Spring Factory','Tianjin');
insert into SPJPro.J(JNO,JNAME,CITY) values('J4','a shipbuilding plant','Tianjin');
insert into SPJPro.J(JNO,JNAME,CITY) values('J5','Locomotive Factory','Tangshan');
insert into SPJPro.J(JNO,JNAME,CITY) values('J6','Wireless power plant','Changzhou');
insert into SPJPro.J(JNO,JNAME,CITY) values('J7','Semiconductor factory','Nanjing');

insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S1','P1','J1',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S1','P1','J3',100);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S1','P1','J4',700);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S1','P2','J2',100);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P3','J1',400);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P3','J2',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P3','J4',500);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P3','J5',400);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P5','J1',400);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S2','P5','J2',100);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S3','P1','J1',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S3','P3','J1',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S4','P5','J1',100);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S4','P6','J3',300);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S4','P6','J4',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S5','P2','J4',100);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S5','P3','J1',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S5','P6','J2',200);
insert into SPJPro.SPJ(SNO,PNO,JNO,QTY) values('S5','P6','J4',500);

Similarly, we load the homework2.sql file and run it. The results are as follows

Step 3: query data
Create a new query file and add the statement select * from SPJPro.S. The result is as shown in the figure below. Strangely, why is the query result empty when the data is inserted into the database?

Reason: the data operation in the Manager program is not updated to the database in real time

Solution: commit the transaction, as shown in the following figure

Run the query statement again and the result will come out

####Step 4: use disql to operate the database
The functions of disql and Manager are similar, except that one is a graphical interface and the other is a character interface. There are detailed usage of disql in the program directory manual, which can be viewed by yourself.
The first is the database connection, using the login command

Press enter directly for the server name, which means "localhost" by default
The user name is "SYSDBA"
Password. The port number is set when the database was created
Others are entered, indicating the default
Then you can execute sql statements on this port. For more detailed usage, see the pdf file of manual/special/DM7_Disql user manual. pdf in the database program directory

Posted by Buglish on Tue, 12 Oct 2021 20:00:41 -0700