A-mysql基础语法（关联查询、分组、排序等）

-- 1.多表联查
-- 查询所有学生信息及其所在班级的名称
SELECT
	* 
FROM
	stu_info AS a,
	cla_info AS b 
WHERE
	a.cla_id = b.cla_id;-- 起别名
SELECT
	a.id,
	a.stu_name,
	b.cla_id,
	b.cla_name 
FROM
	stu_info AS a,
	cla_info AS b 
WHERE
	a.cla_id = b.cla_id;
	-- 2.**关联查询**
-- 左连接 left join  on后面放关联条件
-- 以左边的表为主表，右边的表展示能匹配的行
SELECT
	* 
FROM
	stu_info AS a
	LEFT JOIN cla_info AS b ON a.cla_id = b.cla_id;
-- 右连接
SELECT
	a.stu_name,
	a.id,
	b.cla_id,
	b.cla_name 
FROM
	stu_info AS a
	RIGHT JOIN cla_info AS b ON a.cla_id = b.cla_id;-- 内连接 inner join(效果等于多表联查)
-- 只显示两边的表都能连接的行
SELECT
	a.stu_name,
	a.id,
	b.cla_id,
	b.cla_name,
	b.cla_teacher 
FROM
	stu_info AS a
	INNER JOIN cla_info AS b 
WHERE
	a.cla_id = b.cla_id;
	
-- 3.主键与外键


#***********************************************
-- 4.分页limit

SELECT * from stu_info LIMIT 3; #查询前几行 limit 0,3

SELECT * from stu_info LIMIT 1,3;#从第一行开始，往后查三行，不包括第一行

-- pageNo页码  pageCount 页数（每页查询的行数）
# 第一页
SELECT * from stu_info LIMIT 0,3;
# 第二页
SELECT * from stu_info LIMIT 3,3;
#第二页
SELECT * from stu_info LIMIT 6,3;

#总结，分页公式：limit (pageNo-1)*3,3
#                第pageNo页，每页3行
#注意：数据库的行数从0行开始

-- 5.排序 
-- where 必须放在order by前面
-- where 必须放在limit前面
-- where可以与group by搭配
-- where条件里面不能用聚合函数，having条件里面可以用聚合函数
-- group by必须在order by前面，也必须在limit前面
-- order by 必须在limit 前面
-- where>ORDER  BY>LIMIT
-- GROUP BY>ORDER BY>LIMIT
#示例：查询所有学生信息，并且成绩按照从大到小的顺序排序

#DESC倒叙，从大到小
select * from stu_info order by grade Desc;
#Asc正序，从小到大
SELECT * from stu_info order by grade ASC;
#grade倒序，age正序
selece * from stu_info order by grade desc,age asc;



-- 6.分组group by
# mysql8 分组往往结合聚合函数使用
-- 			 对哪个字段进行分组，查询出来的结果集，只能展示该字段
--       select * from stu_info GROUP BY cla_id;
#示例：统计每个班的学生人数
select count(*) from stu_info GROUP BY cla_id;
select cla_id,count(*) from stu_info GROUP BY cla_id;

#示例：统计学生所在的班级有哪些
#having对group by分组后的数据集进行筛选
#having可以和聚合函数一起使用，但where不可以
select cla_id from stu_info
GROUP BY cla_id HAVING NOT ISNULL(cla_id);

-- 统计每个班的女生人数
-- 错误，GROUP By分组后查出的是cla_id,没有sex
select cla_id from stu_info
GROUP BY cla_id HAVING sex = 0;
-- 正确
SELECT cla_id,count(*) from(
SELECT * from stu_info where sex = 0)
as t1 GROUP BY cla_id;

**注意，每个派生出来的表都要有自己的别名**
SELECT cla_id,count(*) from(
SELECT * from stu_info where sex =0)
as t1
GROUP BY cla_id;



# 子查询
# column IN(数据集) 判断字段column的值是否在数据集中
SELECT * FROM stu_info where cla_id in(
SELECT cla_id from cla_info) ;
-- 找到stu_info表中的cla_id在cla_info表中的学生

# union-->找并集，在连接时去除重复列

select sname,ssex,sbirthday from student
union 
select tname,tsex,tbirthday from teacher;

# like-->符合某种格式的字符串(not like)
select * from student where sname like '王%';
# 找姓王的学生记录
select * from student where sname not like '王%';

# exist-->查看是否返回某种结果
select * from score as s1 where exists(
select * from score as s2 where s1.sno = s2.sno and s1.degree >avg(degree))
# 查询成绩比该课程平均成绩低的学生


# 运算符