需要给user表添加多个字段的联合唯一索引,修改

作者: www.9159.com  发布:2019-11-08

项目中需要用到联合唯一索引:

不希望数据表中有重复记录的时候我们可以给表添加一个联合唯一索引
例如,user表中有user_id,user_name两个字段,如果不希望有两条一摸一样的的user_id和user_name,我们可以给user表添加两个字段的联合唯一索引:
alter table user add unique index(user_id,user_name);
这样当向表中添加相同记录的时候,会返回1062的添加失败信息。
但是有一种情况是表中已经有n个重复的记录,这时候我们才想起来要添加唯一索引,再执行上面的操作时,数据库会告诉你已经有重复的记录了,建立索引失败,这时候,我们可以用下面的操作:
alter ignore table user add unique index(user_id,user_name);
它会删除重复的记录(别怕,会保留一条),然后建立唯一索引,高效而且人性化。

索引:

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结

例如:有以下需求:每个人每一天只有可能产生一条记录:处了程序约定之外,数据库本身也可以设定:

 

why we use INDEX?

1. MYSQL数据库

例如:user表中有userID,userName两个字段,如果不希望有2条一模一样的记录,需要给user表添加多个字段的联合唯一索引:

尤其是绿色的那条sql,在建立索引的同时删除了重复数据,相当有用。

1,提高查询效率,查询方法

1) 创建索引

CREATE INDEX index_name ON table_name(column_list)

CREATE UNIQUE INDEX index_name ON table_name(column_list)

修改表的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY(column_list)

alter table user add nuique index(user_id,user_name);

查看索引  show index from 数据库表名

一、普通索引,index

2) 删除索引

DROP INDEX index_name ON talbe_name

修改表的方式删除索引

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

例如:

alter table 数据库add index 索引名称(数据库字段名称)

创建方法:

3) 创建外键

修改表的方式添加外键

ALTER TABLE TABLE_NAME
ADD [CONSTRAINT 外键名]FOREIGN KEY (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)

举例:

ALTER TABLE TABLE_NAME ADD CONSTRAINT FK_NAME(user_id)

REFERENCE sys_user(id)

需要注意两个字段的数据类型一致;

alter table user_daily_money_info add unique index agd(user_account_id,game_id,daily_date);

PRIMARY KEY(主键索引)

1.CREATE INDEX indexName ON tableName(column(length));

4) 删除外键

ALTER TABLE sys_org DROP FOREIGN KEY fk_s_o_id;

 

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.ALTER table ** ADD INDEX [indexName] (column(length))

5) 列操作语法

? 添加列 birthday ,有COLUMN关键字

alter table sys_useradd COLUMN birthday char(19) not null;

? 修改列 birthday ,有MODIFY 关键字

alter table sys_usermodify birthday char(10);

? 删除列 birthday,有 DROP COLUMN关键字

alter table sys_userdrop column birthday;

? 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10) 和不允许为空not null

alter table sys_userchange birthday CSRQ char(10) not null;

alter table user_daily_money_info add unique key agdkey(user_account_id,game_id,daily_date);

UNIQUE(唯一索引)

3.CREATE TABLE mytable(

2. SQL SERVER 数据库

这样如果向表中添加相同记录的时候,会返回一下错误信息:

ALTER TABLE `table_name` ADD UNIQUE (`column`)

ID INT NOT NULL,

1) 创建索引

create UNIQUE INDEX un_index_name on sys_user(user_name);

图片 1

INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

username VARCHAR(16) NOT NULL,

2) 删除索引

DROP INDEX un_index_name ON sys_user

2还有一种情况就是,我们需要为以前的表 创建这个索引,有可能以前的数据中存在重复的记录 那怎么办呢?

FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

INDEX [indexName] (column(length))

3) 创建外键

alter table sys_org add CONSTRAINT fk_s_o_id FOREIGN key(create_user)

REFERENCES sys_user(id);

与mysql一致,需要注意两个字段的数据类型一致;

 

多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

);

4) 删除外键

ALTER TABLE sys_org DROP constraint fk_s_o_id;

与mysql语法不同

alter ignore table user add unique index(user_id,user_name);
它会删除重复的记录(别怕,会保留一条),然后建立唯一索引,高效而且人性化.

1.普通索引。
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
(1)创建索引:CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是 BLOB 和 TEXT 类型,必须指定length,下同。
(2)修改表结构:ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))
(3)创建表的时候直接指定:CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;

删除方法:

5) 列维护语法

6) 添加列 birthday ,有COLUMN关键字

alter table sys_user add birthday char(19) not null;

7) 修改列 birthday属性 ,有alter column 关键字

alter table sys_user altercolumn birthday char(10);

8) 删除列 birthday,有 DROP COLUMN关键字

alter table sys_user dropcolumn birthday ;

9) 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10) 和不允许为空not null;需要分成两步执行;

exec sp_rename 'sys_user.[birthday]','CSRQ','COLUMN';

alter table sys_user altercolumn CSRQ char(10) not null;

 

2.唯一索引。
它与前面的"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
(1)创建索引:CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
(2)修改表结构:ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))
(3)创建表的时候直接指定:CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

DROP INDEX [indexName] ON mytable;

3. ORACLE 数据库

查看索引  show index from 数据库表名
alter table 数据库add index 索引名称(数据库字段名称)
PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`) 

3.主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID)); 当然也可以用ALTER命令。

二、唯一索引(索引列的值必须唯一,但允许有空值)unique

1) 创建索引

CREATE INDEX index_name ON table_name(column_list)

CREATE UNIQUE INDEX index_name ON table_name(column_list)

create INDEX ind_s_u_sex on sys_user(sex);

INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

CREATE UNIQUE INDEX indexName ON mytable(column(length))

2) 删除索引

DROP INDEX [schema.]indexname;

drop index ind_s_u_sex;

FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

三、主键索引(primary key)

3) 创建外键

修改表的方式添加外键

ALTER TABLE TABLE_NAME
ADD [CONSTRAINT 外键名]FOREIGN KEY (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)

举例:

ALTER TABLE TABLE_NAME ADD CONSTRAINT FK_NAME(user_id)

REFERENCE sys_user(id)

需要注意两个字段的数据类型一致;

多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
1.普通索引。
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
(1)创建索引:CREATE INDEX indexName ON  tableName(tableColumns(length));如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是  BLOB 和 TEXT 类型,必须指定length,下同。
(2)修改表结构:ALTER tableName ADD INDEX [indexName] ON (tableColumns(length)) 
(3)创建表的时候直接指定:CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;

CREATE TABLE mytable(

4) 删除外键

ALTER TABLE TABLE_NAME DROP CONSTRAINT FK_NAME;

举例:

ALTER TABLE sys_org drop constraint fk_s_o_id;

2.唯一索引。
它与前面的”普通索引”类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
(1)创建索引:CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
(2)修改表结构:ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))
(3)创建表的时候直接指定:CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

ID INT NOT NULL,

5) 列操作语法

? 添加列 birthday , 有add关键字

alter table sys_useradd birthday char(19) not null;

? 修改列 birthday ,有MODIFY 关键字

alter table sys_usermodify birthday char(10);

? 删除列 birthday,有 DROP COLUMN关键字

ALTER TABLE sys_user DROPCOLUMN birthday;

? 修改列名称及属性;把birthday修改成CSRQ ,并改属性为char(10) 和不允许为空not null;需要分成两步执行;

ALTERTABLE sys_user RENAME COLUMN birthday to CSRQ;

ALTER TABLE sys_user MODIFYCSRQ char(10) ;

mysql、sqlserver、oracle三种数据库维护索引、外键、字段语法总结 1. MYSQL数据...

3.主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:CREATE TABLE testIndex(i_testID INT  NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY  KEY(i_testID)); 当然也可以用ALTER命令。

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

四、组合索引

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

五、什么时候建立索引

where <,<=,=,>,>=,BETWEEN,IN ,like

六、索引的不足

1.提高查询速度,但是会降低insert,update,delete速度,因为不仅要保存数据,还要保存索引

2.建立索引会占用磁盘空间的索引文件,如果建立太多,索引会很大

3.

七、注意

1.索引不会包含有null值得列

2.使用短索引char(255),可以只取前10位

3.索引列排序

MySQL只使用一个索引,如果where使用了,那么orderby就不会使用

4.like语句操作

like "%abc%"不会使用索引

like "abc%" 会使用

5.不要在列运算

select * from users where YEAR(adddate)<2007;这样索引会失效

改为

select * from users where adddate<‘2007-01-01';

本文由9159.com发布于www.9159.com,转载请注明出处:需要给user表添加多个字段的联合唯一索引,修改

关键词:

上一篇:没有了
下一篇:没有了