Master SQL four most basic data manipulation statements

Master SQL four most basic data manipulation statements: insert,select,update and delete.

Mastering SQL is a valuable asset for database users. In this article, we will guide you through the core functions of the four most basic data manipulation statements-sql-to introduce the comparison operator, the selection assertion, and the three-valued logic in turn. After you have completed these studies, you are clearly beginning to be proficient in SQL.

Before we start, use the CREATE TABLE statement to make a table (as shown in Figure 1). DDL statements define database objects such as tables, columns, and vision. They do not process rows in a table because DDL statements do not handle the actual data in the database. The work is handled by another type of SQL statement-Data Manipulation Language (DML) statement.

There are four basic DML operations in SQL: Insert,select,update and delete. Since this is a frequent use of most SQL users, it is necessary to have a one by one description of them here. In Figure 1 we give a table named employees. Each of these lines corresponds to a specific employee record. Please familiarize yourself with this form, which we will use later in the example.

Connection Query

Multiple table queries can be implemented by connecting operators. The connection is the main feature of the relational database model, and it distinguishes it from other types

A symbol of the database management system.

In the relational database management system, the relationship between the data is not necessarily determined when the table is established, and all the information of an entity is often stored in

In a table. When retrieving data, queries the information of different entities that are stored in multiple tables through a JOIN operation. Connection operations give users a lot of flexibility, and they can add new data types at any time. Create a new table for different entities, and then query by connection.

A connection can be established in the FROM clause or a WHERE clause of a SELECT statement, and paradoxically in the FROM clause to indicate that the connection is helpful

Distinguishes the join operation from the search criteria in the WHERE clause. Therefore, the use of this method is recommended in Transact-SQL.

The connection syntax format for the FROM clause defined by the SQL-92 standard is:

From Join_table Join_type join_table

[On (Join_condition)]

Where join_table indicates the name of the table that participates in the join operation, the connection can manipulate the same table, or it can manipulate multiple tables, and the same

The connection to a table operation is also called a self connection.

Join_type indicates the type of connection, which can be divided into three types: inner, outer, and Cross joins. The INNER join (inner join) is used more than

Compares operators to perform a comparison of some (some) column data between tables and lists the rows of data in those tables that match the join condition. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.

An outer join is divided into a left outer join (a right outer join or a ieft join), an outer join (a right-hand outer join, or a right-click join)

And all outer joins (full outer join or fully join) three kinds. Unlike an inner connection, the outer join lists not only the rows that match the join condition, but all the rows of data that match the search criteria in the left table (when left outer), right (when the right outer join), or two tables (when all outer joins).

A cross join (cross join) does not have a WHERE clause that returns the Cartesian product of all the rows of data in the join table, and its result collection

The number of data rows equals the number of data rows in the first table that match the query criteria multiplied by the number of rows in the second table that match the query criteria.

The ON (join_condition) clause in a JOIN operation indicates the join condition, which consists of columns and comparison operators in the linked table, logical

Operators, and so on.

No connection can be directly connected to the text, ntext, and image data type columns, but these three types of columns may be indirectly

Connection. For example:

Select P1.pub_id,p2.pub_id,p1.pr_info

From pub_info as P1 inner join pub_info as P2

On datalength (p1.pr_info) =datalength (p2.pr_info)

(i) Internal connections

The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the column values of the connected columns using comparison operators. Internal connection Points

Three kinds:

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query results list the connected

All the columns in the table, including the repeating columns.

2. Unequal connection: The column values of the concatenated columns are compared using comparison operators other than the equals operator in the join condition. These

Operators include >, >=, <=, <,!>,!<, and <>.

3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses the select list to indicate the query

The columns that are included in the result collection and delete the duplicate columns in the attached table.

For example, the following uses an equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:

SELECT *

From authors as a inner join publishers as P

On a.city=p.city

Also, if you are using a natural connection, delete the repeating columns (city and state) in the authors and Publishers tables in the select list:

Select A.*,p.pub_id,p.pub_name,p.country

From authors as a inner join publishers as P

On a.city=p.city

(ii) OUTER joins

In the query results collection, the query criteria (where search conditions or having conditions) and join conditions are returned

The line. When an outer join is used, it returns to the query result collection not only for rows that meet the join criteria, but also for all data rows in the left table (when the left outer join), right table (when the right outer join), or two side tables (full outer joins).

Connect Forum content and author information as follows using the left OUTER join:

Select a.*,b.* from Luntan left join Usertable as B

On A.username=b.username

The following uses an Out-of-band connection to all authors in the city table and all authors in the user table, as well as the cities in which they reside:

Select a.*,b.*

From city as a full outer join user as B

On A.username=b.username

(iii) Cross-linking

A cross join does not take a WHERE clause, which returns the Cartesian product of all data rows of the connected two tables, returned to the number in the result set

The number of rows equals the number of rows in the first table that match the query criteria multiplied by the number of rows of data in the second table that match the query criteria.

For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following cross joins will wait

On the 6*8=48 line.

Select Type,pub_name

From titles Cross join publishers

ORDER BY Type

The Union operator can combine the query result sets of two or more SELECT statements into a single result set, that is, the execution of the

Close query. The syntax format of the Union is:

Select_statement

Union [all] Selectstatement

[Union [all] selectstatement] [... n]