Oracle Learning Notes

1. Modify the user password: Log in to Sqlplus as DBA and enter the command:

ALTER USER username identified by NewPassword;

Note here: The new password must not be less than 7 characters Upper The first character cannot be a number.

2. Unlock user accounts: ALTER user username account UNLOCK;

3. The difference between a unique constraint and a PRIMARY KEY constraint is that if a column in the datasheet has a null value, the column cannot be set as the primary key column, but it can be set to a unique constraint, and one difference is that the data table can have only one primary KEY constraint, but there are many unique constraints.

4. Add data from other tables to the existing table:

INSERT into Table_name1 (C1,C2) SELECT c1,c2 from Table_name2;

Create a new table from another table and populate it with the same data:

CREATE TABLE table_name1 as SELECT c1,c2 from Table_name2;

5. Merge clause:

MERGE [into] table_name1 USING table_name2 on (condition)

When matched then Merge_update_clause

When isn't matched then merge_insert_clause;

For example:

MERGE into LOGIN USING managerinfo

On (LOGIN. Login_id=managerinfo. ManagerID)

When matched then

UPDATE SET LOGIN. Loginname=managerinfo. LOGINNAME

When isn't matched then INSERT

VALUES (Managerinfo. Loginname,managerinfo. PASSWORD,

Managerinfo. ManagerID);

6, substr (string, intercept start position, intercept length)//return the intercepted word

substr (' Hello World ', 0, 1)//Returns the result as ' H ', starting from the first character of the string to intercept the long

A string with a degree of 1

substr (' Hello World ', 1, 1)//return result for ' H ', 0 and 1 are all the starting positions of the Intercept

to the first character

substr (' Hello world ', 2,4)//return result as ' Ello '

substr (' Hello world ', -3,3)//Returns the result of ' rld ' * negative number (-i) to indicate the starting position of the intercept is

The right-hand side of the string, the first character

7. ALTER TABLE table_name DROP COLUMN C1 CASCADE CONSTRAINTS.

CASCADE constraints said that the constraints associated with the column were also removed.

8, NVL function: NVL (STR1, Replace_str), function: If STR1 is null, the NVL function returns the value of REPLACE_STR, otherwise the value of STR1 is returned.

Oracle Learning Notes