в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:21Z

1 версия

Новая страница

==Adding Multiple Rows to a Table==



<source lang="sql">
/* 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;

</source>

==Calculation in INSERT clause==



<source lang="sql">
/*
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;

</source>

==Calculation in INSERT command==



<source lang="sql">
/*
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;

</source>

==Insert three rows together==



<source lang="sql">
/*
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;

</source>

==Simple INSERT clause==



<source lang="sql">
/*
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;

</source>

==Use INSERT LOW_PRIORITY command==



<source lang="sql">
/*
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;

</source>

==Using INSERT DELAYED==



<source lang="sql">
/*
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;

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Insert Delete Update/Insert - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия