一、數(shù)據(jù)庫概述
1.為什么軟件測試工程師還需要學習數(shù)據(jù)庫以及開發(fā)方面的知識？
測試工程師的目的是找出軟件的不足，并告訴開發(fā)工程師，出現(xiàn)問題的環(huán)境，操作步驟和輸入輸出數(shù)據(jù)；優(yōu)秀的測試工程師，需要告訴開發(fā)團隊，軟件的不足，這類不足會導致什么情況，如何避免，以及如何去修改（這是為什么高級軟件測試工程師比開發(fā)工程師工資高的原因）。
測試工程師在測試軟件過程中，不僅僅需要在界面進行操作，還需要檢查數(shù)據(jù)庫中的數(shù)據(jù)是否正確，從而在軟件出現(xiàn)問題時候，能夠定位到問題原因；
學習數(shù)據(jù)庫，掌握數(shù)據(jù)庫操作，增加面試成功機會，可以提高工資。
2. 什么是數(shù)據(jù)庫
Excle的數(shù)據(jù)的確很方便，但是對于企業(yè)來說就不一樣了。一個公司里面可能有成千上萬的Excel表格，還在不同的電腦上，而他們的員工和客戶需要實時看到企業(yè)給他們提供的所有數(shù)據(jù)，這種文件管理的方法就很麻煩，總不能每分鐘都把一個新的巨大無比的Excel文件發(fā)給所有客戶呀。
數(shù)據(jù)庫是按照數(shù)據(jù)的結(jié)構(gòu)來組織、存儲、和管理數(shù)據(jù)的倉庫，簡而言之，就是存放數(shù)據(jù)的倉庫。正是因為有了數(shù)據(jù)庫后，所有人可以直接在這個系統(tǒng)上查找數(shù)據(jù)和修改數(shù)據(jù)。例如你每天使用余額寶查看自己的賬戶收益，就是從后臺數(shù)據(jù)庫讀取數(shù)據(jù)后給你的。
數(shù)據(jù)庫的英文名稱叫DB(Database)，那么數(shù)據(jù)庫里面有什么東東呢？
其實，數(shù)據(jù)庫通常包含一個或多個表組成。如果你用過Excel，就會知道Excel是一張一張的二維表。每個表都是由列和行組成的，其中每一列都用名字來標識出來。同樣的，數(shù)據(jù)庫里存放的也是一張一張的表，只不過各個表之間是有聯(lián)系的。所以，簡單來說：數(shù)據(jù)庫=多張表+各表之間的關(guān)系
其實數(shù)據(jù)庫是邏輯上的概念，它是一堆互相關(guān)聯(lián)的數(shù)據(jù)，放在物理實體上，是一堆寫在磁盤上的文件，文件中有數(shù)據(jù)。這些最基礎的數(shù)據(jù)組成了表(table)。
3. 常見數(shù)據(jù)庫
Oracle是最掙錢的數(shù)據(jù)庫，出自甲骨文公司，市場占有率非常高，功能非常強大，被一些大型企業(yè)，電信，銀行，證券公司、金融公司所使用，市場占有率第一。
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng)) 應用軟件。MySQL目前最流行的開源數(shù)據(jù)庫，被甲骨文公司所占用，威脅著老大Oracle的地位，開源，免費，支持多平臺，簡單易學易操作，市場占有率第二。
SQLServer是微軟開發(fā)的數(shù)據(jù)庫，針對服務器，僅支持Windows操作系統(tǒng)，號稱是windows上最好用的數(shù)據(jù)庫。數(shù)據(jù)庫的天下不僅僅是MySQL和Oracle的，SQLServer也正在慢慢崛起，微軟網(wǎng)羅了不少數(shù)據(jù)庫的專家，推出了不少重磅功能。
MongoDB，最好用的文檔型數(shù)據(jù)庫，是NOSQL類型數(shù)據(jù)庫的領導者之一，也是當前最成功的NoSQL類型數(shù)據(jù)庫，數(shù)據(jù)存儲格式采用JSON形式，非常靈活。
Redis，最好的內(nèi)存級數(shù)據(jù)庫，查詢效率極高，并且在Redis 3.0之后，支持多種數(shù)據(jù)類型，String，Set，List，Hash等類型，開始支持集群，彌補了自身短板。是目前做緩存最流行的數(shù)據(jù)庫
Neo4J，最好的圖形化數(shù)據(jù)庫，流行話較低，但是圖形化數(shù)據(jù)庫的絕對領導者。
SQLite，最流行的嵌入式數(shù)據(jù)庫，占領手機行業(yè)的絕對領導者地位，Android和IOS兩大手機系統(tǒng)，都內(nèi)嵌了SQLite數(shù)據(jù)庫，SQLite是一個完整的關(guān)系型數(shù)據(jù)庫，支持標準SQL，支持事務操作，程序包非常小，是嵌入式設備的最佳選擇。
各個數(shù)據(jù)庫軟件的使用差別大同小異，但是因為MySQL是開源的，成為各大公司使用的主流，面試也主要以MySQL為主。
4.數(shù)據(jù)庫和SQL是什么關(guān)系？
結(jié)構(gòu)化查詢語言(Structured Query Language)簡稱SQL，是一種特殊目的的編程語言，是一種數(shù)據(jù)庫查詢和程序設計語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)；同時也是數(shù)據(jù)庫腳本文件的擴展名。
數(shù)據(jù)庫里面放著數(shù)據(jù)，而SQL是用來操作數(shù)據(jù)庫里數(shù)據(jù)的語言（工具）。
例如有一碗米飯（碗就是mysql，里面放的米是數(shù)據(jù)），你要吃碗里的米飯，拿什么吃？拿筷子（sql）。用筷子（sql）操作碗里（mysql）的米飯（數(shù)據(jù)）
三、SQL語句分類
3.1. DDL-數(shù)據(jù)庫定義語言
3.1.1. 概念及作用
database defination language 數(shù)據(jù)庫定義語言，用于定義數(shù)據(jù)庫，用于定義表結(jié)構(gòu)
3.1.2. 表中字段基本數(shù)據(jù)類型
MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時間和字符串(字符)類型
一個漢字占多少長度與編碼有關(guān)：
UTF－8：一個漢字＝3個字節(jié)
GBK：一個漢字＝2個字節(jié)
varchar(n) 表示 n 個字符，無論漢字和英文，Mysql 都能存入 n 個字符，僅是實際字節(jié)長度有所區(qū)別
3.1.3. 數(shù)據(jù)庫語句操作
在登陸 MySQL 服務后，使用 create 命令創(chuàng)建數(shù)據(jù)庫，語法如下:
CREATE DATABASE 數(shù)據(jù)庫名;
//創(chuàng)建第一個數(shù)據(jù)庫 mydb1
create database mydb1
在刪除數(shù)據(jù)庫過程中，務必要十分謹慎，因為在執(zhí)行刪除命令后，所有數(shù)據(jù)將會消失。語法：
//刪除創(chuàng)建的數(shù)據(jù)庫
drop database mydb1;
在你連接到 MySQL 數(shù)據(jù)庫后，可能有多個可以操作的數(shù)據(jù)庫，所以你需要選擇你要操作的數(shù)據(jù)庫
//選擇數(shù)據(jù)庫
user mydb1
注意:所有的數(shù)據(jù)庫名，表名，表字段都是區(qū)分大小寫的。所以你在使用SQL命令時需要輸入正確的名稱。
查看數(shù)據(jù)庫創(chuàng)建細節(jié)
show create database mydb1
創(chuàng)建一個使用gbk字符集的數(shù)據(jù)庫
create database mydb2 character set gbk
3.1.4. 表結(jié)構(gòu)語句操作
選擇數(shù)據(jù)庫
use mydb1
創(chuàng)建MySQL數(shù)據(jù)表需要以下信息：
表名、表字段名、定義每個表字段
//創(chuàng)建表
create table student(id int,name varchar(20),sex varchar(20),age int,salery float(6,2),birthday date)
//刪除表
drop table student;
//查看所有表
show tables
//查看表的創(chuàng)建細節(jié)
show create table student;
//展示表結(jié)構(gòu)
desc student
// 在原有的學生基礎上添加address列
alter table student add address varchar(20)
//在原有的學生基礎上刪除address列
alter table student drop address
3.1.5. 定義表的約束
create table student(id int primary key auto_increment,name varchar(20) unique not null,sex varchar(20),age int,salery float(6,2),birthday date)
如果你不想字段為 NULL 可以設置字段的屬性為 NOT NULL， 在操作數(shù)據(jù)庫時如果輸入該字段的數(shù)據(jù)為NULL ，就會報錯。
AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數(shù)值會自動加1。
PRIMARY KEY關(guān)鍵字用于定義列為主鍵。為了標識數(shù)據(jù)庫記錄唯一性，不允許記錄重復，且鍵值不能為空，主鍵也是一個特殊索引。 您可以使用多列來定義主鍵，列間以逗號分隔。
UNIQUE KEY的用途：主要是用來防止數(shù)據(jù)插入的時候重復的
ENGINE：設置存儲引擎
CHARSET： 設置編碼
3.2 DML - 數(shù)據(jù)庫操作語言
3.2.1. 概念及作用
DML：data manipulation language 數(shù)據(jù)庫操作語言，用以操作數(shù)據(jù)庫。
3.2.2. 插入數(shù)據(jù)
//插入數(shù)據(jù)
insert into student values(1,’zhangsan’,’nan’,19,389.10,’1999-10-10’);
//查詢
select * from student
問題一：插入中文會報錯
insert into student values(2,’李四’,’男’,19,389.10,’1999-10-10’);
解決：
//通知服務器客戶端使用的編碼是gbk
set character_set_client=gbk;
//通知服務器客戶端查看結(jié)果集使用的編碼是 gbk
set character_set_results=gbk;
insert into student(id,name,sex,age) values(3,’王五’,’男’,19);
問題二：數(shù)據(jù)庫命令框如果有中文就亂碼
charset gbk;
3.2.3. 刪除數(shù)據(jù)
//刪除單條數(shù)據(jù)
delete from student where id=1;
//刪除所有數(shù)據(jù)，不刪除結(jié)構(gòu)，會放到日志中，事務提交后才生效
delete from student;
//摧毀表，刪除表中所有數(shù)據(jù)，不刪除結(jié)構(gòu)，立即生效
truncate table student;
注意：delete from student;與truncate table student;都能刪除該表中所有數(shù)據(jù)，區(qū)別：前者刪除后自增主鍵還在，后者主鍵會從1開始。
3.2.4. 修改數(shù)據(jù)
//設置所有人的年齡加10歲
update student set age=age+10
//修改zhangsan 為張三
update student set name=’張三’ where name=’zhangsan’
//修改王五的salery和出生日期
update student set salery=100.01,birthday=’1999-10-10’ where id=3;
3.3 DQL-數(shù)據(jù)庫查詢語言
3.3.1.概念及作用
Data Query Language 數(shù)據(jù)庫查詢語言
3.3.2.數(shù)據(jù)查詢
//刪除student
drop table student
//創(chuàng)建數(shù)據(jù)庫表-學生成績表
create table student(id int primary key auto_increment,name varchar(20) unique not null,chinese float,english float,math float);
//添加幾條數(shù)據(jù)
insert into student values(1,’張三’,90,80,80);
insert into student values(2,’李四’,90,87,60);
insert into student values(3,’王五’,70,60,69);
insert into student values(4,’趙六’,99,90,87);
//查詢所有學生信息
select * from student;
//查詢id為1的學生信息
select * from student where id=1;
//查詢id為1的學生姓名
select name from student where id=1;
//查詢數(shù)學成績大于80的同學成績
select * from student where math>80
//查詢所有學生成績，并輸出效果為 姓名 語文 英語 數(shù)學 效果，見下圖：
select name as 姓名,chinese as 語文,english as 英語,math as 數(shù)學 from student
//查詢所有成績及數(shù)學分+10分
select *,(math+10)from student
//統(tǒng)計每個學生的總分
select name,(math+english+chinese) as 總分 from student
//查詢總分大于230分的同學
select * from student where (math+english+chinese)>230
//查詢數(shù)學成績在80-90之間的同學
select * from student where math between 80 and 90
//查詢數(shù)學語文英語都大于80的同學成績
select * from student where math>80 and english>80 and chinese >80;
//查詢數(shù)學成績在 80 60 90內(nèi)的同學，即數(shù)學成績有60、80、90的。
select * from student where math in(80,60,90);
//模糊查詢
// _? 代表一個，%代表多個（0 - 無限）
//查詢所有姓名中包含張的同學
select * from student where name like ‘%張%’
查詢所有學生成績，并輸出效果.png
3.3.3. 排序查詢
MySQL中 升序為asc，降序為desc
例如：
升序：select * from 表名 order by 表中的字段 asc(MySQL中默認是升序排列，可不寫) ；
降序：select * from 表名 order by 表中的字段 desc ；
若要進行同時一個升序一個降序 例如：
order by 升序字段 asc，降序字段 desc ；
//按照數(shù)學成績從小到大查詢
select * from student order by math;
//按照數(shù)學成績從大到小查詢
select * from student order by math desc;
3.3.4.分頁查詢
limit是mysql的分頁查詢語法:
select * from table limit m,n
其中m是指記錄從m+1開始,，N代表取n條記錄。
//取出第3條至第6條，4條記錄
select * from student limit 2,4
//查詢出數(shù)學成績由高到低前兩名
select * from student order by math desc limit 0,2;
3.3.5.分組查詢
分組查詢得到結(jié)果是第一次查到的某個組別。
//創(chuàng)建一個訂單表
create table employee(id int,name varchar(20),sex varchar(20),age int);
insert into employee values(1,'sunsan','男',18);
insert into employee values(2,'lisi','男',18);
insert into employee values(3,'wangwu','女',19);
insert into employee? values(4,'zhaoliu','男',15);
//分組查詢
select * from employee group by sex;
//分組查詢加條件
select * from employee group by sex having age>18;
(1) having 條件表達式：用來分組查詢后指定一些條件來輸出查詢結(jié)果
(2) having作用和where一樣，但having只能用于group by
3.3.6. 報表查詢
count 個數(shù)
sum 總數(shù)
avg 平均數(shù)
max 最大值
min 最小值
//統(tǒng)計班級里邊有多少學生
select count(*)from student;
//統(tǒng)計總成績大于250分的人數(shù)
select count(*)from student where (math+english+chinese)>250;
//統(tǒng)計班級里邊各科總成績
select sum(math),sum(english),sum(chinese) from student
//統(tǒng)計所有科目的總成績
select sum(math+english+chinese) from student;
//統(tǒng)計一下語文平均成績
select sum(chinese)/count(*) from student;
select avg(chinese) from student;
//統(tǒng)計一下班級語文最高分和最低分
select max(chinese) from student;
select min(chinese) from student;
//報表查詢訂單根據(jù)名稱合并后，總價格>10000的商品
select * from orders group by product having sum(price) >7000
3.4 數(shù)據(jù)控制語言
數(shù)據(jù)控制語言：簡稱【DCL】(Data Control Language)，用來定義數(shù)據(jù)庫的訪問權(quán)限和安全級別，及創(chuàng)建用戶；關(guān)鍵字：grant等
四、多表設計
4.1.一對一
一張表的一條記錄一定只能與另外一張表的一條記錄進行對應，反之亦然。
有時候，為了業(yè)務，或者避免一張表中數(shù)據(jù)量過大，過復雜，在開發(fā)中會進行一對一方式來設計表。
一對一.png
4.2. 一對多（1方建主表(id為主鍵字段), 多方建外鍵字段）
一個實體的某個數(shù)據(jù)與另外一個實體的多個數(shù)據(jù)有關(guān)聯(lián)關(guān)系， 一對多的關(guān)系在設計的時候，需要設計表的外鍵。
4.2.1. 班級表和學生表設計
image.png
image.png
部門表和員工表設計
image.png
4.2.2.創(chuàng)建數(shù)據(jù)庫表
constraint 約束
foreign key就是表與表之間的某種約定的關(guān)系，由于這種關(guān)系的存在，能夠讓表與表之間的數(shù)據(jù)，更加的完整，關(guān)連性更強。
foreign key語句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)
注意：表的外鍵必須是另一張表的主鍵
//創(chuàng)建班級表
create table class(id int primary key auto_increment,name varchar(20));
//創(chuàng)建學生表
create table student(id int primary key auto_increment,name varchar(20),sex varchar(20),class_id int,constraint? foreign key(class_id) references class(id));
//插入班級數(shù)據(jù)
insert into class values(1,'ceshiban');
insert into class values(2,'kaifa');
//插入學生數(shù)據(jù)
insert into student values(1,'zhangsan','nan',1);
insert into student values(2,'lisi','nan',2);
insert into student values(3,'jingjing','nan',2);
//聯(lián)查
select * from student where class_id=(select id from class where id=2);
補一個外鍵的注意（默認是約束): 刪除主鍵信息時，當該主鍵字段值在外鍵表中存在時，該記錄是不能刪除的。---要把外鍵表是的相關(guān)信息刪除之后，才能刪除。
子查詢：嵌套在其他查詢中的查詢。
4.3、多對多（ 3個表= 2個實體表 + 1個關(guān)系表 )
一個實體的數(shù)據(jù)對應另外一個實體的多個數(shù)據(jù)，另外實體的數(shù)據(jù)也同樣對應當前實體的多個數(shù)據(jù)。
一個學生可以有多個老師，一個老師可以教多個學生
解決方案：創(chuàng)建一個中間表，專門用來維護多表之間的對應關(guān)系，通常是能夠唯一標識出數(shù)據(jù)的字段（主鍵）
create table teacher(id int primary key,name varchar(100));
create table student (id int primary key,name varchar(100));
create table teacher_student(teacher_id int,student_id int,constraint foreign key(teacher_id) references teacher(id),constraint foreign key(student_id) references student(id));
insert into teacher values(1,'梁老師');
insert into teacher values(2,'李老師');
insert into student values(1,”張三”);
insert into student values(2,”李四”);
insert into teacher_student values(1,1);
insert into teacher_student values(1,2);
insert into teacher_student values(2,1);
insert into teacher_student values(2,2);
//查詢李老師所教的學生
select id from teacher where name=’李老師’
select student_id from teacher_student where teacher_id=id
select * from student where id in(select student_id from teacher_student where teacher_id =(select id from teacher where name='李老師'));
//查詢張三的所有老師
select * from teacher where id in(select teacher_id from teacher_student where student_id=(select id from student where name='張三'));
五、 連表查詢
分類：內(nèi)連接、外連接、交叉連接
5.1. 初始定義表結(jié)構(gòu)
create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));
create table orders(id int primary key auto_increment,good_name varchar(20),price float(8,2),customer_id int);
insert into customer (name,city) values('李老師','東北');
insert into customer (name,city) values('崔老師','山西');
insert into customer (name,city) values('張老師','內(nèi)蒙');
insert into customer (name,city) values('閆老師','天津');
insert into orders(good_name,price,customer_id) values('電腦',59,1);
insert into orders(good_name,price,customer_id) values('筆記本',88,2);
insert into orders(good_name,price,customer_id) values('吹風機',99,1);
insert into orders(good_name,price,customer_id) values('香水',300,3);
insert into orders(good_name,price,customer_id) values('牛奶',100,6);
5.2.交叉查詢
交叉查詢，又叫笛卡爾積查詢，會將左表和右表的信息，做一個乘積將所有信息查詢出來，會產(chǎn)生臨時表，比較占用內(nèi)存，生成的記錄數(shù)=表1 X表2
select * from customer,orders;
select * from customer cross join orders;
5.3. 內(nèi)連接查詢
內(nèi)連接，inner join on 查詢兩張表，設定條件，將兩張表中對應的數(shù)據(jù)查詢出來
不會產(chǎn)生笛卡爾積，不會產(chǎn)生臨時表，性能高
select * from customer c inner join orders o on c.id=o.customer_id;
select * from customer,orders where customer.id=orders.customer_id;
select * from customer c,orders o where c.id=o.customer_id;
5.4. 左外連接
左外連接 left join on 設定條件，將兩張表對應的數(shù)據(jù)查詢出來，同時將左表自己沒有關(guān)聯(lián)的數(shù)據(jù)也查詢出來
注意：join前面是左，后面是右
select * from customer c left join orders o on c.id=o.customer_id;
5.5. 右外連接
右外連接 right join on 設定條件，將兩張表對應的數(shù)據(jù)查詢出來，同時將右表自己沒有關(guān)聯(lián)的所有數(shù)據(jù)查詢出來
select * from customer c right join orders o on c.id=o.customer_id;
5.6. 聯(lián)合查詢
select * from customer left join orders on customer.id=orders.customer_id
having price>20;
