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 degree103 3-245 86105 3-245 75109 3-245 68101 3-105 90102 3-105 91103 3-105 92104 3-105 88105 3-105 88109 3-105 7626、查问计算机系与电子工程系不同职称的老师的tname和prof
union求并集，联结在一起
SELECT tname,prof FROM teacher WHERE depart='计算机系' AND prof NOT IN(SELECT prof FROM teacher WHERE depart='电子工程系')UNIONSELECT 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 degree103 3-105 92102 3-105 91101 3-105 90104 3-105 88105 3-105 88109 3-105 7628、查问选修编号为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 degree101 3-105 90102 3-105 91103 3-105 92104 3-105 88105 3-105 8829、查问所有老师和同学的name，sex，和birthday
as：取别名
SELECT tname AS name,tsex AS sex,tbirthday as birthday FROM teacherUNIONSELECT 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:0030、查问所有女老师和女同学的name，sex和birthday
SELECT tname AS name,tsex AS sex,tbirthday AS birthday FROM teacher WHERE tsex='女'UNIONSELECT 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:0031、查问问题比该课程均匀问题低的同学的成绩表
SELECT cno,AVG(degree) FROM score GROUP BY cno;cno avg(degree)3-105 87.50003-245 76.33336-166 81.6667--把表分成两个，而后通过score a这样的形式取别名。SELECT * FROM score a WHERE degree <(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno);sno cno degree105 3-245 75105 6-166 79109 3-105 76109 3-245 68109 6-166 8132、查问所有任课教师的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;class950339503134、查问学生表中每个学生的姓名年龄
year函数与now函数
SELECT sname,year(NOW())-year(sbirthday) AS '年龄' FROM student;曾华 43匡明 45王丽 44李军 44王芳 45陆君 46王尼玛 46张全蛋 46赵铁柱 41关羽 4235、查问student表中最大和最小的birthday
max与min函数
SELECT MAX(sbirthday) AS max_birthday,MIN(sbirthday) AS min_birthday FROM student;max min1979-11-30 00:00:00 1974-05-14 00:00:0036、以班号和年龄从大到小的程序查问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 9239、查问和李军同性别的所有同学的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 degree101 3-105 90102 3-105 91104 3-105 88109 3-105 7642、假如应用如下命令建设一个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,gradeWHERE degree BETWEEN low AND upp;sno cno grade101 3-105 A102 3-105 A103 3-105 A103 3-245 B103 6-166 B104 3-105 B105 3-105 B105 3-245 C105 6-166 C109 3-105 C109 3-245 D109 6-166 Bsql的四种连贯查问
内连贯inner join 或者join外连贯左连贯 left join 或者left outer join右连贯 right join 或者right outer join齐全外连贯full join 或者full outer join
创立两个表：
person表id，name，cardID1 张三 12 李四 33 王五 6card表id,name1 饭卡2 建行卡3 农行卡4 工商卡5 邮政卡并没有创立外键--inner join查问（内连贯）SELECT * from person INNER JOIN card ON person.cardID=card.id;id name cardid id name1 张三 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)