Step into the "Database" auditorium SQL database Basic Skills: Statement tutorial

This article comprehensively describes the SQL syntax. Whether you are a newbie or a veteran of SQL, you will have a general understanding of SQL. In addition, you will be able to correctly use SQL to obtain information from the database. Although a thorough understanding of SQL is not completed overnight, it is not difficult to have a basic understanding of SQL. I,

This article comprehensively describes the SQL syntax. Whether you are a newbie or a veteran of SQL, you will have a general understanding of SQL. In addition, you will be able to correctly use SQL to obtain information from the database. Although a thorough understanding of SQL is not completed overnight, it is not difficult to have a basic understanding of SQL. I,

This article comprehensively describes the SQL syntax. Whether you are a newbie or a veteran of SQL, you will have a general understanding of SQL. In addition, you will be able to correctly use SQL to obtain information from the database. Although a thorough understanding of SQL is not completed overnight, it is not difficult to have a basic understanding of SQL.

I. Table processing:

Basic architecture of data storage in table-based databases, which includes column and row

CONSTRAINT: Restricted Storage
NOT NULL: NULL values are not allowed.
UNIQUE: Restrict the materials in each column to be different
CHECK: Ensure that all data in each column meets certain conditions.
Primary Key(PrimaryKey): Make sure that the field used as the primary key is set to [not null].
Foreign key(ForeignKey): one or more columns pointing to the primary key of another table. The purpose is to determine the integrity of the reference data. That is, only permitted data values are stored in the database.

Key combination: CompositeKey

Create table: when defining a TABLE, you need to define the data types of each column

Create table "TABLE name" ("column 1" "column 1 Data Type", "column 2" column 2 Data Type "......)

Create table "TABLE name" ("column 1" "column 1 Data Type") reference customer (column 1) alter table "TABLE Name 1' add foreign key (column name of TABLE 1) reference customer (column name of TABLE 2)

Create table Name ("column 1" "column 1 Data Type" Primary Key) alter table name add primary key ("column 1 ")

CREATE VIEW: A view can be used to connect two tables to identify the required information. A view is a virtual table, which is an architecture built on a table and does not actually store data.

Create view "VIEW name" as "SQL statement"

CREATE INDEX: Quickly search for required information, which is conducive to system efficiency. You can add an initial letter or table name and column name before the name.

Create index "index name" ON "table NAME" (column name)

Alter table:

A) add a column:

ADD "Column 1" "Column 1 data type"

B) delete a column:

DROP "column 1"

C) Change the column Name:

CHANGE "original column name" "new column name" "new column data type"

D) Change the column category:

MODIFY "Column 1" "new data type"

Drop table:Clear a table from the database

Drop table "TABLE name"

TRUNCATE TABLE: Clear all data in a table from the database, but the table continues to exist.

Truncate table "TABLE name"

INSERT: Input multiple pieces of data at a time. The SELECT command inputs the data in Table 2 to table 1.

Insert into "table name" ("column 1", "column 2 ",......) VALUES ("value 1", "value 2 ",......) Insert into "table 1" ("column 1", "column 2 ",......) SELECT "column 3", "column 4 ",...... FROM "Table 2"

UPDATE:Modify the data in the table:

UPDATE "table name" SET "column 1" = [new value] WHERE {condition}

DELETE FROM: Some data is removed from the database.

Delete from "table name" WHERE {condition}

Ii. SQL commands:
How SQL is used to store, read, and process data.


1, SELECT:

SELECT "column name" FROM "table name"

2, DISTINCT: Exclude the same point value

Select distinct "column name" FROM "table name"

3, WHERE:Conditional data selection

SELECT "column name" FROM "table name" WHERE "condition"

4, AND/OR: Connection with Complex Conditions

SELECT "column name" FROM "table name" WHERE "simple condition" {[AND/OR] "simple condition"} +

5, IN:Select data within the specified range

SELECT "column name" FROM "table name" WHERE "column name" IN ('value 1', 'value 2 ',......) WHERE "column name" = 'value 1'

6.:Select data within the specified range

SELECT "column name" FROM "table name" WHERE "column name" BETWEEN "value: 'and' value: 2'

7, LIKE:Pattern Matching

SELECT "column name" FROM "table name" WHERE "column name" like {mode}

A) A_Z: All characters starting with A and ending with Z.
B) ABC %: indicates all strings starting with ABC.
C) % AN %: indicates all strings containing the AN pattern.
8, ORDER: Displays the captured data in ascending or descending order.

SELECT "column name" form "table name" WHERE "condition" order by "column name" [ASC/DESC] order by "column 1" [ASC/DESC], "column 2" [ASC/DESC]

9. function:
Numeric operations: AVG (average), COUNT (COUNT), MAX (maximum), MIN (minimum), SUM (total)
Mathematical operations: Addition +, subtraction-
Text: CONCATENATION connected to text, TRIM modified by text, and substring
10, group:This command is required when at least one column contains function operations.

SELECT "column 1", SUM ("column 2") FROM "table name" group by "column 1"

11, HAVING:Set conditions for the value generated by the function

SELECT "column 1", SUM ("column 2") FROM "table name" group by "column 1" HAVING (function condition)

12, ALIAS:Alias, table alias or column alias. Generally, the alias is placed behind the object to be replaced, separated by a space in the middle.

SELECT "table alias". "column 1" "column alias" FROM "table name" "table alias"

13. Table link:
A) External link outer jion: the two tables are not necessarily associated. In the WHERE clause, select the table with all the data and add a plus to indicate that all the data in the table is required.
B) internal link INNER JION: LEFT join LEFT JION is selected only when the two tables have the same value.
14,
CONCATENATE: Concatenates the data obtained from different columns. +
SUBSTRING: Capture part of a column.
15, TRIM: Removes the header or end of a string. It is common to remove white spaces at the beginning or end of a string.

TRIM ([[location] [string to be removed] FROM] string)

[Location] possible values: leading start, traling end, both start, and end
A) LTrim (string): removes the white space from the start of the string.
B) RTrim (string): removes the white space at the end of the string.