查询练习2

查询练习
创建表
学生表

student

学号

姓名

性别

出生日期

班级

create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10)not null,
sbirthday datetime,
class varchar(20));
课程表

Course

课程号

课程名称

教师编号

create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno));
成绩表

score

学号

课程号

成绩

create table score(
sno varchar(20)not null,
cno varchar(20)not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
教师表

teacher

编号

名字

性别

出生日期

职称

部门

create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20)not null);
插入数据

添加学生信息

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

添加教师信息

insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher 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("105", '3-245', '75');
insert into score values('109', '3-245', '68');
insert into score values('103', "3-105", '92');
insert into score values('105', '3-105', "88");
insert into score values('109', '3-105',"76");
insert into score values("103", '6-166','85');
insert into score values('105', '6-166','79');
insert into score values('109', '6-166','81');

查询练习

1.查询student表的所有记录。

select * from student;# * 表示所有字段

2.查询student表中的所有记录的sname,ssex和class列。

select sname,ssex,class from student;

3.查询教师所有单位及不重复的depart列。

select distinct depart from teacher; #distinct:排除重复

4、查询score表中成绩在60到80之间的所有记录。

查询区间 between....and......

select * from score where degree between 60 and 80;

或

select * from score where degree > 60 and degree<80;#运算符表示

5、查询score表中成绩为85，86或88的记录。

表示或者关系的查询 in

select * from score where degree in(85,86,88);

6、查询student表中“95031"班或性别为“女”的同学记录。#or表示或者

select * from student where class="95031"or ssex="女"；

7、以class降序查询student表的所有记录。

升序降序

select * from student order by class desc;#降序
select * from student order by class; #默认升序
select * from student order by class asc;#升序

8、以cno升序、degree降序查询score表的所有记录。#先以cno为升序，遇见相同的异score为降序

select * from score order by cno asc,degree desc;

9、查询“95031"班的学生人数。

统计 count

select count(*) from student where class="95031";

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

select sno,cno from score where degree=(select max(degree)from score);

排序的做法

select sno,cno,degree from score order by degree desc limit 0,1;#limit 0表示从哪开始，1表示查几条
查询练习

11.查询每门课的平均成绩

select * from course;

avg 求平均值

select avg(degree) from score where cno ="3-105";#一门课程

select cno,avg(degree) from score group by cno;#先利用group进行分组

12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数

select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like "3%";#like%3以3为开头的

13.查询分数大于70，小于90的sno列

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

或者

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

14.查询所有学生的sname、cno、degree列

示例一：进行分开查询，其中sno在两个表中是存在交集的

select sno,sname from student;
+-----+--------+
| sno | sname |
+-----+--------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |

select sno,cno,degree 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 |
+-----+-------+--------+

示例二，将两个查询进行合并

select sname,cno,degree from student,score where student.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 |
+--------+-------+--------+

多表查询

15.查询所有学生的sno，cname,和degree列。

示例一先分别表示

select cno,cname from course;
+-------+------------+
| cno | cname |
+-------+------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |

select cno,sno,degree from score;
+-------+-----+--------+
| cno | sno | degree |
+-------+-----+--------+
| 3-105 | 103 | 92 |
| 3-245 | 103 | 86 |
| 6-166 | 103 | 85 |
| 3-105 | 105 | 88 |
| 3-245 | 105 | 75 |
| 6-166 | 105 | 79 |
| 3-105 | 109 | 76 |
| 3-245 | 109 | 68 |
| 6-166 | 109 | 81 |
+-------+-----+--------+

示例二

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

16.三联表查询

查询所有学生的sname、cname和degree列

select sno,cno,degree 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 |
select sname,sno from student;
+--------+-----+
| sname | sno |
+--------+-----+
| 曾华 | 101 |
| 匡明 | 102 |
| 王丽 | 103 |
| 李军 | 104 |
| 王芳 | 105 |
| 陆君 | 106 |
| 王尼玛 | 107 |
| 张全蛋 | 108 |
| 赵铁柱 | 109 |
+--------+-----+
select cname,cno from course;
+------------+-------+
| cname | cno |
+------------+-------+
| 计算机导论 | 3-105 |
| 操作系统 | 3-245 |
| 数字电路 | 6-166 |
| 高等数学 | 9-888 |
+------------+-------+
select sname,cname,degree from score,student,course where course.cno=score.cno and student.sno=score.sno;
+--------+------------+--------+
| sname | cname | degree |
+--------+------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+--------+------------+--------+

我TM就是个天才

select sname,cname,degree ,student.sno as stu_sno,course.cno as cou_cno from score,student,course where course.cno=score.cno and student.sno=score.sno;#student.sno as 语句需要学习
子查询

17.子查询加分组求平均分

查询“95031”班学生每门课的平均分

示例一，先进行拆分的表示

select * from student where class="95031";
mysql> select * from score where sno in(select sno from student where class="95031");#此处的in是非常值得学习的，筛出来的必须是同一样的
mysql> select cno,avg(degree) from score where sno in(select sno from student where class="95031") group by cno;#此处的group用的也非常棒

TM逻辑鬼才啊，反正就是抽丝剥茧，反复练习，看好最小范围

18.子查询

查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录。

select degree from score where sno="109" and cno="3-105";
select * from score where cno = "3-105" and degree>(select degree from score where sno="109" and cno="3-105");

19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

select degree from score where sno="109" and cno="3-105";
select * from score where degree>(select degree from score where sno="109" and cno="3-105");

20.查询学号为108、101的同学出生的所有学生的sno、sname和sbirthday列。

select * from student where sno="108" or sno ="101";

或者

select * from student where sno in("108",101);

只查年份

select year(sbirthday) from student where sno in("108",101);

进一步进行查询

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

21.查询“张旭”教师任课的学生成绩

select * from teacher where tname="张旭";#第一步
mysql> select cno from course where tno=( select tno from teacher where tname="张旭");#第二步
mysql> select sno,degree from score where cno=(select cno from course where tno=( select tno from teacher where tname="张旭"));#第三步

22.查询选修课程的同学多于2人的教师的姓名（计数）

示例一自我研发

select cno from score group by cno having count()>2;#第一步先找出选修多于2人的课程号
select tno from course where cno=(select cno from score group by cno having count()>2);#第二步找出联系
select tname from teacher where tno in( select tno from course where cno in (select cno from score group by cno having count(*)>2));#第三步

23.查询95033班和95031班全体学生的记录

slect * from student where class in("95033","95031");#我感觉这是在侮辱我智商

24.查询存在85分以上成绩的课程Cno;

select cno,degree from score where degree>85;

25.查询出“计算机系”教师所教课程的成绩表

select tno from teacher where depart="计算机系";#第一步先找出计算机系的
select cno from course where tno in (select tno from teacher where depart="计算机系");#第二步找出其中连接的关键词
select degree,cno from score where cno in ( select cno from course where tno in (select tno from teacher where depart="计算机系"));#最后得出最终的结果

26.查询“计算机系”与“电子工程系”不同职称的tname和port，审题有困难 not in/union

select prof from teacher where depart="电子工程系";
select * from teacher where depart="计算机系" and prof not in(select prof from teacher where depart="电子工程系") union select * from teacher where depart="电子工程系" and prof not in(select prof from teacher where depart="计算机系");#not in 和union用的挺好的

27 查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按degree从高到低排序。any的用法以及排序的用法。

select * from score where cno ="3-105" and degree > any(select degree from score where cno = "3-245")order by degree desc;

28.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、sno和degree.

且，all的用法

mysql> select * from score where cno ="3-105" and degree > all(select degree from score where cno = "3-245");

29.查询所有教师和同学的name、sex、和birthday

union以及as的应用

select sname,ssex,sbirthday from student;#先找出学生的
select tname,tsex,tbirthday from teacher;#在找出老师的
select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;#利用union进行合并
select sname as name,ssex as sex,sbirthday as birthday from student union select tname,tsex,tbirthday from teacher;#利用as对名称进行修改

30.查询所用“女”教师和“女”同学的name,sex,birthday

select sname as name,ssex as sex,sbirthday as birthday from student where ssex="女" union select tname,tsex,tbirthday from teacher where tsex="女";

复制表数据做条件查询

31.查询成绩比该课程平均成绩低的同学此的成绩表

select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);

这个a和b用的妙啊，

？？？where a.cno=b.cno不太明白

32.查询所有任课老师的Tname和depart

select tname,depart from teacher where tno in (select tno from course);

条件加分组筛蹄选

33.查询至少有2名男生的班号

select class from student where ssex="男" group by class having count()>1; #计数的语法不太熟悉，having count()>1返回表中几轮数大于1的记录数

not like模糊查询

34.查询student表中不姓“王”的同学记录

mysql> select * from student where sname not like "王%";

year逃数与now丞数

35.查询student表中每个学生的姓名和年龄

select year(now());#当前年份
select year(sbirthday) from student;#出生的年份
select sname,year(now())-year(sbirthday)as"年龄" from student;#加减乘除还是很有意思的

max与min函数

36.查询student表中最大和最小的sbirthday；

select sbirthday from student order by sbirthday;
select max(sbirthday) as "最小",min(sbirthday)as "最小" from student;

多字段排序

37.以班号和年龄的顺序查询student的全部记录

select * from student order by class desc,sbirthday;

38.查询“男”教师及所上的课程

select * from course where tno in (select tno from teacher where tsex="男");

max函数与子查询

39.查询最高分同学的sno、cno和degree列

select * from score where degree=( select max(degree)from score); #max用的挺好的

40.查询和“李军”同性别的所有同学的sname

select sname from student where ssex=(select ssex from student where sname="李军");

41.查询和“李军”同性别并同班的同学的sname

select sname from student where ssex=(select ssex from student where sname="李军") and class = (select class from student where sname="李军");

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

select degree from score where cno=(select cno from course where cname ="计算机导论")and sno in(select sno from student where ssex="男");

按等级查询

43.假设使用如下命令建立了一个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列

mysql> select sno,cno,grade from score,grade where degree between low and upp;#grade用的很棒

SQL的四种连接查询
连接的优点：可以不用通过创建外键，利用某个元素相等来求交集

内连接

inner join 或者 join
外连接

左连接 left join 或者 left outer join
右连接 right join 或者 right outer join
完全外连接 full join 或者 full outer join
create database testjoin;

person表

id,

name,

cardid

create table person(

id int,

name varchar(20),

cardid int);

card 表

id,

name

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(1,"李四",3);

insert into person values(1,"王五",6);

1.内联查询

两张表中的数据，通过某个字段相对，查询出相关记录数据inner join中inner可以省略，on是必须的

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

2.left join(左外连接)

会把左边表里的所有数据提取出来，而右边表的数据，如果相等的，就会显示出来；如果没有就会补 NULL

select * from person left （outer）join card on person.cardid=card.id;#outer可有可无

3.right join(右外连接)

会把右边表里的所有数据提取出来，而左边表的数据，如果相等的，就会显示出来；如果没有就会补 NULL

select * from person right （outer）join card on person.cardid=card.id;

4.full join(全外链接)

select * from person full join card on person.cardid=card.id;

mysql不支持full join,其等于

select * from person left join card on person.cardid=card.id union select * from person right join card on person.cardid=card.id;