MySQL 之外键和查询关键字
常见操作数据库的方法:
外键
添加外键需注意
1.确保主键和外键字段类型、大小、编码一致
2.确保表引擎一致(engine=Innodb)
3.确保主表中的数据副表中有对应的值
4.确保外键名不重复
create table userinfo(
uid int auto_increment primary key,
name varchar(32),
department_id int,
constraint fk_user_depar foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
唯一索引:
create table t2(
id int ,
nid int,
sid int,
....
unique uq1(nid) # 唯一索引
unique uq2(nid,sid) # 联合索引 作用:加速查找,不能重复,可以为空
)engine=innodb default charset=utf8;
外键的变种: foreign key-->>fk()
一对一:用户表和百合网(admin)
create table userinfo1(
id int auto_increment primary key,
name varchar(20),
age int,
email varchar(20)
)engine=innodb default charset=utf8;
create table admin(
id int not null auto_increment primary key,
username varchar(20) not null,
userpwd varchar(20) not null,
user_id int not null,
unique uq1 (user_id),
CONSTRAINT fk_admin_u1 foreign key(use_id) references userinfo1(id)
)engine=innodb default charset=utf8;
一对多:用户表和部门表
多对多(双向的一对多):用户表和主机表 ,用双向的FK()加快查找速度
用户表
create table userinfo2(
id int not null auto_increment primary key,
name varchar(20),
email varchar(64)
)engine=innodb default charset=utf8;
主机表
create table host(
id int auto_increment primary key ,
name varchar(20)
)engine=innodb default charset=utf8;
中间关联表
create table user2host(
id int auto_increment primary key,
userid not null,
hostid not null,
unique uq_user2_host(userid,hostid)
CONSTRAINT fk_u2h_user2 foreign key(userid) references userinfo2(id),
CONSTRAINT fk_u2h_host foreign key(hostid) references host(id)
)engine=innodb default charset=utf8;
通配符 like
# 表示以a开头的匹配，只要是a开头的都可以 select * from t5 where name like "a%";
# 表示以a开头的匹配，只要以a开头的只能取一位 select * from t5 where name like "a_"; 分页 limit
# 表示取10条数据 select * from t5 limit 0,10;
select * from t5 limit 10;
# 表示从10处开始向后取10条数据 select * from t5 limit 10,10;
# 表示从第20行开始向后取10条数据 select * from t5 limit 20,10; select * from t5 limit 10 offect 20; 排序 order by
# 从小到大 select * from t5 order by id asc;
# 从大到小 倒序 abcd select * from t5 order by id desc;
# 表示从大到小取前面的2个数据 select * from t5 order by id desc limit 2;
# 优先按照age从大到小排，在根据id从小到大排 select * from t5 order by age desc ,id asc; 分组 group by create table department5( id int auto_increment primary key, title varchar(32) )engine=innodb default charset=utf8; insert into department5(title)values("公关"),("IT"),("前台"),("服务"); insert into department5(title)values("飞行"); create table userinfo5( id int auto_increment primary key, name varchar(20), part_id int, CONSTRAINT fk_user_part FOREIGN key (part_id) REFERENCES department5(id) )engine=innodb default charset=utf8; insert into userinfo5(name,part_id)values("旺财",3),("狗蛋",5),("alex",1),("二狗",7),("egon",1); # 按照 part_id分组 select count(id),max(id), part_id from userinfo5 group by part_id; 常见的聚合函数 max ,min,count,sum,avg # *****如果对于聚合函数进行二次筛选时必须使用 ---having--- **** select count(id) as id ,max(id), part_id from userinfo5 group by part_id having part_id >1 ; 连表操作 左右连表 select * from department5,userinfo5 where department5.id = userinfo5.part_id; # left join左边的全部显示 select * from department5 left join userinfo5 on department5.id = userinfo5.part_id; # inner join 去除表中出现的 NUll值 select * from department5 inner join userinfo5 on department5.id = userinfo5.part_id; # right join右边的全部显示 select * from department5 right join userinfo5 on department5.id = userinfo5.part_id; 上下连表 union(列数必须相同,带有自动去重功能) union all(列数必须相同,不带有自动去重功能)
