Neekko33

資料庫操作基礎#

1 - 資料庫的操作#

1.1 顯示資料庫#

show databases;

1.2 創建資料庫#

create database [if not exists] `資料庫名` [字符編碼];

注意：

1. 如果創建的資料庫已存在會報錯，所以創建資料庫時需要先判斷是否存在；
2. 如果資料庫名是關鍵字和特殊字符會報錯，需要在特殊字符、關鍵字行加上反引號；

1.3 刪除資料庫#

drop database [if exists] 資料庫名;

1.4 顯示創建資料庫的 SQL 語句#

show create database 資料庫名;

1.5 修改資料庫#

修改資料庫的字符編碼：

alter database 資料庫名 charset=字符編碼;

1.6 選擇資料庫#

use 資料庫名;

2 - 表的操作#

2.1 顯示所有表#

show tables;

2.2 創建表#

create table [if not exists] 表名(
	字段名 數據類型 [null|not null] [auto_increment] [primary key] [comment]
  字段名 數據類型 [default]
)engine=存儲引擎

創建簡單的表：

create table stu(
	id int,
  name varchar(30)
);

創建複雜的表：

# 如果帶有中文
set names gbk;
# 創建複雜表
create table if not exists teacher(
		id int auto_increment primary key comment '主鍵',
  	name varchar(20) not null comment '姓名',
  	phone varchar(20) comment '電話號碼',
  	address varchar(100) default '地址不詳' comment '地址'
)engine=innodb;

提示： 使用資料庫名.表名可以給其他資料庫創建表

2.3 顯示創建表的語句#

show create table 表名 [\G];

2.4 查看表結構#

desc[ribe] 表名;

2.5 刪除表#

drop table [if exists] 表1,表2,...;

2.6 修改表#

alter table 表名

1. 添加字段

alter table 表名 add [column] 字段名 數據類型 [位置];

位置：

• first：在第一位添加
• after：在 xx 字段後添加

2. 刪除字段

alter table 表名 drop [column] 字段名;

3. 修改字段（名字和類型）

alter table 表名 change 字段名 新字段名 數據類型...

4. 修改字段（不改名）

alter table 表名 modify 字段名 字段屬性...

5. 修改引擎

alter table 表名 engine=引擎名稱;

6. 修改表名

alter table 表名 rename to 新表名;

2.7 複製表#

create table 表名 select 字段 from 舊表

特點：不能複製父表的主鍵，只能複製父表的數據

create table 新表 like 舊表

特點：只能複製表結構，不能複製表數據

3 - 數據操作#

3.1 插入數據#

插入單條數據

insert into 表名 (字段1,字段2,...) values (值1,值2,...);

1. 可以插入部分字段，但是非空字段必須插入
2. 自動增長字段不用插入，資料庫會自動插入增長的數字
3. 自動增長列的值插入 null 即可
4. 插入值的順序和個數與表字段的順序和個數一致，插入的字段可以省略
5. 可以通過 default 插入默認值

插入多條數據

insert into 表名 values (值1,值2,...),(值1,值2,...),...;

3.2 更新數據#

update 表名 set 字段=值 [where 條件]

3.3 刪除數據#

delete from 表名 [where 條件]

3.4 清空表#

truncate table 表名

**delete from 表**和**truncate table 表**區別：
delete from 表：遍歷表記錄，一條一條的刪除；
truncate table 表：將原表摧毀，再創建一個同結構的新表，就清空表而言，這種方法效率高；

3.5 查詢語句（單表查詢）#

語法：select [選項] 列名 [form 表名] [where 條件] [order by 排序] [group by 分組] [having 條件][limit 限制]

3.5.1 字段表達式#

可以通過as來取別名（as 可以省略）

3.5.2 from 語句#

from 後跟數據源，數據源可以放多個，返回笛卡爾積

3.5.3 dual 表#

dual 表是一個虛表。在有些特定情況下，沒有具體的表的參與，但是為了保證 select 語句的完整又必須要一個表名，這時候就使用虛表。

3.5.4 where 子句#

MySQL 支持的運算符：>, <, >=, <=, =, !=, and, or, not
Where 後面跟的是條件語句，返回條件為真的記錄

3.5.5 in|not in#

多個條件查詢可以通過 in 語句 實現：

select * from stu where stuadress in ('北京','上海');

3.5.6 between...and|not between...and#

查找某個範圍的記錄：

select * from stu where stuage between 18 and 20;

3.5.7 is null|is not null#

判斷是否為空：

select * from stu where ch is null;

3.5.8 聚合函數#

1. sum()：求和
2. avg()：求平均數
3. max()：求最大值
4. min()：求最小值
5. count()：記錄值

3.5.9 通配符#

1. _ [下劃線] 表示任意一個字符
2. % 表示任意字符

3.5.10 模糊查詢（like）#

在學生表中查詢姓張的學生：

select * from stu where stuname like '張_'

3.5.11 order by 排序#

• ASC：升序【默認】
• DESC：降序
• 多列排序：

# 年齡升序，成績降序
select *,(ch+math) as '總分' from stu order by stuage asc,(ch+math) desc;

3.5.12 group by [分組查詢]#

將查詢的結果分組，分組查詢目的在於統計數據

# 按照性別分組，顯示每組的平均年齡
select avg(stuage) as '平均年齡',stusex from stu group by stusex;

注意：

1. 如果是分組查詢，查詢字段必須是分組字段和聚合函數；
2. 查詢字段是普通字段，只取分組的第一個值；
3. 可以通過group_concat()函數將同一組的值連接起來顯示
4. 分組以後結構默認按照升序排列，可以使用 desc 實現降序排列（直接加 desc）

多列分組：

# 按照性別和地區分組，顯示每組的平均年齡
select stuaddress,stusex,avg(stuage) as '平均年齡'

3.5.13 having 條件#

where 和 having 的區別：
where 是對原始數據進行篩選，having 是對結果集進行篩選

3.5.14 limit#

語法：limit 起始位置，顯示長度
注意：

1. 起始位置可以不寫，默認從 0 開始
2. limit 可以在 update 和 delete 語句中使用

3.5.15 查詢語句中的選項#

查詢語句中的選項有兩個：

1. all：顯示所有數據【默認】
2. distinct：去除結果集中重複的數據

3.6 查詢語句（多表查詢）#

3.6.1 內連接【inner join】#

# 語法一：
select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段;
# 語法二：
select 列名 from 表1，表2 where 表1.公共字段=表2.公共字段;

注意：如果要顯示公共字段，必須要指定表名

3.6.2 左外連接 【left join】#

以左邊的表為標準，如果右邊的表沒有對應的記錄，就以 null 填充結果

select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段;

3.6.3 右外連接 【right join】#

以右邊的表為標準，如果左邊的表沒有對應的記錄，就以 null 填充結果

select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段;

3.6.4 交叉連接 【cross join】#

1. 如果沒有連接表達式返回的是笛卡爾積
2. 如果有連接表達式等價於內連接

3.6.5 自然連接 【natural】#

自動的判斷連接條件，它是通過同名字段來判斷
自然連接分為：

1. 自然內連接：natural join
2. 自然左外連接：natural left join
3. 自然右外連接：natural right join

自然連接結論：

1. 表連接通過同名的字段來連接
2. 如果沒有同名的字段返回笛卡爾積
3. 會對結果進行整理，整理的規則如下
   • 連接字段保留一個
   • 連接字段放在最前面

3.6.6 using()#

1. 同來指定連接字段
2. using()也會對連接字段進行整理，整理規則與自然連接相同

3.7 子查詢#

3.7.1 只有一個返回值#

語法：

select 語句 where 條件 (select ... from 表)

1. 外面的查詢稱為父查詢，括號中的查詢稱為子查詢
2. 子查詢為父查詢提供查詢條件
3. =只適用於子查詢返回一個值的情況

3.7.2 in|not in 子查詢#

用於子查詢返回多個值的情況

3.7.3 exists|not exists#

# 如果有學生筆試成績大於80就顯示所有學生
select * from stuinfo where exists(select * from stumarks where writtenexam>=80);

3.7.4 子查詢分類#

1. 標量子查詢：子查詢返回的結果就一個
2. 列子查詢：子查詢返回的結果是一個列表
3. 行子查詢：子查詢返回的結果返回一行

• 例題：查詢成績最高的男生和女生

select stuname,stusex,ch from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);

4. 表子查詢：子查詢返回的結果當成一個表

• 注意：如果把子查詢當成表來看待，必須取一個別名

3.8 union#

3.8.1 union 的使用#

作用：將多個 select 語句結果集縱向聯合起來

select 語句 union [選項] select 語句 union [選項] select 語句

3.8.2 union 的選項#

1. all：顯示所有數據
2. distinct：去除重複的數據【默認】

3.8.3 union 的注意事項#

1. union 兩邊的 select 語句的字段個數必須一致
2. union 兩邊的 select 語句的字段名可以不一致，最終按第一個 select 語句的字段名
3. union 兩邊的 select 語句中的數據類型可以不一致

4 - SQL 分類#

DDL（Data Definition Language）資料庫定義語言 CREATE、ALTER、DROP
DML（Data Manipulation Language）數據操縱語言 SELECT、UPDATE、INSERT、DELETE
DCL（Data Control Language）資料庫控制語言，是用來設置或更改資料庫用戶或角色權限的語句

5 - 數據表的文件介紹#

• 一個資料庫對應一個文件夾
• 一個表對應一個或多個文件
  • myisam：一個表對應三個文件（表結構，表數據，表索引）
  • innodb：一個表對應一個表結構文件

6 - 字符集#

字符集：字符在保存和傳輸時對應的二進制編碼合集。

set names 字符集編碼

列屬性與數據完整性#

1 - 數據類型#

1.1 數據類型 —— 值類型#

1.1.1 整形

注意：

1. 無符號整數（unsigned）沒有負數，整數部分是有符號的兩倍
2. 整型支持顯示寬度（最小顯示位數），比如int(5)，如果數值的位數小於 5 位，前面加上前導 0.（結合zerofill才起作用）

1.1.2 浮點型（保存近似值小數)

注意：

1. 浮點數聲明：float(M,D) double(M,D)
   • M：總位數
   • D：小數位數
2. 浮點的精度可能會丟失

1.1.3 定點數
語法：decimal(M, D)
注意：

1. 定點數是變長的，大致每 9 個數字用 4 個字節來存儲。定點數之所以能保存精確的小數，是因為整數和小數是分開存儲的，占用的資源比浮點數要多。
2. 定點數和浮點數都支持顯示寬度和無符號數。

1.2 數據類型 —— 字符型#

注意：

1. char(10)和varchar(10)的區別？

   相同點：它們最多只能保存 10 個字符；
   不同點：char 不回收多餘的字符，varchar 會回收多餘的字符；char 效率高，浪費空間，varchar 節省空間，效率比 char 低。

2. char 最大長度 255

3. varchar 理論長度 65535

1.3 數據類型 —— 枚舉（enum）#

特點：

1. MySQL 的枚舉類型是通過整數來管理的，第一個值是 1，第二個值是 2，以此類推；
2. 枚舉類型可以直接插入數字

優點：

1. 運行速度快（數字比字符串運算快）
2. 限制數據，保證數據完整性
3. 節省空間

1.4 數據類型 —— 集合（set）#

1. 集合中插入順序不一樣，但是顯示的順序都是一樣的
2. 不能插入集合中沒有的數據
3. 集合的每個元素都被分配一個固定數字，從左往右按 20, 21,22... 排列

1.5 數據類型 —— 日期時間#

1.6 數據類型 ——boolean#

true 和 false 在資料庫中對應 1 和 0

資料庫設計#

1 - 資料庫基本概念#

1. 關係：兩個表的公共字段
2. 行：也稱記錄，也稱實體
3. 列：也稱字段，也稱屬性
4. 數據冗餘：相同的數據存儲在不同的地方
5. 數據完整性：正確性 + 準確性 = 數據完整性
   • 正確性：數據類型正確
   • 準確性：數據範圍要準確

2 - 實體和實體之間的關係#

2.1 一對一#

1. 主表中的一條記錄對應從表中的一條記錄
2. 實現：主鍵和主鍵建立關係
3. 問題：一對一兩個表完全可以用一個表實現，為什麼要分成兩個表？
   • 在字段數量很多的情況下，每次查詢需要檢索大量數據，這樣效率低下。所以可以將所有字段分為 “常用字段” 和 “不常用字段”，這樣對大部分查詢者來說效率提高了。【表的垂直分割】

2.2 一對多#

1. 主表中的一條記錄對應的從表中的多條記錄
2. 實現：主鍵和非主鍵建立關係

2.3 多對多#

1. 主表中的一條記錄對應從表中的多條記錄，從表中的一條記錄對應主表中的多條記錄
2. 實現：建立第三張表來保存關係

3 - 資料庫設計的步驟#

3.1 資料庫設計具體步驟#

1. 收集信息：與該系統有關人員進行交流、座談，充分理解資料庫需要完成的任務
2. 標識對象（實體 - Entity）標識資料庫要管理的關鍵對象或實體
3. 標識每個實體的屬性（Attribute）
4. 標識對象之間的關係（Relationship）
5. 將模型轉換成資料庫
6. 規範化

3.2 繪製 E-R 圖（實體關係圖）#

E-R 圖的語法

3.3 將 E-R 圖轉成表#

4 - 數據規範化#

實踐證明，三範式是性價比最高的。

4.1 第一範式：確保每列原子性#

如果每列都是不可再分的最小數據單元（也稱為最小的原子單元），那麼滿足第一範式。
注意：地址包含省、市、縣、地區是否需要拆分？

• 如果僅僅起地址的作用，不需要統計，可以不拆分；如果有按地區統計的功能，那麼需要拆分。
• 在實際項目中，建議拆分

4.2 第二範式：非鍵字段必須依賴於鍵字段#

如果一個關係滿足第一範式，並且除了主鍵意外的其他列，都依賴於該主鍵，則滿足第二範式。
注意：第二範式要求每個表只描述一件事情

4.3 第三範式：消除傳遞依賴#

如果一個關係滿足第二範式，並且除了主鍵以外的其他列都不傳遞依賴於主鍵列，則滿足第三範式。
注意：傳遞依賴不能存在於非鍵字段中

視圖#

1. 視圖是一張虛擬表，它表示一張表的部分或多張表的綜合的結構
2. 視圖僅僅是表結構，沒有表數據。視圖的結構和數據建立在表的基礎上。

1 創建視圖#

語法：

create [or replace] view 視圖的名稱 as select語句

注意：因為視圖是一個表結構，所以創建視圖後，會在資料庫文件夾中創建一個視圖名的 frm 文件

2 查看視圖的結構#

desc 視圖名;

3 查看創建視圖的語法#

show create view 視圖名\G;

4 顯示所有視圖#

show tables;

select table_name from information_schema.views;

show table status where comment='view'\G;

5 更改視圖#

alter view 視圖名 as select語句;

6 刪除視圖#

drop view [if exists] 視圖1,視圖2...;

7 視圖的作用#

1. 篩選數據，防止未經許可訪問敏感數據
2. 隱藏表結構
3. 降低 SQL 語句的複雜度

8 視圖的算法#

場景：找出語文成績最高的男生和女生

select * from (select * from stu order by ch desc)as t group by stusex;

我們可以將子查詢封裝到視圖中：

create view vw_stu as select * from stu order by ch desc;

通過視圖來查詢：

select * from vm_stu group by stusex;

但是結果和上面直接查詢的結果不一樣，這是因為視圖的算法造成的：

1. merge：合併算法，將視圖的語句和外層的語句合併後在執行。
2. temptable：臨時表算法，將視圖生成一個臨時表，再執行外層語句。
3. undefined：未定義，MySQL 到底用哪種算法由 MySQL 決定，一般視圖會自動使用 merge 算法，因為效率更高。

** 解決：** 在創建視圖的時候指定算法

create algorithm=temptable view 視圖名 as select語句;

事務【transaction】#

1. 事務是一個不可分割的執行單元
2. 事務作為一個整體要麼一起執行，要麼一起回滾

1 事務操作#

# 開啟事務
start transaction 或者 begin [work]
# 提交事務
commit
# 回滾事務
rollback

例子：銀行轉帳

start transaction
update bank set money=money-100 where cardid='1001'
update bank set money=money+100 where cardid='1002';
# 如果有報錯：
rollback;
# 如果成功：
commit;

注意：

1. 事務在開啟的時候產生，提交事務或回滾事務都結束；
2. 只有 innodb 和 BDB 才支持事務，myisam 不支持事務；

2 設置事務的回滾點#

語法：

# 設置回滾點
savepoint 回滾點名
# 回滾到回滾點
rollback to 回滾點

2.3 事務的特性（ACID）#

1. 原子性（Atomicity）：事務是一個整體，不可以再分，要麼一起執行，要麼一起不執行；
2. 一致性（Consistency）：事務完成時，數據必須處於一致的狀態；
3. 隔離性（Isolation）：每個事務都是相互隔離的；
4. 永久性（Durability）：事務完成後，對數據的修改是永久性的；

索引#

優點：查詢速度快
缺點：

1. 數據操作語句效率降低
2. 占用空間

1 索引的類型#

1. 普通索引
2. 唯一索引（唯一鍵）
3. 主鍵索引：只要創建主鍵就自動創建主鍵索引，不需要手動創建
4. 全文索引，搜索引擎使用，MySQL 不支持中文的全文索引，我們通過 sphinx 解決中文的全文索引

2 創建普通索引#

# 創建索引方法一
create index 索引名 on 表名(字段名);
# 創建索引方法二
alter table 表名 add index [索引的名稱](列名);
# 創建表的時候就添加索引
create table emp(
	id int,
    name varchar(10),
    index ix_name(name) # 創建索引
);

3 創建唯一索引#

語法一：create unique index 索引名 on 表名(字段名);
語法二：alter table 表名 add unique [index] [索引的名稱](列名);
語法三：創建表的時候添加唯一索引，和創建唯一鍵是一樣的

4 刪除索引#

drop index 索引名 on 表名;

5 創建索引的指導原則#

1. 該列用於頻繁搜索；
2. 該列用於排序；
3. 公共字段要創建索引；
4. 如果表中的數據很少，不需要創建索引。MySQL 搜索索引的時間比逐條搜索數據的時間要長；
5. 如果一個字段上的數據只有一個不同的值，該字段不適合做索引，比如性別；

內置函數#

1 數字類#

# 生成隨機數
select rand();
select * from stu order by rand() limit 2; # 隨機抽取兩個學生

# 取整（四捨五入，向上，向下）
select round(數字);
select ceil(數字);
select floor(數字);

# 截取數字
select truncate(數字，截取小數位數);

2 字符串類#

# 轉成大寫/小寫
select ucase/lcase(字符串);

# 截取字符串
select left/right(字符串,位數);
select substring(字符串，開始位數【從1開始】，位數);

# 字符串拼接
select concat(字符串...);

# 字符串替換
coalesce(字段1，字段2); # 如果字段不為空就顯示字段1，否則顯示字段2
select stuname,coalesce(writtenexam.'缺考') from stuinfo natural left join stumarks; # 如果筆試成績為空就顯示‘缺考’

# 字符串長度
select length(字符串); # 字節長度
select char_length(字符串); # 字符個數

3 時間類#

# 獲取當前的時間戳
select unix_timestamp();

# 時間戳轉換為時間格式
select from_unixtime(unix_timestamp());

# 獲取當前時間
select now();

# 日期相減
select datediff(now(),'2008-8-8') # 當前日期距離2008-08-08共多少天

# 轉換格式
select cast(now() as time)
select convert(now(),time)

4 加密函數#

select md5(字符串);
select sha(字符串);

5 判斷函數#

select stuname,if(ch>=60 && math>=60,'通過','不通過') '是否通過' from stu;

預處理#

預編譯一次，可以多次執行。用來解決一條 SQL 語句頻繁執行的問題

# 預處理語句
prepare 預處理名字 from 預處理語句;

# 執行預處理
execute 預處理名字 [using 變量];

# MySQL中聲明變量
set @id=值

存儲過程【procedure】#

1 存儲過程的優點#

1. 存儲過程可以減少網絡流量
2. 允許模塊化設計
3. 支持事務

2 創建存儲過程#

create procedure 存儲過程名(參數)
	begin
	sql...
	end;

3 調用存儲過程#

call 存儲過程名

4 刪除存儲過程#

drop procedure [if exists] 存儲過程名

5 查看存儲過程的信息#

show create procedure 存儲過程名\G

6 顯示所有的存儲過程#

show procedure status\G

7 存儲過程的參數#

存儲過程的參數分為：輸入參數（in）【默認】，輸出參數（out），輸入輸出參數（inout）
存儲過程不能使用 return 返回值，要返回值只能通過 “輸出參數” 來向外傳遞值。
例 1：輸入參數

create procedure proc(in param varchar(10))
select * from stuinfo where stuno=param;

call proc('s25301');

例 2：查找同桌

create procedure proc(in name varchar(10))
begin
declare seat tinyint
select stuseat into seat from stuinfo where stuname=name
select * from stuinfo where stuseat=seat+1 or stuseat=seat-1
end;

總結：

1. 透過 declare 關鍵字聲明局部變量；全局變量 @開頭就可以了
2. 給變量賦值有兩種方法
   方法一：set 變量名 = 值
   方法二：select 字段 into 變量 from 表 where 條件
3. 聲明的變量不能與列名同名

例三：輸出參數

create procedure proc(num int, out result int)
begin
set result=num*num
end;

call proc(10.@result);

select @result;

例四：輸入輸出參數

create procedure proc(inout num int)
begin
set num=num*num
end;

set @num=10

call proc(@num);

select @num;