MySQL——查询练习(3)

一、子查询

查询所有任课教师的tname 和 depart(任课:说明在课程表中有课程)

---先查询课程表中课程对应教师编号
select tno from course;
---再查询教师编号对应的老师
select tname,depart from teacher where tno in(select tno from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 李诚   | 计算机系        |
| 王萍   | 计算机系        |
| 刘冰   | 电子工程系      |
| 张旭   | 电子工程系      |
+--------+-----------------+

查询"男"教师 及其所上的课

---先找男老师的编号
select tno from teacher where tsex='男';
---根据编号找到课程
select * from course where tno in(select tno from teacher where tsex='男');
+-------+--------------+-----+
| cno   | cname        | tno |
+-------+--------------+-----+
| 3-245 | 操作系统     | 804 |
| 6-166 | 数字电路     | 856 |
+-------+--------------+-----+

查询最高分同学的sno cno 和 degree;

---先查询最高分
select max(degree) from score;
---再查最高分的相关信息
select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+

查询和"李军"同性别的所有同学的sname

---先查询李军的性别
select ssex from student where sname='李军';
---再查询同性别的sname
select sname from student where ssex=(select ssex from student where sname='李军');
+-----------+
| sname     |
+-----------+
| 曾华      |
| 匡明      |
| 李军      |
| 陆军      |
| 王尼玛    |
| 张全蛋    |
| 赵铁柱    |
| 张飞      |
+-----------+

查询和"李军"同性别并且同班的所有同学的sname

---先查询李军的性别
select ssex from student where sname='李军';
---再查询李军的班级
select class from student where sname='李军');
---加以合并
select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
+-----------+
| sname     |
+-----------+
| 曾华      |
| 李军      |
| 王尼玛    |
+-----------+

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

---先找男同学
select * from student where ssex='男';
---再找计算机导论
select * from course where cname='计算机导论';
---成绩表中根据sno和cno进行筛选
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 |     89 |
| 109 | 3-105 |     76 |
+-----+-------+--------+

二、条件加分组筛选

查出至少有2名男生的班号

select class from student where ssex='男' group by class having count(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

三、not like模糊查询取反

查询student 表中 **不姓"王"**的同学的记录

select * from student where sname not like '王%';
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华      || 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      || 1975-10-02 00:00:00 | 95031 |
| 104 | 李军      || 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军      || 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    || 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    || 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞      || 1974-06-03 00:00:00 | 95038 |
+-----+-----------+------+---------------------+-------+

四、year函数与now函数

查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)

---当前年份
select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
---出生年份
select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1975 |
|            1976 |
|            1976 |
|            1975 |
|            1974 |
|            1976 |
|            1975 |
|            1974 |
|            1974 |
+-----------------+
---当前年份-出生年份
select sname,year(now())-year(sbirthday) as '年龄' from student;
+-----------+--------+
| sname     | 年龄   |
+-----------+--------+
| 曾华      |     43 |
| 匡明      |     45 |
| 王丽      |     44 |
| 李军      |     44 |
| 王芳      |     45 |
| 陆军      |     46 |
| 王尼玛    |     44 |
| 张全蛋    |     45 |
| 赵铁柱    |     46 |
| 张飞      |     46 |
+-----------+--------+

五、max与min函数

查询student中最大和最小的 sbirthday的值

select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;
+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+

六、多字段排序

班级号和年龄从大到小的顺序查询student表中的全部记录
注意,年龄和出生日期成反比,所以班级降序,出生日期升序。

select * from student order by class desc,sbirthday;
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 110 | 张飞      || 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽      || 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      || 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    || 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华      || 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军      || 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱    || 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳      || 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋    || 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明      || 1975-10-02 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+

七、按等级查询

假设使用了以下命令建立了一个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     |
+-----+-------+-------+

八、连接查询

创建要使用的表:

create database testJoin;
create table person( id int, name varchar(20), cardId int);
create table card( id int,name varchar(20));

添加数据

insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'邮政卡');
---并没有创建外键
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);

(一)内连接 inner join 或者 join

内联查询就是两张表中的数据,通过某个字段相等,查询出相关记录。
在这里插入图片描述

select * from person inner join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
+------+--------+--------+------+-----------+

(二)外连接

1.左连接 left join 或者 left outer join

把左边表所有数据取出来,而右边表中的数据如果有相等的显示,没有则显示为null。
在这里插入图片描述

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

把右边表所有数据取出来,而左边表中的数据如果有相等的显示,没有则显示为null。
在这里插入图片描述

select * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+
3.完全外连接 full join 或者 full outer join

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 | 邮政卡    |
+------+--------+--------+------+-----------+
发布了74 篇原创文章 · 获赞 8 · 访问量 2146
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 创作都市 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览