Basic operations of the database

First, simple query statements

format:Select *| specific column name as [ alias ] from table name

keywords to prevent duplicate query results:distinct (usage: Used in front of list)

Second, limited query

format:Select *| specific column name from table name where condition expression

Null: indicates that there are no values in the column

three.in keywords

Syntax:select * from table name where column name in ( value 1, Value 2 ...)

SELECT * from table name where column name not in (value 1, Value 2 ...) )

Four, fuzzy query

keywords:like

two wildcard characters: "%" can match any length of content

"_" can match a length of content

Usage:select * from table name where column name like ' _m% ';

V. Operators

not equal to:<> or ! =

Six, sort

Syntax:SELECT * column name from table name where conditional expression ORDER by sort 1, sort 2

ASC: Ascending, default sort mode

Desc: Descending

Seven, single-line function

7.1 Character Functions

A. converting lowercase letters to uppercase letters

function:Upper ()

usage:Select upper (' Smith ') from dual;

B. turning a string into a lowercase letter

function:lower ()

usage:Select lower (' HELLO world ') fromdual;

C. capitalize the first letter of a string

function:initcap ()

usage:select Initcap (' HELLO world ') fromdual;

D. connection of Strings

function:concat ()

usage:select concat (' Hello ', ' world ') fromdual;

E. interception of strings

function:substr ()

usage:select substr (' Hello ', 0,3) 3: Indicates the length of the Intercept

Reverse intercept:select Ename,substr (ename,-3) from EMP; retain the last three digits of all employee names

F. getting string lengths

function:length ()

usage:Select length (' Hello ') from dual;

G. substitution of strings

function:replace ()

usage:selectreplace (' Hello ', ' l ', ' X ') from dual;

7.2 Numeric Functions

A. rounding

function:round ()

Usage:

1. Toperform rounding operations , you can specify reserved decimal digits

Selectround (789.536) from dual; --->800

Selectround (789.436) from dual; --->789

Selectround (789.436,2) from dual; --->789.44

2. Roundingof integers can be done directly

Selectround (789.536,-3) from dual; --->1000

Selectround (789.536,-2) from dual; --->800

Selectround (789.536,-4) from dual; --->0

B. truncating decimal digits

function:trunc ()

Usage:

1, the value is truncated from the decimal points, only the integer part is preserved

Selecttrunc (789.536) from dual; --->789

2. Set the number of decimal digits reserved

Selecttrunc (789.536,2) from dual; --->789.53

Selecttrunc (789.536,-2) from dual; --->700

C. take-up (modulo)

function:mod ()

usage: Select mod (10,3) from dual; --->1

7.3 Date Function

A. keyword:sysdate

B. usage:selectsysdate from dual;

C. The remaining number of date functions

Months_between ()---> find the number of months for a given date range

Add_months ()---> add a specified number of months on the specified date to find out the date

Next_day ()---> from the start date, calculate a specific date for the next week

Last_day ()---> find the date of the last day of a given date

Example: Calculate the number of weeks: Use formula: current date - hire Date = days /7 = number of weeks

Select Empno,ename,round ((sysdate-hiredate)/7) from EMP;

find out the number of all employees, their names, and the number of months of entry

Selectempno,ename,round (Months_between (sysdate,hiredate)) from EMP;

query out the current date plus 4 months after the date

Select Add_months (sysdate,4) from dual;

query for the next given number of days

Select Next_day (sysdate, ' Monday ') from dual;

Query the last day of a given date, that is, the last day of the month of a given date

Select Last_day (sysdate) from dual;

7.4 conversion function

A. type

To_char ()-- convert to String

To_number ()-- convert to digital

To_date ()-- convert to date

B. examples: Querying employee numbers, names and hours of employment for all employees

1, according to the year, month, day to split, the first need to use the To_char () function, split the time to specify a split wildcard:

year -->y, year is four digits, so use yyyy to represent

month -->m, Month is two digits, so use mm to indicate

Day -->d, Day is two digits, so use dd to indicate

Select Empno,ename,to_char (hiredate, ' yyyy ') Year,to_char (hiredate, ' mm ') Month,to_char (hiredate, ' DD ') day from EMP;

2, using to_char () for the conversion function of the date display

Select Empno,ename,to_char (hiredate, ' yyyy-mm-dd ') from EMP;

or:select Empno,ename,to_char (hiredate, ' yyyy " years " MM " month " DD " Day ") from EMP;

3, remove the month before the 0, using the keyword:FM

Selectempno,ename,to_char (hiredate, ' fmyyyy-mm-dd ') from EMP;

4.use "," to separate every 3 digits of the wage

Select Empno,ename,to_char (Sal, ' 99,999 ') from EMP; placeholder for the thing that 9 represents when formatting

5. Two representations indicating the number of wages:

L--> represents the abbreviation for local and displays the amount in the native language

$--> represents the dollar

Select Empno,ename,to_char (Sal, ' $99,999 ') from EMP;

C.to_number (): A function that turns a string into a number

Selectto_number (' 123 ') + to_number (' 123 ') from dual;

D.to_date (): data that changes a string to date

Selectto_date (' 2012-09-12 ', ' YYYY-MM-DD ') from dual;

7.5 General Functions

A. turning a specified null value into the specified content

function:NVL ()

usage:selectempno,ename, (SAL+NVL (comm,0)) *12 from EMP;

B. Functions similar to if...elseif...else statements

function:decode ()

Syntax:decode (COL/EXPRESSION,SEARCH1,RESULT1[SEARCH2,RESULT2,......] [, default])

Description:col/expression--> column name or expression

Search1.search2......--> is the condition used for comparison

Result1,result2......--> is the return value

if col/expression and search I compare, the same words return result I , if not matched with col/expression , the default value is returned .

usage:Select decode (the "content is 1 ', 2, ' content is 2 ', 3, ' content is 3 ') from dual; ---> content is 1

Example: To find out the employee's number, name, date of employment and work, replace the work with Chinese

Select Empno,ename,decode (Job, ' clerk ', ' salesman ', ' salesman ', ' sales person ', ' manager ', ' manager ', ' Analyst ', ' analyst ', ' president ', ' President ') from EMP;

Basic operations of the database