MySQL-初見

ML李嘉圖發表於2021-08-12

MySql

初識MySQL

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。

MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體之一。

關係型資料庫(SQL)

MySQL,Oracle,DB2
通過表與表之間,行與行之間的關係進行儲存

非關係型資料庫(NoSQL)

Redis,MongDB
非關係型資料庫,物件儲存

資料庫基本命令

--連線資料庫
mysql -u root -p [密碼] 

--查詢所有資料庫
show databases;

--切換資料庫
user [資料庫名];

--檢視所有表
show tables;

--顯示錶資訊
describe [表名];

--建立資料庫
create database [資料庫名];

--檢視建立資料庫的語句
show create database school;

--檢視建立表的定義語句
show create table student;

--檢視錶結構
desc student;

--檢視SQL執行的狀況EXPLAIN
EXPLAIN select * from user

--運算元據庫
--運算元據庫> 運算元據庫中的表>操作表中的資料
MySQL中的SQL語句不區分大小寫

--建立資料庫
create database [if not exists] westos;

--刪除資料庫
drop database [if exists] student;

--使用資料庫
--如果表名或庫名是一個特殊字元,就需要帶 ``符
user `westos`;

建立資料庫表

整數:
tinyint 十分小的資料 1個位元組
smallint 較小資料 2個位元組
mediumint 中等大小的資料 2個位元組
int 標準的整數 4個位元組
bigint 較大的資料 8個位元組

浮點數:
float 浮點數 4個位元組
double 浮點數 8個位元組(精度問題)

金融計算的時候一般使用decimal:
decimal 字串形式的浮點數

字串
char 字串固定大小 0~255
varchar 可變字串 0~65535 常用 String
tinytext 微型文字 2^8~1
text 文字串 2^16~1 保持大文字

時間格式
date YYYY-MM-DD 日期格式
time HH:mm:ss 時間格式
datetime YYYY-MM-DDHH:mm:ss 最常用的時間格式
timestamp 時間戳 1970.1.1 到現在的毫秒數
year 年份表示

null
沒有值未知
不要使用null進行運算

建立資料庫表完整語句

CREATE 	TABLE IF NOT EXISTS `t_te_user`(
      `id` INT(4) NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(30) NOT NULL ,   
      `pwd` VARCHAR(20) NOT NULL ,   
      `sex` VARCHAR(30) NOT NULL ,     
     `address` VARCHAR(100) DEFAULT NULL,
      PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

--格式
CREATE 	TABLE [IF NOT EXISTS] `表名`(
        `欄位名` 列型別 [屬性] [索引] [註釋],
        `欄位名` 列型別 [屬性] [索引] [註釋],
        `欄位名` 列型別 [屬性] [索引] [註釋],
        `欄位名` 列型別 [屬性] [索引] [註釋],
          
)[表型別][字符集][註釋]

--設定資料庫表的字符集編碼

CHARSET=utf8

不設定的話,會是mysql預設的字符集編碼,不支援中文!

注意點

欄位名儘量使用``符號包裹
註釋使用--符號
SQL大小寫不敏感,但是建議寫小寫
所有的標點符號,使用英文

資料庫引擎

	InnoDB	MYISAM
事務支援	支援	不支援
資料行鎖定	支援	不支援
外來鍵約束	支援	不支援
全文索引	不支援	支援
表空間的大小	較大,約為2倍	較小

MYISAM:節約空間,速度最快

InnoDB:安全性高,事務的處理,多表多使用者操作

在物理空間的位置

所有的資料庫檔案都存放在data目錄下,一個資料夾就代表一個資料庫

Windows預設安裝目錄在C:\Program Files\

本質還是檔案儲存

MySQL引擎在屋裡檔案上的區別:

InnoDB 在資料庫表中只有一個*.frm檔案,以及上級目錄下的idbdata1檔案
MYISAM 對應的檔案: *.frm-表結構的定義檔案, .MYD-資料檔案(data),.MYI-索引檔案(index)

修改和刪除表欄位

--修改表名

ALTER TABLE [舊錶名] RENAME AS [新表名]

--新增欄位

ALTER TABLE [表名] ADD [欄位名][型別]

--修改欄位約束(如:int變成varchar)

ALTER TABLE [表名] MODIFY [欄位名][欄位約束]

--欄位重新命名

ALTER TABLE [表名] CHANGE [舊欄位名][新欄位名]



--最終結論: change用來欄位重新命名，不能修改欄位型別和約束;

--modify不用來欄位重新命名，只能修改欄位型別和約束;



--刪除

--刪除欄位

ALTER TABLE [表名] DROP [欄位名]


--刪除表

DROP TABLE IF EXISTS [表名]


--所有的刪除和修改儘量都加上判斷(`if exists`),避免報錯

MySQL資料管理

外來鍵

概念

如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外來鍵。

由此可見，外來鍵表示了兩個關係之間的相關聯絡。以另一個關係的外來鍵作主關鍵字的表被稱為主表，具有此外來鍵的表被稱為主表的從表。

在實際操作中，將一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值(在必要時可包括複合主鍵值)。此時，第二個表中儲存這些值的屬性稱為外來鍵(foreign key)。

建表時指定外來鍵約束

-- 建立外來鍵的方式一 : 建立子表同時建立外來鍵

-- 年級表 (id\年級名稱)
CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年級ID',
`gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 學生資訊表 (學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '學號',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性別',
`gradeid` INT(10) DEFAULT NULL COMMENT '年級',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手機',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


-- 建立外來鍵方式二 : 建立子表完畢後,修改子表新增外來鍵
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);


--注意 : 刪除具有主外來鍵關係的表時 , 要先刪子表 , 後刪主表
-- 刪除外來鍵
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 發現執行完上面的,索引還在,所以還要刪除索引
-- 注:這個索引是建立外來鍵的時候預設生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上內容都是物理外來鍵

資料庫級別的外來鍵,不建議使用,避免資料庫過多造成困擾

以後使用外來鍵都是在應用層實現(程式碼實現)

DML語言

--INSERT新增詳解

--插入命令格式:

insert into [表名]([欄位名],[欄位名]) values([值],[值])

--注意事項:

--一般寫插入語句,欄位和資料一定要一一對應

--欄位或值之間用英文逗號隔開

--’ 欄位1,欄位2…’ 該部分可省略 , 但新增的值務必與表結構,資料列,順序相對應,且數量一致 .

--可同時插入多條資料 , values 後用英文逗號隔開



--UPDATE修改詳解

--修改命令格式:

 UPDATE [表名] SET [欄位名]=[修改值] WHERE `id`='4';

--注意:
--where語句之後為篩選條件 , 如不指定則修改該表的所有列資料



--DELETE刪除詳解

--刪除命令格式:

DELETE FROM 表名 [WHERE 條件匹配];

--注意：

--where後為篩選條件 , 如不指定則刪除該表的所有列資料



--TRUNCAT刪除詳解

--刪除命令格式:

TRUNCATE TABLE [表名];

--作用：用於完全清空表資料 , 但表結構 , 索引 , 約束等不變 ;

--注意：區別於DELETE命令

--相同 :

--都能刪除資料 , 不刪除表結構 , 但TRUNCATE速度更快
--不同 :

--使用TRUNCATE TABLE 重新設定AUTO_INCREMENT計數器,自增會歸零

--使用TRUNCATE TABLE不會對事務有影響

瞭解即可:「DELETE刪除的問題，重啟資料庫，現象
●InnoDB 自增列會重1開始(存在記憶體當中的, 斷電即失)
●MyISAM 繼續從上一一個自增量開始(存在檔案中的，不會丟失)

DQL查詢資料

DQL( Data Query Language 資料查詢語言 )

查詢資料庫資料 , 如SELECT語句
簡單的單表查詢或多表的複雜查詢和巢狀查詢
是資料庫語言中最核心,最重要的語句
使用頻率最高的語句

語法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 聯合查詢
  [WHERE ...]  -- 指定結果需滿足的條件
  [GROUP BY...]  -- 指定結果按照哪幾個欄位來分組
  [HAVING]  -- 過濾分組的記錄必須滿足的次要條件
  [ORDER BY  ...]  -- 指定查詢記錄按一個或多個條件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查詢的記錄從哪條至哪條

單表查詢

--查詢表的全部資訊

select * from [表名]

--查詢指定欄位資訊

select [欄位],[欄位] from [表名]

--查詢指定欄位並取別名

select [欄位] as [別名],[欄位] as [別名] from [表名]

--字串拼接函式 Concat(a,b)

select concat('姓名:',[欄位]) from [表名]

--去除重複資料 關鍵字:DISTINCT

select DISTINCT [欄位名]from [表名]

--其他查詢

select version() --查詢系統版本
select 100*3-1 as --計算結果
select @@auto_increment_increment --查詢自增步長

--學員考試成績集體提分一分檢視
SELECT studentno,StudentResult+1 AS '提分後' FROM result;

--資料庫中的表示式 : 一般由文字值 , 列值 , NULL , 函式和操作符等組成

WHERE條件子句

可以簡單的理解為 : 有條件地從表中篩選資料

搜尋條件可由一個或多個邏輯表示式組成 , 結果一般為真或假

--查詢id等於1的使用者

select * from user where id=1;

--查詢id等於1並且姓名是金角大王的使用者

select * from user where id=1 and name='金角大王';

--查詢id=1 或者 id=2的使用者

select * from user where id=1 or id=2;;

模糊查詢:比較操作符

注意：

數值資料型別的記錄之間才能進行算術運算 ;
相同資料型別的資料之間才能進行比較 ;

IS NULL
--查詢email為空的使用者

select * from user where email is null;

IS NOT NULL
--查詢email不為空的使用者

select * from user where email is not null;

BETWEEN
--區間模糊查詢between and 查詢id大於4小於50的使用者

select * from user where id between 4 and 50

LIKE
--一般搭配萬用字元%使用

--查詢姓名是金字開頭的使用者

select * from user where name like '金';

--查詢名字中間有佳字的使用者

select * from user where name like '%佳%';

IN
--匹配多個結果 具體的一個或多個值

--查詢id=1,2,3的使用者

select * from user where id in (1,2,3);

聯表查詢

JOIN

連線查詢

如需要多張資料表的資料進行查詢,則可通過連線運算子實現多個查詢

內連線 inner join
查詢兩個表中的結果集中的交集

格式:

select a.*,b.* from user a 
inner join student b on a.id=b.id;

左外連線 left join
以左表作為基準,右邊表來一一匹配,匹配不上的,返回左表的記錄,右表以NULL填充

格式:

select a.*,b.* from user a 
left join student b on a.id=b.id;

右外連線 right join
以右表作為基準,左邊表來一一匹配,匹配不上的,返回右表的記錄,左表以NULL填充

格式:

select a.*,b.* from user a 
right join student b on a.id=b.id;

等值連線

SELECT s.*,r.*
FROM `user` s , `student` r
WHERE r.studentno = s.studentno

自連線

資料表與自身進行連線

將一張表拆成兩張表

自連線
資料表與自身進行連線
將一張表拆成兩張表

-- 建立一個表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入資料
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','資訊科技'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');

--查詢方式一
select a.categoryName as '父',b.categoryName as'子' 
from category as a,category as b
where a.categoryid=b.pid;
--查詢方式二
select a.categoryName as '父',b.categoryName as'子' 
from category as a
inner join category as b on a.categoryid=b.pid;

等值連線中不要求屬性值完全相同，而自然連線要求兩個關係中進行比較的必須是相同的屬性組（屬性名可以不同），即要求必須有相同的值域。
等值連線不將重複屬性去掉，而自然連線去掉重複屬性，也可以說，自然連線是去掉重複列的等值連線（如圖所示）

排序查詢ORDER BY

排序查詢和分頁查詢的關鍵字在整個SQL語句的最後面,其語法位置是固定不變的,不能放在where或者having的前面

升序:ASC

語法:

select * from [表名]
order BY id asc

降序:BESC

語法:

select * from [表名]
order BY id desc

分組查詢GROUP by

語法:

select * from user
GROUP by [分組的欄位]

分組查詢之後使用HAVING來過濾分組之後的資料

select * from user
GROUP  by sex
HAVING age>20

分頁查詢Limit

語法:

select * from [表名]
limit [起始值],[每頁大小]

查詢規律可以推匯出計算每頁的公式:
第一頁 : limit 0,5 公式:(1-1)*5
第二頁 : limit 5,5 公式:(2-1)*5
第三頁 : limit 10,5 公式:(3-1)*5
…
第N頁 : (N-1)*頁面大小
limit (pageNo-1)*pageSzie,pageSzie
[pageNo:頁碼,pageSize:單頁面顯示條數]

巢狀查詢(子查詢)

在查詢語句中的WHERE條件子句中,又巢狀了另一個查詢語句
巢狀查詢可由多個子查詢組成,求解的方式是由裡及外;
子查詢返回的結果一般都是集合,故而建議使用IN關鍵字;

語法:

select * from [表名] 
where id in ( select id from [表名] )

MySQL常用函式

常用函式

數學函式

select abs(-8) --絕對值
select CEILING(9.4); --向上取整
select RAND(); --返回隨機數
select SIGN(0); --符號函式:負數返回-1 正數返回1 0返回0

字串函式

select CHAR_LENGTH('你就是根基吧'); --字串長度
select CONCAT('鄭','在','稿');--合併字串
select INSERT('你就是根基吧',4,2,'鄭在稿'); --替換字串
select LOWER("SKSSSS");--小寫
select UPPER('ssssss'); --大寫
select LEFT('hello,word',5); --從左邊擷取
select RIGHT('hello,word',5); --從右邊擷取
select REPLACE('狂神說堅持就能成功','堅持','成功');--替換字串
select SUBSTR('狂神說堅持就能成功',4,6);--擷取字串
select REVERSE('狂神說堅持就能成功'); --反轉

時間日期函式

--日期和時間函式
select CURRENT_DATE();--獲取當前日期
select CURDATE();--獲取當前日期
select NOW();--獲取當前日期和時間
select LOCALTIME();--獲取當前日期和時間
select SYSDATE();--獲取當前日期和時間

--獲取年月日時分秒
select YEAR(NOW());
select MONTH(NOW());
select DAY(NOW());
select HOUR(NOW());
select MINUTE(NOW());
select SECOND(NOW());

聚合函式

函式名稱 描述 :
~
COUNT() 返回滿足Select條件的記錄總和數，如 select count(*) 【不建議使用 *，效率低】
~
SUM() 返回數字欄位或表示式列作統計，返回一列的總和。
~
AVG() 通常為數值欄位或表達列作統計，返回一列的平均值
~
MAX() 可以為數值欄位，字元欄位或表示式列作統計，返回最大的值。
~
MIN() 可以為數值欄位，字元欄位或表示式列作統計，返回最小的值。

COUNT()

格式:

select count([欄位]) from [表名] --會忽略所有null
select count(*) from [表名] --不會忽略null
select count(1) from [表名] --不會忽略null

一般情況下count(1)比count(*)速度快

其他聚合函式

格式:

select sum(id) from user --求和
select AVG(id) from user --平均分
select MIN(id) from user --最小分
select MAX(id) from user --最高分

資料庫事務

事務就是將一組SQL語句放在同一批次內去執行
如果一個SQL語句出錯,則該批次內的所有SQL都將被取消執行
MySQL事務處理只支援InnoDB和BDB資料表型別

ACID

原子性(Atomic)

要麼全部成功,要麼全部失敗



一致性(Consist)

事務執行前後資料的完整性要保持一致



隔離性(Isolated)

事務在併發訪問的時候,為每個使用者開啟單獨事務,事務與事務之間要保持隔離



永續性(Durable)

事務一旦提交便不可逆

隔離導致的問題

髒讀

一個事務讀取了另一個沒有提交的事務

事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料是髒數

幻讀

在同一個事務內,讀取到了別人插入的資料,導致前後讀出來結果不一致

不可重複讀

在同一個事務內,重複讀取表中的資料,表資料發生了改變

事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果不一致。

基本語法

-- 使用set語句來改變自動提交模式
SET autocommit = 0;   /*關閉*/
SET autocommit = 1;   /*開啟*/

-- 注意:
--- 1.MySQL中預設是自動提交
--- 2.使用事務時應先關閉自動提交

-- 開始一個事務,標記事務的起始點
START TRANSACTION  

-- 提交一個事務給資料庫
COMMIT

-- 將事務回滾,資料回到本次事務的初始狀態
ROLLBACK

-- 還原MySQL資料庫的自動提交
SET autocommit =1;

-- 儲存點
SAVEPOINT 儲存點名稱 -- 設定一個事務儲存點
ROLLBACK TO SAVEPOINT 儲存點名稱 -- 回滾到儲存點
RELEASE SAVEPOINT 儲存點名稱 -- 刪除儲存點

索引

MySQL官方對索引的定義為: 索引(index)是幫助MySQL高效獲取資料的資料結構

顯示錶的索引資訊

show index from [表名]
1

建立索引

create index [索引名] on [表]([欄位])

索引分類

主鍵索引 (Primary Key)

主鍵 : 某一個屬性組能唯一標識一條記錄

特點 :

最常見的索引型別
確保資料記錄的唯一性
確定特定資料記錄在資料庫中的位置

ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 該語句新增一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。

唯一索引 (Unique Key)

作用 : 避免同一個表中某資料列中的值重複

與主鍵索引的區別:

主鍵索引只能有一個
唯一索引可能有多個

ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
--這條語句建立索引的值必須是唯一的。

常規索引 (Index)

作用 : 快速定位特定資料

注意 :

index 和 key 關鍵字都可以設定常規索引
應加在查詢找條件的欄位
不宜新增太多常規索引,影響資料的插入,刪除和修改操作

CREATE TABLE `result`(
   -- 省略一些程式碼
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 建立表時新增
)

ALTER TABLE tbl_name ADD INDEX index_name (col_list);
--新增普通索引，索引值可出現多次。

全文索引 (FullText)

作用 : 快速定位特定資料

1
2
全文索引 (FullText)
作用 : 快速定位特定資料

注意 :

只能用於MyISAM型別的資料表
只能用於CHAR , VARCHAR , TEXT資料列型別
資料量小的情況下全文索引不生效
適合大型資料集

-- 建立後新增全文索引
ALTER TABLE [表名] ADD FULLTEXT INDEX [索引名]([列名]);

--全文索引的使用:

select * from student where MATCH([全文索引列名]) AGAINST ([查詢的內容])

強制索引

如果查詢優化器忽略索引，您可以使用FORCE INDEX提示來指示它使用索引。

1
強制索引
如果查詢優化器忽略索引，您可以使用FORCE INDEX提示來指示它使用索引。

以下說明了FORCE INDEX提示語法：

SELECT * 
FROM table_name 
FORCE INDEX (index_list)
WHERE condition;

索引原則

索引不是越多越好
不要對經常變動的資料加索引
小資料量的表建議不要加索引
索引一般應加在查詢條件的欄位

索引資料結構

hash型別的索引：查詢單條快，範圍查詢慢

btree型別的索引：b+樹，層數越多，資料量指數級增長（我們就用它，因為innodb預設支援它）

索引刪除方法

DROP INDEX index_name ON tbl_name;
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

許可權管理

建立使用者命令格式:

CREATE USER [使用者名稱] IDENTIFIED BY '[密碼]'

修改當前使用者:

set password=password('[密碼]')

修改指定使用者密碼:

set password for [使用者名稱]=password('[密碼]')

重新命名

rename user [舊使用者名稱] to [新使用者名稱]

賦予使用者全部許可權

--使用者授權ALL PRIVILEGES全部的許可權,庫.表 on to 使用者名稱
-- 除了給別的使用者授權的許可權沒有 其他都能做
GRANT ALL PRIVILEGES ON *.* TO [使用者名稱]

查詢使用者許可權

show GRANTS for [使用者] -- 普通使用者
show GRANTS for root@localhost -- 檢視root使用者許可權

撤銷使用者許可權

REVOKE ALL PRIVILEGES ON *.* from [使用者名稱]

刪除使用者

DROP [使用者名稱]

資料庫備份

保證重要資料不丟失
資料轉移

MySQL資料庫備份方法:

mysqldump命令列匯出

mysqldump -u使用者名稱 -p密碼 庫名 表名 > 檔名(D:/a.sql)

-- 匯出
1. 匯出一張表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u使用者名稱 -p密碼 庫名 表名 > 檔名(D:/a.sql)
2. 匯出多張表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u使用者名稱 -p密碼 庫名 表1 表2 表3 > 檔名(D:/a.sql)
3. 匯出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u使用者名稱 -p密碼 庫名 > 檔名(D:/a.sql)
4. 匯出一個庫 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u使用者名稱 -p密碼 -B 庫名 > 檔名(D:/a.sql)

資料匯入:

命令列匯入

2. 在登入mysql的情況下：-- source D:/a.sql
　　source 備份檔案
3. 在不登入的情況下
　　mysql -u使用者名稱 -p密碼 庫名 < 備份檔案

資料庫設計三大正規化

第一正規化 (1st NF)

第一正規化的目標是確保每列的原子性,如果每列都是不可再分的最小資料單元,則滿足第一正規化

第二正規化(2nd NF)

第二正規化（2NF）是在第一正規化（1NF）的基礎上建立起來的，即滿足第二正規化（2NF）必須先滿足第一正規化（1NF）。

第二正規化要求每個表只描述一件事情

第三正規化(3rd NF)

如果一個關係滿足第二正規化,並且除了主鍵以外的其他列都不傳遞依賴於主鍵列,則滿足第三正規化.

第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

規範化和效能的關係

關聯查詢的表不得超過三張表

為滿足某種商業目標 , 資料庫效能比規範化資料庫更重要(成本,使用者體驗)
在資料規範化的同時 , 要綜合考慮資料庫的效能
通過在給定的表中新增額外的欄位,以大量減少需要從中搜尋資訊所需的時間(增加冗餘

JDBC

建立專案匯入依賴，寫程式碼

/**
 * 測試JDBC程式
 */
@SpringBootTest
public class JdbcFirstDemoTest {

    @Test
    public void TestJDBC() throws Exception {
        
        //載入驅動
        Class.forName("com.mysql.jdbc.Driver");//固定寫法
        //使用者資訊和url
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        //連線成功和資料庫物件
        Connection connection = DriverManager.getConnection(url, username, password);
        //執行SQL物件
        Statement statement = connection.createStatement();

        //使用物件去執行SQL 存在結果 返回物件
        ResultSet resultSet = statement.executeQuery("select * from user");

        //遍歷結果
        while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex"));
        }
        //釋放連線
        resultSet.close();
        statement.close();
        connection.close();
    }
}

connection物件詳解

connection 代表資料庫,資料庫相關操作都在這裡面.

如:設定事務自動提交,事務開啟,回滾等等

connection.setAutoCommit(false);//關閉自動提交
connection.commit();//提交
connection.rollback();//回滾

statement物件詳解

statement.execute("");//執行所有型別的SQL
statement.executeQuery("");//執行查詢SQL
statement.executeUpdate("");//新增 修改  刪除

resultSet結果集詳解

獲取指定型別的資料

resultSet.getObject();//不知道欄位型別時使用
resultSet.getDate();//欄位是時間型別
resultSet.getString();//欄位是varchar
resultSet.getInt();
resultSet.getBoolean();

遍歷,指標

resultSet.next();//指標下移一位
resultSet.beforeFirst();//指標移到最前面
resultSet.afterLast();//指標移到最後面
resultSet.previous();//指標上移一位
resultSet.absolute(1);//指標移動到指定位置

編寫JDBC工具類

/**
 * JDBC工具類
 */
public class JdbcUtil {

    private static String url;
    private static String username;
    private static  String password;

    static {
        //反射讀取配置檔案 src目錄下
        InputStream inp = JdbcUtil.class.getClassLoader().getResourceAsStream("JDBC.properties");
        try {
            Properties properties = new Properties();
            properties.load(inp);//從輸入流中讀取配置資訊
           url = properties.getProperty("url");
           username = properties.getProperty("username");
           password = properties.getProperty("password");
            Class.forName(properties.getProperty("Driver"));//載入資料庫驅動
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //獲取連線
    public static Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url, username, password);
    }

    //釋放連線
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

SQL隱碼攻擊

SQL隱碼攻擊即是指web應用程式對使用者輸入資料的合法性沒有判斷或過濾不嚴，攻擊者可以在web應用程式中事先定義好的查詢語句的結尾上新增額外的SQL語句，在管理員不知情的情況下實現非法操作，以此來實現欺騙資料庫伺服器執行非授權的任意查詢，從而進一步得到相應的資料資訊;

//SQL預編譯 insert into user(id,name,age,sex) values (?,?,?,?)
PreparedStatement preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (?,?,?,?)");

 //手動給引數賦值
preparedStatement.setInt(1,100);
preparedStatement.setString(2,"金角銀角");
preparedStatement.setInt(3,12);
preparedStatement.setString(4,"男");

//執行
int i = preparedStatement.executeUpdate();
System.out.println("執行結果:"+i);

JDBC操作事務

  @Test
    public void Test2()  {

        //載入驅動
        try {
            Class.forName("com.mysql.jdbc.Driver");//固定寫法
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //使用者資訊和url
        String password="123456";
        String username="root";
        String url="jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
        //連線成功和資料庫物件
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            //關閉資料庫自動提交 自動會開啟事務
            connection.setAutoCommit(false);
            //SQL預編譯 insert into user(id,name,age,sex) values (?,?,?,?)
            preparedStatement = connection.prepareStatement("insert into user(id,name,age,sex) values (?,?,?,?)");

            //手動給引數賦值
            preparedStatement.setInt(1,100);
            preparedStatement.setString(2,"金角銀角");
            preparedStatement.setInt(3,12);
            preparedStatement.setString(4,"男");

            //執行
            int i = preparedStatement.executeUpdate();
            System.out.println("執行結果:"+i);
            connection.commit();//提交事務
        } catch (SQLException throwables) {
            try {
                connection.rollback();//回滾
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            try {
                preparedStatement.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

資料庫連線池

資料庫連線>執行>釋放十分浪費資源

池化技術:準備一些預選準備的資源,需要時就直接連線準備好的

編寫連線池,只需要實現一個介面DataSource,各大廠商需要讓自己的連線池實現這個介面

目前市場上常見的連線池有DBCP連線池, C3P0連線池, Druid連線池

無論使用什麼資料來源,本質還是一樣的,DataSource介面不會變

使用C3P0連線池

匯入依賴

 <!--C3P0連線池 -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

編寫C3P0配置檔案

建立c3p0-config.xml配置檔案檔名必須是c3p0-config.xml

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jtdb?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>
    
    <!-- This app is massive! -->
    <named-config name="intergalactoApp">
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
    
        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>
    
        <!-- he's important, but there's only one of him -->
        <user-overrides user="master-of-the-universe">
            <property name="acquireIncrement">1</property>
            <property name="initialPoolSize">1</property>
            <property name="minPoolSize">1</property>
            <property name="maxPoolSize">5</property>
            <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
    </named-config>

</c3p0-config>

編寫工具類

/**
 * C3P0連線池
  */
   public class JdbcUtilsC3P0 {

   private static DataSource dataSource=null;

   static {
       dataSource = new ComboPooledDataSource();
   }
   //獲取連線
   public static Connection getConnection() throws SQLException {
       return dataSource.getConnection();
   }


}

測試

  //測試C3P0連線池
    @Test
    public void TEst4() throws SQLException {
        Connection connection = JdbcUtilsC3P0.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from user");
        //遍歷結果
      while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
           System.out.println(resultSet.getObject("age"));
            System.out.println(resultSet.getObject("sex"));
       }
    }

資料庫級別的MD5

MD5即Message-Digest Algorithm 5（資訊-摘要演算法5），用於確保資訊傳輸完整一致。是計算機廣泛使用的雜湊演算法之一（又譯摘要演算法、雜湊演算法），主流程式語言普遍已有MD5實現。將資料（如漢字）運算為另一固定長度值，是雜湊演算法的基礎原理，MD5的前身有MD2、MD3和MD4。

實現資料加密

1.對所有password進行加密

update testmd5 set pwd = md5(pwd);

2.對單挑記錄進行加密

INSERT INTO testmd5 VALUES(3,'kuangshen2','123456')
update testmd5 set pwd = md5(pwd) where name = 'zwt';

3.新增資料自動加密

INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));

4.查詢登入使用者資訊(MD5對比加密以後的密碼)

SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');

Redis 初見
2022-01-13
Redis
Swift初見
2014-07-27
Swift
JVM-初見
2021-08-19
JVM
RabbitMQ-初見
2021-09-04
MQ
初見Kotlin
2016-03-17
Kotlin
分散式-鎖-初見
2021-08-25
分散式
快取初見——EhCache
2020-10-29
快取
初見websocket技術
2017-11-16
Web
MySQL-事務
2019-02-01
MySql
我和圖靈的初見
2016-04-21
圖靈
MySQL-檢索資料
2022-06-08
MySql
MySQL-排序資料
2022-06-09
MySql排序
MySql-觸發器
2020-09-29
MySql觸發器
MySQL-官方文件摘抄
2017-11-29
MySql
Scala 學習筆記（1）：初見
2017-07-21
筆記
PHP初學者常見問題集
2011-03-10
PHP
MySQL-安裝配置篇
2020-08-02
MySql
MySQL-知識彙總
2020-06-17
MySql
高效能MySQL-索引
2021-05-15
MySql索引
mysql-三表關聯
2018-08-24
MySql
MySQL-刪除欄位
2018-11-29
MySql
Mysql-高效能索引
2018-10-12
MySql索引
MySql-時期函式
2020-11-02
MySql函式
mysql-原始碼安裝
2016-05-20
MySql原始碼
初見函數語言程式設計
2019-05-27
函數程式設計
go語言初學者常見錯誤
2018-11-06
Go
初學python的常見問題
2020-10-07
Python
初見SpringMVC之資料繫結
2018-04-12
SpringMVC
初見React，一步一個坑
2018-06-06
React
初識javascript的一點點見解
2017-05-11
JavaScript
[Shell] awk初學之常見問題
2016-05-20
MySQL-建立計算欄位
2022-06-13
MySql
MySQL-常用命令(二)
2017-02-14
MySql
MySQL-模仿陣列使用方式
2013-06-25
MySql陣列
mysql-列合併成行方式
2013-06-25
MySql
MySQL-基礎語法教程
2024-05-07
MySql
c++ 中vector 常見用法（給初學者）
2021-11-18
C++
【Linux天梯】第一話·初見Linux
2022-03-10
Linux