基本操作-MySQL
创建:
主键约束:
单字段:
id int(11) primary key
或者
primary key(id)
多字段:
primary key(id,name)
外键约束:
constraint fk_emp_dept1 foregin key(deptId) references tb_dept1(id)
非空约束:
not null
唯一性约束:
unique
或者
constraint sth unique(name)
默认约束
default
自增
auto_increment
查看:
desc user 查看表结构
查看创建表语句
show create table user
或者
show create table user \G
修改:
修改表名
alter table user rename user1;
修改字段名:
alter table user change column1 column2 varchar;
添加字段:
alter table user add newcolumn varchar;
添加字段在首位:
alter table user add newcolumn varchar first;
在指定列后添加字段:
alter table user add newcolumn varchar after column;
添加有约束条件的字段
alter table user add new newcolumn not null;
删除字段:
alter table user drop column1;
添加外键约束
alter table tablename add constraint fk_score foreign key (columname) references tablename(columname)
修改字段排列位置:
alter table user modify column varchar first;
alter table user modify column varchar after location;
更改表的存储引擎:
alter table user engine=MyISAM;
查看所有外键约束
SELECT C.TABLE_SCHEMA 拥有者, C.REFERENCED_TABLE_NAME 父表名称, C.REFERENCED_COLUMN_NAME 父表字段, C.TABLE_NAME 子表名称, C.COLUMN_NAME 子表字段, C.CONSTRAINT_NAME 约束名, T.TABLE_COMMENT 表注释, R.UPDATE_RULE 约束更新规则, R.DELETE_RULE 约束删除规则 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C JOIN INFORMATION_SCHEMA. TABLES T ON T.TABLE_NAME = C.TABLE_NAME JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME WHERE C.REFERENCED_TABLE_NAME IS NOT NULL and C.TABLE_NAME like '%%'
删除:
drop table if exists user;
删除外键约束:
alter table user drop foreign key fk_emp_dept;