數(shù)據(jù)庫(kù)

一、數(shù)據(jù)庫(kù)概述

1.為什么軟件測(cè)試工程師還需要學(xué)習(xí)數(shù)據(jù)庫(kù)以及開(kāi)發(fā)方面的知識(shí)？

  • 測(cè)試工程師的目的是找出軟件的不足，并告訴開(kāi)發(fā)工程師，出現(xiàn)問(wèn)題的環(huán)境，操作步驟和輸入輸出數(shù)據(jù)；優(yōu)秀的測(cè)試工程師，需要告訴開(kāi)發(fā)團(tuán)隊(duì)，軟件的不足，這類不足會(huì)導(dǎo)致什么情況，如何避免，以及如何去修改（這是為什么高級(jí)軟件測(cè)試工程師比開(kāi)發(fā)工程師工資高的原因）。
  • 測(cè)試工程師在測(cè)試軟件過(guò)程中，不僅僅需要在界面進(jìn)行操作，還需要檢查數(shù)據(jù)庫(kù)中的數(shù)據(jù)是否正確，從而在軟件出現(xiàn)問(wèn)題時(shí)候，能夠定位到問(wèn)題原因；
  • 學(xué)習(xí)數(shù)據(jù)庫(kù)，掌握數(shù)據(jù)庫(kù)操作，增加面試成功機(jī)會(huì)，可以提高工資。

2. 什么是數(shù)據(jù)庫(kù)

  • Excle的數(shù)據(jù)的確很方便，但是對(duì)于企業(yè)來(lái)說(shuō)就不一樣了。一個(gè)公司里面可能有成千上萬(wàn)的Excel表格，還在不同的電腦上，而他們的員工和客戶需要實(shí)時(shí)看到企業(yè)給他們提供的所有數(shù)據(jù)，這種文件管理的方法就很麻煩，總不能每分鐘都把一個(gè)新的巨大無(wú)比的Excel文件發(fā)給所有客戶呀。
  • 數(shù)據(jù)庫(kù)是按照數(shù)據(jù)的結(jié)構(gòu)來(lái)組織、存儲(chǔ)、和管理數(shù)據(jù)的倉(cāng)庫(kù)，簡(jiǎn)而言之，就是存放數(shù)據(jù)的倉(cāng)庫(kù)。正是因?yàn)橛辛藬?shù)據(jù)庫(kù)后，所有人可以直接在這個(gè)系統(tǒng)上查找數(shù)據(jù)和修改數(shù)據(jù)。例如你每天使用余額寶查看自己的賬戶收益，就是從后臺(tái)數(shù)據(jù)庫(kù)讀取數(shù)據(jù)后給你的。
  • 數(shù)據(jù)庫(kù)的英文名稱叫DB(Database)，那么數(shù)據(jù)庫(kù)里面有什么東東呢？
    其實(shí)，數(shù)據(jù)庫(kù)通常包含一個(gè)或多個(gè)表組成。如果你用過(guò)Excel，就會(huì)知道Excel是一張一張的二維表。每個(gè)表都是由列和行組成的，其中每一列都用名字來(lái)標(biāo)識(shí)出來(lái)。同樣的，數(shù)據(jù)庫(kù)里存放的也是一張一張的表，只不過(guò)各個(gè)表之間是有聯(lián)系的。所以，簡(jiǎn)單來(lái)說(shuō)：數(shù)據(jù)庫(kù)=多張表+各表之間的關(guān)系
    其實(shí)數(shù)據(jù)庫(kù)是邏輯上的概念，它是一堆互相關(guān)聯(lián)的數(shù)據(jù)，放在物理實(shí)體上，是一堆寫(xiě)在磁盤(pán)上的文件，文件中有數(shù)據(jù)。這些最基礎(chǔ)的數(shù)據(jù)組成了表(table)。

3. 常見(jiàn)數(shù)據(jù)庫(kù)

  • Oracle是最掙錢(qián)的數(shù)據(jù)庫(kù)，出自甲骨文公司，市場(chǎng)占有率非常高，功能非常強(qiáng)大，被一些大型企業(yè)，電信，銀行，證券公司、金融公司所使用，市場(chǎng)占有率第一。

  • MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件。MySQL目前最流行的開(kāi)源數(shù)據(jù)庫(kù)，被甲骨文公司所占用，威脅著老大Oracle的地位，開(kāi)源，免費(fèi)，支持多平臺(tái)，簡(jiǎn)單易學(xué)易操作，市場(chǎng)占有率第二。

  • SQLServer是微軟開(kāi)發(fā)的數(shù)據(jù)庫(kù)，針對(duì)服務(wù)器，僅支持Windows操作系統(tǒng)，號(hào)稱是windows上最好用的數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)的天下不僅僅是MySQL和Oracle的，SQLServer也正在慢慢崛起，微軟網(wǎng)羅了不少數(shù)據(jù)庫(kù)的專家，推出了不少重磅功能。

  • MongoDB，最好用的文檔型數(shù)據(jù)庫(kù)，是NOSQL類型數(shù)據(jù)庫(kù)的領(lǐng)導(dǎo)者之一，也是當(dāng)前最成功的NoSQL類型數(shù)據(jù)庫(kù)，數(shù)據(jù)存儲(chǔ)格式采用JSON形式，非常靈活。

  • Redis，最好的內(nèi)存級(jí)數(shù)據(jù)庫(kù)，查詢效率極高，并且在Redis 3.0之后，支持多種數(shù)據(jù)類型，String，Set，List，Hash等類型，開(kāi)始支持集群，彌補(bǔ)了自身短板。是目前做緩存最流行的數(shù)據(jù)庫(kù)

  • Neo4J，最好的圖形化數(shù)據(jù)庫(kù)，流行話較低，但是圖形化數(shù)據(jù)庫(kù)的絕對(duì)領(lǐng)導(dǎo)者。

  • SQLite，最流行的嵌入式數(shù)據(jù)庫(kù)，占領(lǐng)手機(jī)行業(yè)的絕對(duì)領(lǐng)導(dǎo)者地位，Android和IOS兩大手機(jī)系統(tǒng)，都內(nèi)嵌了SQLite數(shù)據(jù)庫(kù)，SQLite是一個(gè)完整的關(guān)系型數(shù)據(jù)庫(kù)，支持標(biāo)準(zhǔn)SQL，支持事務(wù)操作，程序包非常小，是嵌入式設(shè)備的最佳選擇。

各個(gè)數(shù)據(jù)庫(kù)軟件的使用差別大同小異，但是因?yàn)镸ySQL是開(kāi)源的，成為各大公司使用的主流，面試也主要以MySQL為主。

4.數(shù)據(jù)庫(kù)和SQL是什么關(guān)系？

結(jié)構(gòu)化查詢語(yǔ)言(Structured Query Language)簡(jiǎn)稱SQL，是一種特殊目的的編程語(yǔ)言，是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語(yǔ)言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)；同時(shí)也是數(shù)據(jù)庫(kù)腳本文件的擴(kuò)展名。

數(shù)據(jù)庫(kù)里面放著數(shù)據(jù)，而SQL是用來(lái)操作數(shù)據(jù)庫(kù)里數(shù)據(jù)的語(yǔ)言（工具）。

例如有一碗米飯（碗就是mysql，里面放的米是數(shù)據(jù)），你要吃碗里的米飯，拿什么吃？拿筷子（sql）。用筷子（sql）操作碗里（mysql）的米飯（數(shù)據(jù)）

二、MySQL數(shù)據(jù)庫(kù)的安裝配置

大飛聊MySQL

三、SQL語(yǔ)句分類

3.1. DDL-數(shù)據(jù)庫(kù)定義語(yǔ)言

3.1.1. 概念及作用

database defination language 數(shù)據(jù)庫(kù)定義語(yǔ)言，用于定義數(shù)據(jù)庫(kù)，用于定義表結(jié)構(gòu)

3.1.2. 表中字段基本數(shù)據(jù)類型

MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時(shí)間和字符串(字符)類型

  1. 一個(gè)漢字占多少長(zhǎng)度與編碼有關(guān)：
    UTF－8：一個(gè)漢字＝3個(gè)字節(jié)
    GBK：一個(gè)漢字＝2個(gè)字節(jié)
  2. varchar(n) 表示 n 個(gè)字符，無(wú)論漢字和英文，Mysql 都能存入 n 個(gè)字符，僅是實(shí)際字節(jié)長(zhǎng)度有所區(qū)別
3.1.3. 數(shù)據(jù)庫(kù)語(yǔ)句操作

  1. 在登陸 MySQL 服務(wù)后，使用 create 命令創(chuàng)建數(shù)據(jù)庫(kù)，語(yǔ)法如下:
    CREATE DATABASE 數(shù)據(jù)庫(kù)名;

    //創(chuàng)建第一個(gè)數(shù)據(jù)庫(kù) mydb1
create database mydb1

  2. 在刪除數(shù)據(jù)庫(kù)過(guò)程中，務(wù)必要十分謹(jǐn)慎，因?yàn)樵趫?zhí)行刪除命令后，所有數(shù)據(jù)將會(huì)消失。語(yǔ)法：

    //刪除創(chuàng)建的數(shù)據(jù)庫(kù)
drop database mydb1;

  3. 在你連接到 MySQL 數(shù)據(jù)庫(kù)后，可能有多個(gè)可以操作的數(shù)據(jù)庫(kù)，所以你需要選擇你要操作的數(shù)據(jù)庫(kù)

    //選擇數(shù)據(jù)庫(kù)
user mydb1

注意:所有的數(shù)據(jù)庫(kù)名，表名，表字段都是區(qū)分大小寫(xiě)的。所以你在使用SQL命令時(shí)需要輸入正確的名稱。

  1. 查看數(shù)據(jù)庫(kù)創(chuàng)建細(xì)節(jié)

    show create database mydb1

  2. 創(chuàng)建一個(gè)使用gbk字符集的數(shù)據(jù)庫(kù)

    create database mydb2 character set gbk

3.1.4. 表結(jié)構(gòu)語(yǔ)句操作

  1. 選擇數(shù)據(jù)庫(kù)

    use mydb1

  2. 創(chuàng)建MySQL數(shù)據(jù)表需要以下信息：
    表名、表字段名、定義每個(gè)表字段

    //創(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)建細(xì)節(jié)
show create table student;

//展示表結(jié)構(gòu)
desc student

// 在原有的學(xué)生基礎(chǔ)上添加address列
alter table student add address varchar(20) 

//在原有的學(xué)生基礎(chǔ)上刪除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 可以設(shè)置字段的屬性為 NOT NULL， 在操作數(shù)據(jù)庫(kù)時(shí)如果輸入該字段的數(shù)據(jù)為NULL ，就會(huì)報(bào)錯(cuò)。
  • AUTO_INCREMENT定義列為自增的屬性，一般用于主鍵，數(shù)值會(huì)自動(dòng)加1。
  • PRIMARY KEY關(guān)鍵字用于定義列為主鍵。為了標(biāo)識(shí)數(shù)據(jù)庫(kù)記錄唯一性，不允許記錄重復(fù)，且鍵值不能為空，主鍵也是一個(gè)特殊索引。 您可以使用多列來(lái)定義主鍵，列間以逗號(hào)分隔。
  • UNIQUE KEY的用途：主要是用來(lái)防止數(shù)據(jù)插入的時(shí)候重復(fù)的
  • ENGINE：設(shè)置存儲(chǔ)引擎
  • CHARSET： 設(shè)置編碼

3.2 DML - 數(shù)據(jù)庫(kù)操作語(yǔ)言

3.2.1. 概念及作用

DML：data manipulation language 數(shù)據(jù)庫(kù)操作語(yǔ)言，用以操作數(shù)據(jù)庫(kù)。

3.2.2. 插入數(shù)據(jù)
//插入數(shù)據(jù)
insert into student values(1,’zhangsan’,’nan’,19,389.10,’1999-10-10’);
//查詢
select * from student
  • 問(wèn)題一：插入中文會(huì)報(bào)錯(cuò)
insert into student values(2,’李四’,’男’,19,389.10,’1999-10-10’);
  • 解決：
//通知服務(wù)器客戶端使用的編碼是gbk
set character_set_client=gbk;

//通知服務(wù)器客戶端查看結(jié)果集使用的編碼是 gbk
set character_set_results=gbk;

insert into student(id,name,sex,age) values(3,’王五’,’男’,19);
  • 問(wèn)題二：數(shù)據(jù)庫(kù)命令框如果有中文就亂碼
charset gbk;
3.2.3. 刪除數(shù)據(jù)
//刪除單條數(shù)據(jù)
delete from student where id=1;

//刪除所有數(shù)據(jù)，不刪除結(jié)構(gòu)，會(huì)放到日志中，事務(wù)提交后才生效
delete from student;

//摧毀表，刪除表中所有數(shù)據(jù)，不刪除結(jié)構(gòu)，立即生效
truncate table student;

注意：delete from student;與truncate table student;都能刪除該表中所有數(shù)據(jù)，區(qū)別：前者刪除后自增主鍵還在，后者主鍵會(huì)從1開(kāi)始。

3.2.4. 修改數(shù)據(jù)
//設(shè)置所有人的年齡加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ù)庫(kù)查詢語(yǔ)言

3.3.1.概念及作用

Data Query Language 數(shù)據(jù)庫(kù)查詢語(yǔ)言

3.3.2.數(shù)據(jù)查詢
//刪除student
drop table student
//創(chuàng)建數(shù)據(jù)庫(kù)表-學(xué)生成績(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);

//查詢所有學(xué)生信息
select * from student;

//查詢id為1的學(xué)生信息
select * from student where id=1;

//查詢id為1的學(xué)生姓名
select name from student where id=1;

//查詢數(shù)學(xué)成績(jī)大于80的同學(xué)成績(jī)
select * from student where math>80

//查詢所有學(xué)生成績(jī)，并輸出效果為 姓名 語(yǔ)文 英語(yǔ) 數(shù)學(xué) 效果，見(jiàn)下圖：
select name as 姓名,chinese as 語(yǔ)文,english as 英語(yǔ),math as 數(shù)學(xué) from student

//查詢所有成績(jī)及數(shù)學(xué)分+10分
select *,(math+10)from student

//統(tǒng)計(jì)每個(gè)學(xué)生的總分
select name,(math+english+chinese) as 總分 from student

//查詢總分大于230分的同學(xué)
select * from student where (math+english+chinese)>230

//查詢數(shù)學(xué)成績(jī)?cè)?0-90之間的同學(xué)
select * from student where math between 80 and 90

//查詢數(shù)學(xué)語(yǔ)文英語(yǔ)都大于80的同學(xué)成績(jī)
select * from student where math>80 and english>80 and chinese >80;

//查詢數(shù)學(xué)成績(jī)?cè)?80 60 90內(nèi)的同學(xué)，即數(shù)學(xué)成績(jī)有60、80、90的。
select * from student where math in(80,60,90);

//模糊查詢
// _  代表一個(gè)，%代表多個(gè)（0 - 無(wú)限）
//查詢所有姓名中包含張的同學(xué)
select * from student where name like ‘%張%’
image
3.3.3. 排序查詢

MySQL中 升序?yàn)閍sc，降序?yàn)閐esc
例如：
升序：select * from 表名 order by 表中的字段 asc(MySQL中默認(rèn)是升序排列，可不寫(xiě)) ；
降序：select * from 表名 order by 表中的字段 desc ；

若要進(jìn)行同時(shí)一個(gè)升序一個(gè)降序 例如：
order by 升序字段 asc，降序字段 desc ；

//按照數(shù)學(xué)成績(jī)從小到大查詢
select * from student order by math;
//按照數(shù)學(xué)成績(jī)從大到小查詢
select * from student order by math desc;
3.3.4.分頁(yè)查詢

limit是mysql的分頁(yè)查詢語(yǔ)法:
select * from table limit m,n
其中m是指記錄從m+1開(kāi)始,，N代表取n條記錄。

//取出第3條至第6條，4條記錄
select * from student limit 2,4

//查詢出數(shù)學(xué)成績(jī)由高到低前兩名
select * from student order by math desc limit 0,2;
3.3.5.分組查詢

分組查詢得到結(jié)果是第一次查到的某個(gè)組別。

//創(chuàng)建一個(gè)訂單表
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）分組之后查詢的字段最好不要是name等，最好的分組字段和聚合函數(shù)
    2）where和having的區(qū)別
    ①where在分組之前限定，如果不滿足條件則不參與分組。having在分組之后限定，如果不滿足條件，則不會(huì)被查出來(lái)。
    ②where后不可以跟聚合函數(shù)，having可以進(jìn)行聚合函數(shù)判斷。
    ③having只能和group by一起使用

    select sex ,avg(math),count  from student where math > 70 group by sex having age > 18
3.3.6. 報(bào)表查詢

count 個(gè)數(shù)
sum 總數(shù)
avg 平均數(shù)
max 最大值
min 最小值

//統(tǒng)計(jì)班級(jí)里邊有多少學(xué)生
select count(*)from student;

//統(tǒng)計(jì)總成績(jī)大于250分的人數(shù)
select count(*)from student where (math+english+chinese)>250;

//統(tǒng)計(jì)班級(jí)里邊各科總成績(jī)
select sum(math),sum(english),sum(chinese) from student

//統(tǒng)計(jì)所有科目的總成績(jī)
select sum(math+english+chinese) from student;

//統(tǒng)計(jì)一下語(yǔ)文平均成績(jī)
select sum(chinese)/count(*) from student;
select avg(chinese) from student;

//統(tǒng)計(jì)一下班級(jí)語(yǔ)文最高分和最低分
select max(chinese) from student;
select min(chinese) from student;

//報(bào)表查詢訂單根據(jù)名稱合并后，總價(jià)格>10000的商品
select * from orders group by product having sum(price) >7000

3.4 數(shù)據(jù)控制語(yǔ)言

數(shù)據(jù)控制語(yǔ)言：簡(jiǎn)稱【DCL】(Data Control Language)，用來(lái)定義數(shù)據(jù)庫(kù)的訪問(wèn)權(quán)限和安全級(jí)別，及創(chuàng)建用戶；關(guān)鍵字：grant等

四、多表設(shè)計(jì)

4.1.一對(duì)一

一張表的一條記錄一定只能與另外一張表的一條記錄進(jìn)行對(duì)應(yīng)，反之亦然。
有時(shí)候，為了業(yè)務(wù)，或者避免一張表中數(shù)據(jù)量過(guò)大，過(guò)復(fù)雜，在開(kāi)發(fā)中會(huì)進(jìn)行一對(duì)一方式來(lái)設(shè)計(jì)表。

image

4.2. 一對(duì)多（1方建主表(id為主鍵字段), 多方建外鍵字段）

一個(gè)實(shí)體的某個(gè)數(shù)據(jù)與另外一個(gè)實(shí)體的多個(gè)數(shù)據(jù)有關(guān)聯(lián)關(guān)系， 一對(duì)多的關(guān)系在設(shè)計(jì)的時(shí)候，需要設(shè)計(jì)表的外鍵。

4.2.1. 班級(jí)表和學(xué)生表設(shè)計(jì)
image
image

部門(mén)表和員工表設(shè)計(jì)

image
4.2.2.創(chuàng)建數(shù)據(jù)庫(kù)表
  • constraint 約束
  • foreign key就是表與表之間的某種約定的關(guān)系，由于這種關(guān)系的存在，能夠讓表與表之間的數(shù)據(jù)，更加的完整，關(guān)連性更強(qiáng)。
  • foreign key語(yǔ)句的式例：FOREIGN KEY(Sno) REFERENCES Student(Sno)
    注意：表的外鍵必須是另一張表的主鍵
//創(chuàng)建班級(jí)表
create table class(id int primary key auto_increment,name varchar(20));
//創(chuàng)建學(xué)生表
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));

//插入班級(jí)數(shù)據(jù)
insert into class values(1,'ceshiban');
insert into class values(2,'kaifa');
//插入學(xué)生數(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);

補(bǔ)一個(gè)外鍵的注意（默認(rèn)是約束): 刪除主鍵信息時(shí)，當(dāng)該主鍵字段值在外鍵表中存在時(shí)，該記錄是不能刪除的。---要把外鍵表是的相關(guān)信息刪除之后，才能刪除。
子查詢：嵌套在其他查詢中的查詢。

4.3、多對(duì)多（ 3個(gè)表= 2個(gè)實(shí)體表 + 1個(gè)關(guān)系表 )

一個(gè)實(shí)體的數(shù)據(jù)對(duì)應(yīng)另外一個(gè)實(shí)體的多個(gè)數(shù)據(jù)，另外實(shí)體的數(shù)據(jù)也同樣對(duì)應(yīng)當(dāng)前實(shí)體的多個(gè)數(shù)據(jù)。
一個(gè)學(xué)生可以有多個(gè)老師，一個(gè)老師可以教多個(gè)學(xué)生
解決方案：創(chuàng)建一個(gè)中間表，專門(mén)用來(lái)維護(hù)多表之間的對(duì)應(yīng)關(guān)系，通常是能夠唯一標(biāo)識(shí)出數(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);

//查詢李老師所教的學(xué)生
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('吹風(fēng)機(jī)',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.交叉查詢

交叉查詢，又叫笛卡爾積查詢，會(huì)將左表和右表的信息，做一個(gè)乘積將所有信息查詢出來(lái)，會(huì)產(chǎn)生臨時(shí)表，比較占用內(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è)定條件，將兩張表中對(duì)應(yīng)的數(shù)據(jù)查詢出來(lái)
不會(huì)產(chǎn)生笛卡爾積，不會(huì)產(chǎn)生臨時(shí)表，性能高

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è)定條件，將兩張表對(duì)應(yīng)的數(shù)據(jù)查詢出來(lái)，同時(shí)將左表自己沒(méi)有關(guān)聯(lián)的數(shù)據(jù)也查詢出來(lái)
注意：join前面是左，后面是右

select * from customer c left join orders o on c.id=o.customer_id;

5.5. 右外連接

右外連接 right join on 設(shè)定條件，將兩張表對(duì)應(yīng)的數(shù)據(jù)查詢出來(lái)，同時(shí)將右表自己沒(méi)有關(guān)聯(lián)的所有數(shù)據(jù)查詢出來(lái)

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;

六、MySQL圖形化工具navicat

6.1. 安裝介紹

MySQL常見(jiàn)的圖形化工具

6.2. Navicat工具使用步驟

6.2.1. 鏈接，mysql，輸入用戶名，密碼

[圖片上傳失敗...(image-50484f-1647260464552)]

[圖片上傳失敗...(image-b7b283-1647260464552)]

<meta charset="utf-8">

六、MySQL圖形化工具navicat

6.1. 安裝介紹

MySQL常見(jiàn)的圖形化工具

6.2. Navicat工具使用步驟

6.2.1. 鏈接，mysql，輸入用戶名，密碼
image
image
6.2.2.新建庫(kù)，鼠標(biāo)點(diǎn)擊右鍵
image
image
6.2.3.新建表
image

七、數(shù)據(jù)庫(kù)備份與恢復(fù)

1. 使用圖形界面工具：

image

2. 使用doc命令：

mysqldump –u用戶名 –p密碼 數(shù)據(jù)庫(kù)名>生成的腳本文件路徑
注意，不要打分號(hào)，不要登錄mysql，直接在cmd下運(yùn)行
注意，生成的腳本文件中不包含create database語(yǔ)句
mysqldump -uroot -proot host>C:\Users\Administrator\Deskt
op\mysql\1.sql

image

3. 導(dǎo)入SQL文件

image

導(dǎo)入文件

image

刷新即可，F(xiàn)5刷新

4.恢復(fù)

a)使用圖形界面工具：
b)使用doc命令行：
i.不登錄恢復(fù)
mysql -u用戶名 -p密碼 數(shù)據(jù)庫(kù)<腳本文件路徑
注意，不要打分號(hào)，不要登錄mysql，直接在cmd下運(yùn)行

image

ii.登錄之后恢復(fù)
選擇庫(kù) use 庫(kù)名稱
Source sql文件路徑

image

八、數(shù)據(jù)庫(kù)常用性能優(yōu)化（了解）

  • 數(shù)據(jù)庫(kù)性能優(yōu)化這塊，我們考慮比較多的還是查詢這塊，互聯(lián)網(wǎng)項(xiàng)目對(duì)數(shù)據(jù)查詢非常頻繁，對(duì)效率，性能要求比較高。

  • 查詢這塊優(yōu)化的話，主要就需要使用索引這種方式，所謂索引就是建立一種快速查找的方式，比如我們查字典,有一個(gè)ABCD的索引.

    image

  • 舉個(gè)例子,如果我們創(chuàng)建一個(gè)表 create table user(id integer ,name varchar(20)，job varchar(20)); 如果我們數(shù)據(jù)庫(kù)中有1000萬(wàn)條數(shù)據(jù)，當(dāng)我查詢 select * from user where name=’張三’的時(shí)候,這種查詢方式就類似于整個(gè)數(shù)據(jù)庫(kù)的掃描，效率非常低。

    image

  • 我們可以給這個(gè)name設(shè)置一個(gè)索引create index n on user (name);這是設(shè)置一種普通（normal）索引，然后我們查詢的時(shí)候，有了這個(gè)索引，效率就會(huì)大大提升，當(dāng)然對(duì)于索引，它的方式有BTree類型和Hash類型，是兩種管理數(shù)據(jù)庫(kù)索引的方式，這個(gè)我沒(méi)有深入研究。這個(gè)我們可以自己設(shè)置。默認(rèn)是btree。

  • 索引類型的話，有normal（普通類型）類型、unique（唯一類型）、fulltext全文索引、主鍵索引、非空索引、聚集索引。
    ①主鍵索引，primary key 在設(shè)置的時(shí)候，已經(jīng)指定了，其實(shí)也是非空索引。
    ②非空索引是not null,設(shè)置這種方式的該字段下內(nèi)容不能為空，
    ③聚集索引(聯(lián)合索引)，是在設(shè)置多個(gè)查詢條件的時(shí)候使用。比如 創(chuàng)建一張表，有名字，有工作，我們想經(jīng)常頻繁的用到名字和工作它倆結(jié)合在一起來(lái)查詢數(shù)據(jù)庫(kù)中表的數(shù)據(jù)。這個(gè)時(shí)候，可以將名字和工作指定為聚集索引。create index m on user(name,job); 這樣當(dāng)我們指定select * from user where name=xxx and job=xxx的時(shí)候，就會(huì)按照索引方式來(lái)做。

  • 這種優(yōu)化方式就是索引優(yōu)化，在使用索引優(yōu)化方案的時(shí)候，我們需要注意避免在索引字段上使用條件函數(shù)等操作。
    了解：
    Show index form orders;查看索引

    image

面試題：

  1. 為什么要?jiǎng)?chuàng)建索引呢（優(yōu)點(diǎn)）？
    這是因?yàn)?，?chuàng)建索引可以大大提高系統(tǒng)的性能。
    ①過(guò)創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。
    ②可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
    ③可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
    ④在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。
    ⑤通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

  2. 建立方向索引的不利因素（缺點(diǎn)）
    也許會(huì)有人要問(wèn)：增加索引有如此多的優(yōu)點(diǎn)，為什么不對(duì)表中的每一個(gè)列創(chuàng)建一個(gè)索引呢？這種想法固然有其合理性，然而也有其片面性。雖然，索引有許多優(yōu)點(diǎn)，但是，為表中的每一個(gè)列都增加索引，是非常不明智的。這是因?yàn)椋黾铀饕灿性S多不利的一個(gè)方面。
    ①創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。
    ②索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì)更大。
    ③當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

  3. 創(chuàng)建方向索引的準(zhǔn)則
    索引是建立在數(shù)據(jù)庫(kù)表中的某些列的上面。因此，在創(chuàng)建索引的時(shí)候，應(yīng)該仔細(xì)考慮在哪些列上可以創(chuàng)建索引，在哪些列上不能創(chuàng)建索引。
    一般來(lái)說(shuō)，應(yīng)該在這些列上創(chuàng)建索引。
    ①在經(jīng)常需要搜索的列上，可以加快搜索的速度；
    ②在作為主鍵的列上，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；
    ③在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
    ④在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的；
    ⑤ 在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間；
    ⑥在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。

  4. 同樣，對(duì)于有些列不應(yīng)該創(chuàng)建索引。一般來(lái)說(shuō)，不應(yīng)該創(chuàng)建索引的的這些列具有下列特點(diǎn)：
    ①對(duì)于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因?yàn)?，既然這些列很少使用到，因此有索引或者無(wú)索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。
    ②對(duì)于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引。這是因?yàn)椋捎谶@些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。
    ③對(duì)于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引。這是因?yàn)?，這些列的數(shù)據(jù)量要么相當(dāng)大，要么取值很少。
    ④當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。這是因?yàn)椋薷男阅芎蜋z索性能是互相矛盾的。當(dāng)增加索引時(shí)，會(huì)提高檢索性能，但是會(huì)降低修改性能。當(dāng)減少索引時(shí)，會(huì)提高修改性能，降低檢索性能。因此，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引。

九、數(shù)據(jù)庫(kù)性能檢測(cè)方式（了解）

在設(shè)計(jì)SQL的時(shí)候，我們一般會(huì)使用explain檢測(cè)sql，看是否使用到索引，避免出現(xiàn)整表搜索方式查詢[filesort(不是以索引方式的檢索，我們叫做filesort)]（我在這張表中把gender設(shè)置成normal索引，name沒(méi)有任何設(shè)置）

image

對(duì)比看的，對(duì)有索引的字段,在檢測(cè)的時(shí)候，會(huì)顯示是一個(gè)引用的key。
explain select*from tb_product where title='';
還可以使用profiling方式檢測(cè)數(shù)據(jù)庫(kù)執(zhí)行的方式，可以查詢sql的運(yùn)行時(shí)間。http://www.jb51.net/article/31870.htm
注釋：查看profiling信息，show variables like '%profiling%';

image

第一步：set profiling=1;（開(kāi)啟profiling）
第二步運(yùn)行：select title from tb_product ;
第三步：查看運(yùn)行時(shí)間show profiles;

image

Duration：持續(xù)時(shí)間，事件花費(fèi)的時(shí)間總計(jì)（以毫秒為單位？）

十、數(shù)據(jù)庫(kù)視圖

數(shù)據(jù)庫(kù)視圖詳解To-Big_Fish的博客-CSDN博客數(shù)據(jù)庫(kù)視圖

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容