[SQL Entry level] first day SQL preliminary

The SQL statement is divided into the following three types:

• Dml:data manipulation Language Data Manipulation language
  1. INSERT
  2. UPDATE
  3. DELETE
  4. SELECT
• Ddl:data definition Language Data Definitions language
  1. CREATE TABLE
  2. ALTER TABLE
  3. DROP TABLE
  4. CREATE INDEX
  5. DROP INDEX
• Dcl:data Control Language Data Controls language
  1. Grant access Permissions
  2. Remove REVOKE access rights
  3. Commit TRANSACTION Processing
  4. ROLLBACK Transaction Fallback
  5. SavePoint to set the save point
  6. Lock locks A specific part of the database

First, the basic Sql-select statement

Alias

① directly behind the field name select Last_Name name,employee_id ID from empolyees;

② after the field name, add as select Last_Name as name,employee_id as ID from empolyees;

③ double quotes (required when aliases are made up of multiple words and separated by spaces) Select Last_Name "My Name", employee_id as ID from empolyees;

Connector characters

① concatenate columns and columns with characters

② by | |

③ can be used to ' synthesize ' columns

Select Last_name| | ' ' s job_id is ' | | job_id as details from employees

Repeat row (deduplication) distinct

Select distinct department_id from employees;

Note: A null value is also a single row

Note: Characters and dates must be enclosed in single quotation marks (double quotation marks are used in SQL only for aliases mentioned above, and single quotes in other cases), andSQL language is case insensitive, but ' strings enclosed are strictly case-sensitive ' .

Second, filtering and sequencing

Filter where

--  and OR not Logical AND /Logical OR/logic no

-- [not] between and represents the value within the interval, which contains the bounds equivalent to >= and <=

-- [not] in denotes discrete values, where department_id in (70,80,90) is equivalent to where department_id=70 or department_id=80 or de Partment_id=90

-- [not] like fuzzy query

Percent semicolon % denotes 0...N characters;

The underscore _ denotes a single character;

Escape character escape, where last_name like '%\_% ' escape ' \ ' (escape character can be any word such as \ # $ etc)

--is [not] null empty value

--Operation Priority

Arithmetic operators > Connectors > Comparators >is null,like,in>between>not>and>or

Parentheses can change precedence

Sort

ORDER by ... DESC(reverse order)/ASC(sequential, default)

Select Last_name,department_id,salary*12 annual_sal
From Employees

--order by can not be placed in the WHERE clause

--where department_id > 80

--Multilevel sorting

--Sort By field alias
ORDER by Annul_sal,last_name desc

Three, single-line function

Character

① Casing Control functions: Because the contents of the enclosed quotation marks are strictly case-sensitive, and sometimes the query does not care about case, such functions come in handy

LOWER (' SQL Course ') →sql Course all lowercase

UPPER (' SQL Course ') →sql Course all caps

Initcap (' SQL Course ') →sql Course Word first letter capitalized

② Character control functions:

CONCAT(' Hello ', ' world ') →helloworld connection string

SUBSTR(' HelloWorld ', 1,5) →hello intercept a substring of length 5 starting from the 1th subscript position --java the subscript starting from 0, 1 representing invalid data; Subscript starting at 1, 0 representing invalid data

Length (' HelloWorld ') →10 string lengths

inster(' HelloWorld ', ' W ') → 6 characters in the position of the string

lpad(salary , 10, ' * ') →*****24000 string left-aligned, with 10 bits to save, insufficient in the left end with *

Rpad(salary, 10, ' * ') →24000***** string right-aligned, with 10 bits to save, insufficient at the right end use * to complement

TRIM(' H ' from ' HELLOHWORLDH ') →ellohworld remove the original string the same character as the specified character

replace(' abcdb ', ' B ', ' m ') →AMCDM replaces all characters in the string with the same character as the specified character with another character

Numerical

ROUND () Rounding

--round (435.45, 1) ROUND (435.45) ROUND (5. 45,-1)
435.5 435 440

TRUNC () truncation

--round (435.45, 1) ROUND (435.45) ROUND (5. 45,-1)
435.4 435 430

MOD () Find redundancy

--mod (1600,15)

10

Date: Date type data in Oracle actually contains two values (date and time)

Add or subtract a number on a date the result is still a date

Two date subtracts the number of days between dates that return a date

months_between number of months with a two date difference

add_months add a number of months to the specified date

Next_day date of the next week of the specified date

Last_day last day of the month

ROUND Date Rounding

TRUNC Date Truncation

Data type conversions

① implicit date←→varchar2←→number

Source data type	Target data type
Varchar2/char	Number
Varchar2/char	DATE
Number	VARCHAR2
DATE	VARCHAR2

--Note the difference, in Java the + number in the string operation to represent the string connection, the string connection in SQL is the | |,+ number indicates the mathematical addition operation

Select ' 2 ' + from dual; --here ' 12 ' implicitly converted to number type

14

②-Explicit

To_char

Select employee_id,to_char(hire_date, ' yyyy', "mm", "dd")-- The characters interspersed here in the output date format are enclosed in "".
From Employees
where To_char(hire_date, ' yyyy/mm/dd ') = ' 1994/06/07 '-- to_date(' 1994/06/07 ', ' yyyy/mm/dd ')

To_date

To_number

Select To_char (1234567.89,' 999,999,999.99') from dual; --1,234,567.89 not enough to fill 0

Select To_char (1234567.89,' 000,000,999.99 ') from dual; --001,234,567.89 0 of insufficient digits

Select To_char (1234567.89, '$999,999,999.99 ') from dual; --$1,234,567.89 dollar sign

Select To_char (1234567.89, 'L999,999,999.99 ') from dual; --￥1,234,567.89 Local currency symbol

Select To_number ('￥1,234,567.89 ', 'L999,999,999.99 ') from dual;--1234567.89 format to correspond to

General

These functions are suitable for any data type and also for null values

NVL (expr1,expr2) is equivalent to EXPR1!=NULL?EXPR1:EXPR2

--commission_pct the value of the bonus rate field may be null, which is NULL instead of 0

Select employee_id,last_name,salary*12* (1+NVL (commission_pct,0)) annual_sal,commission_pct from Employees ;

--Because department_id is a number type, and ' No department ' is a strict character type, calling the NVL function directly to the "invalid numbers" error requires processing

Select Last_Name,nvl (to_char (department_id), ' no department ') from employees;

NVL2 (expr1,expr2,expr3) equivalent to EXPR1!=NULL?EXPR2:EXPR3

Select Last_Name,nvl2 (commission_pct,commission_pct + 0.015,0.01) from employees;

nullif (expr1,expr2) is equivalent to EXPR1==EXPR2?NULL:EXPR1

Select First_name,length (first_name) "Expr1",
Last_name,length (last_name) "Expr2",
nullif (Length (first_name), Length (last_name)) result
From Employees

COALESCE (expr1,expr2,...,exprn) If the first expression is null, the next expression is returned, followed by iteration

Conditional expressions (if-then-else logic)

①case-expression

Case Expr1  when COMPARISON_EXPR1  Then RETURN_EXPR1
[ when comparison_expr2 then return_expr2
when comparison_exprn  Then return_exprn
ELSE else_expr]

END

--Query the employee information for the department number 10,20,30, and if the department number is 10, print
-1.1 times times its work, department 20th, 1.2 times times, 30th department, 1.3 times Times
Select employee_id,last_name,department_id,
       case department_id when ten then salary*1.1
                                      When salary*1.2
                                      Else salary*1.3
                                      End Revised_salary
from Employees
where department_id in (10,20,30)

②decode function

Select employee_id,last_name,department_id,
Decode (department_id,10,salary*1.1,
20,salary*1.2,
salary*1.3) revised_salary
From Employees
where department_id in (10,20,30)

[SQL Entry level] first day SQL preliminary