Oracle Review (II)

Xi. Replace replacement
Format: (the original string, the character or string to find, the character or string to replace)
Select replace (' Hello World ', ' o ', ' a ') case-sensitive from dual;//substitution
Select E.empno,replace (e.ename, ' S ', ' J ') from EMP e;
12, the function of removing the space
Trim removes whitespace from string 2 edges
LTrim Remove the left space of the string
RTrim Remove the space to the right of the string

13. Date Type
To_date: (a function that converts a string into a date)
To_date (string to convert to date, convert format)
Select To_date (' 2017-04-20 14:30:12 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
To_char () (can convert a date or number to a string)
Select To_char (sysdate, ' Yyyy-mm-dd Hh24:mi:ss:SSSS ') from dual;
Select To_char (1234.5, ' $9999d9 ') from dual;

--Query today is how many days this year
Select To_char (sysdate, ' DDD ') from dual;
--Query How many days of the month
Select To_char (sysdate, ' DD ') from dual;
--Query the number of days of the week
Select To_char (sysdate, ' D ') from dual;

to_number--converts a character number to a number

Calculates the number of days between 2 dates
Select Trunc (sysdate-hiredate) from dual;
-5 years before the current date
Select Sysdate, Sysdate-interval ' 5 ' year from dual;
--date 5 months before the current date
Select Sysdate, Sysdate-interval ' 5 ' month from dual;

Months_between returns the number of months that differ between 2 dates

Add_months--Add a few months to the base of a date
Select Add_months (sysdate, 2) from dual;
Select Add_months (Sysdate,-2) from dual;
last_day--returns the last day of the month
Select To_char (Last_day (sysdate), ' Yyyy-mm-dd ') from dual;

14, the total number of query records
Select COUNT (*) from table name
Select COUNT (*) from EMP;
Select COUNT (1) from EMP;
Select COUNT (comm) from emp;//only counts comm! = NULL Records
1. Employees who seek maximum wages
Select Max (SAL) from the EMP where Sal is not null;
2: Data for minimum value
Select min (column name) from table name
3: Summation
Select SUM (column name) from table name
4: Averaging
Select AVG (column name) from table name
5. A field that is a numeric type must be a value-linked query
Select AVG (SAL), sum (SAL), Max (Sal), Min (sal) from EMP;

6: Group queries
--Query the average salary of each department (grouped by department)
Select Floor (avg. sal), Deptno from EMP Group by DEPTNO;
--Query the total salary and average wage of each department and the maximum wage
Select Floor avg (SAL) as average salary, sum (SAL) Total wages, Max (SAL), Deptno from EMP Group by DEPTNO;
--Query Each department, the total wages of each job and
Select SUM (SAL) Total wages, deptno, job from EMP Group by Deptno,job
--Check out each department, the total number of jobs per job
Select COUNT (1), deptno,job from EMP Group by Deptno,job;
--Query the number of employees in each department
Select COUNT (1), Deptno from EMP Group by DEPTNO;

The operation of the XV column
1. Add columns
ALTER table (table name) ADD (column name data type);
ALTER TABLE t_test add Tname nvarchar2 (20);

2. Change the data type of a column in a table
ALTER table (table name) MODIFY (column name data type);
ALTER TABLE t_test Modify Tname nvarchar2 (20);

3. Change the column name
ALTER table (table name) RENAME column (current column name) to (new column name); No brackets required
ALTER TABLE T_test rename COLUMN tname to Tname2

4. Delete a column
ALTER table (table name) DROP column (column name);
ALTER TABLE t_test drop COLUMN tname2

5. Rename the table name
ALTER table (current table name) RENAME to (new table name);
ALTER TABLE t_test Rename to T_test2;

Oracle Review (II)