Mysql Day Three

列属性

 NUll  、 NOT NULL 、 default 、 Primary key 、  unique key 、auto_increament  、comment；

1 空属性： not null   规定数据插入时 规定的字段不能为空

2 列描述： comment  没有实际含义，可以描述字段。可以通过 show create table 表名 查看字段描述

                  例：filed  数据类型 comment “解释描述字段”

-- 列描述实例
create table m_comment(
name varchar(10) comment "姓名"
)charset utf8;


show create table  m_comment;
--------------------------------------+
| Table     | Create Table                                                                                                       |
--------------------------------------+
| m_comment | CREATE TABLE `m_comment` (
  `name` varchar(10) DEFAULT NULL COMMENT '姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
--------------------------------------+

3 默认值： default  在插入数据时 ，如果插入的是“” 或者是default ，则使用默认值代替

-- default 实例
create table m_default(
 sex enum("man","women") default "man"
 )charset utf8;


insert into m_default values();
insert into m_default values("women");
insert into m_default values(default);

select * from m_default;
+-------+
| sex   |
+-------+
| man   |
| women |
| man   |
+-------+

4 主键：primary key  一张表中最多只能有一个主键且 主键字段不能为空，主键具有唯一性

 -- 主键实例
 -- 增加主键的s第一种方式
 create table m_primary(
 number int primary key,
 name varchar(10) not null
 )charset utf8;

desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

-- 使用primary key在所有字段后添加主键  
-- 如果有多个字段，组成复合键
 create table m_primary1(
 number int ,
 sex char ,
 primary key(number,sex)
 )charset utf8;

 desc m_primary1 ;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | int(11) | NO   | PRI | NULL    |       |
| sex    | char(1) | NO   | PRI | NULL    |       |
+--------+---------+------+-----+---------+-------+

-- 第三种方式，创建表之后追加主键
create table m_primary2(
nmae varchar(10),
sex enum("men","women") default "women"
)charset utf8;

alter table m_primary2 add primary key(nmae);
desc m_primary2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| nmae  | varchar(10)         | NO   | PRI | NULL    |       |
| sex   | enum('men','women') | YES  |     | women   |       |
+-------+---------------------+------+-----+---------+-------+

-- 删除更新组件
-- 必须先删除主键才能重新设置新的主键
desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

alter table m_primary drop primary key;
desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11)     | NO   |     | NULL    |       |
| name   | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

alter table m_primary modify name varchar(10) primary key;
desc m_primary;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | int(11)     | NO   |     | NULL    |       |
| name   | varchar(10) | NO   | PRI | NULL    |       |
+--------+-------------+------+-----+---------+-------+

5 自增长： auto_increament 当对应字段插入值为空时，系统从当前最大值加作为新值

                  自增长必须是索引， 必须是整型，一张表只能有一个自增长。

create table m_autoin(
id int primary key auto_increment,
name varchar(10) not null
)charset utf8;

desc m_autoin;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

insert into m_autoin values (default,"lili");
insert into m_autoin values (3,"liwa");
insert into m_autoin values (default,"lidfi");

select * from m_autoin;
+----+-------+
| id | name  |
+----+-------+
|  1 | lili  |
|  3 | liwa  |
|  4 | lidfi |
+----+-------+

6 唯一键： unique 唯一键默认允许自动为空

-- 方式一
create table m_unique(
name varchar(10) unique not null,
id int primary key
)charset utf8;
desc m_unique;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   | UNI | NULL    |       |
| id    | int(11)     | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 方式二
create table m_unique1(
name varchar(10)not null ,
id int ,
unique key(name)
)charset utf8;
desc m_unique1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | NO   | PRI | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 方式三
create table m_unique2(
name varchar(10) ,
id int 
)charset utf8;
alter table m_unique2 add unique key(name);
desc m_unique2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  | UNI | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+