数据库基础(2)

查询练习

-- 先创建几个表

学生表students
create table students(
sno varchar(20) primary key,
sname varchar(20) not null,
sgender varchar(20) not null,
sbirth datetime,
class varchar(20)
);

教师表teachers
create table teachers(
tno varchar(20) primary key,
tname varchar(20) not null,
tgender varchar(10) not null,
tbirth datetime,
prof varchar(20) not null,
depart varchar(20) not null
);

课程表course
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teachers(tno)
);

成绩表score
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references students(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);

--给表中插入数据

INSERT INTO students VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO students VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO students VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO students VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO students VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO students VALUES('106','陆君','男','1974-06-03','95031');
INSERT INTO students VALUES('107','王尼玛','男','1974-02-20','95033');
INSERT INTO students VALUES('108','张全蛋','男','1974-02-10','95031');
INSERT INTO students VALUES('109','赵铁柱','男','1974-06-03','95031');

INSERT INTO teachers VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teachers VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teachers VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teachers VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('105','6-166','79');

INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('109','6-166','81');

-- 查询students表中的所有记录

mysql> select * from students;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男      | 1974-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)

-- 查询students表中的所有记录的sname、sgender、class列

mysql> select sname,sgender,class from students;
+-----------+---------+-------+
| sname     | sgender | class |
+-----------+---------+-------+
| 曾华      | 男      | 95033 |
| 匡明      | 男      | 95031 |
| 王丽      | 女      | 95033 |
| 李军      | 男      | 95033 |
| 王芳      | 女      | 95031 |
| 陆君      | 男      | 95031 |
| 王尼玛    | 男      | 95033 |
| 张全蛋    | 男      | 95031 |
| 赵铁柱    | 男      | 95031 |
+-----------+---------+-------+
9 rows in set (0.00 sec)

-- 查询教师teachers所有的单位depart 即不重复的depart列
-- distinct 排重

mysql> select distinct depart from teachers;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

-- 查询score表中成绩degree在60~80之间的所有记录
-- 查询区间 where...between...and

mysql> select * from score where degree between 60 and 80; #等于 select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

-- 查询score表中成绩为85、86或88的记录
-- 表示 或者关系 in()

mysql> select * from score where degree in(85, 86, 88);
+-----+-------+--------+
| sno | cno   |  degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
3 rows in set (0.00 sec)

-- 查询 students 表中"95031"班或性别为"女"的同学记录
-- or 表示或者

mysql> select * from students where class='95031' or sgender='女';
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
6 rows in set (0.00 sec)

-- 以 class 降序查询 students 表的所有记录
-- 升序asc 降序decs

mysql> select * from students order by class desc;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    | 男      | 1974-02-20 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)

-- 以cno升序、degree降序查询score表的所有记录
-- 先排cno 后排degree

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
+-----+-------+--------+
9 rows in set (0.00 sec)

-- 查询"95031"班的学生人数.

mysql> select count(*) from students where class='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

-- 查询score表中的最高分的学生学号和课程号.(子查询或者排序)

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

-- 查询每门课的平均成绩
-- avg() where...='...';

select cno,avg(degree) from score where cno='3-105';
select cno,avg(degree) from score where cno='3-245';
select cno,avg(degree) from score where cno='6-166';

-- 如何在一个语句中实现上面3条业务代码?
-- group by 分组

mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 查询score表中至少有2名学生选修并以3开头的课程的平均成绩

select cno,avg(degree) from score
group by cno
having count(cno)>=2
and cno like '3%';

-- 查询分数在70到90之间的同学

select sno,degree from score where degree between 70 and 90;

==

select sno,degree from score where degree>70 and degree<90;

-- 查询学生的sname,cno,degree sname在students表 cno,degree在score表

mysql> select * from students;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾华      | 男      | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男      | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男      | 1974-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)
mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

-- 合并

mysql> select sname,cno,degree from students,score where students.sno = score.sno;
+-----------+-------+--------+
| sname     | cno   | degree |
+-----------+-------+--------+
| 王丽      | 3-105 |     92 |
| 王丽      | 3-245 |     86 |
| 王丽      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 赵铁柱    | 3-105 |     76 |
| 赵铁柱    | 3-245 |     68 |
| 赵铁柱    | 6-166 |     81 |
+-----------+-------+--------+
9 rows in set (0.00 sec)

-- 查询所有学生的sno,cname和degree
-- sno,cname来自course , degree来自score

mysql> select * from course;
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论      | 825 |
| 3-245 | 操作系统        | 804 |
| 6-166 | 数字电路        | 856 |
| 9-888 | 高等数学        | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

mysql> select sno,cname,degree from course,score where
    -> course.cno = score.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 计算机导论      |     92 |
| 105 | 计算机导论      |     88 |
| 109 | 计算机导论      |     76 |
| 103 | 操作系统        |     86 |
| 105 | 操作系统        |     75 |
| 109 | 操作系统        |     68 |
| 103 | 数字电路        |     85 |
| 105 | 数字电路        |     79 |
| 109 | 数字电路        |     81 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)

-- 查询所有学生的same、cname和degree列
-- sname来自students cname来自course degree来自score

select sname,cname,degree from students,course,score where
students.sno = score.sno and course.cno = score.cno;

-- 查询'95031'班每门课的平均分

mysql> select * from students where class='95031';
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
5 rows in set (0.04 sec)

mysql> select * from score where sno in(select sno from students where class='95031');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> select cno,avg(degree) from score
where sno in(select sno from students where class='95031')
group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 查询选修"3-105"课程的同学成绩高于'109'号同学'3-105'成绩的所有同学

mysql> select sno,cno,degree from score 
where 
degree>(select degree from score where sno='109' and cno='3-105') 
and 
cno ='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.00 sec)

-- 查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirth列

select year(sbirth) from students where sno in(108,101); 

select sno,sname,sbirth from students 
where 
year(sbirth) in 
(select year(sbirth) from students where sno in(108,101));

-- 查询张旭教师任课的学生成绩
select cno from course where tno = (select tno from teachers where tname='张旭');

mysql> select sno,cno,degree from score 
where cno=
(select cno from course where tno = 
(select tno from teachers where tname='张旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,098评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,213评论 2赞 380
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,960评论 0赞 336
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,519评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,512评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,533评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,914评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,574评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,804评论 1赞 296
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,563评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,644评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,350评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,933评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,908评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,146评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,847评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,361评论 2赞 342