create table XXX(
id int(11) primary key,
name varchar(22) not null,
dept varchar(22) unique,
location varchar(22),
salary float);
create table XXX_son(
id int(11),
name varchar(22) not null,
dept varchar(22) unique,
location varchar(22),
constraint fk_emp_dept foreign key(dept) references XXX(id)
salary float,
primary key(...,...,...)
);
define a constraint foreign key from XXX(id) to dept and it called fk_emp_dept
their dataFramework should be same
unique 要求该列唯一,允许为空最多一个
default 定义默认值
auto_increment 只用于int(n)型
not null 要求非空
alter table tb_dept1 rename aaaaaaa
改table名字
alter table tb_dept1 MODIFY name varchar(30)
改name字段的数据类型
alter table tb_dept1 CHANGE name anothername varchar(30);
改变name字段的名字和类型(也可以只改变类型,名字不变即可)
alter table tb_dept1 add Another_feature int(30) not null...;
添加一个字段(nut null等约束)
alter table tb_dept1 add Another_feature int(30) First;
在第一行添加
alter table tb_dept1 add Another_feature int(30) after name;
在name后添加
alter table tb_dept1 drop Another_feature int(30);
删除一个字段
alter table tb_dept1 Modify Another_feature int(30) first;
alter table tb_dept1 modify Another_feature int(30) after XXX;
把一个字段改变位置
alter table tb_dept1 engine = MyISAM
更改储存引擎
show create table XXX \G
查看储存打引擎和详细
alter table XXX drop foreign key XXXXX;
删掉某个外键XXXXX为外键的名字 比如constraint fk_emp_dept foreign key(dept) references XXX(id)中的fk_emp_dept
Drop table IF EXISTS tb1,tb2...;
create table office(
officeCode INT(10) primary key not null unique,
city varchar(50) not null ,
address varchar(50),
country varchar(50) not null,
postalCode varchar(15) unique
);
create table employees(
employeeNumber INT(11) primary key not null unique auto_increment,
lastName Varchar(50) not null,
firstName varchar(50) not null,
mobile varchar(25) unique,
officeCode int(10) not null,
jobTitle varchar(50) not null,
birth DateTime,
note varchar(255),
sex varchar(5),
constraint fk_employees_office foreign key(officeCode) references office(officeCode)
);
desc tableX;
select *from tablex;
show create table XXX \G;