MYSQL-----单表查询和多表查询（一）

目录

 

1.创建数据库

2.使用数据库

3.创建表

a.专业表

b.学生表

c.课程表

d.选课表

e.教师表

f.教师教书表

4.插入数据

5.单表查询

6.多表查询

---

 

1.创建数据库

create database studentinfo;

2.使用数据库

use studentinfo；

3.创建表

a.专业表

//专业表
create  table specialty(
 zno varchar(4) primary key not null unique key,
 zname varchar(50) not null
 );
//显示表
 desc specialty;

b.学生表

//创建学生表 
create table student( 
 sno varchar(20) primary key not null ,
 sname varchar(50) not null,
 sex enum('男','女') not null,
 sbirth date,
 sclass varchar(50) not null,
 zno varchar(4) not null
 );
//显示表
 desc student;

c.课程表

//创建课程表
create table course(
 cno varchar(8) primary key,
cname varchar(20) not null,
credit tinyint not null,
zno varchar(4)
);
//显示课程表
desc course;

d.选课表

//创建选课表
create table sc(
sno varchar(20) not null ,
cno varchar(8) not null,
grade float(4.1),
primary key(sno,cno)
);
//（主键之间连接）我们把连接其他表之间的键称为外键
alter table  sc add foreign key (cno) references course(cno);
alter table sc add foreign key (sno) references student(sno);
//显示选课表
desc sc;

e.教师表

create table  teacher(
tno varchar(10) not null primary key unique key ,
Tname varchar(50) not null,
Tbirthday date,
Title varchar(20),
zno varchar(4)
);
alter table teacher add foreign key(zno) references specialty(zno);
desc tescher;

f.教师教书表

create table tc(  
tno varchar(10) not null ,
cno varchar(8) not null,
primary key(tno,cno)
);
//外键
alter table tc add foreign key(tno) references teacher(tno);
alter table tc add  foreign key(cno) references course(cno);
desc tc;

4.插入数据

//专业表
insert into specialty
values 
      ('z001','计算机科学与技术'),
      ('z002','网络工程'),
      ('z003','生物工程'),
      ('z004','应用统计'),
	  ('z005','自动化');
select*from specialty;

//插入课程表数据
insert into course
values  
      ('c01','数据库原理','2','z001'),
	  ('c02','jsp','3','z004'),
	  ('c03','数据结构','2','z005'),
	  ('c04','操作系统','2','z003'),
      ('c05','毛概','3','z003'),
      ('c06','大学英语','2','z005'),
      ('c07','数字逻辑','2','z001'),
      ('c08','马原','3','z001'),
      ('c09','高等数学','2','z001'),
      ('c10','线性代数','2','z001'),
     ( 'c11','计算机网络','2','z001');
select *from course;

//学生表
insert into student 
values 
      ('210401010145','刘电脑','女','2002.10.22','21计科1班','z001'),
	  ('210401010246','刘小胜','男','2004.10.22','21网工2班','z002'),
	  ('210401010347','刘小电','女','2001.1.22','21生环1班','z003'),
	  ('210401010401','刘小小','女','1998.1.22','21统计2班','z004'),
	  ('210401010548','刘大大','男','1999.1.22','21自动化2班','z005');

//选课表
insert into sc
values
('210401010145','c01',45.0),
('210401010145','c07',90.0),
('210401010145','c08',50.0),
('210401010145','c09',85.0),
('210401010246','c04',78.0),
('210401010246','c02',80.0),
('210401010246','c01',95.0),
('210401010246','c09',80.0),
('210401010347','c03',89.0),
('210401010347','c04',59.0),
('210401010347','c05',41.0),
('210401010347','c01',96.0),
('210401010347','c09',41.0),
('210401010347','c02',55.0),
('210401010347','c06',75.0),
('210401010347','c07',88.0),
('210401010347','c08',77.0),
('210401010548','c06',40.0),
('210401010548','c03',90.0),
('210401010548','c01',90.0),
('210401010401','c01',79.0),
('210401010401','c04',70.0);

//教师表
insert into teacher
values
      ('t101','章三','2002.10.22','计算机老师','z001'),
      ('t202','叶小子','2003.2.22','网络工程老师','z002'),
      ('t303','刘小','2000.1.22','生物工程老师','z003'),
	  ('t104','夏雨','2002.10.22','自动化老师','z005'),
	  ('t205','袁晴','2003.2.22','应用统计老师','z004'),
      ('t306','叶西','2000.1.22','计算机老师','z001');
select *from teacher;

//教师教书表
insert into tc
values
      ('t101','c01'),
      ('t101','c03'),
      ('t202','c02'),
      ('t202','c04'),
      ('t303','c08'),
	  ('t303','c07'),
	  ('t104','c03'),
	  ('t104','c04'),
	  ('t205','c01'),
	  ('t205','c06'),
	  ('t306','c02');
select *from tc;

5.单表查询

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

方法一：用”*“。
select *from student;
方法二：列出所有的列名。
select sno,sname,sex,sbirth,sclass,zno from student;

2.查询 student 表的第二条到第四条记录。

select *from student limit 1,3;

3.从student 表查询所有学生的学号、姓名和班级的信息。

select sno,sname,sclass from student;

4.查询“计科”和“网工”班的学生的信息。

方法一：使用 IN 关键字
select* from student where sclass in('21计科1班','21网工2班');
方法二：使用 OR 关键字
select* from student where sclass  like '%计科%'or  sclass  like '%网工%';

5.从 student 表中查询年龄为 18 到 22 岁的学生的信息。

方法一：使用BETWEEN AND 关键字来查询
Select sno,sname,sex,(year(curdate())-year(sbirth)) 
as age,sclass,zno from student   
where sbirth between '2001.1.1 'and '2004.12.31';
方式二：使用 AND 关键字和比较运算符。
select sno,sname,sex,(year(curdate())-year(sbirth)) as age,sclass,zno
 from student where sbirth >='2001.1.1' and sbirth<='2004.12.31';

6.student 表中查询每个班有多少人，为统计的人数列取别名 sum_of_class。

 select  sclass  as'班级',count(*) as'sum_of_class' from student group by sclass;

7.查询各专业的男、女生人数，查询结果以“专业号”、“性别”、“人数”显示。

select zno as '专业号',sex'性别',count(*) '人数' from student group by zno,sex;

8.统计学号为”210401010145”同学的平均分、最高分、最低分。

Selectsno,min(grade),max(grade),avg(grade)from sc 
where sno='210401010145';

9.统计所有同学的平均分、最高分、最低分。

select sno,min(grade),max(grade),avg(grade) from sc group by sno;

10.统计所有同学中所有成绩都及格的学生的平均分、最高分、最低分，按平均成绩的降序排序。

select sno, min(grade),max(grade),avg(grade)from sc
 group by sno having  min(grade)>=60 order by avg(grade) desc;

11.计算每个考试科目的平均成绩，列出平均成绩前3条的记录

select distinct cno,avg(grade) from sc group by cno 
order by avg(grade) desc limit 3;

6.多表查询

1.查询“刘电脑”的考试科目（cname）和考试成绩（grade）。

方法一：子查询
select A.sno,cname,grade from sc A,course B, student C 
where C.sno=A.sno and B.cno=A.cno and sname='刘电脑';
方法二：连接查询
select sname, cname,grade from student natural join
 sc natural join course where sname='刘电脑';

2.用连接查询的方式查询所有学生的信息和考试信息。

select sno ,sname ,sex ,sbirth ,sclass , zno,cno,cname from student
 natural join sc  ;

3.将数据库原理成绩按从高到低进行排序。

select cname,grade from course natural join sc 
where cno='c01' order by grade ;

4.查询数据库原理成绩高于 95 的学生的学号、姓名、成绩，以降序排序。

select sno,sname,grade from student natural join sc 
where cno='c01'and grade>='95' order by grade desc;

5.查询参加高等数学和英语考试的学生的姓名、科目和成绩信息。

SELECT sname,cname,grade FROM sc join student on sc.sno=student.sno
join course on course.cno=sc.cno 
WHERE cname='大学英语' or cname='高等数学';

6.查询学生的学号、姓名、课程名和平均成绩。

select b.sno,a.sname,c.cname,avg(grade) from student a,sc b,course c
  where c.cno=b.cno and b.sno=a.sno group by b.sno,a.sname,c.cname;

7.查询姓刘*的同学的姓名、专业、考试科目和成绩。

select sname,zno,cname,grade from  student
natural join sc natural join  course 
 where  sname like '刘%';

8.查询计算科学与技术专业的同学的姓名、年龄、专业、班级、考试科目和成绩。

Select sname,timestampdiff(year,date(sbirth),curdate())as age,zno,sclass,cname,
grade from student natural join sc natural join course natural join specialty 
where zname='计算机科学与技术';

9.查询所有学生的学号、姓名、年龄。

select sno,sname,timestampdiff(year,date(sbirth),curdate()) as age from student;

10.查询所有没有被选修的课程编号、课程名称。

select cno,cname from course where not exists
(select *from  student where 
exists(select *from sc where student.sno=sc.sno and sc.cno=course.cno));

11.查询“刘*”老师所教的课程名称

select cname from course where exists 
( select * from teacher where exists
(select *from tc where tc.cno=course.cno 
and teacher.tno=tc.tno and tname like '刘_'));

12.统计“章**”老师所教课程每门课的“课程号、课程名、平均分”。

select D.cno,B.cname,avg(grade)
from teacher A,course B,SC C,tc D
where A.tno=D.tno AND B.cno=D.cno AND C.cno=D.cno and A.tname like '章%'
group by D.cno,B.cname;

13.查询“刘*”老师所教课程中，不及格同学的成绩记录。

select  E.sname,D.cno,B.cname,C.grade
from teacher A,course B,SC C,tc D,student E
where A.tno=D.tno AND B.cno=D.cno AND C.cno=D.cno and A.tname like '刘%' and C.sno=E.sno and C.grade<60
group by E.sname,D.cno,B.cname,C.grade;

14.查询选修了所有课程学生的信息：学号、课程号。

select distinct sno,sc.cno from student natural join sc  where not exists(select*from course where not exists 
  (select *from sc where sc.sno=student.sno and sc.cno=course.cno));

1. 聚集函数能否直接使用在 SELECT 子句、HAVING 子句、WHERE 子句、GROUP BY 子句中？

     聚集函数 可用在select和having by中，where是在聚集函数计算前筛选数据，

     Having by是在聚集函数计算后筛选数据。

  2.WHERE 子句与 HAVING 子句有何不同？

1. Where子句通常单独使用，而having子句一般和group by子句使用
2. Where子句不能使用集合函数，而having子句可以
3. Where子句作用于表，选择满足条件的记录，having子句作用于组，选择满足条件的组