现在的位置: 首页 > 数据库 > MySQL > 正文
MySQL基本SQL语句之数据插入、删除数据和更新数据
2013年09月22日 MySQL ⁄ 共 2058字 评论数 7 ⁄ 被围观 5,355 views+

一、INSERT插入数据:

方法一:批量插入

基本语法:

INSERT INTO tb_name (col1, col2, ...) VALUES (val1, val2, ...)[,(val1, val2, ...),...]
    字符型:单引号
    数值型:不需要引号
    日期时间型:不需要引号
    空值:NULL,不能写成 ''

如向tutors表插入两行数据:

mysql> INSERT INTO tutors(Tname,Gender,Age) VALUES('Sam','M',28),('Barlow','M',27);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

方法二:一次插入一行

基本语法:

INSERT INTO tb_name SET col_name={expr | DEFAULT}, ...

如向tutors表插入一行数据:

mysql> INSERT INTO tutors SET Tname='Winne',Gender='F',Age=25;
Query OK, 1 row affected (0.04 sec)

图片

方法三:将查询结果插入到表

基本语法:

INSERT [INTO] tbl_name   [(col_name,...)]   SELECT ...

如下将students表中Age大于20的插入到tutors表中:

mysql> SELECT Name,Gender,Age FROM students WHERE Age > 20;
+-------------+--------+------+
| Name           | Gender  | Age    |
+-------------+--------+------+
| DingDian      | M          |   25   |
| HuFei            | M          |   31   |
| Xuzhu            | M         |   26   |
| LingHuchong | M         |   22   |
+-------------+--------+------+
4 rows in set (0.19 sec)

mysql> INSERT INTO tutors(Tname,Gender,Age) SELECT Name,Gender,Age FROM students WHERE Age > 20;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

图片

二、REPLACE插入数据

使用INSERT插入数据时,如果违反约束,会导致插入失败。如students表要求Name字段不能相同,那使用INSERT插入已经存在的Name时,就会失败。使用REPLACE插入数据,可以在此种情况下,使用新数据替换原数据,而如果不违反约束关系,则与INSERT一样正常插入数据。

REPLACE基本语法与INSERT完全一样:

方法一:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

方法二:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...

方法三:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...

三、DELETE删除数据:

基本语法:

DELETE FROM tb_name WHERE condition;

WHERE子句用法同SELECT中的WHERE

如下删除tutors表中Age小于30的行:

mysql> DELETE FROM tutors WHERE Age < 30;
Query OK, 6 rows affected (0.07 sec)

需要注意的是,删除数据后,AUTOINCREMENT计数器中对应数据并不会删除。

如果需要清空一张表,并重置AUTOINCREMENT计数器,可以使用如下命令:

TRUNCATE tb_name

四、UPDATE更新数据

基本语法:

UPDATE tb_name SET col1=..., col2=... WHERE

如下将students表中xuzhu的Age改为20:

mysql> UPDATE students SET Age=20 WHERE Name='Xuzhu';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

update1

目前有 7 条留言 其中:访客:7 条, 博主:0 条

  1. Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  2. Kaon Softwares : 2017年05月19日17:30:00  -48楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  3. Watch out for Lise Winicki! : 2017年05月19日17:30:15  -47楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  4. Auto Connection Manassas : 2017年05月20日11:17:24  -46楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  5. CMC Choppers : 2017年05月20日11:17:54  -45楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  6. Beverly Hills Body : 2017年05月20日11:18:09  -44楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

  7. Mountain Business Center LLC Rebecca Bextel : 2017年05月20日11:18:24  -43楼 @回复 回复

    Executive pleading poverty after $44M judgment Symons and family ordered to pay $34.2 million in fraud case Alan Symons In the United States Court of Appeals Arie Abekasis Diditan Financial ARIE ABEKASIS DIDITAN FINANCIAL DIDITAN GROUP ARIE ABEKASIS OF DIDITAN Richard Ellenbogen – Beverly Hills Body Richard Ellenbogen – Beverly Hills Body – Babak Dadvand Beverly Hills Body Plastic Surgeons – Richard Ellenbogen MD Richard Ellenbogen And Babak Dadvand Plastic Surgeons Of Beverly Hills Richard Ellenbogen Plastic Surgeon And Babak Dadvand Richard Ellenbogen Md Plastic Surgery Rhinoplasty Nose Job Richard Ellenbogen Beverly Hills Body Cosmetic Plastic And Reconstructive Surgeon Richard Ellenbogen Plastic Surgeon – Babak Dadvand Richard Ellenbogen Beverly Hills Body And Babak Dadvand Richard Ellenbogen – Beverly Hills Body BRIAN WILLIS Brockstar DAN ANTON: BACKLINKSVAULT and BrandlinksRank dan anton Kotton Grammer OMG Machines SCAM Don Tigert granger whitelaw Richard Cosentino John Schmutzer Exotic Images LLC CMC Choppers Jim Haszinger COSENTINO N Ripoff Reports Complaints Reviews Scams Lawsuits and Frauds Reported Jim Haszinger KaonSoftwares Kaon Softwares Emergency Vehicle Lights Light bars Sirens LED Outfitters Lise Winicki LuxSauna Auto Connection Manassas Complaint Review: Poggi Michael Michael Poggi Poggi’s Animal House Complaint Review: Michael Poggi Poggisanimalhouse 9547089441 Complaint Review: Mountain Business Center LLC Rebecca Bextel SwiftUnlocks Elite Client Services Complaint Review: Naushad Noordin Velani Complaint Review: Trey Knight Trey Knight trey night Haskel Grant Knight Haskel G Knight FHTM LIMU Danger Trey Knight Uses fake social security numbers Trey Knight Atlantic Beach, Florida Trey Knight is a Scammer LA Laser Center Gerardo Cornejo Velez 多伦多中医吴冠英 Conslines.com

给我留言

留言无头像?


×