Oracle Database elementary learning, oracle Database elementary

Oracle Database elementary learning, oracle Database elementary

• SQL query statement on the first day

CMD--SQLPLUS ---- account soctt password tiger, enter the SQL database after the input is complete, it shows that the connection is successful.

 

After the connection is successful, we first set the basic interface,

-- Set the number of characters displayed in each column
Set LINES 1000;
Set pages 100;
-- Clear screen
Host cls

 

--------------------- What information is contained in the SQL statement

DQL
Database Query Language
SELECT
DML
Database Operation Language
INSERT DELETE UPDATE
DDL
Database Definition Language
CREATE ALTER
DCL
Database Control Language
GRANT

 

Today we are mainly studying DQL, a database query language.

 

By default, there are four tables in the Orale database.

-- Query a table
SELECT TABLE_NAME FROM USER_TABLES;
DEPT
EMP
SALGRADE
BONUS
-- Query the columns corresponding to the table
Enter desc dept;
DEPTNO
DNAME
LOC
Input desc emp;
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
Enter desc salgrade;
GRADE
LOSAL
HISAL

 

 

/*
Query of DQL Columns
*/

SELECT column name FROM table name WHERE Condition

 

 

-- Simple query Column
Select ename from emp; (find the ename column in the EMP table)
Select ename, SAL, job from emp; (search for the ename, SAL, and JOB columns in the EMP table)
SELECT * from emp; (find all columns in the EMP table)
* Wildcard: simple and inefficient

 

 

 

-- Set aliases in three ways
1. select ename "employee name", SAL "salary" from emp;
2. select ename as "employee name", sal as "salary" from emp;
3. select ename employee name, SAL "salary" from emp;

The third method is recommended. For more information, see.

-- Remove duplicate Columns
SELECTDISTINCTJob from emp;

 

/*
DQL-conditional Query
*/

Sometimes we need to query the data in the database based on certain conditions. The following describes the condition query in DQL.

In SQL, query statements use the WHERE keyword.

-- Equivalent Query
Select ename, sal from e where sal = 3000;
Select ename, sal from emp where ename = 'Smith ';
Select ename, sal from emp where job = 'salesman'

-- Non-equivalent Query
Select ename, sal from emp where sal> 3000;
Select ename, sal from emp where sal <3000;
Select ename, sal from e where sal <= 3000;
Select ename, sal from emp where sal> = 3000;
Select ename, sal from emp where sal! = 3000;
Select ename, sal from emp where sal <> 3000;
Select ename, sal from emp where job <> 'salesman ';

<> The symbol also means a non-equal sign. Other symbols mean the same as JAVA.

-- Interval
Select ename, SAL FROM EMP WHERE SALBETWEEN800AND1600;

-- Null value: null
Select ename, comm from emp where comm is null;
Select ename, comm from emp where comm is not null;
-- Select the required
Select ename, sal from emp where ename in ('Smith ', 'Scott', 'abcd ');
Select ename, sal from emp where ename not in ('Smith ', 'Scott', 'abcd ');
Select ename from emp where job in ('salesman', 'cler ');
-- Fuzzy query
-- % Represents the number of arbitrary characters
Select ename from emp where ename like 'C % '; (data starting with C)
Select ename from emp where ename like '% s'; (Data ending with S)
Select ename from emp where ename like's % '; (data starting with S)
Select ename from emp where ename like '% S %'; (data containing S at any position in the data)
-- _ Represents a random character
Select ename from emp where ename like '_ A % ';
Select ename from emp where ename like '_ A % ';
Select ename from emp where ename like '% __';
-- Escape Character
Select ename from emp where ename like '% \ %' ESCAPE '\';
-- Sort
-- ASC (default) Ascending desc descending
SELECT * from emp order by sal;
SELECT * from emp order by sal asc;
SELECTX * from emp order by sal desc;

SELECT * from emp order by hiredate;
SELECT * from emp order by ename;
SELECT * from emp order by job;

SELECT * from emp order by job, EMPNO;
SELECT * from emp order by deptno, job desc, EMPNO;

SELECT * from emp where ename like '% A %' order by sal;

 

-- Function, similar to the method in java
Select ename | 'annual salary is' | SAL * 12 from emp;
Select ename, (SAL + COMM) * 12 from emp;

SELECT * from emp order by comm;
COMM = NULL? 0: COMM;

-- NVL (expr1, expr2)
-- If expr1 is NULL, expr2 is returned. If expr1 is not NULL, expr1 is returned.

-- Single row Function
-- One in and one out

/*
Single-row functions in DQL-Numbers
(JAVA) ABS CEIL FLOOR ROUND SQRT POW MOD
*/
Select sal/1000, CEIL (SAL/1000) rounded up, FLOOR (SAL/1000) rounded down from emp;
Select sal/1000, ROUND (SAL/1000) Rounding from emp;
Select sal/1000, TRUNC (SAL/1000), TRUNC (SAL/), TRUNC (SAL/10,-2) from emp;
Select mod (SAL, 8000) from emp;

/*
Single-row function of DQL-string
(JAVA) TRIM, REPLACE, SPLIT, SUBSTRING, TOUPPERCASE, TOLOWERCASE, CONCAT, INDEXOF, LIX, CHARAT
*/
Select concat ('employee name: ', ENAME),' -- AAAA ') from emp;
Select ename, UPPER (ENAME), LOWER (ENAME), INITCAP (ENAME) from emp;
Select ltrim ('a B '), RTRIM ('a B'), TRIM ('a B ') FROM EMP;
Select ename, LPAD (ENAME, 10, '#'), RPAD (ENAME, 10, '&') from emp;
Select ename, INSTR (ENAME, 'A') from emp;
Select ename, LENGTH (ENAME) from emp;

/*
Single-row functions in DQL-Date
(Java)
*/
Select sysdate from dual;

Select sysdate + 13 from dual;
SELECT ADD_MONTHS (SYSDATE, 12) from dual;

SELECT SYSDATE-HIREDATE from emp;
SELECT MONTHS_BETWEEN (SYSDATE, HIREDATE) from emp;

SELECT LAST_DAY (HIREDATE) from emp;

SELECT NEXT_DAY (SYSDATE, 'tues') from dual;

/*
Single-row functions in DQL-conversion functions
*/
-- Convert a date to a string
SELECT TO_CHAR (SYSDATE, 'yyyy-MM-DD HH24: MI: ss') from dual;
SELECT TO_CHAR (SYSDATE, 'mon') from dual;
SELECT TO_CHAR (SYSDATE, 'dy ') from dual;
-- Convert digits into strings
SELECT TO_CHAR (1234.5678, '192. 99999 ') from dual;
SELECT TO_CHAR (1234.5678, '192. 00000 ') from dual;
SELECT TO_CHAR (1234.5678, '00. 00') from dual;
SELECT TO_CHAR (1234.5678, '99. 99') from dual;
SELECT TO_CHAR (1234.5678, '2014. 00') from dual;
SELECT TO_CHAR (1234.5678, '192. 99') from dual;

SELECT TO_CHAR (1234.5678, '$00000.00000') from dual;
SELECT TO_CHAR (1234.5678, 'l00000. 00000 ') from dual;

SELECT TO_CHAR (1234567890, '$000,000,000,000.00') from dual;

-- Opposite
SELECT TO_DATE ('1970-11-11 ', 'yyyy-MM-DD') from dual;

SELECT TO_NUMBER ('192. 123', '192. 123') from dual;
SELECT TO_NUMBER ('192. 123', '192. 123') from dual;

 

/*
Single-row functions in DQL-Other Functions
*/

-- Multi-row (Group) Function
-- No matter how many entries are in, one
Select max (SAL), MIN (SAL), AVG (SAL) from emp;
Select count (SAL), SUM (SAL) from emp;

Select max (ENAME), MIN (ENAME) from emp;
-- AVG and sum can only calculate pure numbers.
Select avg (ENAME), SUM (ENAME) from emp;

 

-- Processing of null values
Select min (COMM), MAX (COMM) from emp;
Select count (COMM) from emp;
Select sum (COMM) from emp;
Select avg (COMM) from emp;

 

 

Some exercises:

-- 1. Find all employees who are employed for the last 3rd days of each month.
Select ename, hiredate from emp where hiredate = LAST_DAY (HIREDATE)-2;

-- 2. Find the employees who were employed earlier than 12 years ago.

Select ename, hiredate from emp where MONTHS_BETWEEN (SYSDATE, HIREDATE)/12> 13;

-- 3. display the names of all employees in uppercase.
Select initcap (ENAME) from emp;

-- 4. display the name of an employee with exactly five characters.
Select ename from emp where length (ENAME) = 5;

-- 5. display the names of employees without "R"

Select ename from emp where ename not like '% R % ';

-- 6. display the first three characters of all employees' names.

Select substr (ENAME, 1, 3) from emp;

-- 7. display the names of all employees and use a to replace all ""

Select replace (ENAME, 'A', 'A') ename from emp;

-- 8. display the name and employment date of an employee who has served for 10 years .:

Select ename, hiredate from emp where MONTHS_BETWEEN (SYSDATE, HIREDATE)/12> 10;

-- 9. display details of employees, sorted by name.

SELECT * from emp ord;

-- 10. display the name and date of employment of the employee, and rank the oldest employee at the beginning based on the service life.

Select ename, hiredate from emp order by hiredate asc;

-- 11. display the names, jobs, and salaries of all employees in descending order of work. If the jobs are the same, they are sorted by salary.

Select ename, JOB, sal from emp order by job desc, sal asc;

-- 12. display the names of all employees, the year and month of joining the company, sorted by the month where the employment date is located. If the month is the same, the employees of the earliest year are placed at the top.

Select ename, TO_CHAR (HIREDATE, 'Mm-yyyy') from emp order by TO_CHAR (HIREDATE, 'mm') DESC, TO_CHAR (HIREDATE, 'yyyy') ASC;

-- 14. Find all employees who were employed in April February (in any year.
Select ename, hiredate from emp where TO_CHAR (HIREDATE, 'mm') = 2;

-- 15. For each employee, the number of days of joining the company is displayed.

Select ename, ROUND (SYSDATE-HIREDATE) Day from emp;

-- 16. display the names of all employees whose names contain "A" at any position in the Name field.

Select ename from emp where ename like '% A % ';