Oracle Study Notes series ------ usage of basic functions of oracle basic operations, oracleexists usage

Oracle Study Notes series ------ usage of basic functions of oracle basic operations, oracleexists usage

 

-- CREATE an accout account table create table account (id NUMBER (9) not null, recommender_id NUMBER (9), login_name VARCHAR2 (30) not null, login_passwd VARCHAR2 (8) not null, status CHAR (1) not null, create_date date default sysdate, pause_date DATE, close_date DATE, real_name VARCHAR2 (20) not null, idcard_no CHAR (18) not null, birthdate DATE, gender CHAR (1) not null, occupation VARCHAR2 (50), telephone VARCHAR2 (15) not null, email VARCHAR2 (50), mailaddress VARCHAR2 (50), zipcode CHAR (6 ), qq VARCHAR2 (15), last_login_time DATE, last_login_ip VARCHAR2 (15 ));

 

-- RENAME the table account TO t_account;

 

-- ADD a column of desc t_account; alter table t_account ADD (bak VARCHAR2 (50); desc t_account;

 

-- MODIFY the DEFAULT value alter table t_account MODIFY (bak VARCHAR2 (40) DEFAULT 'login'); desc t_account;

 

-- Delete alter table t_account DROP (bak); desc t_account;

 

-- Insert into t_account (id, login_name, login_passwd, status, create_date, real_name, gender, idcard_no, telephone) VALUES (1, 'souvc ', '123 ', '1', TO_DATE ('1970-01-28 ', 'yyyy-mm-dd'), 'daliu', 'F', '123', '123 ');

SELECT * FROM t_account;

 

 

-- UPDATE table data UPDATE t_accountSET login_passwd = '000000' WHERE id = 1; SELECT * FROM t_account;

 

 

-- DELETE table data delete from t_accountWHERE id = 1; SELECT * FROM t_account;

 

 

-- "|" Is a connection string in the database, which is equivalent to "+" in java. -- note that this is different from "|" in java. -- Example: -- "hello" + "world" => "helloworld" in java -- 'helloworld' in DB | 'World' => 'helloworld' -- CONCAT ('Hello "in oracle ', 'World') => 'helloworld' -- 'think' | 'in' | 'java' CONCAT ('think', 'in '), 'java ');

 

-- Quick Start SELECTSELECT * FROM t_account; SELECT * FROM emp_souvc; -- specify the SELECT id, status FROM t_account for certain columns in the query table; -- only query the SELECT id of a specific column, job FROM emp_souvc; -- virtual table -- DUAL: virtual table, there is no such table, only to meet the -- SELECT syntax requirements. -- We often use virtual tables to test the expression results. -- In the database, we want to test the results of an expression only by using the SELECT statement. SELECT 'hello' | 'World' from dualselect id | ':' | job FROM emp_souvc; select concat (id, ':'), job) FROM emp_souvc;

 

 

-- LENGTH function SELECT id, LENGTH (job) FROM emp_souvc; desc emp_souvc;

 

 

-- UPPER, LOWER, INITCAP function select upper (id), LOWER (job), INITCAP (NAME) FROM emp_souvc;

 

-- TRIM, RTRIM function -- The from parameter can only be a single character. -- if there is no from and the preceding characters, the blank select trim ('E' from 'eeeeeliteeeee') is removed ') from dual; select rtrim ('eeeeliteeeee ', 'E') from dual;

 

-- LPAD, LPAD complement function -- display 20 characters, not many spaces on the left -- aaaaaselect lpad (id, 20, '$') FROM emp_souvc; select lpad ('aaaaaaaaaaa ', 5, '$') from dual; -- Purpose: Display 20 characters. If the sal value is not long enough, add several '$ ', select rpad ('aaaaaaaaaaa', 5, '$') from dual;

 

 

-- INSTR function select instr ('Doctor Who ', 'who', 9, 2) from dual;

 

 

 

-- Usage of the time function select systimestamp from dual; -- usage of the TO_DATE function SELECT TO_DATE ('2017-08-12 22:45:33 ', 'yyyy-mm-dd hh24: mi: ss ') from dual; -- TO_CHAR function usage -- double quotation marks are required for non-key characters or other characters in the date format string. SELECT TO_CHAR (sysdate, 'yyyy "mm" mm "dd" dd "hh: mi: ss') FROM dual; -- two dates can be subtracted, the difference is the number of days. SELECT sysdate-BIRTH, idFROM emp_souvc; -- MONTHS_BETWEEN function SELECT id, MONTHS_BETWEEN (SYSDATE, BIRTH) FROM emp_souvc; -- NEXT_DAY function -- get Week 4 closest to the current time, if the given current time happens to be Week 4, it indicates Week 4 SELECT NEXT_DAY (SYSDATE, 5) FROM dual;

 

 

-- Function for processing NULL values -- no value can be equal to nullSELECT * FROM emp_souvcWHERE gender is not null; -- NVL function: replace NULL with the specified SELECT name, NVL (gender, 'M') FROM emp_souvc; SELECT id, job, NVL (gender, 0) FROM emp_souvc;

 

Similar to java functions:

public Object nvl(Object e1,Object e2){if(e1==null){return e2;}else{return e1;}}

 

 

SELECT id, SALARY + NVL (SALARY, 0) FROM emp_souvc; select salary, NVL2 (SALARY, 'bonus ', 'no bonus') FROM emp_souvc;