SQL/MySQL/Insert Delete Update/Insert

Материал из SQL эксперт
Версия от 10:16, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Adding Multiple Rows to a Table

/* Create the table */
Drop TABLE Professor;
Drop TABLE Student;
CREATE TABLE Professor (
   ProfessorID INT NOT NULL PRIMARY KEY,
   Name        VARCHAR(50) NOT NULL)
TYPE = InnoDB;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;
/* Prepare the data */
INSERT INTO Student (StudentID,Name) VALUES (1,"John Jones");
INSERT INTO Student (StudentID,Name) VALUES (2,"Cury Butz");
INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Smith");

/* Real command */
INSERT INTO Professor (ProfessorID, Name) 
   SELECT StudentID + 7, Name 
   FROM Student;



Calculation in INSERT clause

/*
mysql> Drop table Inventory;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE Inventory
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL,
    ->    Reserved SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO Inventory VALUES (104, 16, 25-InStock, 0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from Inventory;
+-----+---------+---------+----------+
| ID  | InStock | OnOrder | Reserved |
+-----+---------+---------+----------+
| 104 |      16 |       9 |        0 |
+-----+---------+---------+----------+
1 row in set (0.00 sec)

*/
Drop table Inventory;
CREATE TABLE Inventory
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL,
   Reserved SMALLINT NOT NULL
);

INSERT INTO Inventory VALUES (104, 16, 25-InStock, 0);

select * from Inventory;



Calculation in INSERT command

/*
mysql> Drop table CDs;
mysql> CREATE TABLE CDs (
    ->    CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    CDName VARCHAR(50) NOT NULL,
    ->    Copyright YEAR,
    ->    NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    NumberInStock TINYINT UNSIGNED,
    ->    NumberOnReserve TINYINT UNSIGNED NOT NULL,
    ->    NumberAvailable TINYINT UNSIGNED NOT NULL,
    ->    CDType VARCHAR(20),
    ->    RowAdded TIMESTAMP
    -> );
Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO CDs VALUES
    ->    (NULL, "Earle", 1996, 2, 10, 3, NumberInStock-NumberOnReserve, "Country", NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from CDs;
+------+--------+-----------+-------------+---------------+-----------------+-----------------+---------+---------------------+
| CDID | CDName | Copyright | NumberDisks | NumberInStock | NumberOnReserve | NumberAvailable | CDType  | RowAdded            |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+---------+---------------------+
|    1 | Earle  |      1996 |           2 |            10 |               3 |            7 | Country | 2005-10-09 09:19:46 |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+---------+---------------------+
1 row in set (0.00 sec)
*/
Drop table CDs;

CREATE TABLE CDs (
   CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CDName VARCHAR(50) NOT NULL,
   Copyright YEAR,
   NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
   NumberInStock TINYINT UNSIGNED,
   NumberOnReserve TINYINT UNSIGNED NOT NULL,
   NumberAvailable TINYINT UNSIGNED NOT NULL,
   CDType VARCHAR(20),
   RowAdded TIMESTAMP
);

INSERT INTO CDs VALUES 
   (NULL, "Earle", 1996, 2, 10, 3, NumberInStock-NumberOnReserve, "Country", NULL);

select * from CDs;



Insert three rows together

/*
mysql> Drop table CDs;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE CDs (
    ->    CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    CDName VARCHAR(50) NOT NULL,
    ->    Copyright YEAR,
    ->    NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    NumberInStock TINYINT UNSIGNED,
    ->    NumberOnReserve TINYINT UNSIGNED NOT NULL,
    ->    NumberAvailable TINYINT UNSIGNED NOT NULL,
    ->    CDType VARCHAR(20),
    ->    RowAdded TIMESTAMP
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO CDs
    -> (CDName, Copyright, NumberDisks, NumberInStock, NumberOnReserve, NumberAvailable, CDType)
    -> VALUES
    -> ("Variations", 1999, 1, 9, 0, NumberInStock-NumberOnReserve, "Blues"),
    -> ("The", 1990, 1, 14, 2, NumberInStock-NumberOnReserve, "Popular"),
    -> ("Shocked", 1988, 1, 6, 1, NumberInStock-NumberOnReserve, "Folk-Rock");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from CDs;
+------+------------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
| CDID | CDName     | Copyright | NumberDisks | NumberInStock | NumberOnReserve | NumberAvailable | CDType    | RowAdded            |
+------+------------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
|    1 | Variations |      1999 |           1 |             9 |               0 |               9 | Blues     | 2005-10-09 09:19:46 |
|    2 | The        |      1990 |           1 |            14 |               2 |              12 | Popular   | 2005-10-09 09:19:46 |
|    3 | Shocked    |      1988 |           1 |             6 |               1 |               5 | Folk-Rock | 2005-10-09 09:19:46 |
+------+------------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
3 rows in set (0.01 sec)
*/
Drop table CDs;

CREATE TABLE CDs (
   CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CDName VARCHAR(50) NOT NULL,
   Copyright YEAR,
   NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
   NumberInStock TINYINT UNSIGNED,
   NumberOnReserve TINYINT UNSIGNED NOT NULL,
   NumberAvailable TINYINT UNSIGNED NOT NULL,
   CDType VARCHAR(20),
   RowAdded TIMESTAMP
);

INSERT INTO CDs 
(CDName, Copyright, NumberDisks, NumberInStock, NumberOnReserve, NumberAvailable, CDType)
VALUES 
("Variations", 1999, 1, 9, 0, NumberInStock-NumberOnReserve, "Blues"),
("The", 1990, 1, 14, 2, NumberInStock-NumberOnReserve, "Popular"),
("Shocked", 1988, 1, 6, 1, NumberInStock-NumberOnReserve, "Folk-Rock");
select * from CDs;



Simple INSERT clause

/*
mysql> Drop table Inventory;
mysql> CREATE TABLE Inventory
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL,
    ->    Reserved SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO Inventory VALUES (101, 10, 15, 4),
    ->                              (102, 1, 9, 3),
    ->                              (103, 5, 2, 13);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from Inventory;
+-----+---------+---------+----------+
| ID  | InStock | OnOrder | Reserved |
+-----+---------+---------+----------+
| 101 |      10 |      15 |        4 |
| 102 |       1 |       9 |        3 |
| 103 |       5 |       2 |       13 |
+-----+---------+---------+----------+
3 rows in set (0.01 sec)

*/
Drop table Inventory;
CREATE TABLE Inventory
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL,
   Reserved SMALLINT NOT NULL
);

INSERT INTO Inventory VALUES (101, 10, 15, 4), 
                             (102, 1, 9, 3), 
                             (103, 5, 2, 13);
select * from Inventory;



Use INSERT LOW_PRIORITY command

/*
mysql> Drop table CDs;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE CDs (
    ->    CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    CDName VARCHAR(50) NOT NULL,
    ->    Copyright YEAR,
    ->    NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    NumberInStock TINYINT UNSIGNED,
    ->    NumberOnReserve TINYINT UNSIGNED NOT NULL,
    ->    NumberAvailable TINYINT UNSIGNED NOT NULL,
    ->    CDType VARCHAR(20),
    ->    RowAdded TIMESTAMP
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT LOW_PRIORITY INTO CDs
    -> (CDName, Copyright, NumberDisks, NumberInStock, NumberOnReserve, NumberAvailable, CDType)
    -> VALUES
    -> ("Rain", 1995, DEFAULT, 13, 2, NumberInStock - NumberOnReserve, "Classical");
Query OK, 1 row affected (0.00 sec)
mysql> select * from CDs;
+------+--------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
| CDID | CDName | Copyright | NumberDisks | NumberInStock | NumberOnReserve | NumberAvailable | CDType    | RowAdded            |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
|    1 | Rain   |      1995 |           1 |            13 |               2 |           11 | Classical | 2005-10-09 09:19:46 |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+-----------+---------------------+
1 row in set (0.00 sec)
*/
Drop table CDs;

CREATE TABLE CDs (
   CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CDName VARCHAR(50) NOT NULL,
   Copyright YEAR,
   NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
   NumberInStock TINYINT UNSIGNED,
   NumberOnReserve TINYINT UNSIGNED NOT NULL,
   NumberAvailable TINYINT UNSIGNED NOT NULL,
   CDType VARCHAR(20),
   RowAdded TIMESTAMP
);

INSERT LOW_PRIORITY INTO CDs 
(CDName, Copyright, NumberDisks, NumberInStock, NumberOnReserve, NumberAvailable, CDType)
VALUES 
("Rain", 1995, DEFAULT, 13, 2, NumberInStock - NumberOnReserve, "Classical");

select * from CDs;



Using INSERT DELAYED

/*
mysql> Drop table CDs;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE CDs (
    ->    CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    CDName VARCHAR(50) NOT NULL,
    ->    Copyright YEAR,
    ->    NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ->    NumberInStock TINYINT UNSIGNED,
    ->    NumberOnReserve TINYINT UNSIGNED NOT NULL,
    ->    NumberAvailable TINYINT UNSIGNED NOT NULL,
    ->    CDType VARCHAR(20),
    ->    RowAdded TIMESTAMP
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT DELAYED INTO CDs
    -> SET CDName="Blues", Copyright=1998,
    ->    NumberDisks=DEFAULT, NumberInStock=4, NumberOnReserve=1,
    ->    NumberAvailable=NumberInStock-NumberOnReserve, CDType="Blues";
Query OK, 1 row affected (0.01 sec)
mysql> select * from CDs;
+------+--------+-----------+-------------+---------------+-----------------+-----------------+--------+---------------------+
| CDID | CDName | Copyright | NumberDisks | NumberInStock | NumberOnReserve | NumberAvailable | CDType | RowAdded            |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+--------+---------------------+
|    1 | Blues  |      1998 |           1 |             4 |               1 |            3 | Blues  | 2005-10-09 09:19:47 |
+------+--------+-----------+-------------+---------------+-----------------+-----------------+--------+---------------------+
1 row in set (0.00 sec)
*/
Drop table CDs;

CREATE TABLE CDs (
   CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CDName VARCHAR(50) NOT NULL,
   Copyright YEAR,
   NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
   NumberInStock TINYINT UNSIGNED,
   NumberOnReserve TINYINT UNSIGNED NOT NULL,
   NumberAvailable TINYINT UNSIGNED NOT NULL,
   CDType VARCHAR(20),
   RowAdded TIMESTAMP
);

INSERT DELAYED INTO CDs
SET CDName="Blues", Copyright=1998,
   NumberDisks=DEFAULT, NumberInStock=4, NumberOnReserve=1,
   NumberAvailable=NumberInStock-NumberOnReserve, CDType="Blues";
select * from CDs;
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Insert_Delete_Update/Insert&oldid=5167»