MySQL数据库之表的操作
表的操作
数据表的文件
-
拓扑结构
- 一个数据库对应一个文件夹
- 一个表对应一个或多个文件
-
引擎
myisam一个表对应三个文件.frm存储的是表结构.myd存储的是表数据.myi存储的表数据的索引
innodb一个表对应一个表结构文件- innodb的都有表的数据都保存在ibdata1文件中
- 如果数据量很大，会自动的创建ibdata2，ibdata3...
- 如果不指定引擎，默认是innodb
innodb和myisam的区别myisam- 查询速度快
- 容易产生碎片
- 不能约束数据
innodb- 以前没有myisam查询速度快，现在已经提速了
- 不产生碎片
- 可以约束数据
表前环境创建
-- 创建数据库
mysql> create database data;
# `Query OK, 1 row affected (0.00 sec)`
-- 使用数据库
mysql> use data;
# `Database changed`
-- 设置客户端和服务器通讯的编码
mysql> set names gbk;
# `Query OK, 0 rows affected (0.00 sec)`
创建表
- 语法
create table [if not exists] `表名`(
`字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment],
`字段名` 数据类型 [null|not null] [default] [auto_increment] [primary key] [comment]
)[engine=存储引擎] [charset=字符编码]
- 语法说明
null|not null是否为空default默认值auto_increment自动增长,默认从1开始，每次递增1primary key主键，主键的值不能重复,不能为空,每个表必须只能有一个主键comment备注engine引擎决定了数据的存储和查找myisam、innodb- 表名和字段名如果用了关键字，要用反引号引起来
- 如果不指定引擎，默认是innodb
- 如果不指定字符编码，默认和数据库编码一致
varchar(20)表示长度是20个字符
-- 创建简单的表
mysql> create table stu1(
-> id int auto_increment primary key,
-> name varchar(20) not null
-> )engine=innodb charset=gbk;
# `Query OK, 0 rows affected (0.11 sec)`
-- 创建复杂的表
mysql> create table stu2(
-> id int auto_increment primary key comment '主键',
-> name varchar(20) not null comment '姓名',
-> `add` varchar(50) not null default '地址不详' comment '地址',
-> score int comment '成绩,可以为空'
-> )engine=myisam;
# `Query OK, 0 rows affected (0.06 sec)`
显示所有表
- 语法
show tables;
显示创建表的语句
- 语法
show create table 表名称;结果横着排列show create table 表名称\G;结果竖着排列
查看表结构
- 语法
desc[ribe] 表名;
复制表
- 语法
create table 新表 select 字段 from 旧表;- 不能复制父表的键，能够复制父表的数据
create table 新表 like 旧表;- 只能复制表结构，不能复制表数据
*表示所有字段
删除表
- 语法
drop table [if exists] 表1，表2,… ;
修改表
- 语法
alter table 表名;
添加字段
- 语法
alter table 表名 add [column] 字段名 数据类型 [位置];
-- 默认添加字段放在最后
mysql> alter table stu add `add` varchar(20);
# `Query OK, 0 rows affected (0.05 sec)`
-- 在name之后添加sex字段
mysql> alter table stu add sex char(1) after name;
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
-- age放在最前面
mysql> alter table stu add age int first;
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
删除字段
- 语法
alter table 表 drop [column] 字段名;
-- 删除age字段
mysql> alter table stu drop age;
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
修改字段(改名)
- 语法
alter table 表 change [column] 原字段名 新字段名 数据类型;
-- 将name字段更改为stuname varchar(10)
mysql> alter table stu change name stuname varchar(10);
# `Query OK, 0 rows affected (0.02 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
修改字段(不改名)
- 语法
alter table 表 modify 字段名 字段属性 字段属性;
-- 将sex数据类型更改为varchar(20)
mysql> alter table stu modify sex varchar(20);
# `Query OK, 0 rows affected (0.00 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
-- 将add字段更改为varchar(20) 默认值是‘地址不详’
mysql> alter table stu modify `add` varchar(20) default '地址不详';
# Query OK, 0 rows affected (0.00 sec)
# Records: 0 Duplicates: 0 Warnings: 0
修改引擎
- 语法
alter table 表名 engine=引擎名;
mysql> alter table stu engine=myisam;
# `Query OK, 0 rows affected (0.01 sec)`
# `Records: 0 Duplicates: 0 Warnings: 0`
修改表名
- 语法
alter table 表名 rename to 新表名;
-- 将stu表名改成student
mysql> alter table stu rename to student;
# Query OK, 0 rows affected (0.00 sec)
将表移动到其他数据库
- 语法
alter table 表名 rename to 其他数据库.新表名;
-- 将当前数据库中的student表移动到php74数据库中改名为stu
mysql> alter table student rename to php74.stu;
# Query OK, 0 rows affected (0.00 sec)
清空数据表
delete 方式清空数据表
-
语法
delete * from table_name;
-
说明
- delete是逐条删除 (速度较慢)
- delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录
- 如果只需删除表中的部分记录，只能使用 DELETE语句配合 where条件
truncate 方式清空数据表
-
语法
truncate table table_name;
-
说明
- truncate 是整体删除 (速度较快)
- truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因
- truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)
- 相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的 ID数
