初識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&useUnicode=true&characterEncoding=utf8&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.新增資料自動加密
3.新增資料自動加密
INSERT INTO testmd5 VALUES(4,'kuangshen3',md5('123456'));
4.查詢登入使用者資訊(MD5對比加密以後的密碼)
SELECT * FROM testmd5 WHERE `name`='kuangshen' AND pwd=MD5('123456');