mysql-表的操作

mysql支持的存储引擎

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎

1、InnoDB 存储引擎

支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其特点是行锁设计、支持外键,并支持类似 oracle 的非锁定读,即默认读取操作不会产生锁。 从 Mysql 5.5.8 版本开始是默认的存储引擎

2、MyISAM 存储引擎

不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 mysql 5.5.8 版本之前是默认的存储引擎(除 windows 版本外)

3、NDB 存储引擎

NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型

4、Memory 存储引擎

Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失

5、Infobright 存储引擎

第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用

6、NTSE 存储引擎

网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持

7、BLACKHOLE

黑洞存储引擎，可以应用于主备复制中的分发主库

Mysql 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。

指定表类型/存储引擎

mysql> create database db1 charset utf8;   # 创建db1数据库设置字符编码为utf8

Query OK, 1 row affected (0.00 sec)

mysql> use db1;  # 选择db1数据库

Database changed

mysql> create table t1(id int)engine=innodb;  # 创建t1表，设置字段id为int类型，并指定引擎为innodb

Query OK, 0 rows affected (0.11 sec)

练习:

创建四张表，分别使用innodb,myisam,memory,blackhole存储引擎

mysql> create table t2(id int)engine=innodb;  

Query OK, 0 rows affected (0.09 sec)

mysql> create table t3(id int)engine=myisam;

Query OK, 0 rows affected (0.01 sec)

mysql> create table t4(id int)engine=memory;

Query OK, 0 rows affected (0.01 sec)

mysql> create table t5(id int)engine=blackhole;

Query OK, 0 rows affected (0.00 sec)

查看db1数据库中的文件:

[root@Centos6 db1]# tree -N /data/3306/data/db1

/data/3306/data/db1

├── db.opt

├── t1.frm

├── t1.ibd

├── t2.frm

├── t2.ibd

├── t3.frm

├── t3.MYD

├── t3.MYI

├── t4.frm

└── t5.frm

#.frm是存储数据表的框架结构

# .ibd是mysql数据文件 

#.MYD是MyISAM表的数据文件的扩展名

#.MYI是MyISAM表的索引的扩展名

# 发现后两种存储引擎只有表结构，无数据

# memory，在重启mysql或者重启机器后，表内数据清空

# blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录

创建表

语法:

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

1 创建数据库

mysql> create database db1 charset utf8;

Query OK, 1 row affected (0.00 sec)

2 选择数据库

mysql> use db1;

Database changed

3 创建a1表

mysql> create table a1(id int, name varchar(20), age int(3));

Query OK, 0 rows affected (0.39 sec)

4 插入表数据(记录)

mysql> insert into a1 values (1,'张三',18),(2,'李四',19),(3,'王五',20);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

5 查询表的数据和结构

(1) 查询a1表中的存储数据

mysql> select * from a1;

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

| id   | name   | age  |

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

|    1 | 张三   |   18 |

|    2 | 李四   |   19 |

|    3 | 王五   |   20 |

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

3 rows in set (0.00 sec)

(2) 查看a1表的结构

mysql> desc a1;

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

| Field | Type        | Null | Key | Default | Extra |

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

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| age   | int(3)      | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

(3) 查看表的详细结构

mysql> show create table a1\G

*************************** 1. row ***************************

       Table: a1

Create Table: CREATE TABLE `a1` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(20) DEFAULT NULL,

  `age` int(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

6 复制表

(1) 新创建一个数据库db2

mysql> create database db2;

Query OK, 1 row affected (0.00 sec)

(2) 选择db2数据库

mysql> use db2;

Database changed

(3) 复制db1.a1的表结构和数据(记录)

mysql> create table b1 select * from db1.a1;

Query OK, 3 rows affected (0.10 sec)

Records: 3  Duplicates: 0  Warnings: 0

(4) 查看db2.b1表中的数据和表结构

mysql> select * from b1;

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

| id   | name   | age  |

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

|    1 | 张三   |   18 |

|    2 | 李四   |   19 |

|    3 | 王五   |   20 |

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

3 rows in set (0.00 sec)

如果只复制表结构，不要数据

在db2数据库下新创建一个b2表，给一个where条件，条件要求不成立，条件为false，只拷贝表结构，1>5为False

mysql> create table b2 select * from db1.a1 where 1>5;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

查看表结构:

mysql> desc b2;

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

| Field | Type        | Null | Key | Default | Extra |

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

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| age   | int(3)      | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

查看表结构中的数据，发现没有数据,只复制表结构成功

mysql> select * from b2;

Empty set (0.00 sec)

还有一种写法，使用like(只拷贝表结构，不拷贝数据(记录))

mysql> create table b3 like db1.a1;  # 拷贝db1中的a1表结构

Query OK, 0 rows affected (0.09 sec)

mysql> desc b3;   # 查看b3表结构

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

| Field | Type        | Null | Key | Default | Extra |

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

| id    | int(11)     | YES  |     | NULL    |       |

| name  | varchar(20) | YES  |     | NULL    |       |

| age   | int(3)      | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> select * from b3;  # 查询b3表数据

Empty set (0.01 sec)

7 删除表

mysql> drop table b3,b2;  # 删除b3,b2表

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;  # 查看所有表

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

| Tables_in_db2 |

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

| b1            |

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

1 row in set (0.00 sec)

PS:以;作为mysql的结束语(分号)