MariaDB 表(CREATE,DROP,RENAME,ALTER)

MariaDB 表(CREATE,DROP,RENAME,ALTER)

InnoDB is a transaction-safe engine (see Chapter 26, “Managing Transaction Processing”). It does not support full-text searching.
MEMORY is functionally equivalent to MyISAM, but as data is stored in memory (instead of on disk) it is extremely fast (and ideally suited for temporary tables).
MyISAM is a high-performance engine. It supports full-text searching (see Chapter 18, “Full-Text Searching”), but does not support transac- tional processing.
ARIA (specified as ENGINE=Aria) is a new transaction-safe engine that also supports full-text searching and vital crash recovery features.

Engine types may be mixed, so within a single database you can have different tables using different engines if required.

CREATE表

CREATE OR REPLACE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL,

note_date datetime NOT NULL,

note_text text NULL ,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=Aria;

CREATE TABLE orderitems

(

order_num int NOT NULL ,

order_item int NOT NULL ,

prod_id char(10) NOT NULL ,

quantity int NOT NULL ,

item_price decimal(8,2) NOT NULL ,

PRIMARY KEY (order_num, order_item)

) ENGINE=Aria;

注意：每张表只有一个字段具有AUTO_INCREMENT属性,主键是能唯一区分表中所有行的属性，如果一个属性不足以区分，可以设置多个字段为主键

ALTER表

(jlive)[crashcourse]>DESC vendors;

+--------------+----------+------+-----+---------+----------------+

+--------------+----------+------+-----+---------+----------------+

| vend_id | int(11) | NO | PRI | NULL | auto_increment |

+--------------+----------+------+-----+---------+----------------+

7 rows in set (0.01 sec)

添加字段(AFTER)

(jlive)[crashcourse]>ALTER TABLE vendors ADD vend_phone CHAR(20) AFTER vend_address;

Query OK, 6 rows affected (0.01 sec)

Records: 6 Duplicates: 0 Warnings: 0

(jlive)[crashcourse]>DESC vendors;

+--------------+----------+------+-----+---------+----------------+

+--------------+----------+------+-----+---------+----------------+

| vend_id | int(11) | NO | PRI | NULL | auto_increment |

+--------------+----------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

删除字段

(jlive)[crashcourse]>ALTER TABLE vendors DROP COLUMN vend_phone;

Query OK, 6 rows affected (0.02 sec)

Records: 6 Duplicates: 0 Warnings: 0

添加字段(FIRST)

(jlive)[crashcourse]>ALTER TABLE vendors ADD vend_phone CHAR(20) FIRST;

Query OK, 6 rows affected (0.01 sec)

Records: 6 Duplicates: 0 Warnings: 0

(jlive)[crashcourse]>DESC vendors;

+--------------+----------+------+-----+---------+----------------+

+--------------+----------+------+-----+---------+----------------+

| vend_id | int(11) | NO | PRI | NULL | auto_increment |

+--------------+----------+------+-----+---------+----------------+

8 rows in set (0.01 sec)

修改字段属性

(jlive)[crashcourse]>ALTER TABLE vendors MODIFY vend_phone char(250) NOT NULL;

Query OK, 6 rows affected, 6 warnings (0.02 sec)

Records: 6 Duplicates: 0 Warnings: 6

(jlive)[crashcourse]>DESC vendors;

+--------------+-----------+------+-----+---------+----------------+

+--------------+-----------+------+-----+---------+----------------+

| vend_id | int(11) | NO | PRI | NULL | auto_increment |

+--------------+-----------+------+-----+---------+----------------+

定义forign keys

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

DROP表

(jlive)[crashcourse]>SHOW TABLES;

+-----------------------+

| Tables_in_crashcourse |

+-----------------------+

| customers |

| hello |

| hello_2 |

| orderitems |

| orders |

| productnotes |

| products |

| vendors |

+-----------------------+

8 rows in set (0.00 sec)

(jlive)[crashcourse]>DROP TABLE IF EXISTS hello,hello_2;

Query OK, 0 rows affected (0.01 sec)

(jlive)[crashcourse]>SHOW TABLES;

+-----------------------+

| Tables_in_crashcourse |

+-----------------------+

| customers |

| orderitems |

| orders |

| productnotes |

| products |

| vendors |

+-----------------------+

6 rows in set (0.00 sec)

RENAME表

(jlive)[crashcourse]>SHOW TABLES LIKE 'hello%';

+--------------------------------+

| Tables_in_crashcourse (hello%) |

+--------------------------------+

| hello_1 |

| hello_2 |

| hello_3 |

+--------------------------------+

3 rows in set (0.00 sec)

(jlive)[crashcourse]>RENAME TABLE hello_1 TO test_1, hello_2 TO test_2, hello_3 TO test_3;

Query OK, 0 rows affected (0.01 sec)

(jlive)[crashcourse]>SHOW TABLES LIKE 'test%';

+-------------------------------+

| Tables_in_crashcourse (test%) |

+-------------------------------+

| test_1 |

| test_2 |

| test_3 |

+-------------------------------+

3 rows in set (0.00 sec)