Common Oracle syntax, commands, and functions

Document directory

• · Character Functions
• · Numeric Functions
• · Date functions
• · Conversion functions
• · General Functions

I. common syntax

-- 1. drop table name cascade constraint -- 2. when the content in the parent table is deleted, the content in the child table is also deleted on delete casecade -- 3. display the table structure desc table name -- 4. create user [username] identified by [password] -- 5. grant permissions to users. 1. 2... to user ex: grant create session to [username] -- at this time, you can only connect to the database grant connect, resource to [username] -- grant select, delete on scott. emp to [username] -- 6. revoke permission revoke select, delete on scott. emo from [username] -- 7. alter user [username] identified by [password] -- 8. the next time you log on, you will be prompted to change the password alter user [username] password expired -- 9. lock user alter user [username] account lock -- 10. unlock the Locked user alter user [username] account unlock

Ii. Common commands

-- 1. set linesize 100; -- 2. set pagesize 30. use NotePad to open em. SQL -- 4. run the code in file a to specify the file path @ d: a.txt @ a -- 5. connect to the database according to the user name and password. If you connect to the super Administrator (sys), add as sysdba; conn user name/password -- 6. show user of the current connection; -- 7. select * from tab; -- 8. view the table structure desc temp; -- 9. continue to execute the previous query statement/-- 10. clear screen clear scr;

Iii. Common functions-character Functions

-- 1. converts lowercase letters into uppercase letters, and dual is a virtual table select upper ('coolszy ') from dual; -- 2. convert uppercase letters to lowercase select lower ('kuka ') from dual; -- 3. uppercase letters of each word and lowercase letters of other locations select initcap ('kuka abc') from dual; -- 4. connection string, but no | select concat ('hello', 'World') from dual; -- 5. truncation string. The second parameter is truncated starting from the first letter (starting from 1. If it is a negative number, it starts from the end number ), the third parameter is the number of letters to be truncated select substr ('hello', 2, 3) from dual; -- 6. evaluate the string length select length ('hello') from dual; -- 7. replace string select replace ('hello', 'l', 'x') from dual;

· Numeric Functions

-- 1. rounding select round (789.536) from dual; select round (789.536, 2) from dual; select round (789.536,-1) from dual; -- 2. remove decimal places, but do not carry select trunc (789.536) from dual; select trunc (789.536, 2) from dual; select trunc (789.536,-2) from dual; -- 3. evaluate the remainder select Mod () from dual;

· Date functions

-- 1. returns the current date select sysdate from dual; -- 2. returns the number of months between two dates. Select months_between (sysdate, '16-January 1, June-09') from dual; -- 3. returns the date after the specified number of months plus select add_months (sysdate, 4) from dual; -- 4. returns the select next_day (sysdate, 'monday') from dual; -- 5. select last_day (sysdate) from dual on the last day of the month;

· Conversion functions

-- 1. to_charselect to_char (sysdate, 'yyyy') year, to_char (sysdate, 'mm'), to_char (sysdate, 'dd') from dual; select to_char (sysdate, 'yyyy-mm-dd') from dual; select to_char (sysdate, 'fmyyyy-mm-dd') from dual; -- cancel the 0 select to_char ('20140901 ', '000000') from dual; -- split money 9 indicates the format of select to_char ('000000', 'l99, 99,999 ') from dual; -- add the coin symbol -- 2. to_numberselect to_number ('000000') * to_number ('2') from dual; -- 3. to_dateselect to_date ('2017-07-04 ', 'yyyy-mm-dd') from dual;

· General Functions

-- 1. if it is null, use 0 instead of select nvl (null, 0) from dual; -- 2. similar to switch... case... select decode (, 'content is 1', 2, 'content is 2', 3, 'content is 3') from dual;