Full access to SQL syntax (7)

Deletion of SQL syntax tables

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.