柚子快報邀請碼778899分享：數(shù)據(jù)庫 mysql詳細(xì)教程
柚子快報邀請碼778899分享：數(shù)據(jù)庫 mysql詳細(xì)教程
目錄
1、初識數(shù)據(jù)庫
1.1、什么是數(shù)據(jù)庫
1.2、數(shù)據(jù)庫分類
1.3、相關(guān)概念
1.4、MySQL及其安裝
1.5、基本命令
2、操作數(shù)據(jù)庫
2.1、操作數(shù)據(jù)庫
2.2、數(shù)據(jù)庫的列類型
2.3、數(shù)據(jù)庫的字段屬性
2.4、創(chuàng)建數(shù)據(jù)庫表
2.5、數(shù)據(jù)庫存儲引擎
2.6、修改數(shù)據(jù)庫
3、MySQL數(shù)據(jù)管理
3.1、外鍵
3.2、DML語言
1. 添加 insert
2. 修改 update
4、DQL查詢數(shù)據(jù)
4.1、基礎(chǔ)查詢
4.2、條件查詢
4.3、分組查詢
4.4、連接查詢
4.5、排序和分頁
4.6、子查詢
4.7、MySQL函數(shù)
1. 常用函數(shù)
2. 聚合函數(shù)
5、數(shù)據(jù)庫級別的MD5加密
6、事務(wù)
6.1、事務(wù)原則：ACID
6.3、隔離級別
6.4、執(zhí)行事務(wù)的過程
7、索引
7.1、索引的分類
主鍵索引（PRIMARY KEY）
普通索引（KEY / INDEX）
唯一索引（UNIQUE KEY）
全文索引（FULLText）
7.2、索引的使用
1. 索引的創(chuàng)建
3. 顯示索引信息
4. explain分析sql執(zhí)行的情況
7.3、測試索引
7.4、索引原則
8、explain關(guān)鍵字
9、權(quán)限管理和備份
9.1、用戶管理
9.2、數(shù)據(jù)庫備份
10、三大范式
11、數(shù)據(jù)庫驅(qū)動和JDBC
11.1、?第一個JDBC程序
11.2、?JDBC對象
DriverManager
Statement
ResultSet
11.3、?封裝jdbc工具類
1. 編寫數(shù)據(jù)庫配置文件
2. 編寫工具類
3. 測試
4. SQL注入問題
5. PreparedStatement對象
6. 事務(wù)案例
1、初識數(shù)據(jù)庫
1.1、什么是數(shù)據(jù)庫
數(shù)據(jù)庫：DB（DataBase）
概念：數(shù)據(jù)倉庫，軟件，安裝在操作系統(tǒng)之上
作用：存儲數(shù)據(jù)，管理數(shù)據(jù)
1.2、數(shù)據(jù)庫分類
關(guān)系型數(shù)據(jù)庫：SQL（Structured Query Language）
MySQL、Oracle、Sql Server、DB2、SQLlite
通過表和表之間，行和列之間的關(guān)系進(jìn)行數(shù)據(jù)的存儲
通過外鍵關(guān)聯(lián)來建立表與表之間的關(guān)系
非關(guān)系型數(shù)據(jù)庫：NoSQL（Not Only SQL）
Redis、MongoDB
指數(shù)據(jù)以對象的形式存儲在數(shù)據(jù)庫中，而對象之間的關(guān)系通過每個對象自身的屬性來決定
1.3、相關(guān)概念
DBMS（數(shù)據(jù)庫管理系統(tǒng)）
數(shù)據(jù)庫的管理軟件，科學(xué)有效的管理、維護(hù)和獲取我們的數(shù)據(jù)
MySQL就是數(shù)據(jù)庫管理系統(tǒng)
1.4、MySQL及其安裝
MySQL最新版8.0.21安裝配置教程~
1.5、基本命令
所有的語句都要以分號結(jié)尾
show databases; --查看當(dāng)前所有的數(shù)據(jù)庫
use 數(shù)據(jù)庫名; --打開指定的數(shù)據(jù)庫
show tables; --查看所有的表
describe/desc 表名; --顯示表的信息
create database 數(shù)據(jù)庫名; --創(chuàng)建一個數(shù)據(jù)庫
exit --退出連接
-- --單行注釋
# --單行注釋
/*...*/ --多行注釋
2、操作數(shù)據(jù)庫
2.1、操作數(shù)據(jù)庫
1、創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE [IF NOT EXISTS] 數(shù)據(jù)庫名;
2、刪除數(shù)據(jù)庫
DROP DATABASE [if EXISTS] 數(shù)據(jù)庫名;
3、使用數(shù)據(jù)庫
--如果表名或者字段名是特殊字符，則需要帶``
use 數(shù)據(jù)庫名;
4、查看數(shù)據(jù)庫
SHOW DATABASES;
2.2、數(shù)據(jù)庫的列類型
數(shù)值
沒有值，未知
不要使用NULL值進(jìn)行計算
2.3、數(shù)據(jù)庫的字段屬性
UnSigned
無符號的
聲明了該列不能為負(fù)數(shù)
ZEROFILL
0填充的
不足位數(shù)的用0來填充 , 如int(3),5則為005
Auto_InCrement
通常理解為自增，自動在上一條記錄的基礎(chǔ)上默認(rèn)+1
通常用來設(shè)計唯一的主鍵，必須是整數(shù)類型
可定義起始值和步長
當(dāng)前表設(shè)置步長(AUTO_INCREMENT=100) : 只影響當(dāng)前表
SET @@auto_increment_increment=5 ; 影響所有使用自增的表(全局)
NULL 和 NOT NULL
默認(rèn)為NULL , 即沒有插入該列的數(shù)值
如果設(shè)置為NOT NULL , 則該列必須有值
DEFAULT
默認(rèn)的
用于設(shè)置默認(rèn)值
例如,性別字段,默認(rèn)為"男" , 否則為 “女” ; 若無指定該列的值 , 則默認(rèn)值為"男"的值
拓展：每一個表，都必須存在以下五個字段：
2.4、創(chuàng)建數(shù)據(jù)庫表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`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
注意點(diǎn)：
表名和字段盡量使用``括起來
AUTO_INCREMENT 代表自增
所有的語句后面加逗號，最后一個不加
字符串使用單引號括起來
主鍵的聲明一般放在最后，便于查看
不設(shè)置字符集編碼的話，會使用MySQL默認(rèn)的字符集編碼Latin1，不支持中文，可以在my.ini里修改
格式：
CREATE TABLE IF NOT EXISTS `student`(
'字段名' 列類型 [屬性] [索引] [注釋],
'字段名' 列類型 [屬性] [索引] [注釋],
......
'字段名' 列類型 [屬性] [索引] [注釋]
)[表的類型][字符集設(shè)置][注釋]
常用命令：
SHOW CREATE DATABASE 數(shù)據(jù)庫名;-- 查看創(chuàng)建數(shù)據(jù)庫的語句
SHOW CREATE TABLE 表名;-- 查看表的定義語句
DESC 表名;-- 顯示表的具體結(jié)構(gòu)
2.5、數(shù)據(jù)庫存儲引擎
INNODB
默認(rèn)使用，安全性高，支持事務(wù)的處理，多表多用戶操作
MYISAM
早些年使用，節(jié)約空間，速度較快
數(shù)據(jù)庫文件存在的物理空間位置：
MySQL數(shù)據(jù)表以文件方式存放在磁盤中
包括表文件 , 數(shù)據(jù)文件 , 以及數(shù)據(jù)庫的選項文件位置 :?Mysql安裝目錄\data\（目錄名對應(yīng)數(shù)據(jù)庫名 , 該目錄下文件名對應(yīng)數(shù)據(jù)表）
MySQL在文件引擎上區(qū)別：
INNODB數(shù)據(jù)庫文件類型就包括**.frm**、.ibd以及在上一級目錄的ibdata1文件
MYISAM存儲引擎，數(shù)據(jù)庫文件類型就包括
.frm：表結(jié)構(gòu)定義文件
.MYD：數(shù)據(jù)文件
.MYI：索引文件
2.6、修改數(shù)據(jù)庫
修改
修改表名 : ALTER TABLE 舊表名 RENAME AS 新表名
添加字段 : ALTER TABLE 表名 ADD字段名 列屬性[屬性]
修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列類型[屬性]
ALTER TABLE 表名 CHANGE 舊字段名 新字段名 列屬性[屬性]
刪除字段 : ALTER TABLE 表名 DROP 字段名
-- 修改表名
-- ALTER TABLE 舊表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列屬性
ALTER TABLE teachers ADD age INT(11);
-- 修改表的字段(重命名，修改約束)
-- ALTER TABLE 表名 MODIFY 字段名 [列屬性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改約束
-- ALTER TABLE 表名 CHANGE 舊名字 新名字 [列屬性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名
-- 刪除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;
刪除
語法：DROP TABLE [IF EXISTS] 表名
IF EXISTS為可選 , 判斷是否存在該數(shù)據(jù)表
如刪除不存在的數(shù)據(jù)表會拋出錯誤
-- 刪除表(如果存在再刪除)
DROP TABLE IF EXISTS teachers;
所有的創(chuàng)建和刪除盡量加上判斷，以免報錯~
3、MySQL數(shù)據(jù)管理
3.1、外鍵
外鍵概念
如果公共關(guān)鍵字在一個關(guān)系中是主關(guān)鍵字，那么這個公共關(guān)鍵字被稱為另一個關(guān)系的外鍵。由此可見，外鍵表示了兩個關(guān)系之間的相關(guān)聯(lián)系。以另一個關(guān)系的外鍵作主關(guān)鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。
在實際操作中，將一個表的值放入第二個表來表示關(guān)聯(lián)，所使用的值是第一個表的主鍵值(在必要時可包括復(fù)合主鍵值)。此時，第二個表中保存這些值的屬性稱為外鍵(foreign key)。
外鍵作用：
保持?jǐn)?shù)據(jù)一致性，完整性，主要目的是控制存儲在外鍵表中的數(shù)據(jù),約束。使兩張表形成關(guān)聯(lián)，外鍵只能引用外表中的列的值或使用空值。
目標(biāo)：學(xué)生表（student）的gradeid字段 要去引用年級表（grade）的 gradeid字段
創(chuàng)建外鍵
方式一：在創(chuàng)建表的時候增加約束
/*
1. 定義外鍵key
2. 給外鍵添加約束（執(zhí)行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`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 '郵箱',
`gradeid` INT(10) NOT NULL COMMENT '學(xué)生的年級',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 創(chuàng)建年級表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL COMMENT '年級id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年紀(jì)名稱',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
刪除有外鍵關(guān)系的表的時候，必須要先刪除引用別人的表（從表），再刪除被引用的表（主表）
方法二：創(chuàng)建表成功后，添加外鍵約束
/*
1. 定義外鍵key
2. 給外鍵添加約束（執(zhí)行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '學(xué)號',
`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 '郵箱',
`gradeid` INT(10) NOT NULL COMMENT '學(xué)生的年級',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 創(chuàng)建表的時候沒有外鍵關(guān)系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- 創(chuàng)建年級表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL COMMENT '年級id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年紀(jì)名稱',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
以上的操作都是物理外鍵，數(shù)據(jù)庫級別的外鍵，不建議使用！避免數(shù)據(jù)庫過多造成困擾！
最佳實踐
數(shù)據(jù)庫就是用來單純的表，只用來存數(shù)據(jù)，只有行（數(shù)據(jù)）和列（屬性）
我們想使用多張表的數(shù)據(jù)，使用外鍵，用程序去實現(xiàn)
3.2、DML語言
數(shù)據(jù)庫的意義：數(shù)據(jù)存儲，數(shù)據(jù)管理
Data Manipulation Luaguge：數(shù)據(jù)操作語言
1. 添加 insert
-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');
-- 插入多條數(shù)據(jù)
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武漢','1412@qq.com',1);
語法：
INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
注意：
字段和字段之間使用英文逗號隔開字段是可以省略的，但是值必須完整且一一對應(yīng)可以同時插入多條數(shù)據(jù)，VALUES后面的值需要使用逗號隔開
2. 修改 update
-- 修改學(xué)員名字,指定條件
UPDATE `student` SET `name`='zsr204' WHERE id=1;
-- 不指定條件的情況,會改動所有表
UPDATE `student` SET `name`='zsr204';
-- 修改多個屬性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
-- 通過多個條件定位數(shù)據(jù)
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
語法：
UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 條件[];
關(guān)于WHERE條件語句：
3. 刪除 delete
-- 刪除數(shù)據(jù)(避免這樣寫,會全部刪除)
DELETE FROM `student`;
-- 刪除指定數(shù)據(jù)
DELETE FROM `student` WHERE id=1;
語法：
DELETE FROM 表名 [WHERE 條件]
關(guān)于DELETE刪除的問題，重啟數(shù)據(jù)庫現(xiàn)象：
INNODB 自增列會從1開始（存在內(nèi)存當(dāng)中，斷電即失）
MYISAM 繼續(xù)從上一個子增量開始（存在內(nèi)存當(dāng)中，不會丟失）
TRUNCATE
作用：完全清空一個數(shù)據(jù)庫表，表的結(jié)構(gòu)和索引約束不會變！
DELETE和TRUNCATE 的區(qū)別：
DELETE可以條件刪除（where子句），而TRUNCATE只能刪除整個表
TRUNCATE 重新設(shè)置自增列，計數(shù)器會歸零，而DELETE不會影響自增
DELETE是數(shù)據(jù)操作語言（DML - Data Manipulation Language），操作時原數(shù)據(jù)會被放到 rollback segment中，可以被回滾；而TRUNCATE是數(shù)據(jù)定義語言（DDL - Data Definition Language)，操作時不會進(jìn)行存儲，不能進(jìn)行回滾。
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`;
4、DQL查詢數(shù)據(jù)
Data QueryLanguage?數(shù)據(jù)查詢語言
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 聯(lián)合查詢
[WHERE ...] -- 指定結(jié)果需滿足的條件
[GROUP BY ...] -- 指定結(jié)果按照哪幾個字段來分組
[HAVING] -- 過濾分組的記錄必須滿足的次要條件
[ORDER BY ...] -- 指定查詢記錄按一個或多個條件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查詢的記錄從哪條至哪條
查詢數(shù)據(jù)庫數(shù)據(jù) , 如SELECT語句簡單的單表查詢或多表的復(fù)雜查詢和嵌套查詢是數(shù)據(jù)庫語言中最核心,最重要的語句使用頻率最高的語句
前提配置：
-- 創(chuàng)建學(xué)校數(shù)據(jù)庫
CREATE DATABASE IF NOT EXISTS `school`;
-- 用school數(shù)據(jù)庫
USE `school`;
-- 創(chuàng)建年級表grade表
CREATE TABLE `grade`(
`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年級編號',
`GradeName` VARCHAR(50) NOT NULL COMMENT '年紀(jì)名稱',
PRIMARY KEY (`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- 給grade表插入數(shù)據(jù)
INSERT INTO `grade`(`GradeID`,`GradeName`)
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');
-- 創(chuàng)建成績result表
CREATE TABLE `result`(
`StudentNo` INT(4) NOT NULL COMMENT '學(xué)號',
`SubjectNo` INT(4) NOT NULL COMMENT '考試編號',
`ExamDate` DATETIME NOT NULL COMMENT '考試日期',
`StudentResult` INT(4) NOT NULL COMMENT '考試成績',
KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 給result表插入數(shù)據(jù)
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`)
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);
-- 創(chuàng)建學(xué)生表student
CREATE TABLE `student`(
`StudentNo` INT(4) NOT NULL COMMENT '學(xué)號',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '學(xué)生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性別,取值0或1',
`GradeID` INT(11) DEFAULT NULL COMMENT '年級編號',
`Phone` VARCHAR(50) NOT NULL COMMENT '聯(lián)系電話,允許為空,即可選輸入',
`Adress` VARCHAR(255) NOT 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;
-- 給學(xué)生表插入數(shù)據(jù)
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`)
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');
-- 創(chuàng)建科目表
CREATE TABLE `subject`(
`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '課程編號',
`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '課程名稱',
`ClassHour` INT(4) DEFAULT NULL COMMENT '學(xué)時',
`GradeID` INT(4) DEFAULT NULL COMMENT '年級編號',
PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- 給科目表subject插入數(shù)據(jù)
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`)
VALUES(1,'高數(shù)','96',2),(2,'大物','112',2),(3,'程序設(shè)計',64,3);
SELECT 字段 FROM 表;
4.1、基礎(chǔ)查詢
語法：
SELECT 查詢列表 FROM 表名;
查詢列表可以是：表中的（一個或多個）字段，常量，變量，表達(dá)式，函數(shù)查詢結(jié)果是一個虛擬的表格
-- 查詢?nèi)繉W(xué)生
SELECT * FROM student;
-- 查詢指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;
-- 別名 AS(可以給字段起別名,也可以給表起別名)
SELECT `StudentNo` AS 學(xué)號,`StudentName` AS 學(xué)生姓名 FROM student AS 學(xué)生表;
-- 函數(shù) CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;
-- 查詢系統(tǒng)版本(函數(shù))
SELECT VERSION();
-- 用來計算(計算表達(dá)式)
SELECT 100*53-90 AS 計算結(jié)果;
-- 查詢自增步長(變量)
SELECT @@auto_increment_increment;
-- 查詢有哪寫同學(xué)參加了考試,重復(fù)數(shù)據(jù)要去重
SELECT DISTINCT `StudentNo` FROM result;
4.2、條件查詢
where 條件字句：檢索數(shù)據(jù)中
符合條件的值
語法：
select 查詢列表 from 表名 where 篩選條件;
-- 查詢考試成績在95~100之間的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;
-- 查詢除了1000號以外的學(xué)生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
-- 查詢名字含d的同學(xué)
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';
-- 查詢名字倒數(shù)第二個為d的同學(xué)
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';
-- 查詢1000,1001學(xué)員
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);
4.3、分組查詢
語法：
select 分組函數(shù)，分組后的字段
from 表
【where 篩選條件】
group by 分組的字段
【having 分組后的篩選】
【order by 排序列表】
區(qū)別：
-- 查詢不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根據(jù)不同的課程進(jìn)行分組
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;
4.4、連接查詢
-- 查詢學(xué)員所屬的年級（學(xué)號，學(xué)生姓名，年級名稱）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;
-- 查詢科目所屬的年級
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;
-- 查詢列參加程序設(shè)計考試的同學(xué)信息（學(xué)號，姓名，科目名，分?jǐn)?shù)）
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='課程設(shè)計';
自連接
自己的表和自己的表連接，核心：一張表拆為兩張一樣的表即可
-- 創(chuàng)建一個表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '課程id',
`pid` INT(10) NOT NULL COMMENT '父課程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '課程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入數(shù)據(jù)
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技術(shù)'),
('3','1','軟件開發(fā)'),
('4','3','數(shù)據(jù)庫'),
('5','1','美術(shù)設(shè)計'),
('6','3','web開發(fā)'),
('7','5','ps技術(shù)'),
('8','2','辦公信息');
將該表進(jìn)行拆分：
SELECT a.`courseid` AS '父課程',b.`courseid` AS '子課程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;
4.5、排序和分頁
排序
語法：
select 查詢列表
from 表
where 篩選條件
order by 排序列表 asc/desc
order by的位置一般放在查詢語句的最后（除limit語句之外）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;
分頁
語法：
select 查詢列表
from 表
limit offset,pagesize;
offset代表的是起始的條目索引，默認(rèn)從0開始size代表的是顯示的條目數(shù)offset=(n-1)*pagesize
-- 第一頁 limit 0 5
-- 第二頁 limit 5,5
-- 第三頁 limit 10,5
-- 第n頁 limit (n-1)*pagesize,pagesize
-- pagesize:當(dāng)前頁面大小
-- (n-1)*pagesize:起始值
-- n:當(dāng)前頁面
-- 數(shù)據(jù)總數(shù)/頁面大小=總頁面數(shù)
-- limit n 表示從0到n的頁面
4.6、子查詢
本質(zhì)：在?where子句中嵌套一個子查詢語句
-- 查詢‘課程設(shè)計’的所有考試結(jié)果（學(xué)號，科目編號，成績）降序排列
-- 方式一:使用連接查詢
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='課程設(shè)計'
ORDER BY StudentResult DESC;
-- 方式二:使用子查詢（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName='課程設(shè)計'
)
4.7、MySQL函數(shù)
1. 常用函數(shù)
-- 數(shù)學(xué)運(yùn)算
SELECT ABS(-8); -- 絕對值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之間的一個隨機(jī)數(shù)
SELECT SIGN(-10); -- 返回一個數(shù)的符號;0返回0;正數(shù)返回1;負(fù)數(shù)返回-1
-- 字符串函數(shù)
SELECT CHAR_LENGTH('我喜歡你'); -- 字符串長度
SELECT CONCAT('我','喜歡','你'); -- 拼接字符串
SELECT INSERT('我喜歡',1,1,'超級') -- INSERT(str,pos,len,newstr) 從str的pos位置開始替換為長度為len的newstr
SELECT UPPER('zsr'); -- 轉(zhuǎn)大寫
SELECT LOWER('ZSR'); -- 轉(zhuǎn)小寫
SELECT INSTR('zsrs','s'); -- 返回第一次出現(xiàn)字串索引的位置
SELECT REPLACE('加油就能勝利','加油','堅持'); -- 替換出現(xiàn)的指定字符串
SELECT SUBSTR('堅持就是勝利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取長度)
SELECT REVERSE('rsz'); -- 反轉(zhuǎn)字符串
-- 時間日期函數(shù)
SELECT CURRENT_DATE(); -- 獲取當(dāng)前日期
SELECT CURDATE(); -- 獲取當(dāng)前日期
SELECT now(); -- 獲取當(dāng)前時間
SELECT LOCALTIME(); -- 本地時間
SELECT SYSDATE(); -- 系統(tǒng)時間
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 系統(tǒng)信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();
2. 聚合函數(shù)
SELECT COUNT(StudentName) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;
5、數(shù)據(jù)庫級別的MD5加密
MD5信息摘要算法（MD5 Message-Digest Algorithm）
MD5由MD4、MD3、MD2改進(jìn)而來，主要增強(qiáng)算法復(fù)雜度和不可逆性MD5破解網(wǎng)站的原理，背后有一個字典，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,'zsr','200024'),
(2,'gcc','000421'),(3,'bareth','123456');
-- 加密
UPDATE `testMD5` SET `pwd`=MD5(pwd) WHE RE id=1;
UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密碼
-- 插入的時候加密
INSERT INTO `testMD5` VALUES(4,'barry',MD5('654321'));
-- 如何校驗:將用戶傳遞進(jìn)來的密碼，進(jìn)行MD5加密，然后對比加密后的值
SELECT * FROM `testMD5` WHERE `name`='barry' AND `pwd`=MD5('654321');
6、事務(wù)
要么都成功，要么都失敗
SQL執(zhí)行：A轉(zhuǎn)賬給B
SQL執(zhí)行：B收到A的錢
將一組SQL放在一個批次中去執(zhí)行
例如銀行轉(zhuǎn)賬：只有A轉(zhuǎn)賬成功且B成功到賬，該事件才算結(jié)束，如果一方不成功，則該事務(wù)不成功
6.1、事務(wù)原則：ACID
參考鏈接：https://blog.csdn.net/dengjili/article/details/82468576
6.3、隔離級別
在數(shù)據(jù)庫操作中，為了有效保證并發(fā)讀取數(shù)據(jù)的正確性，提出的
事務(wù)隔離級別
讀未提交：一個事務(wù)讀取到其他事務(wù)未提交的數(shù)據(jù)；這種隔離級別下，查詢不會加鎖，一致性最差，會產(chǎn)生臟讀、不可重復(fù)讀、幻讀的問題
讀已提交：一個事務(wù)只能讀取到其他事務(wù)已經(jīng)提交的數(shù)據(jù)；該隔離級別避免了臟讀問題的產(chǎn)生，但是不可重復(fù)讀和幻讀的問題仍然存在；
讀提交事務(wù)隔離級別是大多數(shù)流行數(shù)據(jù)庫的默認(rèn)事務(wù)隔離級別，比如 Oracle，但是不是 MySQL 的默認(rèn)隔離界別
可重復(fù)讀：事務(wù)在執(zhí)行過程中可以讀取到其他事務(wù)已提交的新插入的數(shù)據(jù)，但是不能讀取其他事務(wù)對數(shù)據(jù)的修改，也就是說多次讀取同一記錄的結(jié)果相同；該個里級別避免了臟讀、不可重復(fù)度的問題，但是仍然無法避免幻讀的問題
可重復(fù)讀是MySQL默認(rèn)的隔離級別
串行化：事務(wù)串行化執(zhí)行，事務(wù)只能一個接著一個地執(zhí)行,、，并且在執(zhí)行過程中完全看不到其他事務(wù)對數(shù)據(jù)所做的更新；缺點(diǎn)是并發(fā)能力差，最嚴(yán)格的事務(wù)隔離，完全符合ACID原則，但是對性能影響比較大
6.4、執(zhí)行事務(wù)的過程
1???關(guān)閉自動提交
SET autocommit=0;
2???事務(wù)開啟
START TRANSACTION -- 標(biāo)記一個事務(wù)的開始，從這個之后的sql都在同一個事務(wù)內(nèi)
3???成功則提交，失敗則回滾
-- 提交：持久化（成功）
COMMIT
-- 回滾：回到原來的樣子（失?。?/p>
ROLLBACK
4???事務(wù)結(jié)束
SET autocommit=1; -- 開啟自動提交
?5???其他操作
SAVEPOINT 保存點(diǎn)名; -- 設(shè)置一個事務(wù)的保存點(diǎn)
ROLLBACK TO SAVEPOINT 保存點(diǎn)名; -- 回滾到保存點(diǎn)
RELEASE SAVEPOINT 保存點(diǎn)名; -- 撤銷保存點(diǎn)
7、索引
推薦閱讀：MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
索引（
Index）是幫助MySQL高效獲取數(shù)據(jù)的
數(shù)據(jù)結(jié)構(gòu)。
提高查詢速度確保數(shù)據(jù)的唯一性可以加速表和表之間的連接 , 實現(xiàn)表與表之間的參照完整性使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時 , 可以顯著減少分組和排序的時間全文檢索字段進(jìn)行搜索優(yōu)化
7.1、索引的分類
-- 創(chuàng)建學(xué)生表student
CREATE TABLE `student`(
`StudentNo` INT(4) NOT NULL COMMENT '學(xué)號',
`LoginPwd` VARCHAR(20) DEFAULT NULL,
`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '學(xué)生姓名',
`Sex` TINYINT(1) DEFAULT NULL COMMENT '性別,取值0或1',
`GradeID` INT(11) DEFAULT NULL COMMENT '年級編號',
`Phone` VARCHAR(50) NOT NULL COMMENT '聯(lián)系電話,允許為空,即可選輸入',
`Adress` VARCHAR(255) NOT 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;
主鍵索引（PRIMARY KEY）
唯一的標(biāo)識，主鍵不可重復(fù)，只有一個列作為主鍵
最常見的索引類型，不允許為空值確保數(shù)據(jù)記錄的唯一性確定特定數(shù)據(jù)記錄在數(shù)據(jù)庫中的位置
-- 創(chuàng)建表的時候指定主鍵索引
CREATE TABLE tableName(
......
PRIMARY INDEX (columeName)
)
-- 修改表結(jié)構(gòu)添加主鍵索引
ALTER TABLE tableName ADD PRIMARY INDEX (columnName)
普通索引（KEY / INDEX）
默認(rèn)的，快速定位特定數(shù)據(jù)
index 和 key 關(guān)鍵字都可以設(shè)置常規(guī)索引應(yīng)加在查詢找條件的字段不宜添加太多常規(guī)索引,影響數(shù)據(jù)的插入,刪除和修改操作
-- 直接創(chuàng)建普通索引
CREATE INDEX indexName ON tableName (columnName)
-- 創(chuàng)建表的時候指定普通索引
CREATE TABLE tableName(
......
INDEX [indexName] (columeName)
)
-- 修改表結(jié)構(gòu)添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
唯一索引（UNIQUE KEY）
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值
與主鍵索引的區(qū)別：主鍵索引只能有一個、唯一索引可以有多個
-- 直接創(chuàng)建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)
-- 創(chuàng)建表的時候指定唯一索引
CREATE TABLE tableName(
......
UNIQUE INDEX [indexName] (columeName)
);
-- 修改表結(jié)構(gòu)添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)
全文索引（FULLText）
快速定位特定數(shù)據(jù)（百度搜索就是全文索引）
在特定的數(shù)據(jù)庫引擎下才有：MyISAM只能用于CHAR , VARCHAR , TEXT數(shù)據(jù)列類型適合大型數(shù)據(jù)集
-- 增加一個全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql執(zhí)行的情況
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引
7.2、索引的使用
1. 索引的創(chuàng)建
在創(chuàng)建表的時候給字段增加索引
CREATE TABLE 表名 (
字段名1 數(shù)據(jù)類型 [完整性約束條件…],
字段名2 數(shù)據(jù)類型 [完整性約束條件…],
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(長度)] [ASC |DESC])
);
創(chuàng)建完畢后，增加索引
-- 方法一：CREATE在已存在的表上創(chuàng)建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名[(長度)] [ASC |DESC]) ;
-- 方法二：ALTER TABLE在已存在的表上創(chuàng)建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL]
INDEX 索引名 (字段名[(長度)] [ASC |DESC]) ;
2. 索引的刪除
-- 刪除索引
DROP INDEX 索引名 ON 表名;
-- 刪除主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;
3. 顯示索引信息
SHOW INDEX FROM 表名;
4. explain分析sql執(zhí)行的情況
-- 增加一個全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql執(zhí)行的情況
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引
7.3、測試索引
建表app_user：
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 '手機(jī)號',
`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 UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'
批量插入數(shù)據(jù)：100w
-- 1418錯解決方案(創(chuàng)建函數(shù)前執(zhí)行此語句)
set global log_bin_trust_function_creators=true;
-- 插入100萬條數(shù)據(jù)
DELIMITER $$ -- 寫函數(shù)之前要寫的標(biāo)志
CREATE FUNCTION mock_data() -- 創(chuàng)建mock_data()函數(shù)
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), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
-- 執(zhí)行函數(shù)
SELECT mock_data();
測試查詢速度
-- 查詢用戶名為'用戶9999'性能分析
EXPLAIN SELECT * FROM app_user where name='用戶99999'
增加索引后測試
-- 給name列創(chuàng)建常規(guī)索引
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 再測試
EXPLAIN SELECT * FROM app_user where name='用戶99999'
對比兩次結(jié)果，速度有了很大的提升
7.4、索引原則
索引不是越多越好，小數(shù)據(jù)量的表不需要加索引不要對經(jīng)常變動的數(shù)據(jù)增加索引索引一般加在經(jīng)常要查詢的列上
8、explain關(guān)鍵字
建議閱讀：
MySQL優(yōu)化——看懂explainMySQL高級 之 explain執(zhí)行計劃詳解
9、權(quán)限管理和備份
9.1、用戶管理
方式一：可視化管理
方式二：SQL命令操作
用戶信息存儲在
mysql數(shù)據(jù)庫的
user表中，對用戶的管理本質(zhì)上就是對這張表進(jìn)行增刪改查
-- 創(chuàng)建用戶
CREATE USER zsr IDENTIFIED BY '123456'
-- 刪除用戶
DROP USER zsr
-- 修改當(dāng)前用戶密碼
SET PASSWORD = PASSWORD('200024')
-- 修改指定用戶密碼
SET PASSWORD FOR zsr = PASSWORD('200024')
-- 重命名
RENAME USER zsr to zsr2
-- 用戶授權(quán)(授予全部權(quán)限,除了給其他用戶授權(quán))
GRANT ALL PRIVILEGES on *.* TO zsr2
-- 查詢權(quán)限
SHOW GRANTS FOR zsr
-- 查看root用戶權(quán)限
SHOW GRANTS FOR root@localhost
-- 撤銷權(quán)限
REVOKE ALL PRIVILEGES ON *.* FROM zsr
9.2、數(shù)據(jù)庫備份
保證重要的數(shù)據(jù)不丟失、數(shù)據(jù)轉(zhuǎn)義
方式一：直接拷貝物理文件，MySQL數(shù)據(jù)表以文件方式存放在磁盤中
包括表文件 , 數(shù)據(jù)文件 , 以及數(shù)據(jù)庫的選項文件位置 :?Mysql安裝目錄\data\（目錄名對應(yīng)數(shù)據(jù)庫名 , 該目錄下文件名對應(yīng)數(shù)據(jù)表）
方式二：可視化管理
Navicat打開要備份的數(shù)據(jù)庫，然后點(diǎn)擊新建備份
點(diǎn)擊對象選擇，這里可以自定義選擇備份的表
選擇完畢后，點(diǎn)擊備份即可開始備份
等待備份完成，關(guān)閉，然后便可看到備份的文件
方式三：可視化管理
選中要導(dǎo)出的表，右鍵轉(zhuǎn)儲SQL文件
然就就可以得到.sql文件
方式四：命令mysqldump導(dǎo)出
# mysqldump -h主機(jī) -u用戶名 -p密碼 數(shù)據(jù)庫 [表1 表2 表3] >物理磁盤位置/文件名
# 導(dǎo)出school數(shù)據(jù)庫的cource grade student表到D:/school.sql
mysqldump -hlocalhost -uroot -p200024 school course grade student >D:/school.sql
然后便可看到導(dǎo)出的sql文件
然后可以命令行登錄mysql，切換到指定的數(shù)據(jù)庫，用source命令導(dǎo)入
10、三大范式
規(guī)范化理論：改造關(guān)系模式，通過分解關(guān)系模式來消除其中不合適的數(shù)據(jù)依賴，以解決插入異常、刪除異常、更新異常和數(shù)據(jù)冗余的問題。
為了建立冗余較小、結(jié)構(gòu)合理的數(shù)據(jù)庫，設(shè)計數(shù)據(jù)庫時必須遵循一定規(guī)范化理論。在關(guān)系型數(shù)據(jù)庫中這種規(guī)則就稱為
范式
三大范式的通俗理解
如果一個關(guān)系模式R的所有屬性都是不可分的數(shù)據(jù)項，則R屬于第一范式如果關(guān)系模式R屬于第一范式，且每一個非主屬性完全函數(shù)依賴于碼，則R屬于第二范式若關(guān)系模式R屬于第二范式，且R中所有的非主屬性都直接依賴于碼，則R屬于第三范式
規(guī)范性問題：
數(shù)據(jù)庫的范式是為了規(guī)范數(shù)據(jù)庫的設(shè)計，但是實際中相比規(guī)范性，往往更需要看中性能、成本、用戶體驗等問題；
因此有時會故意給某些表增加一個冗余的字段，使多表查詢變?yōu)閱伪聿樵?。有時還會增加一些計算列，從大數(shù)據(jù)量變?yōu)樾?shù)據(jù)量（數(shù)據(jù)量大時，count(*)很耗時，可以直接添加一列，每增加一行+1，查該列即可）；阿里也曾提出關(guān)聯(lián)查詢的表最多不超過三張表。
這些就是為了性能、成本而舍棄一定規(guī)范性的例子
11、數(shù)據(jù)庫驅(qū)動和JDBC
我們編寫的程序會通過
數(shù)據(jù)庫驅(qū)動來和數(shù)據(jù)庫進(jìn)行交互
然后不同的數(shù)據(jù)庫有不同的驅(qū)動，這不便于我們程序?qū)Ω鞣N數(shù)據(jù)庫進(jìn)行操作；因此為了簡化對不同數(shù)據(jù)庫的操作，SUN公司提供了一個Java操作數(shù)據(jù)庫的規(guī)范JDBC；不同數(shù)據(jù)庫的規(guī)范由對應(yīng)的數(shù)據(jù)庫廠商完成，對于開發(fā)人員，只需要掌握J(rèn)DBC接口的操作即可
11.1、?第一個JDBC程序
1???新建空項目
2???導(dǎo)入mysql-connector-java
在項目目錄下新建lib目錄，放入jar包
3???編寫代碼&測試
在src目錄下新建JDBCDemo用來操作數(shù)據(jù)庫
import java.sql.*;
public class JDBCDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加載驅(qū)動
Class.forName("com.mysql.cj.jdbc.Driver");
//2.連接信息url,用戶信息
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
String username = "root";
String password = "200024";
//3.連接,獲得數(shù)據(jù)庫對象connection
Connection connection = DriverManager.getConnection(url, username, password);
//4.獲取執(zhí)行sql的對象
Statement statement = connection.createStatement();
//5.執(zhí)行sql
String sql = "select * from app_user where id<10";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
}
//6.釋放連接
resultSet.close();
statement.close();
connection.close();
}
}
11.2、?JDBC對象
DriverManager
DriverManager：驅(qū)動管理
//1.加載驅(qū)動
Class.forName("com.mysql.cj.jdbc.Driver");
本質(zhì)上執(zhí)行DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
connection代表數(shù)據(jù)庫，因此可以設(shè)置事務(wù)自動提交，事務(wù)回滾等
Statement
Statement：執(zhí)行sql的對象，用于向數(shù)據(jù)庫發(fā)送SQL語句，想完成對數(shù)據(jù)庫的增刪改査，只需要通過這個對象向數(shù)據(jù)庫發(fā)送增刪改查語句即可
statement.executeQuery();//查詢操作,返回結(jié)果
statement.execute();//執(zhí)行sql
statement.executeUpdate();//用于增刪改,返回受影響的行數(shù)
ResultSet
ResultSet：查詢的結(jié)果集，封裝了所有查詢的結(jié)果
11.3、?封裝jdbc工具類
1. 編寫數(shù)據(jù)庫配置文件
在src目錄下新建db.properties，用于存放數(shù)據(jù)庫配置信息
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=200024
2. 編寫工具類
然后再src目錄下新建JDBCUtils.java作為工具類
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 inputStream = JDBCDemo.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//加載驅(qū)動
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//獲取連接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//釋放連接資源
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
3. 測試
修改JDBCDemo
import java.sql.*;
public class JDBCDemo {
public static void main(String[] args) throws SQLException {
//獲得數(shù)據(jù)庫對象connection
Connection connection = JDBCUtils.getConnection();
//獲取sql執(zhí)行對象statement
Statement statement = connection.createStatement();
//執(zhí)行sql
String sql = "select * from app_user where id<10";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
}
//釋放連接
JDBCUtils.release(connection, statement, resultSet);
}
}
4. SQL注入問題
SQL注入即是指web應(yīng)用程序?qū)τ脩糨斎霐?shù)據(jù)的合法性沒有判斷或過濾不嚴(yán)，攻擊者可以在web應(yīng)用程序中事先定義好的查詢語句的結(jié)尾上添加額外的SQL語句，在管理員不知情的情況下實現(xiàn)非法操作，以此來實現(xiàn)欺騙數(shù)據(jù)庫服務(wù)器執(zhí)行非授權(quán)的任意查詢，從而進(jìn)一步得到相應(yīng)的數(shù)據(jù)信息。
sql注入案例：主函數(shù)中傳入用戶名，查找指定名字用戶信息
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLInjection {
public static void main(String[] args) throws SQLException {
searchName("' or '1=1");
}
//查找指定名字用戶信息
public static void searchName(String username) throws SQLException {
//獲得數(shù)據(jù)庫對象connection
Connection connection = JDBCUtils.getConnection();
//獲取sql執(zhí)行對象statement
Statement statement = connection.createStatement();
//執(zhí)行sql
String sql = "select * from app_user where name='" + username + "'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
}
//釋放連接
JDBCUtils.release(connection, statement, resultSet);
}
}
結(jié)果：查詢到了數(shù)據(jù)庫中所有的數(shù)據(jù)
這里傳入一個不是用戶名，而是一個不合法字符串，卻獲取到了全部的數(shù)據(jù)，為什么呢？
拼接整條sql語句是select * from app_user where name=' ' or '1==1'，其中1==1永遠(yuǎn)是真的，所以該sql語句相當(dāng)于查詢表中所有的數(shù)據(jù)；這就是sql注入，主要是字符串拼接引起的問題，十分危險！！
5. PreparedStatement對象
PreparedStatement是
Statement的子類，與其相比，可以防止SQL注入，并且效率更高
同樣測試sql注入案例
import java.sql.*;
public class SQLInjection {
public static void main(String[] args) throws SQLException {
searchName("' 'or '1=1'");
}
//登錄
public static void searchName(String username) throws SQLException {
//獲得數(shù)據(jù)庫對象connection
Connection connection = JDBCUtils.getConnection();
//獲取sql執(zhí)行對象preparedStatement(預(yù)編譯sql,先寫不執(zhí)行,參數(shù)用?表示)
PreparedStatement preparedStatement = connection.prepareStatement("select * from app_user where name=?");
//手動傳參
preparedStatement.setString(1, username);
//執(zhí)行sql
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
}
//釋放連接
JDBCUtils.release(connection, preparedStatement, resultSet);
}
}
根據(jù)結(jié)果，PreparedStatement對象完美避免了sql注入問題
6. 事務(wù)案例
首先創(chuàng)建account表
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40),
money FLOAT
);
INSERT INTO account(name,money) VALUES('A',1000);
INSERT INTO account(name,money) VALUES('B',1000);
INSERT INTO account(name,money) VALUES('C',1000);
然后編寫Java代碼
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class TestTransaction {
public static void main(String[] args) throws SQLException {
//獲得數(shù)據(jù)庫對象connection
Connection connection = JDBCUtils.getConnection();
//關(guān)閉數(shù)據(jù)庫自動提交,即開啟事務(wù)
connection.setAutoCommit(false);
String sql1 = "update account set money = money+100 where name = 'A' ";
String sql2 = "update account set money = money-100 where name = 'B' ";
//獲取sql執(zhí)行對象preparedStatement
PreparedStatement preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//業(yè)務(wù)完畢,提交事務(wù)
connection.commit();
//釋放資源
JDBCUtils.release(connection, preparedStatement, null);
}
}
運(yùn)行結(jié)果：
如果兩次更新之間加int x = 1 / 0;
則會報錯，且事務(wù)執(zhí)行失敗，兩條語句都不會執(zhí)行成功
柚子快報邀請碼778899分享：數(shù)據(jù)庫 mysql詳細(xì)教程
推薦鏈接
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Kogan海外購物港
