SQL/MySQL/Insert Delete Update/Insert

Содержание

1 Adding Multiple Rows to a Table
2 Calculation in INSERT clause
3 Calculation in INSERT command
4 Insert three rows together
5 Simple INSERT clause
6 Use INSERT LOW_PRIORITY command
7 Using INSERT DELAYED

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);

/

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");

/

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";

/

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

);

  NumberDisks=DEFAULT, NumberInStock=4, NumberOnReserve=1,
  NumberAvailable=NumberInStock-NumberOnReserve, CDType="Blues";

select * from CDs;

      </source>

SQL/MySQL/Insert Delete Update/Insert

Содержание

Adding Multiple Rows to a Table

Calculation in INSERT clause

Calculation in INSERT command

Insert three rows together

Simple INSERT clause

Use INSERT LOW_PRIORITY command

Using INSERT DELAYED

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты