Shulou(Shulou.com)05/31 Report--

Today, the editor will share with you what are the relevant knowledge points about SQL instructions, table processing and SQL advanced knowledge points, which are detailed in content and clear in logic. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

1. SQL instruction

The SQL instruction refers to how SQL is used to store, read and process tabular data in the database.

Common SQL instructions are as follows:

1.1 SELECT: select

(1) function: to select the data from the tables in the database. You can read all the data in one or more columns of the table.

What is a form? A table is a structure in a database designed to store data.

(2) statement: select column name 1, column name 2. From table name

1.2 DISTINCT: differentiate (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 different values are in the column, 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 conditions

(1) function: when conditional selective query is needed, where is used.

(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 made up 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: take a value in a discontinuous range (with where)

(1) function: the in clause, usually used in conjunction with where, indicates that the condition is valued in a discontinuous range

(2) statement: select column name from table name where column name as conditional in (value 1, value 2, value 3.) Exists

(3) add: if there is only one value in parentheses of the in clause, it is equivalent to... where column name = value 1

1.6 BETWEEN: take values in a continuous range (with where)

(1) function: the in instruction gets the values in the database within the limit of one or more discontiguous values, while between gets the values in the database in a continuous range. It is also usually used with where.

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

1.7 LIKE: with lookup mode (with where)

(1) function: to find out the required data according to a pattern. It is also usually used with where.

(2) statement: select column name from table name where column name like {schema}

(3) {Mode}:

For example, ABC% B means that there can be only one character between the beginning of An and the end of B, ABC% represents a string that begins with ABC,% ABC represents a string that ends with ABC, and% ABC% represents a string that contains the pattern of ABC.

1.8 ORDER BY: sort

(1) function: to make a systematic display of the obtained data, that is, sorted display, positive or reverse order.

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

(3) [] means optional, not required. ASC means from small to big, DESC vice versa. ASC is used by default.

(4) Note: if where exists, then where needs to precede the order by clause.

(5) add: you can sort several column names 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: from small to large according to column name 1. If there are several query results with the same column name 1, the results are arranged from small to large according to column 2, and so on.

1.9 function

(1) function: many contents in the database exist in the form of numbers, and when doing operations on these numbers, you can run them directly through some defined functions.

(2) commonly used functions:

AVG: average

COUNT: count

MAX: maximum

MIN: minimum

SUM: sum

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

1.10 COUNT: count

(1) function: count and count the total number of eligible items found in the table.

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

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

1.11 GROUP BY: classification

(1) function: when we choose more than one column name and at least one column name contains the use of the function, we need to use the group by instruction. In this case, you need to make sure that we have all the other column names of group by. That is, except for the column name that includes the function, you need to put it in the clause of group by.

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

1.12 HAVING:

(1) function: set the condition on the value produced by the function. The having clause is at the end of a SQL sentence.

A SQL that contains a having clause does not have to contain a group by clause.

(2) statement: select column name 1 having (column name 2) from table name group by column name 1 having (function condition)

(3) Note: if only the function bar is select, there is no need for the group by clause.

1.13 ALIAS: alias (point to table or column)

(1) function: alias tables and columns.

Alias for column names: to make the results produced by SQL easy to read.

Table aliases: just leave a space after the table name in the from clause, and then list the table aliases you want to use.

That is, both aliases are placed after the table or column name to be replaced, separated from the table or column name by a space.

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

(3) Note: column aliases can be placed not only directly after column names, but also after functions, such as... sum column aliases.

1.14 form connection

(1) function: join two tables through the same columns in two tables.

The where clause plays an important role in table joins.

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

1.15 external connection

(1) function:

We often talk about the left connection, which is the internal connection. In the case of an internal connection, both tables need to have the same value in order for that data to be selected.

If you want to list the data of each stroke in a table, regardless of whether its value appears in another table or not. At this point, an external connection is required: SQL OUTER JOIN.

(2) statement: the syntax of external connections varies from database to database. For example, in Oracle, the table where all the data is selected in the where clause is added (+) to indicate that we need all the data in this table.

(3) Note: when there is no relative data in the second table, SQL will return NULL.

1.16 CONCATENATE: string hyphen

(1) function: concatenate the results obtained from different column names. The methods provided by each database may be different:

MySQL:CONCAT ()

Oracle:CONCAT (), | |

SQL Server:+

(2) statement: concat (character 1, character 2, character 3.)

It means to concatenate character 1, character 2, and character 3.

(3) Note: concat () of Oracle only allows two arguments; that is, only two strings can be concatenated at a time. But you can use | | to concatenate multiple strings at a time.

1.17 SUBSTRING: intercept characters

(1) function: the substring function in SQL is used to obtain a part of a column of data.

This function has a different name in different databases:

MySQL: substr (), substring ()

Oracle: substr ()

SQL Server: substring ()

(2) statement: substr (str, pos) indicates that the character starting from the pos position is selected. This syntax does not apply to SQL Server.

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

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

(1) function: the trim () function is used to remove prefixes or suffixes from a string. The most common is to remove prefix or suffix white space.

This approach is different in different databases:

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

Oracle:rtrim (), ltrim ()

SQL Server:rtrim (), ltrim ()

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

(3) Note: if the string to be removed is not listed, the white space will be removed (if any).

two。 Table processing

Table processing instruction refers to how to use SQL to deal with various tables in the database, add, delete, modify and query, and so on.

2.1 CREATE TABLE: create tables

(1) function: create tables in the database

(2) statement:

Create table table name

(column name 1 attribute

Column name 2 attribute

Column name 3 attribute)

2.2 CONSTRAINT: limit

(1) function: used to specify which data can be stored in the table, or later specified by the alter table statement.

(2) the common restrictions are:

Not null

Unique

Check

Primary key (primary key)

Foreign key (Foreign key)

2.3 NOT NULL: the value of the limit column is not NULL

(1) function: the value of a column is allowed to have null values without 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 columns have unique values

(1) function: to ensure that the values in a column are different.

(2) statement:

Create table table name

(column name 1 attribute not null unique

Column name 2 attribute not null

Column name 3 attribute)

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

2.5 CHECK

(1) function: to ensure that all values in a column meet 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 been enforced on the MySQL database.

2.6 primary key: primary key

(1) function: each value in the primary key (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 nothing to do with the original value).

The primary key can contain one or more column bits. When a primary key contains multiple column bits, 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 attribute)

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 schema:

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 adding a primary key with the alter statement, you need to confirm that the column used as the primary key is set to not null. That is, the primary key column must be not null.

2.7 foreign key: foreign key

(1) function: a foreign key is one or more columns that point to another table primary key.

The purpose of a foreign key is to determine the referential integrity (referential integrity) of the value. That is, only allowed values are 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 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: create a new view

(1) function: the view chart can be regarded as a virtual table. Unlike a regular table, there is actual data stored in the table, but the view is a framework based on the table, which does not actually store the data itself.

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

Where the SQL statement can be similar to: select column name from table name

(3) Note: views can be used to connect two tables. In this case, the user can find the required information directly from a view chart, without having to do a join action from two different tables before looking for it.

2.9 CREATE INDEX: create a new index

(1) function:

The 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 report, a process called table scan.

If there is an appropriate index, the database system can first use this index to find out where the needed data is in the table, and then go directly to that location to obtain data, which speeds up the speed of obtaining data.

Indexing is something that benefits the efficiency of the system. 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 form is established, it is sometimes necessary to change the structure of the table. For example, add a column, delete a column, change the column name, change the properties of the column, and so on.

(2) statement: alter table name column name [change mode]

[change mode] the common ones are:

Add a column: add column name 2 attribute

Delete a column: drop column name 2

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

Change the properties of the column: modify column name 2 New attribute

2.11 DROP TABLE: delete tables

(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 (do not delete the form)

(2) statement: truncate table table name

2.13 INSERT INTO: insert content in the table

(1) function: input data 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 contents

(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 one or more lines

(1) function: in some cases, some data needs to be removed directly from the database. (one or more lines are deleted)

(2) statement: delete from table name where condition

3. Advanced SQL

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

Rank rank

Median median

Cumulative total running total

Total percentage percent to total

Cumulative sum percentage cumulative percent to total

3.1 UNION: merge

(1) function: the purpose is to merge the results of two SQL statements. From this point of view, union is similar to join.

One limitation of union is that the column bits produced by 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 do not repeat, similar to select distinct.

(2) statement:

SQL statement 1

Union

SQL statement 2

(3) Note: if we use a select disinct column in any SQL statement (or both), we are 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 lists every piece of data that meets the criteria, regardless of whether the data value is duplicated or not. The result value can be repeated.

(2) statement:

SQL statement 1

Union all

SQL statement 2

3.3 INTERSECT

(1) function: similar to union, intersect also deals with the results produced 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: intersect directive, different values will only be listed once.

3.4 MINUS

(1) function:

Mius refers to the application of two SQL statements.

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

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

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

(2) statement:

SQL statement 1

Minus

SQL statement 2

(3) Note: minus directive, different values will only be listed once.

3.5 subquery

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

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

The role of subqueries, first, can be used to join tables. Second, sometimes subqueries are the only way to join two tables.

(2) statement:

Select column name 1 from table name 1 where column name 2 [compare operands] (select column name 2 from table name 2 where condition)

Where [compare operands] can be equal operands (=, > =, >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.