sql

案例1

这段 SQL 语句的用法是用于查询两个或多个表中的数据，并对查询结果进行分组、过滤和排序。下面是一个具体的例子：

假设有以下两个表：

student 表：

学号	姓名	性别
1	张三	男
2	李四	女
3	王五	男

score 表：

学号	课程	成绩
1	数学	90
1	英语	80
2	数学	85
2	英语	95
3	数学	75
3	英语	70

如果要查询每个学生的姓名、性别和平均成绩，并按照平均成绩降序排列，只显示前两名，可以使用以下 SQL 语句：

select s.姓名, s.性别, avg(sc.成绩) as 平均成绩
from student as s join score as sc on s.学号 = sc.学号
group by s.姓名, s.性别
having avg(sc.成绩) > 70
order by 平均成绩 desc
limit (0,2)

查询结果如下：

姓名	性别	平均成绩
李四	女	90
张三	男	85

join 与 union

INNER JOIN、LEFT JOIN 和 RIGHT JOIN 都是用于连接两个或多个表的语法。它们的区别在于返回的行的数量和内容。

例如，假设有两个表 table1 和 table2，它们的内容如下：

table1:

id	name
1	A
2	B
3	C

table2:

id	color
1	Red
2	Blue
4	Green

• INNER JOIN：只返回两个表中匹配的行。例如：

SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;

查询结果如下：

id	name	id	color
1	A	1	Red
2	B	2	Blue

• LEFT JOIN：返回左表中的所有行，即使在右表中没有匹配的行。例如：

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id;

查询结果如下：

id	name	id	color
1	A	1	Red
2	B	2	Blue
3	C	NULL	NULL

• RIGHT JOIN：返回右表中的所有行，即使在左表中没有匹配的行。例如：

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.id = table2.id;

查询结果如下：

id	name	id	color
NULL	NULL	4	Green
1	A	1	Red
2	B	2	Blue

• UNION 和 UNION ALL 都是用于合并两个或多个 SELECT 语句的结果集。它们的区别在于 UNION 会自动去除重复行，而 UNION ALL 不会。

例如，假设有两个表 table3 和 table4，它们的内容如下：

table3:

id
1
2
3

table4:

id
3
4
5

• 使用 UNION 合并两个表的结果集：

SELECT * FROM table3
UNION
SELECT * FROM table4;

查询结果如下：

id
1
2
3
4
5

• 使用 UNION ALL 合并两个表的结果集：

SELECT * FROM table3
UNION ALL
SELECT * FROM table4;

查询结果如下：

id
1
2
3
3
4
5

exists 与 in

EXISTS 和 IN 都可以用来判断一个值是否在子查询的结果集中。它们的区别在于执行效率和适用场景。

• 当子查询的结果集较小，主查询的结果集较大时，使用 IN 通常会更快。
• 当子查询的结果集较大，主查询的结果集较小时，使用 EXISTS 通常会更快。

例如，假设有两个表 orders 和 customers，它们的内容如下：

orders:

order_id	customer_id	order_date
1	1	2022-01-01
2	2	2022-02-01
3	3	2022-03-01

customers:

customer_id	customer_name
1	Alice
2	Bob
3	Charlie

如果要查询所有下过订单的客户的姓名，可以使用以下两种方法之一：

• 使用 IN：

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

查询结果如下：

customer_name
Alice
Bob
Charlie

• 使用 EXISTS：

SELECT customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id);

查询结果与使用 IN 相同。

在实际应用中，应根据具体情况选择使用 IN 还是 EXISTS。

练习案例

insert into student(学号,姓名,出生日期,性别) 
values('0001' , '猴子' , '1989-01-01' , '男');

-- 模糊查询
select count(教师号)
from teacher
where 教师姓名 like '孟%';

-- 总成绩
select sum(score)
from score
where id = 1

-- 总人数
select count(学号) 
from socre

-- 查询每门课的最高分，最低分
select 课程号,min(score) as 最低分,max(score) as 最高分
from score
group by 课程号

-- 查询男生、女生人数
select 性别,count(*)
from student
group by 性别;


-- 查询平均成绩大于60分学生的学号和平均成绩
select 学号, avg(成绩)
from score
group by 学号
having avg(成绩)>60;

-- 查询至少选修两门课程的学生学号
select 学号, count(课程号) as 选修课程数目
from score
group by 学号
having count(课程号)>=2;

-- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

-- 查询出只选修了两门课程的全部学生的学号和姓名

select 学号,姓名
from student
where 学号 in(
    select 学号
    from score
    group by 学号
    having count(课程号)=2
);

-- 1990年出生的学生名单
select 学号，姓名
from student
where year(出生日期)=1990;