Due to the learning needs, I will start to learn the basic knowledge of database and the preparation and use of SQL statements from today. Therefore, I record my learning process here to urge myself, consolidate the knowledge I have learned, and hope to help more introductory students.

1. Establish database

create database db_name;//db_name is the name of the database.

Since I have opened the database, creating the database again will prompt "failed, database loaded".

2. Create data table

//Create student table, student number, name, age and gender
create table STUDENTS(
id numeric(4,0) not null,
name char(8) not null,
sex char(4) not null,
age numeric(2,0) not null
);

Among them, general data types are involved, specifically:

NUMERIC(p,s) means precision, and s means the number of digits after the decimal point. For example, NUMERIC(6,0) means precision before the decimal point is 6, and there are 0 digits after the decimal point.

CHAR(n) character / string. The fixed length is n.

The established table is shown below.

ORACLE SQL data type collation:

data type	parameter	describe
number(m,n)	m is the precision (length), and n is the number of digits after the decimal point	number(5,2) means the precision is 5, and 2 digits are reserved after the decimal point, such as 123.45
char(n)	n represents the length of the string	char(5) indicates that the string length is 5, such as hello
varchar2(n)	n is the maximum string length, which is variable length	varchar2(10) represents a string whose length does not exceed 10. When it does not exceed 10, it will automatically adjust the field length according to the data size
data	-	The default format is DD-MON-YY. For example, 07-november-00 means November 7, 2000.
long	-	Variable length string, which is older and similar to varchar2(n), is rarely used
raw(n)	n is the maximum length	Variable length binary data, less used
long raw	-	Variable length binary data, less used
blob,clob nclob	-	Three large objects (lobs) are used to save large graphics files or formatted text files, such as Microsoft Word documents, as well as non text files such as audio and video. The maximum length is 4GB. There are several types of lobs. Depending on the type of bytes you use, Oracle 8i actually stores these data in the database. Special operations such as reading, storage and writing can be performed.
bfile	-	The maximum length of a large binary object file saved outside the database is 4GB. This external LOB type records changes through the database, but the specific saving of data is carried out outside the database. Oracle 8i can read and query BFILE, but cannot write. The size is determined by the operating system‘

3. Add data to the table

//Syntax: insert[into] table name [(column name 1, column name 2,...)] values (value 1, value 2, value 3,...);
//The contents in [] are optional
insert into STUDENTS  values(001, 'Xiao Zhang', 'male'，18);
insert into STUDENTS  values(002, 'Xiao Wang', 'female'，19);
insert into STUDENTS  values(003, 'petty thief', 'male'，20);
insert into STUDENTS  values(004, 'Xiao Zhao', 'male'，18);
insert into STUDENTS  values(005, 'Xiaogu', 'female'，19);
insert into STUDENTS  values(006, 'Small money', 'male'，21);
insert into STUDENTS  values(007, 'Xiao Sun', 'female'，20);
insert into STUDENTS  values(008, 'Xiao Zhou', 'male'，19);
insert into STUDENTS  values(009, 'Xiao Wu', 'male'，17);
insert into STUDENTS  values(010, 'Xiao Zheng', 'female'，19);

After adding data, use the following statement to view

select * from STUDENTS;//*Represents all columns

select NO from STUDENTS;//Query NO column

The display results are shown in the following figure.

Note: if the data submitted by us is not displayed by clicking Query data in PL/SQL Developer software after adding data, the solution is to add an instruction COMMIT at the end of the SQL instruction in the SQL statement window; the problem is solved.

4. Query data by specific conditions

The where keyword is used to specify query conditions. The syntax format is: select column name from table name where condition;

1) Query the data of all students whose gender is "female"

select * from STUDENTS where sex='female';

The results are shown in the figure:

2) Query the data of all students aged between 18 and 20

select * from STUDENTS where age>=18 and age<=20;

The results are shown in the figure:

3) query all student information with id greater than 5 and age greater than 20

select * from STUDENTS where id>5 and age>20;

The results are shown in the figure:

5. Add a column of data

alter   table  STUDENTS  add(address  varchar2(20)); //Add a new column "address"

6. Modify one row of data

Basic syntax: update table name set field = 'value' where condition; as

update students set sex='male' where age=20;

 

7. Delete a row of data

delete from STUDENTS where id = '1';//Delete data with id=1

8. Delete a column of data

alter table STUDENTS drop column address;

Other operations shall be supplemented later.