Mysql建库建表及查询

2022-12-18 13:26:49

　　　　作为测试人员最常用的数据库查询语句，本篇博文做了一个具体的总结，以下只是为了概述查询方法而举的例子，并不一定是最好的查询方式。

　　红色标记的地方是该查询语句中重点想要表达的查询方法。

 

 

一. 新建一个数据库test: create database test;

 Mysql建库建表及查询

 

二. 依次创建三个表：学生表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;

 Mysql建库建表及查询

 

因为以上查询结果中，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

 Mysql建库建表及查询

 

查询命令：

select FIRST.cno,SECOND.cpno from course first,course SECOND where `first`.cpno=`SECOND`.Cno ;

查询后结果：

 Mysql建库建表及查询

 

4.查询每个学生的学生姓名，学号，选修课程名及成绩（多表连接）

select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno

and sc.cno=course.cno;

 Mysql建库建表及查询

 

 

七. 细说数据库查询——嵌套查询（一个查询块嵌套在另一个查询块的条件中）

1.查找所有跟“刘晨”同 一个系的学生

  • 先查找出刘晨所在系：select sdept from student where sname="刘晨"
  • 然后查找出在以上系的学生：select sno,sname from student where sdept=(select sdept from student where sname="刘晨")

查询前：

 Mysql建库建表及查询

 

查询后：

 Mysql建库建表及查询

 

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;

 

 

 

上一篇：数据笔试题


下一篇：数据库高级数据库学习--上机练习9-2（触发器）