SQL:Data Manipulation Language

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

Introduction

The Data Manipulation Language (DML) is used to change, add and delete data in an existing table. The following SQL Commands are available:

  • INSERT
  • UPDATE
  • DELETE
Icon Objective.png

Objective

By the end of this page you should be :

  • familiar with the basic DML commands, INSERT, UPDATE and DELETE

DDL Example

In the heights database the following DML commands were sent to MySQL:

INSERT INTO tblPeople (PerFullName, PerHeightmm )VALUES
  ('C. Lyon',1232),
  ('G. Raff',5232);
 
INSERT INTO tblPeople VALUES
  (NULL,'L. E. Fant',1500);
 
DELETE FROM tblPeople WHERE PerFullName = 'G. Raff';
 
UPDATE tblPeople SET PerHeightmm = '1300' WHERE  PerFullName = 'C. Lyon';

.. and if I wanted to create a backup of the People table I could use

DELETE FROM tblPeopleBak;
INSERT INTO `tblPeopleBak` SELECT * FROM `tblPeople` ;
    • WARNING** the DELETE here permenantly removes all records from the Bak table.. be very careful you have entered the correct table!

INSERT

Backus-Naur Form

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

For the complete format refer to 12.2.5. INSERT Syntax (2010)[1]

Examples

INSERT INTO tblPeople (PerFullName, PerHeightmm )VALUES
  ('C. Lyon',1232),
  ('G. Raff',5232);

If you want to insert into all fields (and there is an autonumber one), and don't want to specify all field names use Null (or \N )

INSERT INTO tblPeople VALUES
  (NULL,'L. E. Fant',1500);

Note you can also use \N for null

UPDATE

Backus-Naur Form

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

For the complete format refer to 12.2.11. UPDATE Syntax. (2010)[2]

Examples

UPDATE tblPeople SET PerHeightmm = '1300' WHERE  PerFullName = 'C. Lyon';

DELETE

Backus-Naur Form

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

For the complete format refer to 12.2.2. DELETE Syntax. (2010)[3]

Examples

DELETE FROM tblPeople WHERE PerFullName = 'G. Raff';

Icon References.png References

  1. 12.2.5. INSERT Syntax (2010) In MySQL 5.0 Reference Manual. Oracle. Retrieved October 24, 2010 from http://dev.mysql.com/doc/refman/5.0/en/insert.html
  2. 12.2.11. UPDATE Syntax. (2010) In MySQL 5.0 Reference Manual. Oracle. Retrieved October 24, 2010 from http://dev.mysql.com/doc/refman/5.0/en/update.html
  3. 12.2.2. DELETE Syntax. (2010) In MySQL 5.0 Reference Manual. Oracle. Retrieved October 24, 2010 from http://dev.mysql.com/doc/refman/5.0/en/delete.html

SQL:Data Manipulation Language. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 17, 2017, from http://www.virtualmv.com/wiki/index.php?title=SQL%3AData_Manipulation_Language    (zotero)