目录
一、mysql约束
1.主键
--mysql约束
--主键
create table t17(
id int primary key,
name varchar(50),
email varchar(50)
)
create table t18(
id int,
name varchar(50),
email varchar(50),
primary key(id)
)
insert into t17 value(1,'tom','tom@sohu.com')
insert into t17 value(1,'tom','tom@sohu.com')--报错
--复合主键
create table t19(
id int,
name varchar(50),
email varchar(50),
primary key(id,name)
)
insert into t19 value(1,'tom','tom@sohu.com')
insert into t19 value(1,'rose','tom@sohu.com')
insert into t19 value(1,'tom','tom@sohu.com')--报错
2.唯一约束(unique)
--unique唯一约束
create table t20(
id int unique,--唯一约束,如果没有加not null,那么该列可以有多个null值
name varchar(50),
email varchar(50)
)
insert into t20 value(1,'tom','tom@sohu.com')
insert into t20 value(null,'tom','tom@sohu.com')
insert into t20 value(null,'tony','tom@sohu.com')
select * from t20;
3.外键
--外键约束
--主表my_class
create table my_class(
id int primary key,
name varchar(32) not null default ''
)
--从表my_stu
create table my_stu(
id int primary key,
name varchar(32) not null default '',
class_id int,
foreign key (class_id) references my_class(id)
)
insert into my_class value(1,'java01'),(2,'javaweb');
insert into my_stu value(1,'tom',1),(2,'jimi',2);
insert into my_stu value(3,'tom',3);--插入失败,因为主表没有3号班级
select * from my_class;
select * from my_stu;
4.check约束
tips:mysql8.0及以上版本支持check
--check
create table t21(
id int primary key,
name varchar(32),
sex varchar(6) check (sex in('man','woman')),
sal double check (sal>100 and sal<3000)
);
insert into t21 value(1,'tom','man',2000)
insert into t21 value(2,'tom','mid',5000)--插不进去
5.商店售货表结构设计
-- 案例:商店售货系统表结构
-- 商品表
create table goods(
goods_id int primary key,
goods_name varchar(128) not null default '',
unitprice decimal(10,2) not null default 0
check(unitprice >=1.0 and unitprice <= 9999.99),
category int not null default 0,
provider varchar(128) not null default ''
);
-- 客户表
create table customer(
customer_id char(8) primary key,
name varchar(32) not null default '',
address varchar(128) not null default '',
email varchar(64) unique not null,
sex ENUM('男','女') not null,
card_id char(18)
)
-- 购买记录表
create table purchase(
order_id int primary key,
customer_id char(8) not null default '',
goods_id int not null default 0,
nums int not null default 0,
foreign key(customer_id) references customer(customer_id),
foreign key(goods_id) references goods(goods_id)
)
6.自增长
-- 自增长
create table t22(
id int primary key auto_increment,
name varchar(32) not null default ''
)
insert into t22 value(null,'tom');
insert into t22 (name) value('jimi');
select * from t22;
-- 修改自增长初始值
alter table t22 auto_increment=100
insert into t22 (name) value('phlipu');
select * from t22;
结果:
二、索引
1.索引优化速度
--索引
create index empid_index on emp(id)
2.索引机制
3.创建索引
-- 索引
create table t23(
id int,
name varchar(32) not null default ''
)
-- 查看索引
show index from t23;
-- 创建唯一索引
create unique index id_index on t23(id);
-- 创建普通索引
create index id_index on t23(id);
-- 如何选择添加唯一索引还是添加普通索引
-- 解答:如果确认某列不会重复,优先选择唯一索引,否则使用普通索引
-- 创建普通索引方式2
alter table t23 add index name_index (name)
-- 添加主键索引
create table t24(
id int,
name varchar(32) not null default ''
)
alter table t24 add primary key (id)
4.删除索引
-- 删除索引
drop index name_index on t23;
-- 删除主键索引
alter table t21 drop primary key
5.修改索引
先删除,再添加
6.查询索引
--查询索引
show index from t23
show keys from t23
desc t23
7.创建索引的规则
三、事务
1.什么是事务
--事务
create table t25(
id int primary key,
name varchar(32) not null default ''
)
select * from t25;
--开启事务
start TRANSACTION
--设置保存点
SAVEPOINT a
--执行dml操作
insert into t25 value(1,'tom')
SAVEPOINT b
insert into t25 value(2,'jimi')
--回退到b
rollback to b
rollback to a
COMMIT
2.事务注意事项
--开启事务方式2
create table t26(
id int primary key,
name varchar(32) not null default ''
)
select * from t26;
--开启事务
set autocommit=off;
--设置保存点
SAVEPOINT a
--执行dml操作
insert into t26 value(1,'tom')
--回退到a
rollback to a