2022-08-17 第一组顾元皓

最新推荐文章于 2024-09-02 22:29:33 发布

激进的香芋

最新推荐文章于 2024-09-02 22:29:33 发布

阅读量214

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_55019823/article/details/126395767

版权

1.DQL查询语言

1.1 子查询（自连接）

按照结果集的行列数不同，子查询可以分为以下几类：

标量子查询
- 结果集只有一行一列（单行子查询）
列子查询
- 结果集有一列多行
行子查询
- 结果集有一行多列
表子查询
- 结果集是多行多列

where型子查询，如果where列=（内层sql），则内层的sql返回的必须是单行单列，单个值

where型子查询，如果where（列1，列2）=（内层sql），则内层的sql返回的可以是单列，可以是多行

经验分享：
- 分析需求
- 拆步骤
- 分步写sql
- 整合拼装sql

-- 查询每个老师的代课数
SELECT t.id, t.NAME,( SELECT count(*) FROM course c WHERE c.id = t.id ) AS 代课的数量 
FROM
	teacher t;
----------------------------------------------------------------------------
SELECT
	t.id,
	t.NAME,
	count(*) '代课的数量' 
FROM
	teacher t
	LEFT JOIN course c ON c.t_id = t.id 
GROUP BY
	t.id,
	t.NAME;

SELECT
	* 
FROM
	teacher t 
WHERE
	EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
----------------------------------------------------------------------------SELECT
	t.*,
	c.`name` 
FROM
	teacher t
	INNER JOIN course c ON t.id = c.t_id;

总结：如果一个需求可以不用子查询，尽量不使用。

sql可读性太低。

需求

建表

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`(
	`id` INT(10) PRIMARY KEY ,
	`name` VARCHAR(10) ,
	`age` INT(10) NOT NULL,
	`gender` VARCHAR(2)
)

DROP TABLE if EXISTS `course`

CREATE TABLE `course`(
	`id` INT(10) PRIMARY key,
	`name` VARCHAR(10) ,
	`t_id` INT(10)
)

DROP TABLE if EXISTS `teacher`

CREATE TABLE `teacher`(
	`id` INT(10) PRIMARY KEY,
	`name` VARCHAR(10)
)

DROP TABLE if EXISTS `score`;

CREATE TABLE `score`(
	`s_id` INT(10),
	`score` INT(10),
	`c_id` INT(10),
	PRIMARY KEY(`s_id`,`c_id`)
)
SELECT DISTINCT
SELECT gender as 性别 FROM author AS `A`
SELECT * FROM study LIMIT 220,6;

插入数据

insert into  student (id,name,age,gender)VALUES(1,'小明',19,'男'),(2,'小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'小虎',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

-- 11 查询老师的信息和他所带的科目的平均分
SELECT t.*,c.`name` 科目, AVG(sc.score)
FROM
teacher t
LEFT  JOIN course c 
ON t.id=c.t_id
LEFT JOIN scores sc 
ON sc.c_id=c.id
GROUP BY
c.`name`

-- 查询每科的平均成绩
SELECT AVG(sc.score),c.`name` FROM scores sc LEFT JOIN course c ON sc.c_id=c.id GROUP BY c.`name`
-- 12 .查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT t.`name`,MAX(sc.score),min(sc.score)
FROM
teacher t
LEFT  JOIN course c 
ON t.id=c.t_id
LEFT JOIN scores sc 
ON sc.c_id=c.id
WHERE t.`name` IN('Tom','Jerry')
GROUP BY
c.`name`
 
-- 13 查询每个学生的最好成绩的科目名称（子查询）
SELECT stu.`name`,c.`name`,sc.score
FROM
student stu 
LEFT JOIN scores sc 
ON sc.s_id=stu.id
LEFT JOIN course c 
on c.id=sc.c_id
WHERE (sc.score,stu.`name`) IN(
	SELECT MAX(sc.score),stu.`name`FROM student stu  LEFT JOIN scores sc  ON sc.s_id=stu.id GROUP BY stu.`name`)
GROUP BY 
stu.`name`
-- 14  查询所有学生的课程及分数
SELECT stu.`name`,c.`name`,sc.score
FROM
student stu
LEFT JOIN scores sc
ON sc.s_id=stu.id
LEFT JOIN course c 
ON c.id=sc.c_id
-- 15 查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT stu.id,stu.`name`,sc.`score`
FROM student stu 
LEFT JOIN scores sc 
ON sc.s_id=stu.id
WHERE sc.c_id = 1 AND sc.score >60

-- -------------------------------------------------
-- 16 查询平均成绩大于等于85的所有学生学号、姓名和平均成绩
SELECT stu.id,stu.`name`,AVG(sc.score)
FROM student stu 
LEFT JOIN scores sc 
ON stu.id=sc.s_id
GROUP BY  
stu.`name`
HAVING
AVG(sc.score) > 70
-- 17 查询有不及格课程的学生信息
SELECT tt.*
FROM
(SELECT stu.*, MIN(sc.score) min
FROM student stu 
LEFT JOIN scores sc 
ON stu.id = sc.s_id
GROUP BY
stu.`name`)tt 
WHERE
tt.min<60
-- 
SELECT student.*,scores.score
FROM student
LEFT JOIN scores
ON scores.s_id=student.id
WHERE
scores.score<60
GROUP BY
student.`name`
-- 
SELECT student.*,MIN(scores.score)
FROM student
LEFT JOIN scores
ON scores.s_id=student.id
GROUP BY
student.`name`
HAVING
MIN(scores.score) <60

-- 18 查询每门课程有成绩的学生人数
SELECT c.`name`,COUNT(*)
FROM scores sc 
LEFT JOIN course c 
ON c.id=sc.c_id
GROUP BY
c_id
-- 19 查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT AVG(score)
FROM scores 
GROUP BY
s_id
ORDER BY AVG(score) DESC,c_id ASC;
-- 20 查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT stu.id,stu.`name`,AVG(sc.score)
FROM student stu 
LEFT JOIN scores sc 
ON sc.s_id=stu.id
GROUP BY
stu.`name`
HAVING
AVG(sc.score)>60

-- 21 查询有且仅有一门课程成绩在80分以上的学生信息
SELECT stu.*
FROM student stu 
LEFT JOIN scores sc 
on sc.s_id= stu.id
WHERE sc.score > 80
GROUP BY stu.`name`
HAVING COUNT(sc.s_id) =1

-- 22  查询出只有三门课程的学生的学号和姓名
SELECT c.id 学号,c.`name` 姓名,COUNT(sc.s_id) 选中科目数
FROM scores sc 
LEFT JOIN student c 
on c.id =sc.s_id
GROUP BY
sc.s_id
HAVING COUNT(sc.s_id)=3
-- 23 查询有不及格课程的课程信息
SELECT c.*
FROM scores sc 
LEFT JOIN course c 
ON c.id =sc.c_id
GROUP BY
c.`name`
HAVING MIN(sc.score)<60

-- 24 .查询至少选择4门课程的学生信息
SELECT c.*,COUNT(sc.s_id) 选中科目数
FROM scores sc 
LEFT JOIN student c 
on c.id =sc.s_id
GROUP BY
sc.s_id
HAVING COUNT(sc.s_id)>=4
-- 25 .查询没有学全所有课程的同学的信息
SELECT c.*,COUNT(sc.s_id) 选中科目数
FROM scores sc 
LEFT JOIN student c 
on c.id =sc.s_id
GROUP BY
sc.s_id
HAVING COUNT(sc.s_id)<(SELECT COUNT(*) FROM course)

-- 26 查询选全所有课程的同学的信息
SELECT c.* 
FROM scores sc 
LEFT JOIN student c 
on c.id =sc.s_id
GROUP BY
sc.s_id
HAVING COUNT(sc.s_id)=(SELECT COUNT(*) FROM course)

-- 27  查询各学生都选了多少门课
SELECT c.*,COUNT(sc.s_id) 选中科目数
FROM scores sc 
LEFT JOIN student c 
on c.id =sc.s_id
GROUP BY
sc.s_id
-- 28 查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT stu.`name`,sc.score
FROM student stu 
LEFT JOIN scores sc 
on sc.s_id=stu.id
LEFT JOIN course c 
on c.id=sc.c_id
WHERE sc.score<60 AND c.`name`='java'
GROUP BY
stu.`name`
-- 29 查询学过"Tony"老师授课的同学的信息
 SELECT stu.*
FROM student stu 
LEFT JOIN scores sc 
on sc.s_id=stu.id
LEFT JOIN course c 
on c.id=sc.c_id
LEFT JOIN teacher t 
ON t.id =c.t_id
WHERE
t.`name` ='Tony'
GROUP BY
stu.`name`
-- 30 查询没学过"Tony"老师授课的学生信息
 SELECT *
 FROM student
WHERE id  NOT IN(
SELECT stu.id
FROM student stu 
LEFT JOIN scores sc 
on sc.s_id=stu.id
LEFT JOIN course c 
on c.id=sc.c_id
LEFT JOIN teacher t 
ON t.id =c.t_id
WHERE
t.`name` ='Tony'
GROUP BY
stu.`name`
)

日期格式

格式	描述
%a	缩写的星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值（00-31）
%e	月的天，数值（0-31）
%f	微秒
%H	小时（00-23）
%h	小时（01-12）
%I	小时（01-12）
%i	分钟，数值（00-59）
%j	年的天（001-366）
%k	小时（0-23）
%l	小时（1-12）
%M	月名
%m	月，数值（00-12）
%p	AM或PM
%r	时间，12-小时（hh:mm:ss AM或PM）
%S	秒（00-59）
%s	秒（0-59）
%T	时间，24-小时（hh:mm:ss）
%U	周（00-53）星期日是一周的第一天
%u	周（00-53）星期一是一周的第一天
%W	星期名
%Y	年，2022
%y	年，22