Oracle Common command Daquan and experience

Learn to Organize

Oracle

1, set linesize 100; Set length
2, set pagesize 30; Set the number of displays per page
3. Em A.sql Open Notepad
4, @ A executes the code in file A, you can specify the path of the file @d:a.txt
5, conn user name/password to connect to the database based on user name and password if the connection Super Administrator (SYS) should be added as SYSDBA;
6, show user; Display the currently connected user
7. Select * from tab; Get all the tables under the current user
8, desc temp; View table Structure
9./Continue execution of the previous query statement
Clear SCR; Clear Screen

Character functions
10, select Upper (' Coolszy ') from dual; Convert lowercase letters to uppercase, dual to a virtual table
11, select lower (' KUKA ') from dual; Convert uppercase letters to lowercase
12, select Initcap (' Kuka ') from dual; Capitalize the first letter
13. Select Concat (' Hello ', ' world ') from dual; Connection string, but no | | Useful Select Concat (' Hello ', ' world ') from dual;
14, select substr (' Hello ', 1,3) from dual; Intercept string
15. Select Length (' Hello ') from dual; To find the string length
16. Select replace (' Hello ', ' l ', ' X ') from dual; Replace string
17, select substr (' Hello ', -3,3) from dual; Three post-intercept

numeric functions
18, select Round (789.536) from dual; Rounding, rounding out decimals
19, select Round (789.536,2) from dual; Keep Two decimal places
20, select Round (789.536,-1) from dual; Rounding an integer
21, select Trunc (789.536) from dual; Give away decimals, but not carry
22, select Trunc (789.536,2) from dual;
23, select Trunc (789.536,-2) from dual;
24. Select mod (10,3) from dual; Returns the result of 10%3

Date function
25, select Sysdate from dual; Returns the current date
26, select Months_between (sysdate, ' 1 June-June -08 ') from dual; Number of months between returns
27, select Add_months (sysdate,4) from dual; Add a month to the date
28, select Next_day (sysdate, ' Monday ') from dual; Ask for the next Monday
29, select Last_day (sysdate) from dual; Ask for the last day of the month
Conversion functions
30, select To_char (sysdate, ' yyyy ') Year,to_char (sysdate, ' mm '), To_char (sysdate, ' DD ') from dual;
31, select To_char (sysdate, ' Yyyy-mm-dd ') from dual;
32, select To_char (sysdate, ' Fmyyyy-mm-dd ') from dual; Cancel 0 in front of the day of the month
33, select To_char (' 20394 ', ' 99,999 ') from dual; Split Money 9 means format
34, select To_char (' 2034 ', ' l99,999 ') from dual; plus the coin symbol.
35, select To_number (' 123 ') *to_number (' 2 ') from dual;
36, select To_date (' 1988-07-04 ', ' YYYY-MM-DD ') from dual;

General functions
37, select NVL (null,0) from dual; If NULL, replace with 0
38, select Decode (1, 1, ' content is 1 ', 2, ' content is 2 ', 3, ' content is 3 ') from dual; Similar to Switch...case ...

Transaction processing
39, commit; Commit a transaction
40, rollback; Rolling back a transaction
41, select rownum from table; Show line numbers in front of a column
42. DROP TABLE name Cascade constraint
On delete Casecade the contents of the child table are also deleted when the contents of the parent table are deleted

43. DESC table name shows the structure of the table
44, create user [username] identified by [password] creating new users
45, Grant rights 1, permissions 2...to user to create user rights
Ex:grant create session to [Username] can only connect to the database at this time
Grant Connect,resource to [username] at this time the permissions can meet the requirements
46. Alter user [username] identified by [Password] Modify password
47. ALTER user [username] password expired prompt to change password at next logon
48. Alter user [USERNAME] account lock Lock User
49. Alter user [username] account Unlock unlock lock users
50. Grant Select,delete on scott.emp to [username] give Scott two permissions to the EMP table to the user
51. Revoke SELECT, delete on Scott.emo from [username] Reclaim permissions

In addition, more comprehensive

02.oracle function
1 Numeric type functions
1.01. Returns the absolute value. ABS ()
1.02. Returns a positive negative value. SIGN ()
1.03. Returns the larger minimum integer. ceil ()
1.04. Returns the smaller maximum integer. Floor ()
1.05. Returns the Y power of X. Power (x, y)
1.06. Returns the y power of the constant E. exp (y)
1.07. Returns the logarithm of the X-base Y. log (x, y)
1.08. Returns the logarithm of Y as the base of the constant E. ln (y)
1.09. Returns the remainder of x divided by Y. mod (x, y)
1.10. Returns the value after rounding. Round ()
1.11. Returns the value after interception. Trunc ()
1.12. Returns the square root of x. sqrt (x)
1.30. Trigonometric functions
2 Character type function
2.01. Returns the ASCII code of the character. ASCII ()
2.02. Returns the ASCII code x character. CHR (x)
2.03. Connect two strings. CONCAT ()
2.04. Turn the first letter of each word into uppercase. Initcap ()
2.05. Convert the entire string to lowercase. LOWER ()
2.06. Converts the entire string to uppercase. UPPER ()
2.07. Turn the first letter of each word into uppercase. Nls_initcap ()
2.08. Convert the entire string to lowercase. Nls_lower ()
2.09. Converts the entire string to uppercase. Nls_upper ()
2.10. Search for character positions in the string (full-width 1 characters). INSTR ()
2.11. Search for character positions in the string (full-width 2 characters). INSTRB ()
2.12. Returns the length of the string (full-width 1 characters). LENGTH ()
2.12. Returns the length of the string (full-width 2 characters). LENGTHB ()
2.13. Returns the length of the string (other). LENGTHC (). LENGTH2 (). LENGTH4 ()
2.14. Add characters to the left. Lpad ()
2.15. Add characters to the right. Rpad ()
2.16. Delete the left string. LTRIM ()
2.17. Delete the right string. RTRIM ()
2.18. Replace the substring. REPLACE ()
2.19. String speech representation. SOUNDEX ()
2.20. Intercept substring (full-width 1 characters). SUBSTR ()
2.21. Intercept substring (full-width 2 characters). SUBSTRB ()
2.22. Replace the sub-characters. TRANSLATE ()
2.23. Delete the left and right strings. TRIM ()
3rd-Period function
3.01. Returns the current date of the system. Sysdate
3.02. Returns the date after the specified number of months. Add_months ()
3.03. Returns the date of the last day of the month. Last_day ()
3.04. Returns the number of months between 2 dates. Months_between ()
3.05. Returns the time zone's corresponding times. New_time ()
3.06. The first day of the period after rounding. Round ()
3.07. Return the first day of the period in which the date is located. Trunc ()
3.08. Return to the date of the next day. Next_day ()
3.09. Extract data from the time date. Extract ()
3.10. Returns the date and time in the session. Localtimestamp
3.11. Returns the current date and time in the current session's time zone. Current_timestamp
3.12. Returns the current date in the current session's time zone. current_date
3.13. Return the database time zone settings. Dbtimezone
3.14. Returns the current session time zone. Sessiontimezone
3.29. Change date time value. INTERVAL
4 Conversion functions
4.01. Convert the string to a rowID value. Chartorowid ()
The 4.02.rowid value converts the string. Rowidtochar ()
4.03. String language character set conversion. CONVERT ()
The 4.04.16 binary is converted to binary. Hextoraw ()
4.05. binary conversion to 16 binary. Rawtohex ()
4.06. Convert a number or date to a string. To_char ()
4.07. The string is converted to a date type. To_date ()
4.08. The string is converted to a digital type. To_number ()
4.09. Turn the half angle into full width. To_multi_byte ()
4.10. Full-width conversion to half-width. To_single_byte ()
4.11. Character set name to ID.NLS_CHARSET_ID ()
4.12. Character set ID to name. Nls_charset_name ()
5 Clustering functions
5.01. Statistical averages. AVG ()
5.02. Statistics total values. SUM ()
5.03. Statistical standard error. STDDEV ()
5.04. Statistical variance. VARIANCE ()
5.05. Statistics the number of rows that are queried. COUNT ()
5.06. The maximum value of the statistics. MAX ()
5.07. Statistic minimum value. MIN ()
6 analysis functions
6.00.oracle analysis functions
6.01. Continuous summation analysis function. SUM (...) over (...)
6.02. Sort value analysis function. RANK () and Dense_rank ()
6.03. Sequence number analysis function after sorting. Row_number ()
6.04. Take the upper and lower data analysis functions. Lag () and lead ()
7 Other functions
7.01. Returns the data type, the byte length, and the internal storage location. DUMP ()
7.02. Returns the maximum value in the expression list. Greatest ()
7.03. Returns the minimum value in the expression list. Least ()
7.04. Assign a value to a null value. NVL (). NVL2 ()
7.05. Returns the database user name corresponding to the current session. User
7.06. Returns the user ID number corresponding to the current session. UID
7.07. Returns the current session context property. Userenv ()
7.08. Condition value. Decode ()
7.09. return null for equality. Nullif ()
7.10. Returns the first non-empty expression in the list. COALESCE ()
7.11. Returns the current line number. rownum
7.12. Specify an external binary file. Bfilename ()
7.13. Returns the size (in bytes) of x. Vsize (x)
7.14. Condition value. Case and then end
7.15. Generate a 32-bit random number. Sys_guid ()
7.16. Return the system data. Sys_context ()
7.17. Generate random values or string dbms_random
7.18. Get the host name and IP address in the Internet

The most functions I use are:

1.count (*), COUNT (distinct column name); Total statistics

2.to_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ');

3.to_date (date string, ' Yyyy-mm-dd hh24:mi:ss ');

4.sum (numeric expression);

5.NVL (expression, 0); If the expression is empty, assign a value of 0, otherwise it will not change

6.substr (string, start value, numeric value); Intercept string

6.1 InStr (String, query character, starting position) gets the position of a character or string

7.decode (condition, value 1,result1, value 2,result2,default); Similar to Switch...case ...

8.translate (String, the character to replace, the character to replace); Replace character

9.ceil (numeric); Returns a large minimum integer. For example: 2.2-3
10.floor (numeric); Returns the smaller maximum integer. For example: 2.2-2
11.round (numeric value, value before and after the decimal point); Returns the value after rounding. Example: Round (123.567,2)-123.57
12.trunc (numeric value, value before and after the decimal point); Returns the value after the intercept. Example: Trunc (123.567,2)-123.56

13.add_months (time, value); Get the year of a time such as: Add_months (sysdate,-3)--Returns the time of the last three months

Other:

Select Add_months (Last_day (sysdate) +1,-2) from dual; Get the first day of the month

Select Add_months (Last_day (sysdate), -1) from dual; Get the last day of last month

Another: Select Trunc (Add_months (Sysdate,-1), ' mm '), trunc (sysdate, ' mm ') from dual;

Note: 1. Two time subtraction is the day, if the second, it must be multiplied by 24*60*60.

2. Date and year of acquisition: To_date (To_char (sysdate, ' yyyy-mm-dd '), ' yyyy-mm-dd ');

3. You can use like '%139% ' to extract data for numeric data of a string

4.to_date (Translate (substr (' [08/September/2009:16:44:01+8000] ', 2, 19), ' September ', ' '), ' dd/mm/yyyy hh24:mi:ss '); Extracts and converts a string into a time format.

Querying data for columns in a table in Oracle: Note that the table name must be capitalized

Select column_id, column_name, Data_type, Data_length, Data_precision, Data_scale,nullable,data_default from User_tab_ columns WHERE table_name = ' ACC_NBR ' ORDER by column_id

Oracle Common command Daquan and experience