The full name of SQL is structured query language, which is translated into structured query language in English. It is a standard language for defining and manipulating data in relational database.
1. Features of SQL language
SQL is a non procedural language, which allows users to operate on the high-level data structure according to their own requirements without considering complex problems such as data storage format and data storage path. SQL statements are usually used to complete some database operation tasks, with complete database operation functions such as adding, deleting, modifying, data definition and control. In the process of data application development, the ingenious use of SQL statements can simplify programming and get twice the result with half the effort.
adopt SQL Statement, programmers or database administrators can do the following main work: a. Establish the table of the database, including setting the space that the table can use. b. Change the database system environment settings. c. Grant access to a database or table. d. Index the data table. e. Modify data table structure(Create, delete or modify table fields). f. Create, delete, modify and query data in the database.
2. Classification of SQL language
SQL is the basic operating language of relational database and the interface between database management system and database. It integrates data query, data manipulation, transaction control, data definition and data control functions, and these functions correspond to their respective SQL languages, as follows:
2.1 Data query language DQL Used to retrieve data in the database, mainly SELECT Statement, which is most frequently used in the process of operating the database. 2.2 Data manipulation language DML Used to change the data in the database, mainly including INSERT,UPDATE and DELETE 3 Statement. among INSERT Statement is used to insert data into the database, UPDATE Statement is used to update the existing data in the database, and DELETE Statement is used to delete the existing data in the database. 2.3 Data control language TCL Used to maintain data consistency, including COMMIT, ROLLBACK,and SAVEEPOINT 3 Statement. among COMMIT Statement is used to commit changes to the database, ROLLBACK Statement is used to cancel changes to the database, and SAVEPOINT Statement is used to set the savepoint. 2.4 Data definition language DDL Used to create, modify and delete database objects. For example, you can use CREATE TABLE Statement to create a table; use ALERT TABLE Statement to modify the table structure; use DROP TABLE Statement to delete a table. 2.5 Data control language DCL It is used to grant and withdraw permissions, mainly including GRANT and REVOKE Two commands. Among them, GRANT Command is used to grant permissions to users or roles, and REVOKE Command is used to revoke the permissions of the user or role.
When writing long SQL statements in SQL*Plus environment, press Enter to realize line feed. Note, however, that you should not use a semicolon before pressing Enter, because the semicolon indicates the end of the SQL statement.
3. User mode
In Oracle database, in order to facilitate the management of database objects created by users (such as data tables, indexes and views), the concept of schema is introduced, so that the database objects created by a user belong to the user schema.
3.1 mode and mode object
A schema is a collection of database objects. The schema is owned by a database user and has the same name as the user, such as system schema, scott schema, etc. Database objects of other modes cannot be accessed directly inside a mode. Even if you have access permission, you need to specify the mode name to access database objects of other modes.
Schema objects are logical structures created by users to store or reference data. For example, segments (tables, indexes, etc.) and other non segment database objects owned by users. These non segmented database objects usually include constraints, views, synonyms, procedures and packages.
A database object that is not owned by a user cannot be called a schema object, such as database objects such as roles, tablespaces and directories.
View user through data dictionary_ Tables can obtain the data tables contained in the schema. In addition, users can also query the data table owned by scott mode in system mode, but DBA is required_ Tables data table.
select table_name from dba_tables where owner='SCOTT';
4. Retrieve data
The most common operation of a user on a table or view is to retrieve data, which can be retrieved through SELECT Statement, which is composed of multiple clauses. Through these clauses, various data operations such as filtering, projection and connection can be completed, and finally the query results desired by users can be obtained.
The basic syntax format of this statement is as follows:
SELECT {[ DISTINCT | ALL ] columns | * ]}
[INTO table_name]
FROM {tables | views | other select}
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY columns]
In the above grammar, there are seven sub sentences, and their functions are as follows:
SELECT Clause: used to select columns in data table and view.
INTO Clause: used to insert the structure and data of the original table into the new table.
FROM Clause: used to specify data sources, including tables, views, and others SELECT sentence.
WHERE Clause: used to filter the retrieved data.
GROUP BY Clause: used to display the search results in groups.
HAVING Clause: used from GROUP BY The query results after Clause grouping always filter data rows.
ORDER BY Clause: used to sort the result set (including ascending and descending).4.1 simple query
A query that contains only the SELECT clause and the FROM clause is a simple query. The SELECT clause and the FROM clause are required options of the SELECT statement, that is, each SELECT statement must contain these two clauses.
(1) Retrieve all columns
If you want to retrieve all the columns of the specified data table, you can use an asterisk (*) after the SELECT clause.
When retrieving a data table, pay attention to the schema to which the table belongs.
SQL> connect scott/tiger Connected. SQL> select * from dept;
To execute the above SELECT statement in system mode, you need to add scott before the table dept, that is, scott dept.
In the above example, there is only one data table after the FROM clause. In fact, multiple data tables can be specified after the FROM clause. Each data table is separated by commas (,), and its syntax format is as follows:
FROM table_name1, table_name2, table_name3, ... table_namen
In scott mode, specify two data tables dept and salgrade in the FROM clause. The code is as follows.
SQL> select * from dept,salgrade;
(2)Retrieves the specified column. Users can specify some columns in the query table instead of all columns, and the order of the specified columns is not limited. The specified part is also called projection operation. These column names follow closely SELECT After the keyword, each column name is separated by a comma. Its syntax format is as follows: SELECT column_name1,column_name2,column_name3,...,column_namen
Note: the advantage of using SELECT to specify columns is that the default display order of columns in query results can be changed.
In the Oracle database, there is a row identifier that identifies the unique attribute in the row, and the name of the row identifier is not ROWID. Row identifier ROWID is a hidden column used internally in Oracle database. Because this column is not actually defined in the table, it is also called pseudo column. The pseudo column ROWID is 18 characters long and contains the physical address of the row data in the Oracle database. The ROWID column cannot be found by using the DESCRIBE command, but it can be retrieved in the SELECT statement.
stay scott In mode, retrieve emp Specified column in table job and ename. In addition, it also includes ROWID Pseudo column, the code is as follows. SQL> select rowid,job,ename from emp;
(3)Query date column Date column means that the data type is DATE Columns for. The query date column is no different from other columns, but the default display format of the date column is DD-MON-RR. a. Display date results in simplified Chinese. SQL> alert session set nls_date_language='SIMPLIFIED CHINESE'; SQL> select ename,hiredate from emp; b. Displays date results in us English. SQL> alert session set nls_date_language='AMERICAN'; SQL> select ename,hiredate from emp; c. Displays date effects in a specific format SQL> alert session set nls_date_format='YYYY"year"MM"month"DD"day"'； SQL> select ename,hiredate from emp; d. use to_char Function customize the date display function. In addition to using parameters nls_date_format In addition to setting the date display format, you can also use[ TO_CHAR Function] converts the date value into a string in a specific format.
(4) SELECT clause with expression
When using the SELECT statement, you can use arithmetic expressions for both numeric data and date data. Arithmetic operators can be used in SELECT statements, including +, -, *, / and (). In addition, in the SELECT statement, you can perform not only separate mathematical operations, but also separate date operations and operations associated with column names.
a.retrieval emp Tabular sal Column and adjust its value to the original 1.1 Times. SQL> select sal*(1+0.1),sal from emp;
(5) Specify an alias for the column
Because the column names of many data tables are English abbreviations, users often need to specify aliases for these columns in order to facilitate viewing the search results. In Oracle system, you can specify aliases for columns either by using AS keyword or directly without any keyword.
In scott mode, retrieve the specified columns empno, ename and job of the emp table, and use the AS keyword to specify Chinese aliases for these columns. The code is AS follows:
SQL> connect scott/tiger Connected. SQL> select empno as "Employee number", ename as "Employee name",job as "post" from emp;
When specifying an alias for a column, the keyword AS is optional. Users can also specify the alias of the column directly after the column name.
(6) Show non duplicate records
stay SELECT Statement, you can use DISTINCT Keyword to restrict the display of non duplicate data in query results. This keyword is used in SELECT Before the list of clauses. SQL> select distinct from emp;
(7) Processing NULL values
NULL indicates an unknown value, which is neither a space nor 0 When inserting data, if no data is provided for a specific column and the column has no default value, the result is NULL.
However, in practical applications, the NULL display results often can not meet the application requirements. In this case, it is necessary to use the function NVL to process NULL and convert it into reasonable display results.
The following is an example to illustrate the display result without processing NULL and the specific method of processing NULL.
a. Do not handle NULL. When an arithmetic expression contains NULL If not handled NULL，Then the display result is empty. SQL> select ename,sal,comm,sal+comm from emp;
If the comm value is NULL, the calculated sal+comm value is also NULL.
b. use NVL Function processing NULL. If the actual salary of an employee is blank, it is obviously inconsistent with the actual situation. In order to avoid this situation, we should deal with it NULL. SQL> select ename,sal,comm,sal+nvl(comm,0) from emp; When using functions NVL(COMM,0)When, if comm If there is a value, the function returns its original value; If comm List as NULL，The function returns 0.
(8) Connection string
When executing a query, you sometimes need to connect multiple strings in order to display more meaningful result values. The connection string can use the "|" operator or the CONCAT function.
Note: when connecting a string, if you add a numeric value to the string, you can directly specify the numeric value; If a character value or date value is added to the string, it must be enclosed in single quotation marks.
a. use"||"Operator connection string. SQL> select ename ||""||'s job is'||job from emp; specific CONCAT Function usage will be introduced later.
4.2 filter query
Using the WHERE clause in the SELECT statement can filter the data rows. Only the rows that meet the judgment conditions in the WHERE clause will be displayed in the result set, while those that do not meet the judgment conditions in the WHERE clause will not be included in the result set. This kind of filtering operation is very meaningful. By filtering data, you can get the data users need from a large amount of data.
stay SELECT In the statement, WHERE Clause at FROM Clause, its syntax format is as follows: SELECT comumns_list FROM table_name WHERE conditional_expression a. columns_list:List of fields. b. table_name:Table name. c. conditional_expression:Filter criteria expression.
(1) Comparison and screening
Can be in WHERE Clause uses comparison operators to filter data, so that only the data rows that meet the filtering conditions will be retrieved, and the data rows that do not meet the comparison conditions will not be retrieved. Basic"Comparison and screening"There are six main operations. a. A=B: compare A and B Whether they are equal. b. A!B or A<>B: compare A And B Whether they are not equal. c. A>B: d. A<B e. A>=B f. A<=B
In addition, in addition to the basic "comparison filtering" operation, there are two special "comparison filtering" operations.
1. A{operator}ANY(B): indicates that any element in a and B is compared with the operator operator. As long as one of the comparison values is TRUE, the data row will be returned.
2. A{operator}ALL(B): indicates that all elements in a and B are compared with the operator operator. Only when the comparison value with all elements is TRUE, the data row is returned.
stay scott In mode, use ALL Keyword filter salary(sal)Employee records not equal to 3000950 and 800 at the same time. The code is as follows: SQL> select empno,ename,sal from emp where sal <> all(3000,950,800);
In the process of comparison and filtering, the values of string and date must be identified with single quotation marks, otherwise Oracle will prompt "invalid identifier".
(2) Filter with special keywords
SQL language provides keywords such as LIKE, IN, BETWEEN and IS NULL to filter data. The functions of these keywords are matching string, querying target value, limiting the range of value and judging whether the value is empty.
1. LIKE keyword
In the WHERE clause, the way of using the LIKE keyword to query data is also called string pattern matching or string fuzzy query. The LIKE keyword needs to use wildcards to find the specified pattern in the string, so you need to know the common wildcards.
LIKE Keywords can use the following two wildcards. a. %:Represents 0 or more characters. b. _:Represents one and can only be one character.
stay emp Table, using LIKE Keyword matching with letters S The employee name with any length at the beginning, and the code is as follows: SQL> select empno,ename,job from emp where ename like 'S%';
Note: you can add NOT before the LIKE keyword to indicate a negative judgment. If LIKE is true, NOT LIKE is false. In addition, you can also add NOT before keywords such as IN, BETWEEN, IS NULL and IS NAN to indicate negative judgment.
stay emp In the table, the query work is SALESMAN Employee's name, but I don't remember SALESMAN The exact spelling, but remember that its first character is S，The third character is L，The fifth character is S，The code is as follows: SQL> select empno,ename,job from emp where job like 'S_L_S%';
In addition, the LIKE keyword can also help simplify some WHERE clauses.
stay emp In the table, the information of all employees employed in 1981 shall be displayed as follows: SQL> select empno,ename,sal,hiredate from emp where hiredate like '%81'; The string to query contains"%"or"_"You can use escape when(escape)Keyword to realize query. SQL> select * from dept_temp where dname like 'IT\_%' escape '\';
It is not necessary to use it"\"As an escape character, any character can be used as an escape character. Of course, many Oracle The reason why professionals often use"\"Character is an escape character because it is in UNIX Operating system and C Languages are always escape characters. SQL> select * from dept_temp where dname like 'ITa_%' escape 'a'; -- 'a' As an escape character.
2. IN keyword.
When testing whether a data value matches one of a set of target values, the IN keyword is usually used to specify the search criteria of the list.
The format of the IN keyword is IN (target value 1, target value 2, target value 3,...), The items of the target value must be separated by commas and IN parentheses.
stay emp Table, using IN Keyword query job is PRESIDENT, MANAGER and ANALYST Employee information of any one of the. The code is as follows:
SQL> select empno,ename,job from emp where job in('PRESIDENT','MANAGER','ANALYST');
In addition, NOT IN It means that the value specified in the query is not in a group of target values, which is also very common in practical application.3. BETWEEN keyword.
You need to return whether a data value is between two given values, which can be retrieved using range conditions. Usually use between And and not BETWEEN ... AND to specify the range condition.
Use between When using and query criteria, the first value specified must be less than the second value. Because between And is actually a short form of the query condition "greater than or equal to the first value and less than or equal to the second value", that is, between And should include the values at both ends, which is equivalent to the comparison operator (> =... < =).
stay emp Table, using NOT .. BETWEEN ... AND Keyword query salary(sal)Employee information between 2000 and 3000, with the following code: SQL> select empno,ename,sal from emp where sal between 2000 and 3000;
and NOT ... BETWEEN ... AND Statement returns a data value outside the range of two specified values, but does not include two specified values. stay emp Table, using NOT ... BETWEEN ... AND Keyword query salary(sal)Employee information not between 1000 and 3000, with the following code: SQL> select empno,ename,sal from emp where sal not between 1000 and 3000;
4. IS NULL keyword.
Null is technically an unknown and uncertain value, but it is different from an empty string because a null value is a non-existent value and an empty string is a string with a length of 0.
Because null values represent unknown values, not all null values are equal. For example, the age of two students in the student table is unknown, but it cannot be proved that the two students are equal in age. In this way, the "=" operator cannot be used to detect null values. Therefore, SQL introduces an IS NULL keyword to detect the equivalence between special values, and the IS NULL keyword is usually used in the WHERE clause.
stay hr In mode, use IS NULL Keyword filter out locations Provinces or states in the table(state_provice)Street address information whose name is null, and the code is as follows: SQL> connect hr/hr; Connected. SQL> select street_address from locations where state_province is null;
(3) Logical filtering
Logical filtering refers to the use of logical operators AND, OR and NOT in the WHERE clause for data filtering. These logical operators can combine multiple filtering conditions, which is convenient for users to obtain more accurate data records.
1. AND A logical operator represents the difference between two logical expressions"Logic and"Users can use the relationship AND Operator plus comparison operator instead BETWEEN ... AND keyword. stay emp Table, using AND Operator query salary(sal)Employee information between 2000 and 3000. The code is as follows: SQL> select empno,ename,sal from emp where sal>=2000 and sal <= 3000; 2. OR A logical operator represents the difference between two logical expressions"Logical or"One of the results of the two expressions is TRUE，Then the value of this logic or expression is TRUE. stay emp Table, using OR The logical operator queries the information of employees whose salary is less than 2000 or more than 3000. The code is as follows: SQL> connect scott/tiger Connected. SQL> select empno,ename,sal from emp where sal < 2000 or sal > 3000; 3. NOT Logical operators perform logical operations on expressions.
4.3 group query
The purpose of data grouping is to summarize data or display single line summary information for the whole grouping. It is usually used in the query result set GROUP BY Clause to group records. stay SELECT In the statement, GROUP BY Clause at FROM Clause, its syntax format is as follows: SELECT columns_list FROM table_name [WHERE conditional_expression] GROUP BY columns_list 1.columns_list: Field list, in GROUP BY Clause can also specify multiple column groups. 2.table_name: Table name. 3.conditional_expression: Filter criteria expression.
The GROUP BY clause can divide the data set into multiple groups based on the value of a specified column. All records in the same group have the same value on the grouping attribute, or divide the data set into multiple groups based on the value of a specified column.
(1) Single column grouping using the GROUP BY clause
Single column grouping refers to generating grouping statistics results based on columns. When grouping a single column, a statistical result will be generated based on each different value of the grouped column.
stay emp In the table, by department number(depno)The specific codes are as follows: SQL> select deptno,job from emp group by deptno,order by deptno;
The GROUP BY clause is often used with aggregate functions. Using the GROUP BY clause and aggregation function, you can classify and count each group of data in the query results. Therefore, in the results, each group of data has a corresponding statistical value. In Oracle system, statistical functions are often used, as shown in the following table:
Common statistical functions function explain AVG Returns the average value of a numeric column or a calculated column. COUNT Returns the number of records in the query result. MAX Returns the maximum value of a numeric or calculated column. MIN Returns the minimum value of a numeric or calculated column. SUM Returns a numeric column or the sum of calculated columns.
stay emp Table, using GROUP BY Clause to group salary records and calculate the average salary(AVG)，Sum of all wages(SUM)And the maximum wage(COUNT)，The specific codes are as follows: SQL> select job,avg(sal),sum(sal),max(sal),count(job) from emp group by job; in use GROUP BY Clause, pay attention to: 1. stay SELECT Clause can only be followed by two types of expressions: statistical functions and column names for grouping. 2. stay SELECT The column name in the clause must be the column for grouping. In addition, it is wrong to add other column names, but GROUP BY The column name after the clause may not appear in the SELECT Clause. 3. By default, the GROUP BY The grouping columns specified in clause are arranged in ascending order. If you need to reorder, you can use ORDER BY Clause to specify a new sort order. GROUP BY The columns in the clause may not be in the SELECT In the list. query emp Table showing by position(job)classification(job Not included in SELECT In Clause)The average salary of each type of employees, and the displayed results are arranged from small to large according to the position. The specific code is as follows: SQL> select avg(sal) from emp group by job; If a grouping function is used in a query, any columns or expressions that are not in the grouping function must be GROUP BY Clause.
(2)use GROUP BY Clause to group multiple columns Multi column grouping refers to generating grouping statistical results based on two or more columns. When grouping multiple columns, statistical results are generated based on different values of multiple columns. use GROUP BY Group multiple columns. query emp Table, showing the average wage and maximum wage of each position in each department. The specific codes are as follows: SQL> select deptno,job,avg(sal),max(sal) from emp group by deptno,job;
(3)use ORDER BY Clause to change the grouping sorting result When used GROUP BY Clause when performing grouping statistics, it will be automatically arranged in ascending order based on the grouping column. In order to change the sorting result of grouped data, you need to use ORDER BY Clause. query emp Table, showing the department number and payroll of each department, and the payroll is arranged in descending order. The specific codes are as follows: SQL> select deptno,sum(sal) from emp group by deptno order by sum(sal) desc;
(4) Using the HAVING clause to restrict grouping results
The HAVING clause is usually used together with the GROUP BY clause. After the grouping results are counted, you can use the HAVING clause to further filter the grouping results. If the GROUP BY clause is not used, the function of the HAVING clause is the same as that of the WHERE clause. The similarity between the HAVING clause and the WHERE clause is to define search conditions. The only difference is that the HAVING clause can contain aggregation functions, such as common COUNT,AVG,SUM, etc; Aggregate functions cannot be used in the WHERE clause.
If the GROUP BY clause is used in the SELECT statement, the HAVING clause applies to those groups created by the GROUP BY clause. If a WHERE clause is specified and no GROUP BY clause is specified, the HAVING clause is applied to the output of the WHERE clause and the entire output is treated as a group. If there is neither a WHERE clause nor a specified GROUP BY clause in the SELECT statement, the HAVING clause is applied to the output of the FROM clause and treated as a group.
One way to understand the function of HAVING clause is to remember the clause processing order in the SELECT statement. In the SELECT statement, the FROM clause receives the output FROM the GROUP BY, WHERE or FROM clause first.
stay emp In the table, the average salary of each department is calculated by grouping, and then HAVING Clause to filter out records whose average salary is greater than 2000. The specific code is as follows: SQL> select deptno as Department number,avg(sal) as average wage from emp group by deptno having avg(sal)>2000;
As can be seen from the above query results, the SELECT statement uses the GROUP BY clause to make group statistics on the emp table, and then further filter by the statistical value of the HAVING clause.
Generally, the HAVING clause is used together with the GROUP BY clause, so that the summarized data can be further filtered after summarizing the relevant data.
(5) Use the ROLLUP and CUBE operators in the GROUP BY clause
By default, when using the GROUP BY clause to generate data statistics results, only the data statistics of relevant columns will be generated, and subtotal and total statistics will not be generated.
1. Use the ROLLUP operator to perform data statistics
When you directly use the GROUP BY clause to group multiple columns, you can only generate simple data statistics results. In order to generate data statistics, horizontal subtotal and total statistics, you can use the ROLLUP operator in the GROUP BY clause.
stay emp Table, using ROLLUP Operator, which displays the average salary of each position of each department, the average salary of each department and the average salary of all employees. SQL> select deptno as Department number,job as post,avg(sal) as average wage from emp group by rollup(deptno,job);
2. Use the CUBE operator to perform data statistics.
In order to generate data statistics, horizontal subtotal, vertical subtotal and total statistics, you can use the CUBE operator.
stay emp Table, using CUBE Operator, which displays the average salary of each department and position, Department average salary, position average salary and average salary of all employees. The specific codes are as follows: SQL> select deptno as Department number,job as post,avg(sal) as average wage from emp group by cube(deptno,job);
3. Use the GROUPING function.
When using the ROLLUP or CUBE operator to generate statistical results, a row of statistical results may use one or more columns, or may not use any columns. To determine whether a specific column is used for the statistical results, you can use the GROUPING function. If the function returns 0, it indicates that the column is used in the statistical result; If the function returns 1, it indicates that the column is not used in the statistical results.
stay emp Table, using GROUPING Function to determine the columns used by the statistical results. The specific code is as follows: SQL> select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job);
4. Use matching columns in the ROLLUP operator.
A conforming column is regarded as a combination of columns of a logical unit. When referring to a conforming column, you need to enclose the relevant column in parentheses. By using compound columns in the ROLLUP operator, you can skip some of the statistical results of the ROLLUP operator.
For example, the statistical result of the clause GROUP BY ROLLUP(a,b,c) is equivalent to the union of GROUP BY(a,b,c),GROUP BY(a,b), GROUP BY a, and GROUP BY().
If (b,c) is taken as a composite column, the result of the clause GROUP BY ROLLUP(a,(b,c)) is equivalent to the union of GROUP BY(a,b,c),GROUP BY a and GROUP BY().
stay ROLLUP Use compound columns in operators emp The table shows the total wages of specific departments and positions and the total wages of all employees. The specific codes are as follows: SQL> select deptno,job,sum(sal) from emp group by rollup((deptno,job));
5. Use compound columns in the CUBE operator.
By using composite columns in the CUBE operator, you can skip some of the statistical results of the CUBE operator. For example, the statistical result of the clause GROUP BY CUBE(a,b,c) is equivalent to the union of GROUP BY(a,b,c), GROUP BY(a,b),GROUP BY(a,c), GROUP BY(b,c), GROUP BY a, GROUP BY b, GROUP BY c and GROUP BY(); If (a,b) is taken as a composite column, the result of the clause GROUP BY CUBE((a,b),c) is equivalent to the union of GROUP BY(a,b,c),GROUP BY(a,b),GROUP BY b and group by ().
stay CUBE Use compound columns in operators emp The table shows the total wages of specific departments and positions and the total wages of all employees. The specific codes are as follows: SQL> select deptno,job,sum(sal) from emp group by cube((deptno,job));
6. Use the GROUP SETS operator
The GROUPING SETS operator is a further extension of the GROUP BY clause. Before Oracle Database 9i, using the GROUP BY clause can only display a single grouping result at a time. If you want to generate multiple grouping statistical results, you need to write multiple SELECT grouping statements. Starting from Oracle 9i, the statistical results of multiple groups can be combined by using the GROUPING SETS operator, which simplifies multiple grouping operations.
stay emp In the table, do the following. (1)Displays the average salary of each department. Department number is required(deptno)Perform grouping statistics. The specific code is as follows: SQL> select deptno,avg(sal) from emp group by deptno; (2)Display the average salary of each position, which needs to be used(job)Perform grouping statistics. The specific code is as follows: SQL> select job,avg(sal) from emp group by job; (3)It displays the average salary of the Department and the average salary of the position. The specific codes are as follows: SQL> select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
In order to display the statistical results of multiple groups, you can use the GROUPING SETS operator to merge the statistical results of groups. For example, if you want to display both the Department average salary and the position average salary, you can use the GROUPING SETS operator to merge the grouping results.
4.4 Sorting Query
When retrieving data, if the data is read directly from the database, the query results will be arranged in the default order, but often this default order is not what users need. Especially when the amount of returned data is large, it is very inconvenient for users to view the information they want, so they need to sort the retrieved result set.
stay SELECT Statement, you can use ORDER BY Clause sorts the retrieved result set at FROM Clause, its syntax format is as follows.
SELECT columns_list
FROM table_name
[WHERE conditional_expression]
[GROUP BY columns_list]
ORDER BY {order_by_expression [ASC | DESC]} [,...n]
(1)columns_list: Field list, in GROUP BY Clause can also specify multiple column groups.
(2)table_name: Table name.
(3)condition_expression: Filter criteria expression.
(4)order_by_expression: Represents the column name or expression to sort. keyword ASC Indicates to sort in ascending order, which is also the default sorting method. Keywords DESC Indicates a descending order.
ORDER BY Clause can sort the query results according to one or more columns in the query results, and the first sorting item is the primary sorting basis and the others are the secondary sorting basis.1. Single column sorting
stay scott In mode, retrieve emp All the data in the table and according to the department number(deptno),Employee number(empno)Sort, the specific code is as follows: SQL> select deptno,empno,ename from emp order by deptno,empno;
If the ORDER BY clause is used, it must be the last clause of the SQL statement. For example, when a SELECT statement contains multiple clauses (WHERE, GROUP BY, HAVING, ORDER BY) at the same time, ORDER BY must be the last clause.
You can also use column numbers in the ORDER BY clause. When sorting, you can sort not only by column name and column name, but also by the position of column or expression in the selection list. If the column name or expression name is very long, sorting by column tag can reduce the length of the sorting statement. In addition, when using UNION, UNION ALL, INTERSECT, MINUS and other set operators to merge query results, if the column names of the selection list are different and you want to sort, you must use the column position to sort.
query emp The annual salary of employees in the table is sorted according to the annual salary, and the code is as follows: SQL> select empno,ename,sal*12 Annual Salary from emp order by 3 desc; 3 here represents the third column, so ORDER BY 3 Is to sort by the third column.
4.5 multi table Association query
In the actual application system development, multiple data tables will be designed. The information of each table does not exist independently, but there is a certain relationship between the information of several tables. When users query the information of a table, they may need to query the information of associated data tables, which is multi table associated query. The SELECT statement itself supports multi table Association queries, which are much more complex than single table queries. In multi table Association query, the concepts of table alias, inner connection, outer connection, natural connection, self connection and cross connection may be involved.
1. Table alias
In multi table Association query, if a column with the same name exists between multiple tables, the table name must be used to limit the reference of the column. For example, in scott mode, both dept table and emp table have deptno column. When users use this column to query two tables, they need to distinguish the ownership of the two columns by specifying the table name. However, as queries become more complex, the statement becomes lengthy because the table name must be entered for each qualified column. In this case, SQL language provides a mechanism to set the table alias. Using a short table alias can replace the original long table name, which can greatly reduce the length of the statement.
stay scott In mode, through deptno(Department number) column emp Table and dept Table and retrieve the relevant field information in the two tables. The code and operation results are as follows: SQL> select e.empno as Employee number,e.ename as Employee name,d.dname as department from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';
In the above SELECT statement, the FROM clause is executed first, followed by the WHERE clause and the SELECT clause. In this way, after the alias of the table is specified in the FROM clause, when the reference column needs to be qualified, all other clauses can use the alias of the table.
In addition, it should be noted that once an alias is specified for the table in the FROM clause, the alias of the table must be used in the remaining clauses, and the original table name is not allowed to be used.
To sum up, the precautions for using table aliases are as follows: a. Table alias in FROM Clause. Aliases are placed after the indication and separated by spaces. b. Once the table name is defined, only the alias of the table can be used in the whole query statement, and the table name can no longer be used. c. The alias of the table is valid only in the defined query statement. d. You should choose a meaningful alias. The alias of the table is up to 30 characters, but the shorter the better.
2. Internal connection
INNER JOIN is a commonly used multi table Association query method, which is generally realized by using the keyword INNER JOIN. Among them, the INNER keyword can be omitted. When only the JOIN keyword is used, the statement only represents the INNER connection operation. When using INNER JOIN to query multiple tables, you must define an ON clause after the FROM clause. The ON Clause specifies the INNER JOIN operation, lists the data rows matching the JOIN conditions, and uses the comparison operator to compare the connected values.
In short, inner connection is to use JOIN Specify two tables to join, using ON Specify the connection conditions of the connection table. If the query scope is further limited, you can add it directly later WHERE Clause. The syntax format of inner connection is as follows: SELECT columns_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition; a. columns_list: List of fields. b. table_name1 and table_name2:The two should be connected to the surface. c. join_codition: Implement the conditional expression of inner connection. stay scott In mode, through deptno Field inner connection emp Table and dept Table and retrieve the information of relevant fields in the two tables. The code and operation results are as follows: SQL> select e.empno as Employee number, e.ename as Employee name, d.dname as department from emp e inner join dept d on e.deptno=d.deptno; Since the above code represents the internal connection operation, the FROM Clause can be omitted completely INNER keyword.
3. External connection
When using inner join for multi table query, the returned query result only contains the rows that meet the query conditions and join conditions. Inner join eliminates rows that do not match any row in another table, while outer join expands the result set of inner join. In addition to returning all matching rows, it will also return some or all mismatched rows, which mainly depends on the type of outer join. There are three types of external connections:
a. Left outer connection: keyword is LEFT OUTER JOIN or LEFT JOIN. b. Right outer connection: keyword is RIGHT OUTER JOIN or RIGHT JOIN. c. Full external connection: keyword is FULL OUTER JOIN or FULL JOIN.
Different from the inner connection, the outer connection can not only list the rows that match the connection conditions, but also list all the data rows that meet the search conditions in the left table (left outer connection), the right table (right outer connection) or two tables (all outer connections).
(1) Left outer connection.
The query result of the left outer join contains not only the data rows that meet the join conditions, but also the data rows that do not meet the conditions in the left table.
use first INSERT Statement in emp Insert new record into table(Note that there is no deptno and dname Columns insert values, that is, their values are NULL)，Then implement emp Table and dept Pass between tables deptno The column is connected to the left outside, and the specific code is as follows: SQL> insert into emp(empno,ename,job) values(9527,'EAST','SALESMAN'); A row has been created. SQL> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno;
As can be seen from the above query results, although the deptno column value of the newly inserted data row is NULL, the row record still appears in the query results, which indicates that the left outer join query will contain the data rows in the left table that do not meet the "join conditions".
(2) Right outer connection.
Similarly, the query result of the right outer join contains not only the data rows that meet the join conditions, but also the data rows that do not meet the join conditions in the right table.
stay scott Mode, implementation emp Table and dept Pass between tables deptno The column is connected outside the right, and the specific code is as follows: SQL> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno; The query results of the right outer connection will contain the query results that are not satisfied in the right table"Connection conditions"Data row for.
The connection operator of external connection can also be used in external connection. The connection operator of external connection is "(+)". The connection operator can be placed to the left or right of the equal sign, but it must be placed on the side lacking corresponding information, such as the side where e.deptno is located.
When using the "(+)" operator to perform an outer join, the operator should be placed at the end that displays fewer rows (rows that fully meet the join conditions).
The above query statement can also be written as follows: SQL> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno; use"(+)"Operators should pay attention to: a. When used"(+)"When the operator performs an outer join, if WHERE Clause contains more than one condition, it must be included in all conditions"(+)"Operator. b. "(+)"Operators apply only to columns, not expressions. c. "(+)"Operators cannot be associated with ON and IN Operator.
(3) Fully external connection.
When performing a full outer join, Oracle will execute a complete left outer join and right outer join query, then merge the query results and eliminate duplicate record rows.
stay scott Mode, implementation emp Table and dept Pass between tables deptno The column is completely externally connected, and the specific code is as follows: SQL> select e.empno,e.ename,e.job,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno;
4. Natural connection.
The function of natural connection is similar to that of internal connection. Natural connection means that when retrieving multiple tables, Oracle will automatically connect the columns in the first table with the columns with the same name in the second table. In natural connection, the user does not need to specify the column to be connected. This task is automatically completed by the Oracle system. Natural connection uses the NATURAL JOIN keyword.
stay emp Retrieve salary from table(sal field)More than 2000 records, and realize emp Table and dept Natural connection of the table. The specific code is as follows: SQL> select empno,ename,job,dname from emp natural join dept where sal > 2000;
Because natural connection requires the same column names between tables, it is easy to make unpredictable errors in the design of tables, so natural connection is rarely used in the development of practical application systems.
However, this is a way of querying data with multi table Association after all, and it still has certain use value in some specific cases. In addition, it should be noted that when using natural connection, you cannot specify a qualifier for the column (i.e. table name or table alias), otherwise the Oracle system will pop up the error prompt "ora-25155: the column used in natural connection cannot have a finite qualifier".
5. Self connection
In application system development, users may have "self referential" foreign keys. "Self referential" foreign key refers to a column in the table, which can be a foreign key of the table's primary key.
Self connection is mainly used to display parent-child relationships or hierarchical relationships on self reference tables. Self reference table refers to a table with reference relationship or master-slave relationship between different columns of the same table. For example, the emp table contains columns empno (employee number) and mgr (administrator number), which have a reference relationship. In this way, users can query the subordinate employee information managed by a manager through the relationship between mgr column and empno column.
empno ename mgr ------------------------ 7893 KING 7566 JONES 7839 7698 BLAKE 7839 7782 CLARK 7839 ...
According to the correspondence between empno column and mgr column, it can be determined that the managers of employees JONES,BLACK, and CLARK are KING.
To show the correspondence between employees and their managers, you can use self connections. Because self join is a join query between the same table, the table alias must be defined.
stay scott In this mode, you can query the information of subordinate employees managed by all managers. The specific code is as follows: SQL> select em2.ename Top management, em2.ename as Subordinate employees from emp em1 left join emp em2 on em1.mgr=em2.empno order by em1.mgr;
6. Cross connection
Cross connection is actually a connection that does not require any connection conditions. It uses CROSS JOIN Keyword, and its syntax format is as follows: SELECT colums_list FROM table_name1 CROSS JOIN table_name2 colums_list:List of fields. table_name1 and table_name2:The names of two tables that implement cross joins. The execution result of cross connection is a Cartesian product. This query result is very redundant, but it can be passed WHERE Clause to filter out useful record information. stay scott In mode, through cross connection dept Table and emp Table to calculate the number of rows of the query result. The specific code is as follows: SQL> select count(*) from dept cross join emp;
Common Oracle system functions
SQL language is a scripting language, which provides a large number of built-in functions. Using these built-in functions can greatly enhance the operation and judgment functions of SQL language. This section will introduce some common functions in Oracle, such as character functions, number functions, date and time functions, conversion functions, aggregation functions, etc.
1. Character function
Character class functions are functions specially used for character processing. The processing objects can be character or string constants or character type columns. There are several commonly used character class functions:
(1) ASCII(c) function and CHR(i) function.
The ASCII(c) function is used to return the ASCII code of a character, where the parameter C represents a character; CHR(i) function is used to return the character corresponding to the given ASCII code value. i represents an ASCII code value.
From the functions of these two functions, we can see that there is an inverse relationship between them.
Get characters separately'Z,H,D And spaces' of ASCII Value, the specific code is as follows:
SQL> select ascii('Z'), ascii('H'), ascii('D') ascii(' ') space from dual;
dual yes Oracle A special table provided in the system for temporary data calculation, which has only one column dummy，Type is VARCHAR2(1).
use CHR()Function returns its corresponding character. The specific codes are as follows:
SQL> select chr(90),chr(72),chr(68),char(32) S from dual;(2)CONCAT(s1,s2) function.
This function connects the string s2 to the back of the string s1, and returns s2 if s1 is NULL; If s2 is NULL, s1 is returned; If s1 and s2 are both NULL, NULL is returned.
use CONCAT()Function connection Hello and World Two strings, the specific code and running results are as follows.
SQL> select concat('Hello','World!') information from dual;(3)INITCAP(s) function
This function capitalizes the first letter of each word of the string s and lowercases the other letters. Words are distinguished by spaces, control characters and punctuation.
use INITCAT()Function conversion string'oh my god!'The specific code and operation results are as follows:
SQL> select initcat('oh my god!') information from dual;(4)INSTR(s1,s2[,i][,j]) function
This function is used to return the position of the character s2 when it appears for the j th time in the string s1. The search starts from the ith character of the string s1.
When the character to be searched is not found, the return value of the function is 0; If i is negative, the search will run from right to left, but the return position of the function is calculated from left to right. Where s1 and s2 are strings; i and j are integers, and the default value is 1
In string oracle 11g In, query the string from the third character'1'The location of the second occurrence, specific code and operation results are as follows:
SQL> select instr('oracle 11g','1',3,2) abc from dual;(5)LENGTH(s) function
This function is used to return the length of the string s. if s is NULL, the return value is NULL.
stay scott In mode, by using LENGTH()The function returns the employee information and department information whose employee name length is greater than 5. The specific code is as follows: SQL> select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where length(e.ename)>5;
(6)LOWER(s) function and UPPER(s) function
The LOWER(s) function and the UPPER(s) function are used to return the lowercase and uppercase forms of the string s, respectively. These two functions often appear in the WHERE clause.
stay hr Mode, in employees Search the employee name in the table with letters a Employee information at the beginning, and first_name Convert the value of the field to lowercase last_name The value of the field is converted to uppercase, and the specific code is as follows: SQL> select employee_id,lower(first_name),upper(last_name) from employee where lower(first_name) like 'a%';
(7)LTRIM(s1,s2) function, RTRIM(s1,s2) function and TRIM(s1,s2) function
These three functions are used to delete the string s2 on the left of the string s1, the string s2 on the right of the string s1, and the string s2 at the left and right ends of the string s1 If the string s2 is not specified in these three functions, it means that the space in the corresponding direction is removed.
use LTRIM()/ RTRIM()and TRIM()Function removes the string separately'####East####', 'East 'and'#####East###' Middle left'#'，Right space and left and right'#The specific code is as follows:
SQL> select ltrim('#####East####','#'),rtrim('East '),trim('#from ') from dual;(8)REPLACE(s1,s2[,s3]) function
This function uses the s3 string to replace all s2 strings that appear in the s1 string, and returns the replaced new string, where the default value of s3 is an empty string.
use REPLACE()Function string'Bad Luck Bad Gril' Medium Bad String use Good Replace, specific code:
SQL> select replace('Bad Luck Bad Gril','Bad','Good') from dual;(9)SUBSTR(s,i,[j]) function
This function indicates that the substring with length J is intercepted from the ith position of string s. If the parameter J is omitted, it is directly intercepted to the tail. Where i and j are integers.
use SUBSTR()Function in string MessageBox The substring with length of 3 is intercepted from the eighth position in. The specific code is as follows:
SQL> select substr('MessageBox',8,3) from dual;2. Digital functions
Digital functions are mainly used to perform various data calculations. All digital functions have digital parameters and return digital values. Oracle system provides a large number of digital functions, which greatly enhance the scientific computing power of Oracle system.
Digital class function and its description -------------------------------------------------- function explain -------------------------------------------------- ABS(n) return n Absolute value of. CEIL(n) Returns a value greater than or equal to n The smallest integer of. COS(n) return n Cosine value of EXP(n) return e of n Power, e=2.71828183 FLORR(n) Return less than or equal to n Maximum integer of LOG(n1,n2) Return to n1 As the bottom n2 Logarithm of MOD(n1,n2) return n1 divide n2 Remainder of POWER(n1,n2) return n1 of n2 Power ROUND(n1,n2) Return to the right of the decimal point n2 Bit n1 The value of, n2 The default value of is 0, which returns the integer closest to the decimal point,If n2 If it is negative, it will be rounded to the corresponding digit to the left of the decimal point Up, n2 Must be an integer. SIGN(n) if n If it is negative, return-1；if n If it is a positive number, 1 is returned; if n 0, then 0 is returned SIN(n) return n Is sinusoidal, n Is radian. SQRT(n) return n Square root of TRUNC(n1,n2) Return to end n2 Decimal n1 The value of, n2 The default setting is 0 when n2 The default setting of the n1 Truncated to an integer, if n2 If it is negative, it is truncated to the corresponding digit to the left of the decimal point. ----------------------------------------------------
(1) CEIL(n) function
This function returns the smallest integer greater than or equal to the value n, which is suitable for some comparison operations.
use DEIL()Function returns the integer value of three specified decimals. The specific code is as follows: SQL> select ceil(7.3),ceil(7),ceil(-7.3) from dual;
(2)ROUND(n1,n2) function
This function returns the value of n1 rounded to the n2 bit on the right of the novel point. The default value of n2 is 0, which will return the nearest integer of the novel point. If n2 is negative, it is rounded to the corresponding digit to the left of the decimal point. n2 must be an integer.
use ROUND()Function returnΠ(3.1415926)Is the value of two decimal places. The specific code is as follows: SQL> select round(3.1415926,2) from dual;
(3)POWER(n1,n2) function
This function returns n1 to the power of n2. Where n1 and n2 are integers.
SQL> select power(2,3) from dual;
3. Date and time functions
In Oracle 11g, the system provides many functions for processing date and time. Through these functions, the specific date and time required for calculation can be realized. The commonly used date and time functions are shown in the table:
function explain ------------------------------------------------- ADD_MONTHS(d,i) Return date d add n Months later. Among them, i Is any integer. LAST_DAY(d) Return included date d Last day of the month MONTHS_BETWEEN(d1,d2) return d1 and d2 Number between, if d1 and d2 If all the dates are the same or are the last day of the month, an integer will be returned, otherwise the returned results will be packaged Including one decimal point. NEW_TIME(d1,t1,t2) Among them, d1 It is a date data type, and the current area t1 The date and time in is d1 Return to time zone when t2 Date and time in. t1 and t2 Is a string. SYSDATE() Returns the current time of the system. -------------------------------------------------------
The default format of date type is' DD-MON-YY ', where DD represents a two digit' Day ', MON represents a three digit' month ', YY represents a two digit' month ', for example,' 01-october-11 'represents October 1, 2011. Let's look at the specific applications of several common functions.
(1) SYSDATE() function
This function returns the current date of the system.
Use the SYSDATE() function to return the current date of the system. The specific code is as follows:
SQL> select sysdate as system date from dual;
(2)ADD_MONTHS(d,i) function
This function returns the result after the date d plus i months. Where i is any integer.
Use ADD_MONTHS() function adds 6 months to the current date and displays its value. Specific code:
SQL> select ADD_MONTHS(sysdate,6) from dual;
4. Conversion function
When operating the data in the table, it is often necessary to convert this data from one type to another. At this time, it is necessary to convert the type function. For example, it is common to convert a string with "specific format" into a date and a number into a string.
function explain CHARTORWIDA(s) This function converts a string s Convert to ROWID Data type. CONVERT() This function converts a string s from bset Convert character set to aset Character set. ROWIDTOCHAR() This function will ROWID Data type conversion to CHAR Type. TO_CHAR() This function converts an expression into a string, format Represents the string format. TO_DATE() This function converts a string s Convert to DATE Type, format Represents string format, lan Indicates the language used. TO_NUMBER() This function returns a string s Represents a number, and the return value is format Display in format,format Represents string format, lan Indicates the language used.
(1)TO_CHAR() function
This function converts the expression into a string, and format represents the string format.
Use to_ The char() function converts the system date to "YYYY-MM-DD" format, and the specific code is as follows:
SQL> select sysdate as Default format date, to_char(sysdate,'YYYY-MM-DD') as Date after conversion from dual;
(2)TO_NUMBER(s[,format[lan]]) function
This function will return the number represented by the string s, and the return value will be displayed in format. Format represents the string format, and lan represents the language used.
Use to_ The number() function converts the hexadecimal number 18f into a decimal number. The specific code is as follows:
SQL> select to_number('18f','xxx') as Decimal number from dual;5. Aggregation function
Using the aggregation function, we can calculate a set of data and get the corresponding results. For example, common operations include calculating the average value, counting the number of records, and calculating the maximum value.
Aggregate functions provided by Oracle 11g:
function explain AVG(x[DISTINCT|ALL) Calculate the average value of the selected list item. The list item can be an expression of one column or multiple columns. COUNT(x[DISTINCT|ALL]) Returns the number of records in the query result. MAX(x[DISTINCT|ALL]) Returns the maximum number of selected list items, which can be expressions for one or more columns MIN(x[DISTINCT|ALL]) Returns the decimal point in the selected column item. The list item can be an expression of one or more columns SUM(x[DISTINCT|ALL]) Returns the sum of the values of the selected list item, which can be an expression of one or more columns VARIANCE(x[DISTINCT|ALL]) Returns the statistical variance of the selected list item, which can be an expression of one or more columns STDDEV(x[DISTINCT|ALL]) Returns the standard deviation of a selected list item, which can be an expression of one or more columns
In the process of practical application development, aggregation functions are widely used, such as statistical average, total number of records and so on.
In scott mode, COUNT() function is used to calculate the total number of employees and AVG() function is used to calculate the average salary. The specific code is as follows:
SQL> select count(empno) as Total number of employees,round(avg(sal),2) as average wage from emp;
5. Usage of subquery
In the process of performing data operations (including query, addition, modification and deletion), if an operation needs to rely on the query results of another SELECT statement, the SELECT statement can be embedded into the operation statement to form a sub query. In fact, in relational database, the data relationship between tables is very close. They are interrelated and interdependent. In this way, the corresponding sub queries can be used according to the relationship between data, so as to realize complex queries.
5.1 what is a subquery
A subquery is another SELECT statement within an SQL statement, also known as an inner query or an inner SELECT statement. In the SELECT, INSERT, UPDATE or DELETE commands, subqueries can be included wherever an expression is allowed, and subqueries can even be included in another subquery.
stay scott Mode, in emp Query department name in the table(dname)by RESEARCH The specific code of the employee information is as follows: SQL> select empno,ename,job from emp where deptno=(select deptno from dept where dname='RESEARCH');
In this way, the requirements of this example can be fully realized through multi table Association query, that is, the following code can be used to replace the above code:
SQL> select empno,ename,job from emp join dept on emp.deptno=dept.deptno where dept.dname='RESEARCH';
As can be seen from the above two codes, compared with multi table Association query, sub query is more flexible, more powerful and easier to understand. However, multi table Association query also has its own advantages. For example, the query efficiency is higher than that of sub query.
In general, the external query statement retrieves one row, and the sub query statement needs to retrieve the data once, and then judge whether the conditions of the external query statement are met. If the conditions are met, the external query statement will add the retrieved data row to the result set. If the conditions are not met, the external query statement will continue to retrieve the next row of data, so the sub query is slower than the multi table associated query.
In addition, when using subqueries, you should also pay attention to the following rules. a. Subqueries must use parentheses"()"Wrap it up. b. Cannot include in subquery ORDER BY Clause. c. Subqueries can be nested more than 25 layers.
In Oracle 11g, sub query is usually subdivided into single line sub query, multi line sub query and associated sub query.
(1) Single line subquery
Single line subquery refers to the subquery statement that returns one line of data. Single line comparison operators (=, >, <, > =, < = and < >) can be used when a single line subquery is referenced in the WHERE clause.
stay emp In the table, you can query the information of employees who are neither the maximum wage nor the minimum wage. The specific codes are as follows: SQL> select empno,ename,sal from emp where sal > (select min(sal) from emp) and sal < (select max(sal) from emp);
In the above statement, if the execution result of the inner sub query statement is null, the outer WHERE clause will never meet the conditions, so the result of the query must be null, because null values cannot participate in the comparison operation.
When executing a single line sub query, it should be noted that the return result of the sub query must be a line of data, otherwise the Oracle system will prompt that it cannot be executed. In addition, the sub query cannot contain the ORDER BY clause. If you have to sort the data, you can only use the ORDER BY clause in the external query statement.
(2) Multiline subquery
Multi row subquery refers to a subquery statement that returns multiple rows of data. When a multiline subquery is used in a WHERE clause, the multiline comparator (in, any, all) must be used.
a. When the IN operator is used IN a multi row subquery, the external query will try to match any one of the subquery results. As long as one of the results matches successfully, the external query will return the currently retrieved record.
stay emp In the table, the query is not a sales department( SALES)The specific code of the employee information is as follows: SQL> select empno,ename,job from emp where deptno in (select deptno from dept where dname<>'SALES');
b. Use ANY operator
The ANY operator must be used in conjunction with the single line operator, and the return line can match ANY result of the subquery.
stay emp In the table, you can query the employee information of other departments whose salary is greater than that of any employee in department No. 10. The specific code is as follows: SQL> select deptno,ename,dal from emp where sal > any (select sal from emp where deptno=10) and deptno <> 10;
c. Use ALL operator
The ALL operator must be used in conjunction with the single line operator, and the returned row must match ALL subquery results.
stay emp In the table, query the employee information whose salary is greater than that of all employees with department No. 30. The specific code is as follows: SQL> select deptno,ename,sal from emp where sal > all (select sal from emp where deptno=30);
3. Associated sub query
In single line sub query and multi line sub query, internal query and external query are executed separately, that is, the execution of internal query has nothing to do with the execution of external query, and external query is only the final result of internal query. In some sub queries with special needs, the execution of internal query needs the help of external query, and the execution of external query is inseparable from the execution of internal query. At this time, internal query and external query are interrelated, and this sub query is called associated sub query.
stay emp Table, using"Associated subquery"Retrieve the information of employees whose salary is greater than the average salary of the same position. The specific code is as follows: SQL> select empno,ename,sal from emp f where sal > (select avg(sal) from emp where job = f.job) order by job;
In the above query statement, the inner query uses the associated sub query to calculate the average salary of each position. The associated sub query must know the position name. Therefore, the outer query uses the f.job field value to provide the position name for the inner query, so as to calculate the average salary of a position. If the salary of the data row being retrieved by the outer query is higher than the average salary, the employee information of the row will be displayed.
In the process of executing the associated sub query, each record in the data table must be traversed. Therefore, if there are a large number of data records in the traversed data table, the execution speed of the associated sub query will be slow.
It should be added that the associated subquery can be used not only as a subquery of the SELECT statement, but also as an associated subquery of the INSERT, UPDATE and DELETE statements.
6. Operation database
Using SQL statements to operate the database includes not only query operations, but also data operations such as insertion, deletion and update. The last three data operations use SQL language (DML), which correspond to INSERT, DELETE and update statements respectively. In Oracle 11g, DML includes TRUNCATE, CALL, LOCKTABLE and MERGE statements in addition to the three statements mentioned above.
This section mainly introduces INSERT, UPDATE, DELETE, TRUNCATE and common DML statements.
6.1 INSERT data (INSERT statement)
Inserting data is to add data records to existing data tables. Oracle database inserts data records through INSERT statement. This statement can not only INSERT one record into the data table at a time, but also use the SELECT clause to INSERT the query result set into the data table in batch.
use INSERT The statement has the following precautions: (1)When adding data to a numeric column, you can provide a numeric value directly or enclose it in single quotes. (2)When adding data to a character column or date column, it must be enclosed in single quotation marks. (3)When adding data, the data must meet the constraint rules and must be the main key column and NOT NULL Columns provide data. (4)When adding data, the data must be consistent with the number and order of columns.
--Insert a single piece of data
Single insert data is INSERT The basic usage of the statement is as follows. INSERT INTO table_name [(column_name1[,column_name2]...)] VALUES(express1[,express2]...) (1)table_name:Indicates the name of the table to insert. (2)column_name1 and column_name2:Specifies the full or partial column name of the table. If the party has more than one column, the columns are separated by commas. (3)express1 and express2:Represents a list of values to insert.
When inserting data with the INSERT statement, you can specify either a column list or no column list. If you do not specify a column list, you must provide data for each column in the VALUES clause, and the data order must be exactly the same as the table column order. If you specify a column list, you only need to provide data for the corresponding column. The following also illustrates the method of adding single line data with an example.
a. Use the column list to add data.
In several ways of using INSERT statements, the most common form is to specify the column to add data in the INSERT INTO clause and provide a value for each column in the VALUES clause.
stay dept Table, using INSERT Statement to add a record. The specific code and running results are as follows. SQL> insert into dept(deptno,dname,loc) values(88,'design','beijing'); A row has been created.
In the above example, the column to add data is specified in the INSERT INTO clause, which can be either all columns or some columns of the data table. When specifying some columns, it should be noted that the columns that cannot be empty (NOT NULL) must be specified, and the corresponding assignment in the VALUES clause must not be NULL, otherwise the system will display the error message "cannot insert NULL".
b. Do not use column lists to add data.
When adding data to all columns of the table, you can also omit the list after the INSERT INTO clause. When using this method, you must provide data for all columns according to the order of the columns defined in the table. You can use the DESC command to view the order of the columns defined in the table.
c. Insert date values in a specific format
When adding date data, by default, the date value must match the date format and date language; Otherwise, an error message will be added when inserting data. If you want to insert date data in the usual way, you must use to_ The date() function.
Insert the date value in a specific format. The specific code is as follows:
SQL> insert into emp(empno,ename,job,hiredate)
values(1356,'MARY','CLERK',to_date('1983-10-20','YYYY-MM-DD'));
1 row created.d. Use DEFAULT to provide data.
Starting from Oracle Database 9i, when adding data, you can use DEFAULT to provide the DEFAULT value. When DEFAULT is specified, if the column has a DEFAULT value, its DEFAULT value will be used; If no DEFAULT value exists, NULL is automatically used.
use DEFAULT Provide data, and the specific code is as follows: SQL> insert into dept values(60,'MARKET',DEFAULT); SQL> select * from dept where deptno=60;
e. Insert data using alternate variables.
If you often need to INSERT data into a table, in order to avoid input errors, you can put the INSERT statement into the SQL script and use alternative variables to INSERT data into the table. If you often need to INSERT data into the emp table, you can use SQL script to INSERT data in order to avoid input errors. Script loademp SQL and running examples are as follows.
Write script file loademp.sql，Use this script to insert data. The specific code is as follows: First write the script file loademp.sql，The code is as follows. accept no prompt 'Please enter employee number:' accept name prompt 'Please enter employee name:' accept title prompt 'Please enter employee position:' accept d_no prompt 'Please enter the department number:' INSERT INTO emp(empno,ename,job,hiredate,deptno) values(&no,'&name','&title',SYSDATE,&d_no); Then use this script file to insert data. The code is as follows: SQL> @C:\loadem
---Batch insert data
INSERT Statement also has a powerful usage, that is, you can add a set of data to the table at a time, that is, insert data in batch. Users can use SELECT Statement replaces the original VALUES Clause so that SELECT Statement provides the added value. Its syntax format is as follows: INSERT INTO table_name[(column_name1[,column_name2]...)] selectSubquery a. table_name: Indicates the name of the table to insert. b. column_name1 and column_name2: Represents the specified column name. c. selectSubquery: Any legal SELECT Statement, the number and type of the selected columns should be the same as those in the statement column corresponding.
stay hr In mode, create a jobs Table with similar structure jobs_temp，Then jobs Maximum wage in the table(max_salary)Records greater than 1000 are inserted into the new table job_temp The specific codes are as follows: SQL> create table jobs_temp( job_id varcahr2(10) primary key, job_title varcahr2(35) not null, min_salary number(6), max_salary number(6) ); Table created. SQL> insert into jobs_temp select * from jobs where jobs.max_salary>10000; Created 9 rows.
It can be seen from the above running results that multiple records can be inserted into the specified data table at one time by using the combination of INSERT statement and SELECT.
It should be noted that when using this combined statement to realize batch data insertion, the column name specified in the INSERT INTO clause can be different from the column name specified in the SELECT clause, but the data types between them must be compatible, that is, the data returned by the SELECT statement must meet the constraints of the columns in the INSERT INTO table.
---Update data (UPDATE statement)
If the data in the table is incorrect or does not meet the requirements, it needs to be modified. Oracle database can modify existing data records through UPDATE statement.
When updating data, the number of columns to be updated can be specified by the user, and columns are separated by commas (,); The number of updates can be limited by the WHERE clause. When using the WHERE clause, the system only updates the record information that meets the WHERE conditions.
UPDATE The syntax format of the statement is as follows:
UPDATE table_name
SET {column_name1=express1[,column_name2=express2 ...]
|(column_name1[,column_name2...])=(selectSubquery)}
[WHERE condition]
(1)table_name: Indicates the name of the table to modify.
(2)column_name1 and column_name2 : Indicates the name of the specified column to update.
(3)selectSubquery: Any legal SELECT Statement, the number and type of the selected columns should be the same as those in the statement column corresponding.
(4)condition: Filter condition expression. Only records that meet the filter conditions will be updated.
use UPDATE The statement has the following precautions.
(1) When updating a numeric column, you can provide a numeric value directly or enclose it in single quotes.
(2) When updating a character or date column, it must be enclosed in single quotes.
(3) When updating data, the data must meet the constraint rules.
(4) When updating data, the data must match the data type of the column.1. Update single column data
When updating single column data, only one column needs to be provided after the SET clause.
stay scott In mode, turn emp The employee name in the table is SCOTT The recorded salary is adjusted to 2460, and the specific code is as follows: SQL> update emp set sal = 2460 where ename='SCOTT';
2. Update multi column data
When using the UPDATE statement to modify table row data, you can modify either one column or multiple columns. When modifying multiple columns, the columns are separated by commas.
stay scott In mode, turn emp The position in the table is salesperson(SALESMAN)Recorded salary increase of 20%，The specific codes are as follows: SQL> update emp set sal=sal*1.2 where job='SALESMAN'; Updated 4 rows.
In the above code, the number of records updated by the UPDATE statement is controlled through the WHERE clause. Here, it is restricted to UPDATE only the salary of the salesperson. If the restriction of the WHERE clause is cancelled, the system will increase the salary of all personnel in the emp table by 20%.
3. Update date column data
When updating date column data, the data format should match the date format and date language; Otherwise, an error message will be displayed. If you want to specify the date value in the customary way, you can use to_ The date() function.
stay scott In mode, turn emp The entry time of employee No. 7788 in the table is adjusted, and the entry time is changed to January 1, 1984. The specific code is as follows:
SQL> update emp set hiredate=TO_DATE('1984/01/01','YYYY/MM/DD') where empno=7788;4. Use the DEFAULT option to update the data.
When updating data, you can use the DEFAULT option to provide data. When using this method, if the column has a DEFAULT value, the data will be updated with the DEFAULT value; If there is no DEFAULT value for the column, NULL is used.
stay scott In mode, use DEFAULT Option update emp The employee name in the table is SCOTT The specific codes are as follows: SQL> select job from emp where ename='SCOTT'; SQL> update emp set job=DEFAULT where ename='SCOTT'; SQL> select job from emp where ename='SCOTT';
5. Update data with subquery
In addition, like INSERT statement, UPDATE statement can also be combined with SELECT statement to UPDATE data.
stay scott In mode, turn emp The salary of employees whose salary is less than 2000 in the table is adjusted to the average salary level of managers. The specific codes are as follows: SQL> update emp set sal=(select avg(sal) from emp where job='MANAGER') where sal<2000; 6 rows updated.
It should be noted that when the UPDATE statement and the SELECT statement are combined, it must be ensured that the SELECT statement returns a single value, otherwise an error prompt will appear, resulting in the failure of updating the data.
-----DELETE data (DELETE statement and TRUNCATE statement)
Oracle system provides the function of adding records to the database and deleting records from the database. DELETE statement and TRUNCATE statement can be used to DELETE records from data, but there are still great differences between the two statements.
1. DELETE sentence DELETE Statement is used to delete all records in the database and records in the specified range. To delete records in the specified range, the same as UPDATE Statement, to pass WHERE Clause, whose syntax format is as follows: DELETE FROM table_name [WHERE condition] (1)table_name: Indicates the table name of the record to be deleted. (2)condition: Filter condition expression is an optional option. When the filter item condition exists, only the records that meet the filter conditions will be deleted.
Delete qualified data: when using DELETE Statement to delete data by specifying WHERE Clause can delete data that meets the conditions. stay hr In mode, delete jobs Job number in the table(job_id)yes PRO The specific code is as follows: SQL> delete from jobs where job_id='PRO'; 1 row deleted. Delete all data of the table: when using DELETE When deleting the data of the table, if you do not specify WHERE Clause, all data in the table will be deleted. delete emp All data in the table, with specific codes as follows: SQL> delete from emp; 5 rows deleted.
When deleting data with the DELETE statement, the Oracle system will generate a ROLLBACK record, so this operation can be reversed with the ROLLBACK statement.
2. TRUNCATE statement
If the user decides to DELETE all records in the table, in addition to the DELETE statement, the TRUNCATE statement can also be used, and Oracle itself recommends using the TRUNCATE statement.
Using TRUNCATE statement to DELETE all records in the table is much faster than DELETE statement. This is because when the TRUNCATE statement is used to DELETE data, it will not produce a ROLLBACK record. Of course, the TRUNCATE statement operation cannot be revoked with the ROLLBACK statement.
stay hr In mode, use TRUNCATE Statement clear custom table jobs_temp For all records in, the specific codes are as follows: SQL> truncate table jobs_temp; Table truncated. SQL> select * from jobs_temp; No rows selected.
In addition, it should be added that the REUSE STORAGE keyword or DROP STORAGE keyword can also be used in the TRUNCATE statement. The former indicates that the space occupied by the deleted record line is still saved, and the latter indicates that the space occupied by the record is recovered immediately after the record is deleted. By default, TRUNCATE statements use the DROP STORAGE keyword.