SQL/MySQL/Table Index/Create Table

Материал из SQL эксперт
Перейти к: навигация, поиск

Create Simple Tables

   <source lang="sql">

/* mysql> DROP TABLE Employee; mysql> CREATE TABLE Employee ( 

   ->     Name VARCHAR(50),
   ->     Phone VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.21 sec) mysql> Show tables; +-------------+ | Tables_in_t | +-------------+ | employee | | enrollment | | student | | studentexam | +-------------+ 4 rows in set (0.07 sec) mysql> Describe Employee; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Name | varchar(50) | YES | | NULL | | | Phone | varchar(15) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.03 sec) mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323"); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO Employee (Name) VALUES ("John Doe"); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL); Query OK, 1 row affected (0.01 sec) mysql> Select * from Employee; +----------+----------+ | Name | Phone | +----------+----------+ | Joe Wang | 666 2323 | | John Doe | NULL | | John Doe | NULL | +----------+----------+ 3 rows in set (0.08 sec)

  • /

DROP TABLE Employee; CREATE TABLE Employee ( 

   Name VARCHAR(50), 
   Phone VARCHAR(15)

); Show tables; Describe Employee; INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323"); INSERT INTO Employee (Name) VALUES ("John Doe"); INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL); Select * from Employee; 

      </source>


Create table: auto increment primary key

   <source lang="sql">

Drop table Product;

CREATE TABLE Product ( 

  ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name VARCHAR(40) NOT NULL,
  PRIMARY KEY (ID)

); Describe Product; 

      </source>


Create table: BLOB

   <source lang="sql">

/* mysql> Drop table Inventory; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Inventory 

   -> (
   ->    ID SMALLINT UNSIGNED,
   ->    Name VARCHAR(40),
   ->    Photo BLOB,
   ->    Quantity INT UNSIGNED
   -> );

Query OK, 0 rows affected (0.04 sec) mysql> Describe Inventory; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | YES | | NULL | | | Name | varchar(40) | YES | | NULL | | | Photo | blob | YES | | NULL | | | Quantity | int(10) unsigned | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

  • /

Drop table Inventory; CREATE TABLE Inventory ( 

  ID SMALLINT UNSIGNED,
  Name VARCHAR(40),
  Photo BLOB,
  Quantity INT UNSIGNED

); Describe Inventory; 

      </source>


Create table: char set and language

   <source lang="sql">

/* mysql> Drop table Product; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Product 

   -> (
   ->    ID SMALLINT UNSIGNED,
   ->    Name VARCHAR(40),
   ->    DescriptionDoc TEXT CHARACTER SET latin1 COLLATE latin1_bin
   -> );

Query OK, 0 rows affected (0.12 sec) mysql> Describe Product; +----------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | YES | | NULL | | | Name | varchar(40) | YES | | NULL | | | DescriptionDoc | text | YES | | NULL | | +----------------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

  • /

Drop table Product;

CREATE TABLE Product ( 

  ID SMALLINT UNSIGNED,
  Name VARCHAR(40),
  DescriptionDoc TEXT CHARACTER SET latin1 COLLATE latin1_bin

); Describe Product; 

      </source>


Create table: char, varchar

   <source lang="sql">

Drop table Catalog; CREATE TABLE Catalog ( 

  ID SMALLINT,
  Description VARCHAR(40),
  Name CHAR(3),
  Price DECIMAL(7,2)

); Describe Catalog; 

      </source>


Create table: default int value

   <source lang="sql">

/* mysql> Drop table Employee; mysql> CREATE TABLE Employee 

   -> (
   ->    ID SMALLINT UNSIGNED NOT NULL,
   ->    YearBorn YEAR NOT NULL,
   ->    CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"
   -> );

Query OK, 0 rows affected (0.11 sec) mysql> Describe Employee; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | | | 0 | | | YearBorn | year(4) | | | 0000 | | | CityBorn | varchar(40) | | | Unknown | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

  • /

Drop table Shelf;

CREATE TABLE Shelf ( 

  ID SMALLINT UNSIGNED NOT NULL,
  YearBorn YEAR NOT NULL,
  NumBooks SMALLINT NOT NULL DEFAULT 1

); Describe Shelf; 

      </source>


Create table: default value for column

   <source lang="sql">

/* mysql> Drop table Books; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Books 

   -> (
   ->    ID SMALLINT NOT NULL,
   ->    Name VARCHAR(40) NOT NULL,
   ->    PubID SMALLINT NOT NULL DEFAULT "Unknown"
   -> )
   -> ENGINE=INNODB;

Query OK, 0 rows affected (0.09 sec) mysql> Describe Books; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ID | smallint(6) | | | 0 | | | Name | varchar(40) | | | | | | PubID | smallint(6) | | | 0 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

  • /

Drop table Books; CREATE TABLE Books ( 

  ID SMALLINT NOT NULL,
  Name VARCHAR(40) NOT NULL,
  PubID SMALLINT NOT NULL DEFAULT "Unknown"

) ENGINE=INNODB; Describe Books; 

      </source>


Create table: engine type INNODB

   <source lang="sql">

Drop table Employee; CREATE TABLE Employee ( 

  AuthID SMALLINT UNSIGNED NOT NULL,
  YearBorn YEAR NOT NULL,
  CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"

) ENGINE=INNODB; Describe Employee; 

      </source>


Create table: ENUM and set

   <source lang="sql">

/* mysql> Drop table Bike; mysql> CREATE TABLE Bike 

   -> (
   ->    ID SMALLINT UNSIGNED,
   ->    Model VARCHAR(40),
   ->    Color ENUM("red", "blue", "green", "yellow"),
   ->    Options SET("rack", "light", "helmet", "lock")
   -> );

Query OK, 0 rows affected (0.09 sec) mysql> Describe Bike; +---------+-------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------------------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | YES | | NULL | | | Model | varchar(40) | YES | | NULL | | | Color | enum("red","blue","green","yellow") | YES | | NULL | | | Options | set("rack","light","helmet","lock") | YES | | NULL | | +---------+-------------------------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)

  • /

Drop table Bike; CREATE TABLE Bike ( 

  ID SMALLINT UNSIGNED,
  Model VARCHAR(40),
  Color ENUM("red", "blue", "green", "yellow"),
  Options SET("rack", "light", "helmet", "lock")

); Describe Bike; 


      </source>


Create table: foreign key

   <source lang="sql">

Drop table Orders; CREATE TABLE Orders ( 

  ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
  ModelID SMALLINT UNSIGNED NOT NULL,
  Desciption VARCHAR(40),
  FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
     ON DELETE CASCADE ON UPDATE CASCADE

); Describe Orders; 

      </source>


Create table: not null and default value

   <source lang="sql">

/* mysql> Drop table Product; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Product 

   -> (
   ->    ID SMALLINT UNSIGNED NOT NULL,
   ->    Name VARCHAR(40) NOT NULL
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> Describe Product; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | | | 0 | | | Name | varchar(40) | | | | | +-------+----------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)

  • /

Drop table Employee; CREATE TABLE Employee ( 

  ID SMALLINT UNSIGNED NOT NULL,
  YearBorn YEAR NOT NULL,
  CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"

); Describe Employee; 

      </source>


Create table: null and not null

   <source lang="sql">

Drop table Product;

CREATE TABLE Product ( 

  ID SMALLINT UNSIGNED NOT NULL,
  Name VARCHAR(40) NOT NULL

); Describe Product; 

      </source>


Create table: primary key

   <source lang="sql">

/* mysql> Drop table Orders; mysql> CREATE TABLE Orders 

   -> (
   ->    OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ->    ModelID SMALLINT UNSIGNED NOT NULL,
   ->    ModelDescrip VARCHAR(40)
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> Describe Orders; +--------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+-------+ | OrderID | smallint(5) unsigned | | PRI | 0 | | | ModelID | smallint(5) unsigned | | | 0 | | | ModelDescrip | varchar(40) | YES | | NULL | | +--------------+----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

  • /

Drop table Orders; CREATE TABLE Orders ( 

  OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
  ModelID SMALLINT UNSIGNED NOT NULL,
  ModelDescrip VARCHAR(40)

); Describe Orders; 

      </source>


Create table: primary key 2

   <source lang="sql">

Drop table Orders; CREATE TABLE Orders ( 

  ID SMALLINT UNSIGNED NOT NULL,
  ModelID SMALLINT UNSIGNED NOT NULL,
  Desciption VARCHAR(40),
  PRIMARY KEY (ID)

); Describe Orders; 

      </source>


Create table: REFERENCES

   <source lang="sql">

/* mysql> Drop table Orders; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Orders 

   -> (
   ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ->    ModelID SMALLINT UNSIGNED NOT NULL REFERENCES Models (ModelID),
   ->    ModelDescrip VARCHAR(40)
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> Describe Orders; +--------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | | PRI | 0 | | | ModelID | smallint(5) unsigned | | | 0 | | | ModelDescrip | varchar(40) | YES | | NULL | | +--------------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

  • /

Drop table Orders; CREATE TABLE Orders ( 

  ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
  ModelID SMALLINT UNSIGNED NOT NULL REFERENCES Models (ModelID),
  ModelDescrip VARCHAR(40)

); Describe Orders; 

      </source>


Create table: small int and unsigned int

   <source lang="sql">

drop table Inventory; CREATE TABLE Inventory ( 

  ProductID SMALLINT(4) UNSIGNED ZEROFILL,
  Quantity INT UNSIGNED

); describe Inventory; 

      </source>


Create table: smallint, decimal and float

   <source lang="sql">

/* mysql> Drop table Product; mysql> CREATE TABLE Product 

   -> (
   ->    ID SMALLINT,
   ->    Price DECIMAL(7,2),
   ->    Weight FLOAT(8,4)
   -> );

Query OK, 0 rows affected (0.09 sec) mysql> Describe Product; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | ID | smallint(6) | YES | | NULL | | | Price | decimal(7,2) | YES | | NULL | | | Weight | float(8,4) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 3 rows in set (0.03 sec)

  • /

Drop table Product; CREATE TABLE Product ( 

  ID SMALLINT,
  Price DECIMAL(7,2),
  Weight FLOAT(8,4)

); Describe Product; 

      </source>


Create table: two columns for primary key

   <source lang="sql">

/* mysql> Drop table Orders; mysql> CREATE TABLE Orders 

   -> (
   ->    ID SMALLINT UNSIGNED NOT NULL,
   ->    ModelID SMALLINT UNSIGNED NOT NULL,
   ->    Descrip VARCHAR(40),
   ->    PRIMARY KEY (ID, ModelID)
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> Describe Orders; +---------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | | PRI | 0 | | | ModelID | smallint(5) unsigned | | PRI | 0 | | | Descrip | varchar(40) | YES | | NULL | | +---------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)

  • /

Drop table Orders;

CREATE TABLE Orders ( 

  ID SMALLINT UNSIGNED NOT NULL,
  ModelID SMALLINT UNSIGNED NOT NULL,
  Descrip VARCHAR(40),
  PRIMARY KEY (ID, ModelID)

); Describe Orders; 

      </source>


Create table: YEAR and TIMESTAMP

   <source lang="sql">

Drop table Orders; CREATE TABLE Orders ( 

  ID SMALLINT UNSIGNED,
  BookID SMALLINT UNSIGNED,
  Copyright YEAR,
  OrderDate TIMESTAMP

); Describe Orders; 

      </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Table_Index/Create_Table&oldid=5491»