Comprehensive exposure to SQL syntax (7)

Deletion of table

　DELETE statement

We can use the DELETE statement to delete records in the table. (Note: After a record is deleted, it cannot be restored, so the condition settings must be correct)


DELETE[table.*]
FROM tableexPRession
WHERE criteria
table
To delete The table name of the record can also be replaced by *.
tableexpression
the name of one or more tables. This parameter can be a single table name or the result of an INNER JOIN, LEFT JOIN, or RIGHTJOIN operation.
criteria
determines the criteria by which records in the table should be deleted.


For example:

If we want to delete the record named '李名' in the employee table, we can use the following SQL statement to complete it.

　DELETE * FROM staff table

　WHERE name='李名';
　
　Operation commands related to database tables

　SQL can be used as a query and database In addition to the tools for creating tables, it also has very good functions for creating, deleting, and maintaining databases and tables. If readers use SQL commands appropriately, it will be of great help to improve the overall efficiency, so for SQL statements The advantage brought by it is that we often encounter a situation: "When we perform complex and multi-step processing on multiple tables, perhaps only one SQL statement can complete all the requirements and goals." At first glance, perhaps It seems a bit mysterious, but the following chapters will let you understand the beauty of it.

SELECT...INTO statement

We can use this command to create a query statement for a new table using an existing table query.

SELECT field1[,field2[,...]]INTO newtable[IN externaldatabase]
FROM source
field1,field2
The name of the field to be copied to the new table.
newtable
The name of the new table to be created cannot be an existing table.
externaldatabase
If the table is in another external database, the name of the database.
source
Record the source table name of the data copy, which can be a single table or a SQL query statement.
For example:
You can create a new "Training Roster" table through the following SQL statement.
SELECT Staff table. Name, Staff table. Department
INTO Training roster FROM Staff table
WHERE Title='New staff';



INNER JOIN operand

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



SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field2
table1,table2
The name of the table to be combined.
field1,field2
field names to be combined. (Must have the same data type)
compopr
comparison operators are as follows: "=", "<", ">", "<=", "<>", etc.
For example:
If you want to combine the classification table with the product table, you can refer to the following SQL statement.
SELECT classification name, product name
FROM classification table INNER JOIN product table
ON classification table. classification number = product table. classification number;



UNION operand

We can establish connection query conditions through UNION operands. UNION operands can combine more than two tables or query results.


[TABLE]query1 UNION [ALL][TABLE]query2 [UNION [ALL]
[TABLE]queryn [...]]
query1,query2,queryn
is A SELECT statement, or an existing query name, or an existing table name.


For example:

You can use the following SQL statement to UNION the customer table records with an order quantity exceeding 1,000 and the new customer table.


TABLE new customer table UNION ALL
SELECT *
FROM customer table
WHERE order quantity>1000;





ALTER statement

After a table is created, we can use the ALTER statement to modify the field design of the table.


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 ALTERed.
field
The name of the field to be added or deleted.
type
Field data type.
size
Field size.
index
The index for this field.
For example:
Create a new "Salary" field in the employee table.
ALTER TABLE employee table
ADD COLUMN salary CURRENCY;
For example:
Delete a "salary" field in the employee table.
ALTER TABLE employee table DROP COLUMN salary;



DROP statement

Delete the specified table or field, or delete the index.

DROP {TABLE table|INDEX index ON table}
table
The name of the table to be deleted or the table to which the index is attached.
index
The name of the index to be deleted from the table.
For example:
From the employee table, delete the number index.
DROP INDEX MyIndex ON Employees;
For example:
Delete the entire table from the database.
DROP TABLE staff table;


　
　INSERT INTO statement

　Create a new piece of data into the table.


　 New query for multiple records:


INSERT INTO target [IN externaldatabase][(field1[,field2[,...]])]
SELECT [source.]field1[,field2[,...]
FROM tableexpression
New query for a single record:
INSERT INTO target[(field1[,field2[,...]])]
VALUES(value1[,value2[,...])
target
Table name of the new record .
externaldatabase
The path to the external database, used with the IN conditional clause.
source
If the record is copied from another table, specify the name of the table.
field1,field2
The name of the field you want to add data to.
tableexpression
The table name or description record is the name of the table from which the record is inserted. Used with INNER JOIN, LEFT JOIN, or RIGHT JOIN operators.
value1,value2
The values ​​​​to be inserted into the table.
For example:
In the customer data table, insert data from a new table.
INSERT INTO customer data form
SELECT new customer data form.*
FROM new customer data form;
For example:
Insert data in the staff table.
INSERT INTO staff table (name, birthday, title)
VALUES("Wang Rong","57/12/11","Manager");
For example:
From the training staff table, select staff who have been employed for more than 30 days , add it to the official staff table.
INSERT INTO staff table
SELECT training staff table.*
FROM training staff table
WHERE employment days >30;



UPDATE statement

Create an UPDATE query to modify specific data through conditional restrictions.


UPDATE table
SET newvalue
WHERE criteria;
table
The name of the table to modify the data.
newvalue
The value you want to modify (insert the value into a specific field).
criteria
Query conditions are used to determine which records to modify.
For example:
If you want to modify the order quantity in the order form to 1.1 times and the shipping fee to 1.03 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
WHERE delivery location = 'United States';
After we complete the modification, you can use the SELECT statement with the same WHERE conditional clause , to check whether the modified data is correct.
In fact, to use SQL to complete some seemingly complex operations, you do not need a complicated combination of SQL commands or many steps to complete. In fact, the most important thing is to make full use of SQL commands to achieve the most streamlined operation. Get the highest efficiency from your SQL statements.

The above is the content of comprehensive contact with SQL syntax (7). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!