一. 查看表的基本结构
DESCRIBE/DESC TABLE_NAME
查询结果含义:
Field: 字段名
Type: 字段类型
Null: 是否可以为空
Key: 是否编制索引
default: 默认值
Extra: 附加信息,如自增列
二. 查看表的详细结构
语法:SHOW CREATE TABLE TABLE_NAME
\G
三. 修改数据表
1.修改表名:
ALTER TABLE OLD_NAME RENAME TO NEW_NAME;
eg: alter table games rename to qq_game;
2.修改字段名:
ALTER TABLE TABLE_NAME CHANGE OLD_NAME NEW_NAME NEW_TYPE;
eg: alter table games change gno game_id varchar(20);
3.修改字段数据类型
ALTER TABLE TABLE_NAME MODIFY COL_NAME NEW_TYPE
eg: alter table games modify gno varchar(20);
4.添加字段:
ALTER TABLE TABLE_NAME ADD
eg: alter table games add a int;
5. 删除字段:
ALTER TABLE TABLE_NAME DROP
eg: alter table games drop a ;
6. 增补约束
ADD CONSTRAINT CON_NAME PRIMARY KEY(COL_NAME);
eg: alter table users add constraint PK_USER_USERQQ primary key (user_qq);
ADD CONSTRAINT CON_NAME FOREIGN KEY (F_COL) REFERENCES
eg: alter table scores add constraint FK_SCORES_GAMES foreign key (gno) references games(gno);
ADD CONSTRAINT CON_NAME CHECK(EXP);
eg:alter table games add constraint CK_GAMES_GNO check (gno>0);
ALTER COL_NAME SET DEFAULT
eg:alter table users alter user_sex set default '男';
MODIFY COLUMN COL_NAME..... AUTO_INCREMENT;
eg:alter table games modify 'Gno' int not null auto_increment primary key;
四. 删除数据表
1. 删除无关联的数据表
DROP TABLE [IF EXIST] TABLE_NAME1,TABLE_NAME2;
eg: drop table scores;
2. 删除有关联的数据表(删除外键所在的表,如果要删除主键所在的表就必须先解除关联关系。)
解除关联关系:ALTER TABLE_NAME(从表)DROP FOREIGN KEY CON_NAME(外键名);
删除主表:DROP TABLE TABLE_NAME1,TABLE_NAME2;