目录
1 表的字节量限制 65535
mysql数据表中所有字段的字节量之和,最大65535字节
如果存在允许 null 值的字段,需要一个额外字节来表示null值
varchar需要1个或2个字节来表示字符串的字节长度
text 只占用表的10个字节
mysql -uroot -p
use db1;
set names gbk;
drop table if exists tb1;
create table tb1( – 1字节表示null
a int, – 4字节
b double, – 8字节
c varchar(65520) – 65535-13-2
)engine=innodb charset=latin1;
drop table if exists tb2;
create table tb2( – 1字节表示null
a int, – 4字节
b varchar(21839), – 65535-15-2 / 3
c text, – 10字节
d tinyint – 1字节
)engine=innodb charset=utf8;
2 约束
对一个字段的取值进行限制
五种约束:
l 主键
l 外键
l 非空
l 唯一
l 检查
2.1 主键
主键是一行数据的唯一标识
l 不重复
l 不允许null值
l 会自动创建索引
l 尽量不使用业务数据作为主键
l 应该使用业务无关的无意义数据作为主键
n 自增主键
n 随机值 58a7ca8f97c7e975ea
n 流水号(日期,用户id,随机值…)
学生表
id(PK) | 学号 | 姓名 | … |
1 | 201409000183 | 张三 |
|
2 | 201409000184 | 李四 |
|
人员表
id(PK) | 身份证号 | 姓名 | … |
ac8768ca6d68f8e | 123123123123 | 张三 |
|
f87c8ca987d7e8b | 234234234234 | 李四 |
|
电信计费
id(PK) | 手机号 | … | … |
1 | 12341234 |
|
|
2 | 12341234 |
|
|
2.1.1 创建主键
2.1.1.1 新建表时创建主键
drop table if exists tb1;
create table tb1(
id int primary key,
name varchar(10)
)engine=innodb charset=utf8;
drop table if exists tb1;
create table tb1(
id int,
name varchar(10),
primary key(id)
)engine=innodb charset=utf8;
insert into tb1 values(1,’a’);
insert into tb1 values(1,’b’);
insert into tb1 values(null,’c’);
2.1.1.2 双主键
用两个字段组合起来作为一个主键
l 两个字段的组合不重复
l 双主键不推荐使用
create table tb2(
username varchar(10),
ip varchar(20),
…,
primary key(username,ip)
);
2.1.1.3 修改表时添加主键
alter table tb1 add
prmary key(id);
2.1.2 查看主键
desc tb1;
show create table tb1\G
2.1.3 删除主键
alter table tb1
drop primary key;
desc tb1;
show create table tb1;
2.1.4 自增主键
2.1.4.1 创建表时设置自增主键
drop table if exists tb1;
create table tb1(
id int primary key auto_increment,
name varchar(10)
)engine=innodb charset=utf8;
drop table if exists tb1;
create table tb1(
id int auto_increment,
name varchar(10),
primary key(id)
)engine=innodb charset=utf8;
insert into tb1(name) values
(‘a’),(‘b’),(‘c’);
select * from tb1;
2.1.4.2 修改主键字段,使其自增
让一个不是自增的主键字段,变成自增
alter table tb1
modify id int auto_increment;
2.1.4.3 查看自增主键
show create table tb1\G
可以调整下一个自增值(一般不会这么做)
alter table tb1
auto_increment=100;
当手动插入一个最大主键值,自增值会变成这个最大值+1
insert into tb1 values(1000, ‘d’);
show create table tb1\G
2.1.4.4 获得最后插入的自增值
last_insert_id() 函数
l 获得当前会话最后插入的一个自增id值
会话一 | 会话二 |
use db1; | use db1; |
insert into tb1(name) values(‘aaa’); |
|
| insert into tb1(name) values(‘bbb’); |
select last_insert_id(); |
|
| select last_insert_id(); |
2.1.4.5 取消自增
取消自增,不会删除主键约束
不加 auto_increment 就是取消
alter table tb1
modify id int;
2.2 外键
约束一个字段的取值,只能取另一个主键字段中,存在的值
l 不能取指定主键中不存在的值
l 可以重复
l 可以取null值
l 外键自动创建索引
2.2.1 添加外键约束
2.2.1.1 创建表时添加
– 班级表
drop table if exists banji;
create table banji(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
– 学生表
drop table if exists xuesheng;
create table xuesheng(
id int primary key auto_increment,
name varchar(10),
gender char(1),
age tinyint unsigned,
ban_id int,
foreign key(ban_id)
references banji(id)
)engine=innodb charset=utf8;
– 插入班级
insert into banji(name)
values(‘A’),(‘B’);
– 插入学生
insert into xuesheng(name,ban_id)
values
(‘张三’,1),(‘李四’,2),(‘王五’,1);
insert into xuesheng(name,ban_id)
values(‘赵六’, 999);
删除被引用的主键值(父数据),不能删除
先删除子项数据或把外键值置空,才能删父数据
delete from banji where id=1;
存在外键约束,不能删除父表
先取消外键,或删除子表,才能删父表
drop table banji;
2.2.1.2 修改表时添加
alter table xuesheng
add foreign key(ban_id)
references banji(id);
2.2.2 查看外键约束
show create table xuesheng\G
2.2.3 删除外键约束
删除外键,不会自动删除索引,可以再手动删除索引
删除时,要指定外键名称
alter table xuesheng
drop foreign key xuesheng_ibfk_1;
show create table xuesheng\G
删除索引(可选)
alter table xuesheng
drop index ban_id;
show create table xuesheng\G
2.3 非空
不允许null值
2.3.1 添加非空约束
学生姓名非空
create table xuesheng(
…
name varchar(10) not null,
…
);
alter table xuesheng
modify name varchar(10) not null;
insert into xuesheng(age)
values(22);
insert into xuesheng(name,age)
values(null, 22);
2.3.2 查看非空约束
desc xuesheng;
show create table xuesheng\G
2.3.3 取消非空约束
不写not null,就是允许null
alter table xuesheng
modify name varchar(10);
或明确写出null
alter table xuesheng
modify name varchar(10) null;
desc xuesheng;
2.4 唯一
限制字段取值,不允许重复
允许重复的null值
自动创建索引
2.4.1 添加唯一约束
2.4.1.1 创建表时添加
create table xuesheng(
…
name varchar(10) unique,
…
);
create table xuesheng(
…
name varchar(10),
…,
unique key(name)
);
两个字段组合不重复
create table tb1(
name …,
ip …,
unique key(name,ip)
);
2.4.1.2 修改表时添加
alter table xuesheng
modify name varchar(10) unique;
alter table xuesheng
add unique key(name);
2.4.2 查看唯一约束
desc xuesheng;
show create table xuesheng\G
2.4.3 取消唯一约束
用唯一约束的索引名,来删除唯一约束
alter table xuesheng
drop index name;
desc xuesheng;
2.5 检查
用条件判断,来检查一个字段的取值,是否符合逻辑要求
mysql不支持检查约束,可以执行添加约束的语法,但是不起作用
create table xuesheng(
…,
check(gender in(‘男‘,’女‘)),
check(age between 16 and 60)
);
3 表之间的关系
l 一对一
l 一对多
l 多对多
3.1 一对一
l 通过不重复的外键关系,来设置表之间的一对一关系
联系方式表
drop table if exists lianxi;
create table lianxi(
xs_id int primary key,
tel varchar(20),
email varchar(100),
foreign key(xs_id)
references xuesheng(id)
)engine=innodb charset=utf8;
select * from xuesheng;
给学生添加联系方式
insert into lianxi(xs_id,tel)
values
(1, ‘123412341234’),
(3, ‘5435345345’);
select * from lianxi;
3.2 一对多
3.3 多对多
l 用关系中间表体现多对多关系
– 课程表
drop table if exists kecheng;
create table kecheng(
id int primary key auto_increment,
name varchar(20)
)engine=innodb charset=utf8;
– 学生课程多对多关系中间表
drop table if exists xs_kc_link;
create table xs_kc_link(
xs_id int not null,
kc_id int not null,
unique key(xs_id,kc_id),
foreign key(xs_id) references xuesheng(id),
foreign key(kc_id) references kecheng(id)
)engine=innodb charset=utf8;
insert into kecheng(name)
values(‘语文’), (‘数学’), (‘英语’);
insert into xs_kc_link values
(1,1),(1,2),(1,3),(2,2),(2,3),(3,1),(3,3);
select * from xuesheng;
select * from kecheng;
select * from xs_kc_link;
4 增删改 DML
l 插入数据 insert
l 更新数据 update
l 删除数据 delete
4.1 插入数据 insert
l insert into tb1 values(1,2,3)
l insert into tb1(c,b) values(3,2)
l insert into tb1(c,b) values
(3,2),(4,3),(5,4)
l 批量插入
n insert into tb1(c,b)
select x,y from tb2…;
n 自表数据复制
insert into tb1(c,b)
select x,y from tb1…;
n 把查询结果,创建成一张表
create table tb2
as – 可省略
select c,b from tb1…;
课程表自表数据复制
insert into kecheng(name) select name from kecheng;
查询学生数据,创建成一张新表tb2
create table tb2s
select gender,name from xuesheng;
select * from tb2;
4.2 更新数据 update
l update tb1 set c=1, b=5
where …
l 特殊修改方式
例如,销量最低的5件商品下架
update tb_item set status=2
order by 销量 limit 5;
最新添加的两个学生,去2班
按id降序排序,取前两条
update xuesheng set ban_id=2
order by id desc limit 2;
select * from xuesheng;
4.3 删除数据 delete
l delete from tb1
where …
l 特殊删除方式
先排序,再指定删除前几条
delete from tb1
order by a limit 2
删除新加入的两个学生
delete from xuesheng
order by id desc limit 2;
select * from xuesheng;