關系型數(shù)據(jù)庫核心元素

• 數(shù)據(jù)行(一條記錄)
• 數(shù)據(jù)列(字段)
• 數(shù)據(jù)表(數(shù)據(jù)行的集合)
• 數(shù)據(jù)庫(數(shù)據(jù)表的集合,一個數(shù)據(jù)庫中能夠有n多個數(shù)據(jù)表)

為什么學習數(shù)據(jù)庫

測試工程師的目的是找出軟件的不足,并告訴開發(fā)工程師,出現(xiàn)問題的環(huán)境,操作步驟和輸入輸出的數(shù)據(jù).而優(yōu)秀的測試工程師,當測出問題后,還可以告訴開發(fā)團隊,問題的原因和解決方案.

常用數(shù)據(jù)類型

• 整數(shù),有符號范圍(-2147483648 ~ 2147483647) ,無符號范圍(0 ~ 4294967295)
• 小數(shù): decimal, 如 decimal(5,2)表示共存 5位數(shù),小數(shù)占2位,整數(shù)占3位.
• 字符串:varchar,范圍(0~65533),如 varchar(3) 表示最多存3個字符,一個中文或一個字母都占一個字符
• 日期時間:datetime,范圍(1000-01-01 00:00:00~ 9999-12-31 23:59:59), 如'202-01-01 12:29:59'

約束

• 主鍵(primary key):物理上存儲的順序,int類型,無符號,自動遞增的,唯一的標識,其中數(shù)據(jù)不可重復
• 非空(not null): 此字段不允許填寫空值
• 唯一(unique):此字段的值不允許重復
• 默認值(default):當不填寫此值時會使用默認值,如果填寫時以填寫為準
• 外鍵(foreign key):維護兩個表之間的關聯(lián)關系

基礎知識

• 一個 byte 等于 1個字節(jié) 等于 8位 ,8bit
• short,char 2個字節(jié) 16位;
• int, float 4 個字節(jié) 32位;

• double, long 8個字節(jié) 64位;

• 一個 int 4個字節(jié) 等于 32位 ,32bit. 代表多大的范圍: 2^32-1

數(shù)據(jù)庫管理員 DBA 或者運維來維護

Navicat 數(shù)據(jù)庫管理工具

• localhost是連接
• 框內是倉庫.
• 一般一個倉庫就是一個網(wǎng)站
• 每個倉庫中可以創(chuàng)建非常多的表
• 只要硬盤夠大可以創(chuàng)建非常多的倉庫,表.
• 一個表中可以有很多字段,一個表中可以有很多記錄.

SQL 語言

• 數(shù)據(jù)庫表操作: 創(chuàng)建,刪除
• 數(shù)據(jù)操作: 增加,刪除,修改,簡單查詢
• 數(shù)據(jù)操作: 查詢

此部分中查詢?yōu)橹攸c,需要熟練掌握

SQL 語言編寫和運行

在查詢中直接使用 SQL 語句

ctrl / 注釋

ctrl shift / 取消注釋

創(chuàng)建表

create table 表明(
    字段名 類型 約束,
    字段名 類型 約束,
    ...
)
例子:創(chuàng)建學生表

刪除表

drop table 表名
或
drop table if exists 表名  -- 如果存在則刪除

添加數(shù)據(jù)

添加一行數(shù)據(jù)

格式一: 所有字段設置值,值的順序與表中字段的順序對應

• 說明:主鍵列是自動增長,插入時需要占位,通常使用0 或者default 或者 null 來占位,插入成功后以實際數(shù)據(jù)為準

insert into 表名 values(...)

格式二:部分字段設置值,值的順序與給出的字段順序對應

insert into 表名(字段1,...) values(值1,...)
例如:插入一個學生,只設置姓名
insert into students(name) values('老夫子')

插入多條數(shù)據(jù)

insert into 表名(字段1) values(值1),(值2),(值3)...
或
insert into students values('值1','值2','值3'...),('值1','值2','值3'...),('值1','值2','值3'...)

執(zhí)行一條語句會比多條語句的效率高

修改

格式：
update 表名 set 列1=值1,列2=值2... where 條件
(如果沒有條件則改對應目標列的所有)
例:修改id 為5 的學生數(shù)據(jù),姓名改為狄仁杰,年齡改為20
update student set name='狄仁杰',age=20 where id=5

刪除

增加表字段

alert table 表名 add 字段 類型

查詢

select * from students
(* 代表的是所有"列")
別名(例子):
select name as 姓名 from students
給表起別名的好處:
select s.name,s.age from student as s,class as c;

去重

select distinct 字段 from 表名
select distinct sex from student
如果是多列,按照多列的標準:
select distinct 字段1,字段2 from 表名

條件

• 使用 where 子句 對表中的數(shù)據(jù)篩選,符號條件的數(shù)據(jù)會出現(xiàn)在結果集中
• 語法如下:

select 字段1,字段2... from 表名 where 條件;
例子:
select * from students where id=1;

• where 后面支持多種運算符,進行條件的處理
  • 比較運算
  • 邏輯運算
  • 模糊運算
  • 范圍查詢
  • 空判斷

查詢'1班'以外的學生信息:
select * from students where class!='班'
或
select * from students where class<>'班'
例子2:查詢年齡小于20的女同學
select * from student where age<20 and sex='女'
例子3:查詢女學生或'1班'的學生
select * from student where sex='女' or class='1班'
例子4:查詢非天津的學生
select * from student where not hometown='天津'

比較運算符

模糊查詢

• like
• % 標識任意多個任意字符
• _ 標識一個任意字符

例子1:查詢姓孫的學生
select * from students where name like '孫%';
例子2:查詢姓孫且名字是一個字的學生
select * from students where name like '孫_';
例子3:查詢叫喬的學生
select * from students where name like '%喬';
例子4:查詢姓名包含白的學生
select * from students where name like '%白%'; 
例子5:查詢名字是兩個字的學生
select * from students where name like '__';
例子6:查詢年齡為18至20的學生
select * from students where age>18 and age<20;
或
select * from students where age between 18 and 20;

空判斷

例子:判斷沒有填寫card的
select * from students where card='';
select * from students where card is null;

null 和 '' 不一樣

null 在插入數(shù)據(jù)后,沒有生成后舊為null

排序

• 為了方便查看數(shù)據(jù),可以對數(shù)據(jù)進行排序

• 語法:

select * from 表名
order by 列1 asc|desc,列2 asc|desc...

• 將行數(shù)據(jù)按照列1進行排序,如果某些行列1的值相同時,則按照2排序,以此類推
• 默認按照列值從小到大排列
• asc 從小到大排列,即升序

• desc 從大到小排序,即降序

• 例1:查詢所有學生信息,按年齡從小到大排序

select * from student order by age;

• 例子2:查詢所有學生信息,按年齡從大到小排序,年齡相同時,再按學號從小到大排序

select * from students order by age desc,studentNo;

• 例子3:查詢所有學生信息,按班級從小到大排序,班級相同時,再按學號從小到大排序.

select * from students order by class ,studentNO;

對中文字符排序

select * from students order by convert(name using gbk)

聚合函數(shù)

統(tǒng)計,查詢總數(shù)

統(tǒng)計行:
select count(*) from students
統(tǒng)計列:
select count(name) from students

• 例子2:查詢女生的最大年齡

select max(age) from students where sex='女';

• 例子3:查詢1班的最小年齡

select min(age) from students where class='1'

• 例子4:查詢北京學生的年齡總和

select sum(age) from students where hometown='北京'

• 例子5:查詢女生的平均年齡

select avg(age) from students where sex='女';

• 例子6:查詢所有學生的最大年齡,最小年齡,平均年齡

select max(age) as 最大年齡,min(age) as 最小年齡,avg(age) as 平均年齡 from students;

• 例子7: 1班共有第多少個學生

select count(*) from students where  class='1班'

• 例子8: 查詢3班年齡小于18歲的同學有幾個

select count(*) from students where class='3班' and age<18; 

分組 group by

分組的字段(依據(jù).'group by之后的')會在結果集中列出來

• 按照字段分組,表示此字段相同的數(shù)據(jù)會被放到一個組中
• 分組后,分組的依據(jù)列會顯示在結果集中,其他列不會顯示在結果集中
• 可以對分組后的數(shù)據(jù)進行統(tǒng)計,做聚合運算

語法:

select 列1,列2,聚合... from 表名 group by 列1,列2...
例1:查詢各種性別的人數(shù)
select sex,count(*) from students group by sex
例1: 查詢各種年齡的人數(shù)
select age,count(*) from students group by age

select name from stu group by name;
感覺像去重: select distinct name from ....

• 例子:查詢各個班級學生的平均年齡最大年齡最小年齡

select avg(age),max(age),min(age) from students group by class;

select 后面跟著的 參數(shù)字段,都會作為列名稱

多字段的分組

select class,sex,count(*) from student group by class,sex

在分組的時候,每一行數(shù)據(jù),班級和性別都相同才會分到一組中.

• 例子:用分組實現(xiàn)統(tǒng)計班級男生總人數(shù)

原: select count(*) from students where sex='男'
現(xiàn): select sex,count(*) from students group by sex having sex="男"

• 例子:查詢1班除外其他班級學生的平均年齡,最大年齡,最小年齡

select class,avg(age),max(age),min(age) from students where class!='1班' group by class having class!='1班';

分頁

• 當數(shù)據(jù)量過大時,在一頁中查看數(shù)據(jù)是一件非常麻煩的事情
• 語法

select * from 表名 limit start,count;
從start 開始,獲取count 條數(shù)據(jù)
start 索引從0開始

例子:查詢前3行學生信息

select * from student limit 0,3;

select * from student limit 7;
相當于: select * from student limit 0,7
簡寫了起始位.

多表查詢(連接查詢) 重要

select * from students,scroo where students.studentno=scores.studentno;

第一種會產(chǎn)生臨時表,會占很大內存,會產(chǎn)生"笛卡兒積"

等值連接(內連接)

• 方式二(又稱內連接)

select * from 表1 inner join 表2 on 表1.列 =表2 列
select * from students inner join scores on students.studentno=scores.studentno

性能高!不會產(chǎn)生笛卡兒積,不會產(chǎn)生臨時表

• 例子:統(tǒng)計每個班級中男女生學生人數(shù),按照班級升序排序

select class,sex,count(*) from students group by class,sex;

• 例子20:查詢年齡最小的學生的全部信息

• 例5:查詢王昭君的成績,要求顯示姓名,課程名,成績

select 姓名,課程名,成績 from 成績表  inner join 課程表 on 課程號=課程號 inner join 學生表 on 學生表學號=課程表學號

• 例子7:查詢男生中最高成績,要i求顯示姓名課程名,成績

select 姓名,課程名,成績 from 成績表  inner join 課程表 on 課程號=課程號 inner join 學生表 on 學生表學號=課程表學號 where sex='男' order by score desc limit 1

左連接

select * from 表1 left join 表2 on 表1.列=表2.列

• 例子1:查詢所有學生的成績,包括沒有成績的學生

select * from students left join scores on students.studentno=scores.studentmo;

left join 左邊前面的表就是主表.join 后面的表是右表

• 例子2:查詢所有學生的成績,包括沒有成績的學生,需要顯示課程名

select * from students stu left join scores sc on stu.studentNo=sc.studentNo left join courses cs on cs.courseNo =sc.courseNo;

右連接

• select * from 表1 right join 表2 on 表1.列=表2.列

• 例子1:查詢所有課程的成績,包括沒有成績的課程

select * from scores sc right join coures cs on cs.courseNo =sc.courseNo

• 例子2:查詢所有課程的成績,包括沒有成績的課程,包括學生信息

select * from scores  right join students on students.studentNo=scores.studentno left join coures on scores.courseNo=courses.courseno

自關聯(lián)查詢

例子1:查詢河南省的所有城市

select * from areas,areas_copy where areas.aid=areas_copy.pid and areas.atitle='河南省';

或 (不拷貝兩個表了,一個表查詢多次)

• 數(shù)據(jù)源,一個表查多次

select * from areas as sheng,areas as shi where sheng.aid=shi.pid and sheng.atitle='河南省';

自關聯(lián) 關聯(lián)3次

• 例子2:查詢鄭州市的所有區(qū)縣

select * from areas as sheng,areas as shi where sheng.aid=shi.pid and sheng.atitle='鄭州市';

• 例子3:查詢河南省的所有區(qū)縣

select * from areas as sheng,areas as shi ,areas as qu where sheng.aid=shi.pid and sheng.atitile='河南省' and shi.aid=qu.pid;

子查詢

• 在一個 select 語句中,嵌入了另外一個select 語句,那么被嵌入的 select語句稱之為子查詢語句

主查詢

• 主要查詢的對象,第一條 select 語句

主查詢和 子查詢的關系

• 子查詢是嵌入到主查詢中
• 子查詢是輔助主查詢的,要么充當條件,要么充當數(shù)據(jù)源
• 子查詢是可以獨立存在的語句,是一條完整的select 語句

子查詢分類

• 標量子查詢:子查詢返回的結果是一個數(shù)據(jù)(一行一列)
• 列子查詢:返回的結果是一列(一列多行)
• 行子查詢:返回的結果是一行(一行多列)
• 表級子查詢:返回的結果是多行多列

標量子查詢

• 例子1: 查詢班級學生的平均年齡

查詢大于平均年齡的學生;(需要先查出平均值,要操作兩次,且結果有時間錯誤)
select * from students  where age > 21.4167;
一條語句查詢出,**被括號括起來的是的子查詢**
select * from students where age > (select avg(age) from students).

• 例子2: 查詢王昭君的語文成績,要求只顯示成績

select scores from scores where studentNo=(select studentNo from students where name='王昭君')
and courseNo=(select courseNo from courses where name='語文')

列級子查詢

列級子查詢返回一列多行

成績表中根據(jù)學號查成績

select * from scores where studentNo in ('002','006')

select * from scores where studentNo in (select studentNo from students where age=18)

行級子查詢

例子4:查詢男生中年齡最大的學生信息

多個條件是且的關系可以這么寫:

select * from students where sex='男' and age=26

select * from students where (sex,age)=('男',26);

select sex,age from students where sex='男' order by age desc limit 1;

select * from students where (sex,age)=(select sex,age from students where sex='男' order by age desc limit 1)

表級子查詢

例子5:查詢數(shù)據(jù)庫和系統(tǒng)測試的課程成績

select * from scores inner join courses on scores.courseNo=courses.courseNo where courses.name in ('數(shù)據(jù)庫','系統(tǒng)測試')

這種方式有一種不好的地方:在過濾之前數(shù)據(jù)量較多.

select * from courses where name in ('數(shù)據(jù)庫','系統(tǒng)測試')

優(yōu)化后:

select * from scores inner join (select * from courses where name in ('數(shù)據(jù)庫','系統(tǒng)測試')) as c on scores.courseNo=c.courseNo;

子查詢中特定關鍵字使用

• in 范圍
  • 格式: 主查詢 where 條件 in (列子查詢)
• any|some 任意一個
  • 格式:主查詢 where 列 = any (列子查詢)
  • 在條件查詢的結果中匹配任意一個即可,等價于 in
• all
  • 格式:主查詢 where 列 = all(列子查詢):等于里面所有
  • 格式:主查詢 where 列 <> all(列子查詢):不等于其中所有

select * from students where age in (select age from students where age between 18 and 20)

any:
select * from students where age=any(select age from students where age between 18 and 20)

案例

函數(shù) round() 保留小數(shù)點

把一個表中查詢的結果插入另一個表中

insert into goods_cates(cate_name) select distinct cate from goods;

查詢演練-數(shù)據(jù)分表

select * from goods inner join goods_cates on goods.cate==goods_cates.cate_name

現(xiàn)在將id1 列的內容更新至 cate 列中

update goods inner join goods_cates on goods.cate=goods_cates.cate_name set goods.cate=goods_cates.id;

高級

E-R 模型

E-R模型的基本元素是:實體,聯(lián)系和屬性

• E 表示entry,實體:一個數(shù)據(jù)對象,描述具有相同特征的事務
• R 表示relationship,聯(lián)系:表示一個或多個實體之間的關聯(lián)關系,關系的類型包括包括一對一,一對多,多對多.
• 屬性:實體的某一特性為屬性

關系也是一種數(shù)據(jù),需要通過一個字段存儲在表中

1. 實體A對實體B為1對1,則在表A或表B中船艦一個字段,存儲另一個表的主鍵值.

讓不常使用的表來維護共同特征數(shù)據(jù)(如拆分出了家庭地址,由家庭地址表維護學生學號字段.學生表不維護地址數(shù)據(jù))

1. 實體A對實體B為1對多:在表B中創(chuàng)建一個字段,存儲表A的主鍵值

當一對多的關系中,應該讓多的那張表來存關系

1. 實體A 對實體B 為多對多:新建一張表C,這個表只有兩個字段,一個用于存儲A的主鍵值,一個用于存儲B的主鍵值.

一個課程可以由多個學生來學,一個學生可以學多個課程.(多對多的關系)

多對多的關系通常會用中間表來記錄

命令行客戶端

常用命令:

連接
mysql -uroot -p 回車 輸入密碼
查看所有倉庫
show databases;
使用某個倉庫
use 倉庫名稱;
查看所有的表:
show tables;
查看表結構:
desc student;
show create table students;
出現(xiàn)中文亂碼這么操作消除亂碼:
set charset gbk;

備份:

mysqldump -uroot -p密碼 備份的數(shù)據(jù)庫名稱>新名稱.sql

恢復:
mysql -uroot -p 新數(shù)據(jù)庫名 < 備份的數(shù)據(jù)庫名稱.sql

根據(jù)提示輸入mysql 密碼

內置函數(shù)

字符串函數(shù)

• 拼接字符串 concat(str1,str2...)

select concat(12,34,'ab');

• 包含字符個數(shù)length(str)

select length('abc');

• 截取字符串
  • left(str,len) 返回字符串str 的左端 len個字符
  • right(str,len)返回字符串str的右端len個字符
  • substring(str,pos,len)返回字符串str的位置pos起len個字符

select substring('abc123',2,3)

• 去除空格
  • ltrim(str)返回刪除了左空格的字符串str
  • rtrim(str)返回刪除了右空格的字符串str

日期時間函數(shù)

• 當前日期
  • select current_date()
• 當前時間current_time()
  • select current_time()
• 當前日期時間now()
  • select now()
• 日期格式化 date_format(date,format)
  • 參數(shù)format可選值如下
    • %Y 獲取年,返回完整年份
    • %y 獲取年,返回簡寫年份
    • %m 獲取月,返回月份
    • %d 獲取日,返回天值
    • %H 獲取時,返回24進制的小時數(shù)
    • %h 獲取時,返回12進制的小時數(shù)
    • %i 獲取分,返回分鐘數(shù)
    • %s 獲取秒,返回秒數(shù)

流程控制

• case 語法:等值判斷
• 說明:當值等于某個比較值得時候,對應得結果會被返回;如果所有的比較值都不相等則返回else得結果;如果沒有else并且所有比較值都不相等則返回null

case 值 when 比較值1 then 結果1 when 比較值2 then 結果2 ... else 結果 end

select 
case 1 
when 1 then 'one' 
when 2 then 'two' 
else 'zero' 
end as result;

自定義函數(shù)

• 創(chuàng)建

• 語法如下

  delimiter $$
  create function 函數(shù)名稱(參數(shù)列表) returns 返回類型
  begin
  sql語句
  end
  $$
  delimiter ;
  

• 說明:delimiter用于設置分隔符,默認為分號

• 再"sql 語句"部分編寫的語句需要以分號結尾,此時回車會直接執(zhí)行,所以要創(chuàng)建存儲過程前需要指定其它符號作為分隔符,此處使用//,頁可以使用其它字符

• "$$" 用于命令行中

示例:

• 要求:創(chuàng)建函數(shù)my_trim,用于刪除字符串左右兩側的空格
• step1:設置分隔符

delimiter $$

• 創(chuàng)建函數(shù)

create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str))
end

• step3: 還原分隔符

delimiter ;

使用自定義函數(shù)

select ' abc ',my_trim(' abc ')

視圖

• 對于復雜的查詢,在多個地方被使用,如果需求發(fā)生了改變,需要更改sql 語句,則需要在多個地方進行修改,維護起來非常麻煩
• 解決:定義視圖
• 視圖本質就是對查詢的封裝
• 定義視圖,建議以v_開頭

create view 視圖名稱 as select 語句;

例子:創(chuàng)建視圖,查詢學生對應的成績信息

select stu.*,cs.courseNo,cs.name cour
seName,sc.score
from
students stu
inner join scores sc on stu.studentNo=sc.studentNO
inner join courses cs on cs.courseNo=sc.courseNo;

事務

• 事務廣泛的運用于訂單系統(tǒng),銀行系統(tǒng)等多種場景
• 例如:A用戶和B用戶時銀行的儲戶,現(xiàn)在A要給B轉賬500元,那么需要做以下幾件事:
  1. 檢查A的賬戶余額>500元;
  2. A賬戶中扣除500元
  3. B賬戶中增加了500元
• 所謂事務,它時一個操作序列,這些操作要么都執(zhí)行,要么都不執(zhí)行,它是一個不可分割的工作單位.列如:銀行對賬工作;從一個賬號扣款并使另一個賬號增款,這兩個操作要么都執(zhí)行,要么都不執(zhí)行.所以,應該把他們看成一個事務.事務是數(shù)據(jù)庫維護數(shù)據(jù)一致性的單位,在每個事務結束時,都能保持數(shù)據(jù)一致性.

事務命令

• 表的引擎必須是innodb類型才可以使用事務,這是mysql表的默認引擎.
• 查看表的創(chuàng)建語句,可以看到engine=innodb

show create table students;

開啟事務:begin;

所有操作都成功:commit;

任何一步失敗: rollback;

索引

思考:在圖書館中是如何找到一本書的?

一般的應用系統(tǒng)對比數(shù)據(jù)庫的讀寫比例在10:1左右,而且插入操作和更新操作很少出現(xiàn)性能問題;當數(shù)據(jù)庫中數(shù)據(jù)量很大時,查找數(shù)據(jù)會變得很慢.

優(yōu)化方案:索引

語法:

show index from 表名;

• 創(chuàng)建索引

方式一:建表時創(chuàng)建索引
create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key(age)
);

• 主鍵會自動創(chuàng)建索引,
• unique也會自動創(chuàng)建索引,
• key(字段)也會創(chuàng)建索引

方式二:
create index 索引名稱 on 表名(字段名稱(長度))
create index i_name on test(name(10))
對已有表創(chuàng)建索引;

• 開啟運行時間監(jiān)測:
• set profiling=1;
• 查看執(zhí)行的時間:
• show profiles;

外鍵 foreign key

• 查看外鍵:show create table 表名
• 外鍵的作用:用來約束表. 設置外鍵約束

方式一:創(chuàng)建數(shù)據(jù)表的時候設置外鍵約束
create table class(
    id int unsigned primary key auto_increment,
    name varchar(10)
);

create table stu(
    name varchar(10),
    class_id int unsigned,
    foreign key(class_id) references class(id)
);

foreign key(自己的字段) references 主表(主表字段)

方式二: 對于已經(jīng)存在的數(shù)據(jù)表設置外鍵約束
alter table 從表名 add foreign key(從表字段) references 主表名(主表字段);
alter table stu add foreign key (class_id) references class(id)

刪除外鍵

需要先獲取外鍵約束名稱
show create table stu;
獲取名稱之后就可以根據(jù)名稱來刪除外鍵約束
alter table 表名 drop foreign key 外鍵名稱;
alter table stu drop foreign key stu_ibfk_i;

在實際開發(fā)中,很少會使用到外鍵約束,會極大的較低表更新的效率

籃圈外鍵