[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 430MOD () 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)