mysql测试要掌握的_测试人员必须掌握的SQL语法

什么是sql

SQL是结构化查询语句(Structured Query Language)

SQL是用于访问和处理数据库的标准的计算机语言

不同厂商在标准规范基础上进行了修改和扩展，略有不同。

SQL大小写不敏感

1.SQL编写----查询语句-语法

SELECT

[DISTINCT]  显示字段列表

[FROM 单表或多表关联

[WHERE 行记录过滤条件]

[GROUP BY 分组字段]

[HAVING 条件]

[ORDER BY 排序字段]

[LIMIT 保留记录数或区间]

(一)查询所有学生的成绩表

1.select * from score；

(二)查询所有学生及其考试科目信息(不去重)

select mid , sid, cid from score；

(三)查询学生表所有学生姓名及考试和性别，要求结果中列表标题为 “姓名”、“性别”

select sname 姓名，gender as 性别 from student

(四)关键字distinct(去重)查询成绩表中成绩，根据根据学号去重

select distinct  sid from score;

(五)查询学生表中zhangsan的信息  (查询语句 ----- where 单条件)

select * from student where sname = "zhangsan"

(六)查询学生表不是中zhangsan的信息  (查询语句 ----- where  (not) )

select * from student where not sname = "zhangsan"

(七)查询查询成绩表学号为1并且分数为80的成绩  (查询语句 -----where 多条件(and))

select *  from score where sid ="1" and score ="80"

(八)查询所有学号为1和为2的科目的成绩信息 (查询语句----- where多条件(or)

select * from score  where sid ='1' or sid ='2';同时也等价于select * from score where sid in ('1', '2');

(九)查询姓zhang san 同学的成绩信息

select * from score  where sname like "%zhang%" (查询语句 ----- where条件(like)

(十)查询每位学生的平均成绩

select sid , avg(score)as 平均成绩 from score group by  sid; (查询语句 -----group by)

平时在测试工作过程中使用的聚合函数有：求和：sum(字段)，最大/最小值：max(字段)min(字段)，记录条数：count(*)

(十一)查询平均成绩不低于85的学生及成绩

selelct sid , avg(score) as 平均成绩 from score group by sid having avg(score) >= 85; (查询语句 ------ having)

(十二)查询成绩信息，要求按学号降序排序

select * from score order by sid desc ; (查询语句 ----- order by)

(十三)查询成绩信息，要求按学号降序排序，成绩升序排序

select * from score order by sid desc, score asc;  (查询语句 -----order by 多字段)

(十四)根据学号排序查询前三名的学生成绩信息

select sid , score from score where sid in ('1', '2','3') order by score desc limit 3; (SQL编写-----limit)

(十五)查询平均分高于85的学生学号及平均分，要求平均分降序排列

select sid , avg(score) as 平均成绩 from score  where sid in ('1', '2','3') group by sid having avg(score)>85 order by avg(score) desc;

2.SQL编写 ----- 插入语句insert

(一)INSERT INTO 表名称 VALUES(值1，值2，.........)

(二) 也可以指定字段插入(未指定字段按默认值设置

)INSERT INTO 表名称(列1，列2，....)VALUES(值1，值2,.....)

例如：将一班李四的英语成绩88插入

insert into tb_score (name ,class subject, score )value ('李四', '一班', '英语', '88');等价于

insert into tb_score value ("李四","一班", "英语", "88");

3.SQL编写 ----- 修改语句update

(一)APDATE 表名称 SET 列名称 = 新值 WHERE 条件；

例如：在一张成绩表中将所有数学成绩减去5分

update t_score set score = score -5 where subject = "数学"

4.SQL编写 ----- 删除语句delete

(一)DELETE  FROM 表名称 WHERE 条件；

(二)TRUNCATE 可以全表数据清空，效率高，但缺点是不能回滚。语法：truncate table 表名称；

例如：将成绩表里所有的数学成绩删除

delete from t_score where subject ="数学"；

5.SQL编写--表连接--交叉连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：cross join

例如：select * from t_student cross ioin t_honors;

6.SQL编写---表连接--内连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：inner join

例如：select * from t_student as a inner join t_honors as b  on a.id=b.id;

7.SQL编写---表连接--自然连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：nature  join

例如：select * from t_student as anature  join t_honors as b  on a.id=b.id;

8.SQL编写---表连接--左连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：left  join

例如：select * from t_student as  a left join t_honors as b  on a.id=b.id;

9.SQL编写---表连接--右连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：right  join

例如：select * from t_student as  a right  join t_honors as b  on a.id=b.id;

10.SQL编写---表连接--全连接

例如：一张学生表(t_student)，一张获奖信息表(t_honors)

交叉连接的关键字：full  join

例如：select * from t_student as  a full join t_honors as b  on a.id=b.id;

11.数据库基本概念----索引

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构

优点：加快查询，排序和分组的速度

缺点：表数据新增，修改，删除时索引页需要更新。频繁变动的表不适合设置索引。

12.数据库基本概念----视图

视图是查询结果集，使用上同表，是一种虚拟表

创建学生表和成绩表的视图

例如：create view aaa as

select a.sname, b.score from student as a  right  join score as b on a.sid=b.sid;

创建成功之后，我们就可以查询创建的视图表：select * from aaa

视图的优点：1.聚焦特定的数据 2.简单操作 3.安全性 4.逻辑独立性

13.数据库基本概念-----存储过程

存储过程：一组为了完成特定功能的SQL语句集。通过指定存储过程名及其参数(若有)来调用执行它。有

存储过程：分为有参数和无参数两种类型

(一)有返回值的例子：(计算depb1表的存款余额，存储过程函数名：sum_balance)

BEGIN

# 存款余额求和后，保留两位小数，转化为万元返回

select  round(sum(balance) / 10000,2) into @aa form depb1;

RETURN @aa;

END

select   sum_balance()

(二)无返回值的例子：(更新余额利润,存储过程函数名：update_balance_rate )

BEGIN

# Routine body goes  here .....

update depe1

set rate = rate * factor

where balance > threshold;

END

call update_balance_rate(10000,1.1)

14.数据库基本概念 ------触发器

触发器即一触即发

包括：触发条件(insert，update，delete/前、后)、触发动作

触发器是一种特殊的存储过程。

和存储过程不同的是：不是调用执行，而是触发执行。

例如：1.修改学生表的学生名时，将其它表中的学生名同步修改；

例如：2.商品价格调整幅度超过30%时，将该异常波动记录到监控表中；

例如：3.敏感数据被人修改后，在日志表中记录操作信息。