MySQL總結及JDBC__狂神說Java

FreedomF2發表於2020-11-23

1、初識MySQL

JavaEE:企業級Java開發 Web

前端(頁面:展示:資料)

後臺 (連線點:連線資料庫JDBC,連線前端(控制檢視跳轉,給前端傳遞資料))

資料庫(存資料,Txt,Excel,Word)

只會寫程式碼,學好資料庫,基本混飯吃:

作業系統,資料結構與演算法!當一個不錯的程式猿!

離散數學,數位電路,體系結構,編譯原理。+實戰經驗,優秀程式猿

1.1為什麼學資料庫

1、崗位需求

2、現在的世界,大資料時代,得資料者得天下

3、被迫需求:存資料

4、資料庫是所有軟體體系中最核心的存在 DBA

1.2 什麼是資料庫

資料庫:(DB,DataBase)

概念:資料倉儲,軟體,安裝在作業系統之(windows,Linux。mac)上的!SQL,可以儲存大量的資料,500萬!

作用:儲存資料,管理資料 Excel

1.3 資料庫分類

關係型資料庫:(SQL)

  • MySQL, Oracle, sql Server, DB2, SQLite
  • 通過表和表之間,行和列之間的關係進行資料的儲存

非關係型資料庫:(NoSQL) Not Only SQL

  • Redis, MongDB
  • 非關係型資料庫,物件儲存,通過物件自身的屬性來決定。

**DBMS(資料庫管理系統) **

  • 資料庫的管理軟體,科學有效的管理我們的資料,維護和獲取
  • MySQL ,資料管理系統!

1.4 MySQL簡介

MySQL是一個**關係型資料庫管理系統**

前世: 瑞典MySQL AB 公司

今身: 屬於 Oracle 旗下產品

MySQL是最好的 RDBMS (Relational Database Management System,關聯式資料庫管理系統) 應用軟體之一。

開源的資料庫軟體

體積小,速度快,總體擁有成本低,招人成本比較低。

中小型網站,或者大型網站,叢集

官網: https://www.mysql.com/

1.5連線資料庫

命令列連線!

mysql -u root -p123456 --連線資料庫

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';  --修改密碼

flush privileges;--重新整理許可權
--------------------------------------------------
--所有語句使用;結尾--

show databases;--檢視所有的資料庫

mysql> use school--切換資料庫, use 資料庫名
Database changed

--

show tables;--檢視資料庫中所有的表
describe student;--顯示資料庫中所有的表的資訊
create database westos;--建立一個資料庫

exit;--退出連線

--單行註釋(sql本來註釋)
/*
多行註釋
*/

2、運算元據庫

運算元據庫》運算元據庫中的表》運算元據庫中表的資料

MySQL不區分大小寫

2.1運算元據庫

1.建立資料庫

CREATE DATABASE IF NOT EXISTS westos;

2.刪除資料庫

DROP DATABASE IF EXISTS westos

3.使用資料庫

-- ``,如果你的表名或者欄位名是一個特殊字元,需要帶``

USE 'school'

4.產看資料庫

SHOW DATABASES--檢視所有資料庫

2.2資料庫的列型別

數值

  • tinyint 十分小的資料 1個位元組
  • smallint 較小的資料 2個位元組
  • mediumint 中等大小 3個位元組
  • int 標準的整數 4個位元組(常用)
  • bigint 較大的資料 8個位元組
  • float 浮點數 4個位元組
  • double 浮點數 8個位元組 (精度問題)
  • decimal 字串形式的浮點數,金融計算的時候,一般用

字串

  • char 字串固定大小 0-255
  • varchar 可變字串 0-65535(常用)
  • tinytext 微型文字 2^8-1
  • text 文字串 2^16-1 (儲存大文字)

時間日期

java.util.Date

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

null

  • 沒有值,未知
  • 注意,不要使用null進行運算,結果為null

2.3資料庫的欄位型別(重點)

unsigened:

  • 無符號的整數

  • 宣告該列不能宣告負數

zerofill:

  • 0填充的
  • 10的長度 1 – 0000000001 不足位數用0 填充

自增:

  • 通常理解為自增,自動在上一條記錄的基礎上+1
  • 通常用來設計唯一的主鍵 index,必須是整數類似
  • 可以自定義設定主鍵自增的起始值和步長

非空 NULL not Null

  • 假設設定為 not null,如何不給他賦值,就會報錯

  • NULL 如果不填寫,預設為NULL

預設:

  • 設定預設的值!

2.4 建立資料庫表

--目標:建立一個schoo1資料庫

--建立學生表(列,欄位)使用SQL 建立

--學號int 登入密碼varchar(20)姓名,性別varchar(2),出生日期(datatime),家庭住址,emai1--注意點,使用英文(),表的名稱和欄位儘量使用括起來

-- AUTO_ INCREMENT 自增

--字串使用單引號括起來!

--所有的語句後面加,(英文的),最後一個不用加

-- PRIMARY KEY 主鍵,一般- 一個表只有一個唯一 -的主鍵!
CREATE DATABASE school
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性別',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
`欄位名` 列型別[屬性][索引][註釋],
`欄位名` 列型別[屬性][索引][註釋],
...
`欄位名` 列型別[屬性][索引][註釋]
)[表型別][表的字符集設定][註釋]

常用命令

SHOW CREATE DATABASE school -- 檢視建立資料庫的語句
SHOW CREATE TABLE student -- 檢視student資料表的定義語句
DESC student -- 顯示錶的結構

2.5資料表的型別

-- 關於資料庫引擎
/*
INNODB 預設使用
MYISAM 早些年使用


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

常規使用操作:

  • MYISAM 節約空間,速度較快,
  • INNODB 安全性高,事務處理,多表多使用者操作

在物理空間存在的位置

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

本質還是檔案的儲存

MySQL 引擎在物理檔案上的區別

  • innoDB 在資料庫表中,只有一個*.frm檔案,以及上級目錄下的ibdata1檔案
  • MYISAM 對應的檔案
    • *.frm - 表結構的定義檔案
    • *. MYD -資料檔案
    • *.MYI 索引檔案

設定資料庫字符集編碼

CHARTSET=UTF8

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

可以在my.ini中配置預設的編碼

character-set-server=utf8

2.6修改刪除表

修改

-- 修改表名 ALTER TABLE 舊錶面 AS 新表名
ALTER TABLE student RENAME  AS student1
-- 增加表的欄位 ALTER TABLE 表名 ADD 欄位名 列屬性
ALTER TABLE student1 ADD age INT(11)
-- 修改表的欄位(重新命名,修改約束)
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- 修改約束
ALTER TABLE student1 CHANGE age age1 INT(1)  -- 欄位重新命名

-- 刪除表的欄位
ALTER TABLE student1 DROP age1

刪除

-- 刪除表
DROP TABLE IF EXISTS student1

所有的建立和刪除操作儘量加上判斷,以免報錯

注意點:

  • `` 欄位名,使用這個包裹
  • 註釋 – /**/
  • sql 關鍵字大小寫不敏感,建議寫小寫
  • 所有的符號全部用英文

3、MySQL資料管理

3.1外來鍵(瞭解)

方式一:在建立表的時候,增加約束(麻煩,比較複雜)

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

-- 學生表的 gradeid 欄位 要去引用年級表的gradeid
-- 定義外來鍵KEY
-- 給這個外來鍵新增約束(執行引用) references 引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性別',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '學生年級',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
PRIMARY KEY (`id`),
KEY `FK_gardeid` (`gradeid`),
CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid)
)ENGINE=INNODB DEFAULT CHARSET=utf8

刪除有外來鍵關係的表的時候,必須先刪除引用的表(從表),再刪除被引用的表(主表)

方式二: 建立表成功後新增外來鍵

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

-- 學生表的 gradeid 欄位 要去引用年級表的gradeid
-- 定義外來鍵KEY
-- 給這個外來鍵新增約束(執行引用) references 引用
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學號',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性別',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '學生年級',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 建立表的時候沒有外來鍵關係
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE`表`  ADD CONSTRAINT 約束名 FOREIGN KEY(作為外來鍵的列) 引用到哪個表的哪個欄位

以上的操作都是物理外來鍵,資料庫級別外來鍵,不建議使用。(避免資料庫過多造成困擾)

最佳實踐

  • 資料庫就是單純的表,只用來存資料,只有行(資料)和列(欄位)
  • 我們想使用多張表的資料,想使用外來鍵(程式去實現)

3.2 DML語言(全記住)

資料庫意義:資料儲存,資料管理

DML語言:資料操作語言

  • Insert
  • update
  • delete

3.3新增

insert

-- 插入語句(新增)
-- nsert into 表名([欄位一], [欄位二])values('值1'),('值2')

INSERT INTO `grade` (`gradename`) VALUES('大四')

-- 由於主鍵自增我們可以省略(如何不寫表的欄位,他會一一匹配)
INSERT INTO `grade` VALUES('大三')
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES ('大三','null')

-- 一般寫插入語句,我們一定要資料和欄位一一對應。
-- 插入多個欄位
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');


INSERT INTO `student`(`name`) VALUES ('張三')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('張三','aaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('李四','aaaaa','男'),('王五','23232','女')

語法:-- insert into 表名([欄位一], [欄位二])values(‘值1’),(‘值2’)

注意事項:

1.欄位和欄位之間用逗號分開

2.欄位可以省略,但是後面的值必須一一對應

3.可以同時插入多條資料,VALUES後面的值需要使用,隔開即可

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('李四','aaaaa','男'),('王五','23232','女')

3.4 修改

update 修改誰(條件) set 原來的值=新值

-- 修改學員名字

UPDATE `student` SET `name`='囷' WHERE id =1;
-- 不指定條件的情況下,會改動所有表
UPDATE `student` SET `name`='233'

-- 語法;
-- UPDATE 表名 set column_name,[] = value where 條件

條件:where 子句 運算子 id 等於 某個值,大於某個值,在某個區間內修改

操作符返回布林值

操作符含義範圍結果
=等於5=6false
!= <>不等於5!=6true
>大於  
<小於  
>=   
<=   
between and在某個範圍內,閉合區間  
and&&5>1and1>2false
or||5>1or1>2true

注意:

  • column_name 是資料庫的列,帶上``

  • 條件,是篩選的條件,如果沒有指定,則會修改所有的列

  • value 是一個具體的值,也可以是一個變數

  • 多個設定的屬性之間,使用英文逗號隔開

    UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'
    
    

3.5 刪除

delete 命令

語法 delete from 表名 [where 條件]

-- 刪除資料 (避免這樣寫)
DELETE FROM `student`


-- 刪除指定
DELETE FROM `student` where id= 1

TRUNCATE 命令

作用:完全清空一個資料庫,表的結構和索引不會變

delete 和 TRUNCATE 區別

  • 相同點: 都能刪除資料,都不會刪除表結構
  • 不同:
    • TRUNCATE 重新設定自增列 計數器會歸零
    • TRUNCATE 不會影響事務
-- 測試delete 和 truncate 區別

CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')

DELETE FROM `test` -- 不會影響自增

TRUNCATE TABLE `test` -- 自增會歸零

瞭解即可:delete刪除的問題 重啟資料庫,現象

  • innoDB 自增列會從1開始(存在記憶體當中,斷電即失)
  • MyISAM 繼續從上一個自增量開始(存在檔案中,不會丟失)

4、DQL查詢資料(最重點)

4.1DQL

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

  • 所有的查詢操作都用它 Select
  • 簡單的查詢,複雜的查詢它都能做
  • 資料庫中最核心的語言
  • 使用頻率最高的語言

4.2指定查詢欄位

-- 查詢  SELECT 欄位 FROM 表

-- 查詢指定欄位  such as
SELECT `StudentNo`,`StudentName` FROM student

-- 別名,給結果起一個名字 AS   可以給欄位起別名 也可以給表起別名
SELECT `StudentNo` AS 學號,`StudentName`AS 學生姓名 FROM student AS S

-- 函式 Concat(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM student

語法: SELECT 欄位 ... FROM 表

有時候,列名字不是那麼見名知意。我們起別名 AS 欄位名 AS 別名 表名 AS 別名

去重

作用:去除select語句查詢出來的結果中重複的語句,重複的語句只顯示一條

-- 查詢一下有哪些同學參加了考試,成績
SELECT * FROM result -- 查詢全部的考試成績
-- 查詢有哪些同學參加了考試
SELECT `studentNo` FROM result 
-- 發現重複資料,去重
SELECT DISTINCT `studentNo` FROM result 

資料庫的列(表示式)

SELECT VERSION()  --查詢系統版本(函式)
SELECT 100*3-1 AS 計算結果 -- 用來計算(表示式)
SELECT @@auto_increment_increment --查詢自增的步長(變數)

-- 學員考試成績+1 分 檢視
SELECT `StudentNo`,`StudentResult`+1 AS '提分後' FROM result


資料庫中的表示式: 文字值,列,Null , 函式,計算表示式,系統變數…

select 表示式 from 表

4.3where 條件子句

作用:檢索資料中符合條件的值

邏輯運算子

運算子語法結果
and &&a and b a&&b邏輯與
or ||a or b a||b邏輯或
Not !=not a !a邏輯非

儘量使用英文

-- 查詢考試成績在95分到100分之間
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100

-- 模糊查詢(區間)
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000號學生之外的同學成績
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000

模糊查詢:比較運算子

運算子語法描述
I S NULLa is null如果操作符為null 結果為真
IS NOT NULLa is not null如果操作符為not null 結果為真
BETWEENa between b and c若a在b 和c之間則為真
LIKEa like bSQL匹配,如果a 匹配到b 則為真
INa in (a1,a2,a3…)假設a 在 a1,a2,a3其中的某一箇中,為真
--  查詢姓劉的同學
-- like結合 %(代表0到任意字元)  _(一個字元)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉%';

-- 查詢姓劉的同學,名字後只有一個字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉_';

-- 查詢姓劉的同學,名字後只有兩個字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '劉__';

-- 查詢名字中間有嘉字的同學 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';



===================IN(具體的一個或者多個值)===========================
-- 查詢1001 1002 1003 學員資訊
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- 查詢在北京的學生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛陽');


===================NULL NOT NULL===================================
-- 查詢地址為空的學生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL

-- 查詢有出生日期的同學  不為空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;



4.4 聯表查詢

JOIN 對比

在這裡插入圖片描述

在這裡插入圖片描述

======================聯表查詢 join ==============================
-- 查詢參加考試的同學 (學號,姓名,考試編號,分數)

SELECT * FROM student 
SELECT * FROM result

/*
1. 分析需求,分析查詢的欄位來自哪些表
2.確定使用哪種連線查詢?7種
確定交叉點(這兩個表中哪個資料是相同的)
判斷的條件: 學生表中 studentNo = 成績表中 studentNo 

*/

-- JION(表) ON (判斷的條件)連線查詢
-- where 等值查詢
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo

--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo


操作描述
Inner join如果表中至少有一個匹配,就返回行
left join即使左表中沒有匹配,也會從左表中返回所有的值
right jion即使右表中沒有匹配,也會從右表中返回所有的值
-- 查詢考的同學
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL

-- 查詢了參加考試同學的資訊:學號:學生姓名:科目名:分數
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo

-- 我要查詢哪些資料 SELECT ....
-- 從哪幾個表中查 FROM 表 xxx JOIN 連線的表 ON 交叉條件
-- 假設存在一中多張表查詢,先查詢兩章表,然後再慢慢增加

--FROM a LEFT JOIN b   左為準
--FROM a RIGHT JOIN b	右為準


自連線

自己的表跟自己的表連線,核心:一張表拆為兩張一樣的表

父類

categoryidcategoryName
2資訊科技
3軟體開發
5美術設計
  

子類

pidcategoryidcategoryName
34資料庫
28辦公資訊
36web開發
57ps技術

操作:查詢父類對應子類關係

父類子類
資訊科技辦公資訊
軟體開發資料庫
軟體開發web開發
美術設計ps技術
-- 查詢父子資訊

SELECT a.`categroryName` AS `父欄目`,b.`categroryName` AS `子欄目`
FROM `catgroy` AS a,`catgroy` AS b
WHERE a.`categoryid`=b.`pid`
-- 查詢學員所屬的年級(學號,學生的姓名,年級)
SELECT 	studentNo,studentName,gradeName
FROM student s
INNER JOIN `grade` g
ON s.`GradeId`=g.`GradeId`

4.5分頁和排序

============================分頁 limit 和排序order by=================


-- 排序:  升序ASC  降序  DESC
SELECT  xx
FROM xx
JOIN xx
WHERE  xx
ORDER BY  xx
ASC   ||  DESC

分頁

-- 為什麼要分頁
-- 緩解資料庫壓力,給人的體驗更好


-- 分頁,每頁顯示五條資料

-- 語法: limit 當前頁,頁面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='資料結構-1'
ORDER BY StudentResult ASC
LIMIT 0,5

-- 第一頁 limit 0,5
-- 第二頁 limit 5,5
-- 第三頁 limit 10,5
-- 第N頁 limit 5*(n-1),5


語法 limit(查詢起始下標,pagesize)

4.6 子查詢

where (這個值是計算出來的)

本質:在where語句中巢狀一個子查詢語句

-- ===========================where=========================

-- 1.查詢 資料庫結構-1的所有考試結構(學號,科目編號,成績) 降序
-- 方式一: 連線查詢
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '資料庫結構-1'
ORDER BY StudentResult DESC

-- 方式二:使用子查詢(由裡及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
	SELECT SubjectNo FROM  `subject` 
    WHERE SubjectName = '資料庫結構-1'
)
ORDER BY StudentResult DESC


-- 分數不少於80分的學生的學號和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80

-- 在這個基礎上 增加一個科目 ,高等數學-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
    SELECT Subject FROM `subject`
    WHERE SubjectName='高等數學-2'
)

-- 查詢課程為 高等數學-2 且分數不小於80分的同學的學號和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等數學-2'
WHERE `SubjectaName`='高等數學-2' AND StudentResult >=80


-- 再改造 (由裡即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等數學-2'
)
)

4.7 分組

-- 查詢不同課程的平均分,最高分,最低分,平均分大於80
-- 核心:(根據不同的課程分組)

SELECT `SubjectName`,AVG(StudentResult),MAX(StudentResult)
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo

GROUP BY r.SubjectNo -- 通過什麼欄位來分組
HAVING AVG(StudentResult)>80



5、MySQL函式

5.1 常用函式

-- 數學運算

SELECT ABS(-8) -- 絕對值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0-1隨機數
SELECT SIGN(-10) -- 判斷一個數的符號 0-0 負數返回-1 正數返回1

-- 字串函式
SELECT CHAR_LENGTH('2323232') -- 返回字串長度
SELECT CONCAT('我','233') -- 拼接字串
SELECT INSERT('java',1,2,'cccc') -- 從某個位置開始替換某個長度
SELECT UPPER('abc') 
SELECT LOWER('ABC')
SELECT REPLACE('堅持就能成功','堅持','努力')

-- 查詢姓 周 的同學 ,改成鄒
SELECT REPLACE(studentname,'周','鄒') FROM student
WHERE studentname LIKE '周%'

-- 時間跟日期函式(記住)
SELECT CURRENT_DATE() -- 獲取當前日期
SELECT CURDATE() -- 獲取當前日期
SELECT NOW() -- 獲取當前日期
SELECT LOCATIME()  -- 本地時間
SELECT SYSDATE()  -- 系統時間

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系統
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2 聚合函式(常用)

函式名稱描述
COUNT()計數
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
 

5.3 資料庫級別MD5加密(擴充)

什麼是MD5

主要增強演算法複雜度不可逆性。

MD5不可逆,具體的MD5是一樣的

MD5破解原理,背後有一個字典,MD5加密後的值,加密前的值

CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=UTF8


-- 明文密碼
INSERT INTO testmd5 VALUES(1,'張三','123456'),(2,'李四','123456'),(3,'王五','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1  -- 加密全部

-- 插入時加密

INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'紅',MD5('123456'))

-- 如何校驗,將使用者傳遞過來的密碼,進行MD5加密,然後對比加密後的值
SELECT * FROM testmd5 WHERE `name`='紅' AND pwd=MD5('123456')

6、事務

6.1 什麼是事務

要麼都成功,要麼都失敗


  1. SQL執行, A給B轉賬 A 1000–> 200 B200
  2. SQL 執行, B收到A的錢 A800 — B400

將一組SQL放在一個批次中執行

事務原則 : ACID原則 原子性,一致性,隔離性,永續性 (髒讀,幻讀…)

原子性(Atomicity)

要麼都成功,要麼都失敗

一致性(Consistency)

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

永續性(Durability)–事務提交

事務一旦提交就不可逆轉,被持久化到資料庫中

隔離性

事務產生多併發時,互不干擾

隔離產生的問題

髒讀:

指一個事務讀取了另外一個事務未提交的資料。

不可重複讀:

在一個事務內讀取表中的某一行資料,多次讀取結果不同。(這個不一定是錯誤,只是某些場合不對)

虛讀(幻讀)

是指在一個事務內讀取到了別的事務插入的資料,導致前後讀取不一致。
(一般是行影響,多了一行)

執行事務

-- mysql 自動開啟事務提交
SET autocommit=0 -- 關閉
SET autocommit=1 -- 開啟(預設的)

-- 手動處理事務
SET autocommit =0 -- 關閉自動提交

-- 事務開啟

START TRANSACTION -- 標記一個事務的開始,從這個之後的SQP都在同一個事務內

INSERT XX
INSERT XX

-- 提交 : 持久化(成功)
COMMIT 
-- 回滾:  回到原來的樣子(失敗)
ROLLBACK
-- 事務結束
SET autocommit = 1 -- 開啟自動提交
-- 瞭解
SAVEPOINT 儲存點名稱 -- 設定一個事務的儲存點
ROLLBACK TO SAVEPOINT 儲存點名 -- 回滾到儲存點
RELEASE SAVEPOINT 儲存點 -- 刪除儲存點

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-DfVvNLP7-1594142971424)(C:\Users\53424\AppData\Roaming\Typora\typora-user-images\1594012051660.png)]

模擬場景

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)

-- 模擬轉賬:事務
SET autocommit = 0; -- 關閉自動提交
START TRANSACTION -- 開啟事務(一組事務)
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 轉賬給B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到錢

COMMIT ; -- 提交事務
ROLLBACK ; -- 回滾

SET autocommit=1 -- 恢復預設值

7、索引

MySQL索引的建立對於MySQL的高效執行是很重要的,索引可以大大提高MySQL的檢索速度。

7.1索引的分類

在一個表中,主鍵索引只能有一個,唯一索引可以有多個

  • 主鍵索引 (PRIMARY KEY)
    • 唯一的標識,主鍵不可重複,只能有一個列作為主鍵
  • 唯一索引 (UNIQUE KEY)
    • 避免重複的列出現,唯一索引可以重複,多個列都可以標識唯一索引
  • 常規索引(KEY/INDEX)
    • 預設的,index,key關鍵字來設定
  • 全文索引(FULLTEXT)
    • 在特點的資料庫引擎下才有,MyISAM
    • 快速定位資料
-- 索引的使用
-- 1.在建立表的時候給欄位增加索引
-- 2.建立完畢後,增加索引

-- 顯示所有的索引資訊
SHOW INDEX FROM 表

-- 增加一個索引
ALTER TABLE 表 ADD FULLTEXT INDEX 索引名(欄位名)

-- EXPLAIN 分析sql執行狀況
EXPLAIN SELECT * FROM student -- 非全文索引



7.2 測試索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100萬資料
DELIMITER $$ --  寫函式之前必寫
CREATE FUNCTION mock_data()
RETURNS INT 
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;

WHILE i<num DO
-- 插入語句
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('使用者',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100));

SET i = i+1;
END WHILE;
RETURN i;


END;

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUE(CONCAT('使用者',i),'534240118@qq.com',FLOOR (CONCAT('18',RAND()*9999999)),FLOOR (RAND()*2),
UUID(),FLOOR (RAND()*100))


SELECT mock_data();

SELECT * FROM app_user WHERE `name`='使用者9999' -- 接近半秒

EXPLAIN SELECT * FROM app_user WHERE `name`='使用者9999'  -- 查詢99999條記錄

-- id _ 表名_欄位名
-- create index on 欄位
CREATE INDEX id_app_user_name ON app_user(`name`); -- 0.001 s
EXPLAIN SELECT * FROM app_user WHERE `name`='使用者9999'  -- 查詢一條記錄

索引在小資料的時候,用處不大,但是在大資料的時候,區別十分明顯

7.3 索引原則

  • 索引不是越多越好
  • 不要對經常變動的資料加索引
  • 小資料量的表不需要加索引
  • 索引一般加在常用來查詢的欄位上

索引的資料結構

Hash 型別的索引

Btree: 預設innodb 的資料結構

閱讀: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8、許可權管理和備份

8.1使用者管理

SQLyog 視覺化管理

在這裡插入圖片描述

SQL命令操作

使用者表:mysql.user

本質:對這張表進行,增刪改查

-- 建立使用者  CREATE USER 使用者名稱 IDENTIFIED BY '密碼'
CREATE USER sanjin IDENTIFIED BY '123456'

-- 修改密碼(修改當前密碼)
SET PASSWORD = PASSWORD('111111')


-- 修改密碼(修改指定使用者密碼)

SET PASSWORD FOR sanjin = PASSWORD('111111')


-- 重新命名  rename user 原名字 to 新名字
RENAME USER sanjin TO sanjin2

-- 使用者授權   ALL PRIVILEGES 全部的許可權   庫,表
-- ALL PRIVILEGES 除了給別人授權,其他都能幹
GRANT ALL PRIVILEGES ON *.* TO sanjin2

-- 查詢許可權
SHOW GRANTS FOR sanjin2  -- 檢視指定使用者的許可權
SHOW GRANTS FOR root@localhost


-- 撤銷許可權 REVOKE 哪些許可權,在哪個庫撤銷,給誰撤銷
REVOKE ALL PRIVILEGES ON *.* FROM sanjin2

-- 刪除使用者
DROP USER sanjin2

8.2 MySQL備份

為什麼備份:

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

MySQL資料庫備份的方式

  • 直接拷貝物理檔案
  • 在SQLyog這種視覺化工具中手動匯出
    • 在想要匯出的表或者庫中,右鍵選擇備份和匯出
    • 在這裡插入圖片描述

在這裡插入圖片描述

9、規範資料庫設計

當資料庫比較複雜的時候,我們就需要設計了

糟糕的資料庫設計:

  • 資料冗餘,浪費空間
  • 資料庫插入和刪除都會麻煩,異常【遮蔽使用物理外來鍵】
  • 程式的效能差

良好的資料庫設計:

  • 節省記憶體空間
  • 保證資料庫的完整性
  • 方便我們開發系統

軟體開發中,關於資料庫的設計

  • 分析需求:分析業務和需要處理的資料庫的需求
  • 概要設計:設計關係圖 E-R圖

設計資料庫的步驟(個人部落格)

  • 收集資訊,分析需求

    • 使用者表(使用者登入登出,使用者的個人資訊,寫部落格,建立分類)
    • 分類表(文章分類,誰建立的)
    • 文章表(文章的資訊)
    • 友連結串列(友鏈資訊)
    • 自定義表(系統資訊,某個關鍵的字,或者某些主欄位)
    • 說說表(發表心情…id ,content ,time)
  • 標識實體(把需求落地到每個欄位)

  • 標識實體之間的關係

    • 寫部落格 user–>blog
    • 建立分類 user–>category
    • 關注 user–>user
    • 友鏈–>links
    • 評論 user–>user

9.2三大正規化

為什麼需要資料規範化?

  • 資訊重複

  • 更新異常

  • 插入異常

  • 刪除異常

    • 無法正常顯示異常
  • 刪除異常

    • 丟失有效的資訊

三大正規化

第一正規化(1NF)

原子性:保證每一列不可再分

第二正規化(2NF)

前提:滿足第一正規化

每張表只描述一件事情

第三正規化(3NF)

前提:滿足第一正規化和第二正規化

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

(規範資料庫的設計)

規範性和效能的問題

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

  • 考慮商業化的需求和目標(成本和使用者體驗) 資料庫的效能更加重要
  • 再規範效能的問題的時候,需要適當的考慮一下,規範性
  • 故意給某些表加一些冗餘的欄位(從多表,變成單表)
  • 故意增加一些計算列(從大資料量降低為小資料量的查詢:索引)

10、JDBC(重點)

10.1 資料庫驅動

驅動:音效卡,顯示卡,資料庫

在這裡插入圖片描述

我們的程式會通過資料庫驅動,和資料庫打交道!

10.2 JDBC

SUN 公司為了簡化開發人員的(對資料庫的統一)操作,提供了一個(Java運算元據庫的)規範,JDBC

這些規範的實現由具體的廠商去做

對於開發人員來說,我們只需要掌握JDBC的介面操作即可

在這裡插入圖片描述

java.sql

javax.sql

還需要匯入資料庫驅動包

10.3 第一個JDBC程式

建立測試資料庫

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1.建立一個普通專案

2.匯入資料庫驅動

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-12VBmers-1594142971429)(C:\Users\53424\AppData\Roaming\Typora\typora-user-images\1594046904540.png)]

3.編寫測試程式碼

package com.kuang.lesson01;
//我的第一個JDBC程式

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcFirstDemo {
    public static void main(String[] args) throws Exception {
        //1. 載入驅動
        Class.forName("com.mysql.jdbc.Driver");//固定寫法
        //2. 使用者資訊和url
        //useUnicode=true&characterEncoding=utf8&&useSSL=true
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
        String name = "root";
        String password = "123456";

        //3. 連線成功,返回資料庫物件  connection代表資料庫
        Connection connection= DriverManager.getConnection(url,name,password);
        //4. 執行SQL的物件 statement 執行SQL的物件
        Statement statement = connection.createStatement();

        //5. 執行SQL的物件 去執行SQL   可能存在結果,檢視返回結果
        String sql="SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的結果集,結果集中封裝了我們全部查詢的結果
        while(resultSet.next()){
            System.out.println("id+"+resultSet.getObject("id"));
            System.out.println("name+"+resultSet.getObject("NAME"));
            System.out.println("password+"+resultSet.getObject("PASSWORD"));
            System.out.println("email+"+resultSet.getObject("email"));
            System.out.println("birthday+"+resultSet.getObject("birthday"));
        }
        //6. 釋放連線
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步驟總結:
1.載入驅動

2.連線資料庫 DriverManager

3.獲取執行SQL的物件 Statement

4.獲得返回的結果集

5.釋放連線

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定寫法
Connection connection= DriverManager.getConnection(url,name,password);

//connection代表資料庫
//資料庫設定自動提交
//事務提交
//事務回滾
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";

//mysql 預設3306
//協議://主機地址:埠號/資料庫名?引數1&引數2&引數3

//Oracle   1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 執行SQL的物件 pPrepareStatement 執行SQL的物件

String sql="SELECT * FROM users";//編寫Sql

statement.executeQuery();
statement.execute();
statement.executeUpdate();//更新,插入,刪除,返回一個受影響的行數

ResultSet 查詢的結果集,封裝了所以的查詢結果

獲得指定的資料型別

ResultSet resultSet = statement.executeQuery(sql);//返回的結果集,結果集中封裝了我們全部查詢的結果
        resultSet.getObject();//在不知道列型別下使用
        resultSet.getString();//如果知道則指定使用
        resultSet.getInt();
        


遍歷,指標

        resultSet.next(); //移動到下一個
        resultSet.afterLast();//移動到最後
        resultSet.beforeFirst();//移動到最前面
        resultSet.previous();//移動到前一行
        resultSet.absolute(row);//移動到指定行

釋放記憶體

//6. 釋放連線
        resultSet.close();
        statement.close();
        connection.close();//耗資源

10.4statement物件

Jdbc中的statement物件用於向資料庫傳送SQL語句,想完成對資料庫的增刪改查,只需要通過這個物件向資料庫傳送增刪改查語句即可。

Statement物件的executeUpdate方法,用於向資料庫傳送增、刪、改的sq|語句, executeUpdate執行完後, 將會返回一個整數(即增刪改語句導致了資料庫幾行資料發生了變化)。

Statement.executeQuery方法用於向資料庫發生查詢語句,executeQuery方法返回代表查詢結果的ResultSet物件。

CRUD操作-create

使用executeUpdate(String sql)方法完成資料新增操作,示例操作:

 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("插入成功");
        }

CRUD操作-delete

使用executeUpdate(String sql)方法完成資料刪除操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "delete from user where id =1";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("刪除成功");
        }

CURD操作-update

使用executeUpdate(String sql)方法完成資料修改操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "update user set name ='' where name = ''";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("修改成功");
        }

CURD操作-read

使用executeUpdate(String sql)方法完成資料查詢操作,示例操作:

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            System.out.println("");
        }

程式碼實現

1.提取工具類

package com.kuang.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");

            //1.驅動只用載入一次
            Class.forName(driver);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //2.獲取連線
    public static Connection getConnection() throws Exception{
        return DriverManager.getConnection(url, username, password);
    }
    //3.釋放資源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {

        if(rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if(conn!=null){
            conn.close();
        }

    }
}


2.編寫增刪改的方法,exectueUpdate

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args){
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
             conn = getConnection();//獲取連線
            st = conn.createStatement();//獲取SQL執行物件
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'sanjin','123456','233223@qq.com','2020-01-01')";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}


3.查詢 executeQuery

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.*;

public class TestInnsert {
    public static void main(String[] args) throws SQLException {
        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
             conn = getConnection();//獲取連線
            st = conn.createStatement();//獲取SQL執行物件
            String sql = "select * from users";
            rs=st.executeQuery(sql);//查詢完畢返回結果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

SQL隱碼攻擊問題

sql存在漏洞,會被攻擊導致資料洩露 SQL會被拼接 or

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.kuang.lesson02.utils.JdbcUtils.getConnection;

public class SQL隱碼攻擊 {
    public static void main(String[] args) {


        //SQL隱碼攻擊
 login("' or '1=1","123456");
    }
    public static void login(String name,String password){


        Connection conn =null;
        Statement st = null;
        ResultSet rs =null;



        try {
            conn = getConnection();//獲取連線
            st = conn.createStatement();//獲取SQL執行物件
            String sql = "select * from users where `NAME`='"+ name +"'  AND `PASSWORD`='"+ password +"'" ;
            rs=st.executeQuery(sql);//查詢完畢返回結果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
            JdbcUtils.release(conn,st,rs);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(conn,st,rs);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}


10.5 PreparedStatement物件

PreparedStatement 可以防止SQL隱碼攻擊 ,效率更高。

  1. 新增
  2. 刪除
  3. 查詢
package com.kuang.lesson03;

import com.kuang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement pstm=null;
        try {

            connection = JdbcUtils.getConnection();
            //區別
            //使用問好佔位符代替引數
            String sql = "insert into users(id,`NAME`) values(?,?)";
            pstm = connection.prepareStatement(sql);//預編譯sql,先寫sql然後不執行
            //手動賦值
            pstm.setInt(1,8);
            pstm.setString(2,"SANJIN");

            //執行
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                JdbcUtils.release(connection,pstm,null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

防止SQL隱碼攻擊本質,傳遞字元 帶有“ ”,轉義字元會被轉義

10.6 使用IDEA連線資料庫

在這裡插入圖片描述

連線成功後,可以選擇資料庫

在這裡插入圖片描述
雙擊資料庫

在這裡插入圖片描述

更新資料

在這裡插入圖片描述
編寫sql程式碼的地方
在這裡插入圖片描述

10.7 JDBC事務

要麼都成功,要麼都失敗

ACID原則

原子性:要麼全部完成,要麼都不完成

一致性:結果總數不變

隔離性:多個程式互不干擾

永續性:一旦提交不可逆,持久化到資料庫了

隔離性的問題:

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

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

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

程式碼實現

  1. 開啟事務conn.setAutoCommit(false);

  2. 一組業務執行完畢,提交事務

  3. 可以在catch語句中顯示的定義回滾,但是預設失敗會回滾

    package com.kuang.lesson04;
    
    import com.kuang.lesson02.utils.JdbcUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Action {
        public static void main(String[] args) {
    
            Connection conn =null;
            PreparedStatement ps = null;
            ResultSet rs = null;
    
            try {
                conn = JdbcUtils.getConnection();
                //關閉資料庫的自動提交功能, 開啟事務
                conn.setAutoCommit(false);
                //自動開啟事務
                String sql = "update account set money = money-500 where id = 1";
                ps =conn.prepareStatement(sql);
                ps.executeUpdate();
                String sql2 = "update account set money = money-500 where id = 2";
                ps=conn.prepareStatement(sql2);
                ps.executeUpdate();
    
                //業務完畢,提交事務
                conn.commit();
                System.out.println("操作成功");
            } catch (Exception e) {
                try {
                    //如果失敗,則預設回滾
                    conn.rollback();//如果失敗,回滾
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                e.printStackTrace();
            }finally {
                try {
                    JdbcUtils.release(conn,ps,rs);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
    
    

10.8資料庫連線池

資料庫連線–執行完畢–釋放

連線–釋放 十分浪費資源

池化技術: 準備一些預先的資源,過來就連線預先準備好的

常用連線數 100

最少連線數:100

最大連線數 : 120 業務最高承載上限

排隊等待,

等待超時:100ms

編寫連線池,實現一個介面 DateSource

開源資料來源實現(拿來即用)

DBCP

C3P0

Druid: 阿里巴巴

使用了這些資料庫連線池之後,我們在專案開發中就不需要編寫連線資料庫的程式碼了

DBCP

需要用到的jar包

dbcp.ar

C3P0

結論

無論使用什麼資料來源,本質是不變的,DateSource介面不會變,方法就不會變

相關文章