mysql表的查询（连接查询）练习

需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称 show create table score;

 

获取名称之后就可以根据名称来删除外键约束

alter table score drop foreign key 加外键名称;

 建库：
 create database sql_select charset=utf8
 use sql_select

 

一、建四个表

由于外键关系，建表要按顺序

1、课程表：Course

课程号	课程名称	教师编号
cno	cname	tno

create table course( cno varchar(20) primary key, cname varchar(20) not null , tno varchar(20) not null );

2、成绩表： Score

学号	课程号	成绩
sno	cno	degree

create table score( sno varchar(20) not null primary key , cno varchar(20) not null, degree decimal );

3、教师表：Teacher

教师编号	教师名字	教师性别	出生年月日	职称	所在班级
tno	tname	tsex	tbirthday	pro	depart

create table teacher( tno varchar(20) primary key, tname varchar(20) not null, tsex varchar(20) not null, tbirthday datetime, pro varchar(20) not null, depart varchar(20) not null );

 

4、学生表：student

学号	姓名	性别	出生年月日期	所在班级
sno	sname	ssex	sbirthday	class

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

);

二、插入数据

三、查询

查询练习:

 1，查询student表中的所有记录
 select * from student;
 ​
 2，检查student表中的所有记录的sname，Ssex和class。
 select sname，ssex，class from student
 ​
 3、查询老师所有的单位即不重复的depart列
 select distinct depart from teacher;
 ​
 4.查询socre表中成绩在从68到80之间的记录。
 select * from score where degree between 60 and 80;
 ​
 5.查询score表成绩中为85、86或88。-
 select * from score where degree in(85,86,88);
 ​
 6、查询student表中“95031”班或性别为“女”同学的同学记录
 select * from student where class="95031" or ssex="女";
 ​
 7.以class降序查询student表的所有记录
 select * from student order by class desc;#asc
 ​
 8.以cno升序。degree降序查询score表的所有记录
 select *from score order by cno asc,degree desc;
 ​
 9、查询“95031”班学生人数。
 select count(*) from student where class="95031"
 ​
 10、查询score表中的最高分的学生学号和课程号（子查询或者排序）
 select sno,cno from score where degree=(select max(degree) from score);
 select sno,cno from score order by degree desc limit 0,1;
 ​
 11.查看每门课的平均成绩 (分组查询)
 select * from course;
 select avg(degree) from score where cno="3-105"
 select avg(degree) from score where cno="1-105"
 select avg(degree) from score where cno="6-166"
 select avg(degree) from score where cno="3-245"
 select degree from score where cno="3-105"
 select cno,avg(degree) from score group by cno;
 ​
 ​
 12.查询score表中至少有2名同学选修的并以3开头的课程的平均分数 havimg(组级过滤)
  select cno,avg(degree),count(*) 
  from score 
  group by cno
  having count(cno)>=2 and cno like '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,con,和degree 列。（多表查询）
 select sno,sname from student;
 select cno,degree from score;
 select sname,cno,degree from student inner join score on student.sno=score.sno;
 ​
 ​
 ​
 15.查询所有学生的sno，cname和degree
 select cno,cname from course;
 select cno,sno,degree from score;
 select sno,cname,degree from course,score where course.cno = score.cno;
 ​
 ​
 连接查询  第二种方法
 select sname,cno,degree from student inner join score
 on student.sno=score.sno;
 ​
 ​
  16. 查询所有学生的sname，cname和degree(三表联查)
  
  sname => student
  cname => course
  degree => score
 ​
 select sname,cname,degree from student,course,score
 where student.sno=score.sno 
 and course.cno=score.cno;
 ​
 ​
 第二种 方法 内连接查询 
 select sname,cname,degree from student inner join course inner join score
 on student.sno=score.sno 
 and course.cno=score.cno;
 ​
 ​
 select sname,cname,degree,student.sno as 
 stu_sno,score.sno,course.cno as cou_cno,score.cno 
 from student,course,score
 where student.sno=score.sno 
 and course.cno=score.cno;
 ​
 ​
 17.查阅“95031"班学生每门课的平均分
 select * from student where class="95031";
 select * from score where sno in (select sno from student where class="95031");
 select cno,avg(degree) from score
 where sno in (select sno from student where class="95031")
 group by cno;
 ​
 ​
 ​
 18.查询选修”3-105“课程的成绩高于”109“号同学”3-105“成绩的所有同学的记录
 子查询的练习
 ​
 1.先查询”109“号同学”3-105“成绩
 select degree from score where sno='109' and cno="3-105";  
 ​
 ​
 2.第二步找到3-105课程成绩高于109
 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 * from score where degree>(select degree from score where sno='109' and cno="3-105");
 ​
 ​
 ​
 20.查询和学号为108.101的同学同年出生的所有学生的son。sname和sbirthday
 year的函数与带in 关键字的子查询
 ​
 #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.查询”张旭“教师任课的学生成绩
 写这个的时候先理清表之间的关系  想好我们到底要查的是什么数据 在对表中的关系进行处理
 ​
 1.先查询教师的id号
 select tno from teacher where tname="张旭";
 ​
 2.根据教师的id号来查询课程id
 select cno from course where tno=(select tno from teacher where tname="张旭"); 
 ​
 3。查询score表根据课程号查询学生的分数
 select * from score where cno=(select cno from course where tno=(select tno from teacher where tname="张旭"));
 ​
 没有创建外键

 

 

连接查询 内连接 外连接 左连接 右连接

---

 

mysql支持三种类型的连接查询，分别为：

内连接查询：查询的结果为两个表匹配到的数据 select 字段 from 表名1 inner join 表名2 on 关联条件

右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

 

 

示例

1. 建表 person ：

   id	name	cardid

2. 建表：card

   id	name

3. 输入数据：

   1	饭卡
   2	建行卡
   3	农行卡
   4	工商卡
   5	邮政卡

    

    

   用户表：

   1	张三	1
   2	李四	3
   3	王五	6

4. 查询数据

   inner join查询

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

   -- 内连接查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据

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

    

   left join（左外连接查询）

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

    

   左外连接查询，会把左边表里面的所有数据取出来，而右边表中的数据，如有相等的，就显示出来，如果没有，就会补null。

    

   右外连接查询

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

   -- 右外连接查询，会把右边表里面的所有数据取出来，而左边表中的数据，如有相等的，就显示出来

   -- 如果没有，就会补null

    

   full join（全外连接查询，mysql不支持此查询方式）

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