Oracle self-study notes

Oracle sentence learning

SQL statements are case-insensitive, can be written on one or more lines, keywords cannot be abbreviated or split, and indentation is used to improve readability

Change the password to oracle:

Alter user sys identified by oracle account unlock

Oracle backup (EXP)

Exp sys@orcl file=D:\ sys.dmp

Oracle recovery (imp)

Imp userid=uname/pwd tables=ce file=/home/oracle/data-bak/ce.dmp

Select: identification, select those columns

From: identification, selecting from those tables

Select ID,name from tables;: select a specific column

Select followed by column name from followed by table name

The connector is represented by | | (concatenates columns with columns, columns with characters)

Duplicate line

By default, the query returns all rows, including duplicate rows

Delete duplicate lines

Delete duplicate lines using the keyword "DISTINCT" in the select clause

Where clause

You can add a where clause after select to qualify the result set of the query

For example, to query a student whose name is XS:

Select * from t_c11_zyryda where xm='xs'

The table structure used by desc/describe to view data

Desc dba_users

Distinct (deduplicated) search results do not need to be used when duplicate data appears

Selec distinct product_name from prcduct

Judge whether it is empty or not (it is difficult to shout anything)

Select * from school where address is null

View time

Select sysdate from dual

Three days later

Select sysdate + 3 from dual

Alias for the column (add the keyword AS, aliases in double quotes)

SQL > select sysdate as current time from dual

Current time

2018-9-4 10

Addition, subtraction, multiplication and division calculator

Select 8-8 from dual

ORACLE default form

Emp table

Empno employee number, unique primary key

Name of ename employee

Job work

Mgr employee Manager number

Hiredate entry date

Sal salary

Comm bonus

Deptno department number

Ioc address

Select statement

Select ename,job,sal from emp; (query employee's name, job, salary)

Select column (followed by column,). Column from table name where column

Select * from emp where sal > 2000; (find employees whose salary is greater than 2000)

Rollback rollback

Commit submission

Bulk insert statement

Insert info table name values ()

Insert into test values (null,'SYS_C00521',51,null)

Insert table test as select from emp

Select from test update, which locks all rows of the test table (do not use)

Select * from test ename= Fan Bingbing for update (lock only one row)

Update modify statement

Select * from test

Update test set sal = 10000; (add the where condition in the actual work, otherwise the whole table will be modified