SQL语句编写的练习(MySQL)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 这篇文章提供了MySQL数据库中关于学生表、课程表、成绩表和教师表的建表语句、数据插入示例以及一系列SQL查询练习,包括查询、排序、聚合和连接查询等操作。

SQL语句编写的练习(MySQL)

一、建表

1、学生表(Student)

学号 | 姓名 | 性别 | 出生年月 | 所在班级

create table Student(
   sno varchar(20) primary key,  
   sname varchar(20) not null,
   ssex varchar(10) not null,
   sbirthday datetime,
  class varchar(20)
);

2、课程表 (Course)

课程号 | 课程名称 | 教师编号

create table Course(
  cno varchar(20) primary key,
  cname varchar(20) not null,
  tno varchar(20) not null,
  foreign key(tno) references Teacher(tno)
);

3、成绩表(Score)

学号 | 课程号 | 成绩

create table Score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);

4、教师表(Teacher)

教师编号 | 教师名称 | 教师性别 | 教师出生年月日 | 职称 | 所在部门

create table Teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);

二、添加数据

1、添加学生信息

insert into student values('108','小红','女','1997-02-23','95033');

insert into student values('107','小黑','男','1597-04-23','95034');

insert into student values('103','张三','女','1999-03-23','95036');

insert into student values('106','李四','男','2000-02-23','95035');

insert into student values('104','李白','男','1897-12-23','95034');

insert into student values('105','李芳','女','1997-02-23','95032');

insert into student values('101','大明','男','1597-04-23','95035');

insert into student values('102','许仙','女','1499-03-23','95033');

insert into student values('109','小芳','男','2010-02-23','95037');

2、添加教师信息

insert into teacher values('804','李成','男','1984-12-03','副教授','计算机系');
insert into teacher values('856','张旭','男','1974-11-03','教授','电子工程系');
insert into teacher values('825','王萍','女','1984-12-03','助教','计算机系');
insert into teacher values('831','刘冰','女','1994-12-03','讲师','电子工程系');

3、添加课程表信息

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

4、添加成绩表信息

insert into score values('108','3-245','87');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','89');
insert into score values('105','3-105','88');
insert into score values('109','3-105','65');
insert into score values('103','3-105','64');
insert into score values('105','3-105','94');
insert into score values('109','6-166','87');
insert into score values('103','6-166','67');
insert into score values('105','6-166','98');
insert into score values('109','9-888','87');

三、查询练习

1、查询student表的所有记录

mysql> select * from student;

±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 许仙 | 女 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.01 sec)

2、查询student表中的所有记录的sname、ssex\class

select sname,ssex,class from student;

mysql> select sname,ssex,class from student;
±-------±-----±------+
| sname | ssex | class |
±-------±-----±------+
| 大明 | 男 | 95035 |
| 许仙 | 女 | 95033 |
| 张三 | 女 | 95036 |
| 李白 | 男 | 95034 |
| 李芳 | 女 | 95032 |
| 李四 | 男 | 95035 |
| 小黑 | 男 | 95034 |
| 小红 | 女 | 95033 |
| 小芳 | 男 | 95037 |
±-------±-----±------+
9 rows in set (0.00 sec)

3、查询教师所有的单位不重复的depart列

distinct排除重复

select distinct depart from teacher;

mysql> select distinct depart from teacher;
±----------------+
| depart |
±----------------+
| 计算机系 |
| 电子工程系 |
±----------------+
2 rows in set (0.10 sec)

4、查询score表中成绩在60到80的所有的记录

查询区间 between …and…

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

mysql> select * from score where degree>60 and degree<80;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.34 sec)

select * from score where degree between 60 and 80;

mysql> select * from score where degree between 60 and 80;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.01 sec)

5、查询score表中成绩为85,86或88的记录。

表或者关系的查询 in

select * from score where degree in(85,86,88);

mysql> select * from score where degree in(85,86,88);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-105 | 88 |
±----±------±-------+
1 row in set (0.35 sec)

6、查询student表中95031班或者性别为女的学生记录

select * from student where class='95033' or ssex='女';

mysql> select * from student where class=‘95033’ or ssex=‘女’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
±----±-------±-----±--------------------±------+
4 rows in set (0.00 sec)

7、以class降序查询student表的所有记录

升序 降序

select * from student order by class desc;

mysql> select * from student order by class desc;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
±----±-------±-----±--------------------±------+
9 rows in set (0.01 sec)

–升序 asc moren
mysql> select * from student order by sno asc;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)

8、以cno升序,degree降序查询score表的所有记录

select * from score order by cno ,degree desc;

mysql> select * from score order by cno ,degree desc;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
| 108 | 3-245 | 87 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 105 | 6-166 | 98 |
| 109 | 6-166 | 87 |
| 103 | 6-166 | 67 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 rows in set (0.00 sec)

9、查询 9501班的学生人数

统计count

select count(*) from student where class='95033';

mysql> select count() from student where class=‘95033’;
±---------+
| count(
) |
±---------+
| 2 |
±---------+
1 row in set (0.01 sec)

10、查询score表中的最高分的学生学号和课程号(子查询或者排序)

select sno,cno from score where degree=(select max(degree) from score);

–1、找到最高分
select max(degree) from score
–2、根据最高分查询学号和课程号

mysql> select sno,cno from score where degree=(select max(degree) from score);
±----±------+
| sno | cno |
±----±------+
| 105 | 6-166 |
±----±------+
1 row in set (0.01 sec)

select sno,cno ,degree from score where degree=(select max(degree) from score);

mysql> select sno,cno ,degree from score where degree=(select max(degree) from score);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)

–根据分数最少值查询学号和课程号
select sno,cno ,degree from score where degree=(select min(degree) from score);

select  sno,cno ,degree from score order by degree desc limit 0,1;

–limit 第一个数字表示从多少开始
–第二个数字代表查询几条数据

mysql> select sno,cno ,degree from score order by degree desc limit 0,1;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)

mysql> select * from score;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 rows in set (0.00 sec)

11、计算每门课的平均成绩

–avg

select avg(degree) from score where cno='3-105';

mysql> select avg(degree) from score where cno=‘3-105’;
±------------+
| avg(degree) |
±------------+
| 80.6667 |
±------------+
1 row in set (0.00 sec)

mysql>

select cno,avg(degree) from score group by cno;
–group by 分组
mysql> select cno,avg(degree) from score group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 80.6667 |
| 3-245 | 76.6667 |
| 6-166 | 84.0000 |
| 9-888 | 87.0000 |
±------±------------+
4 rows in set (0.00 sec)

12、查询score表中至少有两名同学选修的并且以3开头的课程的平均分

select cno,avg(degree) ,count(*) from score
 group by cno
 having count(cno)>=2 and 
cno like '3%';

–模糊查询like

mysql> select cno,avg(degree) ,count() from score group by cno having count(cno)>=2 and cno like ‘3%’;
±------±------------±---------+
| cno | avg(degree) | count(
) |
±------±------------±---------+
| 3-105 | 80.6667 | 3 |
| 3-245 | 76.6667 | 3 |
±------±------------±---------+
2 rows in set (0.00 sec)

mysql>

13、查询分数大于70小于90的sno列

select sno from score where degree between 70 and 90;

mysql> select sno from score where degree between 70 and 90;
±----+
| sno |
±----+
| 103 |
| 105 |
| 105 |
| 108 |
| 109 |
| 109 |
±----+
6 rows in set (0.00 sec)

select sno,degree from score where degree between 70 and 90;

14、查询所有学生的sname、con、和degree列

select sno,sname from student;

mysql> select sno,sname from student;
±----±-------+
| sno | sname |
±----±-------+
| 101 | 大明 |
| 102 | 刘思 |
| 103 | 张三 |
| 104 | 李白 |
| 105 | 李芳 |
| 106 | 李四 |
| 107 | 小黑 |
| 108 | 小红 |
| 109 | 小芳 |
±----±-------+
9 rows in set (0.00 sec)

select sno,cno,degree from score;

mysql> select sno,cno,degree from score;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
10 rows in set (0.00 sec)

select sname,cno,degree from student,score where student.sno=score.sno;

mysql> select sname,cno,degree from student,score where student.sno=score.sno;
±-------±------±-------+
| sname | cno | degree |
±-------±------±-------+
| 张三 | 3-105 | 89 |
| 张三 | 6-166 | 67 |
| 李芳 | 3-105 | 88 |
| 李芳 | 3-245 | 75 |
| 李芳 | 6-166 | 98 |
| 小红 | 3-245 | 87 |
| 小芳 | 3-105 | 65 |
| 小芳 | 3-245 | 68 |
| 小芳 | 6-166 | 87 |
| 小芳 | 9-888 | 87 |
±-------±------±-------+
10 rows in set (0.00 sec)

15、查询所有学生的sno,cname和degree列

select sno,cname,degree from course,score where  score.cno=course.cno;

mysql> select sno,cname,degree from course,score where score.cno=course.cno;
±----±----------------±-------+
| sno | cname | degree |
±----±----------------±-------+
| 103 | 计算机导论 | 89 |
| 103 | 数字电路 | 67 |
| 105 | 计算机导论 | 88 |
| 105 | c操作系统 | 75 |
| 105 | 数字电路 | 98 |
| 108 | c操作系统 | 87 |
| 109 | 计算机导论 | 65 |
| 109 | c操作系统 | 68 |
| 109 | 数字电路 | 87 |
| 109 | 高等数学 | 87 |
±----±----------------±-------+
10 rows in set (0.34 sec)

16、查询所有学生的sname,cname和degree列

select sno,sname from student;

mysql> select sno,sname from student;
±----±-------+
| sno | sname |
±----±-------+
| 101 | 大明 |
| 102 | 刘思 |
| 103 | 张三 |
| 104 | 李白 |
| 105 | 李芳 |
| 106 | 李四 |
| 107 | 小黑 |
| 108 | 小红 |
| 109 | 小芳 |
±----±-------+
9 rows in set (0.00 sec)

select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
±-------±----------------±-------+
| sname | cname | degree |
±-------±----------------±-------+
| 张三 | 计算机导论 | 89 |
| 张三 | 数字电路 | 67 |
| 李芳 | 计算机导论 | 88 |
| 李芳 | c操作系统 | 75 |
| 李芳 | 数字电路 | 98 |
| 小红 | c操作系统 | 87 |
| 小芳 | 计算机导论 | 65 |
| 小芳 | c操作系统 | 68 |
| 小芳 | 数字电路 | 87 |
| 小芳 | 高等数学 | 87 |
±-------±----------------±-------+
10 rows in set (0.00 sec)

select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;

mysql> select sname,cname,degree,student.sno,course.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
±-------±----------------±-------±----±------+
| sname | cname | degree | sno | cno |
±-------±----------------±-------±----±------+
| 张三 | 计算机导论 | 89 | 103 | 3-105 |
| 张三 | 数字电路 | 67 | 103 | 6-166 |
| 李芳 | 计算机导论 | 88 | 105 | 3-105 |
| 李芳 | c操作系统 | 75 | 105 | 3-245 |
| 李芳 | 数字电路 | 98 | 105 | 6-166 |
| 小红 | c操作系统 | 87 | 108 | 3-245 |
| 小芳 | 计算机导论 | 65 | 109 | 3-105 |
| 小芳 | c操作系统 | 68 | 109 | 3-245 |
| 小芳 | 数字电路 | 87 | 109 | 6-166 |
| 小芳 | 高等数学 | 87 | 109 | 9-888 |
±-------±----------------±-------±----±------+
10 rows in set (0.00 sec)

17、 查询95031班学生每门课的平均分

mysql> select * from student where class=‘95037’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
1 row in set (0.00 sec)

select * from score where sno in(select sno from student where class=‘95037’);
mysql> select * from score where sno in(select sno from student where class=‘95037’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
4 rows in set (0.00 sec)

select cno,avg(degree)
from score
where sno in(select sno from student where class='95037')
group by cno;

mysql> select cno,avg(degree)
-> from score
-> where sno in(select sno from student where class=‘95037’)
-> group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 65.0000 |
| 3-245 | 68.0000 |
| 6-166 | 87.0000 |
| 9-888 | 87.0000 |
±------±------------+
4 rows in set (0.00 sec)

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

select degree from score where sno='109' and cno='3-105';

mysql> select degree from score where sno=‘109’ and cno=‘3-105’;
±-------+
| degree |
±-------+
| 65 |
±-------+
1 row in set (0.00 sec)

select * from score where cno=‘3-105’ and degree>(select degree from score where sno=‘109’ and cno=‘3-105’);

mysql> select * from score where cno=‘3-105’ and degree>(select degree from score where sno=‘109’ and cno=‘3-105’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)

19、查询成绩高于学号为109 、课程号为3-105的成绩的所有记录


select * from score where degree>(select degree from score where sno='109' and cno='3-105');

mysql> select * from score where degree>(select degree from score where sno=‘109’ and cno=‘3-105’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 103 | 6-166 | 67 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
9 rows in set (0.00 sec)

20、查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列

select year(sbirthday) from student where sno in(108,109);

mysql> select year(sbirthday) from student where sno in(108,109);
±----------------+
| year(sbirthday) |
±----------------+
| 1997 |
| 2010 |
±----------------+
2 rows in set (0.11 sec)

select * from student
where year(sbirthday)in(select year(sbirthday) from student where sno in(108,109));

mysql> select * from student
-> where year(sbirthday)in(select year(sbirthday) from student where sno in(108,109));
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
3 rows in set (0.00 sec)

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

 select * from teacher where tname='张旭';

mysql> select * from teacher where tname=‘张旭’;
±----±-------±-----±--------------------±-------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
1 row in set (0.00 sec)

mysql> select * from course where tno=(select tno from teacher where tname=‘张旭’ );
±------±-------------±----+
| cno | cname | tno |
±------±-------------±----+
| 6-166 | 数字电路 | 856 |
±------±-------------±----+
1 row in set (0.33 sec)

select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=‘张旭’ ));

mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname=‘张旭’ ));
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 6-166 | 98 |
| 109 | 6-166 | 87 |
±----±------±-------+
3 rows in set (0.00 sec)

22、查询选修某课程的同学人数多于2人的教师姓名

select * from score group by cno having count(cno)>2 ;

mysql> select * from score group by cno having count(cno)>2 ;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-245 | 75 |
| 103 | 6-166 | 67 |
±----±------±-------+
3 rows in set (0.00 sec)

select * from course where cno in(select cno from score group by cno having count(cno)>2);

mysql> select * from course where cno in(select cno from score group by cno having count(cno)>2);
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | c操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
±------±----------------±----+
3 rows in set (0.00 sec)

select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(cno)>2));

mysql> select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(cno)>2));
±-------+
| tname |
±-------+
| 李成 |
| 王萍 |
| 张旭 |
±-------+
3 rows in set (0.00 sec)

23、查询95033班和95035班全体学生的记录

select * from student
where class in(95033,95035);

mysql> select * from student
-> where class in(95033,95035);
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
±----±-------±-----±--------------------±------+
4 rows in set (0.00 sec)

24、查询存在有85分以上成绩的课程Cno

select * from score where degree>85;

mysql> select * from score where degree>85;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 98 |
| 108 | 3-245 | 87 |
| 109 | 6-166 | 87 |
| 109 | 9-888 | 87 |
±----±------±-------+
6 rows in set (0.00 sec)

25、查询出计算机系教师所教课程的成绩表

select * from teacher where depart='计算机系';

mysql> select * from teacher where depart=‘计算机系’;
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1984-12-03 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
2 rows in set (0.00 sec)

select * from course where tno in(select tno from teacher where depart=‘计算机系’);

mysql> select * from course where tno in(select tno from teacher where depart=‘计算机系’);
±------±----------------±----+
| cno | cname | tno |
±------±----------------±----+
| 3-245 | c操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
±------±----------------±----+
2 rows in set (0.00 sec)

select degree,cno from score where cno in(select cno from course where tno in(select tno from teacher where depart=‘计算机系’));

mysql> select degree,cno from score where cno in(select cno from course where tno in(select tno from teacher where depart=‘计算机系’));
±-------±------+
| degree | cno |
±-------±------+
| 75 | 3-245 |
| 87 | 3-245 |
| 68 | 3-245 |
| 89 | 3-105 |
| 88 | 3-105 |
| 65 | 3-105 |
±-------±------+
6 rows in set (0.00 sec)

26、查询计算机系和电子工程系不同职称的教师的tname和prof

–union求并集

select * from teacher where depart=‘计算机系’;

mysql> select * from teacher where depart=‘计算机系’;
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1984-12-03 00:00:00 | 助教 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
2 rows in set (0.00 sec)

select * from teacher where prof not in(select prof from teacher where depart='计算机系');

mysql> select * from teacher where prof not in(select prof from teacher where depart=‘计算机 系’);
±----±-------±-----±--------------------±-------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
±----±-------±-----±--------------------±-------±----------------+
1 row in set (0.00 sec)

select * from teacher where prof not in(select prof from teacher where depart=‘电子工程系’);

mysql> select * from teacher where prof not in(select prof from teacher where depart=‘电子工 程系’);
±----±-------±-----±--------------------±----------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±-------------+
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
±----±-------±-----±--------------------±----------±-------------+
1 row in set (0.00 sec)


select * from teacher where prof not in(select prof from teacher where depart='计算机系')
union
select * from teacher where prof not in(select prof from teacher where depart='电子工程系');

mysql> select * from teacher where prof not in(select prof from teacher where depart=‘计算机 系’)
-> union
-> select * from teacher where prof not in(select prof from teacher where depart=‘电子工 程系’);
±----±-------±-----±--------------------±----------±----------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±----------±----------------+
| 856 | 张旭 | 男 | 1974-11-03 00:00:00 | 教授 | 电子工程系 |
| 804 | 李成 | 男 | 1984-12-03 00:00:00 | 副教授 | 计算机系 |
±----±-------±-----±--------------------±----------±----------------+
2 rows in set (0.01 sec)

27、查询选修编号为3-105课程且成绩至少高于编号为3-245的同学的cno\sno和degree

并按照degree从高到低次序排序

select * from score where cno='3-105';

mysql> select * from score where cno=‘3-105’;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
±----±------±-------+
3 rows in set (0.00 sec)

select * from score where cno=‘3-245’;
mysql> select * from score where cno=‘3-245’;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 108 | 3-245 | 87 |
| 109 | 3-245 | 68 |
±----±------±-------+
3 rows in set (0.00 sec)

select * from score 
where cno='3-105' 
and degree >any(select degree from score where cno='3-245')
order by degree desc;

mysql> select * from score
-> where cno=‘3-105’
-> and degree >any(select degree from score where cno=‘3-245’)
-> order by degree desc;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)

28、查询选修编号为3-105课程且成绩高于编号为3-245的同学的cno\sno和degree


select * from score 
where cno='3-105' 
and degree >all(select degree from score where cno='3-245')
order by degree desc;

mysql> select * from score
-> where cno=‘3-105’
-> and degree >all(select degree from score where cno=‘3-245’)
-> order by degree desc;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±----±------±-------+
2 rows in set (0.00 sec)

29、查询所有教师和同学的name、sex和birthday

别名 as

select sname as name,ssex as sex,sbirthday as birthday from student 
union
select tname,tsex,tbirthday from teacher;

mysql> select sname as name,ssex as sex,sbirthday as birthday from student
-> union
-> select tname,tsex,tbirthday from teacher;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 大明 | 男 | 1597-04-23 00:00:00 |
| 刘思 | 男 | 1499-03-23 00:00:00 |
| 张三 | 女 | 1999-03-23 00:00:00 |
| 李白 | 男 | 1897-12-23 00:00:00 |
| 李芳 | 女 | 1997-02-23 00:00:00 |
| 李四 | 男 | 2000-02-23 00:00:00 |
| 小黑 | 男 | 1597-04-23 00:00:00 |
| 小红 | 女 | 1997-02-23 00:00:00 |
| 小芳 | 男 | 2010-02-23 00:00:00 |
| 李成 | 男 | 1984-12-03 00:00:00 |
| 王萍 | 女 | 1984-12-03 00:00:00 |
| 刘冰 | 女 | 1994-12-03 00:00:00 |
| 张旭 | 男 | 1974-11-03 00:00:00 |
±-------±----±--------------------+
13 rows in set (0.00 sec)

30、查询所有女教师和女同学的name、sex、birthday

select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女';

mysql> select sname as name,ssex as sex,sbirthday as birthday from student where ssex=‘女’
-> union
-> select tname,tsex,tbirthday from teacher where tsex=‘女’;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 张三 | 女 | 1999-03-23 00:00:00 |
| 李芳 | 女 | 1997-02-23 00:00:00 |
| 小红 | 女 | 1997-02-23 00:00:00 |
| 王萍 | 女 | 1984-12-03 00:00:00 |
| 刘冰 | 女 | 1994-12-03 00:00:00 |
±-------±----±--------------------+
5 rows in set (0.00 sec)

31、查询成绩比该课程平均成绩低的学生成绩表

select cno,avg(degree) from score group by cno;

select * from score a where degree<(select avg(degree) from score  b where a.cno=b.cno);

mysql> select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 67 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 65 |
| 109 | 3-245 | 68 |
±----±------±-------+
4 rows in set (0.00 sec)

32、查询所有任课教师的Tname和depart;

select tno from course;

mysql> select tno from course;
±----+
| tno |
±----+
| 804 |
| 825 |
| 831 |
| 856 |
±----+
4 rows in set (0.00 sec)

select tname,depart from teacher where tno in(select tno from course);

mysql> select tname,depart from teacher where tno in(select tno from course);
±-------±----------------+
| tname | depart |
±-------±----------------+
| 李成 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
±-------±----------------+
4 rows in set (0.01 sec)

33、查询至少有两名同学的班号

select class  from student where ssex='男' group by class having count(*)>1;

mysql> select class from student where ssex=‘男’ group by class having count(*)>1;
±------+
| class |
±------+
| 95034 |
| 95035 |
±------+
2 rows in set (0.00 sec)

34、查询student表中不姓王的同学记录。

select * from student where sname not like '王%';

mysql> select * from student where sname not like ‘王%’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)

35、查询student表中每个学生的姓名和年龄

–年龄=当前年份-出生年份

select year(now());

mysql> select year(now());
±------------+
| year(now()) |
±------------+
| 2021 |
±------------+
1 row in set (0.01 sec)

select year(sbirthday) from student;

mysql> select year(sbirthday) from student;
±----------------+
| year(sbirthday) |
±----------------+
| 1597 |
| 1499 |
| 1999 |
| 1897 |
| 1997 |
| 2000 |
| 1597 |
| 1997 |
| 2010 |
±----------------+
9 rows in set (0.00 sec)

select sname,year(now())-year(sbirthday) as '年龄' from student;

mysql> select sname,year(now())-year(sbirthday) as ‘年龄’ from student;
±-------±-------+
| sname | 年龄 |
±-------±-------+
| 大明 | 424 |
| 刘思 | 522 |
| 张三 | 22 |
| 李白 | 124 |
| 李芳 | 24 |
| 李四 | 21 |
| 小黑 | 424 |
| 小红 | 24 |
| 小芳 | 11 |
±-------±-------+
9 rows in set (0.00 sec)

36、查询student表中最大和最小的sbirthday的日期值

select max(sbirthday) as '最大' ,min(sbirthday) as '最小' from student;

mysql> select max(sbirthday) as ‘最大’ ,min(sbirthday) as ‘最小’ from student;
±--------------------±--------------------+
| 最大 | 最小 |
±--------------------±--------------------+
| 2010-02-23 00:00:00 | 1499-03-23 00:00:00 |
±--------------------±--------------------+
1 row in set (0.00 sec)

37、以班号和年龄从大到小的顺序查询student表中的全部记录。

select * from student order by class desc,sbirthday ;

mysql> select * from student order by class desc,sbirthday ;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 103 | 张三 | 女 | 1999-03-23 00:00:00 | 95036 |
| 101 | 大明 | 男 | 1597-04-23 00:00:00 | 95035 |
| 106 | 李四 | 男 | 2000-02-23 00:00:00 | 95035 |
| 107 | 小黑 | 男 | 1597-04-23 00:00:00 | 95034 |
| 104 | 李白 | 男 | 1897-12-23 00:00:00 | 95034 |
| 102 | 刘思 | 男 | 1499-03-23 00:00:00 | 95033 |
| 108 | 小红 | 女 | 1997-02-23 00:00:00 | 95033 |
| 105 | 李芳 | 女 | 1997-02-23 00:00:00 | 95032 |
±----±-------±-----±--------------------±------+
9 rows in set (0.00 sec)

38、查询男教师及其所上课程

select cno from course where tno in(select tno from teacher where tsex='男');

mysql> select cno from course where tno in(select tno from teacher where tsex=‘男’);
±------+
| cno |
±------+
| 3-245 |
| 6-166 |
±------+
2 rows in set (0.00 sec)

39、查询最高分同学的sno、cno,degree列

select max(degree) from score;

mysql> select max(degree) from score;
±------------+
| max(degree) |
±------------+
| 98 |
±------------+
1 row in set (0.00 sec)

select sno,cno,degree from score where degree=(select max(degree) from score);
mysql> select sno,cno,degree from score where degree=(select max(degree) from score);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 6-166 | 98 |
±----±------±-------+
1 row in set (0.00 sec)

40、查询和小芳同性别的所有同学的sname

select * from student where sname='小芳';

mysql> select * from student where sname=‘小芳’;
±----±-------±-----±--------------------±------+
| sno | sname | ssex | sbirthday | class |
±----±-------±-----±--------------------±------+
| 109 | 小芳 | 男 | 2010-02-23 00:00:00 | 95037 |
| 110 | 小芳 | 女 | 2003-08-12 00:00:00 | 95037 |
| 111 | 小芳 | 女 | 2013-08-12 00:00:00 | 95036 |
±----±-------±-----±--------------------±------+
3 rows in set (0.00 sec)

select ssex from student where sname=‘李白’;

mysql> select ssex from student where sname=‘李白’;
±-----+
| ssex |
±-----+
| 男 |
±-----+
1 row in set (0.00 sec)

select sname from student where ssex=(select ssex from student where sname=‘李白’);

mysql> select sname from student where ssex=(select ssex from student where sname=‘李白’);
±-------+
| sname |
±-------+
| 大明 |
| 刘思 |
| 李白 |
| 李四 |
| 小黑 |
| 小芳 |
±-------+
6 rows in set (0.00 sec)

41、查询和李白同性别并且同班同学的sname

select sname from student 
where ssex=(select ssex from student where sname='李白')
and class=(select class from student where sname='李白');

mysql> select sname from student
-> where ssex=(select ssex from student where sname=‘李白’)
-> and class=(select class from student where sname=‘李白’);
±-------+
| sname |
±-------+
| 李白 |
| 小黑 |
±-------+
2 rows in set (0.00 sec)

42、查询所有选修计算机导论课程的男同学的成绩表

select * from course where cname='计算机导论';

select * from score where cno=(select cno from course where cname=‘计算机导论’);

mysql> select * from score where cno=(select cno from course where cname=‘计算机导论’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 65 |
±----±------±-------+
3 rows in set (0.00 sec)

;

select * from score
where cno=(select cno from course where cname=‘计算机导论’)
and sno in(select sno from student where ssex=‘男’);

mysql> select * from score
-> where cno=(select cno from course where cname=‘计算机导论’)
-> and sno in(select sno from student where ssex=‘男’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 109 | 3-105 | 65 |
±----±------±-------+
1 row in set (0.00 sec)

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’);

–现在查询所有同学的sno、cno、grade列。
select sno,cno,grade from score, grade where degree between low and upp;
mysql> select sno,cno,grade from score, grade where degree between low and upp;
±----±------±------+
| sno | cno | grade |
±----±------±------+
| 103 | 3-105 | B |
| 103 | 6-166 | D |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | A |
| 108 | 3-245 | B |
| 109 | 3-105 | D |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
| 109 | 9-888 | B |
±----±------±------+
10 rows in set (0.00 sec)

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
SQL 存储 关系型数据库
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
|
15天前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
15天前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
142 75
|
2月前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 高级(进阶) SQL 语句
MySQL 提供了丰富的高级 SQL 语句功能,能够处理复杂的数据查询和管理需求。通过掌握窗口函数、子查询、联合查询、复杂连接操作和事务处理等高级技术,能够大幅提升数据库操作的效率和灵活性。在实际应用中,合理使用这些高级功能,可以更高效地管理和查询数据,满足多样化的业务需求。
329 3
|
2月前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理,包括它们如何确保数据的一致性和完整性,以及在事务提交过程中各自的角色。同时,文章还探讨了这些日志在故障恢复中的重要性,强调了合理配置相关参数对于提高系统稳定性的必要性。
|
2月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
2月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
2月前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
3月前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
66 0

推荐镜像

更多