в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:28Z

1 версия

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

==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>

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

SQL/MySQL/Table Index/Create Table - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия