5-mysql数据表定义语句

数据表定义语句

创建表

基本形式：

create  table  [if not exists] 表名(字段列表， [约束或索引列表]) [表选项列表];

说明：列表都是表示“多个”，相互之间用逗号分开。

字段列表

字段列表：多个字段的定义项，相互之间用逗号隔开；

字段定义形式：字段名 字段类型 [字段属性列表];

字段属性设置：

字段属性列表由多个字段属性构成，相互之间用空格隔开，可以包括如下一些：

• primary key：
  • 设定为主键。
  • 主键的本质是表示“该表中该字段值可以唯一确定某行数据”。
  • 具有2个隐含含义：唯一性，不为空。是唯一键“加强”：也不能重复并且不能使用null。
  • 并且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;
  • 通常，每个表都应该有个主键，而且大多数表，喜欢使用一个id并自增长类型作为主键。但是一个表只能设定一个主键。
• unique [key]：
  • 设定为唯一（键），即表中所有行的数据在该字段中的值不能有重复。
  • 表示该字段的所有行的值不可以重复（唯一性）。
• not null|null：
  • 设定该字段是否可空（可以放置空值null），默认不设置就是表示可为空。
  • 不为空，表示该字段不能放“null”这个值。
• auto_increment：
  • 设定自增长，表示一个int整数类型的字段的值可以“自动递增取值”，即其值无需“写入”，而会自动获得并增加，设置后其必须同时设置为一个主键或唯一键（即[primary] key, unique [key]）。一个表只能有一个auto_increment属性的字段。此属性必须随同 primary key 或 unique key 一起使用。
• default 默认值：
  • 设定默认值，表示当插入新数据的时候如果该字段没有给值，就会自动使用该默认值。
• comment ‘字段注释’：
  • 设置字段的注释文字，用单引号引起来。
    

索引列表

索引列表：多个索引的定义项，相互之间用逗号隔开，可选。

什么是索引：

索引就是数据库内部对某个表的所有数据预先进行的某种排序。

索引是一个“内置表”，该表的数据是对某个真实表的某个（些）字段的数据做了“排序”之后的存储形式。

一个表可以设定（添加）多个索引，则就会有多个排序。

对应用来说，索引只是一个对某个表的“设定”，一两个单词而已，而在数据库内部，数据库管理系统会创建并维护一个跟当前表关联的“索引表”，该表数据已经按某种方式排好了序。

给一个表建立索引的目的是：加速数据的读取，极大提高表查找数据的速度，其效率（速度）可以匹敌二分查找。但同时加重了数据库增删改命令的负担，因而需要权衡考虑是否需要给某个表的某个字段设定索引。

注意：索引在提高查找速度的同时，降低增删改的速度。

对创建（设计）表来说，建立索引是非常简单的事，形式如下：

索引类型 (字段名1，字段名2， .... ）		# 可以使用多个字段建立索引，但通常是一个

有如下几种索引：

• 普通索引：

  • 没有特定其他作用，就只是建立索引。设定形式：index|key [索引名] (字段名1[，字段名2,…])。
  • 只具有索引的基本功能——提速

• 唯一索引：

  • 设定某字段为唯一，且建立索引。
  • 设定形式： unique [key] [索引名] (字段名1[，字段名2,…])。

• 主键索引：

  • 设定某字段为主键，且建立索引。
  • 设定形式：primary key [索引名] (字段名1[，字段名2,…])。
  • 主键索引是唯一索引的特例，也是唯一索引的“加强”，主键索引本身就包含了“唯一性”，同时主键索引的字段不能为空值（null）。
  • 有时候需要用多个字段来确定主键，此时称为“联合主键”。联合主键的意思是多个（２个以上）字段合在一起才算主键设定。比如某个学生管理系统中有个“成绩表”，记录了一个学生整个大学所有所学课程的考试成绩。则该表中至少要记录３项信息：学生ＩＤ，课程名称，成绩。且此时要查到一个成绩，必须确定学生ＩＤ和课程名，才可能确定该成绩。不能说该学生成绩是多少，也不能说某门课成绩是多少，而必须是某个学生的某门课成绩是多少。此时，学生ＩＤ和课程名称合起来才能确定一个有意义的数据，这个字段就构成“主键”。如果没有主键，则可能会造成数据的冲突或歧义，比如：工资表，如果没有主键，同名时就无法区分谁的工资是多少。成绩表，如果没有主键，则可能出现某个人的某门课有多个成绩数据。
    

• 全文索引：

  • 设定某字段可进行全文查找。
  • 设定形式： fulltext [index][索引名] (字段名1[，字段名2,…])。

创建索引示例：

约束列表

约束列表：多个约束的定义项，相互之间用逗号隔开，可选。

什么是约束：

约束就是一种限定数据以符合某种要求的形式（机制）。

约束就是对表中数据的某种限制。当然此限制是人为加上去的，为了使数据更具有“合理性”和“安全性”。

数据的最基本约束是“数据类型”，比如保存工资的字段，就不应该存入字符串值，此时设定其数据类型就解决了此问题。但这里说的约束，是在现有已经设定好数据类型基础上的进一步“要求”，比如，一个学校的学生学号，有一个要求就是“不能重号”，则可以给该字段设定“唯一性”约束它，这就是唯一约束。mysql有如下几项约束：

• 主键约束：

  • 设定形式：[constraint] primary key (字段名1[，字段名2,…])。含义同字段的同名属性。
  • 其实就是主键索引，也是主键属性。即primary key有3个角度的理解（说法）：字段属性设置为主键，或建立的主键索引，或设定一个主键约束，但他们的本质是一样。

• 唯一约束：

  • 设定形式：[constraint] unique [index] (字段名1[，字段名2,…])。含义同字段的同名属性。
  • 其实也是“3体合一”（类似primary key)。

• 外键约束：

  • 设定形式：[constraint] foreign key (字段名1[，字段名2,…]) references 表名(字段名1[，字段名2,…]）。
  • 什么是外键？外键就是关系数据库的一个基本特征的实现，表明该字段的值是跟外部的某个表的某个字段的值“对应”的，或者也可以说来自于该外部的某个表的某个主键字段值。
  • 如果给一个设定了外键的字段插入一个值，而该值并没有在该外键所指定的外部表的对应字段中出现，则该值就会插入失败，这也就是数据库内部的“约束机制”（使数据更“纯净”）。
  • 对某个（些）字段设定外键，则其相对应的其他表的对应字段需要设置为主键。
  • 参考：

• 非空约束：

  • 其实就是字段属性中的“not null”，只能在字段上设置。
  • 要求该字段的值不能为空，其只能在字段上当作字段属性来设定。

• 默认约束：

  • 其实就是字段属性中的“default 默认值”，只能在字段上设置。
  • 要求该字段的值在“空”的时候会自动填充该设定的默认值，只能字段上设定。

• 检查约束：

  • 指使用一定的判断表达式来对某个字段中的值进行“合理性”限制，比如年龄字段，虽可以设定为tinyint，但其实仍然会超出合理范围，则可以设定检查约束，只能输入0-120作为“合法年龄”数据。不过，当前mysql版本尚不支持。
  • 就是使用一个表达式（逻辑判断）来决定数据是否有效，比如年龄字段，可以使用tinyint，则可能会超过127就不合适了。tinynit unsigned，则0-255是可以的。
    但：如果考虑现实情况，假设某保险公司只作150岁以下的人的保险。则就可以继续对该字段可能存储的数据进行“约束”。比如类似：if（age > 150){return false}
    可惜的是：ｍｙｓｑｌ不支持检查约束的语法和功能。

表选项

表选项就是对一个表的有关属性的设定，通常都不需要。如果不设定，则有其默认值。

表选项列表：多个表选项的设定项，相互之间用逗号或空格隔开，可选。常用表选项如下：

• ENGINE=“表的存储引擎名”；

  • 什么是存储引擎？存储引擎也叫“表类型”，是指一个表中的数据以何种方式存放在文件或内存中，就是将数据存入硬盘（或其他媒介）的方式方法。

  • 存储引擎决定一个数据表的各方面的信息：功能和性能。

  • 不同的存储引擎（表类型）提供不同的性能特性和可用功能。没有一种各方面都又具有最佳性能又具有各种功能的存储引擎。要做的是要根据数据的具体使用情形（需求）来选择合适的存储引擎，有的要读取速度快，有的要写入速度快，有的要具有高安全可靠性，有的要海量存储，等等。

  • 常用的存储引擎是innoDB（默认）和Myisam。各种存储引擎性能与功能对比如下：

  

• CHARACTER SET=要使用的编码名 ,或者charset = 字符编码名称， 字符编码设定的范围及继承关系。

• COLLATE=要使用的校对集名 。

• auto_increment = 自增长字段的起始值， 起始整数，自增长类型值的初值，默认是1。

• comment= ‘表的注释说明文字’

• 系统级设定：安装时确定了。

• 库级设定：建库时设定；

• 表级设定：就是这里的charset = 字符编码名称

• 字段级设定：作为字段属性出现。

​ 他们都只对“字符类型”的字段有效。每一级如果没有设定，就会“继承使用”其上一级的设定。
​ 总的示例：

修改表

通常，创建一个表，能做到的事情，修改表也能做到。大体来说，就可以做到：增删改字段。

修改表的基本形式：

alter  table  表名   修改语句1，修改语句2， .....;

• 增：

  alter  table  表名  add  [column]  字段名  字段类型  字段属性;
  

• 删：

   alter  table  表名  drop  字段名;
  

• 改：

  alter  table  表名  change  原字段名  新字段名  新字段类型  新字段属性；
  

删除表

drop   table  [if  exists]  表名;

表的其他操作

显示所有表：

show  tables;

显示表结构：

desc  表名;

显示表的创建语句：

show  create  table  表名;

从已有表复制表结构：

create table [if not exists] 新表名 like 原表名;

从已有表复制表结构（不推荐）：

create table [if not exists] 新表名 select * from 原表名 where 1<>1;