表创建(上)

表创建(上)

作者:LAMP小白  点击:1833  发布日期:2012-10-23 00:43:00  返回列表
有点晚了 暂时弄一半 - -

查看可用引擎:
mysql> SHOW ENGINESG
*************************** 1. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant tables
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.01 sec)

Support:表示该存储引擎是否可用
DEFAULT:表示这是数据库默认引擎
Transaction:是否支持事务
XA和Savepoints:表明某种存储引擎是否支持分布式事务处理

从某种意义上讲,任何一个MYSQL服务器所管理的任何数据表都可以移动到另一台服务器上去,一是通过表备份还原,二是二进制可移植性

(binary portablity)看上去很高端,其实就是把数据库的相应文件直接复制到另一个正确的地方 - -

创建:
CREATE TABLE table_name
(
...
)
ENGINE = XXX
...
;

如果在一条CREATE TABLE语句中给出一个服务器能够但此时此刻并不适用的引擎,那么MYSQL会适用默认引擎去创建表,并生成一条WARNING.

同样,如果想避免表存在造成报错的情况可以:
CREATE TABLE IF NOT EXISTS

查看表的属性

mysql> SHOW CREATE TABLE studentG
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` varchar(20) NOT NULL,
`sex` enum('F','M') NOT NULL,
`student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

临时数据表

如果在创建语句上加上TEMPORARY那么将创建一张临时表,他会在断开连接的时候自动删除

从其他数据表或结果中创建数据表

CREATE TABLE ... LIKE
CREATE TABLE ... SELECT

建立一张永久表的临时表,并用永久表的数据区填充他

mysql> CREATE TEMPORARY TABLE new_student LIKE student;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO new_student SELECT * FROM student;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

也可以使用更简单的方法

mysql> CREATE TEMPORARY TABLE new_student2 SELECT * FROM student;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> DESC new_student2;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| sex | enum('F','M') | NO | | NULL | |
| student_id | int(10) unsigned | NO | | 0 | |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

使用一个结果来创建表

mysql> CREATE TEMPORARY TABLE haha SELECT PI()*2 AS result;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM haha;
+----------+
| result |
+----------+
| 6.283185 |
+----------+
1 row in set (0.00 sec)

CREATE TABLE ... SELECT 的缺点

不能将原始表的全部属性都复制过去,如索引,他只能复制NULL,NOT NULL,字符集和排序方式,默认值和注释


上一篇:风格超赞 初音未来: いのちの歌 下一篇:快递查询API
0