MySQL查询语句

表设计

---

表关系

• 一对一
• 一对多
• 多对多

场景：学生表与班级表

//创建班级表
create table class(
cid int unsigned  primary key auto_increment,//主键
cname char(20) unique not null,
sum tinyint unsigned not null
);
//将一张表的主键 作为另一张表字段(逻辑外键关联)
create table stus(
sid int unsigned  primary key auto_increment,//主键
sname char(20) unique not null,
sex enum('男','女') not null default '男',
cid int unsigned not null,
score tinyint unsigned not null
);
//创建分数表
create table score(
id int unsigned  primary key auto_increment,
score tinyint unsigned not null,
subject char(20) not null,
sid int unsigned not null
);

---

如何设计表关系

---

外键

1.物理外键：mysql数据库提供表关联方式。引擎必须是Innodb（保证数据完整统一）

2.逻辑外键：设计者通过逻辑关系进行关联。

数据库存储引擎

myisam 检索效率高 支持事务

innodb 执行效率 支持外键 不支持事务

memory 执行效率/检索效率高 数据放置在内存中，数据库发生异常，数据直接丢失，无法恢复。

---

多表查询

---

语法：select * from tb1,tb2;（笛卡尔乘积，无意义）

解决办法：select * from class,stus 条件；

条件：只能存在有关联关系的表，相同字段相等；

select * from class,stus where class.cid=stus.cid；//查询所有学生

select * from class,stus where class.cid=stus.cid and stus.cid=5；//查询5班的人

select count(1) from stus group by sex;//男女各有多少人

select count(1) from stus group by cid;//每个班多少人

select cname,count(1)sum from class,stus where class.cid=stus.cid group by stus.cid;//带班级号

select * from stus where score=(select max(score)from stus);//分数最高的学生

---

嵌套查询

select * from stus where score=(select max(score)from stus);
//将一个查询语句的结果作为另一个条件语句的条件
//嵌套查询/子查询 效率不高 但是可以实现复杂查询

---

内连接查询

语法：[inner] join 表 on 条件（相同字段相等）；

作用：实现多表查询 代替多表查询

---

外连接查询

语法：left jion(左) right join(右)

作用：实现多表查询

select * from class left join stus on class.cid=stus.cid ;
 先查class所有数据，再匹配stus有没有满足条件的数据
select * from class right join stus on class.cid=stus.cid ;
 先查stus所有数据，再匹配class有没有满足条件的数据

差异效果图

---

模糊查询

语法：like

作用：搜索功能

符号：
         %（任意多个字符）；
         _(标准匹配)；
         select * from stus where sname like '%张%';//所有名字包含张字的学生
        //张% 所有姓张的学生  
         select * from stus where sname like '张_';
        //张_ 张X // 张__ 张XX

差异效果图

---

一对一关系

丈夫
create table has(
hid int,
hname char(20) not null,
wid int unique
);
妻子
create table wife(
wid int,
wname char(20) not null,
hid int unique
);

select * from has join wife on has.hid=wife.wid; 通过丈夫找到妻子

---

扩展

select * from stus where score > (select avg(score) from stus);求大于平均分的同学

select cid,max(score) sum from stus group by cid;求每个班的最高分