MySQL学习笔记-表的创建

一，指定存储引擎

mysql> create table if not exists depart_inno(did int,dname varchar(30)) engine=InnoDB; --支持行所和外键，事物安全。创建新表是的默认存储引擎
Query OK, 0 rows affected (0.05 sec)


mysql> create table if not exists depart_myisam(did int, dname varchar(30)) engine=MyISAM; --二进制可移植的存储引擎，用于只读或多数为查询的环境
Query OK, 0 rows affected (0.01 sec)

[root@vmhostd sx]# ll -t
total 102984
-rw-rw---- 1 mysql mysql        0 Feb  8 02:34 depart_myisam.MYD
-rw-rw---- 1 mysql mysql     1024 Feb  8 02:34 depart_myisam.MYI
-rw-rw---- 1 mysql mysql     8590 Feb  8 02:34 depart_myisam.frm
-rw-rw---- 1 mysql mysql    98304 Feb  8 02:34 depart_inno.ibd
-rw-rw---- 1 mysql mysql     8590 Feb  8 02:34 depart_inno.frm

[root@vmhostd test]# cp ../sx/depart_myisam.* ./ --将myisam表的物理文件复制到另一个数据库中
[root@vmhostd test]# ll
total 20
-rw-r--r-- 1 mysql mysql   65 Nov 21 13:34 db.opt
-rw-r----- 1 root  root  8590 Feb  8 02:43 depart_myisam.frm
-rw-r----- 1 root  root     0 Feb  8 02:43 depart_myisam.MYD
-rw-r----- 1 root  root  1024 Feb  8 02:43 depart_myisam.MYI
[root@vmhostd test]# chown mysql:mysql depart* --修改数据库文件所有者
[root@vmhostd test]# chmod 660 depart* --修改数据库文件权限
[root@vmhostd test]# ll
total 20
-rw-r--r-- 1 mysql mysql   65 Nov 21 13:34 db.opt
-rw-rw---- 1 mysql mysql 8590 Feb  8 02:43 depart_myisam.frm
-rw-rw---- 1 mysql mysql    0 Feb  8 02:43 depart_myisam.MYD
-rw-rw---- 1 mysql mysql 1024 Feb  8 02:43 depart_myisam.MYI

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| depart_myisam  |
+----------------+
1 row in set (0.00 sec)


mysql> select * from depart_myisam;
ERROR 1017 (HY000): Can't find file: './test/depart_myisam.frm' (errno: 13 - Permission denied) --由于没有改变复制的文件的所有者和权限所以报错，数据库是运行在mysql用户下的
mysql> select * from depart_myisam; --修改文件所有者和权限后
Empty set (0.00 sec)

mysql> show engines; --查看支持的数据库引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

二，指定数据文件位置，指定索引文件位置（对于myisam引擎，InnoDB数据和索引在同一个文件中)

mysql> create table cart(iid int primary key, cname varchar(30))engine=MyISAM data directory='/var/pub/db' index directory='/var/pub/db'; 
Query OK, 0 rows affected (0.01 sec)

[root@vmhostd db]# ll
total 4
-rw-rw---- 1 mysql mysql    0 Feb  8 03:13 cart.MYD
-rw-rw---- 1 mysql mysql 1024 Feb  8 03:13 cart.MYI
[root@vmhostd db]# ll /usr/local/mysql/data/test
total 268
-rw-rw---- 1 mysql mysql   8590 Feb  8 03:13 cart.frm
lrwxrwxrwx 1 mysql mysql     20 Feb  8 03:13 cart.MYD -> /var/pub/db/cart.MYD
lrwxrwxrwx 1 mysql mysql     20 Feb  8 03:13 cart.MYI -> /var/pub/db/cart.MYI
-rw-r--r-- 1 mysql mysql     65 Nov 21 13:34 db.opt