MySQL常用命令语句
一、MySQL常用SQL修改语句
1 | # 创建数据库
|
2 | create database database_name;
|
3 |
|
4 | # 创建数据表
|
5 | create table blog_archive(
|
6 | aid int unsigned not null default 0,
|
7 | cid SMALLINT UNSIGNED NOT NULL DEFAULT 0,
|
8 | title varchar(50) not null default '',
|
9 | contents text not null default '',
|
10 | click mediumint unsigned not null default 0,
|
11 | primary key(aid),
|
12 | key cid(cid)
|
13 | )engine=MyISAM default charset=utf8;
|
14 |
|
15 | # 增加一个字段
|
16 | alter table blog_archive add column sort smallint not null default 0 after contents;
|
17 | alter table blog_archive add column is_del tinyint(1) not null default 1;
|
18 |
|
19 | # 删除一个字段
|
20 | alter table blog_archive drop column is_del;
|
21 | alter table blog_archive drop is_del;
|
22 |
|
23 | # 修改字段名和字段类型
|
24 | alter table blog_archive change click click_num smallint not null default 0;
|
25 | alter table blog_archive change click_num mediumint not null default 0;
|
26 |
|
27 | # 修改表名
|
28 | alter table blog_archive rename to blog_article;
|
29 |
|
30 | # 修改表引擎
|
31 | alter table blog_archive engine=InnoDB;
|
32 |
|
33 | # 清空表
|
34 | truncate table blog_archive;
|
MySQL索引相关语句
1 | # 添加主键索引
|
2 | alter table `blog_archive` add primary key ('id');
|
3 |
|
4 | # 添加唯一索引
|
5 | alter table `blog_archive` add UNIQUE ('name');
|
6 |
|
7 | # 添加普通索引
|
8 | alter table 'blog_archive' add index index_name ('index');
|
9 |
|
10 | # 添加全文索引
|
11 | alter table 'blog_archive' add FULLTEXT ('label');
|
12 |
|
13 | # 添加多索引
|
14 | alter table 'blog_archive' add index index_name ('id','name','label');
|
15 |
|
16 | # 查看索引
|
17 | show index from blog_archive;
|