[SQL entry level] First day of SQL preliminary, first day of SQL entry level

Source: Internet
Author: User

[SQL entry level] First day of SQL preliminary, first day of SQL entry level

SQL statements are categorized into the following types:

  • DML: Data Manipulation Language
  • DDL: Data Definition Language
  • DCL: Data Control Language

1. Basic SQL-SELECT statements

 

Alias

① Directly write 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 after the field name;

③ Double quotation marks (this parameter is required when an alias consists of multiple words separated by spaces.) select last_name "my name", employee_id as id from empolyees;

 

 

Connector

① Concatenate columns, columns, and characters

② |

③ Can be used to 'composite 'Columns

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

 

 

Duplicate row (deduplication) distinct

Select distinct department_id from employees;

Note: The null value is also a single row.

Note: The characters and dates must be enclosed in single quotation marks (double quotation marks are used only when the alias mentioned above is used in SQL, and single quotation marks are used in other cases). The SQL language is case insensitive, however, the string enclosed by ''is case sensitive.

 

Ii. Filtering and sorting

 

Filter where

-- And or not logical AND/logical OR/logical No

-- [NOT] between and indicates the value in the interval, including the boundary equivalent to> = and <=

-- [NOT] IN indicates a discrete value. where department_id in (, 90) is equivalent to where department_id = 70 or department_id = 80 or department_id = 90

-- [NOT] LIKE fuzzy query

Percent sign % indicates 0... N characters;

Underline _ indicates a single character;

ESCAPE Character escape, where last_name like '% \ _ %' ESCAPE '\' (the escape character can be any character such as \ # $)

-- IS [NOT] NULL

-- Computing priority

Arithmetic Operators> connectors> comparison operators> is null, LIKE, IN> BETWEEN> NOT> AND> OR

Brackets can change the priority.

 

 

Sort

Order... DESC (reverse)/ASC (sequence, default)

Select last_name, department_id, salary * 12 annual_sal
From employees

-- Order by can be left in the where clause

-- Where department_id> 80

-- Multilevel sorting

-- Sort by field alias
Order by annul_sal, last_name desc

 

Iii. Single Row Functions

 

Character

① Case-sensitive control function: because the content enclosed by single quotes is case-sensitive, and sometimes the query does not care about case-sensitive, this type of function is useful at this time.

LOWER ('SQL course') → SQL Course all LOWER case

UPPER ('SQL course') → SQL Course capital

INITCAP ('SQL course') → SQL Course capital

② Character control functions:

CONCAT ('hello', 'World') → HelloWorld connection string

SUBSTR ('helloworld', 1st) → Hello intercepts 5 substrings starting from the third cursor position -- the subscript in Java starts from 0, and-1 indicates invalid data; in SQL, the subscript starts from 1. 0 indicates invalid data.

LENGTH ('helloworld') → 10 String LENGTH

INSTER ('helloworld', 'w') → position of 6 Characters in the string

LPAD (salary, 10, '*') → ***** 24000 the left alignment of the string, which is saved with 10 digits. If not, use * to complete the string on the left.

RPAD (salary, 10, '*') → 24000 ***** the right alignment of the string, which is saved with 10 digits. If not, use * to fill it up on the right.

TRIM ('H' FROM 'hellohworld') → elloHWorld removes the first and last characters of the original string FROM the specified character

REPLACE ('abcb', 'B', 'M') → amcdm replaces all the characters in the string with the specified one

 

 

Value

ROUND () Rounding

-- ROUND (435.45, 1) ROUND (435.45) ROUND (435.45,-1)
435.5 435 440

TRUNC () truncation

-- ROUND (435.45, 1) ROUND (435.45) ROUND (435.45,-1)
435.4 435 430

MOD () for remainder

-- MOD (1600,15)

10

 

 

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

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

Returns the number of days between two dates.

MONTHS_BETWEEN: number of months with different dates

ADD_MONTHS adds the number of months to a specified date

NEXT_DAY specifies the date of the next day of the week

LAST_DAY the last day of this month

ROUND date

TRUNC date Truncation

 

 

Data Type Conversion

① Implicit DATE interval → VARCHAR2 interval → NUMBER

Source data type Target Data Type
VARCHAR2/CHAR NUMBER
VARCHAR2/CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

-- Note the difference. in JAVA, the "+" symbol represents a string connection in string operations. In SQL, the "|" and "+" character represent a mathematical addition operation.

Select '12' + 2 from dual; -- Here '12' is implicitly converted to 12 of the NUMBER type

14

 

② Explicit

TO_CHAR

Select employee_id, to_char (hire_date, 'yyyy "year" mm "month" dd "day" ') -- The characters interspersed in the output date format must be included in ""
From employees
Where to_char (hire_date, 'yyyy/mm/dd') = '2017/07 '-- to_date ('2017/07', 'yyyy/mm/dd ')

TO_DATE

TO_NUMBER

Select to_char (1234567.89, '20140901') from dual; -- if the value is less than 999,999,999.99, do not add zero.

Select to_char (1234567.89, '20140901') from dual; -- Zero Filling for less than 000,000,999.99 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, 100') from dual; -- The 999,999.99 format must correspond

 

 

General

These functions apply to any data type and null values.

NVL (Expr1,Expr2) Is equivalent to expr1! = Null? Expr1: expr2

-- Commission_pct the value of the bonus rate field may be null. If it is null, it is replaced by 0.

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

-- Because department_id is of the NUMBER type and 'no departments' is of the strict character type, an error "Invalid NUMBER" is reported when the NVL function is called directly, which must be processed.

Select last_name, nvl (to_char (department_id), 'No department') from employees;

NVL2 (Expr1,Expr2,Expr3) Is 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 and iterated in turn.

 

 

Conditional expression (IF-THEN-ELSE logic)

① CASE expression

CASEExpr1WHENComparison_expr1THENReturn_expr1
[WHENComparison_expr2THENReturn_expr2
WHENComparison_exprnTHENReturn_exprn
ELSEElse_expr]

END

-- Query the information of employees whose department numbers are 10, 20, and 30. If the department number is 10, print
-- 1.1 times of work, Department 20, 1.2 times, Department 30, 1.3 times
Select employee_id, last_name, department_id,
Case department_id when 10 then salary * 1.1
When 20 then salary * 1.2
Else salary x 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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.