14 、软件测试-MySQL的基本使用方法-增删改查

时间：2021/7/5
版本：1.0
作者：正正得郑
描述：第一个版本的测试使用数据库查询文档，包括主要的增删改查。以及查询的主要语句使用方法。尚有不足，后期使用再额外增加。

一 1、数据准备：

创建student和score表
CREATE  TABLE  student (id  INT(10)  NOT NULL  PRIMARY KEY  ,name  VARCHAR(20)  NOT NULL ,sex  VARCHAR(4)  ,birth  YEAR,department  VARCHAR(20) ,address  VARCHAR(50) );

创建score表，SQL代码如下：
CREATE  TABLE  score (id  INT(10)  NOT NULL  PRIMARY KEY AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,c_name  VARCHAR(20) ,grade  INT(10));

为student表和score表增加记录

向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1984,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1987,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1991,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1993,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1990,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1989,'计算机系', '湖南省衡阳市');
INSERT INTO student VALUES( 907,'老七', '男',1991,'计算机系', '广东省深圳市');
INSERT INTO student VALUES( 908,'老八', '女',1990,'英语系', '山东省青岛市');

向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
INSERT INTO score VALUES(NULL,907, '计算机',98);

二、数据库的创建

查询所有数据库：show databases；
创建数据库：create database  <数据库名>；
删除数据库：drop database  <数据库名>；
进入数据库：use  <数据库名>；

三、数据表的操作

1）查询数据库下表：show tables;
2）创建表：create table student(id int(4) primary key,name char(20));
注释： id为表的第一列；     
int数字类型；  
primary key主键的意思，列不能重复。
       Name为表的第二列名字。  
char:类型;  
创建表：create table score(id int(4) not null,class int(2));
注释： not null字段不能为空。

创建表：create table student1(id int(4) not null,name char(20));
Field (列名)，Type（字段类型），null（是否为空），key（主键）

3）查看表结构：describe  student;    或      desc  student;
4）修改表名：alter table <表名> rename <表名>;
5）删除表：drop table <表名>;
6）修改表字段信息：alter table student change id id int(20);
7）增加表字段信息：alter table student1 add class int(4) not null after id;
8）删除一个表字段：alter table student1 drop number;

四、表数据的增删查改

4.1、查询语句–基本

select 属性名 from 表名 where 条件
mysql> select * from score where class=1 or class=2; 
查询1班与2班的成绩信息
mysql>  select * from score limit 3,4;  
 显示4到7行的数据

4.2、增加语句

INSERT INTO 表名(字段名) VALUES(数据)  where 条件
mysql> insert into score(class,number,maths,chinese,english) values(4,20,98,88,68);
插入数据，属性名对应后面的数据
mysql> INSERT INTO score VALUES(NULL,907, '计算机',98);
插入数据，后面的数据一一对应数据表属性名的排序；

4.3、修改语句

update 表名 set  where 条件
mysql> update student set birth=1988,department='中文系' where id=901 and name='张老大';
把张老大的出生日期修改为1988，院系修改成中文系
mysql> update student set birth=birth-5;
把所有学生的年纪增加5岁；

4.4、删除语句

mysql> delete from student where id=901;
删除901同学的，学生信息
mysql> delete from student where address like "湖南%";
删除湖南籍学生的信息
mysql> delete from student;
清空学生表信息

五、查询进阶

（1）where 与or 、 和and

1.1、查询901与903学生的成绩信息：
mysql> select * from score2 where stu_id=901 or stu_id =903; 
1.2、查询学号为901并且计算机科目的成绩信息：
mysql> select * from score2 where stu_id=901 and c_name='计算机';  
注释：只要不是数字，有汉字数字字母多种组成的形式都要加单引号，表示字符串。

（2）where 字段名A存在（in）B中、字段名A不存在（not in）B中

2.1、查询901,902,903的成绩信息：
mysql> select * from score2 where stu_id in (901,902,903);
2.2、查询901除外其他学生的成绩信息: 
mysql>select * from score2 where stu_id not in (901,903);

（3）where 字段名A=xx、字段名A!=xx

3.1、查询出学生姓名李四的学生信息：
mysql> select * from student2 where name='李四';
3.2、查询901除外其他学生的成绩信息: 
mysql> select * from score2 where stu_id!=901;   
注释：  !在数据库里面为否定的意思:

（4）where 字段名A>=xx、字段名A<=xx

4.1、查询成绩大于等于80小于等于90的成绩信息：
mysql>select * from score2 where grade>=71 and grade <=90;

（5）where 在…之间 between

5.1、查询901到903的学生成绩信息: 
mysql> select * from score2 where stu_id between 901 and 903;
注释： between：在```之间，中间的意思

（6）去除重复 distinct

6.1、按照科目名称去重，显示全部科目名称:
mysql> select distinct c_name from score2;     
注释： distinct 去除重复的意思；

（7）从第a行开始，返回b条信息 limit a,b

7.1、显示4到7行的数据:
mysql> select * from score2 limit 3,4;  
注释：数据库数据排列：0，1，2，3；  3显示第4行； 4，5，6，7共有4行；   3，4 ；  
3表示第4行，4表示从第3行开始到第7行，共有4行；

（8）分组查询 group by 属性名

 8.1、分组查询每个系的人数：
mysql> select department,count(*) from student2 group by department;

温馨提示：分组之后查询其他函数结果是不正确的；

分组函数：group by
8.2、按科目分组，查询出每个科目最高分：
mysql> select c_name,max(grade) from score2 group by c_name;
8.3不分科目查询最高分: 
mysql> select max(grade) from score2;
注释: max:最大值;

参考：
按班级分组，查询出每班数学最低分：
select class,min(maths) from score group by class;
注释:最小值min； 

按班级分组，查询出每班数学总分：
select class,sum(maths) from score group by class;
注释：sum：总分；

按班级分组，查询出每班数学平均分：
select class,avg(maths) from score group by class;
注释：avg：平均值：

按班级分组，查询出每班学生总数：
select class,count(*) from score group by class;
注释：count:有价值的；

语句执行顺序:  
 from先执行，后执行where, 再接着执行having,limit等。
例句：
select class,max(maths) from score where group by(分组) class having(所有) order by(排序) limit 
            from后面可以加兹查询，语句先执行后面再执行前面

（9）函数查询：（*重点）

1、排序数据，order by 字段名 ASC(不加后缀默认，升序方式)，order by 字段名 DESC(降序)

9.1、按grade(分数列小到大)排序数据: 
mysql> select * from score2 order by grade;

部分可参考（8）分组查询
函数查询表：

函数名	属性
最大值	max(xx)
最小值	min(xx)
总分	sum(xx)
平均值	avg(xx)
有值的	count(xx)

（10）数学运算符：（*重点）

查询的结构直接加
select 字段a+5,字段a+字段b from 表名

1、查询，字段grade+5：
mysql>  select id,stu_id,c_name,grade+5 from score2;
2、查询，两个字段想加的值id+stu_id：
mysql>select id+stu_id,c_name,grade from score2;
3、查询全部信息和id+stu_id，且id+stu_id字段命名为xxx:
mysql> select *,id+stu_id as xxx from score2;
4、查询全部信息，且id+stu_id字段命名为xxx，并以xxx降序排列：
mysql> select *,id+stu_id as xxx from score2 order by xxx desc;
5、查询全部信息，且stu_id*2字段命名为xxx，并以xxx降序排列：
mysql> select *,stu_id*2 as xxx from score2 order by xxx desc;

（11）连接查询：（两个表之间的）

就是查A表有B表也有或者A表有B表有的要去掉这类的。
1、左连接查询:left join
查A表全部和B表也有的数据

左连接查询：
mysql> select stu.*, sc.* from student2 stu left join score2 sc on stu.id= sc.stu_id;
注释：stu:为别名。student2 stu left join score：student:为主表，score2为副表显示。 left join:为左连接。 两表关联：其ID必须一一对应（stu.id=sc.stu_id）；

2、右连接查询: right join
与左连接查询刚好相反
查B表全部和A表也有的数据

右连接查询：
mysql> select stu.*, sc.* from student2 stu right join score2 sc on stu.id= sc.stu_id;

3、内连接查询: join
查A表和B表都有的数据，交叉的

右连接查询：
mysql> select stu.*, sc.* from student2 stu join score2 sc on stu.id= sc.stu_id;

（12）多表查询：（几个表之间的数据）（*重点）

1、查询学号为901的学生信息和成绩信息
select * from student2 stu,score2 sc where stu.id=sc.stu_id and stu.id=901;
2、显示班级总数大于等于20的班级：
select class,count(*) as total from student group by class having total>=20;
3、显示人总数大于等于20的班级的成绩信息：
mysql> select sc.class,sc.number,sc.maths from score sc,(select class,count(*) as total from student group by class having total>=20) s where sc.class=s.class;

注释：commit：保存提交的意思，一般文件删除修改都要做保存；
	  Rollback：撤回的意思，命令执行后；可以撤回为修改删除前的数据；
	  truncate table score：永久删除的意思，尽量少用，删除则无记录找回；
  	  select now()：查询现在的时间；

（13）字符查询 like

使用通配符_模糊匹配数据内容
下划线通配符_与百分号通配符%类似，也用于模糊匹配。但是区别在于下划线通配符_只能模糊匹配1个字符。如果你执意想用下划线通配符_匹配多个字符，那只能多用几个_咯！
LIKE关键字和通配符_检索出所有商品名称以r结尾，且前边有9个字母的商品信息

1、查询地址为北京的学生信息：
mysql> select * from student where address like '北京%';
2、查询地址为北京市昌平区的学生信息：
mysql> select * from student where address like '%北京%平%';
3、查询湖南籍学生的成绩信息：
mysql> select * from score where stu_id in (select id from student where address like '湖南%');

练习：

1.查询student表的第2条到4条记录
select * from student limit 1,3;

2.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
mysql> select id,name,department from student;

3.从student表中查询计算机系和英语系的学生的信息
select * from student where department in ('计算机系' ,'英语系');

4.从student表中查询年龄23~26岁的学生信息
select * from student  where birth between 1990 and 1993;    2016-23=1993  2016-26=1990
select id,name,sex,2016-birth as age,department,address from student where 2016-birth;

5.从student表中查询每个院系有多少人 
select department,count(id)  from student group by department;

6.从score表中查询每个科目的最高分。
select c_name,max(grade) from score group by c_name;

7.查询李四的考试科目（c_name）和考试成绩（grade）
select c_name,grade from score,student  where score. stu_id=student.id and name='李四';
select c_name,grade from score where stu_id=(select id from student where name='李四'）;

8.用连接的方式查询所有学生的信息和考试信息
select stu.*,sc.*  from student stu left join score sc on stu.id=sc.id;

9.计算每个学生的总成绩
select stu_id,sum(grade) from score group by stu_id;

10.计算每个考试科目的平均成绩
select c_name,avg(grade) from score group by c_name;

11.查询计算机成绩低于95分的学生信息
select student.*, grade from score,student where   student.id=score.stu_id and c_name like '计算机' and grade<95;

12.查询同时参加计算机和英语考试的学生的信息
select student.*,c_name  from student,score where student.id=score.stu_id and student.
id =any( select stu_id from score where stu_id in (select stu_id from score where c_name= '计算机') and c_name= '英语' );

select * from student where id in(select stu_id from score where stu_id in (select stu_id from 
score where c_name='计算机' )and c_name='英语');

select student.* from student,(select stu_id from score where stu_id in (select stu_id from score where c_name='计算机' )and c_name='英语') t1 where student.id=t1.stu_id;

select * from student where id in (select stu_id from score sc where sc.c_name='计算机') and id in (select stu_id from score sc where sc.c_name='英语');

13.将计算机考试成绩按从高到低进行排序
select c_name,grade from score where c_name='计算机' order by grade; 
 
14.从student表和score表中查询出学生的学号，然后合并查询结果
select id  from student union select id  from score;

15.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select name,department,c_name,grade from score sc,student st where st.id=sc.stu_id and (name like'张%'or name like '王%');

16.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩中文系
select name,2016-birth age,department,address,c_name,grade from student,score where student.id=score.stu_id and address like'湖南%';

17.查询每个科目的最高分的学生信息.                                                    
分解： score=t1,    t2=select c_name,max(grade) as grade from score group by c_name，    t1.stu_id注解
分解： select * from student where id in (select t1.stu_id from score t1,t2 t2 where t1.c_name=t2.c_name and t1.grade=t2.grade);

select * from student where id in (select t1.stu_id from score t1,(select c_name,max(grade) as grade from score group by c_name) t2 where t1.c_name=t2.c_name and t1.grade=t2.grade);

select student.* from student,(select score.* from score,(select max(grade) grade,c_name from score group by c_name) t1 where score.c_name=t1.c_name and score.grade=t1.grade) t2 where student.id=t2.stu_id;