catalogue
2.SQL statement related concepts
3.SQL statement execution sequence
8. Display the structure of the table
9. Select a restricted data row
11. Limit the number of rows of records
1. Basic SELECT
Syntax:
SELECT [DISTINCT]{ * | Listing | expression...} FROM Table name;
2.SQL statement related concepts
(1) Keyword: a string reserved by SQL language. For example, SELECT and FROM are keywords.
(2) Statement: a complete SQL command. For example, SELECT * FROM dept is a statement.
(3) Clause: a partial SQL statement, which is usually composed of keywords and other syntax elements. For example, SELECT * is a clause and FROM table is also a clause.
3.SQL statement execution sequence
SELECT column name list ④
FROM table name ①
WHERE restrictions ②
GROUP BY column name ③
ORDER BY column name [desc | ASC] ⑤
4. Select columns
(1) Select all columns
Syntax:
select * from Table name; --Equivalent to select [Column name 1, Column name 2, Column name 3, ..., Listing N] from Table name; --Where column 1-N Are all fields in the table
example:
select * from t_emp; --Equivalent to select empno, ename, job, mgr, hiredate, sal, comm, deptno from t_emp;
(2) Select the specified column
Syntax:
select Column name 1, Column name 2, Column name 3, ..., Listing N from Table name;
example:
select empno, ename from t_emp;
5. Arithmetic operators
| operator | describe |
| + | plus |
| - | reduce |
| * | ride |
| / | except |
(1) Arithmetic operator priority:
Multiplication and division take precedence over addition and subtraction. Expressions with the same priority are calculated in order from left to right. Parentheses can improve the priority and make the description of the expression clearer
example:
select ename, job, sal, 200 + sal * 6 from t_emp; --Equivalent to select ename, job, sal, 200 + (sal * 6) from t_emp;
(2) NULL null
A NULL value is an invalid, unassigned, unknown, or unavailable value. NULL value is different from zero or space. The result of any arithmetic expression containing NULL value is NULL.
example:
select (null + 250), (null - 520), (null * 250), (null / 520) from dual; --The results are null
6. Column alias
(1) Function:
Rename the display title of the column
(2) Method of use
① Column name column alias;
② Column name as column alias;
(3) In the following three cases, double quotation marks need to be added around the column alias
① The column name contains spaces
② Case sensitivity is required in column names
③ The column name contains special characters
(4) Examples
select ename Employee name, sal as salary, deptno "! Department number", hiredate "Name " from t_emp;
7. De duplication of lines
(1) Grammar
select distinct Column name 1[, Column name 2,Column name 3, ...Listing N] from Table name;
(2) Examples
select distinct deptno from t_emp;
8. Display the structure of the table
(1) Grammar
[desc || describe] Table name;
(2) Examples
describe test;
9. Select a restricted data row
(1) Grammar
SELECT [DISTINCT] { * | column | expression , ...} FROM table [WHERE condition(s)];
(2) Comparison operator
| Operator | meaning |
| = | be equal to |
| > | greater than |
| >= | Greater than or equal to |
| < | less than |
| <= | Less than or equal to |
| <> | Not equal to |
(3) Special operator
| operator | meaning |
| BETWEEN...AND... | Judge whether the value to be compared is within a certain range |
| In (set list) | Determine whether the value to be compared is equal to any value in the set list |
| LIKE | Judge whether the value to be compared meets the partial match |
| IS NULL | Determine whether the value to be compared is NULL |
(4) Logical operator
| operator | meaning |
|---|---|
| AND | Logical and are used to connect multiple conditional expressions. If each condition is true, the entire expression is true; otherwise, it is false |
| OR | Logical or, used to connect multiple conditional expressions. As long as one condition is true, the whole expression is true, otherwise it is false |
| NOT | Logical non, used to negate an expression |
(5) Operator priority
| priority | Operation classification | Operator example |
|---|---|---|
| 1 | Arithmetic operator | *, \, +, - |
| 2 | concatenation operator | || |
| 3 | Comparison operator | =, <>, <, >, <=, >= |
| 4 | Special comparison operator | BETWEEN...AND..., IN, LIKE, IS NULL |
| 5 | Logical non | NOT |
| 6 | Logic and | AND |
| 7 | Logical or | OR |
(6) Examples
--display deptno All employee information for 10 select * from t_emp where deptno = 10; --Displays information about all employees with salaries between 2000 and 3000 select * from t_emp where sal between 2000 and 3000; --display deptno 10 or job The third is'A'All employees select * from t_emp where deptno = 10 or job like '__A%';
10.ORDER BY clause
(1) Grammar
SELECT [DISTINCT] { * | Listing | expression [alias] [,...]} FROM Table name [WHERE condition]
[ORDER BY {Listing|expression|Column alias|Column sequence number} [ASC|DESC],...];
(2) Examples
--Rank first by position and then by salary select * from t_emp order by job asc, sal desc;
11. Limit the number of rows of records
(1) Grammar
select Field list from data source limit [start, ] length;
Start: start from 0, indicating the line from which the record is displayed.
length indicates the number of output record lines.
(2) Query n pieces of data on page m in the data source
Syntax:
select Field list from data source limit (m - 1)*n, n;
example:
select * from t_emp limit 0, 5;--Display 5 rows of data on page 1 of the employee table select * from t_emp limit 5, 5;--Display 5 rows of data on page 2 of the employee table select * from t_emp limit 10, 5;--Display 5 rows of data on page 3 of the employee table