SQL/MySQL/Table Index/Create Table — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:17, 26 мая 2010
Содержание
- 1 Create Simple Tables
- 2 Create table: auto increment primary key
- 3 Create table: BLOB
- 4 Create table: char set and language
- 5 Create table: char, varchar
- 6 Create table: default int value
- 7 Create table: default value for column
- 8 Create table: engine type INNODB
- 9 Create table: ENUM and set
- 10 Create table: foreign key
- 11 Create table: not null and default value
- 12 Create table: null and not null
- 13 Create table: primary key
- 14 Create table: primary key 2
- 15 Create table: REFERENCES
- 16 Create table: small int and unsigned int
- 17 Create table: smallint, decimal and float
- 18 Create table: two columns for primary key
- 19 Create table: YEAR and TIMESTAMP
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>