第一次親密接觸

一、數(shù)據(jù)庫相關(guān)概念：

①、數(shù)據(jù)庫的好處：

1、持久化數(shù)據(jù)到本地
2、使用數(shù)據(jù)庫管理軟件進(jìn)行結(jié)構(gòu)化查詢

②、數(shù)據(jù)庫常見概念

1、DB：數(shù)據(jù)庫，存儲數(shù)據(jù)的容器
2、DBMS：數(shù)據(jù)庫管理系統(tǒng)（數(shù)據(jù)庫軟件、數(shù)據(jù)庫產(chǎn)品）
3、SQL：結(jié)構(gòu)化數(shù)據(jù)查詢語言，不是某個數(shù)據(jù)庫特有的查詢語言，而是幾乎所有的結(jié)構(gòu)化數(shù)據(jù)庫通用的語言。

③、數(shù)據(jù)庫存儲數(shù)據(jù)的特點

1、數(shù)據(jù)存放到表中，表再放到庫中
2、一個庫可以有多張表。每個表擁有唯一表名來標(biāo)識自己
3、一張表中有多個列（COLUMN），又稱為字段，相當(dāng)于python中的屬性
4、表中的每一行數(shù)據(jù)相當(dāng)于python中的對象

④、常見的數(shù)據(jù)庫管理系統(tǒng)

Mysql、Oracal、db2，sqlserver

二、Mysql的介紹

①、Mysql背景

mysql AB 》sun 》oracal

②、mysql的優(yōu)點

1、開源、免費、成本低
2、性能高、移植性也好
3、體積小、便于安裝

③、mysql的安裝

版本

④、mysql服務(wù)的啟動和停止

方式一：通過命令行
net start mysql
net stop mysql
方式二：計算機——右擊——管理——服務(wù)

⑤mysql服務(wù)的登錄和退出

登錄：mysql 【-h主機名 -P端口號】 -u用戶名 -p密碼
-p密碼間不能有空格

退出：exit或ctrl+C

DQL語言

一、基礎(chǔ)查詢

1、語法

select 查詢列表
from 表名

2、特點

• 查詢列表可以是字段、常量、表達(dá)式、函數(shù)，也可以是多個的組合
• 查詢結(jié)果是一個虛擬表

3、示例

• 查詢單個字段
  select 字段名 from 表名
• 查詢多個字段
  select 字段名，字段名 from 表名
• 查詢所有字段
  select * from 表名
• 查詢常量
  select 常量值；
  注意：字符型和日期型的常量必須用單引號引起來，數(shù)值型不需要
  -查詢函數(shù)
  select 函數(shù)名(實參列表)
• 查詢表達(dá)式
  select 100/200；
• 起別名
  ①as
  ②空格
• 去重
  select distinct 字段名 from 表名
• 加號（做加法運算）
  select 數(shù)值+數(shù)值：直接運算
  select 字符+數(shù)值：先試圖將字符轉(zhuǎn)化為數(shù)值，如果轉(zhuǎn)化成功，則進(jìn)行運算，如不能轉(zhuǎn)化，則將字符轉(zhuǎn)化為0再進(jìn)行運算
  select null+值 結(jié)果為null
• concat（拼接字符串）
  select concat（字符1，字符2，...）
• ifnull 函數(shù)
  判斷某字段或表達(dá)式是否為null，如果為null返回指定的值，如果不為null，返回原本的值
  select ifnull(commission_pct,0) from employees
• isnull 函數(shù)
  判斷字段的值是否為null，如果為null，返回1，否則返回0
  select isnull(commission_pct) from employees

二、條件查詢

1、語法

select 查詢列表 ③
from 表名 ①
where 篩選條件 ②

2、篩選條件的分類

①、簡單條件運算符
`> < = <> != >= <= <=>
②、邏輯運算符
&& and
|| or
！ not
③、模糊查詢

• like：一般搭配通配符來匹配字符型數(shù)據(jù)，但也可查詢數(shù)值型
  select *
  from employees
  where department_id like '1__'
  通配符：%匹配任意多個字符，_匹配單個字符
• between and
• in
• is null/is not null：用于判斷null值

is null PK <=>

普通類型數(shù)值

三、排序查詢

1、語法

select 查詢列表
from 表
where 篩選條件
order by 排序列表 【asc|desc】

2、特點

①、后綴
asc：升序
desc：降序
②、排序列表支持單個字段、多個字段、函數(shù)、表達(dá)式、別名
③、order by位置一般放在查詢語句的最后，如果有l(wèi)imit，則為倒數(shù)第二

四、常見函數(shù)

1、概述

功能：類似python中的方法
好處：提高重用性和隱藏實現(xiàn)細(xì)節(jié)
調(diào)用： select 函數(shù)名(實參列表)；

2、單行函數(shù)

①字符函數(shù)

concat：連接
substr：截取字串
upper：變大寫
lower：變小寫
replace：替換
length：獲取字節(jié)長度
trim：去前后空格
lpad：左填充
rpad：右填充
instr：獲取第一次出現(xiàn)的索引

②數(shù)字函數(shù)

ceil：向上取整
floor：向下取整
round：四舍五入
mod：取模
truncate：截斷
rand：獲取隨機數(shù)，返回0-1的小數(shù)（取不到一，可以無限接近）

③、日期函數(shù)

nov：返回當(dāng)前日期加時間
year：返回年
month：返回月
day：返回日
hour：小時
minute：分鐘
second：秒
date_formate：將日期轉(zhuǎn)化為字符
str_to_date：將字符轉(zhuǎn)化為時間
curdate：返回當(dāng)前日期
curtime：返回當(dāng)前時間
monthname：以英文返回月
datediff：返回兩個日期相差的天數(shù)

④、其他函數(shù)

version：當(dāng)前數(shù)據(jù)庫服務(wù)器版本
database：當(dāng)前打開的數(shù)據(jù)庫
user：當(dāng)前用戶
password("字符")：對字符加密
md5("字符")：返回字符的md5加密形式

⑤、流程控制函數(shù)：

• if（條件表達(dá)式，表達(dá)式1，表達(dá)式2）
  如果條件表達(dá)式成立，返回表達(dá)式1，否則返回表達(dá)式2
• case情況1
  case 變量或表達(dá)式或字段
  when 常量1 then 值1
  when 常量2 then 值2
  ...
  else 值n
  end
• case情況2
  when 條件1 then 值1
  when 條件2 then 值2
  ...
  else 值n
  end

3、分組函數(shù)

①、分類

max 最大值
min 最小值
sum 和
avg 平均值
count 計算個數(shù)

②、特點

語法

select max(字段) from 表名；

支持的類型

sum和avg一般用于處理數(shù)值型
max、min、count可以處理任何類型

以上分組函數(shù)都忽略null值

都可以搭配distinct使用，實現(xiàn)去重的統(tǒng)計

select sum(distinct 字段) from 表

count函數(shù)

count(字段)：統(tǒng)計該字段為空值的函數(shù)
count(*)：統(tǒng)計結(jié)果的函數(shù)
count(1)：也可統(tǒng)計行數(shù)

案例：查詢每個部門員工數(shù)
1 xx 10
2 aa 20
3 bb 20
4 aa 40
5 hh 40

select count(*)
from employee
group by department_id
order by department_id

效率上：

• MyISAM存儲引擎，count(*)效率最高
• InnoDB存儲引擎，count(*)和count（1）效率>count（字段）

五、分組查詢

1、語法

select 分組函數(shù)，分組后的字段 ⑤
from 表 ①
【where 篩選條件】 ②
group by 分許的字段 ③
【having 分組后的篩選】 ④
【order by 排序列表】 ⑥
注意：having 和 group by一般不起別名（oracal不支持，為了通用性，移植性等事宜一般在mysql也不用）

2、特點

六、連接查詢

1、含義

當(dāng)查詢中涉及到多個表中的字段，使用多表連接查詢
select 字段1，字段2
from 表1，表2，...；

• 笛卡爾乘積：當(dāng)查詢多個表時，沒有添加有效的連接條件，導(dǎo)致多個表所有行實現(xiàn)完全連接。
• 如何解決：添加有效的連接條件

2、分類

按年代分：

sql92：（一般認(rèn)為只支持內(nèi)連接）

• 等值
  select 查詢列表
  from 表1 別名1，表2，別名2
  where 表1.key1 = 表2.key2
  【and】
  【group by 分組字段】
  【having 分組后的篩選】
  【order by 排序字段】

特點：
一般為表起別名
多表的順序可以調(diào)換
n表連接至少需要n-1個連接條件
等值連接的結(jié)果為多表的交集部分

• 非等值
  select 查詢列表
  from 表1 別名1，表2，別名2
  where 非等值連接條件
  【and】
  【group by 分組字段】
  【having 分組后的篩選】
  【order by 排序字段】
• 自連接
  select 查詢列表
  from 表 別名1，表，別名2
  where 別名1.key1 = 別名2.key2
  【and】
  【group by 分組字段】
  【having 分組后的篩選】
  【order by 排序字段】

其實也支持一部分外連接（在oracal、sqlserver中）但是表現(xiàn)不好，mysql中不支持。

sql99

內(nèi)連接

• 語法：
  select 查詢列表
  from 表1 別名
  【inner】 join 表2 別名 on 連接條件
  where 篩選條件
  group by 分組列表
  having 分組后篩選
  order by 排序列表
  limit 子句

• 特點：
  ①表的順序可以調(diào)換
  ②內(nèi)連接的結(jié)果 = 多表的一個交集
  ③n表連接至少需要n-1個連接條件

• 分類
  等值
  非等值
  自連接

外連接

• 語法：
  select 查詢列表
  from 表1 別名
  left|right|full【outer】 join 表2 別名 on 連接條件
  where 篩選條件
  group by 分組列表
  having 分組后篩選
  order by 排序列表
  limit 子句

• 特點：
  ①查詢的結(jié)果=主表中所有的行，其中從表會顯示匹配行，如果不匹配顯示null
  ②left join 左邊為主表，right join 右邊為主表，full join兩邊都是主表
  ③一般用于查詢除交集部分的剩余不匹配行（匹配部分一般用內(nèi)連就能做）

• 分類：
  左外
  右外
  全外（mysql不支持）

交叉連結(jié)

• 語法：
  select 查詢條件
  from 表1 別名
  cross join 表2 別名；

類似笛卡爾乘積

七、子查詢：

1、含義：

嵌套在其他語句內(nèi)部的select語句稱為子查詢或內(nèi)查詢，外面的語句可以是insert、update、delete、select等，多放在selelct語句內(nèi)

2、分類

①按出現(xiàn)位置

• select后面：僅標(biāo)量子查詢
• from后面：一般表子查詢
• where或having后面：
  標(biāo)量子查詢
  列子查詢
  行子查詢
• exists后面
  標(biāo)量子查詢
  列子查詢
  行子查詢
  表子查詢

②按結(jié)果集的行列

• 標(biāo)量子查詢（單行子查詢）：結(jié)果集為一行一列
• 列子查詢：結(jié)果集為多行一列
• 行子查詢：結(jié)果集為多行多列
• 表子查詢：結(jié)果集為多行多列

三、示例
用得最多：
where或having后面：

• 標(biāo)量子查詢
  查詢最低工資員工的姓名和工資
  ①查詢最低工資
  select min(salary) from employees
  ②查詢最低工資員工的姓名和工資
  select last_name,salary from employees
  where salary=(
  select min(salary) from employees
  )
• 列子查詢
  查詢所有是領(lǐng)導(dǎo)的員工姓名
  ①查詢所有領(lǐng)導(dǎo)的id
  select distinct manaerger_id
  from employees
  ②查詢所以領(lǐng)導(dǎo)的名字
  selelct last_name
  from employess where employee_id in (
  select distinct manaerger_id
  from employees
  )

八、分頁查詢

1、應(yīng)用場景

當(dāng)要查詢的條目數(shù)太多，一頁顯示不全

2、語法

select 查詢列表
from 表
limit 【offset,】size；

注意：
offset代表的是起始條目索引，默認(rèn)從0開始
size代表的是顯示的條目數(shù)

公式：
假如要顯示的頁數(shù)是page，每一頁條目數(shù)為size
select 查詢列表
from 表
limit (page-1)*size,size；

九、聯(lián)合查詢

1、含義

union：合并、聯(lián)合，將多次查詢結(jié)果合并成一個結(jié)果

2、語法

查詢語句1
union 【all】
查詢語句2
union 【all】
...

3、意義

①將一個復(fù)雜語句簡化為簡單語句
②在查詢多個表時，查詢的列基本是一致的

4、特點

①要求多條查詢語句列數(shù)必須一致
②要求查詢語句查詢的各列類型、順序最好一直
③Union和Union All的區(qū)別

• Union：對兩個結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時進(jìn)行默認(rèn)規(guī)則的排序；
• Union All：對兩個結(jié)果集進(jìn)行并集操作，包括重復(fù)行，不進(jìn)行排序；

十、查詢語句總結(jié)

1、語法

select 查詢列表 ⑦
from 表1 別名 ①
連接類型 join 表2 別名 ②
on 連接條件 ③
where 篩選條件 ④
group by 分組 ⑤
having 篩選 ⑥
order by排序列表 ⑧
limit 起始條目索引，條目數(shù)；⑨

DML語言

一、插入

方式1

語法：

insert into 表名(字段名，...) values(值，...)

特點：

①要求值的類型和字段的類型要一致或兼容（“123”可轉(zhuǎn)化為123但“join”不能轉(zhuǎn)化為數(shù)值型）
②子段的個數(shù)不一定與原始表中的字段個數(shù)和順序一致，但必須保證值和字段一一對應(yīng)
③假如表中有可以為null的字段，可以通過以下兩種方式插入null值：

• 字段和值都省略
• 字段寫上，值寫入null

④字段和值的個數(shù)必須一致
⑤字段名可以省略，這種情況下字段部分默認(rèn)包含所有列

方式2

語法：

insert into 表名 set 字段=值，字段=值，...

兩種方式的區(qū)別

• 方式一支持一次插入多行
  insert into 表名(字段名，...) values(值，...)，(值，...)，...

• 方式一支持子查詢：
  insert into 表名
  查詢語句

二、修改

1、修改單表的紀(jì)錄

update 表名 set 字段=值，字段=值 【where 篩選條件】

2、修改多表記錄

update 表名
left|right|inner join 表2 on 連接條件
set 字段=值，字段=值 【where 篩選條件】

四、刪除

方式1：delete

刪除單表記錄

語法：
delete from 表名 【where 篩選條件】【limit 條目數(shù)】

刪除多表記錄（級聯(lián)刪除）

語法：
delete 別名1，別名2
from 表1 別名1
left|right|inner join 表2 別名2 on 連接條件
【where 篩選條件】

方式2：truncate

truncate table 表名

兩種方式的區(qū)別（面試題）：

1、delete刪除后如果再插入，從斷點開始
truncate（清空）：刪除后如果再插入，自增長列從1開始
2、delete可以添加刪除條件
truncate不行
3、truncate效率較高
4、truncate沒有返回值
delete會返回受影響的條數(shù)
5、truncate不可以回滾
delete可以回滾

DDL語言

一、庫的管理

1、創(chuàng)建庫

create database 【if not exists】 庫名 【character set 字符集名】

2、修改庫

alter database 庫名 charecter set 字符集名

3、刪除庫

drop databse 【if exists】 庫名

二、表的管理

1、創(chuàng)建表

create table 【if not exists】 表名 (
字段名 字段類型 【約束】，
字段名 字段類型 【約束】，
...
字段名 字段類型 【約束】
)

2、修改表

• 添加列
  alter table 表名 add column 列名 類型 【約束】【first|after 列名】
• 修改列的類型或約束
  alter table 表名 modify column 列名 新類型 【新約束】
• 修改列名
  alter table 表名 change column 舊列名 新列名 新類型 【新約束】
• 刪除列
  alter table 表名 drop column 列名
• 修改表名
  alter table 表名 rename 【to】新表名

3、刪除表

drop table 【if exists】表名

4、復(fù)制表

• 復(fù)制表結(jié)構(gòu)
  create table 表名 like 舊表
• 復(fù)制表結(jié)構(gòu)和內(nèi)容
  create table 表名
  select 查詢列表 from 舊表 【where 篩選條件】

三、數(shù)據(jù)類型、

數(shù)值型

• 整型
  tinyint、smallint、midiumint、int/integer、bigint
  分別占1、2、3、4、8字節(jié)

特點：

• 都可設(shè)置有符號和無符號，默認(rèn)有符號，通過unsign可設(shè)置無符號
• 如果超出范圍，會報out or range異常，并插入臨界值
• 長度可以不指定，默認(rèn)會有一個長度

長度代表顯示的最大寬度，如果不夠則左邊用0填充，但需要搭配zerofill，并且默認(rèn)變?yōu)闊o符號整型

浮點型

• 定點數(shù)：decimal（M,D）
• 浮點數(shù)：
  float（M,D）
  double（M,D）

特點：

• M代表整數(shù)部分+小數(shù)部分的長度，D代表小數(shù)部分的長度
• 如果超出范圍，會報out or range異常，并插入臨界值
• M和D都可以省略，但對于定點數(shù)，M默認(rèn)為10，D默認(rèn)為0
• 如果精度要求高，優(yōu)先使用定點數(shù)

字符型

char、varchar、binary、varbinary、enum、set、text、blob

• char：固定長度字符，寫法為char(M)，最大長度不超過M，其中M可以省略，默認(rèn)為1
• varchar：可變長度字符，寫法為varchar(M)，最大長度不超過M，其中M不可以省略

日期型

• year 年
• date 日期
• time 時間
• datetime
  日期+時間 8字節(jié) 可保存時間范圍大
• timestamp
  日期+時間 4字節(jié) 可保存時間范圍小-2038 易受時區(qū)等的影響，但更能反映真實時區(qū)的時間

四、約束

1、常見的約束

• NOT NULL：非空：該字段值必填
• UNIQUE：唯一，該字段值不可重復(fù)
• DEFAULT：默認(rèn)，該字段不動手動插入，有默認(rèn)值
• CHECK：檢查，mysql不支持
• PRIMARY KEY：主鍵，該字段的值不可重復(fù)且非空
• FOREIGN KEY：外鍵，該字段的值引用了另外的表的字段

主鍵和唯一

• 區(qū)別：
  ①一個表至多有一個主鍵，但可以有多個唯一建
  ②主鍵不允許為空，唯一可為空
• 相同點：
  ①都具有唯一性
  ②都支持組合鍵，但不推薦（不穩(wěn)定）

外鍵

• 增加外鍵
  ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREGIN KY (majorid) REFERENCE major(id)

可以通過下列方式刪除主表的記錄

1、用于限制兩個表的關(guān)系，從表的字段值引用了主表的某字段值
2、外鍵列額主表的被引用列要求類型一致，意義相同，名稱無要求
3、主表的被引用列要求是一個key，一般為主鍵
4、插入數(shù)據(jù)，先插入主表
5、刪除數(shù)據(jù)，先刪除從表

• 設(shè)置級聯(lián)刪除（刪除主表中數(shù)據(jù)會級聯(lián)刪除從表中數(shù)據(jù)）
  ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREGIN KY (majorid) REFERENCE major(id) ON DELETE CASCADE
• 設(shè)置級聯(lián)置空（刪除主表中數(shù)據(jù)會級聯(lián)將從表中數(shù)據(jù)置空）
  ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREGIN KY (majorid) REFERENCE major(id) ON DELETE SET NULL

2、創(chuàng)建表時添加約束

create 表名（
字段名 字段類型 not null，#非空
字段名 字段類型 unique，#唯一
字段名 字段類型 primary key，#主鍵
字段名 字段類型 default 值，#默認(rèn)
constraint 約束名 foreign key（字段名） reference 主表（被引用字段）
）

注意：

列級約束可以在一個字段上添加多個，中間用空格隔開，沒有順序要求

3、修改表時添加或刪除約束

1、非空

• 添加非空
  alter table 表名 modify column 字段名 字段類型 not null
• 刪除非空
  alter table 表名 modify column 字段名 字段類型
  2、默認(rèn)
• 添加默認(rèn)
  alter table 表名 modify column 字段名 字段類型 default 值
• 刪除默認(rèn)
  alter table 表名 modify column 字段名 字段類型
  3、主鍵
• 添加主鍵
  alter table 表名 add primary key(字段名)
• 刪除主鍵
  alter table 表名drop primary key(字段名)
  4、唯一
• 添加唯一
  alter table 表名 add 【constraint 約束名】unique key(字段名)
• 刪除唯一
  alter table 表名drop inddex 索引名
  5、外鍵
• 添加外鍵
  alter table 表名 add 【constraint 約束名】foreign key(字段名) references 主表（被引用表）
• 刪除唯一
  alter table 表名drop foreign key 約束名

4、自增長列

特點：
①、不用手動插入值，可以自動提供序列值，默認(rèn)1開始，步長為1

• 如果要更改起始值：手動輸入值

• 如果要更改步長：更改系統(tǒng)變量值
  ②、一個表至多有一個自增長列
  ③、自增長列值能為數(shù)值型
  ④、自增長列必須為一個key

• 創(chuàng)建表時設(shè)置自增長列
  create table（
  字段名 字段類型 約束 auto_increment
  ）

• 修改表時設(shè)置自增長列
  alter table 表 modify column 字段名 字段類型 約束 auto_increment

• 刪除自增長列
  alter table 表 modify column 字段名 字段類型 約束

TCL語言

事務(wù)

1、含義：

一條或多條sql語句組成的一個執(zhí)行單位，一組sql語句要么都執(zhí)行要么都不執(zhí)行

2、特點：ACID

• A 原子性：
  一個事務(wù)是一個不可再分割的整體，要么都執(zhí)行要么都不執(zhí)行
• C 一致性：
  一個事務(wù)使數(shù)據(jù)從一個一致性狀態(tài)到另一個一致性狀態(tài)
• I 隔離性：
  一個事務(wù)不受其他事務(wù)的干擾，多個事務(wù)相互隔離
  D 持久性：
  一個事務(wù)一旦提交了，則永久性保存到本地

3、事物的使用步驟：

了解：

• 隱式（自動）事務(wù)：沒有明顯的開啟和結(jié)束，可以自動提交，比如insert、update、delete
• 顯示事務(wù)：具有明顯的開始和結(jié)束

使用顯示事務(wù)：

• ①開啟事務(wù)
  set autocommit=0
  start transaction 可以省略
• ②編寫一組邏輯sql語句
  注意：事務(wù)中的sql語句支持的是insert、update、delete這些語句，select也算（不會影響數(shù)據(jù)庫）
• ③【設(shè)置回滾點】
  savepoint 回滾點名
• ③結(jié)束事務(wù)
  提交 commit
  回滾 rollback 【to 回滾點名】

4、并發(fā)事物

• ①事務(wù)并發(fā)問題是如何發(fā)生的？
  多個事務(wù)同時操作同一個數(shù)據(jù)庫時
• ②并發(fā)問題有哪些？

臟讀：

一個事務(wù)讀取了另一個事務(wù)還沒有提交的數(shù)據(jù)，另一事務(wù)回滾后發(fā)生臟讀

不可重復(fù)的：

一個事務(wù)在另一事務(wù)開始前和提交后分別讀取，結(jié)果不一樣（另一事務(wù)更新數(shù)據(jù)）

幻讀：

一個事務(wù)在另一事務(wù)開始前和提交后分別讀取，行數(shù)不一樣（另一事務(wù)插入數(shù)據(jù)）

• ③如何解決并發(fā)問題？
  通過設(shè)置隔離級別
• ④隔離級別

視圖

1、含義

• mysql5.1出現(xiàn)的新特性
• 本身是一個虛擬表，是通過原始表中的數(shù)據(jù)動態(tài)生成的
• 好處：
  ①簡化了sql語句
  ②提高了重用性
  ③保護(hù)了基表數(shù)據(jù)，提高了安全性

2、創(chuàng)建

create view 視圖名
as
查詢語句；

3、修改

• 方式一：
  create or replace view 視圖名
  as
  查詢語句；
• 方式二：
  alter view 視圖名
  as
  查詢語句；

4、刪除

drop view 視圖1，視圖2，...

5、查看

desc 視圖名
show create view 視圖名

6、使用

• 插入 insert
• 修改 update
• 刪除 delete
• 查看 select
• 注意：視圖一般用于查詢，而不是更新的，所以具備以下特點的視圖都不允許更新
  ①包含分組函數(shù)、group by、distinct、having、union
  ②join
  ③常量視圖
  ④where中的子查詢用到了from中的表
  ⑤用到了不可更新的視圖（視圖來源于視圖的時候）

7、視圖和表的對比