Let’s talk about SQL commands, table processing and summary of advanced SQL knowledge points.

This article brings you relevant knowledge about the database language SQL. It mainly introduces issues related to SQL instructions, table processing and SQL advanced skills. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

##1. SQL command

SQL instructions refer to how SQL is used to store, read and process table data in the database.

Common SQL instructions include the following:

1.1 SELECT: Select

(1) Function: Select data from the database table. Ability to read all data in one or several columns in a table.

What is a table? Tables are structures within a database whose purpose is to store data.

(2) Statement: select column name 1, column name 2... from table name

1.2 DISTINCT: Distinguish (select)

# (1) Function: When processing data, you need to find out the different data values ​​​​in the table, that is, you need to know what is in the column distinct values, regardless of the number of times each value appears. Use distinct at this time.

(2) Statement: select distinct column name from table name

1.3 WHERE: with query Condition

(1) Function: When you need to conditionally selectively query some information, use where

( 2) Statement: select column name from table name where condition

1.4 AND OR: AND, OR (connection condition)

(1) Function: The complex condition of where is composed of two or more simple conditions connected by and or or. There can be an infinite number of simple conditions in a SQL statement.

(2) Statement: select column name from table name where simple condition 1 and|or simple condition 2...

1.5 IN: Taking values ​​in a discontinuous range (coordinated with where)

(1) Function: in clause, usually followed by where Used together, it means that the condition takes values ​​in a discontinuous range

(2) Statement: select column name from table name where column name as condition in (value 1, Value 2, value 3...) exists

(3) Supplement: If there is only one value in the brackets of the in clause, it is equivalent to... where column name = Value 1

1.6 BETWEEN: Take a value in a continuous range (coordinated with where)

(1) Function: The in command is to obtain the value in the database within the limit of one or several discontinuous values, while the between command is to obtain the value in the database within a continuous range. Usually used in conjunction with where.

(2) Statement: select column name from table name where column name between value 1 and value 2

1.7 LIKE: With search mode (cooperated with where)

(1) Function: Find the required data based on a pattern. Usually used in conjunction with where.

(2) Statement: select column name from table name where column name like {mode}

(3 ) {Mode}:

For example, A_B means that A starts with A and ends with B, and there can only be one character in the middle. ABC% means the string starting with ABC, �C means the string ending with ABC, � C% represents a string containing the pattern ABC.

1.8 ORDER BY: Sorting

(1) Function: Make a systematic display of the acquired data , that is, sorting display, positive order or reverse order.

(2) Statement: select column name from table name where condition order by column name [ASC, DESC]

(3) [ ] means optional, not required. ASC means from small to large, DESC means the opposite. ASC is used by default.

(4) Note: If there exists where, then where needs to be in front of the order by clause.

(5) Supplement: Several column names can be sorted at the same time. order by column name 1 [ASC, DESC], column name 2 [ASC, DESC]. If you choose to sort both columns from small to large, the result of this clause is: sort column name 1 from small to large. If there are several query results with the same column name 1, then these results will be arranged in ascending order according to column name 2, and so on.

1.9 Function

(1) Function: Many contents in the database exist in the form of numbers. When performing operations on these numbers, they can be run directly through some defined functions.

(2) Common functions:

##AVG: average value

COUNT: Count

MAX: Maximum value

MIN: Minimum value

SUM: Sum

(3) Statement: select function name (column name) from table name

1.10 COUNT: count

（ 1) Function: Count, count the total number of eligible entries found in the table.

(2) Statement: select count(column name) from table name

(3) Note:count is often used together with distinct to find out how many distinct entries there are in the table.

1.11 GROUP BY: Classification

(1) Function: When we select more than one column name And when at least one of the column names contains the application of a function, you need to use the group by command. In this case, we need to make sure we have group by all other column names. That is: In addition to the column name including the function, it needs to be placed in the group by clause.

(2) Statement: select column name 1 sum(column name 2) from table name group by column name 1

1.12 HAVING:

(1) Function: Set conditions for the value generated by the function . The having clause is at the end of a SQL sentence.

#A SQL that contains a having clause does not necessarily contain a group by clause.

(2) Statement: select column name 1, sum (column name 2) from table name group by column name 1 having (function condition)

(3) Note: If only the function column is selected, then the group by clause is not needed.

1.13 ALIAS: Alias ​​(for tables or columns)

(1) Function: Function for tables and columns Alias.

Aliases for column names: This is to make the results generated by SQL easy to read.

Table alias: Just leave a space after the table name in the from clause, and then list the table alias to be used.

That is: these two aliases are placed after the table name or column name to be replaced, and are separated from the table name or column name by spaces.

(2) Statement: select table alias.Column name 1 column name 1 Column alias from table nameTable alias

(3) Note: The column alias can not only be placed directly after the column name, but also after the function, such as...sum(column name) column Alias ​​...

1.14 Table connection

(1) Function: Through two tables The same columns connect the two tables.

The where clause plays an important role in table connection.

If you use where incorrectly, you are likely to get a Cartesian join (all possible combinations between every two rows of the two tables).

1.15 External connection

(1) Function:

The left join we often call is an internal join. In the case of internal connections, both tables need to have the same value before that piece of data will be selected.

If you want to list every piece of information in a table, regardless of whether its value appears in another table. At this time, an external connection is required: SQL OUTER JOIN.

(2) Statement: The syntax of external connections varies depending on the database. For example, in Oracle, add ( ) after the table where all data is to be selected in the where clause to indicate that we need all the data in this table.

(3) Note: When the second table does not have relative data, SQL will return NULL.

1.16 CONCATENATE: Concatenation character

(1) Function: Concatenate the results obtained by different column names stand up. The methods provided by each database may vary:

MySQL: CONCAT()

##Oracle: CONCAT(), ||

SQL Server:

(2) Statement: concat(Character 1, Character 2, Character 3...)

means concatenating character 1, character 2, and character 3.

(3) Note: Oracle's concat() only allows two parameters; that is, only two strings can be stringed together at one time. But you can use || to concatenate multiple strings at once.

1.17 SUBSTRING: intercept characters

(1) Function: The substring function in SQL is used to obtain Part of a column of data.

In different databases, the name of this function is different:

MySQL: substr(), substring()

Oracle: substr()

SQL Server: substring()

(2) Statement: substr(str, pos) means selecting the character starting from the pos position. This syntax does not apply on SQL Server.

sub(str, pos, len) means starting from the pos position of str, select a string of length len.

1.18 TRIM: Remove the specified content at the head or tail of the string

(1) Function: trim () function is used to remove the beginning or end of characters in a string. The most common is to remove leading or trailing whitespace.

In different databases, the method is different:

MySQL: trim(), rtrim(), ltrim()

Oracle: rtrim(), ltrim()

SQL Server: rtrim(), ltrim()

(2) Statement: trim([[position] [string to be removed] from] string)

(3) Note: If If there is no list of what string to remove, whitespace will be removed (if any).

2. Table processing

Table processing instructions refer to how to use SQL to process each table in the database, add, delete, modify, etc.

2.1 CREATE TABLE: Create a table

(1) Function: Create a table in the database

(2) Statement:

create table Table name

## (Column name 1 attribute,

Column name 2 attribute,

## Column name 3 attribute);

2.2 CONSTRAINT: Restrictions

(1) Function: Used to specify which data can be stored in the table, or borrowed later Specified by the alter table statement.

(2) Common restrictions are:

not null

unique

check

Primary key

Foreign key

2.3 NOT NULL: Limit the value of the column to non-NULL

(1) Effect: When no A column value is allowed to have null values, subject to any restrictions. If the value of a column is not allowed to contain null, then you need to specify not null for that column.

(2) Statement:

create table table name

(column Name 1 attribute not null,

column name 2 attribute not null,

## column Name 3 attribute);

2.4 UNIQUE: Limit the value of the column to be unique

(1) Function : Ensure that the values ​​in a column are different.

(2) Statement:

create table table name

(column Name 1 attribute is not null unique,

Column name 2 attribute is not null,

Column name 3 attribute);

(3) Note: A column designated as the primary key must contain unique characteristics. But a unique column is not necessarily a primary key.

2.5 CHECK

(1) Function: Ensure that all values ​​in a column are consistent certain conditions.

(2) Statement:

create table table name

(column Name 1 attribute check(column name 1>10) not null unique，

Column name 2 attribute not null，

Column name 3 attribute);

(3) Note: The check restriction has not yet been implemented on the MySQL database.

2.6 primary key：primary key

(1) Function: primary key ) is a unique value in the table.

That is, it is used to uniquely confirm each row value in a table.

The primary key can be a column in the original data or an artificial column (a column that has no relationship with the original value).

The primary key can contain one or more columns. When the primary key contains multiple columns, it is called a composite key.

(2) Statement:

In MySQL:

create table table Name

(Column name 1 attribute check(Column name 1>10) not null unique，

Column name 2 attribute not null，

Column name 3 attribute,

primary key(column name 1));

In Oracle:

create table table name

(column name 1 attribute check(column Name 1>10) not null unique primary key，

Column name 2 attribute not null，

Column name 3 attributes);

In SQL Server:

create table table name

(Column name 1 attribute check(Column name 1>10) not null unique primary key，

##Column name 2 attribute not null，

Column name 3 attribute);

(3) How to set the primary key by changing the existing table structure:

MySQL: alter table table name add primary key (Column name 2)

Oracle: alter table table name add primary key (Column name 2)

SQL Server: alter table table name add primary key (column name 2)

(4) Note: Before using the alter statement to add the primary key, you need to confirm that it is used as Whether the primary key column is set to not null. That is, the primary key column must not be null.

2.7 foreign key: foreign key

(1) Function: a foreign key is one or several points pointing to another A table primary key column.

The purpose of the foreign key is to determine the referential integrity of the value. That is, only approved values ​​will be stored in the database.

(2) Statement:

In MySQL:

create table table Name 1

(Column name 1 attribute check(Column name 1>10) not null unique,

Column name 2 attribute not null,

Column name 3 attribute,

primary key (column name 1)

foreign key (column name 2) references table name 2 (primary key column) );

##In Oracle:

create table table name 1

##(column name 1 attribute check(column name 1>10) not null unique primary key，

Column name 2 attribute is not null,

##Column name 3 attribute references Table name 2 (primary key column)

);

In SQL Server:

##create table table name 1

(column name 1 attribute check(column Name 1>10) not null unique primary key,

column name 2 attribute not null,

column name 3 attribute references Table name 2 (primary key column)

);

(3) How to set foreign keys by changing the existing table structure:

MySQL: alter table table name 1 add foreign key (column name 2) references table name 2 (column name 2)

Oracle: alter table Table name 1 add(constraint xxx) foreign key(column name 2) references Table name 2(column name 2)

SQL Server: alter table table name 1 add foreign key(column name 2) references table name 2 (column name 2)

2.8 CREATE VIEW: New view

(1) Function: View tables can be used as virtual tables. Different from regular tables, the table actually stores data, but the view is a structure built on the table and does not itself actually store data.

(2) Statement: create view view name as SQL statement;

Among them, the SQL statement can be similar to: select column name from table name;

(3) Note: You can use a view to connect two tables. In this case, the user can directly find the required information from one view table, without having to perform a join operation and then search from two different tables.

2.9 CREATE INDEX: Create a new index

(1) Function:

Index can help us quickly find the information we need from the table.

If a table does not have an index, the database system needs to read out the entire reported data. This process is called table scan.

If an appropriate index exists, the database system can first use this index to find out where the required data is in the table, and then directly go to that position to obtain the data, speeding up The speed of obtaining data.

Indexing is something that is good for system efficiency. An index can cover one or more columns.

(2) Statement: create index index name on table name (column name 1, column name 2);

##2.10 ALTER TABLE: Modify the table

(1) Function: After the table is created, sometimes it is necessary to change the structure of the table. For example, add a column, delete a column, change column names, change column attributes, etc.

(2) Statement: alter table name column name [change method]

[change method] Common There are:

Add a column: add column name 2 attributes

Delete a column: drop column name 2

Change column name: change old column name, new column name, new attribute

Change column attribute: modify column name 2 new attribute

2.11 DROP TABLE: Delete the table

(1) Function: Clear a table from the database (delete the table)

(2) Statement: drop table table name

##2.12 TRUNCATE TABLE: Clear table contents

(1) Function: Clear all data in a table (without deleting the table)

(2) Statement:

truncate table Table name

2.13 INSERT INTO: Insert content into the table

(1) Function: Data is entered into the form.

(2) Statement:

insert into table name (column 1, column 2...) values (value 1, value 2...)

insert into table name 1 (column 1, column 2...) select column name 3, column name 4 from table name 2

2.14 UPDATE: Update table content

(1) Function: Modify the data in the table.

(2) Statement: update table name set column name 1=new value where condition

2.15 DELETE FROM: delete a row Or multiple lines

# (1) Function: In some cases, some data need to be removed directly from the database. (One or more rows are deleted)

(2) Statement:

delete from table name where condition

3. Advanced SQL

How to use SQL to perform some more complex operations, and how to use SQL to do these operations:

Rankingrank

Median median

Cumulative total running total

Sum percentage percent to total

cumulative percent to total

3.1 UNION: merge

(1) Function: The purpose is to combine the results of two SQL statements. From this perspective, union is similar to join.

One limitation of union is that the columns generated by the two SQL statements need to be of the same attribute type.

In addition,

When using union, we will only see different data values, that is, the result values ​​are not repeated, similar to select distinct .

(2) Statement:

SQL

Statement 1

union ##SQL

Statement 2

(3) Note : If we use select disinct column name in any SQL statement (or both statements together), it is likely to get exactly the same result.

3.2 UNION ALL

(1) Function: The purpose is also to merge the results of two SQL statements together.

The difference is that union all will list every piece of data that meets the conditions, regardless of whether the data value is duplicated or not. Result values ​​are repeatable.

(2) Statement:

SQL statement 1

union all

SQL statement 2

##3.3 INTERSECT

(1) Function: Similar to union, intersect also processes the results generated by two SQL statements.

The difference is that union is basically an OR, while intersect is more like AND. That is, union is a union, and intersect is a union .

(2) Statement:

SQL statement 1

intersect

SQL statement 2

(3) Note: With the intersect command, different values ​​will only be listed once.

3.4 MINUS

(1) Function:

mius It refers to being applied to two SQL statements.

First find out the results produced by the first SQL statement, and then see if these results are among the results of the second SQL statement. If there is, then the first piece of data will be removed and will not appear in the final result.

If the result generated by the second SQL statement does not exist in the result generated by the first SQL statement, then this data will also be discarded.

(Similar to subtraction? In the end, only those items in the first SQL statement that only appear in the first SQL statement but not in the second SQL statement remain )

(2) Statement:

SQL statement 1

##minus

SQL Statement 2

(3) Note: With the minus command, different values ​​will only be listed once.

3.5 Subquery

(1) Function: subquery, put another SQL in one SQL statement statement.

When we insert another sql statement in the where clause or having clause, we have a subquery.

The role of subquery, first, can be used to join tables. Second, sometimes a subquery is the only way to join two tables.

(2) Statement:

select column name 1 from table name 1 where column name 2 [comparison operator] ( select column name 2 from table name 2 where condition)

Among them, [comparison operand] can be an equal operand (=, >=, >, < ;=, <), or it can be a literal operand (LIKE).

##3.6 EXISTS

(1) Function: We use in in front, >, <, = Wait to join the inner query and outer query. exists is also one of the ways.

Basically,

exists is used to test whether the inner query produces any results. If there is, the system will execute the SQL in the outer query. If not, the entire SQL statement will not produce any results.

(2) Statement:

select column name 1 from table name 1 where exists (select column name 2 from table name 2 where condition)

3.7 CASE

(1) Function: case is used as if- Logical keywords like then-else.

(2) Statement:

select

case (column name)

when condition 1 then result 1

when condition 2 then result 2

...

[else result n]

end

from table name

(3) Supplement: The condition can be a numerical value or a formula. The else clause is not required.

Recommended learning: mysql video tutorial

The above is the detailed content of Let’s talk about SQL commands, table processing and summary of advanced SQL knowledge points.. For more information, please follow other related articles on the PHP Chinese website!