MySQL總結及JDBC__狂神說Java
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 早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事務支援 | 不支援 | 支援 |
| 資料行鎖定 | 不支援 | 支援 |
| 外來鍵約束 | 不支援 | 支援 |
| 全文索引 | 支援 | 不支援 |
| 表空間的大小 | 較小 | 較大，約為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=6 | false |
| != <> | 不等於 | 5！=6 | true |
| > | 大於 | ||
| < | 小於 | ||
| >= | |||
| <= | |||
| between and | 在某個範圍內，閉合區間 | ||
| and | && | 5>1and1>2 | false |
| or | || | 5>1or1>2 | true |
注意：
-
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 NULL | a is null | 如果操作符為null 結果為真 |
| IS NOT NULL | a is not null | 如果操作符為not null 結果為真 |
| BETWEEN | a between b and c | 若a在b 和c之間則為真 |
| LIKE | a like b | SQL匹配，如果a 匹配到b 則為真 |
| IN | a 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 右為準
自連線
自己的表跟自己的表連線，核心：一張表拆為兩張一樣的表
父類
| categoryid | categoryName |
|---|---|
| 2 | 資訊科技 |
| 3 | 軟體開發 |
| 5 | 美術設計 |
子類
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 資料庫 |
| 2 | 8 | 辦公資訊 |
| 3 | 6 | web開發 |
| 5 | 7 | ps技術 |
操作：查詢父類對應子類關係
| 父類 | 子類 |
|---|---|
| 資訊科技 | 辦公資訊 |
| 軟體開發 | 資料庫 |
| 軟體開發 | 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 什麼是事務
要麼都成功，要麼都失敗
- SQL執行， A給B轉賬 A 1000–> 200 B200
- 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隱碼攻擊 ，效率更高。
- 新增
- 刪除
- 查詢
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原則
原子性：要麼全部完成，要麼都不完成
一致性：結果總數不變
隔離性：多個程式互不干擾
永續性：一旦提交不可逆，持久化到資料庫了
隔離性的問題：
髒讀： 一個事務讀取了另一個沒有提交的事務
不可重複讀：在同一個事務內，重複讀取表中的資料，表發生了改變
虛讀（幻讀）：在一個事務內，讀取到了別人插入的資料，導致前後讀出來的結果不一致
程式碼實現
-
開啟事務
conn.setAutoCommit(false); -
一組業務執行完畢，提交事務
-
可以在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介面不會變，方法就不會變
相關文章
- 對狂神說的MybatisPlus的學習總結MyBatis
- 【狂神說】SpringBootSpring Boot
- 【狂神說Java】網路程式設計Java程式設計
- 狂神說Java Web學習筆記_CookieJavaWeb筆記Cookie
- 狂神說Java Web學習筆記_TomcatJavaWeb筆記Tomcat
- Java基礎（向B站狂神說Java學習）Java
- 【狂神說】Docker（三） - 高階進階Docker
- Git最新教程通俗易懂----狂神說Java -- ---學習筆記GitJava筆記
- JVM狂神說視訊學習筆記JVM筆記
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL 同步複製及高可用方案總結MySql
- 工控安全的神總結，說出了多少人心裡的問題？
- MySQL 總結MySql
- mysql總結MySql
- Java與Mysql鎖相關知識總結JavaMySql
- RSA總結 From La神
- Java乾貨神總結，程式設計師面試技巧Java程式設計師面試
- 【Java】jdk1.8新特性及用法總結JavaJDK
- Java-Stream流方法學習及總結Java
- mysql索引總結MySql索引
- MySQL鎖總結MySql
- java總結Java
- MySQL主從複製原理及必備知識總結MySql
- 《MySQL重要知識點及面試總結》：推薦收藏MySql面試
- Mysql半同步複製模式說明及配置示例 - 運維小結MySql模式運維
- MySQL知識總結MySql
- MySQL簡單總結MySql
- MySQL 讀後總結MySql
- MySQL索引——總結篇MySql索引
- MYSQL學習總結MySql
- MySQL的Explain總結MySqlAI
- Java Servet 總結Java
- java Stack總結Java
- 香格里拉+去哪網Java面試題及總結Java面試題
- MySQL資料庫總結MySql資料庫
- MySQL中介軟體總結MySql
- MySQL 讀後總結 (三)MySql
- mysql日期函式總結MySql函式