数据库-mysql查询练习

最新推荐文章于 2023-11-18 23:30:00 发布
最新推荐文章于 2023-11-18 23:30:00 发布
阅读量270 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44999716/article/details/121459754
版权

title: 数据库-mysql查询练习
date: 2021-11-21 20:39:00
tags:

数据库-mysql查询练习

数据准备

新建一个查询用的数据库:selectTest
CREATE DATABASE selectTest;
选择该数据库:
USE selectTest;

学生表:
student
学号
姓名
性别
出生日期
所在班级
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'学生所在的班级'
);


课程表:
course
课程号
课程课程名称
教师编号
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)
);

报错:Failed to open the referenced table 'teacher'
我们先创建teacher表


教师表
teacher
教师编号
教师名字
教师性别
出生日期
职称
所在部门
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'教师所在的部门'
);


成绩表
score
学号
课程号
成绩
注意:视频中原先只有一个主键s_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)
);

查看创建的表以及架构
SHOW TABLES;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course               |
| score                |
| student              |
| teacher              |
+----------------------+

查看student表结构    DESCRIBE student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no       | varchar(20) | NO   | PRI | NULL    |       |
| s_name     | varchar(20) | NO   |     | NULL    |       |
| s_sex      | varchar(10) | NO   |     | NULL    |       |
| s_birthday | datetime    | YES  |     | NULL    |       |
| s_class    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

查看teacher表结构    DESCRIBE teacher;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no       | varchar(20) | NO   | PRI | NULL    |       |
| t_name     | varchar(20) | NO   |     | NULL    |       |
| t_sex      | varchar(20) | NO   |     | NULL    |       |
| t_birthday | datetime    | YES  |     | NULL    |       |
| t_rof      | varchar(20) | NO   |     | NULL    |       |
| t_depart   | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

查看course表结构    DESCRIBE course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| t_no   | varchar(20) | NO   | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

查看score表结构    DESCRIBE score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | MUL | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

向表中添加数据

--学生表数据
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.查询student表中所有的记录
select * from student;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

2.查询student表中所有记录的s_name,s_sex和s_class列
select s_name, s_sex, s_class from student;

±-------±------±--------+
| s_name | s_sex | s_class |
±-------±------±--------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆军 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
±-------±------±--------+

3.查询教师所有的单位但是不重复的t_depart列
distinct排除重复

select t_depart from teacher;
±-----------+
| t_depart |
±-----------+
| 计算机系 |
| 计算机系 |
| 电子工程系 |
| 电子工程系 |
±-----------+

select distinct t_depart from teacher;

±-----------+
| t_depart |
±-----------+
| 计算机系 |
| 电子工程系 |
±-----------+

4.查询score表中成绩在60-80之间所有的记录(sc_degree)
注意:BETWEEN… ADN… 是包含边界的
select * from score where sc_degree between 60 and 80;

或者

select * from score where sc_degree > 60 and sc_degree < 80;

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±-----±------±----------+

5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
select * from score where sc_degree in (85, 86, 88);

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
±-----±------±----------+

6.查询student表中’95031’班或者性别为’女’的同学记录
select * from student where s_class = ‘95031’ or s_sex = ‘女’;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

7.以class降序查询student表中所有的记录
升序asc、降序desc，默认就是升序的，所以升序可以不用写asc
select * from student order by s_class desc;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

select * from student order by s_class;

或者

select * from student order by s_class asc;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
±-----±-------±------±--------------------±--------+

8.以c_no升序.sc_degree降序插叙score表中所有的数据
先以c_no进行升序,若c_no相同,则以sc_degree降序
select * from score order by c_no asc, sc_degree desc;

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
±-----±------±----------+

9.查询’95031’班的学生人数
统计用count
select count(*) from student where s_class = ‘95031’;

或者

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

±---------+
| count(*) |
±---------+
| 5 |
±---------+

10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
select s_no, c_no from score where sc_degree = (select max(sc_degree) from score);

拆解：

select max(sc_degree) from score //找到最高分

select s_no, c_no from score where sc_degree = (select max(sc_degree) from score);
±-----±------+
| s_no | c_no |
±-----±------+
| 103 | 3-105 |
±-----±------+
desc score;
±----------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±--------------±-----±----±--------±------+
| s_no | varchar(20) | NO | PRI | NULL | |
| c_no | varchar(20) | NO | PRI | NULL | |
| sc_degree | decimal(10,0) | YES | | NULL | |
±----------±--------------±-----±----±--------±------+

或者用排序，但是排序的话有两个或多个最高分会不能全部找到
limit x,y (x:表示从X条数据开始 y:需要查出多少条)

select s_no, c_no, sc_degree from score order by sc_degree;

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 109 | 3-245 | 68 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 76 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
| 103 | 6-166 | 85 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 103 | 3-105 | 92 |
±-----±------±----------+

select s_no, c_no, sc_degree from score order by sc_degree limit 0, 1;

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 109 | 3-245 | 68 |
±-----±------±----------+
select s_no, c_no, sc_degree from score order by sc_degree limit 0, 2;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 109 | 3-245 | 68 |
| 105 | 3-245 | 75 |
±-----±------±----------+

11.查询每门课的平均成绩
group by //按…进行分组

select * from course;
±------±-----------±-----+
| c_no | c_name | t_no |
±------±-----------±-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±-----+

select avg(sc_degree) from score where c_no=‘3-105’; //这是算一条特定的

±---------------+
| avg(sc_degree) |
±---------------+
| 85.3333 |
±---------------+

select avg(sc_degree) from score group by c_no; //一起算

±---------------+
| avg(sc_degree) |
±---------------+
| 85.3333 |
| 76.3333 |
| 81.6667 |
±---------------+

select c_no, avg(sc_degree) from score group by c_no; //带标号的

±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±---------------+

12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
like //模糊查询
'3% //以3开头,%是通配符，可以匹配任意的字符
select c_no from score group by c_no having count(c_no) >= 2;

±------+
| c_no |
±------+
| 3-105 |
| 3-245 |
| 6-166 |
±------+

select c_no from score group by c_no having count(c_no) >= 2 and c_no like ‘3%’;

±------+
| c_no |
±------+
| 3-105 |
| 3-245 |
±------+

select c_no, avg(sc_degree) from score group by c_no having count(c_no) >= 2 and c_no like ‘3%’;

±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
±------±---------------+

select c_no, avg(sc_degree), count(*) from score group by c_no having count(c_no) >= 2 and c_no like ‘3%’;

±------±---------------±---------+
| c_no | avg(sc_degree) | count(*) |
±------±---------------±---------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
±------±---------------±---------+

13.查询分数大于70但是小于90的s_no列:
select s_no, sc_degree from score where sc_degree > 70 and sc_degree < 90;

或者

select s_no, sc_degree from score where sc_degree between 70 and 90;

±-----±----------+
| s_no | sc_degree |
±-----±----------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±-----±----------+
进阶:显示s_name,c_name
select s_name,sc_degree,c_name FROM score,student,course WHERE score.s_no = student.s_no AND score.c_no = course.c_no AND sc_degree BETWEEN 71 AND 89;
±-------±----------±-----------+
| s_name | sc_degree | c_name |
±-------±----------±-----------+
| 王丽 | 86 | 操作系统 |
| 王丽 | 85 | 数字电路 |
| 王芳 | 88 | 计算机导论 |
| 王芳 | 75 | 操作系统 |
| 王芳 | 79 | 数字电路 |
| 赵铁柱 | 76 | 计算机导论 |
| 赵铁柱 | 81 | 数字电路 |
±-------±----------±-----------+

14.查询所有的学生 s_name , c_no, sc_degree列
多表查询

select s_no, c_no, sc_degree from score;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+

select s_no, s_name from student;
±-----±-------+
| s_no | s_name |
±-----±-------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆军 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
±-----±-------+

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

±-------±------±----------+
| s_name | c_no | sc_degree |
±-------±------±----------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
±-------±------±----------+

15.查询所有学生的s_no, c_name, sc_degree列
多表查询

select c_no, c_name from course;
±------±-----------+
| c_no | c_name |
±------±-----------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
±------±-----------+

select c_no, s_no, sc_degree from score;
±------±-----±----------+
| c_no | s_no | sc_degree |
±------±-----±----------+
| 3-105 | 103 | 92 |
| 3-245 | 103 | 86 |
| 6-166 | 103 | 85 |
| 3-105 | 105 | 88 |
| 3-245 | 105 | 75 |
| 6-166 | 105 | 79 |
| 3-105 | 109 | 76 |
| 3-245 | 109 | 68 |
| 6-166 | 109 | 81 |
±------±-----±----------+

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

±-----±-----------±----------+
| s_no | c_name | sc_degree |
±-----±-----------±----------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
±-----±-----------±----------+

16.查询所有的学生 s_name , c_name, sc_degree列
多表查询
select s_name, c_name, sc_degree from student, course, score where student.s_no = score.s_no and course.c_no = score.c_no;

±-------±-----------±----------+
| s_name | c_name | sc_degree |
±-------±-----------±----------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
±-------±-----------±----------+

select s_name, c_name, sc_degree, student.s_no, course.c_no from student, course, score where student.s_no = score.s_no and course.c_no = score.c_no;
//因为多张表都含有s_no和c_no，所以在使用时需要明确哪张表的，否则就会报错

±-------±-----------±----------±-----±------+
| s_name | c_name | sc_degree | s_no | c_no |
±-------±-----------±----------±-----±------+
| 王丽 | 计算机导论 | 92 | 103 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 6-166 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 3-105 |
| 赵铁柱 | 操作系统 | 68 | 109 | 3-245 |
| 赵铁柱 | 数字电路 | 81 | 109 | 6-166 |
±-------±-----------±----------±-----±------+

select s_name, c_name, sc_degree, student.s_no as stu_sno, course.c_no as cou_cno from student, course, score where student.s_no = score.s_no and course.c_no = score.c_no;
//取别名student.s_no as stu_sno, course.c_no as cou_cno

±-------±-----------±----------±--------±--------+
| s_name | c_name | sc_degree | stu_sno | cou_cno |
±-------±-----------±----------±--------±--------+
| 王丽 | 计算机导论 | 92 | 103 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 6-166 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 3-105 |
| 赵铁柱 | 操作系统 | 68 | 109 | 3-245 |
| 赵铁柱 | 数字电路 | 81 | 109 | 6-166 |
±-------±-----------±----------±--------±--------+

select s_name, c_name, sc_degree, student.s_no as stu_sno, score.s_no, course.c_no as cou_cno, score.c_no from student, course, score where student.s_no = score.s_no and course.c_no = score.c_no;
//可以看出他们之间的联系就是共同字段之间相等来联系

±-------±-----------±----------±--------±-----±--------±------+
| s_name | c_name | sc_degree | stu_sno | s_no | cou_cno | c_no |
±-------±-----------±----------±--------±-----±--------±------+
| 王丽 | 计算机导论 | 92 | 103 | 103 | 3-105 | 3-105 |
| 王丽 | 操作系统 | 86 | 103 | 103 | 3-245 | 3-245 |
| 王丽 | 数字电路 | 85 | 103 | 103 | 6-166 | 6-166 |
| 王芳 | 计算机导论 | 88 | 105 | 105 | 3-105 | 3-105 |
| 王芳 | 操作系统 | 75 | 105 | 105 | 3-245 | 3-245 |
| 王芳 | 数字电路 | 79 | 105 | 105 | 6-166 | 6-166 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 109 | 3-105 | 3-105 |
| 赵铁柱 | 操作系统 | 68 | 109 | 109 | 3-245 | 3-245 |
| 赵铁柱 | 数字电路 | 81 | 109 | 109 | 6-166 | 6-166 |
±-------±-----------±----------±--------±-----±--------±------+

17.查询班级是’95031’班学生每门课的平均分

select * from student where s_class=‘95031’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

select s_no from student where s_class=‘95031’;

//用于下面复合语句

±-----+
| s_no |
±-----+
| 102 |
| 105 |
| 106 |
| 108 |
| 109 |
±-----+

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

//复合语句，其中有学生没有成绩就没显示出来

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+

select c_no, avg(sc_degree) from score where s_no in ( select s_no from student where s_class=‘95031’) group by c_no;

//group by c_no根据它来分组

±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
±------±---------------+

进阶,加入课程名称:

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 ;

±------±-----------±------------------+
| c_no | c_name | AVG(sc.sc_degree) |
±------±-----------±------------------+
| 3-105 | 计算机导论 | 82.0000 |
| 3-245 | 操作系统 | 71.5000 |
| 6-166 | 数字电路 | 80.0000 |
±------±-----------±------------------+

18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录

select sc_degree from score where s_no=‘109’ and c_no=‘3-105’;
±----------+
| sc_degree |
±----------+
| 76 |
±----------+

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

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
±-----±------±----------+

19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录
select * from score where sc_degree > (select sc_degree from score where s_no=‘109’ and c_no=‘3-105’);

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±-----±------±----------+

SELECT * FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’) AND s.s_no = sc.s_no;

±-----±-------±------±--------------------±--------±-----±------±----------+
| s_no | s_name | s_sex | s_birthday | s_class | s_no | c_no | sc_degree |
±-----±-------±------±--------------------±--------±-----±------±----------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-245 | 86 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 6-166 | 85 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 6-166 | 79 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | 109 | 6-166 | 81 |
±-----±-------±------±--------------------±--------±-----±------±----------+

SELECT s.s_no AS’学生学号’, s.s_name AS’学生姓名’, s_sex AS’性别’, s_class AS’班级’, c.c_no AS’课程编号’, c.c_name AS’课程名称’ ,sc.sc_degree AS’分数’ FROM student AS s, score AS sc ,course AS c WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’) AND s.s_no = sc.s_no AND sc.c_no = c.c_no;

±---------±---------±-----±------±---------±-----------±-----+
| 学生学号 | 学生姓名 | 性别 | 班级 | 课程编号 | 课程名称 | 分数 |
±---------±---------±-----±------±---------±-----------±-----+
| 103 | 王丽 | 女 | 95033 | 3-105 | 计算机导论 | 92 |
| 103 | 王丽 | 女 | 95033 | 3-245 | 操作系统 | 86 |
| 103 | 王丽 | 女 | 95033 | 6-166 | 数字电路 | 85 |
| 105 | 王芳 | 女 | 95031 | 3-105 | 计算机导论 | 88 |
| 105 | 王芳 | 女 | 95031 | 6-166 | 数字电路 | 79 |
| 109 | 赵铁柱 | 男 | 95031 | 6-166 | 数字电路 | 81 |
±---------±---------±-----±------±---------±-----------±-----+

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

select * from student where s_no in (108, 101);
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

select year(s_birthday) from student where s_no in (108, 101);
±-----------------+
| year(s_birthday) |
±-----------------+
| 1977 |
| 1975 |
±-----------------+

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

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

21.查询 张旭 教师任课的学生的成绩

select * from teacher where t_name = ‘张旭’;
±-----±-------±------±--------------------±------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±------±-----------+
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±------±-----------+

select t_no from teacher where t_name = ‘张旭’;
±-----+
| t_no |
±-----+
| 856 |
±-----+

select * from course where t_no = (select t_no from teacher where t_name = ‘张旭’);
±------±---------±-----+
| c_no | c_name | t_no |
±------±---------±-----+
| 6-166 | 数字电路 | 856 |
±------±---------±-----+

select c_no from course where t_no = (select t_no from teacher where t_name = ‘张旭’);
±------+
| c_no |
±------+
| 6-166 |
±------+

select * from score where c_no=(select c_no from course where t_no = (select t_no from teacher where t_name = ‘张旭’));

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±-----±------±----------+

22.查询选修课程的同学人数多余 5 人的教师姓名

为了效果,添加数据:

INSERT INTO score VALUES(‘101’,‘3-105’,‘90’);

INSERT INTO score VALUES(‘102’,‘3-105’,‘91’);

INSERT INTO score VALUES(‘104’,‘3-105’,‘89’);

select c_no from score group by c_no;
±------+
| c_no |
±------+
| 3-105 |
| 3-245 |
| 6-166 |
±------+

select c_no from score group by c_no having count(*) > 5;
±------+
| c_no |
±------+
| 3-105 |
±------+

select * from teacher;
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

select * from course;
±------±-----------±-----+
| c_no | c_name | t_no |
±------±-----------±-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±-----+

select t_no from course where c_no = (select c_no from score group by c_no having count(*) > 5);
±-----+
| t_no |
±-----+
| 825 |
±-----+

select t_name from teacher where t_no = ( select t_no from course where c_no = (select c_no from score group by c_no having count(*) > 5));

±-------+
| t_name |
±-------+
| 王萍 |
±-------+

注意:视频中用 ‘=’ 是不严谨的,实际中你根本不知道有多少条件是符合的,要用IN
SELECT t_name FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(s_no) > 5));

±-------+
| t_name |
±-------+
| 王萍 |
±-------+

23.查询95033班和95031班全体学生的记录

由于视频中就只有这两个班,所以要插入数据:

INSERT INTO student VALUES(‘110’,‘张飞’,‘男’,‘1974-06-03’,‘95038’);

select * from student;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±-----±-------±------±--------------------±--------+

select * from student where s_class in (‘95031’, ‘95033’);

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

24.查询存在85分以上成绩的课程c_no

select * from score;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+

select c_no from score where sc_degree > 85;

±------+
| c_no |
±------+
| 3-105 |
| 3-105 |
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
±------+

25.查出所有’计算机系’ 教师所教课程的成绩表

select * from teacher where t_depart = ‘计算机系’;
±-----±-------±------±--------------------±-------±---------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±---------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
±-----±-------±------±--------------------±-------±---------+

select * from course where t_no in ( select t_no from teacher where t_depart = ‘计算机系’);
±------±-----------±-----+
| c_no | c_name | t_no |
±------±-----------±-----+
| 3-245 | 操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
±------±-----------±-----+

select * from score where c_no in (select c_no from course where t_no in ( select t_no from teacher where t_depart = ‘计算机系’));

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+

26.查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof

select * from teacher;
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

//所以按照题意只是找副教授和讲师，找计算机系中有但是电子工程系没有的教师的name和rof

select t_rof from teacher where t_depart = ‘电子工程系’;
±------+
| t_rof |
±------+
| 助教 |
| 讲师 |
±------+

select * from teacher where t_depart = ‘计算机系’ and t_rof not in (select t_rof from teacher where t_depart = ‘电子工程系’);
±-----±-------±------±--------------------±-------±---------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±---------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
±-----±-------±------±--------------------±-------±---------+

select * from teacher where t_depart = ‘电子工程系’ and t_rof not in (select t_rof from teacher where t_depart = ‘计算机系’);
±-----±-------±------±--------------------±------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±------±-----------+
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±------±-----------+

用union求并集
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 = ‘计算机系’);

±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序

select * from score where c_no = ‘3-245’;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
±-----±------±----------+

select * from score where c_no = ‘3-105’;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+

至少，说明大于其中至少一个，用any语法

select * from score where c_no = ‘3-105’ and sc_degree > any(select sc_degree from score where c_no = ‘3-245’);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+

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;

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+

28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
且，说明的是所有，用all语法
select * from score where c_no = ‘3-105’ and sc_degree > all(select sc_degree from score where c_no = ‘3-245’);

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±-----±------±----------+

29. 查询所有教师和同学的 name ,sex, birthday
select t_name, t_sex, t_birthday from teacher union select s_name, s_sex, s_birthday from student;
用union

±-------±------±--------------------+
| t_name | t_sex | t_birthday |
±-------±------±--------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆军 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 男 | 1975-02-10 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
±-------±------±--------------------+

select t_name as name, t_sex as sex, t_birthday as birthday from teacher union select s_name, s_sex, s_birthday from student;
别名，用as，第二排默认和第一排使用一样的名字

±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆军 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 男 | 1975-02-10 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
±-------±----±--------------------+

30.查询所有’女’教师和’女’学生的name,sex,birthday
select t_name as name, t_sex as sex, t_birthday as birthday from teacher where t_sex = ‘女’ union select s_name, s_sex, s_birthday from student where s_sex = ‘女’;

±-----±----±--------------------+
| name | sex | birthday |
±-----±----±--------------------+
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
±-----±----±--------------------+

31.查询成绩比该课程平均成绩低的同学的成绩表
select c_no, avg(sc_degree) from score group by c_no; //每门课程的平均成绩

±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±---------------+

select * from score;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+

select * from score a where sc_degree < (select avg(sc_degree) from score b where a.c_no = b.c_no);

//相当于复制了一个表，在两个表中进行操作，第一个表取别名叫做a，第二个表叫做b

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+

32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)

//可能有的教师在但是没有任课的情况，所以需要根据课程表进行查看一下

select * from teacher;
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

select * from course;
±------±-----------±-----+
| c_no | c_name | t_no |
±------±-----------±-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±-----+

select * from teacher where t_no in (select t_no from course);
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

select t_name, t_depart from teacher where t_no in (select t_no from course);

±-------±-----------+
| t_name | t_depart |
±-------±-----------+
| 李诚 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
±-------±-----------+

33.查出至少有2名男生的班号

select * from student;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±-----±-------±------±--------------------±--------+

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

±--------+
| s_class |
±--------+
| 95033 |
| 95031 |
±--------+

34.查询student 表中 不姓"王"的同学的记录
select * from student where s_name not like ‘王%’;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±-----±-------±------±--------------------±--------+

35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
//年龄=当前年份-出生年份
select year(now());

±------------+
| year(now()) |
±------------+
| 2021 |
±------------+

select year(s_birthday) from student;

±-----------------+
| year(s_birthday) |
±-----------------+
| 1977 |
| 1975 |
| 1976 |
| 1976 |
| 1975 |
| 1974 |
| 1976 |
| 1975 |
| 1974 |
| 1974 |
±-----------------+

select s_name, year(now()) - year(s_birthday) as ‘年龄’ from student;

±-------±-----+
| s_name | 年龄 |
±-------±-----+
| 曾华 | 44 |
| 匡明 | 46 |
| 王丽 | 45 |
| 李军 | 45 |
| 王芳 | 46 |
| 陆军 | 47 |
| 王尼玛 | 45 |
| 张全蛋 | 46 |
| 赵铁柱 | 47 |
| 张飞 | 47 |
±-------±-----+

select s_name, year(now()) - year(s_birthday) as ‘年龄’ from student;
±-------±-----+
| s_name | 年龄 |
±-------±-----+
| 曾华 | 44 |
| 匡明 | 46 |
| 王丽 | 45 |
| 李军 | 45 |
| 王芳 | 46 |
| 陆军 | 47 |
| 王尼玛 | 45 |
| 张全蛋 | 46 |
| 赵铁柱 | 47 |
| 张飞 | 47 |
±-------±-----+

36.查询student中最大和最小的 s_birthday的值

//max min

select s_birthday from student order by s_birthday;
±--------------------+
| s_birthday |
±--------------------+
| 1974-06-03 00:00:00 |
| 1974-06-03 00:00:00 |
| 1974-06-03 00:00:00 |
| 1975-02-10 00:00:00 |
| 1975-02-10 00:00:00 |
| 1975-10-02 00:00:00 |
| 1976-01-23 00:00:00 |
| 1976-02-20 00:00:00 |
| 1976-02-20 00:00:00 |
| 1977-09-01 00:00:00 |
±--------------------+

select max(s_birthday) as ‘最大’, min(s_birthday) as ‘最小’ from student;

//这个最大和最小是按照年份的大小值排的

±--------------------±--------------------+
| 最大 | 最小 |
±--------------------±--------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
±--------------------±--------------------+

37.以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by s_class desc, s_birthday;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+

38.查询"男"教师 及其所上的课

select * from teacher where t_sex = ‘男’;
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+

select * from course where t_no in (select t_no from teacher where t_sex = ‘男’);

±------±---------±-----+
| c_no | c_name | t_no |
±------±---------±-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
±------±---------±-----+

进阶:查出教师名称,教师性别, 课程名字

SELECT t.t_name,t.t_sex ,c.c_name FROM teacher t ,course c WHERE t_sex = ‘男’ AND t.t_no = c.t_no;

±-------±------±---------+

| t_name | t_sex | c_name |+

--------±------±---------+|

李诚 | 男 | 操作系统 ||

张旭 | 男 | 数字电路 |

±-------±------±---------+

39.查询最高分同学的s_no c_no 和 sc_degree;

select max(sc_degree) from score;
±---------------+
| max(sc_degree) |
±---------------+
| 92 |
±---------------+

select * from score where sc_degree = (select max(sc_degree) from score);

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
±-----±------±----------+

41.查询和"李军"同性别的所有同学的s_name

select s_sex from student where s_name = ‘李军’;
±------+
| s_sex |
±------+
| 男 |
±------+

select s_name from student where s_sex = ( select s_sex from student where s_name = ‘李军’);

±-------+
| s_name |
±-------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆军 |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
| 张飞 |
±-------+

41.查询和"李军"同性别并且同班的所有同学的s_name
select s_name from student where s_sex = (select s_sex from student where s_name = ‘李军’) and s_class = (select s_class from student where s_name = ‘李军’);

±-------+
| s_name |
±-------+
| 曾华 |
| 李军 |
| 王尼玛 |
±-------+

42.查询所有选修’计算机导论’课程的’男’同学的成绩表
select * from student where s_sex = ‘男’;

±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±-----±-------±------±--------------------±--------+

select * from course where c_name = ‘计算机导论’;
±------±-----------±-----+
| c_no | c_name | t_no |
±------±-----------±-----+
| 3-105 | 计算机导论 | 825 |
±------±-----------±-----+

select * from score where c_no = (select c_no from course where c_name = ‘计算机导论’) and s_no in (select s_no from student where s_sex = ‘男’);

±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
±-----±------±----------+

43.假设使用了以下命令建立了一个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’);

select s_no, c_no, grade from score, grade where sc_degree between low and upp;
±-----±------±------+
| s_no | c_no | grade |
±-----±------±------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | c |
| 105 | 6-166 | c |
| 109 | 3-105 | c |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
±-----±------±------+

查询所有同学的s_no , c_no 和grade列

select * from grade;
±-----±-----±------+
| low | upp | grade |
±-----±-----±------+
| 90 | 100 | A |
| 80 | 89 | B |
| 70 | 79 | c |
| 60 | 69 | D |
| 0 | 59 | E |
±-----±-----±------+

select s_no, c_no, grade from score, grade where sc_degree between low and upp;
±-----±------±------+
| s_no | c_no | grade |
±-----±------±------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | c |
| 105 | 6-166 | c |
| 109 | 3-105 | c |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
±-----±------±------+

进阶:显示学生名字和课程名称

SELECT s.s_no, s.s_name, c.c_name ,c.c_no , grade FROM student s, course c ,score sc, grade WHERE sc_degree BETWEEN low and upp AND sc.s_no = s.s_no AND sc.c_no = c.c_no;
±-----±-------±-----------±------±------+
| s_no | s_name | c_name | c_no | grade |
±-----±-------±-----------±------±------+
| 101 | 曾华 | 计算机导论 | 3-105 | A |
| 102 | 匡明 | 计算机导论 | 3-105 | A |
| 103 | 王丽 | 计算机导论 | 3-105 | A |
| 103 | 王丽 | 操作系统 | 3-245 | B |
| 103 | 王丽 | 数字电路 | 6-166 | B |
| 104 | 李军 | 计算机导论 | 3-105 | B |
| 105 | 王芳 | 计算机导论 | 3-105 | B |
| 105 | 王芳 | 操作系统 | 3-245 | c |
| 105 | 王芳 | 数字电路 | 6-166 | c |
| 109 | 赵铁柱 | 计算机导论 | 3-105 | c |
| 109 | 赵铁柱 | 操作系统 | 3-245 | D |
| 109 | 赵铁柱 | 数字电路 | 6-166 | B |
±-----±-------±-----------±------±------+

煜成'Studio
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库MySQL-模糊查询
12-14
1.7 模糊查询 1.7.1 通配符 _ [下划线] 表示任意一个字符 % 表示任意字符 练习 1、满足“T_m”的有（A、C） A：Tom B：Toom C：Tam D：Tm E：Tmo 2、满足“T_m_”的有（ B C） A:Tmom B:Tmmm C:T1m2 D:Tmm E:Tm 3、...
MySql数据库基础查询语句练习大全一（练习完，你就完全掌握了基本的数据库查询）
m0_37510446的博客
05-17 1253
1首先给大家看看我们这次进行练习的数据库表 user表 id name score gender 1 小李 100 男 2 小易 89 男 3 小峰 80 男 4 小花 60 女 5 小宇 99 女 6 小明 10 男 user_A表 id name age heigth weight 1 小李 23 178 112
mysql 统计_mysql数据统计技巧备忘录
weixin_39603598的博客
11-26 214
　　mysql 作为常用数据库，操作贼六是必须的，对于数字操作相关的东西，那是相当方便，本节就来拎几个统计案例出来供参考！order订单表,样例如下：CREATE TABLE `t_order` (　　`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,　　`user_id` int(11) NOT NULL,　　`order_nid` va...
【工作记录】mysql中实现分组统计的三种方式
泽济天下的专栏
08-10 3412
本文针对分组区间统计提出了mysql的三种sql写法。
mysql统计－关于学生成绩
热门推荐
mayanyun2013的专栏
03-10 2万+
转载地址http://blog.csdn.net/suntanyong88/article/details/7683089 问题： 1.   计算每个人的总成绩并排名(要求显示字段：姓名，总成绩) 2.   计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩) 3.   计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩) 4.   计算每
mysql 统计金额排序_MySQL 对某个字段先统计后获取排序名次
weixin_39645249的博客
01-21 1166
一，普通获取排序名次比如获取一个班级成绩排名，分两步(1)查出所有用户和他们的成绩排名select id,maxScore,(@rowNum:[emailprotected]+1) as rowNofrom t_user,(select (@rowNum :=0) ) border by t_user.maxScore desc(2)查出某个用户在所有用户成绩中的排名select u.rowNo...
mysql查询练习题
12-11
一份关于mysql的题目，查询相关的练习题，一表，两表，多表等的连接查询。
Java连接MySql数据库-并且java练习代码编程资料实现插入、删除、更新、选择操作编程资料
03-24
Java连接MySql数据库java练习代码-并且实现插入、删除、更新、选择操作
mysql数据库学习与练习
最新发布
03-15
mysql数据库
mysql数据库小练习
07-18
考察知识点 - 约束作用 - 约束分类 - <null>l的含义 - is null - is not null
数据库查询语句的练习
12-26
包括学生表，成绩表，科目表，老师表。练习查询语句的书写，可以练习内连接，自连接，排序，分组等查询的常用操作。
SQL-ORDER BY 多字段排序(升序、降序)
09-10
本节主要介绍了SQL-ORDER BY 多字段排序包括升序、降序，下面有个示例，大家可以参考下
MySQL - Failed to open the referenced table XXX
A_bad_horse的专栏
03-26 1万+
问题复现 CREATE TABLE `master_role` ( `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID', `role` VARCHAR(50) CHARACTER SET latin1 NOT NULL COMMENT 'Role', `active` TINYINT NOT NULL COMMENT 'Active', PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET
Failed to open the referenced table ‘xxx‘(数据库建立外键时引发的错误）
学习笔记
11-18 2424
外键约束
sqlyog备份数据库后导入报错Failed to open the referenced table ‘xxx‘
Healer1996的博客
02-01 1004
sqlyog备份数据库后导入报错Failed to open the referenced table 'xxx'
MySQL数据库 - 复杂查询（二）第一关
qq_44745905的博客
08-24 5224
@R星校长 第1关：查询学生平均分 本关任务：根据提供的表和数据，查询平均成绩小于60分的同学的学生编号（s_id）、学生姓名（s_name）和平均成绩（avg_score），要求平均成绩保留2位小数点。（注意：包括有成绩的和无成绩的） student表数据： s_id s_name s_sex 01 Mia 女 02 Riley 男 03 Aria 女 04 Lucas 女 05 Oliver 男 06 Caden 男 07 Lily 女 08 Jacob 男.
Fail to open the referenced table
chillaxy的博客
04-14 9532
问题场景：给数据库的表添加外键时报错 Fail to open the referenced table 解决办法：,将ENGINE修改为INNODB即可
mysql常见错误处理
u011608531的专栏
06-21 2501
mysql常见错误处理1、mysql启动错误处理2、Mysql Incorrect datetime value3、ERROR : (2006, 'MySQL server has gone away’)4、 [ERR] 1824 - Failed to open the referenced table ‘User5、2013 - Lost connection to MySQL server at 'reading initial communication packet', system error:
Mysql 创建数据表外键报错：Fail to open the referenced table
大大大大大碗面
03-03 2万+
在mysql中，建立外键关联，关联表必须是使用innodb,将engine修改为innodb即可。 存储引擎是什么？ MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。 关于数据库其它几个引擎的区别如下： ...
mysql查询练习数据库
09-28
MySQL查询练习数据库是一个用于练习和学习SQL查询语句的数据库。它通常包含了多个数据表和示例数据，以帮助用户熟悉和掌握MySQL查询语言。 在这个数据库中，用户可以通过编写不同的SQL查询语句来对数据表进行查询...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值