Basic notes of Oracle Database

Time:2021-6-7

Oracle Database

Oracle → Oracle

Oracle database is a kind of relational database, which is composed of rows and columns

Data: text, picture, video

  • DB (database): a warehouse for storing data
  • The minimum unit of data storage in database is table, which follows SQL standard
  • DBS (database system): database system
  • DBMS (database management system)
Oracle: database version
Oracle DB Version function
Oracle7、Oracle8 Localized access
Oracle8i、Oracle9i Network access
Oracle10g、Oracle11g Support grid access and distributed operation
Oracle12c Support cloud technology access
Common databases in the market:
Parameters / database Excel、Access Sql、Server(MicroSoft)、MySQL(Oracle) Oracle(Oracle) DB2 (IBM)
Storage capacity Within 2W 10W level 1000W class 1000W class
Security nothing Account / password Account / password Account / password / permission
cost 0 0 service charge service charge

The data stored in a single table has reached tens of millions level (without affecting the query efficiency)

SQL (Structured Query Language)

Differences between different databases
  1. data type
  2. Library function
  3. PL / SQL programming: functions, stored procedures, triggers, cursors, etc

1. Start data service: (Oracle control panel → management tools → service)

Oracleservice + instance name (oracleserviceorcl)

2. Start the database service listener so that the client can connect to the data

OracleOraDb10g_home1TNSListener

Account password: system / system Database: orcl

3. Oracle data type:

three point one Data type: number

​ Number (): represents a numeric type. The default value is 38 bits

​ Number (n): represents an n-bit integer, number (3) → (- 999-999)

​ Number (n, m): represents n significant digits, with 2 decimal places

three point two String type
Char (n): a fixed length string. It can only store 3 bytes (one Chinese character) at most

Char (10): only 10 characters can be stored, not enough 10 characters —— Waste of storage space, but the query effect is higher than varchar, the maximum value is 2000 characters

Varchar (n): variable length string

Varchar (10): up to 10 characters, not enough to store according to the actual size —— The maximum value of varchar is 4000 characters

VARCHAR2 (Oracle10g start)

VARCHAR2 (n): optimizing varchar with variable length strings —— The query efficiency is higher than that of varchar. The maximum value of varchar is 4000 characters

three point three Time type

Data: accurate to year, month, day, hour, minute and second

Timestamp: accurate to the last five digits of year, month, day, hour, minute, second and millisecond)

three point four Big field class (big data)

Blob: big field (big data) stored in binary mode: video file, picture file (maximum 4G)

CLOB: big field of bytecode storage (big data): (maximum 4G)

DDL: Data Definition Language

DML: Data Manipulation Language

TCL: transaction control language

DCL: data control language

DQL: Data Query Language

4. DDL statement (database definition language)

Table name and column name naming specification:
With (letter – $) First letter followed by (letter – $)_ Number), cannot be Oracle keyword
four point one Create table
Create table name{
	Column name 1 data type,
	Column name 2 data type,
	.......
	Column name n data type
	}
four point two Modify table structure
New column:
Alert table name
	add(
	Column name 1 data type,
	Column name 2 data type,
	.......
	Column name n data type
	)
Modify the data type of the column:
Alter table name
Modify new data type of column name;
Note: the new data type must be compatible with the old data type
Delete column:
Alter table drop column;
Modify column name:
Alter table name rename to new column name;
Modify table name:
Rename (table name) to (new table name)
four point three Table constraints
Three paradigms of data design

​ 1. The first normal form: column is not separable (atomicity)
​ 2. The second paradigm: first, the data must be distinguishable (primary key) on the basis of satisfying the first paradigm
​ 3. The third normal form: on the basis of satisfying the second normal form, the non primary attribute of this table depends on other tables and must only rely on the primary attribute (foreign key) of other tables

Add constraint:
alter table t_class
Add constraint constraint name constraint category column
Constraint category

​ 1. Primary key constraint primary key
​ 2. Unique key constraint unique
​ 3. Check constraints check
​ 4. Foreign key constraints foreign key
​ 5. Default value default

four point four SQL structured query language

​ create table、alter table、drop table、truncate table

Data definition language (DML)

​ Adding, deleting and modifying DML statements can only affect buffer contents
​ DML statement must be used with TCL statement to really affect table data

Insert into table name (column name 1,... Column name n) values (value 1,... Value n);
			
Update table name set column name 1 = value 1,... Column name n = value n [where column name XX = XXX];
			
Delete from table name [where column name XX = XXX];
TCL > transaction control language

​ commit; Commit transaction
​ rollback; RollBACK

DQL data query language
Select column name 1,... Column name n from table name [where column name XX = XXX];

5. Built in functions

five point one Character function:

​ lower: Convert upper case to lower case
​ upper: Lower case to upper case
​ initcap: Capitalize the first letter
​ instr: Find the subscript of a character in a string
​ concat: Splicing strings
​ substr: Intercept string
​ trim: Remove spaces before and after
​ lpad: Fills a string from the left with the specified character
​ rpad: Fills a string from the right with the specified character
​ replace: replace
​ length: Calculate string length

five point two Numerical function:

​ round: rounding
​ floor: Round down
​ ceil: Round up
​ trunc: truncation

five point three Time function:

​ sysdate: Get the current system time
​ current_ date: Get the current system time
​ current_ timestatmp: Get the current system time (MS)
​ months_ between: How many months is the interval between two times calculated
​ add_ months: Calculate the time after n months after the current time
​ last_ day: Calculates the last day of the month in which the current time is located
​ next_ day: The date of the nearest week from the current time (the first day of a week in foreign countries is Sunday)

five point four Conversion function:

​ to_ number: Convert data to numerical type
​ to_ char: Convert data to character type
​ to_ date: Turn data into time

five point five General function:

​ MVL: NVL (value, 1) Judge whether the value is null. If it is null, take 1; otherwise, take 1
​ NVL: nvl2 (value, value 1, value 2) Judge whether the value is null, if it is null, take 2, otherwise take 1

five point six case when: General condition expression
five point seven decode: Calculation method of special function
five point eight dense_ Rank () over (partition by) does not consider the partition in the case of concurrency
five point nine row_ Number () over (partition by) considers the partition in the case of concurrency
five point one zero rank() over(partition by) If there is no concurrency in rownum, only the first three partitions will be sorted. If there is concurrency, all the parallel data will be displayed

is null The data is null

is not null The data is not null

= be equal to

<> != Not equal to

or perhaps

and also

in In… Inside

not in It’s not in

between and Within the scope of

not between and Not within the scope of

group by The purpose is to use the aggregate function sum AVG min max count

group by You can only query which field

Subquery: continue to query the result as a condition

6. Table link:

six point one Self connection: it is a special form of internal connection, connecting with oneself
A table inner join a table on A. field = A. field
A table a table where A. field = A. field
six point two Inner join: n tables are connected, with at least n-1 constraints. Matching data will be displayed, and unmatched data will not be displayed
Equivalent connection:
A table inner join b table on A. field = B. field
A table B table where A. field = B. field
Non equivalent connection:
In the join condition, compare the column values of the joined column using a comparison operator other than the equal operator. These operators include >, > =, < =!
six point three External connection:
Left outer link:

​ N tables are connected, and the constraint condition is at least N – 1. The left table is the driving table (main table). The data in the left table is displayed, the matching data in the right table is displayed, and the unmatched data is null

Right outer connection:

​ The right table is used as the driving table (main table). The data in the right table is displayed, the matched data in the left table is displayed, and the unmatched data is null

All foreign links:

​ N tables are connected with at least N – 1 constraints, data matched by inner connection + data not matched by left outer connection + data not matched by right outer connection

six point four Cartesian connection
Select * from table a, table b
Select * from a table cross join b table

7. DCL language (data control language): Grant revoke

DBA、RESOURCE、CONNECT Three standard roles
Empowerment method:

​ Give users permission directly

​ Give the role to the user, different roles have different permissions

seven point one Create user
create user ztkj1710 identified by admin
seven point two Grant permission grant
grant connect to ztkj1710
grant create session to ztkj1710
seven point three Recall
revoke connect from ztkj1710

Recommended Today

What is “hybrid cloud”?

In this paper, we define the concept of “hybrid cloud”, explain four different cloud deployment models of hybrid cloud, and deeply analyze the industrial trend of hybrid cloud through a series of data and charts. 01 introduction Hybrid cloud is a computing environment that integrates multiple platforms and data centers. Generally speaking, hybrid cloud is […]