学习一门新技能,最难的是不知道到底要学啥;没法聚焦精力,锁定目标,把这门技能掌握;
比如,SQL,如果是从零开始,把所有SQL相关的语句都学一遍,可能得几年,而且95%的同学会坚持不下去,直接放弃 ;
想想当年大学,那么厚厚的一门SQL书籍(后台回复 MYSQL 看电子书),看完 + 练习实操,每个1 - 2年,是搞不定的 ;
作为职场人,学一门技能是用来解决日常工作问题的,没必要从头到尾把这块知识弄透,没那么多时间 ;
基于此,老徐根据自己的经验,把软件测试从业者需要掌握的SQL知识,整理如下;大家跟着这个顺序,从头到尾执行即可 ;
如果不知道测试从业者需要掌握哪些数据库知识,见前几天的文章:-> 软件测试从业者,需掌握的数据库体系知识
开始之前的,前置准备事项:
1、在自己电脑上安装一个mysql数据库,文章见 -> 软件测试从业者必备 之 MYSQL从零到入坑 。
2、找一个mysql客户端链接工具:初学者,推荐 Navicat
OK ,如上准备完成后,接下来就是实操了 。
-
注:关于SQL的注释
--:表示单行注释
/*…*/:用于多行(块)注释
>说明:如下SQL,基于MYSQL
作者:IDO老徐 ,博客http://isTester.com
1.创建数据库:
create DATABASE istester
2.删除数据库:
drop DATABASE istester
3.创建新表:
1)创建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)
);
2)根据已有的表创建新表(复制表)
create table istester2 like istester ;
create table idoxu2 as select * from idoxu where 2=1;
create table idoxu2 as select id,stu_id,istester from idoxu where 1<>1;
4.删除表:
drop table istester2
drop table idoxu,idoxu2,istester,istester99
5.增加表字段:
Alter table istester add column istester6 VARCHAR(20) NOT NULL
6.主键
-- 添加主键:
Alter table istester add primary key(idoxu)
-- 删除主键:
Alter table istester drop primary key(idoxu)
7.几个简单的基本入门sql语句
插入:
insert into istester(id,uname,idoxu) values(1,"idoxu",2020);
insert into istester(id,uname,idoxu) values(2,"idoxu2",2020);
insert into idoxu(id,stu_id,c_name,grade) values(4,11,"idoxu",90),(5,12,"lin",100),(6,33,"6ido",20);
insert into istester(id,sex,idoxu) values(11,1,2020),(12,2,2020),(13,2,2020),(14,1,2020);
选择:
select * from istester
select * from istester where id = 1
删除:
delete from istester where id = 1
更新:
update istester set uname="idoxu666" where id = 12
查找:
select * from istester where uname like '%idoxu%'
排序:
select * from istester order by id desc
总数:
select count(id) as totalcount from istester
求和:
select sum(grade) from idoxu
平均:
select avg(id) as avgvalue from istester
最大:
select max(id) as "maxvalue" from istester
最小:
select min(id) as "minvalue" from istester
8.模糊查找(like)
select * from istester where uname like "%ido%" order by id desc limit 10;
select * from istester t where t.uname like "ido%" order by id desc limit 10;
select * from istester.idoxu s where s.stu_id like "%2%";
9.拷贝表数据(从其他表)
INSERT into istester2 SELECT * FROM istester;
INSERT INTO idoxu(id,stu_id,grade) SELECT id,id,idoxu FROM istester;
10.修改表名
-- ALTER TABLE 旧表名 RENAME TO 新表名 ;
ALTER TABLE idoxu3 RENAME TO idoxu6
11.修改表字段名
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
alter table istester CHANGE uname aname varchar(60);
14.跨数据库之间表的拷贝
CREATE TABLE istester10 LIKE istester.idoxu;
15.查询between的使用
select * from idoxu where grade between 1 and 30;
select id,stu_id,c_name,grade from idoxu where grade not between 1 and 30;
16.查询 in 的使用
select id,stu_id,c_name,grade from idoxu where grade in (10,20,90);
select id,stu_id,c_name,grade from idoxu where grade not in (10,20,90);
作者:IDO老徐 ,博客http://isTester.com
17.子查询
select id,stu_id,c_name,grade from idoxu where id in (select id from istester);
18.多表(左链接、右链接、内链接)
-- 左链接
select i.id,i.stu_id,i.c_name,i.grade from idoxu i LEFT join istester t ON i.id = t.id;
-- 右链接
select i.id,i.stu_id,i.c_name,i.grade from idoxu i RIGHT join istester t ON i.id = t.id;
-- 内链接
select i.id,i.stu_id,i.c_name,i.grade from idoxu i inner join istester t ON i.id = t.id;
19.
select * from (Select id,stu_id,c_name,grade FROM idoxu ) d where d.id > 10
20.四表联查
select i.id,i.stu_id,i.c_name,i.grade from idoxu i LEFT join istester t ON i.id = t.id right join istester2 c on i.id=c.id inner join idoxu2 d on i.id=d.id where 1=1;
21.
select d.* from (select d.id,d.stu_id,d.c_name,d.grade from idoxu d order by grade desc limit 10) i,idoxu d where i.id = d.id order by stu_id desc limit 10;
22.选择从10到15的记录
select * from (select * from idoxu order by id asc limit 15) i order by id desc limit 5
23.创建视图
create view istester_view as select id,stu_id,c_name,grade from idoxu where id in (select id from istester);
24.删除视图
drop view istester_view
注:先列这些,后续再弄一个V2.0
还记得的之前的Linux体系吗(那篇文章,跟着实操,可以搞定软测从业者需要的Linux命令) ?