目录
- MySQL的查询
- 准备数据
- 开始查询
- 1.查询students表的所有记录
- 2.查询students表的所有记录的s_name,s_sex,s_class列
- 3.查询教师所有的单位但是不重复的t_dept列
- 4.查询score表中成绩在60-80之间所有的记录(sc_degree)
- 5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
- 6.查询student表中'95031'班或者性别为'女'的同学记录
- 7.以class降序查询student表中所有的记录
- 8.以c_no升序.sc_degree降序查询score表中所有的数据
- 9.查询'95031'班的学生人数
- 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
- 11.查询每门课的平均成绩
- 12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
- 13.查询分数大于70但是小于90的s_no列
- 14.查询所有的学生 s_name , c_no, sc_degree列
- 15.查询所有学生的s_no, c_name, sc_degree列
- 16.查询所有的学生 s_name , c_name, sc_degree列
- 17.查询班级是'95031'班学生每门课的平均分
- 18.查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录
- 19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录
- 20.查询所有学号为'108','101'的同学同年出生的所有学生的s_no,s_name和s_birthday
MySQL的查询
准备数据
创建表
-
学生表(student)
create table student(s_no varchar(20) primary key,s_name varchar(20) not null,s_sex VARCHAR(10) not null,s_birthday datetime,s_class VARCHAR(20) );
+------------+-------------+------+-----+---------+-------+ | 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)
create table teacher(t_no varchar(20) primary key,t_name varchar(20) not null,t_sex varchar(20) not null,t_brithday datetime,t_rof varchar(20) not null,t_dept varchar(20) not null);
+------------+-------------+------+-----+---------+-------+ | 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_brithday | datetime | YES | | NULL |教师生日| | t_rof | varchar(20) | NO | | NULL |教师职称| | t_dept | varchar(20) | NO | | NULL |所在部门| +------------+-------------+------+-----+---------+-------+
-
课程表(course)
create table course(c_no varchar(20) primary key,c_name varchar(20) not null,t_no varchar(20) not null,foreign key(t_no) references teacher(t_no) );
+--------+-------------+------+-----+---------+-------+ | 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)
create table score(s_no varchar(20) not null,c_no varchar(20) not null,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));
+-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | s_no | varchar(20) | NO | PRI | NULL |学生学号| | c_no | varchar(20) | NO | PRI | NULL |课程表id| | 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.查询students表的所有记录
mysql> 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.查询students表的所有记录的s_name,s_sex,s_class列
mysql> 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_dept列
- distinct 排除重复的值
mysql> select distinct t_dept from teacher;
+-----------------+
| t_dept |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
4.查询score表中成绩在60-80之间所有的记录(sc_degree)
- *** between 60 and 80 是包含边界值的**
mysql> select * from score where sc_degree between 60 and 80 ;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+------+-------+-----------+
mysql> SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ;
+------+-------+-----------+
| 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)
mysql> 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’班或者性别为’女’的同学记录
mysql> 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表中所有的记录
-
**order by 字段 按照字段进行降序排序(默认使用的是asc) **
-
升序 desc 降序 asc
mysql> 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 |
+------+-----------+-------+---------------------+---------+
8.以c_no升序.sc_degree降序查询score表中所有的数据
mysql> SELECT * FROM score order by c_no, 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() 统计查询的数据个数
mysql> SELECT count(*) FROM student where s_class = '95031';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
-
使用子查询
-
最高分: SELECT MAX(sc_degree) FROM score;
mysql> select s_no,c_no from score where sc_degree = (select max(sc_degree) from score); +------+-------+ | s_no | c_no | +------+-------+ | 103 | 3-105 | +------+-------+
-
使用排序方式
- limit x,y (x:表示从X条数据开始 y:需要查出多少条)
- 其实使用limit的方式会有问题,如果最高分数一样,limit只会返回一条数据,而使用子查询可以将最大值都取到
mysql> select s_no,c_no from score order by sc_degree desc limit 0,1;
+------+-------+
| s_no | c_no |
+------+-------+
| 103 | 3-105 |
+------+-------+
11.查询每门课的平均成绩
-
avg(字段) avg求字段的平均值
-
group by 字段 通过该字段对数据进行分组
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 ‘%’ 模糊匹配,%表示匹配符,并且不限制个数
- having 通常用于给分组设置条件(分组只是按照某一字段相同,我们通过having可以对这个数据进行限制)
mysql> 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列
mysql> select * from score where sc_degree >70 and sc_degree < 90;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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 | 6-166 | 81 |
+------+-------+-----------+
mysql> select * from score where sc_degree between 70 and 90;;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 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 | 6-166 | 81 |
+------+-------+-----------+
14.查询所有的学生 s_name , c_no, sc_degree列
mysql> select s.s_name,c.c_no,sc.sc_degree from student s,course c,score sc where sc.s_no = s.s_no and sc.c_no = c.c_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列
mysql> select s.s_no,c.c_name,sc.sc_degree from student s,course c,score sc where sc.s_no = s.s_no and sc.c_no = c.c_no;
+------+-----------------+-----------+
| s_no | c_name | sc_degree |
+------+-----------------+-----------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 109 | 计算机导论 | 76 |
| 109 | 操作系统 | 68 |
| 109 | 数字电路 | 81 |
+------+-----------------+-----------+
16.查询所有的学生 s_name , c_name, sc_degree列
mysql> select s.s_name,c.c_name,sc.sc_degree from student s,course c,score sc where s.s_no = sc.s_no and c.c_no = sc.c_no;
+-----------+-----------------+-----------+
| s_name | c_name | sc_degree |
+-----------+-----------------+-----------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+-----------+-----------------+-----------+
17.查询班级是’95031’班学生每门课的平均分
mysql> select score.c_no,avg(score.sc_degree) from student,score where s_class = '95031'and score.s_no = student.s_no group by score.c_no;
+-------+----------------------+
| c_no | avg(score.sc_degree) |
+-------+----------------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+----------------------+
18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录
- 子查询 即为将查询出的结果作为条件代入其他SQL语句
mysql> 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’的成绩的所有记录
mysql> 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 |
+------+-------+-----------+
20.查询所有学号为’108’,'101’的同学同年出生的所有学生的s_no,s_name和s_birthday
- year(datetime) 获取时间类型的年份
mysql> 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 |
+------+-----------+-------+---------------------+---------+