Shulou(Shulou.com)06/01 Report--

Oracle Series I: Simple SQL and Single-Line Functions

[1] EMP table content query

SQL> SELECT * FROM emp;

Error, the reason is that the table is not found, because the table is the SCOTT user table, so the query should be added scott.emp can be

[2] Display the current user

SQL> show user

[3] View all tables of the current user

SQL> SELECT * FROM tab;

[4] To execute the previous SQL statement repeatedly, enter "/" under sqlplus command line.

[5] Query the structure of a table, such as dept table

SQL> desc dept

[6] Query the employee's number, name, and job in the employee table

SQL> SELECT empno,ename,job FROM emp;

[7] You can take aliases for column names. Under Linux, Oracle will turn English aliases into caps if they are not enclosed in double quotes.

SQL> SELECT empno number,ename name,job jobs FROM emp;

[8] Query all jobs

SQL> SELECT DISTINCT job FROM emp; job may be duplicated, plus DISTINCT keyword

[9] If the output of results is required according to the following format, such as

NO:7469,Name:SMITH,Job:CLERK

SQL> SELECT 'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;

[10] Require the name and annual salary of each employee

SQL> SELECT NAME,sal*12 income FROM emp; where the annual salary is best identified with an alias, you can understand it at a glance

[11] Check the information of employees who can get bonus every month

SQL> SELECT * FROM emp WHERE comm is NOT NULL;

[12] Information on employees who require a basic salary greater than 1500 and can receive bonuses

SQL> SELECT * FROM emp WHERE sal>1500 AND comm is NOT NULL;

[13] Query the employee information whose basic salary is not greater than 1500 and who cannot receive bonus-----------If yes or yes, use OR

SQL> SELECT * FROM emp WHERE NOT (sal>1500 OR comm is NOT NULL);

or

SQL>SELECT * FROM emp WHEREM salselect * from emp where to_char(hiredate,'yyyy')=1981;

Oracle is sensitive to size, so names should be case-sensitive when querying.

[16] It is required to query the employee information whose employee number is not 7369 or 7499.

SQL> SELECT * FROM emp WHERE empno NOT IN(7369,7499);

SQL> select * from emp where empno != all(7369，7499)

[17] Like statements in SQL should pay attention to wildcards % and_

SQL> SELECT * FROM emp WHERE hiredate LIKE '%81%';

[18] To view employee information for employee numbers other than 7369, use or!=

SQL> SELECT * FROM emp WHERE empno7369;

[19] Requires sorting employee salaries from low to high, ascending default (ASC), descending (DESC)

SQL> SELECT * FROM emp ORDER BY sal;

[20] View employee information with department No. 10. Query information is arranged from high to low according to salary, or from early to late according to employment date if salary is equal.

SQL> SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC;

In the database system, the biggest difference between each database is in the support of the function. The single-line function is the simplest function. The single-line function is divided into

Character function: Accepts character input and returns characters or numbers

2. Numerical function: accepts numerical input and returns numerical value

3. Date function: operate on date data

Conversion functions: convert one data type to another

5. General function: NVL, DECODE function

Character function:

[1] Case conversion UPPER and LOWER

SQL> SELECT UPPER('smith') FROM dual;

[2] Capitalize employee name, INITCAP

SQL> SELECT INITCAP(ename) FROM emp;

There is a connection function CONCAT in the character function, but it is not as good as|| Easy to use, there are some string processing functions

String truncation: substr()

String length: length()

Content replacement: replace()

Find character: instr(ename,'A')=1(name with first letter A)

SQL> SELECT substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;

Note here that in Oracle string interception from 0 and from 1 are the same, beware of interview questions

[3] Required to display the names of all employees and the last 3 characters of their names

SQL> SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;

The above operation seems to be more troublesome, substr() function can be intercepted backwards

SQL> SELECT ename,SUBSTR(ename,-3,3) FROM emp;

Numerical function:

Round to ROUND()

2. Truncated decimal places: TRUNC()

3. Remainder (modulo): MOD

[1] Keep 1 decimal place,(if it is-2, round the whole number to 800

SQL> SELECT ROUND(783.56,1) FROM dual;

The result is------783.6

[2] Truncated decimal places,

SQL> SELECT ROUND(783.56) FROM dual;

The result is------783

[3] Use MOD() function for remainder operation

SQL> SELECT MOD(10,3) FROM dual;

The result is--------1

Date function:

1. Date-Number = Date

Date + Number = Date

Date-Date = Number (days)

Find the current date

SQL> SELECT SYSDATE FROM dual;

Oracle provides support for the following date functions:

MONTHS_BETWEEN (): Find the number of months in a given date range

select months_between(to_date('2015-10','yyyy-mm'),to_date('1997-4','yyyy-mm') )from dual

ADD_MONTHS (): Add the specified number of months to the specified date

select add_months(to_date('2015-10-1','yyyy-mm-dd'),1) from dual

3. Find the next date NEXT_DAY(): Next today's date

select next_day(sysdate,'Wednesday') from dual

LAST_DAY (): Find the last day of a given date

select last_day(to_date('2015-10','yyyy-mm'))from dual

[2] Find employee numbers, names, and months for all employees from date of hire to today.

SQL> SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

Conversion function:

TO_CHAR(): converts a date or number to a string

TO_NUMBER(): Convert a string to a number (the number in the string)

TO_DATE(): Convert a string to a date

[1] Separate the year, month and day, and specify the wildcard to be split, y-mm-dd

SQL> SELECT empno,ename,TO_CHAR(hiredate,'yyyy') datetime FROM emp

Format salary numbers such that '$99,999' represents USD and 'L99,999' represents local currency

SQL> SELECT empno,ename,TO_CHAR(sal,'L99,999) salary FROM emp;

[3] TO_NUMBER() verification

SQL> SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM DUAL;

【4】TO_DATE

SQL> SELECT TO_DATE('2011-7-11','yyyy-mm-dd') FROM DUAL;

Common Functions:

[1] Find out the annual salary of each employee (should include bonus)

SQL> SELECT empno,ename,(sal+comm)*12 FROM emp;

Since there is NULL in comm,NULL value is still NULL after calculation, which is correct as follows:

SQL> SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;

NVL can be understood as converting NULL values to specific content, here 0

[2] DECODE() function, similar to IF... ELSEIF... ELSE syntax is as follows:

DECODE(field, select1, result1 [, select2, result2,..., Default])

Verify the DECODE() function

SQL>select empno,ename,hiredate,DECODE

(job,'CLERK',' Salesperson','SALESMAN',' Salesperson','Manager',' Manager','ANALYST',' Analyst','PRESENT',' CEO') Position FROM emp;

SQL simple statement exercises:

Find employees whose commissions are higher than 60% of salary

SQL> SELECT * FROM emp WHERE comm>sal*0.6

Find details of all managers in department 10 and all clerks in department 20

SQL> SELECT * FROM emp

WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');

Find information on all employees who are neither managers nor clerks but whose salaries are greater than or equal to 2000

SQL> SELECT * FROM emp

WHERE job NOT IN('MANAGER','CLERK') AND sal >= 2000;

[4] Find out the different jobs of employees with bonuses

SQL> SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

Find all employees hired on the third day from the end of each month

SQL> SELECT * FROM emp

WHERE LAST_DAY(hiredate)-2=hiredate;

Find employees hired 30 years ago

SQL> SELECT * FROM emp

WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 > 30;

[7] Display employee names of exactly 5 characters

SQL> SELECT ename FROM emp WHERE length(ename)=5;

[8] Show names of employees without "R"

SQL> SELECT ename FROM emp WHERE ename NOT LIKE '%R%';

[9] Display employee names and dates of employment, ranking oldest employees first

SQL> SELECT * FROM emp GROUP BY hiredate;

[10] Display the names of all employees, year and month of joining the company, sorted by month of employment date, sorted by year if month is the same

select ename, to_char(hiredate,'y') Year, to_char(hiredate,' mm') Month from emp order by Month, Year

Find employees hired in February

SQL> SELECT * FROM emp

WHERE TO_CHAR(hiredate,'mm')=2;

[12] Display all employees 'service years by year, month and day

SQL> SELECT ename,

TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)year,

TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month,

TRUNC(MOD(sysdate-hiredate,30)) day

FROM emp;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.