MySQL Tutorial/Insert Update Delete/Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 09:50, 26 мая 2010
Содержание
- 1 Change multiple columns using one statement
- 2 Syntac for UPDATE statement
- 3 Update a value based on the value it currently holds
- 4 UPDATE-ing Records
- 5 Update only 5 records
- 6 Update two tables in one update statement
- 7 Use the LIMIT function to control the number of rows that are affected by your UPDATE statement
Change multiple columns using one statement
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.02 sec)
mysql>
mysql>
mysql> UPDATE employee SET first_name = "Other Title",
-> salary = 9999
-> WHERE (ID = 1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from employee;
+------+-------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+-------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Other Title | Martin | 1996-07-25 | 2006-07-25 | 9999.00 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+-------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.02 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
Syntac for UPDATE statement
UPDATE tablename SET columnname = value WHERE x=y
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> UPDATE Employee SET Last_Name = "Smith" WHERE ID = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Smith | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.02 sec)
mysql>
Update a value based on the value it currently holds
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> UPDATE Employee SET Salary = Salary + 1;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql>
mysql> select * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4323.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7898.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1233.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
UPDATE-ing Records
The syntax of the update statement.
UPDATE <table_name>
SET <column_name> = "new_value"
WHERE (<column_name> = "some_value");
Update only 5 records
mysql>
mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY,
-> BookName VARCHAR(40) NOT NULL,
-> InStock SMALLINT NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> 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;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, "Java", 12),
-> (102, "PHP", 17),
-> (103, "MySQL", 23),
-> (104, "Perl", 32),
-> (105, "Pyton", 6),
-> (106, "www.sqle.ru", 28);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, "2001-11-12 12:30:00"),
-> (1002, 101, 1, "2002-10-16 12:31:00"),
-> (1003, 103, 2, "2003-02-11 12:34:00"),
-> (1004, 104, 3, "2004-01-19 12:36:00"),
-> (1005, 102, 1, "2005-12-17 12:41:00"),
-> (1006, 103, 2, "2006-10-18 12:59:00"),
-> (1007, 101, 1, "2004-11-21 13:01:00"),
-> (1008, 103, 1, "2014-10-16 13:02:00"),
-> (1009, 102, 4, "1994-09-02 13:22:00"),
-> (1010, 101, 2, "1995-10-04 13:30:00"),
-> (1011, 103, 1, "1996-08-12 13:32:00"),
-> (1012, 105, 1, "2004-10-03 13:40:00"),
-> (1013, 106, 2, "2002-05-12 13:44:00"),
-> (1014, 103, 1, "2001-10-01 14:01:00"),
-> (1015, 106, 1, "1997-05-05 14:05:00"),
-> (1016, 104, 2, "1998-10-07 14:28:00"),
-> (1017, 105, 1, "2004-03-12 14:31:00"),
-> (1018, 102, 1, "2004-10-21 14:32:00"),
-> (1019, 106, 3, "1991-01-30 14:49:00"),
-> (1020, 103, 1, "1990-10-12 14:51:00");
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 12 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.sqle.ru | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2001-11-12 12:30:00 |
| 1002 | 101 | 1 | 2002-10-16 12:31:00 |
| 1003 | 103 | 2 | 2003-02-11 12:34:00 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 2 | 2006-10-18 12:59:00 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 1 | 2014-10-16 13:02:00 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 1 | 2001-10-01 14:01:00 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
mysql>
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>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 12 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.sqle.ru | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 2 | 2007-07-23 19:09:37 |
| 1002 | 101 | 1 | 2002-10-16 12:31:00 |
| 1003 | 103 | 3 | 2007-07-23 19:09:37 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 3 | 2007-07-23 19:09:37 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 2 | 2007-07-23 19:09:37 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 2 | 2007-07-23 19:09:37 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.05 sec)
mysql>
Update two tables in one update statement
mysql>
mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY,
-> BookName VARCHAR(40) NOT NULL,
-> InStock SMALLINT NOT NULL
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> 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;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (101, "Java", 12),
-> (102, "PHP", 17),
-> (103, "MySQL", 23),
-> (104, "Perl", 32),
-> (105, "Pyton", 6),
-> (106, "www.sqle.ru", 28);
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO Orders VALUES (1001, 103, 1, "2001-11-12 12:30:00"),
-> (1002, 101, 1, "2002-10-16 12:31:00"),
-> (1003, 103, 2, "2003-02-11 12:34:00"),
-> (1004, 104, 3, "2004-01-19 12:36:00"),
-> (1005, 102, 1, "2005-12-17 12:41:00"),
-> (1006, 103, 2, "2006-10-18 12:59:00"),
-> (1007, 101, 1, "2004-11-21 13:01:00"),
-> (1008, 103, 1, "2014-10-16 13:02:00"),
-> (1009, 102, 4, "1994-09-02 13:22:00"),
-> (1010, 101, 2, "1995-10-04 13:30:00"),
-> (1011, 103, 1, "1996-08-12 13:32:00"),
-> (1012, 105, 1, "2004-10-03 13:40:00"),
-> (1013, 106, 2, "2002-05-12 13:44:00"),
-> (1014, 103, 1, "2001-10-01 14:01:00"),
-> (1015, 106, 1, "1997-05-05 14:05:00"),
-> (1016, 104, 2, "1998-10-07 14:28:00"),
-> (1017, 105, 1, "2004-03-12 14:31:00"),
-> (1018, 102, 1, "2004-10-21 14:32:00"),
-> (1019, 106, 3, "1991-01-30 14:49:00"),
-> (1020, 103, 1, "1990-10-12 14:51:00");
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 12 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.sqle.ru | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2001-11-12 12:30:00 |
| 1002 | 101 | 1 | 2002-10-16 12:31:00 |
| 1003 | 103 | 2 | 2003-02-11 12:34:00 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 2 | 2006-10-18 12:59:00 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 1 | 2014-10-16 13:02:00 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 1 | 2001-10-01 14:01:00 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql>
mysql>
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.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql>
mysql> select * from Books;
+--------+----------------+---------+
| BookID | BookName | InStock |
+--------+----------------+---------+
| 101 | Java | 10 |
| 102 | PHP | 17 |
| 103 | MySQL | 23 |
| 104 | Perl | 32 |
| 105 | Pyton | 6 |
| 106 | www.sqle.ru | 28 |
+--------+----------------+---------+
6 rows in set (0.00 sec)
mysql>
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2001-11-12 12:30:00 |
| 1002 | 101 | 3 | 2007-07-23 19:09:39 |
| 1003 | 103 | 2 | 2003-02-11 12:34:00 |
| 1004 | 104 | 3 | 2004-01-19 12:36:00 |
| 1005 | 102 | 1 | 2005-12-17 12:41:00 |
| 1006 | 103 | 2 | 2006-10-18 12:59:00 |
| 1007 | 101 | 1 | 2004-11-21 13:01:00 |
| 1008 | 103 | 1 | 2014-10-16 13:02:00 |
| 1009 | 102 | 4 | 1994-09-02 13:22:00 |
| 1010 | 101 | 2 | 1995-10-04 13:30:00 |
| 1011 | 103 | 1 | 1996-08-12 13:32:00 |
| 1012 | 105 | 1 | 2004-10-03 13:40:00 |
| 1013 | 106 | 2 | 2002-05-12 13:44:00 |
| 1014 | 103 | 1 | 2001-10-01 14:01:00 |
| 1015 | 106 | 1 | 1997-05-05 14:05:00 |
| 1016 | 104 | 2 | 1998-10-07 14:28:00 |
| 1017 | 105 | 1 | 2004-03-12 14:31:00 |
| 1018 | 102 | 1 | 2004-10-21 14:32:00 |
| 1019 | 106 | 3 | 1991-01-30 14:49:00 |
| 1020 | 103 | 1 | 1990-10-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.02 sec)
mysql>
mysql>
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.05 sec)
mysql>
Use the LIMIT function to control the number of rows that are affected by your UPDATE statement
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> UPDATE Employee SET Salary = Salary + 1 LIMIT 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql>
mysql> select * from employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
This statement would limit the update to the first 3 rows of the table.
mysql>
mysql>
mysql>
mysql>