MySql条件查询及连接

where子句

where子句用于过滤记录

单条件

# 查询 id 等于1的记录
select * from books where id = 1;

# 查询 price 大于100的记录
select * from books where price > 100;

# 查询 price 小于100的记录
select * from books where price < 100;

# 查询 price 大于等于100的记录
select * from books where price >= 100;

# 查询 price 小于等于100的记录
select * from books where price <= 100;

# 空值判断 查询分类是空的记录
select * from books where category is null;

# 模糊匹配 查询作者的名字中是否带‘三’的记录， %表示任意字符
select * from books where author like '%三%';

# 查询作者的名字是以‘三’开头的记录
select * from books where author like '三%';

# 查询书名以‘笔记’结尾的记录
select * from books where name like '%笔记'

多条件

and & or 运算符用于一个以上的条件，对记录的过滤

如果多个条件都要成立，则使用and运算符

# 查询 价格在50 - 150 之间的书籍
select * from books where price <= 50 and price <= 150;

如果多个条件只要有一个成立，则使用or运算符

# 查询 分类是仙侠 或者价格大于150 或价格小于50 的书籍
select * from books where category = '仙侠' or price > 150 or price < 50;

# 查询 价格在 100 - 150 之间的书籍 或 分类是 爱情的 书籍
select * from books where category = '爱情' or price >= 100 and price <= 150;

# 查询  价格大于 100 ，分类是 爱情或者是冒险的书籍。注意，and与or同时出现的，将视为整体的部分用小括号包裹
select * from books where price > 100 and(category = '冒险' or category = '爱情')

in 符合范围中的某一个值

# 查询 分类是 爱情，冒险，仙侠 中的一个书籍
select * from books where category in('爱情','冒险','仙侠');

聚合函数

# 查询个数
select * from books;

# 查询价格大于100的有几本
select * from books where price > 100;

# 求和
select sum(price) from books;

# 求最大值
select max(price) from books;

# 求最小值
select min(price) from books;

# 查询最贵的那本书 （子查询）
select * from book where price = (select max(price) from books);

# 平均值
select avg(price) from books;

排序

order by可以对记录进行排序

# 按照价格从小到大排序
select * from books order by price;

# 按照价格从小到大排序
select * from books order by price desc;

# 与where联合使用。where要在order by的前面，id大于3的值，按照价格排序
select * from books where id > 3 order by price;

分页

使用limit可以限制 查询结果的数量

# 查询 前4本书籍
select * from books limit 4;

# 查询第二页的书籍，每页显示4本
select * from books limit 4,4;

# 查询最贵的三本书
select * from books order by price desc limit 3;

# 查询最贵的那本书
select * from books order by price desc limit 1;

多表关联查询

数据库有三种关联关系，分别为：一对多，一对一，多对多

​ 以学生表，老师表，班级表为例，学生与班级的关系就是多对一，学生请了家教，一个学生对应一个老师，就是一对一。老师与班级之间是多对多的关系，一个班级有多个任课老师，每个任课老师带多个班级，

​ 新建两张表

DROP TABLE IF EXISTS `classroom`;
CREATE TABLE `classroom` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '教室名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of classroom
-- ----------------------------
INSERT INTO `classroom` VALUES ('1', '鄱阳湖');
INSERT INTO `classroom` VALUES ('2', '淀山湖');
INSERT INTO `classroom` VALUES ('3', '老君山');
INSERT INTO `classroom` VALUES ('4', '桐柏山');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(255) DEFAULT NULL COMMENT '学生姓名',
  `gender` varchar(255) DEFAULT NULL COMMENT '性别',
  `age` int DEFAULT NULL COMMENT '年龄',
  `score` int DEFAULT NULL COMMENT '成绩',
  `c_id` int DEFAULT NULL COMMENT '教室id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '吴用', '男', '20', '4', '1');
INSERT INTO `student` VALUES ('2', '李逵', '男', '23', '22', '1');
INSERT INTO `student` VALUES ('3', '孙二娘', '女', '26', '103', '1');
INSERT INTO `student` VALUES ('4', '扈三娘', '女', '18', '59', '2');

在多的那一端加一个字段，存储一 那一端的id，在上面的例子中，学生是多的一端，在student表添加

一个字段，c_id存储教室classroom表的id

内连接

#查询鄱阳湖的同学的信息
select
   s.id 学生编号,
   s.name 学生姓名,
   c.name as 教室
from 
# 我们可以为表添加别名
	student s
inner join classroom c on c_id = c.id
where
	c.name = '鄱阳湖';
	
#内连接 另外一种方法
select 
	s.id,
	s.name,
	c.`name` as classroom
from
	student s,
	classroom c
where
	s.c_id = c.id

外连接

左外连接

#查询所有的教室信息，包含所属的班级名
select 
	s.id,
	s.name,
	c.`name` as classroom
from
	student s left join
	classroom c
on   #on后面跟两种表关联的条件
	s.c_id = c.id

右外连接

# 查询所有教室的信息，包含它的学生信息
select
	s.id,
	s.name,
	c.`name` as classname
from 
	student s right join
	classroom c
on
	s.c_id = c.id