软件测试Mysql数据库学习（二）

数据的管理：增删改查（简单的查询）

数据的插入：

准备工作：

use test1;

建表：

create table student(

sid int primary key,

sname varchar(20) not null,

grade int

);

往student表插入数据：

• 1：插入一条数据

insert into 表名【(字段1,字段2,...字段n)】

values(值1,值2,...值n);

举例：

insert into student(grade,sname,sid)

values(5,'Lily',1);

insert into student values(2,'Lucy',5);

insert into student(sname,sid) values('Jim',3);

insert into student(sid,sname,grade)

values(4,'XX',null);

insert into student values(5,'yy',null);

• 2：一次插入多条数据

insert into 表名【(字段1,字段2,...字段n)】

values(值1,值2,...值n)，

(值1,值2,...值n)，

……

(值1,值2,...值n);

举例：

insert into student(sid,sname,grade)，

values(6,'xy',4),

(7,'zz',5),

(8,'xb',7);

1. 文件的导入和导出

SQL文件的导出步骤：双击你要导出的数据库将其激活——》右击数据库，

选择“转储SQL文件”——》选择“结构和数据”

SQL文件的导入步骤：双击你要导入的数据库将其激活——》右击数据库，

选择“运行SQL文件”——》选择你要导入的SQL文件运行

数据的查询

准备工作：导入已经准备好的student.sql文件

导入的3张表：

xsb 学生表

kcb 课程表

cjb 成绩表

数据的查询

1 查询所有的字段

语法：select * from 表名

举例：

select * from xsb;

2 查找特定的字段

语句：select 字段1,字段2,...字段n from 表名;

举例：

select xm,nl from xsb;

select xm from xsb;

select distinct bj from xsb; -- 使用distinct过滤重复的记录

3 条件查询

语法：

select *|字段1,字段2,...字段n

from 表名

where 查询条件;

select * from student where sid>=5;(查询sid大于等于5的数据)

注解1：

from子句的作用是明确数据的来源；

select子句的作用是过滤出满足需求的列/字段；

where子句的作用是过滤出满足需求的行/记录；

注解2：

where条件里经常用到的运算符：

= != > >= < <=

and  与   并且

or  或 或者

not  取反

between and 在某个范围里

in 在一组数据里任取其一

not in

举例：

查询张三学员的性别和籍贯

select xb,jg

from xsb

where xm='张三';

查询年龄大于20岁的学员信息

select *

from xsb

where nl>20;

select * from xsb where nl>20;

查询年龄在19到21岁之间的学员信息

写法1：

select *

from xsb

where nl>=19 and nl<=21;

select *

from xsb

where nl<=21 and nl>=19;

以下写法不对，查询结果不满足要求：

select *

from xsb

where 19<=nl<=21;

写法2：还可以用between and来实现：

select * from xsb where nl between 19 and 21;

注意：使用between and时，between后要跟较小的值，and后要跟较大的值，

且这种用法包含边界。

以下写法不对，查询不出满足条件的记录：

select * from xsb where nl between 21 and 19;

写法3：

select * from xsb where nl in(19,20,21);

写法4：

select * from xsb where nl=19 or nl=20 or nl=21;

查询张三和李四的学员信息

select * from xsb where xm='张三' or xm='李四';

select * from xsb where xm in('张三','李四');

查询除了张三和李四以外的学员信息

select * from xsb where xm not in('张三','李四');

select * from xsb where not (xm='张三' or xm='李四');

条件查询还支持模糊查询：like

常用的两个通配符：

% 表示此处有0个或1个或多个字符

_ 表示此处有1个字符

举例：

查询姓张的学员信息

select * from xsb where xm like '张%';

查询姓名里包含“三”的学员

select * from xsb where xm like '%三%';

查询姓张的学员信息（该学员名字就2个字符）

select * from xsb where xm like '张_';

条件查询还支持空值查询，空值查询用is连接，而不是=:

is null 是空值

is not null 不是空值

查询student表里grade为空值的学员信息

select * from student where grade is null;

不能写成如下这种，因为查询不到相关的结果：

select * from student where grade=null;

4 排序显示

语法：

select *|字段1,字段2,...字段n

from 表名

where 查询条件

order by 字段 【asc|desc】;

注解：

asc ascend 升序

desc descend 降序

举例：

select * from xsb order by nl desc;

select * from xsb order by nl asc;

select * from xsb order by nl; -- 默认升序

select * from xsb order by nl desc,xh asc;

5 聚合函数

聚合函数一般用于分组统计

常用的聚合函数有：

count()  统计个数

avg() 求平均值

sum() 求和

max() 求最大值

min() 求最小值

举例：

select count(*) as 学员个数 from xsb; -- as可以省略

select count(xh) 学员个数 from xsb;

 

select * from student;

select count(sid),count(sname),count(*),count(grade) from student;

查询001号学员的最高分、最低分、平均分和总分

select max(cj),min(cj),round(avg(cj))**round去掉小数点后面的数保留整数**,sum(cj)

from cjb

where xh='001';

6 分组查询

语法：

select *|字段1,字段2,...字段n

from 表名

where 查询条件

group by 分组字段

having 过滤条件

order by 字段 【asc|desc】;

注解：

group by 子句：按某个字段做分组

having 子句：对分组之后的数据做进一步过滤，它是针对组的过滤

where 子句：对分组之前的数据做过滤，它针对的是行/记录的过滤，聚合函数不能出现在where子句里

举例：

select xh,sum(cj)

from cjb

group by xh;

查询总分在100分以上的学员及其总分

select xh,sum(cj) from cjb group by xh having sum(cj)>100;

数据的更新

语法：

update 表名 set 修改的内容 【where 更新条件】;

注解:

如果不跟更新条件，则表里所有的记录都会更新；

如果跟了更新条件，则只有满足条件的记录会更新。

举例：

将成绩表里所有人的成绩减少2分

select * from cjb;

update cjb set cj=cj-2;

将001号学员的01号课程的成绩增加2分

update cjb set cj=cj+2 where xh='001' and kch='01'

将张三的性别更新为女，年龄更新为21岁

select xb,nl from xsb where xm='张三';

update xsb set xb='女',nl=21 where xm='张三';

update student set grade=null where sid=1;

数据的删除

语法：

delete from 表名 【where 删除条件】;

注解：

如果不跟条件则会删除表里所有的数据（保留表结构）

如果跟条件则会删除满足条件的记录

举例：

删除001号学员的01号课程的成绩记录

delete from cjb where xh='001'  and kch='01';

删除001号学员的信息

delete from xsb where xh='001';

直接删除001号学员的信息会失败，因为001号学员在cjb里有记录，

并且cjb(子表)的xh参照了xsb(父表)的xh,即主外键关联了，此时，

我们通常需要先删除cjb里的相关数据，再删除xsb的数据。

delete from cjb where xh='001';   先删除cjb

delete from xsb where xh='001';   再删除xsb的数据

select * from xsb where xh='001';

小结：

数据的常见操作：增删改查（简单的查询）

insert

select

update

delete