Simple review of SQL statements

1.Intersect

Form:

Statement 1 INTERSECT Statement 2

Results: Take 1, 2 intersection

Like union, Union is a union, and intersect is the intersection.

2.Minus

Form:

Statement 1 minus Statement 2

Result: Select only the elements that are specific to statement 1

3.Concatenate concatenated string

Form: Concate (String 1, String 2 ....) )

Result: string concatenation display

Note: Concate can only concatenate two parameters in Oracle, but we can use | | To concatenate multiple strings,

Use the + concatenation string in SQL sserver.

4.substring is used to grab a portion of a profile that has a slightly different name from the database.

In Oracle: SUBSTR ()

In SQL Server: SUBSTRING ()

Form:

SUBSTR (Str,pos, Len)//() The Parameters are field names, starting positions, ending

Results:

Displays the specified character in the specified field.

5.Trim used to remove a word or a word from a string.

Mysql:trim (), RTRIM (), LTRIM ( )  

Oracle:rtrim (), LTRIM ()

SQL Server:rtrim (), LTRIM ()

Form:

TRIM ([[position] [string to remove] from] string

The 6.Creat View viewing table (views) can be viewed as a virtual table. It differs from the table in that there are actual data stored in the table, and that the view table is an architecture built on top of the table, which itself does not actually store the data.

Form:

CREATE VIEW "View_name" as "SQL statement"

7.Creat Index Creating indexes

Form:

CREATE INDEX "index_name" on "table_name" (column_name)

Attention:

The name of the index does not have a fixed way. The usual way to do this is to add a header to the name, such as "idx_", to avoid confusion with other objects in the database.

8.Alter Table Structure Modification

Common changes: Add and Subtract fields

Change field name, category

Form:

ALTER TABLE "table_name" add/change/modify/drop Field Name property

9.Primary Key Primary Key

Form: Add Primary Key directly after the field name you created.

To change an existing structure table to set the primary key:

ALTER table name ADD PRIMARY KEY (field name);

10.Foreign Key FOREIGN Key

Form: add Foreign Key directly after the field name you created

For example, suppose we have two tables: a Customer table that records all of the customer's data, and another Orders table that records all the customer's orders. One of the limitations here is that all the customers in the order information must be in the Customer table. Here, we will set a foreign key in the ORDERS table, and this foreign key is the primary key in the CUSTOMER table. In this way, we can determine that all customers in the ORDERS table exist in the Customer table. In other words, there cannot be any customer in the Orders table that does not exist in the Customer table.

11.Drop Table Delete Tables

12.Truncate table content is cleared and the structure is still

Form: Truncate table name

Simple review of SQL statements