共计 5454 个字符,预计需要花费 14 分钟才能阅读完成。
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 从高到底排序
至多的含意:大于其中至多一个,any
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
且:所有,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 和 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
王萍 女 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 <(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;
曾华 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、查问和李军同性别的所有同学的 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
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;
+------+--------+--------+------+-----------+
| 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)
正文完