(
id int ,
name varchar(20)
);
insert into e (id,name) values(1,'aaa');
为表加上主键约束
alter table e add primary key(id);
此处修改的命令：
修改job列，使其长度为60。
alter table employee modify job varchar(60);
删除主键约束
alter table e drop primary key;
www.2cto.com
4. 定义主键自动增长
这个的作用就是让id键随着条目的增加，自动往上增长。
注意主键类型必须是int，只有int类型的才可以自增
create table f
(
id int primary key auto_increment,
name varchar(20)
);
insert into f(name) values ('aaa');
insert into f(id,name) values (11,'bbb');
mysql> select * from f;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | aaa |
| 11 | bbb |
| 12 | aaa |
| 13 | aaa |
| 14 | aaa |
| 15 | aaa |
| 16 | aaa |
+----+------+
外键约束
约束力: 插入的外键值必须为被参照列存在的值
被参照表中被参照的数据不允许删除
注意：外键约束并没有非空和唯一的约束力
创建丈夫表和妻子表
create table husband
(
id int primary key auto_increment,
name varchar(20)
);
create table wife
(
id int primary key auto_increment,
name varchar(20),
husbandid int,
constraint husbandid_FK foreign key(husbandid) references husband(id)
);
www.2cto.com
外界约束：Constraint是定义外键约束的，foreign key是外键的名称 参照某张表的主键
分别添加两条记录
insert into husband(name) values ('laobi');
insert into husband(name) values ('laoyu');
insert into wife(name,husbandid) values ('fengjie',2);
insert into wife(name,husbandid) values ('furongjie',1);
fengjie 找老公
select * from husband where id=(select husbandid from wife where name='fengjie');
delete from husband where name='laoyu';
6 表的关系
1 多对一
创建部门表
create table department
(
id int primary key auto_increment,
name varchar(20)
);
添加三个部门信息
insert into department(name) values('开发部');
insert into department(name) values('销售部');
insert into department(name) values('人事部');
创建员工表
create table employee
(
id int primary key auto_increment,
name varchar(20),
departmentid int,
constraint departmentid_FK foreign key(departmentid) references department(id)
);
添加六个员工信息
insert into employee(name,departmentid) values ('张三',1);
insert into employee(name,departmentid) values ('李四',1);
insert into employee(name,departmentid) values ('王五',2);
insert into employee(name,departmentid) values ('赵六',3);
insert into employee(name,departmentid) values ('田七',3);
insert into employee(name,departmentid) values ('周八',null);
多表的查询
查出1号部门所有的员工
select * from employee where departmentid=1;
查出开发部所有的员工
select * from employee where departmentid=(select id from department where name='开发部');
查出赵六在那个部门
select * from department where id=(select departmentid from employee where name='赵六');
--------------------------------------
www.2cto.com
联合查询
查出开发部所有的员工
select * from department,employee;
+----+--------+----+------+--------------+
| id | name | id | name | departmentid |
+----+-------