Learning video: https://www.bilibili.com/video/BV1tJ411r7EC?p=15

Baidu cloud installation package is attached to the installation tutorial: https://blog.csdn.net/qq_40774525/article/details/79919057 

oracle Baidu cloud installation package: https://pan.baidu.com/s/1qVWwCUR37j2JxEecYbe5HQ  Extraction code: 5abz

After the oracle service above is installed, open the client of SQL developer and the error "SQL developer lacks corresponding shortcut" will be reported. At this time, install the following SQL developer to solve the problem.

sqldeveloper attachment:

• Link: https://pan.baidu.com/s/1eiRUYwUp35_oDv5AhHOlkA Extraction code: xynf
• PLSQL Developer 12.0.7 registration code
  product code: 4vkjwhfeh3ufnqnmpr9brvcuyujrx3n3le
  serial Number: 226959
  password: xs374ca

Tablespace user authorization:

SID uniquely identifies the database name of the computer oracle. A computer may have multiple Oracle installations. SID differentiation is required at this time. The SID is placed after the Oracle account name @. To run Oracle, two services must be enabled, one is the primary service "OracleServiceSID", the other is the primary service listener. If the client is unable to connect, check that both services are turned on.

SYS and SYSTEM are SYSTEM users, but SYS will have greater permissions. SYSTEM can only log in using normal mode, while SYS can only log in using SYSDBA or SYSOPER role. The operation of SYS is irreversible and should be used with caution.

Create your own users:

• Create a table namespace using create tablespace

  • create tablespace pratice
    datafile 'E:\pratice.dbf'
    size 10m

• Create user with create user

  • create user bibi
    identified by bibi
    default tablespace pratice

• grant is used to authorize users. In this case, authorization refers to "assign roles". The "assign roles" and "assign permissions" are different. "grant create view to bibi" is to assign create view permission to bibi users.

  • -- connect Temporary users resouce Reliable regular users dba Database Administrator
    grant connect,resource to bibi

To sum up the above process: install oracle server software - > create database (auto configuration during installation) - > configure listener (auto configuration during installation) - > install oracle database operation client sqldeveloper - > create user's tablespace - > create user and authorize

Data type:

• Number, similar to the double type of C ා, number(4,3) means up to four numbers in total, and up to three digits after the decimal place. number(3,-1) means that the decimal place moves one digit to the left 139 - > 130.

  • declare
      --Up to five integers,If there are decimal places, round them.
      test number(5) := 1234.64;
      --Integers can be up to two digits, decimals can be up to three digits, and the extra decimals can be rounded.
      test1 number(5, 3) := 34.9345;
      test2 number(3,-1):=998;
    begin
      dbms_output.put_line(test);
      dbms_output.put_line(test1);
      dbms_output.put_line(test2);
    end;
    
    output: 
    1235
    34.935
    1000

• varchar2, variable length string type, up to 4000 bytes. null if empty string. It is unique to oracle.
• char, fixed length storage, if the content is not enough, fill with space.
• clob, store large text.
• date: there are hours, minutes and seconds on MM DD YY.

Create Table: Create Table

create table person(
 name varchar2(20),
 age number,
 gender char(1),
 phone varchar2(20)
)

Delete table: Drop Table

drop table person

Modify table: Alter Table

-- Table add column
alter table person add name1 varchar2(30);
-- Table delete column
alter table person drop column name1;
-- Table rename column
alter table person rename column name1 to name2;
-- Rename table name
alter table person rename to people

Steps to modify the table structure: data backup, empty the original table data, modify the original table structure, and insert the backup data into the original table.

Table adding data: three writing methods

-- inset into tablename (Column 1, column 2..,Last column) values(Value 1,Value 2...)
insert into person
  (name, age, gender, phone)
values
  ('Uncle husky', 90, '1', '110');
  
-- inset into tablename (Column 1, column 2..,Last column) values(Value 1,Value 2...)
-- Column order, as many as you can, just match the value to the previous column
insert into person
  (name, age)
values
  ('Uncle husky', 90);

-- There is no column parameter. All column values must be filled in sequence
insert into person values ('Uncle husky', 90, '1', '111');

Table delete data:

-- Delete all data from table
delete from person
delete person
-- Delete data by condition
delete from person where name='Uncle husky'

Table modification data:

update person set name='Uncle corky',age=88;
update person set name='Meow meow' where age=90;

Table query data:

-- *For all columns
select * from person;
select * from person where ... order by desc