讲解SQL的三个主要语句及技巧

1.插入数据

(insert)向表中添加一个新记录，你要使用SQL INSERT 语句。这里有一个如何使用这种语句的例子： INSERT mytable (mycolumn) VALUES (‘some data')　 这个语句把字符串'some data'插入表mytable的mycolumn字段中。将要被插入数据的字段的名字在第一个括号中指定，实际的数据在第二个括号中给出。

INSERT 语句的完整句法如下：

INSERT [INTO] {table_name|view_name} [(column_list)] {DEFAULT VALUES |

Values_list | select_statement}

如果一个表有多个字段，通过把字段名和字段值用逗号隔开，你可以向所有的字段中插入数据。假设表mytable有三个字段first_column，second_column，和third_column.下面的INSERT语句添加了一条三个字段都有值的完整记录：

INSERT mytable (first_column，second_column，third_column)

VALUES (‘some data'，'some more data'，'yet more data')

注意

你可以使用INSERT语句向文本型字段中插入数据。但是，如果你需要输入很长的字符串，你应该使用WRITETEXT语句。

如果你在INSERT 语句中只指定两个字段和数据会怎么样呢?换句话说，你向一个表中插入一条新记录，但有一个字段没有提供数据。在这种情况下，有下面的四种可能：

如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段third_column提供数据，而这个字段有一个缺省值'some value'.在这种情况下，当新记录建立时会插入值'some value'.

如果该字段可以接受空值，而且没有缺省值，则会被插入空值。

如果该字段不能接受空值，而且没有缺省值，就会出现错误。你会收到错误信息：

The column in table mytable may not be null.

最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。

注意 ：向一个有标识字段的表中插入新记录后，你可以用SQL变量@@identity来访问新记录 的标识字段的值。考虑如下的SQL语句：

INSERT mytable (first_column) VALUES(‘some value')

INSERT anothertable(another_first，another_second)

VALUES(@@identity，'some value')

如果表mytable有一个标识字段，该字段的值会被插入表anothertable的another_first字段。这是因为变量@@identity总是保存最后一次插入标识字段的值。

字段another_first应该与字段first_column有相同的数据类型。但是，字段another_first不能是应该标识字段。Another_first字段用来保存字段first_column的值。

Smart Yello Mouse Like 4 Ports USB 2.0 Hub for PC LaptopSATA HDD To Male2.5'' IDE adapter card high-speed fashion design transmission cardDVI24+1 Female right angle DVI plug DVI connector pin DVI connector5.25" LCD Media Dashboard Card Reader Front PanelUSB 2.0 to Mini 5 Pin Data Charger Led Retractable Cable for Nokia Motorola Phone BlackUSB Male to 2*PS2 Convertor Mouse Cable for PC ComputerWooden Bamboo Shaped 4Gb USB 2.0 Flash Memory DriveCool High Cylindrical Wooden 2Gb USB 2.0 Flash Memory Drive16Mb USB 2.0 Flash DriveD link 7DBI antenna wireless lan card WLAN card external WPS button DSSS spread spectrumUSB A sinking SMT&DIP 180° RoHS compliant connector OEM adapter connectorSwitching power supply adapter card Bi-directional high speed transmission cardEmergency Cellphone Charger Cable for Traveller 6 pcs Nokia Motorola SamsungIPod audio transitions cable & USB cableUSB A/M to IPOD,LG,DP,NIMI8P,USB B/M adapter connector stable performance2.5 DC to 2.5 adapter with long life time connector OEM standardized sizeUSB A/F long body DIP 90°(right angle L=19.25mm) connector RoHS compliant socket adapter connectorUSB A/F 180° DIP short body connector (bend angle L=17.1mm) RoHS compliant adapter connectorTraffic Light Shaped 4 Ports USB HubIPod home power supply 180°swiveling AC plug design USB portHDMI 19P/F DIP with screw hole H7.2mm HDMI high definition data plug HDMI connectorMini 5P A/M short body wire solder USB connector cellphone connector mini usb adapterMicro 5P Male with PCB connector cellphone connector LG mobile phone connectorSATA 7 pin to SATA 7 pin Right-angle Data Cable RedYellow Gold 8Gb USB 2.0 Flash Storage DriveCool Ratatable 512Mb USB 2.0 Flash Storage DriveLens Shaped 4 Ports USB HubCute 4 Ports White and Red USB HubBlack Box Like 4 Ports USB 2.O Hub for PC LaptopTF USB Card ReaderMini 5P B/F 180° DIP USB connector cellphone connector mini usb connector socket adapter3.0 SATA adapter high speed fashion design transmission cardSee-through USB Bluetooth Wireless PC Adapter Dongle3.5" Hard Drive Disk USB 3.0 HDD Enclosure Case 5GbpsKing of card wireless lan card external detachable antenna WLANMini 4P Female SMT USB connector cellphone connector mini usb adapterUSB A Male to Male CableWooden 8Gb USB 2.0 Flash Memory Drive with BoxLG 12P bend head connector cellphone connector LG mobile phone connectorHDMI 19P/F 90° DIP single ear HDMI high definition data connector HDMI connectorUSB Bluetooth Wireless PC Adapter Dongle DongleUltra-thin power supply 90W chager adapter with 8 renewable DC plugs 124*56*45 mmMini 16P Female SMT USB connector mini usb port type cellphone connector adapterUSB A/M SMT short body connector RoHS compliant OEM adapter connectorDVI 24+1 Female DIP straight DVI plug DVI connector pin DVI connector8P network three-channel adapter connector with long life time OEM90W chager adapter with 8 renewable DC plugs SONY series 124*56*38 mmUnique Car Logos Shaped USB 2.0 Flash Storage Drive, Nissan, Benz, Volkswagen, HondaUSB A/M to 3.5mm DC connector with long life time OEMWhite and Yello Monitor 4 Ports USB Hub for PC Notebook Replica lv Hangbags Replica Hangbags Hangbags replica handbags replica handbags replica handbags replica handbags usb cable usb cable usb cable usb cable usb flash drives