Learning Notes for beginner Oracle and SQL learners. Hanshunping-Play with Oracle.

I myself is a beginner of Oracle and SQL, some time ago saw Hanshunping Teacher's Oracle Video tutorial, feel very easy to learn, a lot of harvest. At the same time he also made a lot of notes, now want to put paper notes in their own words summed up. As the saying goes: teaching always complements each other, in the process of summing up will certainly deepen their impressions, can consolidate their own learning process.

Oracle User section:

Create User: User_name identified by User_password;

The user does not have permission to connect to the database at this time. Need to give it permission to connect

Permissions: Permissions are divided into object permissions and system permissions.

Grant [Select|updat|all] on table_name to user_name;--object permissions

Grant connect to user_name with admin option; --System permissions

With admin option indicates that the user can continue to send this permission

REVOKE permission: Revoke Quanxian on table_name from user_name;

Connection database: Conn User_name/user_password;

Profile file, you can limit the behavior of certain users, such as number of login attempts, password input errors, etc.

Creating profile: Create profiles profile_name limit failed_login_attempts m password_lock time N;

Apply to a User: Alter user user_name profile Profile_name;

When you first set up an Oracle DB instance, several users are created by default, but only a few users are unlocked, and the steps to unlock a user are as follows:

First, to log in to the Oracle database as a SYSDBA user, you need to open Sqlplus this Oracle-installed tool

CMD or terminal under: Sqlplus/as sysdba;

Unlock User:alter user user_name account unlock;

Change Password: Alter user user_name identified by password;

Here, the system default password for several commonly used users is summarized as follows:

Scott:tiger

System:manager

Sys:change_on_install

SYSDBA does not require a password to log on.

Several users of the rights specific how to wait for me to sum up the back, Hanshunping teacher here is a little scattered.

Continue user management.

Set Password history: You cannot use a previously used password when modifying a password

Password_reuse_time n;--n days to reuse passwords

Change Password regularly:

Create profile profile_name limit password_life_time m pasword_grace_time N;

M days Change Password once, grace period n days.

Several data types in the SQL statement:

CHAR (n): fixed-length string, maximum 2000, fast query speed

VARCHAR2 (n): variable length string, maximum 4000, space saving

CLOB (character large object): Large-character, 4G Max

Number (M,n): M-bit active, where n decimal

Number (m): M-bit integer

Date: Day of the month days and seconds

Timestamp: more precise

Blob: Picture type, binary data can be stored picture, sound, Maximum 4G (Han teacher was using 9i, I used a 64-bit 12c version, estimated to support the maximum 8G bar. Own guess)

Display table structure: DESC table_name;

Add field: ALTER TABLE table_name Add (colum_name,colum_type); \

Modify field: ALTER TABLE table_name modify (COLUM_NAME,COLUM_TYPE);

Delete field: ALTER TABLE table_name drop Colum colum_name;

Delete tables: DROP TABLE table_name;

Date format ' Day-month-year '

INSERT INTO table_name values (the value of each field, if there are non-numeric field values, you need to enclose them in single quotation marks)

Alter session set Nls_date_format ' YYYY-MM-DD ';

"Note" The effect of this change is temporary.

Insert null value null (this field is allowed to be empty)

Query null value: Select xxx from xxx where colum_name is [null| Not NULL];

Change table contents: Update table_name set colum_name= ' xxx ' where yyy;

Delete data: Delete from table_name;--deletes the data, the structure of the table is still, recoverable, and the deletion speed is slightly slower
TRUNCATE TABLE table_name; Delete all the data in the table, the table structure is still there, but because the log is not written, delete records can not be retrieved, the advantage is that the removal speed is fast.  

Recover data:

Save point point_name;--Create recovery points

Delete from table_name;

Rollback to point_name;--Recovery

Delete table structure and data: Drop table table_name;--Delete the structure and data of the tables

Turn on the display operation time switch: Set timming on;

The NVL function handles null values: NVL (field name, value), if the field itself value is NULL, then change its new value to the one after the comma, if not null, do nothing.

The LIKE keyword after the WHERE statement is used to find and match a field that matches the criteria:

% represents 0 to more characters

Underline _ denotes any single word

In keyword means picking from a range

The order BY statement is the default ascending (ASC), and if you want to sort in descending order, you can:

SELECT * * * * FROM table_name where xxx ORDER BY colum_name desc;

Alias sort select SSS as xx from XXX and the like

Data grouping function: Max,min,avg,sum,count

Subquery: A SELECT statement followed by the Where field of the SELECT statement, specifically analyzed.

Group queries

Paging Query

Continue tomorrow.

Learning Notes for beginner Oracle and SQL learners. Hanshunping-Play with Oracle.