软件测试工程师-数据库

数据库

数据库概念
MySQL、Navicat
SQL语言 查询（重点）

1、概念
数据库的作用，是把数据以表的形式存储起来，方便查询。

2、RDBMS
关系型数据库，核心是以二维表存储数据。
行：一条记录，一个事物的信息。
列：字段，一个事物的某一个属性。
一个表中的所有行是一类事物的集合。
一个数据库由很多表组成。

3、SQL
结构化查询语言，用于关系型数据库。
不区分大小写
重点是查询。

4、MySQL
由瑞典公司开发，被卖给Sun，Sun又卖给oracle
开源、免费、支持多平台

5、数据类型
整数：int ，有符号范围（-2147483648~2147483647），无符号范围（0-4294967295）
小数：decimal，如decimal（5,2）表示共存5位数，小数占2位，整数占3位。
字符串：varchar ，范围（0~65533），如varchar（3）表示最多存3位字符，一个中文或一个字母或符号都占一个字符。

6、约束
主键：int 类型 、无符号、自动递增。唯一的标识一条记录。
Ctrl+/ 注释
Ctrl+shift+/取消注释

7、逻辑删除
①设计表，给表添加isdelete字段，1代表删除，0代表没有删除
②把所有的数据isdelete都改为0
update students3 set isdelete=0
③要删除某一条数据时，更新它的isdelete为1
update students3 set isdelete=1 where id=1
④当要查询数据时，只查询isdelete为0的数据
select *from students3 where isdelete=0

8、查询
简单查询
查询指定列，如果没有写where会把指定列的所有数据都显示出来
select name,sex,hometown from students

给查询出来的字段起别名
select name as 姓名,sex as 性别,hometown as 家乡 from students

给表起别名
select s.name,s.sex,s.hometown from students as s

去除重复数据，如果有重复的数据只显示一条
select distinct sex from studens
去除重复数据，当查询多个字段时，只有一行记录跟另一行记录完全一样时才是重复数据
select distinct sex,class from students
select distinct * from students

9、判断空
null是空
''是空字符串

查询没有写身份证的学生
select * from stu where card is null
select * from stu where card=’’

查询填写了身份证的学生
select * from stu where not card is null

插入数据时如果某些字段没有设置值，默认为null
insert into students（studentno）values（20）
插入数据时指定某个字段为空字符串
insert into students（studentno，num）values（22，’’）

10、排序
查询所有学生信息，按年龄从小到大排序
select * from stu order by age desc

查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from stu order by age，studentno desc

对中文数据进行排序
select * from stu order by convert（name using gbk）

11、聚合函数
查询学生总数
count（*）代表一行任意字段有值，都会统计在内
count（card）代表只统计card字段的个数，如果有null值不会被统计
select count（*） from stu
select count（card） from stu

查询女生的最大年龄
select max（age） from stu where sex=‘女’
查询1班的最小年龄
select min（age） from stu where class=‘1班’

查询北京学生的年龄总和
select sum（age） from stu where hometown=‘北京’
查询女生的平均年龄
select avg（age） from stu where sex=‘女’

12、分组
通常与聚合函数结合使用
按照某个字段分组后，再对每组数据分别进行统计
select max（age）as 最大年龄，min(age) 最小年龄，avg （age）平均年龄 from stu
select count（age） from stu where sex=‘女’

查询各种性别的人数
select sex，count（*） from stu group by sex

查询各种年龄的人数
select age，count（*） from stu group by age

查询各个班级学生的平均年龄、最大年龄、最小年龄
select class，avg（age），max（age），min（age） from stu group by class

按照多个字段分组，当一行记录的这多个字段都一样时被分在一组里
select class，sex，count（*） from stu group by class，sex

分组后过滤
where是对form后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by 的结果进行筛选

查询男生总数
select count（*） from stu where sex=‘男’
select sex，count（*） from stu group by sex having sex=‘男’

查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
select class，avg（age），max（age），min（age） from stu
where class！=‘1班’ group by class
select class，avg（age），max（age），min（age） from stu group by class having class！=‘1班’

13、分页
按照排序后的顺序获取
select * from students order by age limit 0,3

要求每页显示3条
select count（*）from students
12/3获取总页数
第一页
select * from students limit 0,3 3*（1-1）
第二页
select * from students limit 3,3 3*（2-1）
第三页
select * from students limit 6, 3 3*（3-1）
第四页
select * from students limit 9, 3 3*（4-1）

select * from students limit 7
等同于下面的语句
select * from students limit 0，7

14、连接查询
①等值连接
select * from 表1，表2 where 表1.字段=表2.字段
此方法会产生笛卡尔积，生成的记录总数=表1的总数+表2的总数，会产生临时表

查询学生信息及学生的成绩
select * from students，scores where students.studentno=scores.studentno

②内连接
不会产生笛卡尔积，也不会产生临时表，性能高
select * from students inner join scores on students.studentno=scores.studentno

查询男生中最高成绩，要求显示姓名、课程名、成绩
select
students.name,
courses.name,
scores.score
from
courses
inner join scores on courses.courseno=scores.courseno
inner join students on students.studentno=scores.studentno
where
sex=‘男’
order by
score desc
limit 1

③左连接
查询所有学生的成绩，包括没有成绩的学生
左连接join前面的表称为左表，join后面的表称为右表
select * from students
left join scores on students.studentno=scores.studentno

查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from students
left join scores on students.studentno=scores.studentno
join前面生成的结果作为左表，join后面是右表
left join courses on courses.courseno=scores.courseno

④右连接
查询所有课程的成绩，包括没有成绩的课程
insert into courses values（0，‘语文’），（0，‘数学’）

join前面生成的结果作为左表，join后面是右表，把右表的数据全部显示
select * from scores
right join courses on scores.courseno=courses.courseno

查询所有课程的成绩，包括没有成绩的课程，包括学生信息
select * from scores
right join courses on scores.courseno=courses.courseno
right join students on students.studentno=scores.studentno

查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
select * from scores
inner join courses on scores.courseno=courses.courseno
right join students on students.studentno=scores.studentno

select * from scores
right join students on students.studentno=scores.studentno
left join courses on scores.courseno=courses.courseno

15、自关联
查询河南省的所有城市
select * from areas，areas_copy
where areas.aid=areas_copy.pid
and areas.atitle=‘河南省’

数据源，从一个表中查询多次，必须起别名
select * from areas as sheng,areas as shi
where sheng.aid=shi.pid and sheng.atitle=‘河南省’

16、子查询
①标量子查询：子查询返回的结果是一个值，一行一列
查询大于平均年龄的学生
select avg（age） from students
select * from students where age>21.5833
select * from students where age>（select avg（age） from students）

查询最小年龄的人
select * from students where age=（select min（age） from students）

查看王昭君的成绩，要求显示成绩
select studentno from students where name=‘王昭君’
select score from scores where studentno=（select studentno from students where name=‘王昭君’）

查询王昭君的数据库成绩，要求显示成绩
select * from scores where studentno=（select studentno from students where name=‘王昭君’）
and courseno=（select courseno from courses where name='数据库 '）

②列子查询：子查询返回的结果是一列多行
查询18岁的学生的成绩，要求显示成绩
select * from students where age=18
select * from scores where studentno in（‘002’，‘006’）
select * from scores where studentno in （select studentno from students where age=18）

③行子查询：子查询返回的结果是一行多列
查询男生中年龄最大的学生信息
select * from students where sex=‘男’ and age=26
select * from students where （sex，age）=（‘男’，26）
select * from students where （sex，age）=（select sex，age from students where sex=‘男’ order by age desc limit 1）

④表子查询：子查询返回的结果是一个表，多行多列
查询数据库和系统测试的课程成绩
select * from scores
inner join courses on scores.courseno=courses.courseno
where courses.name in (‘数据库’，‘系统测试’)

select * from scores
inner join
把查询出来的结果当做数据源使用，必须给子查询返回的结果起别名
（select * from courses where name in (‘数据库’，‘系统测试’)) as c on scores.courseno=courses.courseno

select * from
(select * from students) as s

=any 等于in 等于=some

>any 等于 >some 大于子查询返回的结果中的任意一个值
>all 大于子查询返回的所有的值

select * from students
where age！=all （select age from students where age between 18 and 20）

select * from students where age>any（select age from students where age between 18 and 20）

17、查询演练
查询所有价格大于平均价格（保留2位小数）的商品，并且按照价格降序排列
select * from goods where price>（select round（avg（price），2）from goods）order by price desc

查询价格大于或等于超极本价格的商品，并且按价格降序排列
select price from goods where cate=‘超极本’
select * from goods where price>=any（select price from goods where cate=‘超极本’）order by price desc

数据分表

创建类型表
create table goods_cates（
id int unsigned primary key auto_increment，
cate_name varchae(10)
）
select distinct cate from goods

把查询出来的数据插入到另一个表中
insert into goods_cates（cate_name）select distinct cate from goods

18、创建品牌表
create table goods_brands（
id int unsigned primary key auto_increment，
brand_name varchae(10)
）
select distinct brand_name from goods

create table … select …
create table goods_back select * from goods

create table goods_brands_back（
id int unsigned primary key auto_increment，
name varchae(10)
）
查询出来的列必须对应表中的字段名，如果在表中找不到同名字段，会新建一个字段
select distinct brand_name as name from goods

select * from goods
inner join goods_cates on goods.cate=goods_cates.cate_name

update goods
inner join goods_cates on goods.cate=goods_cates.cate_name
set goods.cate=goods_cates.id

update goods
inner join goods_brands on goods.brand_name=goods_brands.brands_name
set goods.brands_name=goods_brands.id

查询商品名称，商品类型，品牌名称
select goods.name,goods_cates.cate_name,goods_brands.brand_name from goods,goods_cates,goods_brands
where goods.cate=goods_cates.id and goods.brand_name=goods_brands.id

select * from goods
inner join goods_cates on goods.cate=goods_cates.id
inner join goods.brand on goods.brand_name=goods_brands.id

19、回顾

20、命令行
连接：mysql -uroot -p 回车 输入密码

查看所有仓库：show databases;

使用某个仓库（重要）：use ce;

查看所有的表：show tables；

查看表结构：desc student；
show create table students；

中文乱码可输入：set charset gbk;

21、函数
随机0-10的整数：select round（rand()*10）

随机从表中取一条记录
select * from goods order by rand() limit 1

创建自定义函数
create function my_trim（str varchar（100））returns varchar（100）
begin
return ltrim（rtrim（str））；
end
使用自定义函数
select my_trim（‘abc’）

22、视图
把视图当做表用
创建视图
视图中的查询语句不能有重复的字段名，如果有可以起别名
create view v_stu as
select
stu.*,cs.coursenno,cs.name coursename,sc.score
from
students stu
inner join scores sc on stu.studentno=sc.studentno
inner join courses cs on cs.courseno=sc.courseno

create view v_students as
select name 姓名，age 年龄 from students

查询视图
select * from v_students

23、事务
转账
update account set money=money-100 where name =‘大乔’
update account set money=money+100 where name =‘小乔’

begin；开启事务
所有操作都成功
commit；

begin；
任何一步失败
rollback；