一、資料庫概述
1.為什么軟體測驗工程師還需要學習資料庫以及開發方面的知識？
測驗工程師的目的是找出軟體的不足，并告訴開發工程師，出現問題的環境，操作步驟和輸入輸出資料；優秀的測驗工程師，需要告訴開發團隊，軟體的不足，這類不足會導致什么情況，如何避免，以及如何去修改（這是為什么高級軟體測驗工程師比開發工程師工資高的原因），
測驗工程師在測驗軟體程序中，不僅僅需要在界面進行操作，還需要檢查資料庫中的資料是否正確，從而在軟體出現問題時候，能夠定位到問題原因；
學習資料庫，掌握資料庫操作，增加面試成功機會，可以提高工資，
2. 什么是資料庫
Excle的資料的確很方便，但是對于企業來說就不一樣了，一個公司里面可能有成千上萬的Excel表格，還在不同的電腦上，而他們的員工和客戶需要實時看到企業給他們提供的所有資料，這種檔案管理的方法就很麻煩，總不能每分鐘都把一個新的巨大無比的Excel檔案發給所有客戶呀，
資料庫是按照資料的結構來組織、存盤、和管理資料的倉庫，簡而言之，就是存放資料的倉庫，正是因為有了資料庫后，所有人可以直接在這個系統上查找資料和修改資料，例如你每天使用余額寶查看自己的賬戶收益，就是從后臺資料庫讀取資料后給你的，
資料庫的英文名稱叫DB(Database)，那么資料庫里面有什么東東呢？
其實，資料庫通常包含一個或多個表組成，如果你用過Excel，就會知道Excel是一張一張的二維表，每個表都是由列和行組成的，其中每一列都用名字來標識出來，同樣的，資料庫里存放的也是一張一張的表，只不過各個表之間是有聯系的，所以，簡單來說：資料庫=多張表+各表之間的關系
其實資料庫是邏輯上的概念，它是一堆互相關聯的資料，放在物理物體上，是一堆寫在磁盤上的檔案，檔案中有資料，這些最基礎的資料組成了表(table)，
3. 常見資料庫
Oracle是最掙錢的資料庫，出自甲骨文公司，市場占有率非常高，功能非常強大，被一些大型企業，電信，銀行，證券公司、金融公司所使用，市場占有率第一，
MySQL是一個關系型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬于 Oracle 旗下產品，MySQL 是最流行的關系型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關系資料庫管理系統) 應用軟體，MySQL目前最流行的開源資料庫，被甲骨文公司所占用，威脅著老大Oracle的地位，開源，免費，支持多平臺，簡單易學易操作，市場占有率第二，
SQLServer是微軟開發的資料庫，針對服務器，僅支持Windows作業系統，號稱是windows上最好用的資料庫，資料庫的天下不僅僅是MySQL和Oracle的，SQLServer也正在慢慢崛起，微軟網羅了不少資料庫的專家，推出了不少重磅功能，
MongoDB，最好用的檔案型資料庫，是NOSQL型別資料庫的領導者之一，也是當前最成功的NoSQL型別資料庫，資料存盤格式采用JSON形式，非常靈活，
Redis，最好的記憶體級資料庫，查詢效率極高，并且在Redis 3.0之后，支持多種資料型別，String，Set，List，Hash等型別，開始支持集群，彌補了自身短板，是目前做快取最流行的資料庫
Neo4J，最好的圖形化資料庫，流行話較低，但是圖形化資料庫的絕對領導者，
SQLite，最流行的嵌入式資料庫，占領手機行業的絕對領導者地位，Android和IOS兩大手機系統，都內嵌了SQLite資料庫，SQLite是一個完整的關系型資料庫，支持標準SQL，支持事務操作，程式包非常小，是嵌入式設備的最佳選擇，
各個資料庫軟體的使用差別大同小異，但是因為MySQL是開源的，成為各大公司使用的主流，面試也主要以MySQL為主，
4.資料庫和SQL是什么關系？
結構化查詢語言(Structured Query Language)簡稱SQL，是一種特殊目的的編程語言，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統；同時也是資料庫腳本檔案的擴展名，
資料庫里面放著資料，而SQL是用來操作資料庫里資料的語言（工具），
例如有一碗米飯（碗就是mysql，里面放的米是資料），你要吃碗里的米飯，拿什么吃？拿筷子（sql），用筷子（sql）操作碗里（mysql）的米飯（資料）
二.、MySQL資料庫的安裝配置
大飛聊MySQL
三、SQL陳述句分類
3.1. DDL-資料庫定義語言
3.1.1. 概念及作用
database defination language 資料庫定義語言，用于定義資料庫，用于定義表結構
3.1.2. 表中欄位基本資料型別
MySQL支持多種型別，大致可以分為三類：數值、日期/時間和字串(字符)型別
一個漢字占多少長度與編碼有關：
UTF－8：一個漢字＝3個位元組
GBK：一個漢字＝2個位元組
varchar(n) 表示 n 個字符，無論漢字和英文，Mysql 都能存入 n 個字符，僅是實際位元組長度有所區別
3.1.3. 資料庫陳述句操作
在登陸 MySQL 服務后，使用 create 命令創建資料庫，語法如下:
CREATE DATABASE 資料庫名;
在洗掉資料庫程序中，務必要十分謹慎，因為在執行洗掉命令后，所有資料將會消失，語法：
在你連接到 MySQL 資料庫后，可能有多個可以操作的資料庫，所以你需要選擇你要操作的資料庫
注意:所有的資料庫名，表名，表欄位都是區分大小寫的，所以你在使用SQL命令時需要輸入正確的名稱，
查看資料庫創建細節
show create database mydb1
3.1.4. 表結構陳述句操作
選擇資料庫
創建MySQL資料表需要以下資訊：
表名
表欄位名
定義每個表欄位\
3.1.5.定義表的約束
如果你不想欄位為 NULL 可以設定欄位的屬性為 NOT NULL， 在操作資料庫時如果輸入該欄位的資料為NULL ，就會報錯，
AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數值會自動加1，
PRIMARY KEY關鍵字用于定義列為主鍵，為了標識資料庫記錄唯一性，不允許記錄重復，且鍵值不能為空，主鍵也是一個特殊索引， 您可以使用多列來定義主鍵，列間以逗號分隔，
UNIQUE KEY的用途：主要是用來防止資料插入的時候重復的
ENGINE 設定存盤引擎，CHARSET 設定編碼
3.2DML-資料庫操作語言
7.1.概念及作用
DML data manipulation language 資料庫操作語言，用以操作資料庫
7.2.插入資料
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;
通知服務器客戶端查看結果集使用的編碼是 gbk
set character_set_results=gbk;
insert into student(id,name,sex,age) values(3,’王五’,’男’,19);
7.3.洗掉資料
洗掉單條資料
delete from student where id=1;
洗掉所有資料
delete from student;
摧毀表
truncate table student;
7.4.修改資料
設定所有人的年齡加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-資料庫查詢語言
8.1.概念及作用
Data Query Language 資料庫查詢語言
8.2.資料查詢
洗掉student
drop table student
創建資料庫表-學生成績表
create table student(id int primary key auto_increment,name varchar(20) unique not null,chinese float,english float,math float);
添加幾條資料
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);
8.2.1.普通條件查詢
查詢所有學生資訊
select * from student;
查詢id為1的學生資訊
select * from student where id=1;
查詢id為1的學生姓名
select name from student where id=1;
查詢數學成績大于80的同學成績
select * from student where math>80
查詢所有學生成績，并輸出效果為 姓名 語文 英語 數學 效果
select name as 姓名,chinese as 語文,english as 英語,math as 數學 from student
查詢所有成績及數學分+10分
select *,(math+10)from student
統計每個學生的總分
select name,(math+english+chinese) as 總分 from student
查詢總分大于230分的同學
select * from student where (math+english+chinese)>230
查詢數學成績在80-90之間的同學
select * from student where math between 80 and 90
查詢數學語文英語都大于80的同學成績
select * from student where math>80 and english>80 and chinese >80;
查詢數學成績在 80 60 90內的同學
select * from student where math in(80,60,90);
8.2.2.模糊查詢
查詢所有姓名中包含張的同學
select * from student where name like ‘%張%’
8.2.3.排序查詢
按照數學成績從小到大查詢
select * from student order by math;
按照數學成績從大到小查詢
select * from student order by math desc;
8.2.4.分頁查詢
limit是mysql的語法
select * from table limit m,n
其中m是指記錄從m+1開始
，N代表取n條記錄，
select * from student limit 2,4
即取出第3條至第6條，4條記錄
查詢出數學成績由高到低前兩名
select * from student order by math desc limit 0,2;
8.2.5.分組查詢
創建一個訂單表
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 條件運算式：用來分組查詢后指定一些條件來輸出查詢結果
(2) having作用和where一樣，但having只能用于group by
8.3.報表查詢
count 個數
sum 總數
avg 平均數
max 最大值
min 最小值
統計班級里邊有多少學生
select count(*)from student;
統計總成績大于250分的人數
select count(*)from student where (math+english+chinese)>250;
統計班級里邊各科總成績
select sum(math),sum(english),sum(chinese) from student
統計所有科目的總成績
select sum(math+english+chinese) from student;
統計一下語文平均成績
select sum(chinese)/count(*) from student;
select avg(chinese) from student;
統計一下班級語文最高分和最低分
select max(chinese) from student;
select min(chinese) from student;
報表查詢訂單根據名稱合并后，總價格>10000的商品
select * from orders group by product having sum(price) >7000
3.4 資料控制語言
資料控制語言：簡稱【DCL】(Data Control Language)，用來定義資料庫的訪問權限和安全級別，及創建用戶；關鍵字：grant等
四、多表設計
4.1.一對一
一張表的一條記錄一定只能與另外一張表的一條記錄進行對應，反之亦然，
有時候，為了業務，或者避免一張表中資料量過大，過復雜，在開發中會進行一對一方式來設計表，
4.2一對多（1方建主表(id為主鍵欄位), 多方建外鍵欄位）
指的是一個物體的某個資料與另外一個物體的多個資料有關聯關系， 一對多的關系在設計的時候，需要設計表的外鍵
9.1.1.班級表和學生表設計
部門表和員工表設計
9.1.2.創建資料庫表
constraint 約束
foreign key就是表與表之間的某種約定的關系，由于這種關系的存在，能夠讓表與表之間的資料，更加的完整，關連性更強，
foreign key陳述句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)
附：表的外鍵必須是另一張表的主鍵
create table class(id int primary key auto_increment,name varchar(20));
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));
insert into class values(1,‘ceshiban’);
insert into class values(2,‘kaifa’);
insert into student values(1,‘zhangsan’,‘nan’,1);
insert into student values(2,‘lisi’,‘nan’,2);
insert into student values(3,‘jingjing’,‘nan’,2);
select * from student where class_id=(select id from class where id=2);
補一個外鍵的概念（默認是約束): 洗掉主鍵資訊時，當該主鍵欄位值在外鍵表中存在時，該記錄是不能洗掉的，—要把外鍵表是的相關資訊洗掉之后，才能洗掉，
4.3、多對多（ 3個表= 2個物體表 + 1個關系表 )
一個物體的資料對應另外一個物體的多個資料，另外物體的資料也同樣對應當前物體的多個資料，
一個學生可以有多個老師，一個老師可以教多個學生
解決方案：創建一個中間表，專門用來維護多表之間的對應關系，通常是能夠唯一標識出資料的欄位（主鍵）
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=‘張三’));
10.連接查詢
10.1.初始定義表結構
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(‘張老師’,‘內蒙’);
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);
10.2.交叉查詢
交叉查詢，又叫笛卡爾積查詢，會將左表和右表的資訊，做一個乘積將所有資訊查詢出來，會產生臨時表，比較占用記憶體，生成的記錄數=表1 X表2
select * from customer,orders;
select * from customer cross join orders;
10.3.內連接查詢
內連接，inner join on 查詢兩張表，設定條件，將兩張表中對應的資料查詢出來
不會產生笛卡爾積，不會產生臨時表，性能高
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;
10.4.左外連接
左外連接 left join on 設定條件，將兩張表對應的資料查詢出來，同時將左表自己沒有關聯的資料也查詢出來
注意：join前面是左，后面是右
select * from customer c left join orders o on c.id=o.customer_id;
10.5.右外連接
右外連接 right join on 設定條件，將兩張表對應的資料查詢出來，同時將右表自己沒有關聯的所有資料查詢出來
select * from customer c right join orders o on c.id=o.customer_id;
10.6.聯合查詢
select * from customer left join orders on customer.id=orders.customer_id
having price>20;
11.MySQL圖形化工具navicat
11.1.1、安裝介紹
先執行安裝navicat111_mysql.exe檔案
安裝成功之后，執行patchNavicat.exe，
找到所安裝目錄里的navicat.exe檔案，進行破解
11.2.Navicat工具使用步驟
11.2.1.鏈接，mysql，輸入用戶名，密碼
11.2.2.新建庫，滑鼠點擊右鍵
11.2.3.新建表
12.資料庫備份與恢復
使用圖形界面工具：
使用doc命令：
mysqldump –u用戶名 –p密碼 資料庫名>生成的腳本檔案路徑
注意，不要打分號，不要登錄mysql，直接在cmd下運行
注意，生成的腳本檔案中不包含create database陳述句
mysqldump -uroot -proot host>C:\Users\Administrator\Deskt
op\mysql\1.sql
匯入SQL檔案
12.1.恢復
a)使用圖形界面工具：
b)使用doc命令列：
i.不登錄恢復
mysql -u用戶名 -p密碼 資料庫<腳本檔案路徑
注意，不要打分號，不要登錄mysql，直接在cmd下運行
ii.登錄之后恢復
選擇庫 use 庫名稱
Source sql檔案路徑
13.資料庫常用性能優化（了解）
資料庫性能優化這塊，我們考慮比較多的還是查詢這塊，互聯網專案對資料查詢非常頻繁，對效率，性能要求比較高，
查詢這塊優化的話，主要就需要使用索引這種方式，所謂索引就是建立一種快速查找的方式，比如我們查字典,有一個ABCD的索引.
舉個例子,如果我們創建一個表 create table user(id integer ,name varchar(20)，job varchar(20)); 如果我們資料庫中有1000萬條資料，當我查詢 select * from user where name=’張三’的時候,這種查詢方式就類似于整個資料庫的掃描，效率非常低，
我們可以給這個name設定一個索引create index n on user (name); 這是設定一種普通（normal）索引，然后我們查詢的時候，有了這個索引，效率就會大大提升，當然對于索引，它的方式有BTree型別和Hash型別，是兩種管理資料庫索引的方式，這個我沒有深入研究，這個我們可以自己設定，默認是btree，
索引型別的話，有normal（普通型別）型別，還有unique（唯一型別），fulltext全文索引
主鍵索引，還有非空索引，還有聚集索引，
主鍵索引，primary key 在設定的時候，已經指定了，其實也是非空索引，
非空索引是not null,設定這種方式的該欄位下內容不能為空，
聚集索引(聯合索引)，是在設定多個查詢條件的時候使用，比如 創建一張表，有名字，有作業，我們想經常頻繁的用到名字和作業它倆結合在一起來查詢資料庫中表的資料，這個時候，可以將名字和作業指定為聚集索引，create index m on user(name,job); 這樣當我們指定select * from user where name=xxx and job=xxx的時候，就會按照索引方式來做，
這種優化方式就是索引優化，在使用索引優化方案的時候，我們需要注意避免在索引欄位上使用條件函式等操作，
了解：
Show index form orders;查看索引
14.資料庫性能檢測方式（了解）
在設計SQL的時候，我們一般會使用explain（分析） https://segmentfault.com/a/1190000008131735檢測sql，看是否使用到索引，避免出現整表搜索方式查詢[filesort(不是以索引方式的檢索，我們叫做filesort)]（我在這張表中把gender設定成normal索引，name沒有任何設定）
對比看的，對有索引的欄位,在檢測的時候，會顯示是一個參考的key，
explain select*from tb_product where title=’’;
還可以使用profiling方式檢測資料庫執行的方式，可以查詢sql的運行時間，http://www.jb51.net/article/31870.htm
注釋：查看profiling資訊，show variables like ‘%profiling%’;
第一步：set profiling=1;（開啟profiling）
第二步運行：select title from tb_product ;
第三步：查看運行時間show profiles;
Duration：持續時間，事件花費的時間總計（以毫秒為單位？）
在這里推薦一個軟體測驗資源，測驗面試題以及行業資訊，大家可以在群中積極交流技術，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/252497.html
標籤：其他
上一篇：Maven專案的配置開發腳本詳解