内容摘取自【狂神說Java】MySQL最新教程通俗易懂
連結:【狂神說Java】MySQL最新教程通俗易懂_哔哩哔哩_bilibili
以下為聽課時記錄的筆記。
目錄
01基本指令行操作
1.連接配接資料庫
2.修改使用者密碼
3.檢視所有的資料庫
4.切換資料庫
5.檢視資料庫中所有的表
6.顯示資料庫中所有的表的資訊
7.建立一個資料庫
8.退出連接配接
9.注釋
10.資料庫xxx語言
02操作資料庫語句
1.建立資料庫
2.删除資料庫
3.使用資料庫
4.檢視資料庫
03資料庫的列類型
04資料庫的字段屬性
05建立資料庫表(重點)
06MyIsam和InnoDB差別
07修改和删除資料表字段
1.修改
2.删除
09MySQL資料庫資料管理
1.外鍵(了解即可)
2.DML語言(全部記住)
3.添加
4.修改
5.删除
10DQL查詢資料(最重點)
1.DQL
2.指定查詢字段
3.where條件子句
4.聯表查詢
5.自連接配接(了解)
6.分頁和排序
7.子查詢
8.MySQL常用函數
8.1常用函數
8.2聚合函數(常用)
9.分組和過濾
10.Select小結
11.資料庫級别的MD5加密(擴充)
12.事務
12.1什麼是事務
12.2模拟場景
12.3測試事務實作轉賬
13索引
13.1索引的分類
13.2測試索引
13.3索引原則
14資料庫備份
14.1使用者管理
14.2MySQL備份
15規範資料庫設計
15.1為什麼需要設計
15.2三大範式
16JDBC
16.1資料庫驅動
16.2JDBC
16.3第一個JDBC程式
16.4JDBC對象詳解
16.5statement對象
16.6PreparedStatement對象
16.6使用IDEA連接配接資料庫
16.7JDBC操作事務
16.8 資料庫連接配接池
01基本指令行操作
1.連接配接資料庫
mysql -uroot -p123456--連接配接資料庫
123456為賬戶密碼
2.修改使用者密碼
update mysq1.user set authentication_string=password('123456') where user=' root' and Host ='loca7host'; --修改使用者密碼
flush privileges; --重新整理權限
所有語句都以“;”結尾
3.檢視所有的資料庫
show databases;--檢視所有的資料庫
4.切換資料庫
mysq1> use school --切換資料庫 use 資料庫名
scool為庫名
Database changed
5.檢視資料庫中所有的表
show tables; --檢視資料庫中所有的表
6.顯示資料庫中所有的表的資訊
describe student; --顯示資料庫中所有的表的資訊
7.建立一個資料庫
create database home; --建立一個資料庫
home是要建立資料庫的名稱
8.退出連接配接
exit;--退出連接配接
9.注釋
--單行注釋(SQL的本來的注釋)
#單行注釋(用在sqlyog)
10.資料庫xxx語言
CRUD增删改查
CRUD分别指增加(Create)、讀取查詢(Retrieve)、更新(Update)和删除(Delete)
DDL定義
DML操作
DQL查詢
DCL控制
02操作資料庫語句
操作資料庫>操作資料庫中的表>操作資料庫中表的資料
mysql關鍵字不分區大小寫
1.建立資料庫
CREATE DATABASE [IF NOT EXISTS] westos;
[IF NOT EXISTS] 可以不加 隻是存在會報異常 先進行判斷再進行
2.删除資料庫
DROP DATABASE [IF EXISTS] westos
[IF NOT EXISTS] 可以不加 隻是存在會報異常 先進行判斷再進行
3.使用資料庫
-- tab鍵的上面,如果你的表名或者字段名是一個特殊字元,就需要帶``
SELECT`user`FROM student
注意:不是單引号‘ 而是tab鍵上面的符号
use school
4.檢視資料庫
SHOW DATABASES --檢視所有的資料庫
學習思路:
對照sqlyog可視化曆史記錄檢視sql
固定的文法或關鍵字必須要強行記住!
03資料庫的列類型
1.數值
整型
tinyint 1個位元組
smallint 2個位元組
mediumint 3個位元組
int 4個位元組 常用的int
bigint 8個位元組
浮點數
float 4個位元組
double 8個位元組
decimal 字元串形式的浮點數﹐金融計算的時候,一般是使用decimal
字元串
char 字元串固定大小的 0~255.
varchar 可變字元串 0~65535
常用的變量String
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
04資料庫的字段屬性
Unsigned :
無符号的整數
聲明了該列不能聲明為負數
zerofill :
0填充的
不足的位數,使用0來填充,int (3) , 5 --- 005
自增:
通常了解為自增,自動在上一條記錄的基礎上+1(預設)
通常用來設計唯一的主鍵~index,必須是整數類型
可以自定義設計主鍵自增的起始值和步長
非空NUll not null
假設設定為not null ,如果不給它指派,就會報錯!
NUII,如果不填寫值,預設就是null!
預設∶
設定預設的值!
sex,預設值為男,如果不指定該列的值,則會有預設的值!
拓展:(現在聽聽就好)
每一個表,都必須存在以下五個字段!未來做項目用的,表示一個記錄存在意義!
id主鍵
version樂觀鎖
is_delete 僞删除
gmt_create建立時間
gmt_update修改時間
05建立資料庫表(重點)
-- 目标:建立一個school資料庫
-- 學号int登入密碼varchar(20)姓名,性别varchar(2),出生日期(datatime) ,家庭住址, email
-- 注意點,使用英文(),表的名稱 和 字段盡量使用``括起來
-- AUTO_INCREMENT自增
-- 字元串使用單引号括起來
-- 所有的語句後面加逗号, 最後一個不用
-- PRIMARY KEY 主鍵,一般一個表隻有一個唯一的主鍵
CREATE TABLE IF NOT EXISTS `student1`(
`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 -- 顯示表的結構
再點選複制->複制單元格到剪切闆
也會出建立表的代碼
CREATE TABLE `student` (
`id` int NOT NULL COMMENT '學生ID',
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '學生姓名',
`age` int(3) unsigned zerofill NOT NULL COMMENT '學生年齡',
`sex` char(1) NOT NULL DEFAULT '女' COMMENT '學生性别',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
06MyIsam和InnoDB差別
-- 關于資料庫引擎
/*
INNODB 預設使用
MYISAM 早些年使用的
*/
MYISAM | INNODB | |
事務支援 | 不支援 | 支援 |
資料行鎖定 | 不支援(表鎖) | 支援(行鎖) |
外鍵限制 | 不支援 | 支援 |
全文索引 | 支援 | 不支援 |
表空間的大小 | 較小 | 較大,約為MYISAM兩倍 |
正常使用操作:
MYISAM 節約空間 速度較快
INNODB 安全性高,事務的處理,多表多使用者操作
在school資料庫中建立teacher新表
student1是引擎INNODB
teacher 是引擎MYISAM
進行對比
在實體空間存在的位置
所有資料庫檔案都在data目錄下,一個檔案夾就對應一個資料庫
本質還是檔案的存儲!
MySQL引擎在實體檔案上的差別
INNODB在資料庫中隻有一個*.frm檔案,以及上級目錄下的ibdata1檔案
MYISAM對應檔案
*.frm 表結構的定義檔案
*.MYD 資料檔案(data)
*.MYI 索引檔案(index)
設定資料庫表的字元集編碼
CHARSET = utf8
不設定的話,會是mysql預設的字元集編碼(不支援中文!)
MySQL的預設編碼是Latin1,不支援中文
在my.ini中配置預設的編碼
character-set-server = utf8
07修改和删除資料表字段
1.修改
-- 修改表名 :ALTER TABLE 舊表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段 :ALTER TABLE 表名 ADD 字段名 列屬性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段(重命名,修改限制!)
-- ALTER TABLE 表名 MODIFY 字段名 列屬性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改限制
-- ALTER TABLE 表名 CHANGE 舊名字 新名字 列屬性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
-- 删除表的字段 :ALTER TABLE 表名 DROP 列屬性[]
ALTER TABLE teacher1 DROP age1
差別modify跟change:
change用來字段重命名,不能修改字段類型和限制;
modify不用來字段重命名,隻能修改字段類型和約克;
2.删除
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS teacher1
所有的建立和删除操作盡量加上判斷,以免報錯
注意點:
` `字段名,使用這個包裹!
注釋 --
sql關鍵字大小寫不敏感
所有的符号全部用英文
09MySQL資料庫資料管理
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 `student1`(
`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_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
代碼跟視訊是一樣的,但是報錯。
錯誤代碼: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `student` (
`studentno` INT(4) NOT NULL COMMENT '學号',
`studentn' at line 10
删除有外鍵關系表的時候,必須要先删除引用别人的表(從表),再删除被引用的表(主表)
方式二:建立表成功後,添加外鍵限制
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 `student1`(
`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(作為外鍵的列) REFERENCES `哪個表`(`那個字段`);
依舊還是報錯,錯誤代碼: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `student1`(
`id` INT(4) NOT NULL AUTO_INCREMENT COM' at line 9
以上的操作都是實體外鍵,資料庫級别的外鍵,我們不建議使用!(避免資料庫過多造成困擾,這裡了解即可)
最佳實踐
資料庫就是單純的表,隻用來存資料,隻有行(資料)和列(字段)
我們想使用多張表的資料,想使用外鍵〔程式去實作)
2.DML語言(全部記住)
資料庫意義:資料存儲,資料管理
DML語言:資料操作語言
Insert
update
delete
3.添加
-- 插入語句(添加)
-- INSERT INTO `表名`(`字段名1`,`字段名2`,`字段名3`) VALUES ('值1'),('值2'),....(`值n`)
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主鍵自增我們可以省略(如果不寫表的字段,他就會一一比對)
INSERT INTO `grade` VALUE ('大三')`grade` -- 會報錯,因為比對不了
-- 一般寫插入語句,我們一定要和資料和字段一一對應
-- 插入多個字段
INSERT INTO `grade`(`gradename`)
VALUES ('大一'),('大二')
INSERT INTO `student1`(`name`,`pwd`,`sex`)VALUES('張三','111111','男')
INSERT INTO `student`
VALUES(4, '李四', 'aaaaa', '男', '2001-01-01', '廣州', '[email protected]')
-- 之前一直報錯的原因是:逗号後面一定要空格!!!
文法:INSERT INTO `表名`(`字段名1`,`字段名2`,`字段名3`) VALUES ('值1'),('值2'),....(`值n`)
注意事項:
1.字段和字段之間使用英文逗号隔開
2.字段是可以省略的,但是後面的值必須要要一一對應,不能少
3.可以同時插入多條資料,VALUES後面的值,需要使用,隔開即可VALUES(),()
4.修改
update 修改誰(條件) set原來的值 = 新值
-- 修改學員名字
UPDATE `student` SET `name` = '小張' WHERE id = 2;
-- 不指定條件的情況下,會改動所有表!
UPDATE`student` SET `name` = '長江七号'
-- 修改多個屬性,逗号隔開
UPDATE `student` SET `name`= '小張', `email` = '[email protected]' WHERE id = 2;
-- 文法:
-- UPDATE `表名` SET `colnum_name(列名)`= 'value', [`colnum_name(列名)`= 'value'] WHERE 條件;
條件:where 子句 運算符 id 等于某個值,大于某個值,在某個區間内修改
操作符會傳回布爾值
操作符 | 含義 |
= | 等于 |
<>或!= | 不等于 |
> | 大于 |
< | 小于 |
<= | 小于等于 |
>= | 大于等于 |
BETWEEN...AND... | 在某個範圍内 |
AND | 和 |
OR | 或 |
-- 通過多個條件定位資料
UPDATE `student` SET `name` = '張三' WHERE `name`='長江七号' AND sex = '男'
文法:UPDATE 表名 set colnum_name = value,[colnum_name = value,...]where [條件]
注意:
colnum_name是資料的列,盡量帶上``
條件,是篩選的條件,如果沒有指定,則會修改所有的列
value,是一個具體的值,也可以是一個變量
多個設定的屬性之間,使用英文逗号隔開
UPDATE `student` SET `birthday` = CURRENT_DATE WHERE`name`='張三' AND sex = '男'
視訊裡是CURRENT_TIME,但我使用這個報錯
5.删除
delete指令
文法:delete from 表名 [where 條件]
-- 删除指令(避免這樣寫,會全部删除)
DELETE FROM `student`
-- 删除指定資料
DELETE FROM `student` WHERE id = 1;
truncate指令
作用:完全清空一個資料庫表,表的結構和索引限制不會變!
-- 清空student表
TRUNCATE `student`
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` -- 不會影響自增
-- 再次運作insetr那條語句
TRUNCATE TABLE `test` -- 自增會歸零
了解即可:DELETE删除的問題,重新開機資料庫,現象
InnoDB 自增列會從1開始(存在記憶體當中的,斷電即失)
MyISAM 繼續從上一個自增量開始(存在檔案中的,不會丢失)
10DQL查詢資料(最重點)
1.DQL
(Data Query LANGUAGE:資料查詢語言)
所有的查詢操作都用它Select
簡單的查詢,複雜的查詢它都能做
資料庫中最核心的語言,最重要的語句
使用頻率最高的語句
CREATE DATABASE IF NOT EXISTS `sch`;
-- 建立一個sch資料庫
USE `sch`;
-- 如果存在grade表則删除
DROP TABLE IF EXISTS `grade`;
-- 建立年級表
CREATE TABLE `grade`(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年級編号',
`Gradename` VARCHAR(50) NOT NULL COMMENT '年級名稱',
PRIMARY KEY (`GradeID`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
INSERT INTO `grade` (`GradeID`, `GradeName`) VALUES (1,'大一'), (2,'大二'), (3,'大三'), (4,'大四'), (5,'預科班');
-- 建立成績表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
`StudentNo` INT(4) NOT NULL COMMENT '學号',
`SubjectNo` INT(4) NOT NULL COMMENT '課程編号',
`ExamDate` DATETIME NOT NULL COMMENT '考試日期',
`StudentResult` INT (4) DEFAULT NULL COMMENT '考試成績',
KEY `SubjectNo` (`SubjectNo`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 建立學生表
CREATE TABLE `student`(
`StudentNo` INT(4) NOT NULL COMMENT '學号',
`Loginpwd` VARCHAR(20) DEFAULT NULL,
`Studentname` VARCHAR(20) DEFAULT NULL COMMENT '學生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
`GradeID` INT(11) DEFAULT NULL COMMENT '年級編号',
`Phone` VARCHAR(50) NOT NULL COMMENT '聯系電話,不允許為空',
`Address` VARCHAR(255) DEFAULT NULL COMMENT '位址,允許為空',
`Borndate` DATETIME DEFAULT NULL COMMENT '出生時間',
`Email` VARCHAR (50) NOT NULL COMMENT '郵箱賬号不允許為空',
`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard`(`IdentityCard`),
KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
-- 建立科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`SubjectNo`INT(11) NOT NULL AUTO_INCREMENT COMMENT '課程編号',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '課程名稱',
`ClassHour` INT(4) DEFAULT NULL COMMENT '學時',
`GradeID` INT(4) DEFAULT NULL COMMENT '年級編号',
PRIMARY KEY (`subjectNo`)
)ENGINE = INNODB AUTO_INCREMENT = 18 DEFAULT CHARSET = utf8;
2.指定查詢字段
-- 查詢全部的學生 SELECT * 字段 FROM 表
SELECT * FROM student
-- 查詢指定字段
SELECT `StudentNo`,`Studentname` FROM student
-- 别名,給結果起一個名字 AS
SELECT `StudentNo` AS 學号,`Studentname` AS 學生姓名 FROM student
-- 函數 CONCAT(a,b)
SELECT CONCAT('姓名:',`Studentname`) AS 新名字 FROM student
文法:SELECT字段,... FROM表
有的時候,列名字不是那麼的見名知意。我們起别名AS字段名 as 别名―表名as 别名
去重distinct
作用:去除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表
3.where條件子句
作用:檢索資料中符合條件的值
邏輯運算符
運算符 | 文法 | 描述 |
and && | a and b a && b | 邏輯與,兩個都為真,結果為真 |
or || | a or b a || b | 邏輯或,其中一個為真,則結果為真 |
Not ! | not a !a | 邏輯非,真為假,假為真! |
邏輯運算符盡量使用英文字母,而不使用符号
-- where
SELECT `StudentNo`,`StudentResult` FROM result
-- 查詢考試成績在 95-100分之間
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100
-- and &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100
-- 模糊查詢(區間)
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100
-- 除了學号1000學生以外的同學成績
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- != not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
模糊查詢:比較運算符
運算符 | 文法 | 描述 |
IS NULL | a is null | 如果操作符為null,結果為真 |
IS NOT NULL | a is not null | 如果操作符不為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...其中的某一個值中,結果也為真 |
-- 模糊查詢(區間)
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult` BETWEEN 95 AND 100
-- 除了學号1000學生以外的同學成績
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- != not
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
-- 模糊查詢
-- 查詢姓劉的同學
-- 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` IN (1001,1002,1003);
-- 查詢在汕頭、廣東肇慶的學生
SELECT `StudentNo`,`Studentname` FROM `student`
WHERE `Address` IN ('汕頭','廣東肇慶');
-- 在這裡不能用%肇慶% 原因是% _搭配的是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.聯表查詢
join 對比
-- 連表查詢 join
-- 查詢參加了考試的同學(學号,姓名,科目編号,分數)
SELECT * FROM student
SELECT * FROM result
/*
思路
1.分析需求,分析查詢的字段來自哪些表(連接配接查詢)
2.确定使用哪種連接配接查詢?7種
缺點交叉點(這兩個表有哪些資料是相同的)
判斷的條件:學生表中的 studentno = 成績表 studentno
*/
-- join(連接配接的表) on(判斷的條件)連接配接查詢
-- where 等值查詢
-- 有重複資料時,一定要标注好是哪個表的屬性,否則會報異常,“模棱兩可”
SELECT s.`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` s -- 可以不加as
RIGHT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo` -- where可以改成on 一樣的
-- Left Join
SELECT s.`StudentNo`,`Studentname`,`SubjectNo`,`StudentResult`
FROM `student` s -- 可以不加as
LEFT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo` -- where可以改成on 一樣的
-- 查詢缺考的同學
SELECT s.`StudentNo`,`Studentname`,`SubjectNo`,`StudentResult`
FROM `student` s
LEFT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
WHERE `StudentResult` IS NULL
操作 | 描述 |
Inner join | 如果表中至少有一個比對就傳回行 |
left join | 會從左表中傳回所有的值,即使右表中沒有比對 |
right join | 會從右表中傳回所有的值,即使左表中沒有比對 |
-- 思考題(查詢了參加考試的同學資訊:學号,學生姓名,科目名,分數)
/*
思路:
1.分析需求,分析查詢的字段來自哪些表(連接配接查詢)
2.确定使用哪種連接配接查詢? 7種
确定交叉點(三個表中有哪些資料是相同的)
判斷條件:學生表中的 studentno = 成績(result)表 studentno
學科表中的 subjectno = 成績表的subjectno
*/
SELECT s.`StudentNo`,`Studentname`,`SubjectName`,`StudentResult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
-- 我要查詢哪些資料 select...
-- 從那幾個表中查from表 *** join 連接配接的表 on 交叉條件
-- 假設存在一種多張表查詢,慢慢來,先查詢兩張表然後再慢慢增加
5.自連接配接(了解)
自己的表和自己的表連接配接,核心:一張表拆分為兩張一樣的表即可
父類
categoryid | categoryName |
2 | 資訊技術 |
3 | 軟體開發 |
5 | 美術設計 |
子類
pid | categoryid | categoryName |
3 | 4 | 資料庫 |
2 | 8 | 辦公資訊 |
3 | 6 | web開發 |
5 | 7 | ps技術 |
操作:查詢父類對應的子類關系
父類 | 子類 |
資訊技術 | 辦公資訊 |
軟體開發 | 資料庫 |
軟體開發 | web開發 |
美術設計 | ps技術 |
插入sch這個庫
-- 自連接配接
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主題id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主題名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
INSERT INTO `category`(`categoryid``category`,`pid`,`categoryName`)
VALUES('2','1','資訊技術'),
('3','1','軟體開發'),
('4','3','資料庫'),
('5','1','美術設計'),
('6','3','web開發'),
('7','5','ps技術'),
('8','2','辦公資訊');
SELECT a.`categoryName` AS '父欄目',b.`categoryName` AS '子欄目'
FROM `category` AS a,
`category` AS b
WHERE a.`categoryid` = b.`pid`
即可得出父類子類對應的圖
練習
-- 查詢學員所屬的年紀(學号,學生的姓名,年級名稱)
SELECT `StudentNo`,`Studentname`,`Gradename`
FROM student s
INNER JOIN `grade` g
ON s.`GradeID` = g.`GradeID`
-- 查詢科目所屬的年紀(科目名稱,年紀名稱)
SELECT `SubjectName`,`Gradename`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeID` = g.`GradeID`
-- 查詢了參加 高等數學-1 考試的同學資訊 考試的同學資訊:學号,學生姓名,科目名,分數
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'
select語句的順序
6.分頁和排序
-- 分頁 limit 和排序 order by
-- 排序:升序 ASC 降序 DESC
-- 查詢的結果根據 成績降序 排序
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` DESC
-- 100萬
-- 為什麼要分頁?
-- 緩解資料庫壓力,給人的體驗更好, 瀑布流
-- 分頁:每頁隻顯示五條資料
-- 文法:limit 目前頁,頁面的大小
-- 網頁應用:目前,總的頁數,頁面大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 2~6
-- 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 1,5
-- 第一頁 limit 0,5 (1-1)*5
-- 第二頁 limit 5,5 (2-1)*5
-- 第三頁 limit 10,5 (3-1)*5
-- 第四頁 limit n,pageSize (n-1)*pageSize,pageSize
-- pageSize:頁面大小
-- (n-1)*pageSize:起始值
-- n目前頁
-- 資料總數/頁面大小 = 總頁數
-- 思考:
-- 查詢 高等數學-1 課程成績排名前十的學生,并且分數要大于80 的學生資訊(學号, 姓名,課程名稱,分數)
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' AND `StudentResult` >= 80
ORDER BY `StudentResult` DESC
LIMIT 0,10
7.子查詢
where(這裡面是計算出來的)
本質︰在where語句中嵌套一個子查詢語句
-- 回顧之前内容 inner join
-- 查詢課程為 高等數學-1 且分數不小于80的同學的學号和姓名
SELECT s.`StudentNo`,`Studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等數學-1' AND `StudentResult`>=80
-- 引入子查詢
-- 分數不小于80分的學生的學号和姓名
SELECT DISTINCT s.`StudentNo`,`Studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult` >= 80
-- 在這個基礎上增加一個學科,高等數學-1
-- 查詢 高等數學-1 的編号
SELECT DISTINCT s.`StudentNo`,`Studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult` >= 80 AND `SubjectNo`=(
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` = '高等數學-1'
LIMIT 1
-- 在子查詢條件語句加limit 1,找到一個符合條件的就可以了
)
-- 視訊跟上面是一樣的,但是我運作不了,在此,我加了limt 1
-- 這個是我自己改了之後發現可行的方法 不用加limt 1
SELECT DISTINCT s.`StudentNo`,`Studentname`
FROM `student` s
INNER JOIN `result` r
ON r.`StudentNo` = s.`StudentNo`
WHERE `StudentResult` >= 80 AND `SubjectNo`IN(
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` = '高等數學-1'
)
-- 再改造(由裡及外)
SELECT `StudentNo`,`Studentname` FROM `student` WHERE `StudentNo` IN(
SELECT `StudentNo` FROM `result` WHERE `StudentResult`>=80 AND `SubjectNo` = (
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等數學-1'
LIMIT 1
-- 上面這句視訊也沒有
)
)
8.MySQL常用函數
函數查詢官網:MySQL :: MySQL 8.0 Reference Manual :: 12.1 Built-In Function and Operator Reference
8.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 ('傳回字元串長度')
SELECT CONCAT('拼','接','字元串')
SELECT INSERT('我愛程式設計hello world',1,2,'超級熱愛') -- 查詢,從某個位置開始替換某個長度
SELECT LOWER('LEY') -- 小寫字母
SELECT UPPER('ley') -- 大寫字母
SELECT REPLACE('行而不辍,未來可期','未來','來日') -- 替換出現的指定字元串
SELECT SUBSTR('行而不辍,未來可期',6)
-- 兩個方式顯示的結果都是一樣的,包含六到九的字元串
SELECT SUBSTR('行而不辍,未來可期',6,9) -- 傳回指定字元串(源字元串,截取的位置,截取的長度)
SELECT REVERSE('654321') -- 反轉
-- 查詢姓張的同學名字 ,并改為 王
SELECT REPLACE (`Studentname`,'張','王') FROM `student`
WHERE `Studentname` LIKE '張%'
-- 時間和日期函數(記住)
SELECT CURRENT_DATE() -- 擷取目前日期
SELECT CURDATE() -- 擷取目前日期 跟上面一樣的
SELECT NOW() -- 擷取目前的時間 傳回日期跟時間
SELECT LOCALTIME() -- 本地日期跟時間
SELECT SYSDATE() -- 系統日期跟時間
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系統
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
8.2聚合函數(常用)
函數名稱 | 描述 |
COUNT() | 計數 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
-- 聚合函數
-- 都能統計表中的資料(想查詢一個表中有多少個記錄,就用這個count())
SELECT COUNT(`Borndate`) FROM `student`; -- count(字段),會忽略所有的null值
SELECT COUNT(*) FROM `student`; -- count(*),不會忽略null值,本質 計算行數
SELECT COUNT(1) FROM `result`; -- count(1),不會忽略所有的null值 本質 計算行數
SELECT SUM(`StudentResult`) AS '總和' FROM result
SELECT AVG(`StudentResult`) AS '平均分' FROM result
SELECT MAX(`StudentResult`) AS '最高分' FROM result
SELECT MIN(`StudentResult`) AS '最低分' FROM result
9.分組和過濾
-- select完整語句
SELECT 屬性
FROM 表
WHERE 将符合條件的資料行摘取出來。生成一張新的臨時表
GROUP BY 通過什麼字段分類
HAVING 過濾掉group by生成的不符合條件
-- where 隻能用一次 having可以多于一次
ORDER BY 條件 按照條件進行排序
LIMIT
-- 查詢不同課程的平均分,最高分,最低分,平均分大于80
-- 核心:(根據不同的課程分組)
SELECT `SubjectName`,AVG(`StudentResult`) AS '平均分',MAX(`StudentResult`) AS '最高分',MIN(`StudentResult`) AS '最低分'
FROM `result` r
INNER JOIN `subject` sub
ON r.`SubjectNo`= sub.`SubjectNo`
GROUP BY r.`SubjectNo` -- 通過什麼字段來分組
HAVING 平均分 >80 -- 這裡不能用where,要按照select語句的順序
10.Select小結
select完整語句的順序很重要:
select去重要查詢的字段
from表(注意:表和字段可以取别名)xxx
join要連接配接的表 on等值判斷
where(具體的值,子查詢語句)
Group By(通過哪個字段來分組)
Having(過濾分組後的資訊,條件和where是一樣的,位置不同)
Order By ..(通過哪個字段排序)[升序/降序]
Limit startIndex, pagesize
業務層面:
查詢:跨表,跨資料庫等
11.資料庫級别的MD5加密(擴充)
什麼是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,'zhangsan','123456'),(2,'lisi',123456),(3,'wangwu','123456')
-- 加密
UPDATE `testmd5` SET pwd = MD5(pwd) WHERE id = 1
UPDATE `testmd5`SET PWD = MD5(PWD) -- 加密全部的密碼
-- 插入的時候加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'))
-- 如何校驗:将使用者傳遞進來的密碼,進行md5加密,然後比對加密後的值
SELECT * FROM `testmd5` WHERE `name`='xiaoming' AND `pwd`=MD5('123456')
12.事務
12.1什麼是事務
要麼都成功,要麼都失敗
事務原則:ACID原則 (原子性,一緻性,隔離性,持久性)
參考部落格連結:事務ACID了解_dengjili的專欄-CSDN部落格_acid事務管理(ACID)談到事務一般都是以下四點原子性(Atomicity) 原子性是指事務是一個不可分割的工作機關,事務中的操作要麼都發生,要麼都不發生。 一緻性(Consistency) 事務前後資料的完整性必須保持一緻。 隔離性(Isolation) 事務的隔離性是多個使用者并發通路資料庫時,資料庫為每一個使用者開啟的事務,不能被其他事務的操作資料所幹擾,多個并發事務之間要互相隔...
https://blog.csdn.net/dengjili/article/details/82468576
原子性(Atomicity)
要麼都發生,要麼都不發生
一緻性(Consistency)
事務前後的資料完整性要保證一緻
持久性(Durability)--事務送出
事務一旦送出則不可逆,被持久化到資料庫中
隔離性(Isolation)
事務的隔離性是多個使用者并發通路資料庫時,資料庫為每一個使用者開啟的事務,不能被其他事務的操作資料所幹務之間要互相隔離。
隔離所導緻的問題
髒讀:
指一個事務讀取了另外一個事務未送出的資料。
不可重複讀:
在一個事務内讀取表中的某一行資料,多次讀取結果不同。(這個不一定是錯誤,隻是某些場合不對)
虛讀(幻讀):
是指在一個事務内讀取到了别的事務插入的資料,導緻前後讀取不一緻。
12.2模拟場景
-- 事務
-- mysql是預設開啟事務自動送出的
SET autocommit = 0/*關閉*/
SET autocommit = 1/*開啟(預設的)*/
-- 手動處理事務
-- 事務開啟
START TRANSACTION -- 标記一個事務的開始,從這個之後的sql都在同一個事務内
INSERT **
INSERT **
-- 送出:持久化(成功!)
COMMIT
-- 復原:回到原來的樣子(失敗!)
ROLLBACK
-- 事務結束
SET autocommit = 1 -- 開啟自動送出
-- 了解
SAVEPOINT 儲存點名 -- 設定一個事務的儲存點
ROLLBACK TO SAVEPOINT 儲存點名 -- 復原到儲存點
RELEASE SAVEPOINT 儲存點名 -- 撤銷儲存點
12.3測試事務實作轉賬
-- 轉賬
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.00),('B',10000.00)
-- 模拟轉賬:事務
SET autocommit = 0; -- 關閉自動送出
START TRANSACTION -- 開啟一個事務
UPDATE account SET money = money - 500 WHERE `name` = 'A' -- A減500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B減500
COMMIT; -- 送出事務,就被持久化了
ROLLBACK;-- 復原
SET autocommit = 1;-- 恢複預設值
13索引
MySQL官方對索引的定義為:索引(Index)是幫助MySQL高效擷取資料的資料結構。
提取句子主幹,就可以得到索引的本質:索引是資料結構。
13.1索引的分類
在一個表中,主鍵索引隻能有一個,唯一索引可以有多個
主鍵索引(PRIMARY KEY)
唯一的辨別,主鍵不可重複,隻能有一個列作為主鍵
唯一索引(UNIQUE KEY)
避免重複的列出現,唯一索引可以重複,多個列都可以辨別位唯一索引
正常索引(KEY/INDEX)
預設的,index/key關鍵字來設定
全文索引(FullText)
在特定的資料庫引擎下才有,MylSAM。快速定位資料
基礎文法
-- 索引的使用
-- 1、在建立表的時候給字段增加索引
-- 2、建立完畢後,增加索引
-- 顯示所有的索引資訊
SHOW INDEX FROM student
-- 增加一個全文索引(索引名) 列名
ALTER TABLE student ADD FULLTEXT INDEX `Studentname`(`studentName`)
-- EXPLAIN 分析sql執行的狀況
EXPLAIN SELECT * FROM student;-- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('王');
MySQL建立全文索引 - 周國偉 - 部落格園
13.2測試索引
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '使用者昵稱',
`email` VARCHAR(50) NOT NULL COMMENT '使用者郵箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手機号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男 1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密碼',
`age` TINYINT(4) DEFAULT '0' COMMENT '年齡',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app使用者表'
-- 插入十萬條資料
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`)
VALUES(CONCAT('使用者',i),'[email protected]',CONCAT('17',FLOOR(RAND()*((999999999-100000000)+100000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
SET i = i+1;
END WHILE;
RETURN i;
END;
-- 報1418錯誤 補充
SET @@global.log_bin_trust_function_creators='ON';
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SELECT mock_data();
SELECT * FROM app_user WHERE `name` = '使用者9999';
EXPLAIN SELECT * FROM app_user WHERE `name`= '使用者9999';
-- 索引名:id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '使用者9999';
EXPLAIN SELECT * FROM app_user WHERE `name`= '使用者9999';
怎麼解決執行MySQL函數時缺少權限:錯誤代碼1418 方法參考如下↓https://jingyan.baidu.com/article/59a015e30e83b1f795886567.html
在建立時間字段的時候
DEFAULT CURRENT_TIMESTAMP
表示當插入資料的時候,該字段預設值為目前時間
ON UPDATE CURRENT_TIMESTAMP
表示每次更新這條資料的時候,該字段都會更新成目前時間
這兩個操作是mysql資料庫本身在維護,是以可以根據這個特性來生成【建立時間】和【更新時間】兩個字段,且不需要代碼來維護
如何删除索引
索引在小資料量的時候,使用者不大,但是在大資料的時候,差別十分明顯
13.3索引原則
索引不是越多越好
不要對進行變動資料加索引
小資料量的表不需要加索引
索引一般加在常用來查詢的字段上!
索引的資料結構
Hash類型的索引
Btree : lnnoDB的預設資料結構
閱讀 CodingLabs - MySQL索引背後的資料結構及算法原理
目前我們學習的内容---業務級别MySQL學習
除此之外還有---運維級别MySQL
14資料庫備份
14.1使用者管理
Sqlyog可視化管理
SQL指令操作
使用者表: mysql.user
本質:讀這張表進行增删改查
-- 建立使用者
CREATE USER ley IDENTIFIED BY '123456'
-- 修改密碼(修改目前使用者密碼) 目前使用者密碼是[email protected]
SET PASSWORD = PASSWORD('123456')
-- 修改密碼(修改指定使用者密碼)
SET PASSWORD FOR ley = PASSWORD('111111')
-- 重命名
RENAME USER ley TO ley1
-- 使用者授權 all privileges 全部的權限 庫/表
-- all privileges 除了給别人授權,其他都能夠幹
GRANT ALL PRIVILEGES ON *.* TO ley1
-- 查詢權限
SHOW GRANTS FOR ley1 -- 檢視指定使用者的權限
SHOW GRANTS FOR [email protected]
-- root使用者權限:GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
-- 撤銷權限,revoke 哪些權限,在哪個庫撤銷,給誰撤銷
REVOKE ALL PRIVILEGES ON *.* FROM ley1
14.2MySQL備份
為什麼要備份:
保證重要的資料不丢失
資料轉移
MySQL 資料庫備份的方式
直接拷貝實體檔案
在Sqlyog這種可視化工具中手動導出
使用指令行導出mysqldump 指令行使用(cmd)
window+r->cmd
#mysqldump - 主機 -u使用者名 -p密碼 資料庫 表名 > 實體磁盤位置/檔案名
mysqldump - hlocalhost -uroot -proot sch student >D:/a.sql
#mysqldump - 主機 -u使用者名 -p密碼 資料庫 表1 表2 表3 > 實體磁盤位置/檔案名
mysqldump - hlocalhost -uroot -proot sch student grade >D:/b.sql
#mysqldump - 主機 -u使用者名 -p密碼 資料庫 表名 > 實體磁盤位置/檔案名
mysqldump - hlocalhost -uroot -proot sch >D:/c.sql
#導入
#登入的情況下,切換到指定的資料庫
#source 備份檔案
source d:/a.sql
#沒登陸的情況下
mysql -u使用者名 -p密碼 庫名 < 備份檔案(注意是“<”)
備份
假設你要備份資料庫,防止資料丢失。把資料庫給朋友,把 sql檔案給别人即可!
15規範資料庫設計
15.1為什麼需要設計
糟糕的資料庫設計:
資料備援,浪費空間
資料庫插入和删除都會麻煩、異常【屏蔽使用實體外鍵】
程式的性能差
良好的資料庫設計:
節省記憶體空間
保證資料庫的完整性
友善我們開發系統
軟體開發中,關幹資料庫的設計
分析需求:分析業務和需要處理的資料庫的需求
概要設計:設計關系圖E-R圖
設計資料庫的步驟:(個人部落格)
收集資訊,分析需求
使用者表(使用者登入登出,使用者的個人資訊,寫部落格,建立分類)
分類表(文章分類,誰建立的)
文章表(文章的資訊)
友連結清單(友鍊資訊)
自定義表(系統資訊,某個關鍵的字,或者一些主字段)key : value
說說表(發表心情.. id... content....create_time)
辨別實體(把需求落地到每個字段)
寫部落格:user --> blog
建立分類:user -> category
關注: user ->user
友鍊:links
評論: user-user-blog
15.2三大範式
為什麼需要資料規範化?
資訊重複
更新異常
插入異常
無法正常顯示資訊
删除異常
丢失有效的資訊
三大範式
第一範式(1NF)
原子性:保證每一列不可再分
第二範式(2NF)
前提:滿足第一範式
每張表隻描述一件事情
第三範式(3NF)
前提:滿足第一範式和第二範式
第三範式需要確定資料表中的每一列資料都和主鍵直接相關,而不能間接相關。
(規範資料庫的設計)
規範性和性能的問題
關聯查詢的表不得超過三張表
考慮商業化的需求和目标,(成本,使用者體驗! )資料庫的性能更加重要
在規範性能的問題的時候,需要适當的考慮一下規範性!
故意給某些表增加一些備援的字段。(從多表查詢中變為單表查詢)
故意增加一些計算列(從大資料量降低為小資料量的查詢:索引)
資料庫三大範式-CSDN部落格_資料庫三範式
16JDBC
16.1資料庫驅動
驅動:聲霸卡,顯示卡、資料庫
16.2JDBC
SUN公司為了簡化開發人員的(對資料庫的統一)操作,提供了一個(Java操作資料庫的)規範,俗稱JDBC
這些規範的實作由具體的廠商去做
對于開發人員來說。我們隻需要掌握JDBC接口的操作即可!
java.sql
javax.sql
還需要導入一個資料庫驅動包mysql-connector-java-5.1.47.jar
16.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','[email protected]','1980-12-04'),
(2,'LISI','123456','[email protected]','1991-12-04'),
(3,'WANGWU','123456','[email protected]','1979-10-1');
SELECT
1.建立一個普通項目
import java.sql.*;
import java.util.Arrays;
public class JDBCTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加載驅動
Class.forName("com.mysql.cj.jdbc.Driver");//固定寫法
//2.使用者資訊和url
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true";
String username = "root";
String password = "root";
//3.連接配接成功,資料庫對象 Connection 代表資料庫
Connection connection = DriverManager.getConnection(url,username,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("pwd = " + resultSet.getObject("Password"));
System.out.println("Email = " + resultSet.getObject("Email"));
System.out.println("birth = " + resultSet.getObject("Birthday"));
}
//6.釋放連接配接
resultSet.close();
statement.close();
connection.close();
}
}
com.mysql.cj.jdbc.Driver和com.mysql.jdbc.Driver的差別_程式和我有一個能跑就行了-CSDN部落格_com.mysql.cj.jdbc.driver今天寫東西測試的時候發現一個問題,如下:application.yml中資料源是這樣配置的:第一反應就是記憶中連接配接mysql的驅動不都是com.mysql.jdbc.Driver嗎?com.mysql.cj.jdbc.Driver是什麼鬼?後來檢視了一下才知道 這個跟驅動的依賴版本有關<dependency><groupId>mysql</grou...
https://blog.csdn.net/weixin_43770545/article/details/90486809
2.導入資料庫驅動
JDBC驅動jar包的下載下傳&導入IDEA_ViVi_Z的部落格-CSDN部落格
步驟總結:
1、加載驅動
2、連接配接資料庫 DriverManager
3、獲得執行sql的對象Statement
4、獲得傳回的結果集
5、釋放連接配接
16.4JDBC對象詳解
1)DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver");//固定寫法
//3.連接配接成功,資料庫對象 Connection 代表資料庫
Connection connection = DriverManager.getConnection(url,username,password);
//資料庫設定自動送出
connection.rollback();
//事務送出
connection.commit();
//事務滾回
connection.setAutoCommit();
2)URL
String url = "jdbc:mysql://localhost:3306/jdbcStudy?
useUnicode = true & characterEncoding = utf8 && useSSL = true";
mysql預設端口 3306
協定://主機位址:端口号/資料庫名?參數1&參數2&參數3
Oracle預設端口 1521
//jdbc:oracle:thin:@localhost:1521:sid
3)Statement執行SQL的對象 PrepareStatement 執行SQL的對象
String sql = "SELECT * FROM users"; //編寫SQL
statement.executeQuery();//查詢操作傳回ResultSet
statement.execute();//執行任何SQL
statement.executeUpdate();//更新、插入、删除都是用這個,傳回一個受影響的行數
4)ResultSet查詢的結果集:封裝了所有的查詢結果
獲得指定的資料類型
resultSet.getObject();//在不知道列類型的情況下使用
//如果知道列的類型就使用指定的類型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
...
周遊,指針
resultSet.beforeFirst();//移動到最前面
resultSet.afterLast();//移動到最後面
resultSet.next();//移動到下一個資料
resultSet.previous();//移動到前一行
resultSet.absolute(row);//移動到指定行
5)釋放資源
//6.釋放連接配接
resultSet.close();
statement.close();
connection.close();//耗資源,用完關掉
16.5statement對象
jdbc中的statement對象用于向資料庫發送SQL語句,想完成對資料庫的增删改查,隻需要通過這個對象向資料庫發送增删改查語句即可
Statement對象的executeUpdate方法,用于向資料庫發送增、删、改的sql語句,executeUpdate執行完後,将會傳回一個結果集(即增删改語句導緻了資料庫幾行資料發生了變化)。
Statement.executeQuery方法用于向資料庫發送查詢語句,executeQuery方法傳回代表查詢結果的ResultSet對象。
CRUD操作-create
使用executeUpdate(String sql)方法完成資料添加操作,示例操作:
statement st = conn.createstatement();
string sql = "insert into user(.... ) values (.... .) ";
int num = st.executeupdate(sql);
if(num>0){
system.out.println("插入成功! ! ! ");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成資料删除操作,示例操作:
statement st = conn.createstatement();
string sql = "update user set name='' where name=' " ";
int num = st.executeupdate(sql);
if(num>0){
system.out.println("删除成功! ! ! ");
}
CRUD操作-read
使用executeQuery(String sql)方法完成資料查詢操作,示例操作:
statement st = conn.createstatement();
string sql = "select * from user where id=1";
ResultSet rs = st.execute(sql);
int num = st.executeupdate(sql);
while(rs.next(){
//根據擷取列的資料類型,分别調用rs的相應方法映射到java對象中
}
3
1.提取工具類
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true
username = root
password = root
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");
} catch (IOException e) {
e.printStackTrace();
}
}
//擷取連接配接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//釋放連接配接資源
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.編寫增删改的方法 executeUpdate
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
st = conn.createStatement();//獲得SQL的執行對象
String sql = "INSERT INTO users(`ID`,`Name`,`Password`,`Email`,`Birthday`)" +
"VALUES(4,'LEY','123456','[email protected]','2020-01-01')";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
st = conn.createStatement();//獲得SQL的執行對象
String sql = "DELETE FROM users WHERE id = 4";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("删除成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
st = conn.createStatement();//獲得SQL的執行對象
String sql = "UPDATE users SET `Name` = 'LEY',`Email` = '[email protected]' WHERE ID = 1";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("更新成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
st = conn.createStatement();//獲得SQL的執行對象
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);//查詢完畢會傳回一個結果集
while (rs.next()){
System.out.println(rs.getString("Name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
SQL注入問題
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) throws SQLException {
//login("LEY","123456");
//這個登入是沒有問題的
login("'' OR '1=1'","'' OR '1=1'");//運作時異常
}
//登陸業務
public static void login(String username,String password) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
st = conn.createStatement();
//SELECT * FROM users WHERE `Name` = 'LEY' AND `Password` = '123456';
//SELECT * FROM users WHERE `Name` = '' OR '1=1' AND `Password` = '' OR '1=1';
String sql = "select * from users where `Name`='"+ username + "' AND `Password` = '" + password + "'";
rs = st.executeQuery(sql);//查詢完畢會傳回一個結果集
while (rs.next()){
System.out.println(rs.getString("Name"));
System.out.println(rs.getString("Password"));
System.out.println("-----------------");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
16.6PreparedStatement對象
PreparedStatement 可以防止SQL注入,效率更高
1、新增
import java.sql.*;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
//差別:使用?占位符代替參數位置
String sql = "INSERT INTO users(`ID`,`Name`,`Password`,`Email`,`Birthday`) VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//預編譯SQL,先寫sql,然後不執行
//手動給參數指派
st.setInt(1,4);//ID
st.setString(2,"ly");
st.setString(3,"123456");
st.setString(4,"[email protected]");
/*
注意點:sql.Date 資料庫 java.sql.Date()
util.Date Java new Date().getTime() 獲得時間戳
*/
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,null);
}
}
}
2、删除
import java.sql.*;
public class TestDelete {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
String sql = "DELETE FROM users WHERE id = ?";
st = conn.prepareStatement(sql);
st.setInt(1,4);
//注意這裡沒有帶入sql
int i = st.executeUpdate();
if (i > 0){
System.out.println("删除成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
3、更新
import java.sql.*;
public class TestUpdate {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
String sql = "UPDATE users SET `Name` = ?,`Email` = ? WHERE ID = ?";
st = conn.prepareStatement(sql);
st.setString(1,"LLy");
st.setString(2,"[email protected]");
st.setInt(3,1);
int i = st.executeUpdate();
if (i > 0){
System.out.println("更新成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
4、查詢
import java.sql.*;
public class TestSelect {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();//擷取資料庫連接配接
String sql = "select * from users where id = ?";
st = conn.prepareStatement(sql);//預編譯
st.setInt(1,1);//傳遞參數
rs = st.executeQuery();//執行
while (rs.next()){
System.out.println(rs.getString("Name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
sql注入問題優化(防止sql注入)
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) throws SQLException {
login("LLy","123456");//顯示結果
login("'' OR '1=1'","'' OR '1=1'");//不顯示結果
}
//登陸業務
public static void login(String username,String password) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
// PreparedStatement防止SQL注入的本質,把傳遞進來的參數當作字元
// 假設其中存在轉義字元,比如說' 會被直接轉義
String sql = "select * from users where `Name`=? and `Password` = ?";//mybaits
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();//查詢完畢會傳回一個結果集
while (rs.next()){
System.out.println(rs.getString("Name"));
System.out.println(rs.getString("Password"));
System.out.println("-----------------");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
16.6使用IDEA連接配接資料庫
打開IDEA
連接配接成功,可以選擇資料庫
更新資料
16.7JDBC操作事務
要麼都成功,要麼都失敗
ACID原則
原子性:要麼全部完成,要麼都不完成
一緻性:總數不變
隔離性:多個程序互不幹擾
持久性:一旦送出不可逆,持久化到資料庫了
隔離性的問題:
髒讀:一個事務讀取了另一個沒有送出的事務
不可重複讀:在同一個事務内,重複讀取表中的資料,表資料發生了改變不可重複讀:
虛讀(幻讀)︰在一個事務内,讀取到了别人插入的資料,導緻前後讀出來結果不一緻
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
//關閉資料庫的自動送出,自動會開啟事務
conn.setAutoCommit(false);//開啟事務
String sql1 = "update account set money = money - 100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
//int a = 1/0;//報錯 正常來說上一條語句是會被執行完畢的,但是用了復原,并沒有變化
String sql2 = "update account set money = money + 100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//業務完畢,送出事務
conn.commit();
System.out.println("成功!");
} catch (SQLException throwables) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JDBCUtils.release(conn,st,rs);
}
}
}
代碼實作
1、開啟事務conn.setAutocommit(false);
2、一組業務執行完畢,送出事務
3、可以在catch語句中顯示的定義復原語句,但預設失敗就會復原
16.8 資料庫連接配接池
資料庫連接配接---執行完畢---釋放
連接配接--釋放十分浪費系統資源
池化技術:準備一些預先的資源,過來就連接配接預先準備好的
最小連接配接數: 10
最大連接配接數:15
等待逾時:100ms
編寫連接配接池,實作一個接口DataSource
開源資料源實作
DBCP
C3P0
Druid:阿裡巴巴
使用了這些資料庫連接配接池之後,我們在項目開發中就不需要編寫連接配接資料庫的代碼了!
DBCP
需要用到的jar包
commons-dbcp-1.4、commons-pool-1.6(依版本而定)
dbcpconfig.properties
#連接配接設定
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 && useSSL = true
username=root
password=root
#
initialSize=10
#最大連接配接數量
maxActive=50
#
maxIdle=20
#
minIdle=5
#
maxWait=60000
#JDBC驅動建立連接配接時附帶的連接配接屬性屬性的格式必須為這樣:[屬性名=property;]
#注意:“user” 與 “password” 兩個屬性會被明确地傳遞,是以這裡不需要包含他們。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由連接配接池所建立的連接配接的自動送出(auto-commit)狀态。
defaultAutoCommit=true
#driver default 指定由連接配接池所建立的連接配接的事務級别(TransactionIsolation)。
#可用值為下列之一:(詳情可見javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//建立資料源 工廠模式-->建立
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//擷取連接配接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//釋放連接配接資源
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();
}
}
}
import java.sql.*;
import java.util.Date;
public class TestJDBC_DBCP {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_DBCP.getConnection();//擷取資料庫連接配接
//差別:使用?占位符代替參數位置
String sql = "INSERT INTO users(`ID`,`Name`,`Password`,`Email`,`Birthday`) VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//預編譯SQL,先寫sql,然後不執行
//手動給參數指派
st.setInt(1,4);//ID
st.setString(2,"ly");
st.setString(3,"123456");
st.setString(4,"[email protected]");
/*
注意點:sql.Date 資料庫 java.sql.Date()
util.Date Java new Date().getTime() 獲得時間戳
*/
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,st,null);
}
}
}
C3P0
需要用到的jar包
c3p0-0.9.5.5、mchange-commons-java-0.2.19
結論
無論使用什麼資料源,本質還是一樣的,DataSource接口不會變,方法就不會變
JdbcUtils_C3P0
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<!--
C3P0的預設(預設)配置:
如果在代碼中"ComboPooledDataSource ds = new ComboPooledDataSource();"
這樣寫就代表使用的是C3P0的預設配置
-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true &characterEncoding = utf8</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxIdleTime">10</property>
<property name="maxPoolSize">5</property>
<property name="minPoolSize">20</property>
</default-config>
<!--
C3P0的預設(預設)配置:
如果在代碼中"ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");"
這樣寫就代表使用的是name為mysql配置
-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true &characterEncoding = utf8</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxIdleTime">10</property>
<property name="maxPoolSize">5</property>
<property name="minPoolSize">20</property>
</named-config>
</c3p0-config>
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try {
//代碼版配置
// dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
dataSource = new ComboPooledDataSource();
//建立資料源 工廠模式-->建立
//dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//擷取連接配接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();//從資料源中擷取連接配接
}
//釋放連接配接資源
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();
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
public class TestC3P0 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils_C3P0.getConnection();//擷取資料庫連接配接
//差別:使用?占位符代替參數位置
String sql = "INSERT INTO users(`ID`,`Name`,`Password`,`Email`,`Birthday`) VALUES(?,?,?,?,?)";
st = conn.prepareStatement(sql);//預編譯SQL,先寫sql,然後不執行
//手動給參數指派
st.setInt(1,4);//ID
st.setString(2,"ly");
st.setString(3,"123456");
st.setString(4,"[email protected]");
/*
注意點:sql.Date 資料庫 java.sql.Date()
util.Date Java new Date().getTime() 獲得時間戳
*/
st.setDate(5,new java.sql.Date(new Date().getTime()));
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_C3P0.release(conn,st,null);
}
}
}
開源平台
Druid
Apache