MYSQL数据库查询
作为测试人员最常用的数据库查询语句,本篇博文做了一个具体的总结,以下只是为了概述查询方法而举的例子,并不一定是最好的查询方式。
红色标记的地方是该查询语句中重点想要表达的查询方法。
一. 新建一个数据库test: create database test;
二. 依次创建三个表:学生表student,课程表course,学生选课表sc
create table student
(
sno char(9) primary key, #学号
sname char(20) unique, #学生姓名
ssex char(2), #学生性别
sage smallint, #学生年龄
sdept char(20) #学生所在系
);
create table course
(
cno char(4) primary key, #课程号
cname char(40) not null, #课程名称
cpno char(4), #先修课,学这门课之前要先修的课程
ccredit smallint, #学分
foreign key (cpno) references course(cno) #外键
);
create table sc
(
sno char(9) , #学号
cno char(4) , #课程号
grade smallint, #成绩
primary key(sno,cno),
foreign key (cno) references course(cno),
foreign key (sno) references student(sno)
);
三. 对表结构进行增删改(以student表为例)
1.增加表字段
alter table student add column s_entrance date;
2.删除表字段
alter table student drop column s_entrance;
3.修改字段类型
alter table student modify column sage int;
4.修改字段名字(需重新定义该字段类型)
alter table student change sage snew varchar(5) comment“修改的字段”;
5.删除外键
alter table course drop foreign key course_ibfk_1;(course_ibfk_1为自动生成的外键id)
四. 对表的值进行增删查改(以student表为例)
1.新增student值:
insert into student (sno,sname,ssex,sage,sdept) values ("20190101","李亮","男",20,"cs");
insert into student (sno,sname,ssex,sage,sdept) values ("20190102","李白","男",19,"ma");
insert into student (sno,sname,ssex,sage,sdept) values ("20190103","刘晨","女",20,"is");
当该表所有字段都给了值的情况下,也可以省略字段,默认给所有字段插入值,eg:
insert into student values ("20190101","李亮","男",20,"cs");
2.新增course表
由于建表的时候,我们将cpno字段作为cno的外键了,所以在建表的时候,同时插入这两个字段的值,cpno字段会先判断cno是否有值,如果没有就会报错:a foreign key constraint fails 。
我们可以通过先删除外键再添加外键的方式插入数据。
insert into course values ("1","数学","3",4);
insert into course values ("2","语文","5",3);
insert into course values ("3","英语","2",4);
3.新增sc表
insert into `sc` values ('20190101', '2', 90);
insert into `sc` values ('20190102', '2', 88);
insert into `sc` values ('20190102', '3', 92);
insert into `sc` values ('20190103', '1', 59);
4.删除表的所有记录(student表为例)
delete from student;
5.删除学号为20190101的记录
delete from student where sno="20190101";
6.查询表的所有记录
select * from student;
7.修改刘晨学生的年龄
update student set sage=19 where sname="刘晨";
五. 细说数据库查询-单表查询
1.查询学生表的所有记录
select * from student;
2.查询学生表的所有学号
select sno from student;
3.查询出的是学生的出生年份
select 2019-sage from student;
4.对查询学号重命名
select sno as newname from student;
5.查询所有课程号并去重
select distinct cno from sc;
6.查询大于等于60分的记录
select * from sc where grade>=60;
7.查询60-70分之间的记录
select * from sc where grade between 60 and 70;
8.查询在ma和is系的学生姓名和学号
select sname,sno from student where sdept in("ma","is");
9.查询姓刘的学生的详细情况
select * from student where sname like "刘%";
10.查询名字姓刘且姓名是两个字的学生的详细情况
select * from student where sname like "刘_";
11.查询成绩是空的学生的记录
select * from sc where grade is null;
12.查询成绩不为空的学生的记录
select * from sc where grade is not null;
13.查询ma系或者is系的且年龄在20岁以下的学生姓名
select sname from student where sname=20 and sdept="ma" or sdept="is" ;
14.查询出学生表的所有记录并按照年龄的升序排序(降序用desc代替asc)
select * from student order by sage asc;
15.统计学生个数
select count(*) from student ;
16.统计所有课程中最高分,平均分,最低分min,总分
select max(grade),avg(grade), min(grade),sum(grade) from sc ;
17.统计各个课程号及相应的选课人数
select cno,count(cno) from sc group by cno ;
六. 细说数据库查询——连接查询(一个查询涉及两张表)
1.查询每个学生及其选修课程的课程号(等值连接)
select student*,sc* from student ,sc where student.sno=sc.sno;
因为以上查询结果中,sno和sno1是重复的列,查询的时候只查询其中一个就可。这样的查询叫做自然连接。
2.查询每个学生及其选修课程大于等于90分的课程号
select student*,sc* from student ,sc where student.sno=sc.sno and sc.grade>=90;
3.查找每门课的间接先修课
course表的内容如下,可看出,课程1数学的先修课是课程3英语,英语的先修课是课程2语文。
也就是课程1的间接先修课是课程2语文。我们可以将course表自身连接查询,也就是将表取两个别名:first,second
查询命令:
select first.cno,second.cpno from course first,course second where `first`.cpno=`second`.cno ;
查询后结果:
4.查询每个学生的学生姓名,学号,选修课程名及成绩(多表连接)
select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno
and sc.cno=course.cno;
七. 细说数据库查询——嵌套查询(一个查询块嵌套在另一个查询块的条件中)
1.查找所有跟“刘晨”同 一个系的学生
- 先查找出刘晨所在系:select sdept from student where sname="刘晨"
- 然后查找出在以上系的学生:select sno,sname from student where sdept=(select sdept from student where sname="刘晨")
查询前:
查询后:
2.找出每个学生超过他自己选修课程的平均成绩的课程号
select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno);
3.查询非is系中比is系中任意一个学生年龄都小的学生年龄和姓名
select sname,sage from student where sage<any(select sage from student where sdept="is") and sdept<>'is';
4.查询所有选修了1号课程的学生姓名
select sname from student where exists(select * from sc where sno=student.sno and cno='1');
八. 细说数据库查询——集合查询
1.查询“is”系的所有学生和年龄不大于19岁的学生的并集
select * from student where sdept='is' union select * from student where sage<=19;
2.查询“is”系的所有学生和年龄不大于19岁的学生的交集
select * from student where sdept='is' intersect select * from student where sage<=19;
3.查询“is”系的所有学生与年龄不大于19岁的学生的差集
select * from student where sdept='is' except select * from student where sage<=19;
九. 细说数据库查询——基于派生表的查询
子查询出现在from子句中,生成的是临时的派生表
1.查询每个学生超过他自己选修课程的平均成绩的课程号
select sc.sno,sc.cno from sc,(select sno,avg(grade) as avg_grade from sc group by sno)as x
where x.sno=sc.sno and sc.grade>=x.avg_grade;