SQL查询技巧与实践-优快云博客

分屏 vin键+ 上下左右键

查询所有的学生 cno 和 cname 和degree

mysql> select cno,cname from course;
+-------+----------------+
| cno   | cname          |
+-------+----------------+
| 3-105 | jisuanjidaolun |
| 3-245 | caozuo xitong  |
| 9-888 | gaodengshuxue  |
+-------+----------------+
3 rows in set (0.00 sec)

mysql> select cno,sno,degree from score;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 102 |     79 |
| 3-245 | 102 |      9 |
| 3-105 | 103 |     92 |
| 3-245 | 103 |     86 |
| 3-105 | 105 |     88 |
| 3-245 | 105 |     75 |
| 3-105 | 108 |     70 |
| 3-105 | 109 |     76 |
| 3-245 | 109 |     68 |
+-------+-----+--------+
9 rows in set (0.00 sec)

mysql> select sno,cname,degree from course,score where course.cno=score.cno;
+-----+----------------+--------+
| sno | cname          | degree |
+-----+----------------+--------+
| 102 | jisuanjidaolun |     79 |
| 102 | caozuo xitong  |      9 |
| 103 | jisuanjidaolun |     92 |
| 103 | caozuo xitong  |     86 |
| 105 | jisuanjidaolun |     88 |
| 105 | caozuo xitong  |     75 |
| 108 | jisuanjidaolun |     70 |
| 109 | jisuanjidaolun |     76 |
| 109 | caozuo xitong  |     68 |
+-----+----------------+--------+
9 rows in set (0.04 sec)

三表关联查询学生的sname cname degree;
sname–student
cname–course
degree – score

错误1 少一个判断条件 and
mysql> select sname,cname,degree form student,course,score where student.cno=course.con,score.con=scores.con;

mysql> select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;
+------------+----------------+--------+
| sname      | cname          | degree |
+------------+----------------+--------+
| kuangming  | jisuanjidaolun |     79 |
| kuangming  | caozuo xitong  |      9 |
| wangli     | jisuanjidaolun |     92 |
| wangli     | caozuo xitong  |     86 |
| wangfan    | jisuanjidaolun |     88 |
| wangfan    | caozuo xitong  |     75 |
| zhujunqing | jisuanjidaolun |     70 |
| zhude      | jisuanjidaolun |     76 |
| zhude      | caozuo xitong  |     68 |
+------------+----------------+--------+
9 rows in set (0.00 sec)
##错误
select sname,cname,degree from student,course,score,cno,sno  where student.sno=score.sno and course.cno=score.cno;
ERROR 1146 (42S02): Table 'selecttest.cno' doesn't exist
## 表不存在;

## 写反了 student.sno 和 course.cno 应该在 select的后面
mysql> select sname,cname,degree from student,course,score,student.sno,course.cno  where student.sno=score.sno and course.cno=score.cno;
正确: 加上sno 和cno 
mysql> select sname,cname,degree,student.sno,score.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
+------------+----------------+--------+-----+-------+
| sname      | cname          | degree | sno | cno   |
+------------+----------------+--------+-----+-------+
| kuangming  | jisuanjidaolun |     79 | 102 | 3-105 |
| kuangming  | caozuo xitong  |      9 | 102 | 3-245 |
| wangli     | jisuanjidaolun |     92 | 103 | 3-105 |
| wangli     | caozuo xitong  |     86 | 103 | 3-245 |
| wangfan    | jisuanjidaolun |     88 | 105 | 3-105 |
| wangfan    | caozuo xitong  |     75 | 105 | 3-245 |
| zhujunqing | jisuanjidaolun |     70 | 108 | 3-105 |
| zhude      | jisuanjidaolun |     76 | 109 | 3-105 |
| zhude      | caozuo xitong  |     68 | 109 | 3-245 |
+------------+----------------+--------+-----+-------+
 
as 命令 :取一个别名


##添加对比
mysql> select sname,cname,degree,student.sno as cou_sno,score.cno as cou_cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
+------------+----------------+--------+---------+---------+
| sname      | cname          | degree | cou_sno | cou_cno |
+------------+----------------+--------+---------+---------+
| kuangming  | jisuanjidaolun |     79 | 102     | 3-105   |
| kuangming  | caozuo xitong  |      9 | 102     | 3-245   |
| wangli     | jisuanjidaolun |     92 | 103     | 3-105   |
| wangli     | caozuo xitong  |     86 | 103     | 3-245   |
| wangfan    | jisuanjidaolun |     88 | 105     | 3-105   |
| wangfan    | caozuo xitong  |     75 | 105     | 3-245   |
| zhujunqing | jisuanjidaolun |     70 | 108     | 3-105   |
| zhude      | jisuanjidaolun |     76 | 109     | 3-105   |
| zhude      | caozuo xitong  |     68 | 109     | 3-245   |
+------------+----------------+--------+---------+---------+
mysql> select sname,cname,degree,student.sno as cou_sno,score.sno,score.cno as cou_cno,score.cno from student,course,score where student.sno=score.sno and course.cno=score.cno;
+------------+----------------+--------+---------+-----+---------+-------+
| sname      | cname          | degree | cou_sno | sno | cou_cno | cno   |
+------------+----------------+--------+---------+-----+---------+-------+
| kuangming  | jisuanjidaolun |     79 | 102     | 102 | 3-105   | 3-105 |
| kuangming  | caozuo xitong  |      9 | 102     | 102 | 3-245   | 3-245 |
| wangli     | jisuanjidaolun |     92 | 103     | 103 | 3-105   | 3-105 |
| wangli     | caozuo xitong  |     86 | 103     | 103 | 3-245   | 3-245 |
| wangfan    | jisuanjidaolun |     88 | 105     | 105 | 3-105   | 3-105 |
| wangfan    | caozuo xitong  |     75 | 105     | 105 | 3-245   | 3-245 |
| zhujunqing | jisuanjidaolun |     70 | 108     | 108 | 3-105   | 3-105 |
| zhude      | jisuanjidaolun |     76 | 109     | 109 | 3-105   | 3-105 |
| zhude      | caozuo xitong  |     68 | 109     | 109 | 3-245   | 3-245 |
+------------+----------------+--------+---------+-----+---------+-------+
9 rows in set (0.00 sec)

查询95031班的学生没门课的成绩

select sno from student where class='95031'

select *from score where sno in (select sno from student where class='95031');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 3-105 |     79 |
| 102 | 3-245 |      9 |
| 110 | 3-105 |     79 |
| 111 | 3-105 |     78 |
| 121 | 3-105 |     90 |
| 122 | 3-105 |     80 |
+-----+-------+--------+
6 rows in set (0.00 sec)
平均成绩
mysql> select cno,avg(degree)from score where sno in (select sno from student where class='95031') group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     81.2000 |
| 3-245 |      9.0000 |
+-------+-------------+
2 rows in set (0.00 sec)

avg 求平均数
group by 按分组

查询3-105同学的成绩高于109号同学 3-105成绩的所有同学记录.

错误
mysql> select degree from score where degree > (select degree score where sno='109' and cno='3-105');
Empty set (0.00 sec)

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

##错误1mysql> select * from score where cno='3-105', degree > (select degree from score where sno='109' and cno='3-105');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' degree > (select degree from score where sno='109' and cno='3-105')' at line 1


mysql> select * from score where cno='3-105' and degree > (select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 3-105 |     79 |
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 110 | 3-105 |     79 |
| 111 | 3-105 |     78 |
| 121 | 3-105 |     90 |
| 122 | 3-105 |     80 |
+-----+-------+--------+

查询成绩高于109号同学 3-105成绩的所有同学记录

mysql> select * from score where degree  > (select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 102 | 3-105 |     79 |
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 105 | 3-105 |     88 |
| 110 | 3-105 |     79 |
| 111 | 3-105 |     78 |
| 121 | 3-105 |     90 |
| 122 | 3-105 |     80 |
+-----+-------+--------+
8 rows in set (0.00 sec)

查询学号为108
1 01 同学的年出生的偶有学生的sno sname 和birthday列;

错误 
mysql> select *from student where year(birthday) in (select *from student where sno in (108,109);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

##
mysql> select *from student where year(birthday) in (select *from student where sno in (108,109));
ERROR 1241 (21000): Operand should contain 1 column(s)

mysql> select *from student where year(birthday) in (select *from student where sno in (108,109);)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    -> ;
   -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

   -> ;
mysql> select year(birthday) from student where sno in (108,101);
+----------------+
| year(birthday) |
+----------------+
|           1977 |
|           1744 |
+----------------+
2 rows in set (0.00 sec)

mysql> select *from student where year(birthday) in (select year(birthday) from student where sno in (108,101));
+-----+------------+------+---------------------+--------+
| sno | sname      | sexx | birthday            | class  |
+-----+------------+------+---------------------+--------+
| 101 | zenhua     | F    | 1977-09-01 00:00:00 | 95003  |
| 108 | zhujunqing | F    | 1744-09-08 00:00:00 | 955033 |
+-----+------------+------+---------------------+--------+
2 rows in set (0.00 sec)