SQL学习笔记（3）之索引、唯一及主键约束

 

//建立普通索引；

CREATE INDEX INDEX_NAME ON TB_NAME(COLUMN);

ALTER TABLE TB_NAME ADD INDEX INDEX_NAME(COLUMN);

 

//建立多重索引，是“且”的关系

CREATE INDEX INDEX_NAME ON TB_NAME(COLUMN1，COLUMN2);

ALTER TABLE TB_NAME ADD INDEX INDEX_NAME(COLUMN1，COLUMN2);

 

//查看索引；

SHOW INDEX FROM TB_NAME; 

 

//查看索引效果

EXPLAIN SELECT *FROM TB_NAME WHERE COLUMN1=..;

 

//删除普通索引

ALTER TABLE TB_NAME DROP INDEX INDEX_NAME;

 

//创建唯一约束（索引）

ALTER TABLE TB_NAME ADD CONSTRAINT IN_UQ_NAME UNIQUE (COLUMN);

//有“IN_UQ_NAME”，唯一约束（索引）名字为“IN_UQ_NAME”，否则为“COLUMN”

 

ALTER TABLE TB_NAME ADD CONSTRAINT IN_UQ_NAME UNIQUE KEY(COLUMN1，COLUMN2)

//有“IN_UQ_NAME”，唯一约束（索引）名字为“IN_UQ_NAME”,否则取名“COLUMN1”（即第一个参数）；

//UNIQUE 后加或不加key都可以；

 

//创建主键约束（索引）

 

ALTER TABLE TB_NAME ADD CONSTRAINT IN_PR_NAME PRIMARY KEY(COLUMN);

ALTER TABLE TB_NAME ADD CONSTRAINT PRIMARY KEY(COLUMN1，COLUMN2);

//IN_PR_NAME不起作用，索引名为PRIMARY；  PRIMARY后必须有key；

 

删除唯一约束（索引）同删除普通索引；

//删除主键约束（索引）

ALTER TABLE T1 DROP INDEX PRIMARY;//错误
ALTER TABLE T1 DROP PRIMARY KEY;//正确！

//建表时即指定约束

例1：

CREATE TABLE STUDENT(
ID INT UNIQUE,
NAME VARCHAR(10) PRIMARY KEY//没有默认值！
);

==

CREATE TABLE `student` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(10) NOT NULL,
  PRIMARY KEY (`NAME`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

例2：

CREATE TABLE TEACHER(
ID INT ,                         
NAME VARCHAR(10) ,
CONSTRAINT ID_index UNIQUE KEY(ID),
CONSTRAINT PRIMARY KEY(NAME)
);

==

CREATE TABLE `teacher` (
  `ID` int(11) DEFAULT NULL,//默认空！但是两个null的数据，不认为数据相同，ID=null（报错），（ID is null）
  `NAME` varchar(10) NOT NULL DEFAULT ' ',//此时NAME不仅非空且有‘ ’的默认值；
  PRIMARY KEY (`NAME`),
  UNIQUE KEY `ID_index` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

例3：

CREATE TABLE PROFESSOR(
ID INT ,                         
NAME VARCHAR(10) ,
CONSTRAINT PRIMARY KEY(ID,NAME)//此时ID、NAME均有默认值，且ID和NAME为“且”的关系。
);

==

CREATE TABLE `professor` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `NAME` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`,`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
//插入数据：

INSERT INTO PROFESSOR(NAME)
VALUES('L'),('W');//是对的！此时数据为（0，L),(0,W)

INSERT INTO PROFESSOR(ID)
VALUES(1),(2);//是对的！此时数据为（1，''),(2,'')

INSERT INTO PROFESSOR(ID,NAME)
VALUES(0,'');//为错的，数据重复！

 

注意点：（1）两个NULL型数据，不认为相同，DELETE FROM STUDENT WHERE ID IS NULL;(能把数据数据删除），DELETE FROM STUDENT WHERE ID=NULL；（无效）

               （2）PRIMARY KEY=NOT NULL + UNIQUE KEY；且一个表中只能有一个PRIMARY KEY；

               （3）PRIMARY KEY的索引名为PRIMARY！删除主键时，名字为PRIMARY KEY，直接DROP

               （4）以“CONSTRAINT UNIQUE/PRIMARY KEY...”方式建立时，唯一约束默认NULL，主键默认值与字段type有关，