SQL language basics

SQL language classification

1. Data Definition Language (DDL)
Common DDL statements
Create Table create database object
Create index create database table Index
Drop table delete database table
Drop index delete database table Indexes
Truncate delete all industries in the table
 
Add a table column in alert table, redefine the table column, and change the storage allocation.
Alert table add constraint add constraints to existing tables

2. Data manipulation language (DML)
Insert to Add rows to the table
Delete Delete A data row from a table
Update changes table data
Select to retrieve data rows from a table or view

3. Data Control Language (DCL)
DCL is used to specify various permissions of database users.
 
Grant permissions or roles to users or other roles
Revoke revokes permissions from a user or database role
Set role disables or allows a role

4. Database Transaction Control
 
Common transaction statements include:
 
Commit work changes the current transaction to permanent (written to disk)
Rollback invalidate all changes since the last submission

Basic SQL statement syntax
 
Each SQL statement must end with a semicolon.

Each SQL statement can be written into a single row, but can be divided into several rows for clarity.

SQL statements are case-insensitive and keywords (such as insert and select) of SQL statements ),

Table Name and column name, which can be case-insensitive.

Alias

Data Table name as data table alias
Or:
Data Table name data table alias

Eg:
Use sample
Select E. employee ID, E. employee name
From employee data table as E

Select statement
The basic structure of the SELECT statement is as follows:
Select select_list
[Into new_table_name]
From table_list
[Where search_conditions]
[Group by group_by_list]
[Having search_conditions]
[Oder by order_list [ASC | DESC]
 
1. Distinct keywords
Eg:
Use sample
Department of select distinct
From employee data table
2. Top keywords
 
From clause
Join condition --- On clause
Derived table --- sp
 
Select employee data table. employee ID, project data table. Project Name
From employee data table JOIN project data table on
(Employee data table. employee ID = project data table. Owner)
 
Select EMP. employee ID, EMP. employee name, sp. Department name
From employee data table as EMP,
(Select Department data table. Department ID, Department data table. Department name
From Department data table
Where Department data table. Department No.> 2
) As SP
Where EMP. Department No. = sp. Department No.

Where clause
The query and Restriction Conditions in the WHERE clause can be:
Comparison operators (such as regular =, <>, <and> ).
Description of the range (between and not ).
Optional list (in, not in)
Pattern Matching (like and not like ).
Whether it is null (is null and is not null ).
Logical combination of the preceding conditions (and, or, and not ).

Group by clause
The group by clause can be used to divide data records into multiple groups based on the set conditions,
And only group by is used.

Clause, the aggregate function used in the SELECT statement (for example, sum, Count, Min, Max, etc)
.
Eg:
Select department, AVG (salary) as average salary from employee data table group
Department
Having keyword
The having clause filters the results selected by the Group by clause again, and finally outputs

Records of conditions in the having clause.
Eg:
Select department, AVG (salary) as average salary from employee data table group
Department having average salary> 2000
 
The where clause is used to filter records generated by the operations specified in the from clause.
The group by clause wakes up the results in the WHERE clause.
The having clause filters records from the intermediate results after grouping.

All keyword
Cube keywords
The with cube keyword is used to automatically list fields in the group by clause.
Group aggregation.
Eg:
Select department, gender, AVG (salary) from employee data table group
Department, gender with cube
 
Rollup keyword
 
Order by clause
 
ASC --- ascending
Desc -- descending order

Compute and compute by clauses
 
Use union clause
The Union operator can be used to combine two or more query result sets to form a result set.

. All result sets using the Union operator must meet the following conditions:
With the same structure
The number of fields is the same.
The data type corresponding to the result set must be compatible.
The format specified by the Union operator is as follows:
Select statement
Union [all]
Select statement

Aggregate functions
Sum Function
AVG Functions
Count Function
Count (*) Function
Max Functions
Min Function

Join Query
You can query two or more data tables based on the logical relationship between data tables.
Search data in a data table.

How to improve the efficiency of select statements
(1). Use the exists keyword to check the result set.
(2). Use standard connections instead of nested queries
(3). effectively avoid full table Scan

Join type

Inner join
The format of inner join is:
Data Table 1 inner join data table 2 on join expression
The internal Join Operation uses the comparison operator to join the public fields of the data table as needed.
Value to match records in two tables.
Eg:
Select * from employee data table inner JOIN project data table
On employee data table. employee ID = project data table. Owner
 
Outer Join
The outer join includes three types: left Outer Join, right outer join, and complete outer join.

The format of the left Outer Join is:
Table 1 left join table 2 on join expression
Or:
Table 1 left Outer Join table 2 on join expression
The result set that uses the left join for query will include all records in Table 1, instead of just connecting

If the field matches the record, all fields related to table 2 in the result set will be null.

For example, the following will retrieve all records in the employee data table and match the field of the person in charge in the project table

Output records to the result set:
Select *
From employee data table left jion project data table
On employee data table. employee ID = project data table. Owner

Right Outer Join
Format of the right outer join:
Data Table 1 right join data table 2 on Expression
Or:
Data Table 1 right Outer Join data table 2 on Expression
Opposite to left Outer Join.

Complete Outer Join
Complete Outer Join format
Data Table 1 full join data table 2 on Expression
Or:
Data Table 1 full outer join data table 2 on Expression
 
Cross join
The format of the Cross-join is:
Data Table 1 cross join data table 2
If the WHERE clause is not used in the SELECT statement, the data table 1 and

The cardinar product of table 2, that is, the cross join operation returns all records in Table 1 and all records in table 1.

Some records are combined with all sentence paths in Table 2. The number of records in the result set is equal to that in table 1.

The number is multiplied by the number of records in table 2.

Specify the join in the from and where clauses.

Nested Query
Nested query refers to an outer query that contains an inner query. The outer query is called the primary query.

Query. An inner-layer query is called a subquery.
Use the in and not in keywords
Use sample
Select employee name
From employee data table
Where employee ID in
(Select distinct owner
From project data table
Where end date <'january 2006, 100 ')
Use comparison Operators
Returns a single value.
Use sample
Select employee name
From employee data table
Where employee ID =
(Select distinct owner
From project data table
Where project name = 'demo1 ')
Use the exists and not exists keywords

Data Change
Insert
Update
Delete