SQL/MySQL/Insert Delete Update/Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
- 1 Assign value in select clause
- 2 Do PLUS calculation in where clause
- 3 Modifying Row Data
- 4 Update records with calculation based on two tables
- 5 Update two tables with calculation
- 6 Update with condition
- 7 Update with limitation and calculation
- 8 Using UPDATE Statements to Modify Data in Joined Tables
Assign value in select clause
/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> UPDATE employee SET
-> salary=220000, perks=55000
-> WHERE title="CEO";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
*/
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
UPDATE employee SET
salary=220000, perks=55000
WHERE title="CEO";
select * from employee;
Do PLUS calculation in where clause
/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.00 sec)
mysql> UPDATE employee SET salary = salary + 20000, perks = perks + 5000
-> WHERE title="CEO";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
*/
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
UPDATE employee SET salary = salary + 20000, perks = perks + 5000
WHERE title="CEO";
select * from employee;
Modifying Row Data
/* Create the table */
Drop TABLE Professor;
CREATE TABLE Professor (
ProfessorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL)
TYPE = InnoDB;
/* Prepare the data */
INSERT INTO Professor (ProfessorID,Name) VALUES (1,"John Jones");
INSERT INTO Professor (ProfessorID,Name) VALUES (2,"Cury Butz");
INSERT INTO Professor (ProfessorID,Name) VALUES (3,"JJ Smith");
/* Real command */
UPDATE Professor
SET Name = CONCAT("Prof. ", Name)
WHERE ProfessorID > 6;
Update records with calculation based on two tables
/*
mysql> select * from Books;
+--------+-----------+---------+
| BookID | BookName | InStock |
+--------+-----------+---------+
| 1 | Poet | 1934 |
| 2 | Ohio | 1919 |
| 3 | Angels | 1966 |
| 4 | Black | 1932 |
| 101 | Writing | 10 |
| 102 | News | 17 |
| 103 | Angels | 23 |
| 104 | Poet | 32 |
| 105 | Dunces | 6 |
| 106 | Solitude | 28 |
| 107 | Postcards | 1992 |
| 108 | The | 1993 |
+--------+-----------+---------+
12 rows in set (0.00 sec)
mysql> UPDATE Books, Orders
-> SET Books.InStock=Books.InStock-Orders.Quantity
-> WHERE Books.BookID=Orders.BookID
-> AND Orders.OrderID=1002;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Books;
+--------+-----------+---------+
| BookID | BookName | InStock |
+--------+-----------+---------+
| 1 | Poet | 1934 |
| 2 | Ohio | 1919 |
| 3 | Angels | 1966 |
| 4 | Black | 1932 |
| 101 | Writing | 9 |
| 102 | News | 17 |
| 103 | Angels | 23 |
| 104 | Poet | 32 |
| 105 | Dunces | 6 |
| 106 | Solitude | 28 |
| 107 | Postcards | 1992 |
| 108 | The | 1993 |
+--------+-----------+---------+
12 rows in set (0.01 sec)
*/
Drop table Books;
Drop table Orders;
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookName VARCHAR(40) NOT NULL,
InStock SMALLINT NOT NULL
)
ENGINE=INNODB;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
INSERT INTO Books VALUES (101, "Writing", 12),
(102, "News", 17),
(103, "Angels", 23),
(104, "Poet", 32),
(105, "Dunces", 6),
(106, "Solitude", 28);
select * from Books;
UPDATE Books, Orders
SET Books.InStock=Books.InStock-Orders.Quantity
WHERE Books.BookID=Orders.BookID
AND Orders.OrderID=1002;
select * from Books;
Update two tables with calculation
/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql> select * from Books;
+--------+-----------+---------+
| BookID | BookName | InStock |
+--------+-----------+---------+
| 1 | Poet | 1934 |
| 2 | Ohio | 1919 |
| 3 | Angels | 1966 |
| 4 | Black | 1932 |
| 101 | Writing | 9 |
| 102 | News | 17 |
| 103 | Angels | 23 |
| 104 | Poet | 32 |
| 105 | Dunces | 6 |
| 106 | Solitude | 28 |
| 107 | Postcards | 1992 |
| 108 | The | 1993 |
+--------+-----------+---------+
12 rows in set (0.00 sec)
mysql> UPDATE Books, Orders
-> SET Orders.Quantity=Orders.Quantity+2,
-> Books.InStock=Books.InStock-2
-> WHERE Books.BookID=Orders.BookID
-> AND Orders.OrderID = 1002;
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 3 | 2005-10-09 08:51:30 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql> select * from Books;
+--------+-----------+---------+
| BookID | BookName | InStock |
+--------+-----------+---------+
| 1 | Poet | 1934 |
| 2 | Ohio | 1919 |
| 3 | Angels | 1966 |
| 4 | Black | 1932 |
| 101 | Writing | 7 |
| 102 | News | 17 |
| 103 | Angels | 23 |
| 104 | Poet | 32 |
| 105 | Dunces | 6 |
| 106 | Solitude | 28 |
| 107 | Postcards | 1992 |
| 108 | The | 1993 |
+--------+-----------+---------+
12 rows in set (0.00 sec)
*/
Drop table Books;
Drop table Orders;
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookName VARCHAR(40) NOT NULL,
InStock SMALLINT NOT NULL
)
ENGINE=INNODB;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
INSERT INTO Books VALUES (101, "Writing", 12),
(102, "News", 17),
(103, "Angels", 23),
(104, "Poet", 32),
(105, "Dunces", 6),
(106, "Solitude", 28);
select * from Orders;
select * from Books;
UPDATE Books, Orders
SET Orders.Quantity=Orders.Quantity+2,
Books.InStock=Books.InStock-2
WHERE Books.BookID=Orders.BookID
AND Orders.OrderID = 1002;
select * from Orders;
select * from Books;
Update with condition
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> UPDATE Bird SET birth = "1989-08-31" WHERE name = "BlueBird";
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1989-08-31 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.03 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
select * from Bird;
UPDATE Bird SET birth = "1989-08-31" WHERE name = "BlueBird";
select * from Bird;
Update with limitation and calculation
/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.01 sec)
mysql> UPDATE Orders
-> SET Quantity=Quantity+1
-> WHERE BookID=103
-> ORDER BY DateOrdered DESC
-> LIMIT 5;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 2 | 2005-10-09 08:51:26 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 2 | 2005-10-09 08:51:26 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 2 | 2005-10-09 08:51:26 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 2 | 2005-10-09 08:51:26 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 2 | 2005-10-09 08:51:26 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
*/
Drop table Orders;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
select * from Orders;
UPDATE Orders
SET Quantity=Quantity+1
WHERE BookID=103
ORDER BY DateOrdered DESC
LIMIT 5;
select * from Orders;
Using UPDATE Statements to Modify Data in Joined Tables
Drop table DVDs;
Drop table Studios;
CREATE TABLE DVDs
(
DVDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DVDName VARCHAR(60) NOT NULL,
NumDisks TINYINT NOT NULL DEFAULT 1,
YearRlsd YEAR NOT NULL,
StudID VARCHAR(4) NOT NULL
) ENGINE=INNODB;
CREATE TABLE Studios
(
StudID VARCHAR(4) NOT NULL,
StudDescrip VARCHAR(40) NOT NULL,
PRIMARY KEY (StudID)
)
ENGINE=INNODB;
INSERT INTO Studios VALUES ("s101", "Universal Studios"),
("s102", "Warner Brothers"),
("s103", "Time Warner"),
("s104", "Columbia Pictures"),
("s105", "Paramount Pictures"),
("s106", "Twentieth Century Fox"),
("s107", "Merchant Ivory Production");
INSERT INTO DVDs
(DVDName, NumDisks, YearRlsd, StudID)
VALUES
("Christmas", 1, 2000, "s105"),
("What", 1, 2001, "s103"),
("Out", 1, 2000, "s101"),
("Falcon", 1, 2000, "s103"),
("Amadeus", 1, 1997, "s103"),
("Show", 2, 2000, "s106"),
("View", 1, 2000, "s107"),
("Mash", 2, 2001, "s106");
select * from DVDs;
select * from Studios;
UPDATE DVDs, Studios
SET DVDs.StutID="s2"
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip="Time Warner";
select * from DVDs;
select * from Studios;