Oracle Database entry-level query Basics

Simple Table operations

Command: create a simple table: createtable student (name varchar2 (20), age number (3 ));

Insert new record: insert into studentvalues ('stone ', 24 );

View the table structure: desc student;

Query record: select * from student;

Drop table student;

Delete table data: the delete from studentwhere condition;

Note: SQLPlusWorksheet uses "--" as a single line comment mark. Varchar2 (20) indicates a variable-length string, up to 20

Number (3) indicates that age is a three-digit integer that does not contain the decimal point. If only number is defined, the system defaults to a 10-digit integer.

Single quotation marks are used to represent string constants in SQL. Keywords in SQL are case insensitive.

Database developers prefer to use uppercase keywords. Most application developers use lower-case letters for better readability.

After a record is inserted, it does not take effect immediately in the database. You can use commit to submit data update operations.

Simple Select statement

Syntax format: SELECT * | {<field Name>,...} FROM <Table Name>; -- fields are separated by commas. Adding spaces after commas can enhance readability.

For example, select * from emp; or select empno, ename, sal from emp;

Display Effect: SQL * plus default display: Date and character data are left aligned, Numeric data is right aligned, and Column Titles are displayed in uppercase by default.

ISql * plus default display: the column title is displayed in uppercase by default, and the column title is centered and aligned by default.

Use arithmetic expressions

Overview: in Select statements, you can use Arithmetic Operators (+,-, *,/) to create expressions for NUMBER-type data.

Example: select empno, ename, sal, sal * 12 from emp; -- query the employee ID name salary and annual salary in emp.

Select empno, ename, sal, sal * 12 + 1000 from emp;

Select empno, ename, SQL, sal * (12 + 1000) from emp; -- you can use parentheses to change the computing sequence.

Join Operator

Overview: "|" can be used to connect a column with a character or other expressions to obtain a new string and implement the "synthesize" column function.

Example: select ename | 'is a' | job from emp;

Select empno, ename | ''s annual salary is '| sal * 12 from emp;

Note: A single quotation mark represents the separator mark of a String constant. Two single quotes are used to represent a single quotation mark character in a String constant.

During query, there is no need to escape characters. directly use two single quotes to represent a single quotation mark in a String constant.

Use Field alias

Overview: Rename fields in the query results to improve readability. Double quotation marks are required if special characters are used in the alias or the output is case-insensitive.

Format: SELECT <field Name> | <expression> [[AS] <field alias>],... FROM <Table Name>;

Example: select empno, ename "Ename", sal * 12 "annual salary" from emp;

Select empno as employee ID, ename employee name, sal * 12 "annual salary" from emp;

Note: as can be skipped. Aliases can be enclosed in double quotation marks. If the alias does not contain special characters, double quotation marks can be omitted.

If the alias contains spaces, such as "yearly salary", double quotation marks must be added. Otherwise, the alias will be parsed into two components, resulting in Syntax problems.

If Ename is not enclosed in double quotation marks, it is displayed as ENAME after execution. It will only be displayed in case-sensitive format after double quotation marks are added.

Null Value

Summary: NULL values are invalid, unspecified, unknown, or unpredictable values. NULL values are not equal to 0 or spaces.

Example: select empno, ename, sal, comm from emp; -- after execution, the comm attribute of some records is displayed blank, that is, the null value.

Insert into student (age) values (24); -- when inserting data, if the name value is not specified, the default name is null.

Supplement: If a null value exists in an arithmetic expression, the result of the entire expression is null.

The Null Value in the join expression is treated as an empty (zero-length) string.

For example, select ename, sal, comm, ename | '-' | comm, sal + comm from emp;

If null values are involved in arithmetic expressions, you can use the corresponding function to set the default values for fields that may have null values.

After judgment, if it is a null value, 0 is returned. If it is not a null value, the true value is returned, and then the arithmetic operation is performed.

Remove duplicate rows

Overview: by default, all matching record rows, including duplicate rows, are displayed in the query results. You can use DISTINCT to clear duplicate rows.

The scope of DISTINCT is the combination of all the following fields, that is, the combination of the following fields can be avoided.

Example: select deptno from emp; -- the type of the Department number is queried, but all numbers, including duplicate numbers, are displayed.

Select distinct deptno from emp; -- clear duplicate Department numbers in the query results

Select distinct deptno, job from emp; -- as long as the results of deptno and job combination are not repeated, the output is displayed

Note: Generally, operations such as data query, combination, filtering, and conversion are handed over to the database for processing.

For example, grouping statistics, summation, averaging, filtering null values, filtering duplicate values, filtering spaces at the beginning and end of data, and converting string to date type.

We recommend that you process complex business logic or business logic-related content in your application. This achieves loose coupling.

Sort query results

Overview: query results are arranged by record insertion order by default. You can use the order by clause to sort the query results.

The sorting method can be ascending (ASC, default) or descending (DESC. You can also sort by field alias or by multiple fields

Example: select empno, ename, sal from emp order by sal; -- sort by sal in ascending order, ASC can be omitted without writing

Select empno, ename, sal from emp order by sal desc; -- sort by sal in descending order

Select empno, ename, sal * 12 annsal from emp order by annsal;

Select deptno, empno, ename, sal from emp order by deptno, sal;

Note: multiple fields are sorted by deptno first. If duplicate deptno records exist in the result

Repeat the deptno record by sal. As for the specific sorting method, you need to separately specify the two fields

For example, deptno, sal indicates that both are in ascending order. Deptno, sal desc indicates that deptno is in ascending order, and sal is in descending order.

For example, deptno desc, sal indicates that deptno is listed in descending order, and sal is listed in ascending order. And so on

This logic is suitable for the database to process in the query phase. It is difficult to implement the Code in the application.

Conditional Query

Note: When a string or date is used in a query statement or query condition, the string and date value must be enclosed in single quotes.

String is case sensitive. Date value format sensitive, default date format is DD-MON-RR

Note: All cases involving string content are case sensitive.

Format: SELECT * | {[DISTINCT] <field Name> | <expression> [<alias>],...} FROM <Table Name> [WHERE <query condition>];

Example: select * from emp where ename = 'Smith '; -- the SMITH to be queried is case sensitive.

Select * from emp where hiredate = '02-August 1-81 '; -- the system automatically converts a string in the specified format to the Date type.

Select sysdata from dual; -- get the current system time in the default date format

Note: The table dual has only one row and one column. It does not store the system time. sysdate always represents the current system time.

Generally, when performing simple queries or operations, the dual table acts as a medium, making the form look like a query

The advantage of dual is that there is only one line of record, which is generally used to test certain commands during exercises. It is generally not used in real development.

For example, select 3 + 2 from dual; 5 is returned. Another example is select 3 + 2 from emp; 5 of multiple rows is returned.

It queries every row of records in emp, but does not query the actually saved content. Instead, it calculates 3 + 2 records row by row.

Then, take the result 5 as a unique result and return a unique field. Therefore, the result is 5 of more than 10 rows.

For an Oracle database, even if you want it to perform a simple constant expression operation, you must use the Q & A form.

Even if all the data is in the user's hands at this time, pretend to be a query and give it a command to return the result.

Relational operators

Symbol: BETWEEN... AND...: boundary BETWEEN two values, including boundary, AND a small value must be placed in front

IN (set): appears IN the set. A record that determines whether a field is equal to the value in the Set

LIKE: fuzzy query, also called wildcard Query

Is null: determines the NULL Value

Example: select * from emp where sal between 1600 and 2900; -- query the information of employees in the wage range between 1600 and 2900.

Select * from emp where ename in ('Smith ', 'King', 'King'); -- returns the record whose name is the value of the element in the set.

Select * from emp where comm is null; -- returns a record with null values for all comm fields.

Select * from emp where comm is not null; -- Return non-null. Note that 0 and null are different.

Note: All cases involving string content are case sensitive. That is, the value in () must match the ename value to query the record.

LIKE: % Represents zero or multiple characters. _ Represents a character. You can use ESCAPE to identify special symbols.

Select * from emp where ename like's % '; -- query records with names starting with S. S is case sensitive.

Select * from emp where ename like '_ A %'; -- query the record of the name with the second letter.

Select * from emp where ename like '% _ % 'escape'; -- returns a record with an underscore in the name.

Here, the escape marks "" as an escape character. In actual development, there are not many escape characters.

Escape characters in Oracle can be defined at will, so "" In the example can be replaced with other symbols, such as "K"

Note: A single quotation mark represents the separator mark of a String constant. Two single quotes are used to represent a single quotation mark character in a String constant.

During fuzzy query, there is no need to escape characters. directly use two single quotes to represent a single quotation mark in a String constant.

Logical operators

Overview: priority from high to low: NOT → AND → OR

Example: select * from emp where deptno = 20 and sal> 2000; -- return the record with the department number 20 and the salary above 2000

Select * from emp where deptno = 20 or sal> 2000; -- returns the person with the department number 20 or the record with the salary greater than 2000.

Select * from emp where deptno not in (20, 30, 40); -- returns the information of the person whose department number does not appear in the Set