关于mysql:MySQL数据库基础练习二

46次阅读

共计 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)

正文完
mysql
发表至： mysql
2020-11-18
 0
MySql分组查询最新的多有记录
关于mysql:第38期MySQL-时间类分区具体实现
关于mysql:SQL-性能优化梳理
关于mysql:MySql二基本概念与基础操作