mysql查找练习

最新推荐文章于 2021-10-16 16:19:32 发布

cp哦

最新推荐文章于 2021-10-16 16:19:32 发布

阅读量772

点赞数 1

文章标签： mysql mysql查找

本文链接：https://blog.csdn.net/weixin_44012730/article/details/117405698

版权

建表

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
);

插入数据

INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');


INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

查询
1.查询教师所有的单位但是不重复的t_depart列
distinct()函数

select distinct(t_depart) from teacher;

2.查询score表中成绩在60-80之间所有的记录(sc_degree)
注意:BETWEEN… ADN… 是包含边界的

select * from score where sc_degree between 60 and 80;

3.查询score表中成绩为85, 86, 或者88的记录(sc_degree)

select * from score where sc_degree=85 or sc_degree=86 or sc_degree=88;

in ()范围

select * from score where sc_degree=85 or sc_degree in(85,86,88);

4.以class降序查询student表中所有的记录

select * from student **order by s_class DESC**

5.以c_no升序.sc_degree降序插叙score表中所有的数据
先以c_no进行升序,若c_no相同,则以sc_degree降序

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

6.查询’95031’班的学生人数

select count(s_no) from student where s_class=95031;

注意，这里如果出现了null,count是查不到

select count(1) from student where s_class=95031;
select count(*) from student where s_class=95031;
可以无视null

count(*) 和 count(1)和count(列名)区别
执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL 。
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率上：

列名为主键，count(列名)会比count(1)快。
列名不为主键，count(1)会比count(列名)快。
如果表多个列并且没有主键，则 count（1）的执行效率优于 count（）。
如果有主键，则 select count（主键）的执行效率是最优的。
如果表只有一个字段，则 select count（）最优。

7.查询score表中的最高分数的学生号和课程号.(子查询或者排序) 难度高！！！！
子查询
先查最高分是多少
select max(sc_degree) from score;
再查
select * from score where sc_degree=(select max(sc_degree) from score);

排序
注意最高分有多个的情况下可能有数据问题
limit x,y (x:表示从X条数据开始 y:需要查出多少条)
select * from score order by sc_degree desc limit 0,1;

8.查询每门课的平均成绩 !!!
avg(）
group by

select c_no, avg(sc_degree) from score group by c_no;

9.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
having字句可以让我们筛选分组之后的各种数据

 select avg(sc_degree) from score group by c_no having count(c_no)>3 where c_no like "%3";

错了
select avg(sc_degree) from score group by c_no having count(c_no)>3 这部分没错

select avg(sc_degree) from score group by c_no having count(c_no)>=2 and c_no like “3%”; 加上后面的部分就对了

9.查询分数大于70但是小于90的s_no列:
select s_no from score where sc_degree between 71 and 89;

10.查询所有的学生 s_name , c_no, sc_degree列
首先这个包含2个表score 和student，使用联查，笛卡尔积原理

select s_name , c_no, sc_degree from score ,student where student.s_no =score.s_no;

11.查询所有学生的s_no, c_name, sc_degree列

select   s_no, c_name,sc_degree from course,student,score  where student.s_no=scroe.s_no and score.c_no =course.c_no ;

报错：1052 - Column ‘s_no’ in field list is ambiguous
因为student和score都有s_no
更改为student.s_no过了

select student.s_no, c_name,sc_degree from student,course,score where student.s_no=score

12.查询班级是’95031’班学生每门课的平均分
12.1 自己想的
先查班级是95031学生的成绩，注意这条语句查出来的是一张表

select *  from score where s_no in (select s_no from student where s_class='95031');

在嵌套查询

select avg(sc_degree) from (select *  from score where s_no in (select s_no from student where s_class='95031')) group by c_no;

Every derived table must have its own alias（每一个派生出来的表都必须有一个自己的别名）报错

(select * from score where s_no in (select s_no from student where s_class=‘95031’ ) ) as a 给派生表一个a的别名

select avg(sc_degree) from 
(select *  from score where s_no in (select s_no from student where s_class='95031' ) )  as a group by c_no;

12.2 联合查询
把student和score联合，找到里面s_class在95031的学生然后再找s_no相同的

SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

12.3 左连接

select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = ‘95031’ GROUP BY sc.c_no;

12.4 进阶加入课程名称
SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = ‘95031’ AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩的所有同学的记录
select s_no from score where c_no=‘3-105’ and sc_degree >(select sc_degree from score where c_no='3-105’and s_no=‘109’); 没法使用？？？

select s_no from score where c_no=‘3-105’ and sc_degree >(select sc_degree from score where c_no='3-105’and s_no=‘109’
);

14.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩的所有同学的记录
select * from student ,course, score where student.s_no in (select s_no from score where c_no=‘3-105’ and sc_degree >(select sc_degree from score where c_no='3-105’and s_no=‘109’)) and student.s_no=score.s_no and course.c_no=score.c_no and sco
re.c_no=‘3-105’;

显示
±-----±-------±------±--------------------±--------±------±-----------±-----±-----±------±----------+
| s_no | s_name | s_sex | s_birthday | s_class | c_no | c_name | t_no | s_no | c_no | sc_degree |
±-----±-------±------±--------------------±--------±------±-----------±-----±-----±------±----------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 3-105 | 计算机导论 | 825 | 103 | 3-105 | 92 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 3-105 | 计算机导论 | 825 | 105 | 3-105 | 88 |
±-----±-------±------±--------------------±--------±------±-----------±-----±-----±------±----------+

出现了2个c_no和2个s_no，暂时不知道怎么处理

15.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

注意这题是同年出生
使用year()函数，对date类型可以求出它的年份
查出年份
select year(s_birthday) from student where s_no in (108,101);

嵌套

select s_no,s_name,s_birthday from student where year(s_birthday) in (select year(s_birthday) from student where s_no in  (108,101)

16.查询选修课程的同学人数多余 5 人的教师姓名
查找到选课超过2人的课程号
select c_no from score group by c_no having count(c_no)>=3;

用course表，从课程号找到教师号
from course where c_no in(select c_no from score group by c_no having count(c_no)>=3);

17 查询’计算机系’与’电子工程系’ 不同职称的教师的t_name和t_rof(职称)
select t_rof from teacher where depart =“计算机系”；
select t_rof from teacher where depart =“电子工程系”；

select * from teacher where t_depart =“计算机系” and t_rof not in (select t_rof from teacher where t_depart =“电子工程系”);

select * from teacher where t_depart =“电子工程系” and t_rof not in (select t_rof from teacher where t_depart =“计算机系” );

select * from teacher where t_depart ="计算机系" and t_rof not in (select t_rof from teacher where t_depart ="电子工程系")
union
select * from teacher where t_depart ="电子工程系" and t_rof not in (select t_rof from teacher where t_depart ="计算机系" );

18. 查询选修编号为"3-105"课程且成绩 至少高于 选修编号为’3-245’同学
的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
至少高于说明 == 大于其中任意一个 ==大于最小值
至少使用any()

select * from score where c_no=“3-105”;

select * from score where c_no="3-105" and sc_degree > any(select sc_degree from score where c_no="3-245") order by sc_degree desc;

19.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree

且成绩高于选修编号为"3-245"课程的同学 = 大于3-245中的所有

select * from score where c_no="3-105" and sc_degree > all(select sc_degree from score where c_no="3-245");

总结: ANY 和 ALL
ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了.
ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行.

20. 查询所有教师和同学的 name ,sex, birthday
重点是要显示在同一张表中，用到as别名

select s_no,s_name,s_sex from student
union
select t_no as s_no , t_name as s_name,t_sex as s_sex
 from teacher;

21.查询成绩比该课程平均成绩低的同学的成绩表
我的写法

select score.c_no,
sc_degree from score ,(select c_no, avg(sc_degree) as sc from score group by c_no) as a where score.c_no=a.c_no and score.sc_degree < a.sc;

教程答案!!!

SELECT * FROM score AS sc1 WHERE sc1.sc_degree < (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);

这个sql可以看做一个两层的for循环
遍历sc1
遍历sc2
找到sc1.sc_degree<AVG(sc_degree)

22.查出至少有2名男生的班号 !!!
一开始写不出来

select s_class from student where s_sex="男" group by class having count(*) >1;

这个执行过程是 from student 加载表到内存，where进行过滤，group by 把过滤完的表进行分表，对每个分表执行一次select s_class，再having，然后汇总就是结果

23 .查询student 表中不姓"王"的同学的记录
SELECT * FROM student WHERE s_name NOT LIKE ‘王%’;

查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;

25.查询所有同学的s_no , c_no 和grade列
假设建了个表

CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'c');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');