SQL语法之列属性和索引

本文主要介绍MySQL语法之列属性。

1.关于列属性

MySQL中，真正约束字段的是数据类型，但是数据类型的约束太单一，需要有一些额外的约束，来更加保证数据的合法性。
MySQL中的常用列属性有：null、not null、default、primary key、auto_increment和comment。

2.列属性

• 空属性
  两个值：NULL(默认的)/NOT NULL(不为空) 虽然默认的，数据库基本都是字段为空，但是实际上在真实开发的时候，尽可能的要保证所有的数据都不应该为空：空数据没有意义;空数据没有办法参与运算

  mysql> create table user(id varchar(40) not null, name varchar(20) not null,age tinyint,address varchar(40) null,gender enum('boy','girl','secret') default 'secret');

• 列描述
  列描述 ：comment描述，没有实际含义：是专门用来描述字段，会根据表创建语句保存：用来给程序猿(数据库管理员)来进行了解的

  mysql> create table user(id varchar(40) not null comment '身份证号', name varchar(20) not null comment '姓名',age tinyint comment '年龄',address varchar(40) null comment '地址',gender enum('boy','girl','secret') default 'secret' comment '性别');
  mysql> show full columns from user;

• 默认值
  默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指d定好，在需要真实数据的时候，用户可以选择性的使用默认值 默认值关键字：default

  mysql> create table user(id varchar(40) not null comment '身份证号', name varchar(20) not null comment '姓名',age tinyint comment '年龄',address varchar(40) null comment '地址',gender enum('boy','girl','secret') default 'secret' comment '性别');

• 主键
  主键：primary key，一张表只能有一个字段可以使用对应的键,用来唯一的约束该字段里面的数据，不能重复

  mysql> create table user(id varchar(40) not null primary key comment '身份证号', name varchar(20) not null comment '姓名',age tinyint comment '年龄');

• 唯一键
  一张表往往有很多字段需要具有唯一性，数据不能重复。

  mysql> create table student(id varchar(30) not null, stu_num int unique,name varchar(20));
  
  mysql> alter table student add unique key(stu_num);

• 自动增长
  自动增长通常是跟主键进行搭配,一般而言,声明为自动增长属性的字段,插入数据的时候系统会自动的在当前最大值的基础上对指定的数据+1。
  自动增长(auto_increment)有几个必要的条件。
  任何一个字段要做自动增长的前提是本身是一个索引(key一栏有值)。
  自动增长必须是数字而且是整型。
  一张表只能有一个自增长,自动增长的起始默认值是1

  mysql> create table my_auto(id int primary key auto_increment comment '自动增长',name varchar(10) not null);
  
  修改自动增长的步长:
  mysql> set auto_increment_increment=5;
  
  删除字段的自动增长的属性:
  mysql> alter table my_auto modify id int;

3.索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。上述SQL语句，在没有索引的情况下，数据库会遍历全部数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？上面的两个图片鲜明的用时对比已经给出了答案（注：一般数据库默认都会为主键生成索引）。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。
索引是一种数据结构，可以是B-tree, R-tree, 或者 hash 结构。其中R-tree 常用于查询比较接近的数据；B-trees适合用于查找某范围内的数据，可以很快的从当前数据找到下条数据；hash结构则适用于随机访问的场合，查找每条数据的时间几乎相同。显然，若要查找某个时间段的数据，用B-tree结构要比hash结构快好多。

• 普通索引
  这是最基本的索引，它没有任何限制。

  mysql> CREATE TABLE mytable(
  ID INT NOT NULL, 	 
  username VARCHAR(16) NOT NULL,
  INDEX (username(16))
  );
  如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。这个length的用处是什么?
  CREATE INDEX part_of_name ON customer (name(10));
  创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。
  
  直接创建索引:
  mysql> CREATE INDEX index_name ON table(column(length))
  
  mysql> CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );
  mysql> create index user_index on mytable(username(16));
  
  查看索引：
  mysql> show index from mytable;
  
  修改表结构的方式添加索引:
  mysql> ALTER TABLE table_name ADD INDEX index_name (column(length))
  mysql> alter table mytable add index user_index  (username(16));
  
  删除索引:
  mysql> DROP INDEX index_name ON table
  
  mysql> drop index user_index on mytable;

• 唯一索引
  与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。

  mysql> CREATE TABLE mytable(  
  ID INT NOT NULL,   	 
  username VARCHAR(16) NOT NULL,   
  INDEX (username(16))  	 
  ); 
  直接创建索引:
  mysql> CREATE INDEX index_name ON table(column(length))
  mysql> create index user_index on mytable(username(16));
  
  修改表结构的方式添加索引:
  mysql> ALTER TABLE table_name ADD UNIQUE indexName (column(length))
  mysql> alter table mytable add UNIQUE user_index  (username(16));
  
  删除索引:
  mysql> DROP INDEX index_name ON table
  mysql> drop index user_index on mytable;

  其他索引都是类似的，本文不涉及。