MySQL Tutorial/Table/Create Table — различия между версиями

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

Текущая версия на 12:49, 26 мая 2010

Copy a table with Create ... Select statement

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE Employee( 

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> CREATE TABLE myTable TYPE=MYISAM AS 

   -> SELECT *
   ->   FROM employee
   ->  WHERE start_date BETWEEN "2000-01-01" AND "2000-12-31";

Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | first_name | varchar(15) | YES | | NULL | | | last_name | varchar(15) | YES | | NULL | | | start_date | date | YES | | NULL | | | end_date | date | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | | city | varchar(10) | YES | | NULL | | | description | varchar(15) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 8 rows in set (0.02 sec) mysql> mysql> select * from myTable; Empty set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec)</source>


Create a table by UNION another table

   <source lang="sql">

mysql> mysql> CREATE TABLE Employee( 

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.02 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> CREATE TABLE myTable 

   ->    (    id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> )
   ->  TYPE=MERGE
   ->  UNION=(employee)
   ->  INSERT_METHOD=LAST ;

Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> mysql> mysql> select * from myTable; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>


CREATE TABLE table_name (column_name field_type [NULL|NOT NULL],KEY col_index(column_name));

   <source lang="sql">

CREATE TABLE table_name ( 

  column_name [NULL|NOT NULL][DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference definition]...);</source>

Creating a Table with an Index

   <source lang="sql">

mysql> mysql> CREATE TABLE myTable 

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

Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> desc myTable; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | | | | | Name | varchar(40) | NO | MUL | | | +-------+----------------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.01 sec)</source>


Creating Tables with AUTO_INCREMENT and NOT NULL column

The attribute AUTO_INCREMENT for the column id has the effect that with each new record the appropriate value for id is automatically inserted.

The attribute NOT NULL ensures that actual values must be placed in both columns. It is not permitted to store the data record NULL or not to insert any value at all. Thus this attribute prevents invalid data records from being stored.

PRIMARY KEY (id) has the effect that the column id is used to identify the data records.



   <source lang="sql">

mysql> mysql> CREATE TABLE myTable ( 

   ->    id      INT     NOT NULL AUTO_INCREMENT,
   ->    choice  TINYINT NOT NULL,
   ->    ts      TIMESTAMP,
   ->    PRIMARY KEY (id));

Query OK, 0 rows affected (0.05 sec) mysql> mysql> DROP TABLE myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>


Creating table with Foreign key

   <source lang="sql">

mysql> mysql> CREATE TABLE Models 

   -> (
   ->    ModelID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   ->    Name VARCHAR(40) NOT NULL,
   ->    PRIMARY KEY (ModelID)
   -> );

Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> CREATE TABLE Orders 

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

Query OK, 0 rows affected (0.03 sec) mysql> mysql> desc Models; +---------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+----------------+ | ModelID | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(40) | NO | | | | +---------+----------------------+------+-----+---------+----------------+ 2 rows in set (0.02 sec) mysql> mysql> desc Orders; +-------------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+-------+ | ID | smallint(5) unsigned | NO | PRI | | | | ModelID | smallint(5) unsigned | NO | | | | | Description | varchar(40) | YES | | NULL | | +-------------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> mysql> drop table Orders; Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop table Models; Query OK, 0 rows affected (0.00 sec)</source>


"IF NOT EXISTS" parameter can be used to check if a table exists before you actually create it

CREATE TABLE IF NOT EXISTS tablename (columnname data type);



   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE IF NOT EXISTS employee (id int); Query OK, 0 rows affected (0.03 sec) mysql> mysql> desc employee; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> mysql> drop table employee; Query OK, 0 rows affected (0.02 sec) mysql> mysql></source>


The CHARACTER SET attribute specifies the character set, and the COLLATE attribute specifies a collation for the character set

The ASCII attribute is shorthand for CHARACTER SET latin1.

The UNICODE attribute is shorthand for CHARACTER SET ucs2.

The BINARY attribute is shorthand for specifying the binary collation of the column character set.

The CHAR BYTE data type is an alias for the BINARY data type. This is a compatibility feature.



   <source lang="sql">

mysql> mysql> CREATE TABLE myTable 

   -> (
   ->     c1 VARCHAR(20) CHARACTER SET utf8,
   ->     c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>


The syntax for creating a table

The syntax for creating a table is as follows:



   <source lang="sql">

CREATE TABLE <table_name> ( 

      field1 datatype,
      field2 datatype,
      etc......

);</source>


TYPE=MYISAM

   <source lang="sql">

mysql> mysql> mysql> CREATE TABLE Employee( 

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description) 

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> CREATE TABLE myTable TYPE=MYISAM AS 

   -> SELECT *
   ->   FROM employee
   ->  WHERE start_date BETWEEN "2000-01-01" AND "2000-12-31";

Query OK, 0 rows affected, 1 warning (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc myTable; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | first_name | varchar(15) | YES | | NULL | | | last_name | varchar(15) | YES | | NULL | | | start_date | date | YES | | NULL | | | end_date | date | YES | | NULL | | | salary | float(8,2) | YES | | NULL | | | city | varchar(10) | YES | | NULL | | | description | varchar(15) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> mysql> select * from myTable; Empty set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>


Use a CREATE TABLE statement to specify the layout of your table:

   <source lang="sql">

mysql> mysql> CREATE TABLE myTable ( 

   -> id           int,
   -> first_name   VARCHAR(20),
   -> last_name    VARCHAR(20),
   -> sex          CHAR(1),
   -> start_date   DATE,
   -> end_date     DATE);

Query OK, 0 rows affected (0.05 sec) mysql> mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | mytable | +----------------+ 1 row in set (0.00 sec) mysql> mysql> describe myTable; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | start_date | date | YES | | NULL | | | end_date | date | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> mysql> drop table myTable; Query OK, 0 rows affected (0.00 sec) mysql></source>

Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Table/Create_Table&oldid=66»