mysql数据库入门第二张试卷_MySQL数据库基础练习二-CSDN博客

本文链接：https://blog.csdn.net/weixin_34551573/article/details/113285300

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从高到底排序

至少的含义：大于其中至少一个，anySELECT * 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

且：所有，all表示所有的关系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

李诚男 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

王尼玛男 1974-06-03 00:00:00

张全蛋男 1974-06-03 00:00:00

赵铁柱男 1974-06-03 00:00:00

关羽男 1974-06-03 00:00:00

30、查询所有女教师和女同学的name，sex和birthdaySELECT 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

王萍女 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 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

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;

曾华 43

匡明 45

王丽 44

李军 44

王芳 45

陆君 46

王尼玛 46

张全蛋 46

赵铁柱 41

关羽 42

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、查询和李军同性别的所有同学的snameSELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军');

曾华

匡明

李军

陆君

王尼玛

张全蛋

赵铁柱

关羽

40、查询和李军同性别同班级的同学的snameSELECT 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

42、假设使用如下命令建立一个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;

sno cno 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

sql的四种连接查询内连接

inner join 或者join

外连接

左连接 left join 或者left outer join

右连接 right join 或者right outer 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;

+------+--------+--------+------+-----------+

+------+--------+--------+------+-----------+

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

+------+--------+--------+------+-----------+

+------+--------+--------+------+-----------+

| 1 | 张三 | 1 | 1 | 饭卡 |

| 2 | 李四 | 3 | 3 | 农行卡 |

+------+--------+--------+------+-----------+

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;

+------+--------+--------+------+-----------+

+------+--------+--------+------+-----------+

| 1 | 张三 | 1 | 1 | 饭卡 |

| 2 | 李四 | 3 | 3 | 农行卡 |

| 3 | 王五 | 6 | NULL | NULL |

+------+--------+--------+------+-----------+

6 rows in set (0.00 sec)