mysql查询外键包含的信息_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(列数必须相同,不带有自动去重功能)