數據庫系統學習

關系型數據庫

關系型數據庫建立在關系模型基礎上的數據庫，關系型數據庫是由多張能相互相連的二維表組成的數據庫
優點：

都是使用表結構，格式一致，易于維護
使用通用的sql語言操作，使用方便，可用于復雜查詢
數據存儲在磁盤中，安全

結構化查詢語言，一門操作關系型數據庫的編程語言
定義操作所有關系型數據庫的統一標準

sql分類

DDL(Data Definition Language）數據庫定義語言，用來定義數據庫對象：數據庫，表、列等
DML(Data Manipulation Language)數據庫操作語言，用來對數據庫中的表數據進行增刪改查
DQL(Data Query Language)數據查詢語言，用來查詢數據庫中表的記錄（數據）
DCL(Data Control Language)數據庫控制語言，用來定義數據庫的訪問權限和安全級別，及創建用戶

DDL-操作數據庫

查看Mysql中都有哪些數據庫

SHOW DATABASES;

使用數據庫

USE 數據庫名稱;

查看當前正在使用的數據庫

select database();

查看一個數據庫的定義信息

show create database 數據庫名；

創建數據庫

CREATE DATABASES 數據庫名稱;

創建數據庫（判斷，如果不存在則創建）

CREATE DATABASE IF NOT EXISTS 數據庫名稱;

創建指定名稱的數據庫，并且指定字符集（一般都指定
utf-8）

create database 數據庫名 character set 字符
集；

刪除數據庫

DROP DATABASE 數據庫名稱

刪除數據庫（判斷，如果存在則刪除）

DROP DATABASE IF EXISTS 數據庫名稱

修改數據庫
數據庫的字符集修改操作

alter database 數據庫名 character set 字符集；

DDL-操作表

創建create
查詢retrieve
修改update
刪除delete

查詢表

查詢當前數據庫下所有的表名稱

show tables；

查詢表結構

desc 表名稱；

創建表

創建表

create table 表名(字段名1 數據類型1，字段名2 數據類型2
）；

查看表

查看當前數據庫中的所有表名

show tables;

查看數據表的結構

desc 表名；

刪除表

刪除表

drop table 表名；

刪除表同時判斷表是否存在

drop table if exists 表名；

修改表

修改表名

alter table 表名 rename to 新的表名；

添加一列

alter table 表名 add 列名 數據類型；

修改數據類型

alter table 表名 modify 列名 新數據類型；

修改列名和數據類型

alter table 表名 change 列名 新列名 新數據類型；

修改表的字符集

alter table 表名 character set 字符集

刪除一列

alter table 表名 drop 列名;

數據類型

mysql支持多種類型，可以分為三類：

數值
日期
字符串

DML操作表

插入數據

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);

插入指定字段

INSERT INTO category (cname) VALUES('白骨精');

更改數據

不帶條件的修改

update 表名 set 列名 = 值

帶條件的修改

update 表名 set 列名 = 值 [where 條件表達式：字段名 = 值 ]

刪除數據

刪除所有數據

delete from 表名

指定條件刪除數據

delete from 表名 [where 字段名 = 值]

DQL 查詢表中數據

簡單查詢

select 列名 from 表名

條件查詢

select 列名 from 表名 where 條件表達式

比較運算符

運算符	說明
> < <= >= = <> !=	大于、小于、大于(小于)等于、不等于
BETWEEN …AND…	顯示在某一區間的值例如: 2000-10000之間： Between 2000 and 10000
IN(集合)	集合表示多個值,使用逗號分隔,例如: name in (悟空，八戒) in中的每個數據都會作為一次條件,只要滿足條件就會顯示
ILIKE ‘%張%’	模糊查詢
IS NULL	查詢某一列為NULL的值, 注: 不能寫 = NULL

邏輯運算符

運算符	說明
And &&	多個條件同時成立
Or	多個條件任一成立
Not	不成立

模糊查詢通配符

通配符	說明
%	表示匹配任意多個字符串
_	表示匹配一個字符

DQL操作單表

排序

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
-- 組合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

聚合函數

聚合函數	作用
count(字段)	統計指定列不為NULL的記錄行數
sum(字段)	計算指定列的數值和
max(字段)	計算指定列的最大值
min(字段)	計算指定列的最小值
avg(字段)	計算指定列的平均值

分組

分組查詢指的是使用 GROUP BY 語句,對查詢的信息進行分組,相同數據作為一組

分組時可以查詢要分組的字段, 或者使用聚合函數進行統計操作.

在這里插入圖片描述

SELECT 分組字段/聚合函數 FROM 表名 GROUP BY 分組字段 [HAVING 條件];#1. 查詢有幾個部門
SELECT dept_name AS '部門名稱' FROM emp GROUP BY dept_name;
#2.查詢每個部門的平均薪資
SELECT
dept_name AS '部門名稱',
AVG(salary) AS '平均薪資'
FROM emp GROUP BY dept_name;#3.查詢每個部門的平均薪資, 部門名稱不能為null
SELECT dept_name AS '部門名稱',AVG(salary) AS '平均薪資'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

having使用：

需要在分組后,對數據進行過濾,使用關鍵字 having
分組操作中的having子語句，是用于在分組后對數據進行過濾的，作用類似于where條件。

# 查詢平均薪資大于6000的部門
-- 需要在分組后再次進行過濾,使用 having
SELECT
dept_name ,
AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000 ;

having與where的區別

過濾方式	特點
where	where 進行分組前的過濾 where 后面不能寫聚合函數
having	having 是分組后的過濾 having 后面可以寫聚合函數

limit關鍵字

limit 關鍵字的作用

limit是限制的意思,用于限制返回的查詢結果的行數 (可以通過limit指定查詢多少行數據)
limit 語法是 MySql的方言,用來完成分頁

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

limit offset , length; 關鍵字可以接受一個或者兩個為0 或者正整數的參數
offset 起始行數, 從0開始記數, 如果省略則默認為 0
length 返回的行數

# 查詢emp表中的前5條數據
-- 參數1 起始值,默認是0 , 參數2 要查詢的條數
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;
# 查詢emp表中 從第4條開始,查詢6條
-- 起始值默認是從0開始的.
SELECT * FROM emp LIMIT 3 , 6;

分頁操作

-- 分頁操作 每頁顯示3條數據
SELECT * FROM emp LIMIT 0,3; -- 第1頁
SELECT * FROM emp LIMIT 3,3; -- 第2頁 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三頁
-- 分頁公式 起始索引 = (當前頁 - 1) * 每頁條數
-- limit是MySql中的方言

sql約束

約束的作用: 對表中的數據進行進一步的限制，從而保證數據的正確性、有效性、完整性. 違反約束的不正確數據,將無法插入到表中
常見的約束

約束名	約束關鍵字
主鍵	primary key
唯一	unique
非空	not null
外鍵	foreign key

主鍵約束

不可重復、唯一、非空

添加主鍵約束

字段名 字段類型 primary key

刪除主鍵約束

ALTER TABLE 表名 DROP PRIMARY KEY;

主鍵的自增

關鍵字:
AUTO_INCREMENT 表示自動增長(字段類型必須是整數類型)

修改主鍵自增

-- 創建主鍵自增的表,自定義自增其實值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;

delete和truncat對自增長的影響

清空表數據的方式	特點
delete	只是刪除表中數據，對自增沒有影響
truncate	是將整個表刪除掉，然后創建一個新的表自增的主鍵，重新從1開始

非空約束

字段名 字段類型 not null

唯一約束

字段名 字段值 unique

主鍵約束和唯一約束的區別：

主鍵約束唯一且不能為空
唯一約束，唯一可以為空
一個表中只能有一個主鍵約束，但是可以有多個唯一約束

默認值

默認值約束用來指定某列的默認值

字段名 字段類型 DEFAULT 默認值

數據庫事務

事務是一個整體,由一條或者多條SQL 語句組成,這些SQL語句要么都執行成功,要么都執行失敗, 只要有一條SQL出現異常,整個操作就會回滾,整個業務執行失敗

回滾
即在事務運行的過程中發生了某種故障，事務不能繼續執行，系統將事務中對數據庫的所有已完成的操作全部撤銷，滾回到事務開始時的狀態。（在提交之前執行）

mysql事務操作

兩種方式操作事務

手動提交事務
自動提交事務

手動提交事務

功能	語句
開啟事務	start transaction;或者begin
提交事務	commit
回滾事務	rollback;

start transaction：這個語句顯式地標記一個事務的起始點。
commit：表示提交事務，即提交事務的所有操作，具體地說，就是將事務中所有對數據庫的更新都寫到磁盤上的物理數據庫中，事務正常結束。
rollback：表示撤銷事務，即在事務運行的過程中發生了某種故障，事務不能繼續執行，系統將事務中對數據庫的所有已完成的操作全部撤銷，回滾到事務開始時的狀態

流程：

執行成功的情況：開啟事務 -> 執行多條 SQL 語句 -> 成功提交事務
執行失敗的情況：開啟事務 -> 執行多條 SQL 語句 -> 事務的回滾

自動提交事務

MySQL 默認每一條 DML(增刪改)語句都是一個單獨的事務，每條語句都會自動開啟一個事務，語句執行完畢自動提交事務，MySQL 默認開始自動提交事務

取消自動提交
登錄mysql，查看autocommit狀態

SHOW VARIABLES LIKE 'autocommit';

on：自動提交
off：手動提交

把 autocommit 改成 off;

SET @@autocommit=off;

事務的四大特性ACID

特性	含義
原子性	每個事務都是一個整體，不可再拆分，事務中所有的 SQL 語句要么都執行成功，要么都失敗。
一致性	事務在執行前數據庫的狀態與執行后數據庫的狀態保持一致。如：轉賬前2個人的總金額是2000，轉賬后 2 個人總金額也是 2000
隔離性	事務在執行前數據庫的狀態與執行后數據庫的狀態保持一致。如：轉賬前2個人的總金額是2000，轉賬后 2 個人總金額也是 2000
持久性	一旦事務執行成功，對數據庫的修改是持久的。就算關機，數據也是要保存下來的.

mysql事務隔離級別

并發訪問會產生的問題

產生的問題	說明
臟讀	一個事務讀取到了另一個事務中尚未提交的數據
不可重復讀	一個事務中兩次讀取的數據內容不一致, 要求的是在一個事務中多次讀取時數據是一致的這是進行 update 操作時引發的問題
幻讀	一個事務中,某一次的 select 操作得到的結果所表征的數據狀態, 無法支撐后續的業務操作. 查詢得到的數據狀態不準確,導致幻讀.

設置隔離級別：

read uncommitted 讀未提交：不能防止以上三種任何一種情況
read committed 讀已提交：可以防止臟讀（Oracle和SQLServer默認隔離級別）
repeatable read 可重復讀：可以防止臟讀和不可重復讀（MySql默認隔離級別）
serializable串讀：可以防止以上三種情況

隔離級別越高，效率越低

隔離級別相關命令

查看隔離級別

select @@tx_isolation;

設置事務隔離級別，需要退出 MySQL 再重新登錄才能看到隔離級別的變化

set global transaction isolation level 級別名稱;
read uncommitted 讀未提交
read committed 讀已提交
repeatable read 可重復讀
serializable 串行化

臟讀

臟讀非常危險的，比如張三向李四購買商品，張三開啟事務，向李四賬號轉入 500 塊，然后打電話給李四說錢已經轉了。李四一查詢錢到賬了，發貨給張三。張三收到貨后回滾事務，李四的再看錢沒了

解決方法：
將全局的隔離級別進行提升為: read committed

不可重復讀

不可重復讀: 同一個事務中,進行查詢操作,但是每次讀取的數據內容是不一樣的

會遇到的問題：
比如銀行程序需要將查詢結果分別輸出到電腦屏幕和發短信給客戶，結果在一個事務中針對不同的輸出目的地進行的兩次查詢不一致，導致文件和屏幕中的結果不一致，銀行工作人員就不知道以哪個為準了

解決方法：
將全局的隔離級別進行提升為: repeatable read

幻讀

幻讀: select 某記錄是否存在，不存在，準備插入此記錄，但執行 insert 時發現此記錄已存在，無法插入，
此時就發生了幻讀

將事務隔離級別設置到最高 SERIALIZABLE ，以擋住幻讀的發生
如果一個事務，使用了SERIALIZABLE——可串行化隔離級別時，在這個事務沒有被提交之前 , 其他的線程，只能等到當前操作完成之后，才能進行操作，這樣會非常耗時，而且，影響數據庫的性能，數據庫不會使用這種隔離級別

外鍵約束

外鍵指的是在從表中與主表的主鍵對應的那個字段
使用外鍵約束可以讓兩張表之間產生一個對應關系,從而保證主從表的引用的完整性

創建外鍵約束

新建表時添加外鍵

[CONSTRAINT] [外鍵約束名稱] FOREIGN KEY(外鍵字段名) REFERENCES 主表名(主鍵字段名)

已有表添加外鍵

ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵約束名稱] FOREIGN KEY (外鍵字段名) REFERENCES 主表(主鍵字段名)

刪除外鍵約束

alter table 從表 drop foreign key 外鍵約束名稱

外鍵約束的注意事項

從表外鍵類型必須與主表主鍵類型一致否則創建失敗
添加數據時, 應該先添加主表中的數據
刪除數據時,應該先刪除從表中的數據

級聯刪除操作
如果想實現刪除主表數據的同時,也刪除掉從表數據,可以使用級聯刪除操作

級聯刪除
ON DELETE CASCADE-- 重新創建添加級聯操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加級聯刪除
ON DELETE CASCADE
);

多表查詢分類

內連接查詢

內連接的特點:
通過指定的條件去匹配兩張表中的數據, 匹配上就顯示,匹配不上就不顯示
比如通過: 從表的外鍵 = 主表的主鍵方式去匹配

隱式內鏈接
from子句，后面直接寫多個表名，使用where制定連接條件，這種連接方式是隱式內鏈接，使用where條件清除無用的數據

SELECT 字段名 FROM 左表, 右表 WHERE 連接條件;

顯式內鏈接
使用inner、join…on這種方式

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 條件
-- inner 可以省略

外連接查詢

左外連接
左外連接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

左外連接的特點：

以左表為基準，匹配右邊表中的數據；如果匹配上，就展示匹配到的數據
如果匹配不到，左表中的數據正常展示，右邊展示null

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 條件

右外連接
右外連接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

右外連接的特點：

以右表為基準，匹配左邊表中的數據，如果能匹配到，展示匹配到的數據
如果匹配不到，右表中的數據正常展示，左邊展示為null

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 條件

各種連接方式的總結

在這里插入圖片描述
內連接: inner join , 只獲取兩張表中交集部分的數據.
左外連接: left join , 以左表為基準 ,查詢左表的所有數據, 以及與右表有交集的部分
右外連接: right join , 以右表為基準,查詢右表的所有的數據,以及與左表有交集的部分

子查詢

子查詢的概念：
一條select 查詢語句的結果, 作為另一條 select 語句的一部分

子查詢的特點

子查詢必須在小括號中
子查詢一般作為父查詢的查詢條件使用

子查詢常見分類

where型子查詢：將子查詢的結果，作為父查詢的比較條件
from型子查詢：將子查詢的結果，作為一張表提供給父層查詢使用
exists型子查詢：子查詢的結果是單列多行，類似一個數組，父層查詢使用IN 函數 ,包含子查詢的結果

子查詢的結果作為查詢條件

SELECT 查詢字段 FROM 表 WHERE 字段=（子查詢）;

子查詢的結果作為一張表

SELECT 查詢字段 FROM （子查詢）表別名 WHERE 條件;

-- 1. 先查詢分類表的數據
SELECT * FROM category;
-- 2.將上面的查詢語句 作為一張表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查詢作為一張表使用時 要起別名 才能訪問表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;

子查詢結果是單列多行

SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;

# 查詢家電類 與 鞋服類下面的全部商品信息
-- 先查詢出家電與鞋服類的 分類ID
SELECT cid FROM category WHERE cname IN ('家電','鞋服');
-- 根據cid 查詢分類下的商品信息
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家電','鞋服'));