# MySQL数据库基础练习二

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

``````SELECT tno FROM teacher WHERE depart='计算机系';

SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系');

SELECT * FROM score WHERE cno IN(SELECT cno FROM course WHERE tno IN(SELECT tno FROM teacher WHERE depart='计算机系'));
sno cno     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    88
105    3-105    88
109    3-105    76``````

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

`union`求并集，联合在一起

``````SELECT tname,prof FROM teacher WHERE depart='计算机系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='电子工程系')
UNION
SELECT tname,prof FROM teacher WHERE depart='电子工程系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='计算机系');``````

### 27、查询选修编号为3-105课程且成绩至少高于选修编号为3-245的同学的cno，sno和degree，并按degree从高到底排序

``````SELECT * FROM score WHERE cno = '3-105';

SELECT * FROM score WHERE cno = '3-245';

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    92
102    3-105    91
101    3-105    90
104    3-105    88
105    3-105    88
109    3-105    76``````

### 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')
sno cno     degree
101    3-105    90
102    3-105    91
103    3-105    92
104    3-105    88
105    3-105    88``````

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

`as`：取别名

``````SELECT tname AS name,tsex AS sex,tbirthday as birthday FROM teacher
UNION
SELECT sname,ssex,sbirthday FROM student;
name  sex   birthday

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

``````SELECT tname AS name,tsex AS sex,tbirthday AS birthday FROM teacher WHERE tsex='女'
UNION
SELECT sname,ssex,sbirthday FROM student WHERE ssex='女';
name  sex      birthday

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

``````SELECT cno,AVG(degree) FROM score  GROUP BY cno;
cno      avg(degree)
3-105    87.5000
3-245    76.3333
6-166    81.6667

--把表分成两个，然后通过score a这样的方式取别名。
SELECT * FROM score a WHERE degree <(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno);
sno  cno  degree
105    3-245    75
105    6-166    79
109    3-105    76
109    3-245    68
109    6-166    81``````

### 32、查询所有任课教师的tname和depart

``````SELECT tname,depart FROM teacher WHERE tno IN(SELECT tno FROM course);
tname  depart

### 33、查询至少有两名男生的班号

``````SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(*)>1;
class
95033
95031``````

### 34、查询学生表中每个学生的姓名年龄

year函数与now函数

``````SELECT sname,year(NOW())-year(sbirthday) AS '年龄' FROM student;

### 35、查询student表中最大和最小的birthday

max与min函数

``````SELECT MAX(sbirthday) AS max_birthday,MIN(sbirthday) AS min_birthday FROM student;
max                         min
1979-11-30 00:00:00    1974-05-14 00:00:00``````

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

``mysql> SELECT * FROM student ORDER BY class desc, sbirthday;``

### 37、查询男教师及其所上的课程

``````SELECT cname FROM course WHERE tno in(SELECT tno FROM teacher WHERE tsex='男');

### 38、查询最高分同学的sno，cno和degree列

``````SELECT sno,cno,degree from score WHERE degree=(SELECT max(degree) FROM score);
103    3-105    92``````

### 39、查询和李军同性别的所有同学的sname

``````SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');

### 40、查询和李军同性别同班级的同学的sname

``````SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')
AND class=(SELECT class from student WHERE sname = '李军');

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

``````SELECT * FROM score WHERE cno=(SELECT cno FROM course WHERE cname='计算机导论')
AND sno in(SELECT sno from student WHERE ssex='男');
sno   cno    degree
101    3-105    90
102    3-105    91
104    3-105    88
109    3-105    76``````

``````create table grade(
low int(3),
upp int(3),
);

``````SELECT sno,cno,grade FROM score,grade
WHERE degree BETWEEN low AND upp;
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``````

## sql的四种连接查询

``````内连接
inner join 或者join

full join 或者full outer join``````

``````person表
id，name，cardID
1    张三    1
2    李四    3
3    王五    6
card表
id,name
1    饭卡
2    建行卡
3    农行卡
4    工商卡
5    邮政卡

--inner join查询（内连接）
SELECT * from person INNER JOIN card ON person.cardID=card.id;
id name  cardid id name
1    张三    1    1    饭卡
2    李四    3    3    农行卡
--内连查询，其实就是两张表中的数据，通过某个字段相等查询出相关记录数据。
--left join左外连接
SELECT * FROM person LEFT JOIN card ON person.cardID=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
---左外连接，会把左边表里所有的数据取出来，而右边表里的数据，如果有相等的，就显示出来，如果没有就补null。
----right join右外连接
SELECT * FROM person RIGHT JOIN card ON person.cardID=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)
---右外连接，会把右边表里所有的数据取出来，而左边表里的数据，如果有相等的，就显示出来，如果没有就补null。
--------------------------------------------------------------------
---------------------------------------------------------------------
----full join全外连接
---MySQL不支持全外连接
---只能通过union来实现
mysql> SELECT * FROM person LEFT JOIN card ON person.cardID=card.id
-> UNION
-> SELECT * FROM person RIGHT JOIN card ON person.cardID=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)``````

4 声望
1 粉丝