【Mysql学习笔记（四）】

数据准备

学生表
Student：学号、姓名、性别、出生年月日、所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);

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-02-20’,‘95033’);
INSERT INTO student VALUES(‘108’,‘张全蛋’,‘男’,‘1975-02-10’,‘95031’);
INSERT INTO student VALUES(‘109’,‘赵铁柱’,‘男’,‘1974-06-03’,‘95031’);

教师表
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 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’,‘助教’,‘电子工程系’);

课程表
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)
);
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’);
成绩表
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)
);

查询练习

1.查询student表中所有的记录
select * from student;
2.查询student表中所有记录的s_name,s_sex和s_class列
select sname,ssex,class from student;
3.查询教师所有的单位但是不重复的t_depart列
select distinct(depart) from teacher;
4.查询score表中成绩在60-80之间所有的记录(sc_degree)
查询区间： between …and…
select*from score where degree between 60 and 80;
使用运算符比较：
mysql> select * from score where degree>60 and degree<80;
5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
select *from score where degree in(85,86,88);
select from score where degree=85 or degree=86 or degree=88;
6.查询student表中’95031’班或者性别为’女’的同学记录
select * from student where class=‘95031’ or ssex=‘女’;
7.以class降序查询student表中所有的记录
select * from student order by class desc;
8.以c_no升序.sc_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,degree from score order by degree desc limit 0,1;
limit 第一个参数表示从哪一条开始，第二个参数表示查多少条

分组计算平均成绩

查询每门课的平均成绩：
select avg(degree) from score where cno=‘3-105’;
select avg(degree) from score where cno=‘3-245’;
select avg(degree) from score where cno=‘6-166’;
select avg(degree) from score where cno=‘9-888’;

用group by将四个查询合并：
用group by将score表分成子表，对每个子表进行查询
select cno,avg(degree) from score group by cno;

分组条件和模糊查询

查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
select cno,avg(degree),count(*) from score group by cno having count(cno>=2) and cno like ‘3%’;

范围查询的两种方式

查询分数大于70但是小于90的s_no列:
select sno,degree from score where degree >70 and degree<90;

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

多表查询

查询所有的学生 sname , cno, degree列
select sname,cno,degree from student,score where student.sno=score.sno;

查询所有学生的sno, cname, degree列
select sno,cname,degree from score,course where score.cno=course.cno;

查询所有的学生 sname , cname, degree列
select sname,cname,degree from student,score,course where student.sno=score.sno and score.cno=course.cno;

子查询

查询班级是’95031’班学生每门课的平均分
select cno,avg(degree) from score where sno in(select sno from student where class=‘95031’) group by cno;

查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录
select * from score where cno=‘3-105’ and degree >(select degree from score where cno=‘3-105’ and sno=‘109’);
查询所有学号为108.101的同学同年出生的所有学生的sno,sname和sbirthday
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
查询 张旭 教师任课的学生的成绩
select * from score where cno = (select cno from course where tno=(select tno from teacher where tname=‘张旭’));
查询选修课程的同学人数多余 5 人的教师姓名
select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(*) >5));

查询所有任课教师的t_name 和 t_depart(要在课程表中可以查得到)

查询95033班和95031班全体学生的记录
select * from student where class in (‘95033’,‘95031’) ;
查询存在85分以上成绩的课程c_no
select distinct(cno) from score where degree > 85;

select tno from teacher where depart =‘计算机系’;
select cno from course where tno in (select tno from teacher where depart =‘计算机系’);
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart =‘计算机系’));

union求并集

查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof
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 =‘计算机系’);
查询所有教师和同学的 name ,sex, birthday
select tname as name,tsex as sex, tbirthday as birthday from teacher union select sname,ssex,sbirthday from student;
查询所有’女’教师和’女’学生的name,sex,birthday
select tname as name,tsex as sex ,tbirthday as birthday from teacher where tsex=‘女’ union select sname ,ssex,sbirthday from student where ssex =‘女’;

any

查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
select * from score where cno=‘3-105’ and degree > any(select degree from score where cno=‘3-245’) order by degree desc;

all

查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
select * from score where cno=‘3-105’ and degree > all (select degree from score where cno=‘3-245’) order by degree desc;

复制数据做条件查询

查询成绩比该课程平均成绩低的同学的成绩表
select * from score a where degree > (select avg(degree) from score b where a.cno=b.cno );

条件加分组筛选

查出至少有2名男生的班号
select class from student where ssex=‘男’ group by class having count(*)>1;

not like

查询student 表中 不姓"王"的同学的记录
select * from student where sname not like ‘王%’;

year 函数与now函数

查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
select sname,year(now())-year(sbirthday) as ‘年龄’ from student;

查询student中最大和最小的 s_birthday的值
select max(sbirthday) as ‘最大’,min(sbirthday) as ‘最小’ from student;

以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,sbirthday;

子查询1

查询"男"教师 及其所上的课
select * from course where tno in (select tno from teacher where tsex=‘男’);
查询最高分同学的sno cno 和 degree;
select * from score where degree = (select max(degree) from score );
查询和"李军"同性别的所有同学的s_name
select sname from student where ssex=(select ssex from student where sname=‘李军’);
查询所有选修’计算机导论’课程的’男’同学的成绩表
select degree from score where cno=( select cno from course where cname=‘计算机导论’) and sno in (select sno from student where ssex=‘男’) ;
查询所有同学的s_no , c_no 和grade列
select sno,cno,grade from score,grade where degree between low and upp;

四种连接查询

先创建两个表：
create table person(
id int,
name varchar(20),
cardId int
);
insert into person values(1,‘张三’,1);
insert into person values(1,‘李四’,3);
insert into person values(1,‘王五’,6);
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,‘邮政卡’);

并没有创建外键：

内连接

inner join 或者join
select * from person inner join card on person.cardId=card.id;
内连接其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据。

外连接

左连接 left join 或者 left outer join
select * from person left join card on person.cardId=card.id;
左连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来，如果没有，就会补null。
right join 或right outer join
select * from person right join card on person.cardId=card.id;
右连接会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
如果没有，就会补null。

完全外连接 full join 或者 full outer join(mysql不支持全外连接)
select * from person full join card on person.cardId=card.id;
mysql不支持全连接
如果想要全连接，
select * from person left join card on person.cardId=card.id union select * from person right join card on person.cardId=card.id;