MySQL数据表基本操作

MySQL数据表基本操作

0 引言

  数据表，是MySQL中一种重要的数据库对象，是组成数据库的基本单元，数据以表的形式进行记录和组织。

  数据表，由多个数据字段组成，用来实现数据存储的记录，每一行代表一条记录，每一列代表一个字段。

  表的基本操作包括：创建表、查看表、删除表、修改表。

  表有三个重要概念：

  1）列（columns）：即属性列，必须指定列名（属性名）和数据类型；

  2）索引（indexes）：数据表中具体的属性列（单个或多个）所建立起来的顺序，用以提供快速地对记录行进行访问。

  3）触发器（triggers）：用户定义的事务命令的集合，即当对一个表中数据进行插入、更改、删除时，触发器命令自动执行，用以确保数据的完整性和安全性。

1 创建表

  （1）基本语句（包含：表名、属性、存储引擎、字符集）

    create table table_name(属性名1 数据类型，属性名2 数据类型，

      ......属性名n 数据类型)engine=MyISAM DEFAULT CHARSET=latin1;

  （2）修改表存储引擎和字符集

    alter table table_name engine=InnoDB DEFAULT CHARSET=latin1;

  （3）先检查表是否存在，然后再创建表

    create table  if not  exist table_name(属性名1 数据类型，属性名2 数据类型，...... 属性名n 数据类型)engine=MyISAM;

  （4）创建临时表（temporary）

    create temporary table  table_name(属性名1 数据类型，属性名2 数据类型，...... 属性名n 数据类型);

    即：在创建表语句里加上temporary关键字，即服务器会创建一个临时表，并随服务器的会话终止而自动删除，且此表只直接创建的客户端相关，不受其他客户端的影响。

  （5）根据其他表或查询结构来创建表

    1)创建一个空表，即直接复制其他表结构。

    create table table_name like  tab_name2;

    2）创建表再插入一部分查询的数据

    create  table  table_name  like  tab_name2;

    insert into table_name select  *  from tab_name2;

    3）创建表并直接复制一部分查询的数据

    create  table  table_name  select * from  tab_name2;

    create  table  table_name  select * from  tab_name2  where ......;

　　　　4）多表关联查询结果创建表并插入数据

　　　　略。需注意同名属性列表冲突的问题。

  （6）创建分区表（对表进行区分存储）

　　　　此特性属于高级应用，尤其是大规模数据存储，通过分区可有效提升数据库访问响应性能。但也需要根据应用需求合理规划分区，否则达不到提升性能的作用。

　　　　MySQL支持表分区，可以让表的内容分散存储在不同的物理存储位置。使用表分区有以下好处：

　　　　1）表存储可以分布在多个设备上，这样可以通过I/O并行机制来缩短访问时间。

　　　　2）优化器可以把本地化搜索限定在某个特定的分区，或者并行搜索多个分区。

　　　　创建表分区，即通过"partition by"子句来定义，可以把不同记录行划分到不同分区函数中去。

    分区函数，可以根据范围、值列表或散列值来分配各行。分区依据，一定是按需来规划，不能盲目划分，分区不当，目的难以实现。

    示例：

      create   table  tab_partition (

        dt datetime  not null,

        info varchar(100) not null,

        index(dt))

        partition by range(year(dt))(

        partition p0 values less than (2010),

        partition p1 values less than (2012),

        partition p2 values less than (2014),

        partition p3 values less than (2016),

        partition p4 values less than maxvalue

        );

 

2 查看表

  查看表相关信息，主要包括：表定义与结构信息

  （1）语法1：describe table_name;

  （2）语法2：show create table table_name; 

3 修改表

  修改表，是指对已经创建的表定义和结构上进行修改，即表的修改操作。

  注：修改表，必须评估是否会影响表中所记录的数据，以及表相关数据的使用是否受到影响。

3.1 修改表名

  在MySQL中，同一个数据库中，数据表的名称是唯一的，不能重复，即通过表名可用来区分不同的表。

  修改表名，不影响表中数据。

  修改表名的语法如下：

  alter table old_table_name rename new_table_name;

3.2 增加字段

  表中列视为字段，而字段由字段名和数据类型进行定义的。基本语法规则如下：

  （1）在表的最后一列增加字段

    alter table table_name add new_colname datatype;

  （2）在表的第一个列增加字段

    alter table table_name add new_colname datatype  first;

  （3）在表的指定字段之后增加字段

    alter table table_name add new_colname datatype after colname;

    即：在某个属性之后，添加新的属性列。

3.3 删除字段

  删除表中字段，尤为慎重。删除某个字段，即删除表中已经定义好的字段。

  删除表中字段的语法：alter table table_name drop colname;

3.4 修改字段

  修改表字段，即对表中字段（字段名、数据类型）重写定义。

  （1）修改字段数据类型

    alter table table_name modify old_colname  new_datatype；

    注：修改字段数据类型，可能会影响数据记录。当低精度或低宽度数据向高精度或高宽度数据修改时，数据不受影响，反之则受影响。

  （2）修改字段名

    alter table table_name change old_colname new_colname old_datatype;

  （3）同时修改字段名和数据类型

    alter table table_name change old_colname new_colname new_datatype;

  （4）修改字段的顺序

    alter table table_name modify old_colname1 old_datatype  first|after old_colname2；

4 删除表

  删除表较为容易，但需非常慎重，尤其是当表中存在数据的时候。慎重！慎重！慎重！

  drop table tab_name;

5 表的约束操作

  所谓表的约束操作，是指对表中所要存储的数据进行的约束性检查或准入，即完整性检查通过约束完成。

5.1 MySQL支持的完整性约束

  所谓完整性是指数据的准确性和一致性，而完整性检查就是指检查数据的准确性和一致性。

  MySQL提供了一致机制来检查数据库中数据是否满足规定的条件，以保证数据库表中数据的准确性和一致性，这种机制就是约束。

  根据约束数据列限制，约束可以分为：单列约束，即每个约束只约束一列数据；多列约束，即每个约束可以约束多列数据。

  表5-1 MySQL完整性约束清单

序号	关键字	说明
1	Not Null （NK）	非空约束，约束字段的值不能为空
2	Default	默认约束，设置字段的默认值
3	Unique Key（UK）	唯一约束，约束字段的值是唯一
4	Primary Key (PK)	主键约束，约束字段为表的主键，可以作为该表记录的唯一标识
5	Auto_Increment	自增约束，约束字段的值为自动增加
6	Foreign Key（FK）	外键约束，约束字段为表的外键

  

5.2 设置非空约束（Not Null，NK）

  语法：create  table  table_name (colname datatype not null,...);

5.3 设置字段的默认值（Default）

  语法：create  table  table_name (colname datatype default value,...);

5.4 设置唯一约束（Unique，UK）

  语法：create  table  table_name (colname datatype unique,...);

  语法：create  table  table_name (dname varchar(20) unique,...,constraint uk_dname unique(dname));

    这是在字段已有约束和数据的情况下，增加 constraint【约束、强制】，且推荐使用“约束缩写_字段名”为设置的约束标识符，如“uk_dname”。

5.5 设置主键约束（Primary Key，PK）

  设置主键约束，便于数据库快速地进行数据记录查找。

  所设置主键约束时，其字段必须是唯一、非空的；主键，可以是单一字段，也可以是多个字段，即单字段主键和多字段主键。

  语法：create table  table_name (colname1 datatype,...,constraint pk_name primary key(colname1,colname2))

5.6 设置字段值自动增加（Auto_Increment）

  Auto_Increment是MySQL唯一扩展出来的完整性约束，即当为数据表中插入新纪录时，字段上的值自动生成唯一的ID。

  设置为Auto_increment 约束后的字段会生成唯一的ID，故该字段也经常会设置成PK主键。

  语法：create  table table_name (colname datatype auto_increment,...)

  语法：create  table table_name (colname int primary key auto_increment,...)

5.7 设置外键约束（Foreign Key）

  设置外键约束，即某个表的约束由外部表进行参照约束，用于构建多个表（通常为两个表）之间的参照完整性，用于构建两个表之间相关联字段的参照关系。

  设置A表中某一字段，参照于B表中某一字段时，则A表中该字段的取值，必须在B表该字段中的取值是存在的，且唯一的，即是主键字段或者取值唯一的字段，且两表关联字段的数据类型也必须相同。

  语法：

    create table  tab_A(colname datatype,...,constraint fk_colname foreign key (colname) references tab_B(colname2));