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.