Oracle notes (4) simple query, restricted query, and data sorting

Oracle note (4) Structured Query Language (Structured Query Language): simple Query, limited Query, and data sorting 1. It is a database Query and programming Language, it is used to access data and query, update, and manage relational database systems. ANSI (American National Standards Institute) claims that SQL is the standard language for relational database management systems. Oracle databases have developed very well, mainly because Oracle is the world's first database product that uses SQL statements. SQL is powerful. In summary, it can be divided into the following groups: DML (Data Manipulation Language) Data operation Language, used to retrieve or modify Data Definition Language (DDL) Data Definition Language, used to define the Data structure, create, modify, or delete the database object DCL (Data Control Language) Data Control Language, A simple query for defining database permissions refers to querying all the data in a table. the syntax of a simple query is as follows: SELECT [DISTINCT] * | field [alias] [, field [alias] www.2cto.com FROM table name [alias]; example: Query all records of the dept table SELECT * FROM dept; example: SELECT empno, ename, sal FROM emp; example: SELECT job FROM emp; When I found that duplicate data exists in the queried job content, the main reason is that duplicate records are not eliminated, you can use DISTINCT to remove all duplicate content: select distinct job FROM emp; but for duplicate data, it means that the records of each column in a row are repeated, which is called repeated data. Example: query the name and position of each employee, select distinct ename, job FROM emp. You can also use the four Arithmetic Operators of each mathematics in the simple query operation. Example: the name, position, basic annual salary SELECT ename, job, sal * 12 FROM emp must be displayed for each employee. However, a "SAL * 12" column is displayed ", this is definitely a query column, but the column name is not convenient to browse, so you can create an alias. Www.2cto.com SELECT ename, job, sal * 12 income FROM emp; however, it is recommended that you do not use Chinese characters for aliases. As long as it is program development, you must avoid Chinese characters. Example: because the company has high welfare, 200 yuan of meal allowance and 100 yuan of fare subsidy are available every month. In this case, the annual salary of SELECT ename, job, (sal + 300) * 12 income FROM emp; example: at the end of each year, the company will pay more than one month's basic salary (SELECT ename, job, (sal + 300) * 12 + sal income FROM emp; you can also use "|" to connect the queried fields in a simple query. Example: Observe the use of "|" SELECT empno | ',' | ename FROM emp;

Because "," is an output string, it must be enclosed by "'". That is, "'" indicates a string in an SQL statement. Example: The current database is required to display in the following format: "employee ID: 7369 employee name: SMITH, basic salary: 800, position: CLERK !" Now it is equivalent to finding the empno, ename, sal, and job fields, and using "|" to connect. SELECT 'employee ID: '| empno |' the employee name is '| ename |'. The basic salary is '| sal | ', job title: '| job | '! 'Employee information FROM emp; be sure to remember that the alias content should not be included in '', but" '"should be used only for the content in the SELECT clause.

Ii. Limited query in the previous simple query, all records are displayed, but now you can filter the displayed records, this is a restricted Query Task. a where clause is added based on the previous syntax to specify the conditions. The syntax is as follows: SELECT [DISTINCT] * | field [alias] [, field [alias] www.2cto.com FROM table name [alias] [WHERE condition (S)]; you can add multiple conditions after the WHERE clause. The most common conditions are basic relational operations:>, >=, <, <= ,! = (<>), BETWEEN, AND, LIKE, IN, is null, AND, OR, NOT; 1. relational operation example: query all employees whose basic salary is higher than 1500 SELECT * FROM emp WHERE sal> 1500. Example: query the employee information of all clerks. SELECT * FROM emp WHERE job = 'cler'. At this time, no query results are returned, mainly because in the Oracle database, all data is case-sensitive, so the code is modified as follows: SELECT * FROM emp WHERE job = 'cler'; the preceding statements only operate on one condition. Now, you can operate on multiple conditions, you can use and or to connect multiple conditions. Example of www.2cto.com: query the salary range of 1500 ~ SELECT * FROM empWHERE sal> = 3000 AND sal <= 1500 for all employee information between 3000. Example: the query result shows that the job is a clerk, or select * FROM empWHERE job = 'cler' OR job = 'salesman 'For All salesperson information. Example: Query all information of a clerk or salesperson, in addition, these employees are required to pay more than 1200 SELECT * FROM empWHERE (job = 'cler' OR job = 'salesman') AND sal> 1200; example: query all employee information that is not a CLERK. SELECT * FROM emp WHERE job <> 'cler'; SELECT * FROM emp WHERE job! = 'Cler'; SELECT * FROM emp where not job = 'cler'; 2. Range judgment:... AND... "BETWEEN minimum AND maximum" indicates a range judgment process. Example: The basic salary range is 1500 ~ 3000 of employee information SELECT * FROM empWHERE sal BETWEEN 1500 AND 3000; example: You can also... AND... Operation inversion SELECT * FROM emp www.2cto.com where not sal BETWEEN 1500 AND 3000; "... AND ..." Operators are not only useful for numbers, but also for dates. Example: query the information of all employees hired in. Time Range: 1981 ~ 1981-12-31: Use the hiredate field to represent the employment date. Use a string to represent the content in the hiredate field: '01-January 1, January-1981 '~ '31-December 31-81 'SELECT * FROM empWHERE hiredate BETWEEN '01-December 31-December 31-December 31-December 81'; 3. check whether it IS null: IS (NOT) NULL uses this syntax to determine whether the content of a field is "null", but null, number 0, and NULL string are two concepts. Example: Query Information about all employees who receive the bonus. SELECT * FROM empWHERE comm is not null; SELECT * FROM empWHERE NOT comm is null; example: SELECT * FROM empWHERE comm is null for all employees who do not receive the bonus; 4. Determination of the specified range: The IN operator represents a specified query range, for example, the following query request is as follows: Example: Query Information about employees numbered 7369, 7566, and 7799. If the earliest practice is followed, use OR; SELECT * FROM empWHERE empno = 7369 OR empno = 7566 OR empno = 7799; if the new operator IN is used, the code is simplified; SELECT * FROM emp WHERE empno IN (72.16,7566, 7799); but what if we use not in?? Is not in the specified range. SELECT * FROM emp www.2cto.com WHERE empno not in (72.16,7566, 7799); Note: If the not in operator is used, null exists IN the query range, query is NOT affected. SELECT * FROM emp WHERE empno IN (72.16,7566, null). If the not in operator is used, if null exists in the query range, all data is queried. SELECT * FROM emp WHERE empno not in (72.16,7566, null); for this restriction, remember it as a feature and explain why not in cannot be null. 5. fuzzy query: The LIKE clause provides fuzzy search operations. For example, search operations in some programs belong to the implementation of the LIKE clause, however, you must note that the query on the search engine is not LIKE. However, to use the LIKE clause, you must recognize two matching symbols: match a single character: _;-> 1 matching any number of characters: %; -> 0, 1, and multiple examples: Query all employee information starting with the letter A in the employee name. SELECT * FROM emp WHERE ename LIKE 'a % '. Example: query all the employee information in employee name with the second letter A. SELECT * FROM emp WHERE ename LIKE '_ A %'; www.2cto.com example: the employee SELECT * FROM emp WHERE ename LIKE '% A %' with the letter A must be queried. You can also use the NOT operation to reverse the operation: SELECT * FROM emp WHERE ename not like '% A %'; but for the LIKE clause, it is NOT necessarily represented only on string data, but can be expressed on any data: SELECT * FROM emp WHER E ename LIKE '% 100' OR hiredate LIKE' % 100' OR sal LIKE '% 100'; note: the use of LIKE clauses is under development, fuzzy queries in databases must use the LIKE clause, but there is one of the biggest notes when using the LIKE clause: if no keyword is set for fuzzy search ('%'), all records are queried: SELECT * FROM emp WHERE ename LIKE '%' OR hiredate LIKE '%' OR sal LIKE '%'; this feature can help users save a lot of code, so remember. Iii. Data Sorting when data returns the query results, all data is sorted by employee numbers by default. Of course, you can also use the "order by" clause to specify the operation columns for sorting. The SQL syntax is as follows: SELECT [DISTINCT] * | field [alias] [, field [alias] FROM table name [alias] www.2cto.com [WHERE condition (S)] [order by field [ASC | DESC] [, field [ASC | DESC], …]; The "order by" clause is the final content written at the end of all SQL statements. It provides the following instructions for sorting: Multiple sorting fields can be specified during sorting; there are two sorting methods: Ascending Order (ASC): by default, it is also ascending without writing; descending order (DESC): You need to specify, from large to small sort; example: query the information of all employees. SELECT * FROM emp order by sal; SELECT * FROM emp order by sal ASC; example: SELECT * FROM emp order by sal DESC in descending ORDER. Example: Query all employee information, sorted BY salary FROM high to low. If the salary is the same, sort BY employment date FROM morning to night. At this time, two fields must be sorted: salary (DESC), employment date (ASC); SELECT * FROM emp order by sal DESC, hiredate ASC; www.2cto.com SQL> SELECT * FROM emp order by sal DESC, hiredate ASC; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- Jun ---------- 7839 king president 17-11 month-81 5000 10 7902 ford analyst 7566 03-12 month-81 3000 20 7566 jones manager 7839 02-04 month-81 2975 20 7698 blke MANAGER 7839 month-81 2850 30 7782 clark manager 7839 month-81 2450 10 7499 ALLEN SALES MAN 7698 20-2 month-81 1600 300 30 7844 turner salesman 7698 month-81 1500 0 30 7934 miller clerk 7782 23-1 month-82 1300 10 7521 ward salesman 7698 22-2 month-81 1250 500 30 7654 martin salesman 7698 28-9 month-81 1250 1400 30 www.2cto.com 7876 adams clerk 7788 23-5 months-87 1100 20 7900 james clerk 7698 03-12 months-81 950 30 7369 smith clerk 7902 17-12 months- 80 800 20 7788 scott clerk 7566-87 800 20 14 rows selected. For sorting operations, the order by clause is generally used only as needed, and it must be remembered that the order by clause is the last part of all SQL statements.