Some SQL commands commonly used by Oracle

View system Current time HH24 24-hour MI is the right minute

Select To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from dual

HH non-MM case-insensitive date mm system is considered a month

Select To_char (sysdate, ' Yyyy-mm-dd HH:MM:SS ') from dual

Reports the system date in timestamp data type format.

Select Systimestamp from dual;

Querying the currently logged on user

Select username from user_users

Show user

View table structure (field data type)

Desc user_users

Querying tables owned by the current user

Select table_name from User_tables

Forgot Password Login

Open the cmd window and enter Sqlplus/as SYSDBA

Remote connection

Gb/[email PROTECTED]/ORCL

Create a sequence

Create sequence sequence name

Start with start sequence

Increment by value per increment

Sequence name. Nextval (Next sequence)

Sequence name. Currval (current sequence)

Create a reply temp table data

Create global temporary table as another table data on commit preserve rows; (You can copy data from the original table, which is what is commonly used.) ）

Add a field comment (Table note)

Comment on column tmp_xian.id is ' primary key '

Comment on column tmp_xian.name is ' Maximum user name '

COMMENT on table TABLENAME is ' user table ';

VARCHAR2 Maximum length 4000bite char (2000bite)

View the first 500 rows of data

SELECT * FROM T_pub_download_detail where rownum<500

COUNT (*) and COUNT (1) Efficiency results are the same as count (name) statistic Name column is not empty record

Select COUNT (*) from table; COUNT (1)

Date datetime (accurate to seconds) timestamp (accurate to fractional seconds)

Union Table link (filter duplicate records) union ALL (simple 2 tables result merge) union ALL high efficiency

LTRIM (String): Remove the space to the left of the string. 　　RTRIM (String): Removes the space to the right of the string. 　　TRIM (String): Removes the spaces at the beginning and end of the string. ALLTRIM (String): Remove the space between the left and right sides of the string.

This article is from the "fairy height of the cold" blog, please be sure to keep this source http://xian521.blog.51cto.com/9240575/1585677

Some SQL commands commonly used by Oracle