資料庫學習知識點總結

SELECT

   person_id,

   on_time,

   off_time,

   signin_time,

   signout_time,

   result,

   TIMESTAMPDIFF( MINUTE, on_time, off_time ) scheduleWorkingDuraion,

   TIMESTAMPDIFF( MINUTE, signin_time, signout_time ) realWorkingDuraion

FROM

   `attendance_results`

WHERE

   date >= '2020-07-01 08:05:09'

   AND date <= '2020-09-10 08:05:09'

   ORDER BY person_id ASC

 --  AND person_id IN ( '1','2' )

DBMS資料庫管理系統

show databases;      顯示目前連接配接下的所有資料庫；

show tables;         顯示目前庫中所有表

show tables from 庫名 顯示指定庫中所有表

show columns from 表名 顯示指定表中所有列

use 庫名                打開指定庫

DQL 資料查詢語言

  select    查詢清單

  from        表名

  where        篩選條件

     as作為别名  兩個數值方可以用“+”符号

去重 distinct

補充函數 

select database（）；

select user（）；

select ifnull（字段名，表達式）；

select concal（字元1，字元2）；拼接字元

select length（）；

二.特點

1.條件表達式篩選，

>= <= <>不等于

2.按邏輯表達式篩選

邏輯運算符：

and or  not

3.模糊查詢

like 一般和通配符搭配使用

_任意單個字元

%任意多個字元

between and：一般用于某字段是否在指定的區間

in：一般用于某字段是否在指定的清單；

is null 判斷null值

三.排序查詢

 order by 排序清單：放到末尾排序；

特點：

①排序清單是單個字段·多個字段·函數·表達式·列的索引·以及組和别名

②升序 asc

③降序 desc

例題1.查詢員工的姓名和部門号和年薪，按年薪降序，按姓名升序。

select last_name，department_id,salary*12*(1+IFNULL(comission_pct,0)) 年薪 from employees 

OEDER BY 年薪 DESC,last_name ASC；

四.常見函數

1.自定義函數

2.調用函數

常見函數：字元函數：CONCAT 拼接函數 SUBSTR（‘asdfg’，1,3） 截取子串 //從1開始的幾位字母

    TRIN去前後空格，LRAD/RPAD（'王港'，10，‘a’） 左填充、右填充，UPEPER/LOWER 變大寫/變小寫

    STRCMP（）/比較函數,LEFT/RIGHT('23333',1) ；

      數學函數: ABS(）絕對值，CELT/FLOOR() 向上取整/向下取整  , TRUNCATE(1.999,1)截斷函數 ， NOD（) 取餘

      日期函數： NOW（）目前時間，CURDATE（目前日期），CURTIME(目前時間),DATE_FORMAT（datetime，fmt），

    STR_TO_DATE（） 按指定格式解析字元串為日期類型

      流程控制函數：if（100>9,'好'，‘壞’）, CASE 表達式  WHEN 值1 THNE 結果1， 

例題：查詢員工表的姓名，要求格式：首性字母大寫，名小寫，且姓和名之間用_分割；

select UPPER(substr(str,pos,len))from .;

select LOWER(substr(str,pos))from .;

select UPPER(str) ;

select CONCAT(UPPER(substr(str,pos,len)),LOWER(substr(str,pos)),‘_’)；

例題：    部門編号是30，工資顯示為2倍

    部門編号是50，工資顯示為4倍

    否則不變

    顯示部門編号，新工資，舊工資；

    select  department-id，salary，

    case department—id

    when 30 then salary*2

    when 50 then salary*

    else salary

    end newsalary from 表名;

分組函數：一組資料進行統計計算，最終得到一個值

    sum（）求和

    avg（）求平均數

    max（）求最大值

    min（）求最小值

    count（）非空字段的個數

    order by()排序清單

    groupby（）分組清單

    執行順序：     ①from

            ②where    

            ③group by

            ④having

            ⑤select

            ⑥order by

    需要分組查詢的時候使用GROUP BY子句，例如查詢每個部門的工資和，要使用部門來分組。

    select 查詢清單

    from 表名

    where 篩選條件

    group by 分組清單

    特點① 查詢清單往往是分組函數和被分組的字段

        ② 分組查詢的篩選一般分為兩類

                    篩選的基表    使用的關鍵詞    位置

分組前篩選                原始表        where        GROUP BY的前邊

分組後篩選                分組後的結果集    having        GROUP BY後邊

where--group by--having

例題：    select sum（salary）

    from employee

    GROUP BY department；

例題：搭配distinct實作去重統計

    select COUNT（DISTINCT XX）from 表名；

例題：每個工種有獎金的員工的最高工資>12000的工種編号和最高工資

select job_id，MAX（salary）

from employees

where commission is not null

having MAX(salary)>12000;

連接配接查詢：又稱多表查詢。

    假設多表 A*B*C;

    分類：

        按年代分類；

        sq192标準

        sq199标準【推薦】

        按功能分類：

            内連接配接：

                等值連接配接

                非等值連接配接

                自連接配接

            外連接配接：

                左外連接配接

                右外連接配接

                全外連接配接

            交叉連接配接：

---------------------------SQL92------------------------------

1.内連接配接

1.等值連接配接

    文法：

        select 查詢清單

        from 表名1 as 别名1，表名2 别名2

        where 等值連接配接的連接配接條件

    特點：

        1.表字段重名問題用别名解決

        2.

例題：查詢員工名和部門名

    select last_name，‘department_name’

    from employee e，department d

    where e.'department_id'= d.'department_id'; 

    AND

    添加分組加篩選

    案例1：查詢每個城市的部門個數。1，‘city’

    select COUNT(*) as 部門個數,l.'city'

    from departments d,localtion_id l

    where d.'location_id' = 1.'location_id'

    group by l.city;

二.sql99文法

文法：         

    select

    from 表1  别名  【連接配接類型】

    join 表2  别名

    on  連接配接條件

    【where 篩選條件】

    【GROUP BY 分組】

    【having 篩選條件】

    【order by 排序清單】

分類

内連接配接 ：inner

外連接配接

    左外：left【outer】

    右外：right【outer】

    全外：full【outer】

交叉連接配接：cross

特點：

1.添加排序，分組，篩選

2.inner可以省略

3.篩選條件放在where後面，連接配接條件放在on後面，提高閱讀性

4.内連接配接和92文法中的等值連接配接效果一樣，都是查詢多表的交際

1.等值連接配接

例題：查詢員工名，部門名

select last_name，department_name

from employee e

inner join department d

on e.‘department’ = d.department_id

2.非等值連接配接 

例題查詢員工的工資級别

select count(*)，grade_level

from empl e

join job g

on e.salry between g.glowest_sal and g.high_sal

group by grade_level

having count(*)>20

order by grade_level desc;

3.自連接配接

查詢員工的名字上級的名字

二.外連接配接

應用場景：用于查詢一個表中有，另一個表中沒有的記錄

特點：

1.外連接配接的查詢結果為主表中的所有記錄

    如果表中有和它比對的，則顯示比對的值

    如果表中沒有和它比對的，則顯示NULL

    外連接配接查詢結果 = 内連接配接查詢結果+主表中有而從表中沒有的記錄

2.左外連接配接，left join左邊的是主表

  右外連接配接，right join右邊的是主表

3.左外和右外連接配接兩個表的順序，可以實作同樣的效果    

引用：查詢沒有男朋友的女神名

select b.name ,bo.*

from beauty b

left outer join boys bo

on b.b_id = bo.id

where bo.id if null;

子查詢：

含義：

出現在其他語句中的select語句，稱為子查詢或内查詢

1.在where或者having後邊

1.标量子查詢（單行子查詢）

2.列子查詢（多行子查詢）

3.行子查詢（多列多行）

特點：

1.子查詢放在小括号内

2，标量子查詢一般搭配這單行操作符使用

> < >= <= = <>

标量子查詢

例題1：誰的工資比abel高？

1.查詢abel的工資

select salary

from employee

where last_name = ‘able’

2.查詢員工的資訊，滿足salary》1

select salary

from employee

where salary》

（

    1

）

分頁查詢

應用場景：當要顯示的資料，一頁顯示不全需要分頁

文法：

    select 查詢清單

    from 表

    【join type】 join表2

    on 連接配接條件

    where 篩選條件

    GROUP BY 分組字段

    having 分組後的篩選

    order by 排序的字段

    limit 起始索引，size

select 查詢清單        7

from 表            1

連接配接類型 join 表2    2

on 連接配接條件        3

where 篩選條件        4

group by 分組清單    5

having 分組後的篩選    6

order by 排序清單    8

limit 偏移，條目數    9

9.聯合查詢

union 聯合 合并：将多條查詢語句合并成一個結果

自主去重 加 all可以不去重

DML語言 

資料操作語言

插入insert，修改update，删除delete

文法

插入

insert into 表名（列名） values（值1，。。。）

可以支援插入多行

支援子查詢

例題

insert into beauty（id，name，phone）

select id，boyname，‘1234567’

from boys where id<3；

insert into 表名

set 列名 = 值，列名 = 值。。。。

修改資料 

文法 

1.修改單表的記錄

update 表名

set 列=新值，列=新值，...

where 篩選條件

例題修改beauty表中姓唐的女神的電話為138999

update beauty set phone = ‘139999’

where name like‘唐%’；

修改多表的記錄

文法：mysql92

update 表1 别名，表2，别名

set 列=值，...

where 篩選條件

文法mysql99

update 表一，别名

inner|left|right join 表2 别名

on 連接配接條件 

set 列 = 值，...

where 篩選條件；

删除語句

1.單表的删除

delete from 表名 where 篩選條件

2.多表的删除

truncate table 表名

DDL語言

1.庫的管理

建立修改删除

2.表的管理

建立修改删除

建立 create

修改alter

删除 drop

1.庫的管理

庫的建立 create database 【if not exists】 庫名；

庫的修改 更改庫的字元集

alter database 庫名 character set gbk；

庫的删除 drop database 【if exist】 庫名；

2.表的管理

表的建立 create table 表名（

    列名 列的類型【（長度） 限制】， 

    列名 列的類型【（長度） 限制】， 

    列名 列的類型【（長度） 限制】， 

）

表的修改

    修改列名 

    alter table 表名 change column     列名 被改列名 列類型；

    修改列的類型或限制

    alter table 表名 modify column     列名  列類型；

    添加新列

    alter table 表名 add column 新列名 類型； 

    删除列

    altet table 表名 drop column 列名；

    修改表名

    alter table 表名 rename to 新表名；

    複制表結構

    create table 表一 like 表二

常見的資料類型

常見限制（限制，用于限制表中的資料，為了保證表中的資料的準确性和可靠性）

{

    NOT NULL，非空，用于保證該字段的值不能為空

    比如姓名，學号等

    default，預設，用于保證該字段有預設值

    比如性别

    PRIMARY KEY：主鍵，用于保證唯一性（非空）

    UNIQUE 唯一：用于保證字段有唯一性，可以為空

    CHECK：檢查支援（mysql不支援）

    FPREIGN KEY ：外鍵 用于限制兩個表的關系，用于保證該字段的值必須來自主表的關系列的值

}

表級限制

添加限制的時機：1.建立表時

        2.修改表時

create 表名（

    字段名 字段類型 限制

）

主鍵和唯一的差別 

        唯一性    允許為空 可以存在多個    

    主鍵：     √      ×        最多提一個

    唯一：     √      √        允許多個

辨別列（自增長列）

    提供預設序列值

    1建立表時添加辨別列 主鍵後加 AUTO_INCREMENT

    2修改表時添加辨別列 後加INT PRINARY KEY；

    3修改表時删除辨別列 

TCL（Transaction Control Language）事務控制語言

事務：一個或一組語句組成一個執行單元，這個執行單元要麼全都執行，要麼全不執行

事務屬性(ACID)

1.原子性 : 一個事務不可再分割，要麼都執行，要麼都不執行

2.一緻性：一個事務執行會使資料從一個一緻狀态切換到另外一個一緻狀态

3.隔離性：一個事務的執行不受其他事務的幹擾

4.持久性：一個事務一旦送出，則會永久的改變資料庫的資料

對于同時運作的多個事務 導緻并發的問題：

1.髒讀：對于兩個事務T1，T2，T1讀取了一個字段，然後T2更新了該字段但還沒有被送出的字段之後，T2復原，T1讀取的内容就是無效的

2.不可重複讀：對于兩個事務T1，T2，T1讀取了已經被T2更新但還沒有被送出的字段，然後T2更新了該字段，之後，T1再次讀取了同一個字段，值不相同

3.幻讀： 插入時

避免方法：設定隔離級别，避免并發

隔離級别： 

            髒讀    不可重複讀    幻讀

read uncommitted    可以    可以        可以

read committed        不可以    可以        可以

repeatable        不可以    不可以        可以 （預設第三個預設級别）

serializable        不可以    不可以        不可以

檢視隔離級别 select @@tx_isolation

設定隔離級别 set session|global transation isolation level 隔離級别；

truncate（不支援） 與 delete（支援復原） 在事務中的差別

視圖{

    含義：虛拟表

    mysql5.1出現的新特性，通過表動态生成的資料

}

create view 别名

AS

視圖的修改{

    1.create or replace view as  沒有就建立有就替代

    2.alter view 視圖名 as

}

視圖的删除{

    DROP view 視圖1，視圖2，視圖3

}

視圖檢視 {

    DESC 視圖名

    SHOW CREATE VIEW 視圖名；

}

視圖的修改

1.插入    insert into 視圖名 values（~）；

2.修改 update 視圖名 xxx=yyy where xxx=zzz

3.删除 delete from 視圖名 where xxx=yyy；

視圖權限

變量{

    系統變量

        全局變量：

            檢視所有的系統變量 show session/global variables； 

        會話變量

    自定義變量

        使用者變量

        局部變量

}

函數 建立文法

CREATE FUNCTION 函數名（參數清單）RETURNS 傳回類型

BEGIN

END

{    

    如果函數體中僅有一句話可以省略begin end

    使用delimiter語句設定結束智語

}

例題 根據員工名 傳回他的工資

delimiter $

CREATE FUNCTION myf2（rmpName VARCHAR(20)）RETURNS double

BEGIN

    set sal = 0;//定義使用者變量

    select salary into sal

    from employees

    where last_name = empName;

    return sal;

END $

檢視函數

SHOW CREATE FUNCTION myf3；

删除函數

DROP FUNCTION myf3;

流程控制結構

{

    順序結構：程式從上往下依次進行

    分支結構：程式從兩條或者多條路徑上去找一條去執行

    {

        if（表達式1，表達式2，表達式3）//若1成立，傳回2，否則3

        case結構

    }

    循環結構：程式在滿足一定條件的基礎上，重複執行一段代碼

}

mysql性能下架的原因：執行時間長，等待時間長

{

    查詢語句寫的爛

    索引失效：單值/複合

    關聯查詢太多join

    伺服器調優以及各種參數設定

}

SQL

索引（index）是幫助MYsql高效擷取資料的資料結構，可以得到索引的本質是資料結構

排好序的快速查找資料結構，降低i/o成本與cpu消耗

索引的類别：

    單值索引：一個索引隻包含單個列，一個表可以有多個單列索引

    唯一索引：索引列的值必須唯一，但必須有空值

    複合索引：一個索引包含多個列

    基本索引：{

            建立CREATE【unique】 INDEX indexname on mytable （columnname（length））

                ALTER mytable ADD 【unique】 INDEX【indexName】ON （columnname（length））

            删除 DROP INDEX 【indexName】 ON mytable

            檢視 SHOW INDEX FROM table_name\G

        }

索引分類 BTree

    索引結構：1.主鍵自動建立索引

          2.頻繁作為查詢條件應該建立索引

          3.查詢中與其他表關聯的字段，外鍵關系建立索引

          4.頻繁的更新不适合建立索引

          5.where條件用不到的字段不建立索引

          6.單鍵/組合索引的選擇問題

          7.查詢中排序的字段，排序字段若通過索引去通路将大大提高排序速度

          8.查詢中統計或者分組字段

    不适合建立索引：1.表記錄太少

            2.經常增删改查的表

            3.重複記錄太多的表

    性能分析：1.mysql query optimizer（自帶性能優化器）

          2.IO：磁盤I/O瓶頸發生在裝入資料遠大于記憶體容量的時候

          3.伺服器可以用：top，free，iostat和vmstat來檢視

使用explain可以模拟執行查詢語句

    Explain+SQL語句

            表頭資料：id select_type table type possible_keys key key_len ref rows

    id相同 執行順序，執行順序由上到下。如果是子查詢，id的序号會遞增，id值越大，優先值越高，越先被執行

    select_type {

            simple：簡單的select查詢，查詢中不包括子查詢或者union

            primary：查詢中包含任何複雜的子部分，最外層查詢則被标記為

            subquery：在select或者where清單中包含了子查詢

            derived：在FORM清單中包含的子查詢被标記為DERIVED

            union：若第二個select出現在union之後，則被标記為

            union_result：從union獲得的表

            }

    table：是關于資料是關于哪張表的

    type：{

        通路類型排列，顯示查詢使用了哪種類型從最好到最差依次是

        system>const>eq_ref>ref>range>index>ALL

        SYSTEM:表隻有一行記錄，const類型的特列，平時不會出現

        const：表示通過索引一次就找到了，const用于比較primary key或者union索引。

        er_ref:唯一索引掃描，對于每個索引鍵，表中隻有一條記錄為之比對。常見于主鍵或者唯一索引

        ref：非唯一性索引掃描，傳回比對單獨值的所有行。

        range：隻檢索給定範圍的行，使用一個索引來選擇行，key列顯示使用了哪些索引，一般就是在你的where語句中出現了between,<，>,in的查詢，這種範圍查找比全表掃描要好，因為它需要開始于索引的某一點，而結束語另一點，不用掃描全部索引。

        index：從索引中讀取，而不是all從硬碟中讀的。

        All：周遊全表找到比對的行。    

        }

    possible_keys：顯示可能應用在這張表中的索引，一個或多個。

    key：實際使用的索引

    key_len：表示索引中使用的位元組數，在不損失精确性的情況下，長度越短越好，key_len顯示的是指為索引字段的最大可能長度，并非實際長度，即key_len是根據定義所得，而不是通過表内索引檢索出的。

    ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。

    rows：根據表統計資訊及索引選用情況，大緻估算出找到所需的記錄所需要讀取的行數

    extra：包含不适合在其他列又很重要的資訊

    {

        Using filesort:說明mysql會對資料使用一個外部的索引排序，而不是按照表内的索引順序進行讀取，mysql中無法利用索引完成的排序操作稱為檔案排序。

        Using temporary：使用了臨時表儲存中間結果，MYsql在對查詢結果排序使用臨時表。常見于排序order by 和分組查詢 group by。

        Using Index：表示使用的select操作使用了覆寫索引，避免了通路表的資料行，效率不錯

    }

索引的優化：explain是性能優化的參數

    索引失效（應該避免）

    {

        1.全職比對最好

        2.最佳左字首法則

        3.不在索引列上做任何操作（計算，函數，（自動or手動）類型轉換），會導緻索引失效而轉向全表掃描

        4.儲存引擎不能使用索引範圍的條件右邊的列

        5.盡量使用覆寫索引（隻通路索引的查詢（索引列和查詢列一緻）），減少select*

        6.mysql在使用不等于（！=或者《》）的時候無法使用索引會導緻全表掃描

        7.is null，is not null也無法使用索引

        8.like以通配符開頭（‘%abc。。。’）mysql索引失效會變成全表掃描的操作

        9.字元串不加單引号索引失效

        10.少用or，用它來連接配接時索引會失效

    }