mysql学习笔记(四)
在上一节中,学习了表单的多种查询方法,本节将进一步学习利用alter语句对数据表进行更新、更改;添加索引、主键;以及利用索引和主键处理数据表中重复数据等操作。
1、alter语句
- 修改表名:
alter table table_name1 rename to table_name2;
- 修改表类型:
- 增加/删除字段:
alter table table_name add/drop column_name;
添加字段确认非空并指定默认值。
- 修改表字段名:
alter table table_name rename column_name1 to column_name2;
- 修改字段类型:
alter table table_name modify column_name column_type;
alter table table_name modify column_name column_type not null default xx; 修改字段类型同时说明非空且指定默认值
- 修改字段默认值:
alter table table_name alter column_name set default xx;
- 删除字段默认值:
alter table table_name alter column_name drop default;
- 查看所有字段名:
show columns from table_name;
2、索引
数据表看作字典,则索引就相当于前面单独的拼音、笔画、偏旁部首页,用来快速找到对应记录,而不用从头到尾遍历数据表,这样可以大大提高查询数据的速度,减少时间损害。当然,和字典一样,索引页的存在也会占用掉一部分存储空间,而且建立索引后,再对数据表进行增删改之类的操作,速度会变慢,因为对数据表的更改操作不仅会改变数据表内部结构,还需要改变索引页(列)。
mysql中索引可以分为普通索引和唯一索引,也可以分为单列索引和组合索引,这里讨论第一种分法。普通索引和唯一索引的区别就是,唯一索引的列的值必须唯一,不能重复,但允许有空值。主键是特殊的唯一索引,不允许空值。可以同时设置多个字段为唯一索引和主键。
(1)普通索引
- 在创建数据表时一起创建:
create table table_name(...,index index_name(username(length)));
- 给数据表添加索引:
alter table table_name add index index_name(column_name);
或 create index index_name on table_name(username(length));
(2)唯一索引
- 在创建数据表时一起创建:
create table table_name(...,unique index_name(username(length)));
- 给数据表添加索引:
alter table table_name add unique index_name(column_name);
或 create unique index index_name on table_name(username(length));
- 删除索引
alter table table_name drop index index_name;
- 显示索引的方法:
show index from table_name;\G
(3)主键
- 在创建数据表时一起创建:
create table table_name(...,primary key(column_name));
- 给数据表添加索引:
alter table table_name modify column_name not null; 先保证该列非空,添加主键有两步!!
或 alter table table_name add primary key(column_name);
- 删除主键:
alter table table_name drop primary key;
3、处理重复数据
防止重复数据的几种:通过在创建数据表时将该字段设置为主键或者唯一索引来保证,这样在插入新数据时,若数据不存在,则插入新数据;若已经存在,使用:
insert into 会报错;
insert ignore into 会自动忽略新数据,不插入新数据,仍保留原数据,只返回一个警告;
replace into 会删除旧数据,插入新数据。
- 统计重复数据:
select count(*) as repetitions, column1,column2 from table_name group by column1,column2 having repetitions >1;
- 查询时过滤重复数据:
select columns from table_name group by( column1,...);
或 select distinct column1 from table_name;
- 删除重复数据:
a.新建一个数据表:create table new_tbl select now_title,now_author,k from now_tbl group by(now_title,now_author,k);
删除旧表:drop odd_tbl;
将新表改名为旧表:alter table new_tbl rename to odd_tbl;
b.直接给可能重复的字段添加为主键:alter ignore table now_tbl add primary key(columns);