Shulou(Shulou.com)06/01 Report--

Inquiry:

select * from table name

select * from table name where condition

select * from Table 1, Table 2 where Table 1 Field = Table 2 Field [and Condition 2... ]

select fields from Table A JOIN Table B ON Table A Field 1 = Table B Field 1 AND Table A Field 2 = Table B Field 2... Where can be table A or table B

select A. Field, B. Field from Table 1 A JOIN Table 2 B ON A. Field 1 = B. Field 1 AND A. Field 2 = B. Field 2... WHERE can be table 1, can also be table 2-table name long can be abbreviated in this alias method. Table 1 is abbreviated as A and Table 2 is abbreviated as B.

select FName from t_ICItem where FNumber in (select FNumber from t_ICItem where FNumber like '3.% ')

####join can be replaced with the following ######

join: Show only records that exist in both tables A and B

LEFT OUTER JOIN: As long as there are records in table A, they will be displayed. If there is no corresponding value in table B, it will be assigned NULL.

FULL join: Display all records of table A/B, assign NULL if there is no value in the corresponding table.

inner join: Same effect as JOIN

SELECT A. Field, B. Field, C. Field FROM Table 1 A (Table 1 Alias) LEFT JOIN Table 2 B (Table 2 Alias) ON A. Field = B. Field LEFT JOIN Table 3 C (Table 2 Alias) ON A. Field = C. Field [where condition]

- -Replace Table 1, Table 2 and Table 3 with A, B and C respectively for the purpose of shorthand, and then take one field from each of the three tables

Copy the data table:

First create a new one with the same structure as the target data table; then execute

insert into new table as select * from old table

Update:

update table name set field name ='new value' where condition

update table name set field name ='new value' where condition 1 and condition 2

update table name set field name ='new value' from table 1, table 2 where table 1 field = table 2 field [and condition 2... ]

delete

delete from table name where condition

delete from table name from table 1, table 2 where table 1 field = table 2 field [and condition 2... ]

delete table name where field1 ='value' and field1 in (select field1 from table 2 where condition2 [...]）

------------------------------------------------------------------------------------------------------------------------------------------------Operation in Query-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select Item Table. FNumber Item Code, Item Table. FName Item Name, Purchase Order. FBillNo Order Number, Purchase Entry Table. FMrpClosed Business Line Close, Purchase Entry Table. FQty, Purchase Entry Table. fnote Note,

(Purchase Entry Table.FQty-ISNULL (Purchase Entry Table.FStockQty,0)) AS Outstanding Quantity

from POOrder, POOrderEntry, t_ICItem

where Item Table. FItemID = Purchase Entry Table. FItemID and Purchase Order. FInterID = Purchase Entry Table. FInterID and Purchase Order. FClosed = 0 and Purchase Entry Table. FMrpClosed ='0'

and Purchase Entry Table. FMrpClosed ='0' and FQty-ISNULL (Purchase Entry Table. FStockQty,0)!= Purchase Entry Table.fqty--Query unclosed purchase orders

Description: (Purchase Entry Table.FQty-ISNULL (Purchase Entry Table.FStockQty,0)) AS Outstanding Quantity--This is a field

First use the quantity of purchase entry table-the quantity received in purchase entry table, and display it in the column of "Undelivered Quantity."

ISNULL (): function, when the value of purchase entry table. FStockQty is NULL, replace it with the value 0, otherwise an error is reported and the operation cannot be performed.

New column of AS cannot be referenced, but the whole "operation content" before it is referenced: FQty-ISNULL (purchase entry table.FStockQty,0)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.