Wikibooks

Structured Query Language/Data Manipulation Language

< Structured Query Language

← Data Query Language | Data Definition Language → Data Manipulation Language is used to modify the records in the database. It never modifies the schema of the database (table features, relationships, ...). It describes three statements: INSERT, UPDATE and DELETE.

INSERT statement

edit

The exhaustive syntax of the INSERT statement is as follows: 

INSERT INTO <table name>[ (<column name>[, <column name>]*)]
{
 VALUES (<value>[, <value>]*)
|
 SELECT [ALL | DISTINCT] <column name> [, [ALL | DISTINCT] <column name>]*
 FROM <table> [[AS | =] <alias> | [[FULL | LEFT | RIGHT] OUTER | INNER] JOIN <table> ON <expression>]
  [, <table> [[AS | =] <alias> | [[FULL | LEFT | RIGHT] OUTER | INNER] JOIN <table> ON <expression>]]*

 [WHERE <predicate> [{AND | OR} <predicate>]*]
 [GROUP BY <column name> [, <column name>]*
  [HAVING <predicate> [{AND | OR} <predicate>]]*]
 ]
 [ORDER BY <column name> [ASC | DESC] [, <column name> [ASC | DESC]]*]
 [LIMIT <count>]
};

The INSERT statement is used to add new records (rows) in a table. For instance, we want to add a new reunion:

  • Its primary key is 7,
  • Its name is "Job interview",
  • Its description is "Meeting with Mr. SPENCER",
  • Its priority is B,
  • Its planned,
  • Its date is on October 28, 2009,
  • Its hour is 18:30:00,
  • Its duration is 30,
  • Its office technical id is 23,
  • There is no pdf report.


  • The table before the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
  • Query:
INSERT INTO reunion (id_reunion, name, description, priority, planned, date, hour, duration, id_office, pdf_report)
VALUES (7, 'Job interview', 'Meeting with Mr. SPENCER', B, 1, 2009-10-28, 18:30:00, 30, 23, NULL);
  • The table after the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23

The INTO clause contains the name of the table where the record needs to be inserted. It can be followed by a list of columns in brackets. The VALUES clause contains the values to insert in brackets. If the column names are omitted, the VALUES clause must contains as many values as the number of columns of the table. The values are inserted in the table columns in the same order that the order in which the columns have been declared in the table. If the column names are mentioned, there must be as many column names as values. The values are respectively inserted into the named columns. If a column in the table is omitted, a NULL value is inserted instead.

The VALUES clause can be replaced by an inner SELECT statement. In this case, the INSERT statement can insert several rows in the table. For example, we want to plan twice all the reunion with a B priority level, one year later:

  • The table before the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
  • Query:
INSERT INTO reunion (id_reunion, name, description, priority, planned, date, hour, duration, id_office)
SELECT id_reunion + max(id_reunion), name, description, priority, 1, date + 0001-00-00, hour, duration, id_office
FROM reunion
WHERE priority = 'B';
  • The table after the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
10 Change What we need to change in the project. B 1 2009-06-03 9:30:00 90 41
12 Reporting Explanation to the new beginner. B 1 2010-03-15 14:00:00 60 7
13 Learning A new software version has been installed. B 1 2010-09-21 16:00:00 120 11
14 Job interview Meeting with Mr. SPENCER B 1 2010-10-28 18:30:00 30 23

UPDATE statement

edit

The exhaustive syntax of the UPDATE statement is as follows: 

UPDATE <table name>
SET <column name> = <value>[, <column name> = <value>]*
WHERE <predicate> [{AND | OR} <predicate>]*;

The UPDATE statement is used to modify already existent records in a table. The UPDATE clause is followed by the table name in which the rows need to be changed. The SET clause is followed by couples of column name and value. The values will be inserted in the given columns. The WHERE clause contains predicates. If the predicates are true for an existent row, this row will be modified.

For instance, we want to change the date, the hour and the description of the reunion with id 14:


  • The table before the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
10 Change What we need to change in the project. B 1 2009-06-03 9:30:00 90 41
12 Reporting Explanation to the new beginner. B 1 2010-03-15 14:00:00 60 7
13 Learning A new software version has been installed. B 1 2010-09-21 16:00:00 120 11
14 Job interview Meeting with Mr. SPENCER B 1 2010-10-28 18:30:00 30 23
  • Query:
UPDATE reunion
SET description = 'Meeting with Ms. JOHNSON', date = '2010-02-11', hour = '08:00:00'
WHERE id_reunion = '14';
  • The table after the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
10 Change What we need to change in the project. B 1 2009-06-03 9:30:00 90 41
12 Reporting Explanation to the new beginner. B 1 2010-03-15 14:00:00 60 7
13 Learning A new software version has been installed. B 1 2010-09-21 16:00:00 120 11
14 Job interview Meeting with Ms. JOHNSON B 1 2010-02-11 08:00:00 30 23

DELETE statement

edit

The exhaustive syntax of the DELETE statement is as follows: 

DELETE FROM <table name>
[WHERE <predicate> [{AND | OR} <predicate>]*];

The DELETE statement is used to remove specific rows in a table with conditions. The FROM clause is followed by the table name in which the rows need to be removed. The WHERE clause contains predicates. If the predicates are true for an row, this row will be removed. If the predicates are false for all the rows, the statement do nothing. A DELETE statement without WHERE clause empties the table.

For example, we want to remove all the reunions that last two hours:


  • The table before the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
4 Presentation Presentation of the project. D 0 2008-09-11 15:30:00 120 27
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
6 Learning A new software version has been installed. B 1 2009-09-21 16:00:00 120 11 785278...37528
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
10 Change What we need to change in the project. B 1 2009-06-03 9:30:00 90 41
12 Reporting Explanation to the new beginner. B 1 2010-03-15 14:00:00 60 7
13 Learning A new software version has been installed. B 1 2010-09-21 16:00:00 120 11
14 Job interview Meeting with Ms. JOHNSON B 1 2010-02-11 08:00:00 30 23
  • Query:
DELETE FROM reunion
WHERE duration = 120;
  • The table after the statement:
reunion
id_reunion name description priority planned date hour duration # id_office pdf_report
1 Planning We need to plan the project. A 1 2008-03-24 10:30:00 60 35 48644...846348
2 Progress What we have done. C 1 2008-05-12 14:00:00 30 13 9862...15676
3 Change What we need to change in the project. B 1 2008-06-03 9:30:00 90 41 34876...4846548
5 Reporting Explanation to the new beginner. B 1 2009-03-15 14:00:00 60 7 19739...37718
7 Job interview Meeting with Mr. SPENCER B 1 2009-10-28 18:30:00 30 23
10 Change What we need to change in the project. B 1 2009-06-03 9:30:00 90 41
12 Reporting Explanation to the new beginner. B 1 2010-03-15 14:00:00 60 7
14 Job interview Meeting with Ms. JOHNSON B 1 2010-02-11 08:00:00 30 23

Now you can use an already existing database schema to handle your own data.


Retrieved from "https://en.wikibooks.org/w/index.php?title=Structured_Query_Language/Data_Manipulation_Language&oldid=3677898"