SQL introduction and query

1. SQL statements

Is a language

Meets ANSI standards

Keyword cannot be abbreviated

Use statements to control the definition information and data of tables in the database

SQL is the abbreviation of Structured Query Language (Structured Query Language. You can use SQL statements to create or delete database objects, insert, modify, and update database data, and perform various daily management operations on the database. It is the standard language for all relational database management systems. In other words, you can use SQL to operate all relational databases.

2. SQL * Plus command

Is an environment

One of the features of Oracle

Abbreviations of keywords

Command cannot change the value of data in the database

Centralized Operation

3. Use SQL * Plus:

Describes the table structure.

Edit an SQL statement.

Execute SQL statements.

Save the SQL statement in the file and save the SQL statement execution result in the file.

Execute the statement in the saved file.

Load the text file into the SQL * Plus editing window.

4. SQL query

1. data Type: data is a digital representation of information. The processing and processing of information is based on a large amount of structured data. The core of the database management system is the database, and the main object of the database is the table, A table is a place where structured data is stored.

The Oracle System also provides a large number of data types, including:

1. User-Defined Data Types

2. built-in data types

Character type: the character data type can be used to declare fields that contain multiple letters and numbers.

1. fixed-length Character Types

CHAR: used to store characters with a fixed length. If the declared length is fixed (no matter the actual size you store, but the size must not be greater than the declared length), spaces are used to fill in the length. The default size of 1B. the maximum size is 2000b.

Nchar and Char are parsed in the same way. However, ncarh stores UNICODE character data.

2. variable-length Character Types

Varchar is similar to Char, but it is used to store variable strings, while char is used to store fixed strings.

Nvarchar is the same as varchar parsing, but nvarchar is used to store data bits.

Double-byte data.

Date type

Date short date format (1990-10-10)

Timestamp long Date Format (10:10:10)

Number Type

Number (2), which represents a two-digit integer.

Number (3, 2) indicates that it is declared as a three-digit number, and there are two digits after the decimal point.

Text-Lob Data Type

BLOB: stores images, audio files, videos, and other files.

Clob: a large object in character format. It is encoded in the unicode format of ORACLE data.

Bfile: used to store binary files.

Rowid: Specifies the physical address used to store each record in an oracle table.

2. SQL is classified by function:

Database Definition Statement:

DDL (Data Definition Language): used to create, modify, and delete database objects.

Database Operation Language

DML (data manipulation language) is used to query, add, modify, or delete data in database objects.

Database Control statement DCL

DCL (Data Control Language) is used to control users accessing specific objects in the database, grant revoke

In the Oracle system, SQL language extensions are called PL/SQL languages.

Basic SQL statements

Select * | {[distinct] column | expression [alias],...}

From table;

3. condition (s:

Operator meaning

= Equal

> Greater

> = Greater than or equal

<Less

<= Less than or equal

<> Not equal! =

Other operations

And logic operation logic and

Or logic operation logic or

Not logical operation logic No

Between start value and end value: Use the between operation to display the value in a range (start and end value)

In: displays the values in the list using the in operation.

In (,) is equivalent to a set. If a set matches, it is displayed.

Like: select a similar value using the like operation.

The selection conditions can contain characters or numbers:

% Represents zero or multiple characters (any character ).

_ Represents a character.

Escape: Escape special characters: Use escape characters. For example, convert [%] to [\ %], [_] to [\ _], and then add [escape '\'].

Null: null value is determined using is (not) null.

4. Operator priority table:

Priority

1 arithmetic operation

2 Connector

3 comparison operator

4 is not null like not in

5 not

6 not

7 and

8 or

Note: brackets can be used to change the priority.

5. query statement (basic query, alias query ):

1. query all data in a table.

SQL> select * from EMP;

Queries special columns in a table.

SQL> select hiredate from EMP;

Hiredate

-----------

1980-12-17

1981-2-20

1981-2-22

1981-4-2

2. query by alias

SQL> select hiredate as date from EMP;

Date

-----------

1980-12-17

1981-2-20

1981-2-22

1981-4-2

Note: As can also be omitted.

SQL> select e. hiredate from EMP E; // table alias

Hiredate

-----------

1980-12-17

1981-2-20

1981-2-22

1981-4-2

6. Conditional Query

Case: The employee information department with Department Number 10 is an integer.

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where deptno = 10;

The type of the employee information position whose job number is manager is character type

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where job = 'manager ';

Case: query the information of employees whose team number is 10 and their positions are manager.

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where job = 'manager' and deptno = 10;

Note: characters and dates must be included in single quotes.

The characters are case sensitive and the date format is sensitive.

The default date format is dd-mon-rr.

Case: Query Information of employees with Department numbers greater than 10

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where deptno> 10;

Case: Query Information of employees whose salaries are greater than or equal to 3000

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where Sal >= 3000;

Case: Query Information of employees whose salaries are not equal to 3000

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where Sal <> 3000;

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where Sal! = 3000;

Case: Query Information about employees with salaries greater than 2000 and smaller than 3000

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where SAL> 2000 and Sal <3000;

Case: Query Information about employees with salaries greater than or equal to 2000 and smaller than or equal to 3000

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where Sal Between 1600 and 3000;

// Equivalent

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where SAL> = 1600 and Sal <= 3000;

Case: query the employee information of Department number (10, 20)

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where deptno in (10, 20 );

Note: This operation is equivalent to the following:

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where deptno = 10 or deptno = 20;

Case: Query Information of employees whose names start with "S"

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like's % ';

Case: Query employee information whose name ends with "S"

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like '% s ';

Case: Query employee information whose third character is n

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like '_ n % ';

Case: Query Information about employees whose names contain N

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like '% N % ';

Case: Query employee information with the second to last character % in employee name

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like '% \ % _ 'escape '\';

Case: query the total number of employees whose names contain %

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where ename like '% \ %' escape '\';

Case: query the employee information whose bonus value is null

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where comm is null;

Case: Query employee information with non-null bonuses

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where comm is not null;

7. SQL insert statement:

1. Insert a new (full field) record into the table

SQL> insert into EMP (empno, ename, job, Mgr, hiredate, Sal, comm, deptno) values (1111, 'test', 'test', '123 ', to_date ('1970-12-12 ', 'yyyy-mm-dd'), 1992, 20 );

Or write it

SQL> insert into EMP values (2222, 'test', 'test', '000000', to_date ('2017-12-12 ', 'yyyy-mm-dd'), 7369, 20 );

2. You can see the results after all queries.

SQL> select * from EMP;

3. Insert a new (partial field) record into the table

SQL> insert into EMP (empno, ename, hiredate, deptno) values (3333, 'test', to_date ('2017-12-12 ', 'yyyy-mm-dd '), 30 );

Note: if there is an association during the insert operation, you must insert the values of the related fields.

Note: After insertion, you must manually submit the commit;

SQL> commit;

Commit complete

8. Order by clause

Sort by the order by clause. The order by clause is at the end of the SELECT statement. The default value is litre.

Ordered (ASC) Query

ASC (ascend): ascending

DESC (descend): Descending

Case: Query employee information in ascending order by Department number. The default value is ASC.

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP order by deptno;

Case: DESC

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP order by deptno DESC;

Case: query the information of employees whose department is 20 and sort the information in ascending order according to the employee ID

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP where deptno = 20 order by empno ASC;

Note: It can be used together with the condition statement, but the order by clause is placed at the end.

Case: query the employee information and sort the employee's Department numbers in ascending order.

SQL> select empno, ename, job, Mgr, hiredate, Sal, comm, deptno from EMP order by deptno ASC, empno DESC;

Note: Understanding: first, query the employee information in ascending order by Department number, and then sort the employees in each department in descending order by employee number.