創(chuàng)建表的命令
命令格式：create [temporary] table [if exists] [數(shù)據(jù)庫名.]表名稱(字段信息) [表選項]
每個字段必須有數(shù)據(jù)類型，最后一個字段后不能有逗號。temporary 臨時表，會話結(jié)束時表自動消失。對于字段的定義如下：
字段名 數(shù)據(jù)類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT '描述']
表選項如下表：
| 選項 | 用法 | 備注 |
|---|---|---|
| 字符集 | CHARSET = 字符集 | 如果表沒有設定，則使用數(shù)據(jù)庫字符集 |
| 存儲引擎 | ENGINE =引擎名稱 | 設置表使用什么引擎 |
| 數(shù)據(jù)文件目錄 | DATA DIRECTORY = '目錄' | 設置數(shù)據(jù)文件存放的路徑 |
| 索引文件目錄 | INDEX DIRECTORY = '目錄' | 設置索引文件存放的路徑 |
| 表注釋 | COMMENT = '描述' | 表的描述及解釋該表的用途 |
| 分區(qū)選項 | PARTITION BY | 表分區(qū)主要是用來優(yōu)化數(shù)據(jù)查詢操作 |
ps: 表在管理數(shù)據(jù)時采用的不同的數(shù)據(jù)結(jié)構(gòu)，結(jié)構(gòu)不同會導致處理方式、提供的特性操作等不同。常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive。不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時采用不同的方式:
MyISAM表文件含義：.frm表定義，.MYD表數(shù)據(jù)，.MYI表索引
InnoDB表文件含義：.frm表定義，表空間數(shù)據(jù)和日志文件
ex：
-- 顯示存儲引擎的狀態(tài)信息
SHOW ENGINES
-- 顯示存儲引擎的日志或狀態(tài)信息
SHOW ENGINE 引擎名 {LOGS|STATUS}
-- 創(chuàng)建一個名為student的臨時表，包含名稱，學號，性別等信息
create temporary table if not exists student(
id int not null auto_increment,
name varchar(20) not null default "" comment "這是學生名字",
code varchar(20) not null default "" unique key comment "學生學號",
gender varchar(6) not null default "male" comment "性別",
primary key(id)
) engine = innodb charset=utf8 comment = "學生信息表";
---創(chuàng)建一個名為course的臨時表(名稱)，并讓主鍵自增長從1001開始
create temporary table if not exists course(
id int not null auto_increment,
name varchar(10) not null,
unique key(name),
primary key(id),
check( id >= 1001)
) engine=innodb charset=utf8 comment="課程表" auto_increment = 1001
--創(chuàng)建一個名為student_course臨時表(student_id,course_id)
create temporary table if not exists student_course(
student_id int not null,
course_id int not null,
foregin key(student_id) references student(id),
foregin key(course_id) references course(id)
) engine = innodb charset=utf8 comment="學生課程中間表，學生和課程之間是多對多的關(guān)系";
顯示表信息命令
顯示表信息命令格式如下：
命令格式|作用
---|------|------
show tables|顯示當前數(shù)據(jù)庫下的所有表
show tables [like 'pattern']|顯示當前數(shù)據(jù)庫下匹配到的表
show tables from 數(shù)據(jù)庫名稱|顯示某數(shù)據(jù)庫下全部表名稱
show tables from 數(shù)據(jù)庫名 [like 'pattern']|顯示某數(shù)據(jù)庫下匹配到的表
show create table 表名稱|顯示表的詳細信息
desc 表名| 顯示表信息
describe 表名|顯示表信息
explain 表名|顯示表信息
show columns from 表名 [like 'pattern']|顯示表信息
show table status [from 數(shù)據(jù)庫名] [like 'pattern']|顯示表信息
ex:
--顯示當前數(shù)據(jù)庫下的所有表
show tables;
--顯示當前數(shù)據(jù)庫下匹配到d的表
show tables like '%d%';
--顯示demo數(shù)據(jù)庫下全部表名稱
show tables from demo;
--顯示demo數(shù)據(jù)庫下表名含有d的表
show tables from demo like '%d%';
--顯示student表的詳細信息
show create table student;
desc student;
describe student;
explain student;
--顯示表student所有列信息
show columns from student;
--顯示表student包含d的列信息
show columns from student like '%d%';
--顯示demo數(shù)據(jù)庫所有表的信息
show table status from demo；
--顯示demo數(shù)據(jù)庫表名包含d的所有表信息
show table status from demo like "%d%";
修改表信息的命令
- 修改表本身的選項
命令格式：alert table if exists 表名 表選項
ex:
--修改student表自增長值、字符集、描述信息等
alter table if exists student auto_increment=10 charset=gbk comment="學生信息";
- 表重命名、將表遷移到某個數(shù)據(jù)庫
命令格式: rename table old_table to [數(shù)據(jù)庫.]new_table
ex:
--把student表名改為new_student
rename table student to new_student;
--把new_student表移動到數(shù)據(jù)庫demo2中
rename table new_student to demo2.student;
- 修改表字段信息
命令格式：alter table 表名 操作
操作列表如下：
| 操作命令格式 | 描述 |
|---|---|
| add [column] 字段(dataType 約束) first | 增加字段，表示增加在第一個位置 |
| add [column] 字段(類型 約束) after 字段 | 增加該字段在某個字段后面 |
| add primary key(字段名,...) | 創(chuàng)建主鍵 |
| add unique [索引名] (字段名) | 創(chuàng)建唯一索引 |
| add index [索引名] (字段名) | 創(chuàng)建普通索引 |
| add foreign key(字段名) references 表名(字段名) | 創(chuàng)建外鍵 |
| drop [column] 字段名 | 刪除字段 |
| modify [column] 字段名 字段屬性 | 對字段屬性進行修改 |
| change [column] 原字段名 新字段名 字段屬性 | 對字段名修改 |
| drop primary key | 刪除主鍵(刪除主鍵前需刪除其auto_increment屬性) |
| drop index 索引名 | 刪除索引 |
| drop foreign key 外鍵 | 刪除外鍵 |
ex:
--向student表中添加birthday字段并放在字段id之后
alter table student add column birthday datetime default now() not null after id;
--向student表添加idcard字段并放在第一個位置
alter table student add idcard varchar(18) not null first;
--刪除student表主鍵
alter table student drop primary key;
--為student表增加主鍵
alter table student add primary key(id);
--student表身份證信息創(chuàng)建索引
alter table student add unique 'idcard'(idcard);
--刪除student表中索引名為idcard的索引
alter table student drop index idcard;
--student表身份證信息創(chuàng)建索引
alter table student add index 'idcard'(idcard);
--student_course表增加外鍵
alter table student_course add foreign key(student_id) references (id);
--student表刪除idcard字段
alter table student drop idcard;
--修改student表birthday屬性
alter table student modify birthday date default current_date not null;
--修改student表birthday屬性名為birth
alter table student change column birthday birth;
刪除表
命令格式：drop table [if exists] 表名
ex:
--刪除student表
drop table if exists student;
清空表數(shù)據(jù)
命令格式：truncate [table] 表名
ps:truncate清空表是先刪除表然后再重新創(chuàng)建表，自增長值從默認開始
ex:
--清空course表中數(shù)據(jù)
truncate table course;
復制表結(jié)構(gòu)
命令格式: create table 表名 like 要復制的表名
ex:
--創(chuàng)建course副表
create table course course_temp like course;
復制表結(jié)構(gòu)和數(shù)據(jù)
命令格式:create table 表名 [as] select * from 要復制的表名
ex:
--復制表course結(jié)構(gòu)及數(shù)據(jù)到course_temp2
create table course_temp2 as select * from course;
檢查表是否有錯誤
命令格式:check table 表名,.......
ex:
--檢查表student，course是否有錯誤
check table student,course;
優(yōu)化表
命令格式：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ....
修復表
命令格式：REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
分析表
命令格式:ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
ps:mysql表的優(yōu)化、修復、分析針對于存儲引擎為MyISAM和ARCHIVE表才起作用
