Full access to SQL Syntax 2

SQL syntax

BETWEEN ... And operator

Determines whether a person's numeric value is within a specific range, and this operator can only be used in SQL statements.

Expr[not]between value1 and value2

Expr

Specifies the combination of fields and expressions to be evaluated.

Value1,value2

The range of values specified.

For example:

If you want to check out all employees aged 25-30 years from the staff form, you can use the following procedure.

SELECT name, age BETWEEN and 30

from staff form;

Like operand

Used to compare a string to another particular string style (pattern) and filter the records that match the string style.

Expression like "pattern"

Expression

Use in the WHERE Condition clause, the SQL expression.

Pattern

The string style used to compare.

For example:

If you want to check out all the names that are headed by "Li", you can use the following formula.

Like "Li *"

Multiple examples of like operands:

1. Multiple characters:

(1) "A*a"

Filter: "AA", "ABa", "Abbba", Can not be screened: "ABC"

(2) "*ab*"

Filter: "ABC", "AABB", "Xab", Can not be screened: "Azb", "BAC"

2, special characters:

"A" * "a"

Filter: "A*a", Cannot filter: "AAA"

3, single character:

"A?a"

Can be screened: "AAA", "a3a", "ABa", Can not be screened: "Abbba"

4. Single digit:

"A#a"

Can be screened: "a0a", "A1A", "A2A", cannot be screened: "AAA", "a10a"

5, Character range:

"" A-Z ""

Filter: "F", "P", "J", Can not be filtered: "2", "&"

6, the specified character to the external scope:

""!a-z ""

7. Specify non-digit:

""!0-9 ""

Filter: "A", "a", "&", "~", Can not filter: "0", "1", "9"

8. Modular Structure:

"A"!b-m "#"

Filter: "An9", "az0", "A99", Cannot filter: "ABC", "Aj0"

SQL number function

1, AVG: arithmetic average

AVG (expr)

Expr

The name of the field or expression.

For example:

To calculate the average height of a staff member over 165 cm tall, you can use the following SQL statement to complete it.

SELECT AVG (height)

As average height

From staff form WHERE height > 165;

2. Count: Count the number of record bars

COUNT (expr)

Expr

The name of the field or expression.

For example:

If you want to count the number of staff in the business unit and check out the name of the employee, you can use the following procedure.

SELECT Count (name) as Employee name

From staff form

WHERE Department name = ' business unit ';

3, First and last: Returns a field of data with the final data.

A (expr)

Last (expr)

Expr

The name of the field or expression.

For example:

If you want to find the first piece of data in the Quantity field and the last piece of data in the Price field, you can use the following query method.

SELECT A (number of items), last (item price)

From order Form

4, Max, and min: Returns the maximum and minimum values for a field.

Use the same as the one and last.

5, sum: Returns the sum value of a particular field or operation.

SUM (expr)

Expr

The name of the field or expression.

For example:

To calculate the total price of a product, you can use the following procedure.

SELECT

Sum (Unit Price * Number of items)

As goods price from order form

Multi-layer SQL query

As the name implies, the multi-level SQL query is: "In one SQL statement can contain another SQL query statement, forming an internal nested query type." ”

comparison[any| all| SOME] (SQLStatement)

Expression[not]in (SQLStatement)

[NOT] EXISTS (SQLStatement)

Comparison

An operation that compares an expression to the result of an inner query.

Expression

An expression that searches for the results of a query on the inner layer.

SQLStatement

The SQL query that is composed of the SELECT statement must be surrounded by ().

For example:

We first query all the units from the order form, then compare the units in the product table with the one by one, and query all the records of the unit price above the order form.

SELECT * from Product form

WHERE Unit Price > any (SELECT unit price from order Form WHERE discount > =.25);

SQL and database Maintenance

The establishment of the form

After introducing the basic syntax in SQL, but mostly in favor of querying and filtering the database data, in fact, there are many things we can do with SQL commands, and the next step is to use SQL syntax commands to build a table in a database.

CREATE Table Statement

We can use this command to create a completely new form, but the premise is that the database must already exist.

CREATE table Table (field1 type[(size)][index1][,field2 type[(size)][index2][,...]] [, nultifieldindex[,...]])

Table

The new form name to create.

Field1,field2

The new field name in the new table, to less than one field above.

Type

The data type of the field.

Size

The size of the field.

Index1,index2

Use the constraint condition clause to define the index name of a single field.

Multifieldindex

Use the constraint condition clause to define the index name of a multiple field.

For example:

Create a table with Employee name and Department fields.

CREATE table Employee form (name Test, Department test, employee number INTEGER constraint Employee field index primary KEY)

In this example, we created a table with the table name "Employee table" and defined the primary key value of the table to restrict the data from being entered repeatedly.

Establishment of Table index

CREATE Index statement

This command is primarily an index of an existing table, and its use is as follows:

Create[unique]index INDEX on table (field[asc| desc][,field[asc| DESC],...])

[With {primary| disallownull| Ignorenull}]

Index

The name of the index to be established.

Table

The name of the table in which to build the index.

Field

The field name of the index to be built. The order of the indexes can be determined by desc reserved words.

For example:

Create an index in the employee table.

CREATE Index New index name

On staff form (name Department);

field updates for a table

Constraint conditional clause

CONSTRAINT functions are similar to indexes (index), although CONSTRAINT can also establish the correlation between tables.

Single Field index:

CONSTRAINT name{primary key| unique| REFERENCES foreigntable[(foreignfield1,foreignfield2)]}

Multi-field Index:

CONSTRAINT Name

{PRIMARY KEY (primary1[,primary2[,...]])

| UNIQUE (unique1[,unique2[,...]])

| FOREIGN KEY (ref1[,ref2[,...]])

| REFERENCES foreigntable[(foreignfield1[,foreignfield2[,...])]}

Name

The name of the constraint to be established.

Primary1,primary2

The name of the field that is designed to be the primary key value (more than one).

Unique1,unique2

The name of the field that is designed to be a unique key value (more than one).

FOREIGN key

The field name, or the field name that references the field in the other table.

ForeignTable

As mentioned above, the table to be referred to.

Foreignfield1,foreignfield2

In the table referenced to, the field specified by the Ref1,ref2 field. If the referenced field is the primary key value in the reference table, you can also omit the conditional clause.

For example:

The following SQL statement can be used when we want to create a new staff data table that contains the name, department name, and birthday three fields, and when a unique index is established by the three fields.

CREATE Table Employee Data table

(name test, department name test, birthday datetime,constraint staff data form limit UNIQUE (name, department name, birthday));

The above is in SQL, with the database table to establish the relevant commands, you can use these commands, through SQL statements, the database table is fully established, the next section will be for the database after the establishment of maintenance and additions and deletions to use the SQL statements to be introduced.

Deletion of the table

Delete statement

We can delete the records in the table by using the DELETE statement. (Note: After the record is deleted, it cannot be restored, so the condition is set to correct)

DELETE[TABLE.*]

From Tableexpression

WHERE criteria

Table

To delete the form name of the record, you can also replace it with *.

Tableexpression

The name of one or more tables. This parameter can be the result of a single table name or an operation from a inner join,left JOIN, or a rightjoin.

Criteria

Determine the criteria to be deleted in the table.

For example:

We can use the following SQL statement if we want to delete the records in the employee form named ' Li name '.

DELETE * from staff form

WHERE name = ' li name ';

database table-Related action commands

In addition to being a tool for querying and creating database tables, SQL for database and table new, delete repair, and maintenance, and with a fairly good function, if the reader use SQL command properly, for the overall efficiency of the improvement has a lot of help, so for the advantages of SQL statements, It is often the case that "when we do complex and multi-step processing of multiple tables, perhaps SQL can accomplish all the requirements and goals with just one statement," at first glance, it may seem a bit esoteric, but the next chapters will give you an idea of the beauty of it.

SELECT ... INTO statement

We can use this command to create a query for a new table by using the existing form query.

SELECT field1[,field2[,...]] Into Newtable[in Externaldatabase]

From source

Field1,field2

The name of the field to copy to the new table.

NewTable

The name of the new form you want to create must not be a pre-existing form.

Externaldatabase

If the form is in another external database, the name of the database.

Source

Record the source form name of the copy of the data, can be a single table or a section of SQL query statements.

For example:

You can use the following SQL statement to create a new "training roster" form.

SELECT staff form. Name, staff form. Department

into the training roster from staff form

WHERE title = ' New Person ';

Number of INNER join operands

When a common field data is equal, the records of the two tables are grouped.

SELECT fields

From table1 INNER JOIN table2

On table1.field1 compopr table2.field2

Table1,table2

The name of the table for which you want to combine records.

Field1,field2

The name of the field you want to combine. (Must have the same data type)

Compopr

The comparison operators are as follows: "=", "<", ">", "<=", "<>", and so on.

For example:

If you want to combine the category form with the product form, refer to the following SQL statement.

SELECT category name, product name

From category table INNER JOIN Product table

On classification table. Category Number = Product table. Category number;

Union operand

We can use the Union operand to establish the query condition of the connection, and the union operand can combine the results of more than two tables or queries.

[Table]query1 Union [All][table]query2 [All]

[Table]queryn [...]]

Query1,query2,queryn

is a SELECT statement, an existing query name, or a table name that already exists.

For example:

You can use the following SQL statement, the Customer table record of more than 1000 order quantity, and the new Customer table for union operation.

Table new Customer table UNION all

SELECT *

From Customer table

WHERE order quantity > 1000;

Alter statement

After a table is established, we can modify the field design of the table using the ALTER statement.

ALTER Table Table

{ADD {COLUMN field type[(size)][constraint index]

| CONSTRAINT Multifieldindex}

| DROP {COLUMN field| CONSTRAINT IndexName}}

Table

The name of the table to be alter.

Field

The name of the field to be added or deleted.

Type

The field data type.

Size

The field size.

Index

The index of this field.

For example:

Create a new "pay" field in the Staff table.

ALTER Table Staff Table

ADD COLUMN Salary CURRENCY;

For example:

Delete a "pay" field in the Staff table.

ALTER table Staff form DROP COLUMN salary;

Drop statement

Deletes the specified table or field, or deletes the index.

DROP {TABLE table| Index Index on table}

Table

The name of the table or index attached to the deletion.

Index

The name of the index to remove from the table.

For example:

Deletes a numbered index from the staff table.

DROP INDEX myindex on Employees;

For example:

From the database, delete the entire table.

DROP table Staff form;

INSERT into statement

Create a new piece of data into the table.

Multiple Records new query:

INSERT into Target [in externaldatabase][(field1[,field2[,...]])]

SELECT [Source.] field1[,field2[,...]

From Tableexpression

A new query for a single record:

INSERT into target[(field1[,field2[,...])]

VALUES (value1[,value2[,...])

Target

The table name of the new record.

Externaldatabase

The path to the external database, used with the in condition clause.

Source

If the record is copied from another table, specify the name of the form.

Field1,field2

The name of the field that you want to add data to.

Tableexpression

Table name or description record is the table name from which table to insert. Used with the inner join,left join, or the right join operator.

Value1,value2

To insert a value in a table.

For example:

In the Customer data table, insert the data from the new table.

INSERT into Customer data table

SELECT the new Customer data table. *

From new customer data form;

For example:

Inserts data into the staff table.

INSERT into Employee form (name, birthday, title)

VALUES ("Wang Rong", "57/12/11", "manager");

For example:

From the training staff form, add the staff member to the official staff form for more than 30 days.

INSERT into Employee form

SELECT trainer form. *

From trainer form

WHERE to hire days > 30;

UPDATE statement

Create an update query that modifies specific data by limiting the conditions.

UPDATE table

SET newvalue

WHERE criteria;

Table

The table name for which you want to modify data.

NewValue

The value to be modified (insert the item value into a specific field).

Criteria

Query criteria to determine which records to modify.

For example:

If you want to change the order quantity in the order form to 1.1 times times, the freight is 1.03 times times, you can use the following SQL statement to complete.

UPDATE Order Form

SET Order quantity = Order quantity * 1.1, freight = freight * 1.03 times times

Where to reach the location = ' United States ';

When we have finished with the changes, you can use the SELECT statement and the same WHERE condition clause to see if the modified data is correct.

In fact, to take advantage of SQL to do some seemingly complex operations, does not require a cumbersome combination of SQL commands, or many steps to complete, in fact, the most important thing is to use SQL commands, in the most concise SQL statements to achieve maximum efficiency.