软件测试从业者必备SQL合集V1.0（21天搞定）

原创 IDO老徐简尚

这是，IDO老徐最近在进行的21天SQL打卡的作业 & 参考答案的合集，提供给所有软件测试从业者；

题目不是很难，零基础也可以跟着入门；如果你的工作中，每天要用到SQL，那么这些你应该非常熟悉，都是经常用的内容；

但，对于95%以上的软件测试从业者，足够了；搞定这些，日常工作中的问题定位 & 查询数据 & 插入数据，都可以得心应手；

希望，对你有用；

学SQL，很简单，安装个Mysql ，弄个客户端，每天实操，21天搞定；

每位同学建议循环3个周期（熟悉 -> 回顾 -> 巩固，21 * 3 ，2个月）

IDO老徐，关于学习SQL的3*21理论：

1）敲一遍这些命令：熟悉21天

2）敲完过后，如果不回归，隔段时间，就忘了：得再回顾 21天

3）再循环一次，会发现很多命令，都很有意思，爱上她了：最后巩固 21天

学SQL，没诀窍，就是多敲、多实践。

每天掌握一条SQL语句；每天一个作业，跟着实操21天，工作中常见的使用，就差不多了。

建议：多敲几次命令，掌握当天内容；否则，欠的技术债会越来越多，最后放弃学习。

补充：这个玩法的逻辑是，

每天一个作业，第2天同步前一天的参考答案（仅限参考，不一定完全正确）；

开始前的准备事项，第0天，

开始之前的，前置准备事项：

1、在自己电脑上安装一个mysql数据库，文章见

-> http://istester.com/article/258.html

2、找一个mysql客户端链接工具：推荐workbench ，

安装包 http://istester.com/article/405.html

第1天作业，

1）创建数据库 istester

2）查看数据库创建是否成功

第2天作业，

在昨天创建的isTester数据库下，创建表 istester 和表 idoxu

注：SQL的作业，前几天的库、表，都是打基础；没有这些，后面的增删改查，你也没法玩；

1）isTester表的字段、类型、属性：

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,

uname VARCHAR(20) NOT NULL ,

sex VARCHAR(4) ,

birth YEAR,

department VARCHAR(20) ,

address VARCHAR(50) ,

idoxu VARCHAR(20)

2）idoxu表的字段、类型、属性：

id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,

stu_id INT(10) NOT NULL ,

c_name VARCHAR(20) ,

istester VARCHAR(50) ,

grade INT(10)

前一天作业答案参考，

1）创建数据库 istester

create DATABASE istester;

2）查看数据库创建是否成功

show DATABASES;

第3天作业，

根据已有的表istester 和 idoxu 创建新表（复制表）

1）复制istester表结构，创建新表istester2

注：可以有两种方式

2）复制idoxu表结构和数据，创建新表idoxu2

3）复制idoxu表结构的 id,stu_id,istester三个字段，创建新表idoxu3

前一天作业答案参考，

创建istester和idoxu表

CREATE TABLE istester (

id INT(10) NOT NULL UNIQUE PRIMARY KEY ,

uname VARCHAR(20) NOT NULL ,

sex VARCHAR(4) ,

birth YEAR,

department VARCHAR(20) ,

address VARCHAR(50) ,

idoxu VARCHAR(20)

);

CREATE TABLE idoxu (

id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,

stu_id INT(10) NOT NULL ,

c_name VARCHAR(20) ,

istester VARCHAR(50) ,

grade INT(10)

);

第4天作业，

1）删除表 istester2

2）同时删除表idoxu2和idoxu3

3）查看还有哪些表

前一天作业答案参考，

1）复制istester表结构，创建新表istester2

注：可以有两种方式

create table istester2 like istester ;

或

create table idoxu2 as select * from idoxu where 2=1;

2）复制idoxu表结构和数据，创建新表idoxu3

create table idoxu3 as select * from idoxu

3）复制idoxu表结构的 id,stu_id,istester三个字段，创建新表idoxu3

create table idoxu3 as select id,stu_id,istester from idoxu where 1<>1;

第5天作业，

1）修改istester表，添加一个字段istester6 （字符类型VARCHAR，长度100，不允许为空）

2）查看字段istester6 ，是否添加成功

3）修改istester表，删除字段istester6

4）确认字段istester6 ，是否删除成功

前一天作业答案参考，

1）删除表 istester2

drop table istester2 ;

2）同时删除表idoxu2和idoxu3

drop table idoxu2,idoxu3 ;

3）查看还有哪些表

show tables;

第6天作业，

插入 insert：

1）向表istester插入数据

id = 1 ,uname = idoxu ,idoxu = 2020

id = 2 ,uname = idoxu2 ,idoxu = 2020

id=11,uname = idoxu3,sex=1

id=12,uname = idoxu4,sex=2

id=13,uname = idoxu5,sex=1

id=14,uname = idoxu6,sex=2

2）向表idoxu插入数据

id=4,stu_id=11,c_name=idoxu,grade=90

id=5,stu_id=12,c_name=lin,grade=100

id=6,stu_id=33,c_name=istester,grade=20

前一天作业答案参考，

1）修改istester表，添加一个字段istester6 （字符类型VARCHAR，长度100，不允许为空）

ALTER table istester add column istester6 VARCHAR(100) NOT NULL;

2）查看字段istester6 ，是否添加成功

desc istester

3）修改istester表，删除字段istester6

ALTER table istester drop istester8

4）确认字段istester6 ，是否删除成功

desc istester

第7天作业，

1）查询istester表 id = 1 的内容

2）查询idoxu 表 grade=100 的内容

前一天作业答案参考，

插入 insert：

1）向表istester插入数据

id = 1 ,uname = idoxu ,idoxu = 2020

id = 2 ,uname = idoxu2 ,idoxu = 2020

id=11,uname = idoxu3,sex=1

id=12,uname = idoxu4,sex=2

id=13,uname = idoxu5,sex=1

id=14,uname = idoxu6,sex=2

2）向表idoxu插入数据

id=4,stu_id=11,c_name=idoxu,grade=90

id=5,stu_id=12,c_name=lin,grade=100

id=6,stu_id=33,c_name=istester,grade=20

参考语句：

insert into istester(id,uname,idoxu) values(1,"idoxu",2020);

insert into istester(id,uname,idoxu) values(2,"idoxu2",2020);

insert into istester(id,uname,sex) values(11,"idoxu3",1),(12,"idoxu4",2),(13,"idoxu5",1),(14,"idoxu6",2);

insert into idoxu(id,stu_id,c_name,grade) values(4,11,"idoxu",90),(5,12,"lin",100),(6,33,"istester",20);

第8天作业，

继续 select

1）查找idoxu表，名称（c_name）包含 “i” 的数据

2）查找istester表，id 包含 “1” 的数据

3）查找istester表，id 包含 “1” 的数据，按id降序

4）查找istester表，id 包含 “1” 的数据，取id最大的三个

前一天作业答案参考，

1）查询istester表 id = 1的内容

select * from istester where id = 1;

2）查询idoxu 表 grade=100 的内容

select * from idoxu where grade =100;

第9天作业，

1）找出idoxu表中，分数最高的同学和分数

2）找出idoxu表中，分数最低的同学和分数

前一天作业答案参考，

1）查找idoxu表，名称（c_name）包含 “i” 的数据

select * from idoxu where c_name like '%i%' ;

2）查找istester表，id 包含 “1” 的数据

select * from istester where id like '%1%';

3）查找istester表，id 包含 “1” 的数据，按id降序

select * from istester where id like '%1%' order by id desc ;

4）查找istester表，id 包含 “1” 的数据，取id最大的三个

select * from istester where id like '%1%' order by id desc limit 3 ;

第10天作业，

-- 1) 找出istester表，sex为空的的数据；

-- 2）更新istester表，把sex为空的，设置为0（性别未知）

-- 3）找出idoxu表，grade小于60分的同学

-- 4）更新idoxu表，把grade小于60分的同学，一律改为59分

前一天作业答案参考，

-- 1）找出idoxu表中，分数最高的同学和分数；

SELECT c_name,grade as "maxvalue" from idoxu WHERE grade in (select MAX(grade) from idoxu ) ;

-- 2）找出idoxu表中，分数最低的同学和分数；

SELECT c_name,grade as "minvalue" from idoxu WHERE grade in (SELECT MIN(grade) from idoxu );

第11天作业，

-- 1）查找istester表，按id降序

-- 2）查找idoxu表，按grade升序

前一天作业答案参考，

-- 1) 找出istester表，sex为空的的数据；

select * from istester where sex is null ;

-- 2）更新istester表，sex为空的，设置为0（性别未知）

update istester set sex = 0 where sex is null ;

-- 3）找出idoxu表，grade小于60分的同学

select * from idoxu where grade < 60 ;

-- 4）更新idoxu表，把grade小于60分的同学，一律改为59分

update idoxu set grade = 59 where grade < 60 ;

第12天作业，

1）查询istester表，有多少条数据

2）查询istester表，有几种性别类型（sex字段，去重）

前一天作业答案参考，

-- 1）查找istester表，按id降序

select * from istester order by id desc;

-- 2）查找idoxu表，按grade升序

select * from idoxu order by grade;

第13天作业，

1）查找idoxu表，学生成绩(grade) 总分

2）查找idoxu表，学生成绩(grade) 平均分

前一天作业答案参考，

1）查询istester表，有多少行数据

select COUNT(sex) from istester;

2）查询istester表，有性别类型数量（sex字段，去重）

select COUNT(DISTINCT sex) from istester;

第14天作业，

1）查找idoxu表，成绩在80 - 100区间的学生；

2）查找istester表，id 为 2，11，12 的数据；

前一天作业答案参考，

1）查找idoxu表，学生成绩(grade) 总分

select sum(grade) as sumgrade from idoxu ;

2）查找idoxu表，学生成绩(grade) 平均分

select avg(grade) as avggrade from idoxu ;

第15天作业，

1）删除 istester表，id大于12的数据；

2）删除idoxu表，分数grade不及格（小于60分）的数据；

前一天作业答案参考，

-- 1）查找idoxu表，成绩在80 - 100区间的学生；

select * from idoxu where grade between 80 and 100;

-- 2）查找istester表，id 为 2，11，12 的数据；

select * from istester where id in (2,11,12) ;

第16天作业，

1）造数据，把istester表的所有数据，插入到 idoxu表

字段取值规则

id 取id

stu_id 取id

c_name 取 uname

istester 和 grade字段，给默认值 60

前一天作业答案参考，

1）删除 istester表，id大于12的数据；

delete from istester where id > 12;

2）删除idoxu表，分数grade不及格（小于60分）的数据；

delete from idoxu where grade < 60 ;

第17天作业，

-- 1）把idoxu表，改名为 idoxu6

-- 2）检查是否修改成功

-- 3）修改表idoxu，把字段istester ，改为istester6（字符类型varchar，长度160 ）

-- 4）检查是否修改成功

前一天作业答案参考，

造数据，把istester表的所有数据，插入到 idoxu表

字段关系

id 取id

stu_id 取id

c_name 取 uname

istester 和 grade字段，给默认值 60

insert into idoxu(id,stu_id,c_name,istester,grade) select id,id,uname,60,60 from istester ;

第18天作业，

1）创建数据库 istesterdb6

2）检查数据库是否创建成功；

3）进入istesterdb6 库

4）在数据库 istesterdb6 ，创建idoxu表，直接拷贝 istester库idoxu6表的数据和结构；

5）检查表是否创建成功；

前一天作业答案参考，

-- 1）把idoxu表，改名为 idoxu6

-- ALTER TABLE 旧表名 RENAME TO 新表名 ;

ALTER TABLE idoxu RENAME TO idoxu681 ;

-- 2）检查是否修改成功

show tables;

-- 3）修改表idoxu6，把字段istester ，改为istester6（字符类型varchar，长度160 ）

alter table idoxu6 CHANGE istester istester6 varchar(160);

-- 4）检查是否修改成功

desc idoxu6;

第19天作业，

idoxu表，分数grade

1）排名前三的学生和分数；

2）排名3 - 6名的学生和分数；

3）排名6名以后的所有学生和分数；

前一天作业答案参考，

1）创建数据库 istesterdb6

create database istesterdb6 ;

2）检查数据库是否创建成功；

show databases ;

3）进入istesterdb6 库

use istesterdb6 ;

4）在数据库 istesterdb6 ，创建idoxu表，直接拷贝 istester库idoxu6表的数据和结构；

create table istesterdb6.idoxu as SELECT * from istester.idoxu6;

或

create table idoxu as SELECT * from istester.idoxu6;

5）检查表是否创建成功；

show tables;

第20天作业，

多表（左链接、右链接、内链接）

表 idoxu6 和表istester

前一天作业答案参考，

-- 1）排名前三的学生和分数；

SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 3;

-- 2）排名3 - 6名的学生和分数；

SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 2,4;

-- 3）排名6名以后的所有学生和分数；

SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 5,666;

注：这题目的是limit的用法，很多同学写的太复杂；

limit是mysql的语法

select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录

n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

即取出第3条至第6条，4条记录

第21天作业（最后1天，结业），

1）把表idoxu6，改完idoxu

2）删除库 istesterdb6

3）删除istester表

4）清空idoxu表的数据

End ，一切恢复如初，坐等SQL第2期；

前一天作业答案参考，

注：主要目的是熟悉这三种玩法，以及观察结果差异；

#左连接

SELECT * from idoxu6 a LEFT JOIN istester b on a.stu_id=b.id;

#内连接

SELECT * from idoxu6 a INNER JOIN istester b on a.stu_id=b.id;

#右连接

SELECT * from idoxu6 a RIGHT JOIN istester b on a.stu_id=b.id;

结业，最后一天作业答案参考

1）把表idoxu6，改名为idoxu

rename table idoxu6 to idoxu;

或

alter table idoxu6 rename idoxu;

2）删除库 istesterdb6

drop database istesterdb6;

3）删除istester表

drop table istester;

4）清空idoxu表的数据

truncate table idoxu;

或

delete from idoxu;

End ，恭喜结业；

循环3次21天打卡，总共63天（两个月掌握软测必备的SQL日常使用）

整个2020年，老徐会把21天打卡系列玩下去，通过打卡的方式，带一批同学，搞定懒惰、搞定不知道学啥、搞定基础技能；

4月19日，SQL 21天打卡，第2期，启动；

注：第2期，会与这篇文章的第1期略有差异，但难度差不多；还是围绕软件测试从业者必备SQL（用不上的暂时不关注，实用为主）；

End 。

IDO老徐

2020.04.17 深圳

注：（太多同学问）IDO老徐的个人微信，只有 isTester 和 957863300 （微信昵称叫“老徐”的多的去了，都与本人无关，也不认识<居然经常有冒充的 ... >）；

推荐文章：

从一次面试题总结说起（99%问题，平时文章都有写）

21天，搞定软件测试从业者必备的Linux命令（完整篇）

21天打卡落地这事，今年会一直持续下去；包括各种硬技能、软技能、通用技能；如 Linux / SQL / Git / Jenkins / Python / 接口 / 性能 / 简历面试 / 管理 / Docker / 各种测试辅助工具Jmeter、Fiddler、Postman 等。

坐等。

期待已久的21天Jenkins打卡，5天后启动，阅读原文 ，参与。

<End>

我是IDO老徐，isTester，十年测试职业老鸟，某公司质量部Leader，持续输出原创职业观点、经验、答疑解惑。希望通过自己的文字改变测试职业现状，让测试从业者整体水平提升一个Level 。

这是所有的21天打卡系列

或者，阅读原文

继续滑动看下一个