软件测试培训总结篇1
所学内容的整体整理（中）:数据库的全部查询命令详解：
查询
简单的查询记录基础语法
基础语法 select 查询的内容（也就是字段名1，字段名2） from 表名 where 条件
（用*号可以显示所有字段）
查询李进峰的所有基本信息
SELECT * FROM student WHERE sname='李进峰';
查询李进峰和菲菲的所有基本信息
SELECT * FROM student WHERE sname='李进峰' OR sname='菲菲';
SELECT * FROM student WHERE sname IN (‘李进峰’, '菲菲');
查询所有姓张的同学的所有基本信息
SELECT * FROM student WHERE sname LIKE '张%';
查询姓名中有“宇”的同学的所有信息
SELECT * FROM student WHERE sname LIKE '%宇%';
查询姓名长度为3，第一个字为“李”最后一个字是“照”的记录
SELECT * FROM student WHERE sname LIKE '李_照';
查询所有姓张和姓李的同学的学号和姓名以及出生年月
SELECT sid,sname,borth_year FROM student WHERE sname LIKE '张%' OR sname LIKE '李%'
SELECT sid,sname,borth_year FROM student WHERE sname LIKE [张李]%';
查询姓“杨”并且所在省份为“河北省”的同学的学号
SELECT sid FROM student WHERE sname LIKE '杨%' AND province='河北省';
查询”北京”、”湖南”和”河南省”同学的所有信息
SELECT * FROM student WHERE province=’北京' OR province='湖南' OR province='河南省'
SELECT sid,sname,borth_year FROM student WHERE province IN ('北京', '湖南', '河南省');
查询姓“”李但是所在省份没有“湖南”字样的同学的省份证号码
SELECT card_id FROM student WHERE sname LIKE '李%' AND province NOT LIKE '%湖南%'
查询18岁（不含18）以前上学的同学的姓名和电话
SELECT sname,tel FROM student WHERE year(enrollment)-borth_year<18;
查询所有大于25岁的人的所有信息
SELECT * FROM student WHERE year(getdate())-year(enrollment)>25;
通配符
使用环境，用于模糊查询，连接符号用Like 而非 =
* 代表全部，所有，没有规定数量和内容。一般用于select 后面 不作为条件
_ 代表一个字符，一个数据位。中文要用两个__ 。作为条件使用
% 代表任意的，没有规定数量和内容。作为条件使用
[值1,值2,值3] 从括号内的任意值。作为条件使用
select * from student where stname like '张%'
select * from student where stname like '[张李]%四'
运算符。条件
-- = 两边内容相同。
-- > 左边大于右边。
-- < 右边大于左边。
-- >= 左边大于等于右边。
-- <= 右边大于等于左边。
-- <> 或者 != 不等于
-- between A and B 介于 A 和 B 之间，并且 >=a <=b .前面的数要小于后面的数，包含边界数的。
select * from grade where coursegrade = 80
select * from grade where coursegrade between 80 and 100
条件关联
-- 条件1 and 条件2 :要同时满足条件1 和 条件2 是并且的关系。
-- 条件1 or 条件2 :只要满足一个条件就可以 是或的 关系。
-- () :先去执行括号中得条件
-- 字段 in (值1,值2,值3) :字段的内容要满足 值1 或 值2 或 值3
select * from student where family = '北京' and stname like '张%'
select * from student where family in ('上海','天津')
排序
--对查询到的结果按照升序或者降序的方式进行排列。
--语法 order by 字段 排序方式 （desc,asc） asc 可以省略
--注意：order by 一定要写在所有查询sql 的最后面
select * from grade order by coursegrade
聚合函数
对查询的结果中某个字段进行函数运算
sum(字段) :求和函数 字段需为数字类型
avg(字段) :求平均值 字段需为数字类型.不会计算为空的条目。
min(字段) :最小的值
max(字段) :最大的值
count(字段) :统计字段的个数，这里可以使用 * ，统计所有结果的行数。
distinct(字段) :去重复
注：聚合函数，不能放在WHERE中，需要放在HAVING里
-- 显示学生的总成绩
select sum(coursegrade) from grade
-- 显示最高的成绩是多少
select min(coursegrade) from grade
select avg(coursegrade) from grade
select * from grade
select count(*) from student
DISTINCT 去除重复行
SELECT DISTINCT province FROM student;
别名AS (Alias)
as 临时别名
列别名在WHERE中不能使用 GROUP BY 中不能使用 ORDER BY 中可以
表别名可以在任何地方使用
select class, sex, count(sex) as数量 FROM student GROUP BY class,sex ORDER BY class;
select count(*) as 学生个数 from student
select stname as 姓名,stuid as 学号from student
SELECT和ORDER BY 不影响表中数据 所以可以使用别名
分组里加WHERE和ORDER BY
--统计每个课程的选修人数大于2个人的信息
对课程分组，显示课程和对应的人数
select couid,count(stuid) from grade group by couid having count(stuid) >2
--统计<学生选修信息表>，统计每个同学的总成绩
统计记录条数 count？
查多少名学生
SELECT count(*) FROM student
查有多少种班级
SELECT count (DISTINCT class) FROM student;
查入学时间在1990-1-1到2000-12-31的学生个数
SELECT count(enrollment) FROM student WHERE enrollment BETWEEN '1990-1-1' AND '2000-12-31' ;
分组查询 GROUP BY
先对查询内容进行分组、分类，然后在对一个组中的数据进行查询或者运算
select 查询的内容 from 表名 group by 字段名
select stuid,sum(coursegrade) from grade group by stuid
select * from grade
select stuid,sum(coursegrade) from grade where coursegrade is not null group by stuid
分组前条件
在group by 之前使用where 条件去选取数据
分组后条件
在 group by 分组后 + having 条件
select stuid,sum(coursegrade) from grade where coursegrade is not null
group by stuid having sum(coursegrade) >=200
--统计<学生信息表>，统计每个班级中每种性别的学生人数
select * from student
--统计按照性别分得学生人数
select sex,count(stuid) from student group by sex
--统计<学生信息表>，统计每个班级中每种性别的学生人数,两个分组条件
--统计<grade表>，统计每门课程的平均成绩,并按照成绩降序排序
select couid,avg(coursegrade) from grade group by couid order by avg(coursegrade) desc
RIGHT函数
取身份证最后5位数
SELECT right(card_id, 5) FROM student;
将身份证补齐20位，前面用0补
SELECT right('00'+card_id, 20) FROM student;
SELECT ‘00’+card_id FROM student;
LEFT函数
取身份证的前10位
SELECT left(card_id, 10) FROM student;
将学号小于50的电话设置成NULL
UPDATE student SET tel = NULL WHERE sid< 50;
将电话为NULL的学生信息查询出来
SELECT * FROM student WHERE tel IS NULL;
判断条件是NULL的时候不能用= 要用IS IS NOT
HAVING
分组查询中，如果条件中有聚合函数，不能放在WHERE中，需要放在HAVING里
查询平均出生年>1991的班级信息
SELECT class,avg(borth_year) FROM student GROUP BY class HAVING avg(borth_year) > 1991 ;
统计哪些班级男生平均年龄大于20
SELECT calss FROM student WHERE sex=’男’ GROUP BY class HAVING avg(year(getdate())-borth_year)>20;
优先级WHERE——GROUP BY——HAVING——PRDER BY
查询学生的姓名和出生年，年龄最大的放前面
SELECT sname, borth_year FROM student ORDER BY borth_year ;
TOP 查询结果去前几个结果
查询年龄最大的学生信息 年龄最大的前10名
SELECT TOP 1 sname, borth_year FROM student ORDER BY borth_year ;
SELECT TOP 10 sname, borth_year FROM student ORDER BY borth_year ;
查询年龄最小的学生信息 年龄最大的前10名
SELECT TOP 1 sname, borth_year FROM student ORDER BY borth_year DESC;
SELECT TOP 10 sname, borth_year FROM student ORDER BY borth_year DESC;
查询按照学号排序，前50个学生的信息
SELECT TOP 50 * FROM student ORDER BY sid;
查询最近入学的10名学生信息
SELECT TOP 10 * FROM student ORDER BY enrollment DESC;
关联查询（等值联接）只需看懂查询语句的意思即可
内连接 等值连接
外连接 1、左连 2、右连
多表关联
1内连接 等值连接 inner join
语法 select 内容 from 表1 inner join 表2 on 等值条件
select * from student inner join grade on grade.stuid = student.stuid
select grade.couid,couname,coursegrade from grade inner join course on grade.couid = course.couid
2外连接 outer join
1）左连 select 内容 from 表1 left outer join 表2 on 条件
2）右连 select 内容 from 表1 right outer join 表2 on 条件
select * from grade outer join student on student.stuid = grade.stuid
注：左连接 以 outer join 左边的表为准，要显示全部的左边表的数据，如果右边表没有关联的数据显示Null 与右边表比较 当右边表没有值时 只取左边表的记录
select * from student right outer join grade on student.stuid = grade.stuid
注：右连接 以 outer join 右边的表为准，要显示全部的右边表的数据，如果左边没有关联数据，显示Null
多表关联
语法：select 内容 from 表1，表2，表3 where 条件
select stname,couname,coursegrade from student,course,grade
where student.stuid = grade.stuid and course.couid= grade.couid and coursegrade >80
select * from grade
--所有男同学的平均成绩
select avg(coursegrade) from student,grade where student.stuid = grade.stuid and sex = '男' and coursegrade is not null
select * from student
3子查询，嵌套查询
--当某一个查询的条件，是在另一个查询的结果集里面时，使用嵌套查询
--查询李四的成绩。
select * from grade where stuid = (select stuid from student where stname ='李四')
--查询 张三和李四的成绩
select * from grade where stuid in
(select stuid from student where stname ='张三' or stname = '李四')
select * from grade where stuid = (select stuid from student where stname ='张三')
or stuid = (select stuid from student where stname ='李四')
--有考试成绩在 70~90的学生的姓名
select stname from student where stuid in
(select stuid from grade where coursegrade between 70 and 90)
视图与索引为额外拓展部分
视图/虚表 view
创建视图 create view 视图名 as (SQL)
create view tiger as (
select stname,couname,coursegrade from student,grade,course
where student.stuid = grade.stuid and grade.couid = course.couid)
--注意1.视图中不保存数据，不保存表结构，不保存表，没有约束。只保存sql 语句。
--注意2.视图可以当表来使用，可以用视图去建视图。但是，不建议做 增 删 改的操作。
--注意3.删除视图时,不会影响到原来的基础表。可以直接在视图中使用SELECT
create view big_tiger as (select stname,coursegrade from tiger )
delete from big_tiger where coursegrade is null
删视图
--语法：drop view 视图名
drop view big_tiger
drop view tiger
视图的优点
--1.对于使用sql 不熟练来说。视图是个很简单的查询途径
--2.对于经常重复的复杂sql，使用视图可以提高查询效率
--3.可以辅助用于权限管理，提高数据的安全性
--4.帮助保护表和数据的完整一致性
视图的缺点
--1.降低了查询效率
--2.增加保存空间
--3.无法对数据和表进行操作。使得操作测试不方便进行。
create view cll as (
select class,count(stuid) as 人数 from student group by class
)
select * from cll where 人数 = 2
--视图在企业中如何应用
1.对于测试而言，降低测试的复杂度。例如：复杂 sql 写一次保存视图，以后都直接调用视图。
2.对于测试而言，降低工作难度。例如：新来的新人可能对表结构不熟悉，对sql 也不熟悉，使用视图可以尽快上手开始工作。
3.对于开发而言，降低服务器的工作。提高服务器运行效率。例如：如果不用视图，查询语句要在服务器端生成传给数据库。如果使用视图，服务器直接查询视图就可以满足查询功能。
4.对产品设计和业务人员而言，降低了工作难度，不用学习和使用复杂sql 语言。例如：如果产品或者业务人员想去查询某些指定的数据时，只要让开发或测试人员建一个视图。直接去查询视图就可以了。
索引
--索引就像目录，表中对于一个字段的目录结构。如果想操作表中的数据，先在索引字段中找到那一行，然后再去操作那一条数据。
--注意1：建议索引设置在主键、外键、唯一约束，效率高
--注意2：索引是应用于查询条件的。经常使用的查询条件字段，应该被设置为索引字段。
--注意3：一个表中可以有多个索引，索引之间可以交叉字段，一个索引可以有多个字段。
索引的优点：
--1.提高查询效率
--2.提高排序效率
索引的缺点：
--1.占用数据库资源
--2.降低了对数据库增、删、改的效率
索引在企业中如何应用：
--1.页面上如果有很多的查询条件并且需要排序时，建议一一创建索引。
--2.检查是否主键和外键、唯一约束建立了索引关系。
--3.在测试过程中应该检查，运行查询条件时是否使用了索引。
--4.测试中检查，平凡被更新的表，不要设置太多的索引。
--建索引
--语法：create index 索引名 on 表(字段，字段...);
create index suoyin1 on student(stname,family);
--删索引
--语法：drop index 表名.索引名
drop index student.suoyin
