ORACLE: query (see) Table's primary key, foreign key, uniqueness constraint, and index

ORACLE: Enquiry ( See ) table primary key, FOREIGN key, uniqueness constraint, and index

1. Find all indexes of the table (including index name, type, constituent column)
Select T.*,i.index_type from User_ind_columns t,user_indexes i wheret.index_name = i.index_name and t.table_name = i.table _name and T.table_name= table name

2. lookup table's primary key (including name, constituent column):
Select cu.* from User_cons_columns cu, user_constraints au wherecu.constraint_name = au.constraint_name and Au.constraint _type = ' P ' andau.table_name = table name

3, find the uniqueness of the table constraints (including the name, the constituent column):
Select column_name from User_cons_columns cu, user_constraints au wherecu.constraint_name = au.constraint_name and Au.con Straint_type = ' U ' andau.table_name = table name

4. Find the foreign key of the table (including the name, the table name of the reference table and the corresponding key name, the following is divided into multi-step query):
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name= table name

Column names for foreign KEY constraints:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name

Column name of the key referencing the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name

5. Query all columns of the table and their properties:
Select t.*,c.comments from User_tab_columns t,user_col_comments c wheret.table_name = c.table_name and t.column_name = C.C Olumn_name and T.table_name= table name

[@[email protected]]

6. View all the triggers created by the user in Oracle

SELECT object_name from dba_objects WHERE object_type= ' TRIGGER ';

SELECT object_name from dba_objects WHERE object_type= ' TRIGGER ' and owner= ' SCOTT ';

SELECT NAME from User_source WHERE type= ' TRIGGER ' GROUP by NAME;

1, disable all trigger of table_name table

Altertable table_name Disable all triggers;

2. Enable all trigger of the TABLE_NAME table

Altertable table_name enable all triggers;

3. Disable the specified trigger

Altertrigger trigger_name Disable;

4. Enable the specified trigger

Altertrigger trigger_name enable;

Oracle of Get current date and date formatting

Oracle gets the current date and date format:
Get system Date: Sysdate

Formatted Date: To_char (sysdate, ' Yy/mm/dd HH24:MI:SS)
or to_date (sysdate, ' Yy/mm/dd HH24:MI:SS)
Formatted number: To_number

Note: To_char converts a date or number to a string
To_char (number, ' format ')
To_char (Salary, ' $99,999.99 ')
To_char (date, ' format ')

To_date Convert a string to a date type in the database to_date (char, ' format ')

To_number converts a string to a numeric to_number (char, ' format ')
　　

Note: In the format string, YYYY is a four-bit year, YY is the year after two bits, MM is the month, HH is a 12-hour system, HH24 is a 24-hour system, MI is the minute, SS is the second.

Example: Return to System date, 2016-09-26 14:23:31
Select Sysdate from dual;
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from dual;

　　
Select To_date (' 2009-12-25 14:23:31 ', ' yyyy-mm-dd,hh24:mi:ss ') fromdual
And if you write on the writing:
Select To_date (' 2009-12-25 14:23:31 ', ' Yyyy-mm-dd,hh:mi:ss ') from dual
will be error, because the hour HH is 12 binary, 14 is illegal input, can not match.

Output $10,000,00:
Select To_char (1000000, ' $99,999,99 ') from dual;
Output rmb10,000,00:
Select To_char (1000000, ' l99,999,99 ') from dual;
Output 1000000.12:
Select Trunc (To_number (' 1000000.123 '), 2) from dual;
Select To_number (' 1000000.123 ') from dual;

Format of conversion:

Representing year: Y represents the last of the years,
YY represents the last 2 digits of the year,
YYY represents the last 3 digits of the year,
YYYY 4-digit year

Indicates: mm with 2 digits for month,
Mon in shorthand form, such as November or Nov,
Month with full name, like November or November.

Represents day: DD indicates days of the month,
DDD indicates the day ordinal of the year
Dy when in a few days, shorthand, such as Friday or Fri,
Day when in days, full name, such as Friday or Friday

Represents hour: HH 2 digits represent hours 12 decimal,
Hh24 2-digit hour 24 hour

Represents minute: Mi 2 digits for minutes

Represents second: SS 2 digits for seconds 60 binary

Indicates quarterly: Q A number represents quarter (1-4)

There is also WW used to indicate the week of the year when W is used to denote the month ordinal.

Time range under 24-hour system: 00:00:00-23:59:59
Time range under 12-hour system: 1:00:00-12:59:59

digital format:  9  represents a number  
               0  force display 0 
               $  Place a $ character  
               L  Place a floating local currency symbol  
               .  display decimal  
               ,  Displays the thousand indicator

Add:
Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual;
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual;
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual;
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual;
Time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual;
Time interval multiplied by a number
Select Sysdate,sysdate-8*interval ' 7 ' hour from dual;

Meaning explanation:
Dual Pseudo-column
Dual is an actual table in Oracle that can be read by any user, often in a SELECT statement block without a target table.
Different systems may return dates that are not in the same format.
Returns the currently connected User: Select User fromdual;

ORACLE: query (see) Table's primary key, foreign key, uniqueness constraint, and index