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
- 【狂神說Java】網路程式設計Java程式設計
- 狂神說Java Web學習筆記_CookieJavaWeb筆記Cookie
- Java基礎(向B站狂神說Java學習)Java
- 【狂神說】SpringBootSpring Boot
- 狂神說Java Web學習筆記_TomcatJavaWeb筆記Tomcat
- 【狂神說】Docker(三) - 高階進階Docker
- Git最新教程通俗易懂----狂神說Java -- ---學習筆記GitJava筆記
- JVM狂神說視訊學習筆記JVM筆記
- MySQL 索引及查詢優化總結MySql索引優化
- Java乾貨神總結,程式設計師面試技巧Java程式設計師面試
- 工控安全的神總結,說出了多少人心裡的問題?
- RSA總結 From La神
- 轉Java九個月反思及總結Java
- java學習總結及心得體會Java
- MySQL 同步複製及高可用方案總結MySql
- MYSQL實戰-MHA搭建及問題總結MySql
- mysql總結MySql
- MySQL 總結MySql
- Java與Mysql鎖相關知識總結JavaMySql
- 【Java】jdk1.8新特性及用法總結JavaJDK
- Java-Stream流方法學習及總結Java
- 《MySQL重要知識點及面試總結》:推薦收藏MySql面試
- JAVA 總結Java
- java總結Java
- MySQL鎖總結MySql
- mysql索引總結MySql索引
- MySQL日常總結MySql
- Mysql半同步複製模式說明及配置示例 - 運維小結MySql模式運維
- MySQL主從複製原理及必備知識總結MySql
- Mysql安裝過程問題總結及處理方法MySql
- 香格里拉+去哪網Java面試題及總結Java面試題
- MySQL索引——總結篇MySql索引
- MySQL 讀後總結MySql
- MySQL的Explain總結MySqlAI
- MySQL簡單總結MySql
- Mysql基本操作總結MySql
- 【總結】mysql半同步MySql