oracle installation, user authorization, table operation, data type, ddl table, dml data.
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.
- Link: https://pan.baidu.com/s/1eiRUYwUp35_oDv5AhHOlkA Extraction code: xynf
- PLSQL Developer 12.0.7 registration code
product code: 4vkjwhfeh3ufnqnmpr9brvcuyujrx3n3le
serial Number: 226959
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
- 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