010_MySQL

• 2021 年 1 月 14 日
• 筆記
• 040_MySQL

初識MySQL

為什麼學習資料庫

1. 崗位需求
2. 現在的世界，大數據時代，得數據者得天下
3. 被迫需求：存數據
4. 資料庫是所有軟體體系中最核心的存在
   

什麼是資料庫

1. 資料庫（DB，DataBase）
2. 概念：數據倉庫，軟體，安裝在作業系統之上。
3. 作用：存儲數據，管理數據。
   

資料庫分類

1. 關係型資料庫（SQL）
   1. MySQL，Oracle，SQLServer，DB2，SQLlite
   2. 通過表和表之間，行和列之間的關係進行數據的存儲
2. 非關係型資料庫（NoSQL-Not Only SQL）
   1. Redis，MongoDB
   2. 對象存儲，通過對象的屬性來決定。
3. 資料庫管理系統（DBMS）
   1. 資料庫的管理軟體，科學有效的管理數據，維護和獲取數據。
   2. MySQL，資料庫管理系統。
      

MySQL簡介

1. MySQL是一個關係型資料庫管理系統。
2. 前世：瑞典MySQL AB 公司
3. 今生：屬於Oracle旗下產品
4. MySQL是最好的RDBMS(Relational Database Management System，關係資料庫管理系統) 應用軟體之一。
5. 開源的資料庫軟體。
6. 體積小、速度快、總體擁有成本低，招人成本比較低，所有人必須會！
7. 中小型網站或者大型網站（集群）。
8. 官網：//www.mysql.com
   

Windows安裝MySQL

安裝建議

1. 盡量不要使用exe，卸載麻煩，註冊表
2. 儘可能使用壓縮包安裝
   

軟體下載

1. 下載地址：//downloads.mysql.com/archives/community/
2. MySQL5.7.31 64位下載地址：//cdn.mysql.com/archives/mysql-5.7/mysql-5.7.31-winx64.zip
   

安裝步驟

1. 解壓到安裝的目錄，如：D:\environment\mysql-5.7.31
2. 添加環境變數
   1. 我的電腦-》屬性-》高級-》環境變數
   2. 選擇PATH，在其後添加：MySQL安裝目錄下的bin目錄路徑，如：D:\environment\mysql-5.7.31\bin
3. 在MySQL安裝目錄D:\environment\mysql-5.7.31下新建my.ini文件
4. 編輯my.ini文件
   1. 注意：data目錄不需要創建，會在初始化時自動生成
   2. 注意：skip-grant-tables是跳過密碼驗證，設置密碼後，需要注釋掉

[mysqld]
basedir=D:\environment\mysql-5.7.31\
datadir=D:\environment\mysql-5.7.31\data\
port=3306
skip-grant-tables
character-set-server=utf8

5. 安裝MySQL服務：啟動管理員模式下的cmd，並將路徑切換到MySQL下的bin目錄，然後輸入mysqld -install，成功，返回：Service successfully installed.

cd /d D:\environment\mysql-5.7.31\bin
mysqld -install

6. 初始化數據文件：輸入mysqld –initialize-insecure –user=mysql，成功，生成data目錄。data目錄下的目錄對應資料庫，如：mysql，sys，performance_schema。

mysqld --initialize-insecure --user=mysql

7. 啟動MySQL：輸入net start mysql，成功，返回：MySQL 服務正在啟動 MySQL服務已經啟動成功。並且任務管理器中出現MySQL服務。

net start mysql

8. 進入MySQL管理介面：輸入mysql -u root -p,回車，不輸密碼，再回車，成功。
   1. 注意：-p後不能加空格，空格也是字元，也會認為是密碼。
   2. 注意：配置skip-grant-tables，會跳過密碼驗證，輸入密碼時，不需要輸入，直接回車。
   3. 注意：注釋掉skip-grant-tables後，有兩種輸入密碼方法：一是-p後直接回車，再輸入密碼；二是-p後直接輸密碼，再回車，如：mysql -u root -p123456，但是不推薦這樣。

mysql -u root -p

9. 更改root密碼，其實就是修改mysql資料庫的user表的authentication_string欄位，password()函數用於md5加密。

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

10. 刷新許可權：flush privileges，

flush privileges;

11. 注釋掉my.ini文件中的skip-grant-tables，不再跳過密碼驗證。

[mysqld]
basedir=D:\environment\mysql-5.7.31\
datadir=D:\environment\mysql-5.7.31\data\
port=3306
#skip-grant-tables
character-set-server=utf8

12. 重啟mysql：net stop mysql，net start mysql

# 退出mysql
exit
# 關閉mysql
net stop mysql
# 啟動mysql
net start mysql

13. 測試。

C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

安裝SQLyog

下載安裝

1. 下載地址：//sqlyog.en.softonic.com/
2. SQLyog-13.1.6下載地址：//gsf-fl.softonic.com/1ce/278/b337b72b7ac51c9fcc627a7e1aa8a7710b/SQLyog-13.1.6-0.x64Community.exe?Expires=1609550838&Signature=d64bccebb6402a44cda8f80f9016fa852af514d0&url=//sqlyog.en.softonic.com&Filename=SQLyog-13.1.6-0.x64Community.exe
3. 按提示安裝，免費版不需要註冊，專業版需要註冊，如果註冊，可以嘗試下面的註冊碼。
   1. 註冊名：kuangshen
   2. 註冊碼：8d8120df-a5c3-4989-8f47-5afc79c56e7c

連接資料庫

簡單操作

1. 新建資料庫school，基字符集：utf8，資料庫排序規則：utf8_general_ci。

utf8_unicode_ci和utf8_general_ci對中、英文來說沒有實質的差別。
utf8_general_ci 校對速度快，但準確度稍差。
utf8_unicode_ci 準確度高，但校對速度稍慢。
如果你的應用有德語、法語或者俄語，請一定使用utf8_unicode_ci。一般用utf8_general_ci就夠了。
ci是 case insensitive, 即 “大小寫不敏感”, a 和 A 會在字元判斷中會被當做一樣的；
bin 是二進位, a 和 A 會別區別對待。
例如你運行：
SELECT * FROM table WHERE txt = ‘a’
那麼在utf8_bin中你就找不到 txt = ‘A’ 的那一行， 而 utf8_general_ci 則可以。
utf8_general_ci 不區分大小寫，這個你在註冊用戶名和郵箱的時候就要使用。
utf8_general_cs 區分大小寫，如果用戶名和郵箱用這個 就會照成不良後果
utf8_bin:字元串每個字元串用二進位數據編譯存儲。 區分大小寫，而且可以存二進位的內容

每一個SQLyog的執行操作，本質就是對應了一個SQL語句，都可以在軟體的歷史記錄中查看。
如：創建資料庫，在歷史記錄中如下，本質是執行語句：CREATE DATABASE schoolCHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 新建表student，引擎：InnoDB，字符集：utf8，核對：utf8_general_ci。

對應語句如下

CREATE TABLE `school`.`student` ( `id` INT(10) NOT NULL COMMENT '學員ID', `name` VARCHAR(100) NOT NULL COMMENT '學員姓名', `age` INT(3) NOT NULL COMMENT '學員年齡', PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

3. 查看錶：右鍵點擊student，打開表數據窗口，查看錶數據窗口。

4. 添加記錄：在表數據窗口，輸入記錄，點擊保存或者點擊刷新，保存記錄。

INSERT INTO `school`.`student` (`id`, `name`, `age`) VALUES ('1003', 'zhwj', '66');

命令行連接資料庫

單行注釋：–
多行注釋：/* */

mysql -u root -p --連接資料庫

exit; --退出連接

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

flush privileges; --刷新許可權

--------------------------------------------------

show databases; --查看所有的資料庫

use school; --切換資料庫

show tables; --查看資料庫中所有的表

describe student; --查看錶的結構
describe student id; --查看錶中欄位id的資訊

create database westos; --創建資料庫
CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci; --創建資料庫

操作資料庫

SQL語言的分類

SQL語言共分為四大類：數據查詢語言DQL，數據操縱語言DML，數據定義語言DDL，數據控制語言DCL。

注意事項

1. 學習順序：操作資料庫>操作數據表>操作數據。
2. mysql關鍵字不區分大小寫。
3. 單行注釋：–
4. 多行注釋：/* */
5. 字符集的區別：utf8_unicode_ci、utf8_general_ci、utf8_general_cs、utf8_bin。

utf8_unicode_ci和utf8_general_ci對中、英文來說沒有實質的差別。
utf8_general_ci 校對速度快，但準確度稍差。
utf8_unicode_ci 準確度高，但校對速度稍慢。
如果你的應用有德語、法語或者俄語，請一定使用utf8_unicode_ci。一般用utf8_general_ci就夠了。
ci是 case insensitive, 即 “大小寫不敏感”, a 和 A 會在字元判斷中會被當做一樣的；
bin 是二進位, a 和 A 會別區別對待。
例如你運行：
SELECT * FROM table WHERE txt = ‘a’
那麼在utf8_bin中你就找不到 txt = ‘A’ 的那一行， 而 utf8_general_ci 則可以。
utf8_general_ci 不區分大小寫，這個你在註冊用戶名和郵箱的時候就要使用。
utf8_general_cs 區分大小寫，如果用戶名和郵箱用這個 就會照成不良後果
utf8_bin:字元串每個字元串用二進位數據編譯存儲。 區分大小寫，而且可以存二進位的內容

6. 如果表名或者欄位名是一個特殊字元，需要加反引號（tab鍵上面的符號），如：user。
   

操作資料庫

1. 創建資料庫

utf8_unicode_ci和utf8_general_ci對中、英文來說沒有實質的差別。
utf8_general_ci 校對速度快，但準確度稍差。
utf8_unicode_ci 準確度高，但校對速度稍慢。
如果你的應用有德語、法語或者俄語，請一定使用utf8_unicode_ci。一般用utf8_general_ci就夠了。
ci是 case insensitive, 即 “大小寫不敏感”, a 和 A 會在字元判斷中會被當做一樣的；
bin 是二進位, a 和 A 會別區別對待。
例如你運行：
SELECT * FROM table WHERE txt = ‘a’
那麼在utf8_bin中你就找不到 txt = ‘A’ 的那一行， 而 utf8_general_ci 則可以。
utf8_general_ci 不區分大小寫，這個你在註冊用戶名和郵箱的時候就要使用。
utf8_general_cs 區分大小寫，如果用戶名和郵箱用這個 就會照成不良後果
utf8_bin:字元串每個字元串用二進位數據編譯存儲。 區分大小寫，而且可以存二進位的內容

CREATE DATABASE IF NOT EXISTS westos;
CREATE DATABASE IF NOT EXISTS westos1 CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 刪除資料庫

DROP DATABASE IF EXISTS westos1;

3. 使用資料庫

USE school; --切換資料庫
USE `school`; --如果表名或欄位名是一個特殊字元，需要加反引號，如：`school`

4. 查看資料庫

SHOW DATABASES; --查看所有資料庫

5. 查看創建資料庫的語句

SHOW CREATE DATABASE `school`; --查看創建資料庫的語句
--查詢結果
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

數據類型

數值

字元串

日期時間

欄位屬性

1. Unsigned
   1. 無符號數
   2. 聲明了該欄位的值不能為負數
   3. 如果添加負數，就會報錯
2. zerofill
   1. 0填充
   2. 不足的位數，前面用0填充，如：0030
3. 自增
   1. 默認自動在上一條記錄的基礎上+1，可以自定義起始值和步長
   2. 通常用來設計唯一的主鍵
4. 非空
   1. 設置為非空not null時，如果不給它賦值且沒有設置默認值時，就會報錯
   2. null，如果不填寫值，默認就是null
5. 默認
   1. 設置默認的值，如果不填值，就會設置為默認值
      

操作數據表

1. 創建表

-- 如果表名或者欄位名是一個特殊字元，需要加反引號（tab鍵上面的符號），如：`student`
-- AUTO_INCREMENT 自增，默認+1
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '學號',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
	`sex` VARCHAR(1) NOT NULL DEFAULT '女' COMMENT '性別',
	`birthday` DATE DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '學員';

-- 格式
CREATE TABLE [IF NOT EXISTS] `表名` (
	`欄位名` 列類型 [屬性] [索引] [注釋],
  ......
	`欄位名` 列類型 [屬性] [索引] [注釋],
	PRIMARY KEY (`欄位名`)
) [表類型] [字符集] [校對規則] [表注釋];

2. 刪除表

DROP TABLE IF EXISTS `student`;

3. 查看錶

SHOW TABLES; --查看所有表

4. 查看錶結構

DESCRIBE `student`;
DESC `student`;

5. 查看創建數據表的語句

SHOW CREATE TABLE `student`; --查看創建數據表的語句
--查詢結果
CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '學號',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密碼',
  `sex` varchar(1) NOT NULL DEFAULT '女' COMMENT '性別',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  `email` varchar(50) DEFAULT NULL COMMENT '郵箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學員'

修改表

1. 修改表名

-- ALTER TABLE `舊錶名` RENAME AS `新表名`;
ALTER TABLE `student` RENAME AS `student1`;

2. 增加欄位

-- ALTER TABLE `表名` ADD `欄位名` 數據類型 [約束] [注釋];
ALTER TABLE `student1` ADD `phone` INT(11) DEFAULT NULL COMMENT '電話';

3. 修改欄位名及欄位類型、約束、注釋，注意：欄位名欄位類型必填，其它可選。

-- ALTER TABLE `表名` CHANGE `舊欄位名` `新欄位名` 欄位類型 [約束] [注釋];
ALTER TABLE `student1` CHANGE `phone` `tel` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '聯繫方式';

4. 修改欄位類型、約束、注釋，注意：欄位類型必填，其它可選，不能修改欄位名。

-- ALTER TABLE `表名` MODIFY `欄位名` 欄位類型 [約束] [注釋];
ALTER TABLE `student1` MODIFY `tel` INT(11) DEFAULT NULL COMMENT '電話';

5. 刪除欄位

-- ALTER TABLE `表名` DROP `欄位名`;
ALTER TABLE `student1` DROP `tel`;

資料庫引擎

1. INNODB 5.5及之後默認使用

2. MYISAM 5.5之前默認使用
   | | MYISAM | INNODB |
   | — | — | — |
   | 事務支援 | 不支援 | 支援 |
   | 數據行鎖定 | 不支援    （表鎖） | 支援    （行鎖） |
   | 外鍵約束 | 不支援 | 支援 |
   | 全文索引 | 支援 | 不支援 |
   | 表空間的大小 | 較小 | 較大，約為2倍 |

3. 常規使用操作

   1. MYISAM 節約空間，速度較快
   2. INNODB 安全性高，事務的處理，多表多用戶操作

4. 所有的資料庫文件都存在data目錄下，一個目錄對應一個資料庫，本質還是文件的存儲。

5. mysql引擎在物理文件上的區別

   1. INNODB對應文件
      1. *.frm 資料庫目錄下
      2. ibdata1 data目錄下
   2. MYISAM對應文件都在資料庫目錄下
      1. *.frm 表結構定義文件
      2. *.MYD 數據文件（data）
      3. *.MYI 索引文件（index）
         

數據表的字符集編碼

1. mysql默認的字符集編碼是Latin1，不支援中文。
2. 設置數據表字符集編碼的兩種方式，兩種方式最好同時使用，防止創建表時，沒有設置字符集編碼，導致中文亂碼。
   1. 創建表時添加CHARSET=utf8
   2. 在my.ini中配置默認編碼character-set-server=utf8

CHARSET=utf8

character-set-server=utf8

MySQL數據管理

外鍵

1. 創建表時，增加外鍵約束

-- 創建年級表
CREATE TABLE IF NOT EXISTS `grade` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名稱',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '年級';

-- 創建學生表，學生表的grade_id欄位引用年級表的id欄位
-- 定義外鍵key
-- 給這個外鍵添加約束（執行引用（reference））
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名稱',
	`grade_id` INT(10) NOT NULL COMMENT '年級id',
	PRIMARY KEY (`id`),
	KEY `FK_grade_id` (`grade_id`),
	CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '學員';

2. 刪除有外鍵關係的表時，必須先刪除引用的表（從表），再刪除被引用的表（主表）
3. 添加外鍵

-- 創建年級表
CREATE TABLE IF NOT EXISTS `grade` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名稱',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '年級';

-- 創建學員表
CREATE TABLE IF NOT EXISTS `student` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
	`name` VARCHAR(100) NOT NULL COMMENT '名稱',
	`grade_id` INT(10) NOT NULL COMMENT '年級id',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT '學員';

-- 添加外鍵
-- ALTER TABLE `表名` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`);
ALTER TABLE `student`
ADD CONSTRAINT `約束名` FOREIGN KEY (`作為外鍵的欄位名`) REFERENCES `被引用表名` (`被引用欄位名`);

4. 物理外鍵，是資料庫級別的外鍵，不建議使用。
5. 最佳實踐
   1. 資料庫就是單純的表，只用來存數據，只有行（數據）和列（欄位）
   2. 想使用多張表的數據，想使用外鍵，就用程式來實現
      

插入數據

-- insert into 表名(欄位1,欄位2,欄位3) values(值1,值2,值3)
-- 主鍵自增，可以不寫
INSERT INTO `grade`(`name`) VALUES('大一');

-- 插入一條數據，部分欄位
INSERT INTO `grade`(`name`) VALUES('大二');

-- 插入一條數據，全部欄位
INSERT INTO `grade`(`id`,`name`) VALUES(11,'大三');

-- 插入多條數據時，values後面的值，需要用括弧和逗號隔開，如：values(),(),()
-- 插入多條數據，部分欄位
INSERT INTO `grade`(`name`) VALUES('大四'),('大五'),('大六');

-- 插入多條數據，全部數據
INSERT INTO `grade`(`id`,`name`) VALUES(100,'大旗'),(200,'大壩'),(300,'大酒');

修改數據

-- update `表名` set 欄位名1=值/其它欄位值/變數,欄位名2=值 where 條件
-- 修改一個欄位的值
UPDATE `grade` SET `name`='小一' WHERE `id`=1;

-- 修改一個欄位的值為其它欄位的值
UPDATE `grade` SET `name`=`id` WHERE `id`=11;

-- 修改多個欄位的值
UPDATE `grade` SET `id`=20,`name`='小二' WHERE `id`=2;

刪除數據

-- delete from `表名` where 條件
DELETE FROM `grade` WHERE `id`=1;

清空表

-- truncate table `表名`
TRUNCATE TABLE `grade`;

delete和truncate的區別

1. truncate重新設置自增列，計數器歸零。
2. delete自增列不變；但重啟資料庫後，引擎是INNODB的，自增列會從1開始（存在記憶體中，斷電即失）；引擎是MYISAM的，自增列不變（存在文件中，不會丟失）。
3. truncate不會影響事務。
   

where子句運算符

DQL數據查詢語言

DQL

1. 資料庫中最核心的語言，最重要的語言。
2. 使用頻率最高的語言。
   

簡單查詢和別名(as)

-- 查詢全部欄位
SELECT * FROM `grade`;

-- 查詢指定欄位
SELECT `name` FROM `grade`;

-- 別名 AS，欄位和表都可以用別名
SELECT `name` AS `名稱` FROM `grade`;

拼接(concat)和去重(distinct)

-- 函數 concat(a,b,c) 拼接abc，可以拼接字元串或欄位值
SELECT CONCAT('名稱：',`name`) AS `名稱` FROM `grade`;
SELECT CONCAT(`id`,':',`name`) AS `id:名稱` FROM `grade`;

-- 關鍵字 distinct 去重
-- 去除select查詢出來的結果中重複的數據，重複的數據只顯示一條
-- 去重一般配合count使用，用來統計數量，因為distinct不能顯示其它不去重的欄位
-- 單個欄位去重
SELECT DISTINCT `name` FROM `grade`;

-- 多個欄位去重
SELECT DISTINCT `id`,`name` FROM `grade`;

-- 配合count使用，用來統計數量
SELECT COUNT(DISTINCT `name`) AS `count` FROM `grade`;

查詢函數/計算表達式/系統變數

資料庫中的表達式：文本值、列、null、函數、計算表達式、系統變數……

-- select 表達式 [from 表名]

-- 查詢系統版本（函數）
SELECT VERSION();
SELECT VERSION(),`name` FROM `grade`;

-- 用來計算（計算表達式）
SELECT 123*3-12 AS `結果`;
SELECT 123*3-12 AS `結果`,`name` FROM `grade`;
-- 數字類型的欄位計算,其它類型不可以
SELECT `id`/100 FROM `grade`;

-- 查詢自增的默認步長（變數）
-- @a用戶變數，@@a系統變數
SELECT @@auto_increment_increment AS '自增步長';
SELECT @@auto_increment_increment AS '自增步長',`name` FROM `grade`;

where條件字句

1. 作用：檢索數據中符合條件的值。
2. 檢索的條件由一個或者多個表達式組成。
   

邏輯運算符

運算符盡量使用英文字母

-- 查詢成績95到100分的學員
select `name`,`score` from `result` where `score`>=95 and `score`<=100;
select `name`,`score` from `result` where `score` between 95 and 100;

-- 查詢成績不是100分的學員
select `name`,`score` from `result` where `score`!=100;
select `name`,`score` from `result` where not `score`=100;

模糊查詢：比較運算符

-- 通配符,只能用在like中 %代表0到任意個字元，_代表一個字元

select 'name' like '%me';
select 'name' like 'na%';
select 'name' like '%am%';

select 'name' in ('name','na','me');

聯表查詢join

自連接

-- 自連接：可以把一張表看成兩張一樣的表
select a.`id` as '父', b.`id` as '子'
from `menu` as a,`menu` as b
where a.`id`=b.`pid`

排序和分頁

-- 排序 order by
-- 升序 asc
-- 降序 desc
order by `number` desc

-- 分頁 limit 起始下標,條數
-- 起始下標從0開始
limit 0,5

-- 第一頁 limit 0,5
-- 第二頁 limit 5,5
-- 第三頁 limit 10,5
-- 第n頁  limit (n-1) * pageSize, pageSize
-- n:當前頁 pageSize:條數

分組和過濾

group by 分組
having 過濾

子查詢和嵌套查詢

-- 子查詢
select id,name from student
where grade_id=(select id from grade where name='大二')

MySQL函數

常用函數

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

-- 字元串函數
SELECT CHAR_LENGTH('走在路上。。。'); -- 返回字元串長度，以字元為單位
SELECT LENGTH('走在路上。。。'); -- 返回字元串長度，以位元組為單位
SELECT CONCAT('走','在','路','上'); -- 拼接字元串
SELECT INSERT('我在路上',1,2,'你好嗎') ; -- 插入替換，從某個位置開始替換指定長度
SELECT LOWER('Hello'); -- 轉小寫
SELECT UPPER('hello'); -- 轉大寫
SELECT INSTR('helloworld','l'); -- 返回第一次出現子串的索引
SELECT REPLACE('hello','ll','ss'); -- 替換出現的指定字元串
SELECT SUBSTR('helloworld',2,5); -- 返回指定的子字元串(源字元串,截取的位置,截取的長度)

-- 時間和日期函數
SELECT CURDATE(); -- 獲取當前日期
SELECT CURTIME(); -- 獲取當前時間
SELECT NOW(); -- 獲取當前日期和時間
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(); -- 查詢版本

聚合函數

-- 聚合函數
SELECT COUNT(`name`) FROM student; -- count(欄位)，會忽略所有的null值，欄位是主鍵時，效率最高
SELECT COUNT(*) FROM student; -- count(*)，不會忽略null值，本質是計算行數
SELECT COUNT(1) FROM student; -- count(1)，不會忽略null值，本質是計算行數

資料庫級別的MD5加密

1. 什麼是MD5?

2. 1996年後該演算法被證實存在弱點，可以被加以破解，對於需要高度安全性的數據，專家一般建議改用其他演算法，如SHA-2。2004年，證實MD5演算法無法防止碰撞（collision），因此不適用於安全性認證，如SSL公開密鑰認證或是數字簽名等用途。
3. MD5由MD4、MD3、MD2改進而來，主要增強演算法複雜度和不可逆性。MD5演算法因其普遍、穩定、快速的特點，仍廣泛應用於普通數據的加密保護領域。
4. MD5不可逆，兩個相同的值加密後的值是一樣的。
5. MD5破解網站的原理，就是背後有一個字典，根據MD5加密後的值找加密前的值，只能找到簡單密碼。

-- 測試MD5加密
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(100) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 明文加密
INSERT INTO `testmd5` VALUES(1,'zhangs','123456'),(2,'lis','123456'),(3,'wangw','123456');

-- MD5加密
INSERT INTO `testmd5` VALUES(4,'zhaol',MD5('123456')),(5,'maq',MD5('123456'));

-- 校驗密碼
SELECT * FROM `testmd5` WHERE `name`='zhaol' AND `pwd`=MD5('123456');

事務

什麼是事務

1. 要麼都成功，要麼都失敗。
2. 將一組SQL放在一個批次中去執行。
   

事務原則ACID原則

1. 原子性：多個步驟要麼一起成功，要麼一起失敗，不能只成功一個。（同進同退）
2. 一致性：最終一致性，開始前的總值等於結束後的總值。（能量守恆）
3. 隔離性：針對多個用戶同時操作，其他事務不會影響本次事務。（互不干擾）
4. 持久性：事務沒有提交，恢復到原狀，事務一旦提交，不可改變。（不可逆）
   

隔離導致的問題

1. 臟讀：一個事務讀取了另一個事務未提交的數據。
2. 不可重複讀：在一個事務內讀取表中的一行數據，多次讀取結果不同。
3. 虛度（幻讀）：一個事務內讀取到了別的事務插入的數據，導致前後讀取不一致。
   

手動處理事務

-- 事務

/*
mysql默認開啟事務自動提交
set autocommit=0; -- 關閉
set autocommit=1; -- 開啟（默認）
*/

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

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

INSERT xx;
INSERT xx;

-- 提交 commit
COMMIT;

-- 回滾 rollback
ROLLBACK;

-- 事務結束
SET autocommit=1; -- 開啟自動提交

-- 了解即可
SAVEPOINT 保存點 -- 設置一個事務的保存點
ROLLBACK TO SAVEPOINT 保存點 -- 回滾到保存點
RELEASE SAVEPOINT 保存點 -- 撤銷保存點

模擬轉賬

-- 轉賬
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 COLLATE=utf8_general_ci;

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減500
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B'; -- B加500

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

SET autocommit=1; -- 開啟自動提交

SELECT @@autocommit; -- 查詢自動提交狀態

索引

MySQL官方對索引的定義為： 索引（Index）是幫助MySQL高效獲取數據的數據結構。提取句子主幹，就可以得到索引的本質：索引是數據結構。

索引的分類

1. 主鍵索引（PRIMARY KEY）
   1. 唯一的標識，值不可重複，只能有一個列作為主鍵
2. 唯一索引（UNIQUE KEY）
   1. 避免重複的列出現，值不可重複，可以多個列都標識為唯一索引
3. 常規索引（KEY/INDEX）
   1. 默認的，index或key關鍵字來設置
4. 全文索引（FullText）
   1. 在特定的資料庫引擎下才有，之前MyISAM支援，現在InnoDB也在支援，具體自查
   2. 快速定位數據
      

索引的創建

-- 顯示所有索引
SHOW INDEX FROM `student`;

-- 索引的創建
-- 1.在創建表的時候給欄位增加索引
-- 2.創建完畢後，alter增加索引
-- 3.創建完畢後，create增加索引

-- 索引命名：id_表名_欄位名
-- 添加索引：create index 索引名 on 表名 (欄位名);
CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec

-- 增加一個全文索引 索引名(列名)
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName` (`studentName`);

-- EXPLAIN 分析SQL執行的狀況
EXPLAIN SELECT * FROM `student`; -- 非全文索引
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentName`) AGAINST('劉'); -- 全文索引

測試索引

插入100萬數據

CREATE TABLE `user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`phone` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 插入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 `user`(`name`,`phone`,`pwd`) VALUES(CONCAT('用戶',i),CONCAT('18',FLOOR(RAND()*1000000000)),UUID());
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data();

SELECT COUNT(`id`) FROM `user`;

分析查詢語句

SELECT * FROM `user` WHERE `name`='用戶9999'; -- 0.748 sec
SELECT * FROM `user` WHERE `name`='用戶9999'; -- 0.689 sec
SELECT * FROM `user` WHERE `name`='用戶9999'; -- 0.765 sec

EXPLAIN SELECT * FROM `user` WHERE `name`='用戶9999';

-- 索引命名：id_表名_欄位名
-- 添加索引：create index 索引名 on 表名 (欄位名);
CREATE INDEX `id_user_name` ON `user` (`name`); -- 15.772 sec

SELECT * FROM `user` WHERE `name`='用戶9999'; -- 0.052 sec

SHOW INDEX FROM `user`;

ALTER TABLE `user` DROP INDEX `id_user_name`;

創建索引前查詢

創建索引後查詢

結論

索引在小數據量的時候，用處不大，但是在大數據量的時候，區別十分明顯。

索引原則

1. 索引不是越多越好
2. 不要對經常變動數據的表加索引
3. 小數據量的表不需要加索引
4. 索引一般加在常用來查詢的欄位上
   

索引的數據結構

1. InnoDB的默認索引數據結構是Btree
2. Hash
   

許可權管理和備份

用戶管理

1. 用戶表：mysql.user
2. 用戶管理本質是對用戶表數據的增刪改查

-- 創建用戶 CREATE USER 用戶名 IDENTIFIED BY '密碼';
CREATE USER qing IDENTIFIED BY '123456';

-- 修改當前用戶密碼
SET PASSWORD=PASSWORD('123456');

-- 修改指定用戶密碼
SET PASSWORD FOR qing=PASSWORD('123456');

-- 重命名 RENAME USER 原用戶名 TO 新用戶名;
RENAME USER qing TO qing2;

-- 用戶授權 GRANT ALL PRIVILEGES ON 庫.表 TO 用戶名;
-- ALL PRIVILEGES 除了給人授權外的其他許可權
-- *.* 所有庫和表
GRANT ALL PRIVILEGES ON *.* TO qing2;

-- 查詢許可權
SHOW GRANTS FOR qing2; -- 查看指定用戶的許可權
-- qing2許可權 GRANT ALL PRIVILEGES ON *.* TO 'qing2'@'%'
SHOW GRANTS FOR root@'%';
-- root許可權 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION

-- 撤銷許可權 REVOKE ALL PRIVILEGES ON 庫.表 FROM 用戶名;
REVOKE ALL PRIVILEGES ON *.* FROM qing2;

-- 刪除用戶
DROP USER qing2;

MySQL備份

1. 為什麼要備份？
   1. 保證重要的數據不丟失
   2. 數據轉移
2. MySQL資料庫備份的方式
   1. 直接拷貝物理文件data目錄
   2. 使用可視化工具中的導出功能
   3. 使用命令行導出 mysqldump

# 導出表
# mysqldump -h IP -u 用戶名 -p 密碼 資料庫 表名 >盤符/文件名
C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 導出多個表
mysqldump -h192.168.10.226 -uroot -p123456 school grade >d:/a.sql

# 導出庫
mysqldump -h192.168.10.226 -uroot -p123456 school >d:/a.sql

# 導入
# 登錄的情況下，切換到指定的資料庫
# source 備份文件
source d:/a.sql

D:\>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;
Database changed
mysql> source d:/a.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 或者 mysql -u用戶名 -p密碼 庫名< 備份文件

規範資料庫設計

為什麼需要設計

1. 當資料庫比較複雜的時候，我們就需要設計了
2. 糟糕的資料庫設計
   1. 數據冗餘，浪費空間
   2. 資料庫插入和刪除都會麻煩、異常（屏蔽使用物理外鍵）
   3. 程式的性能差
3. 良好的資料庫設計
   1. 節省記憶體空間
   2. 保證資料庫的完整性
   3. 方便我們開發系統
4. 軟體開發中，關於資料庫的設計
   1. 分析需求：分析業務和需要處理的資料庫需求
   2. 概要設計：設計關係圖 E-R圖
      

設計資料庫的步驟（個人部落格）

1. 收集資訊，分析需求
   1. 用戶表（用戶登錄註銷，用戶的個人資訊，寫部落格，創建分類）
   2. 分類表（文章分類，誰創建的）
   3. 文章表（文章資訊）
   4. 友鏈表（友鏈資訊）
   5. 自定義表（系統資訊，關鍵字，主欄位）
2. 標識實體（將需求落地為具體的表、欄位）
   

三大範式

第一範式（1NF）-原子性

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

第二範式（2NF）

前提：滿足第一範式
每張表只描述一件事情

第三範式（3NF）

前提：滿足第一範式和第二範式
確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關。

規範性和性能的問題

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

1. 考慮商業化的需求和目標（成本，用戶體驗），資料庫的性能更加重要
2. 在考慮性能的時候，需要適當的考慮一下規範性
3. 故意給某些表增加一些冗餘的欄位（從多表關聯查詢變為單表查詢）
4. 故意增加一些統計列（從大數據量的統計降低為小數據量的查詢）
   

JDBC

資料庫驅動

程式通過資料庫驅動，和資料庫打交道。

JDBC

1. SUN公司為了簡化開發人員對資料庫的統一操作，提供了一個Java操作資料庫的規範，俗稱JDBC。
2. 這些規範的實現由具體的廠商去做。
3. 對於開發人員來說，我們只需要掌握JDBC介面的操作即可。

相關jar包

1. java.sql
2. javax.sql
3. 資料庫驅動包 mysql-connector-java-5.1.49.jar
   

第一個JDBC程式

1. 創建測試資料庫。

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	`id` INT(10) NOT NULL,
	`name` VARCHAR(100) NOT NULL,
	`pwd` VARCHAR(100) NOT NULL,
	`email` VARCHAR(100),
	`birthday` DATE,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `users`(`id`,`name`,`pwd`,`email`,`birthday`) 
VALUES(1,'zhangsf','123456','[email protected]','1988-03-13'),
(2,'zhangwj','123456','[email protected]','1983-04-03'),
(3,'zhangyq','123456','[email protected]','1989-05-23');

2. 創建一個普通項目。

3. 導入資料庫驅動。
   1. 創建lib目錄，拷貝mysql-connector-java-5.1.49.jar
   2. 添加到項目庫，右鍵點擊Add as Library

4. 編寫測試程式碼。

package com.qing.demo01;

import java.sql.*;

/**
 * 第一個JDBC程式
 */
public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.載入驅動
        Class.forName("com.mysql.jdbc.Driver");

        //2.用戶資訊和url
        //useUnicode=true 支援中文編碼
        //characterEncoding=utf8 設定中文字符集編碼為utf8
        //useSSL=true 使用安全的連接，如果伺服器沒有SSL證書會報錯，使用false
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String user = "root";
        String password = "123456";

        //3.連接成功，獲取資料庫對象Connection
        Connection connection = DriverManager.getConnection(url,user,password);

        //4.創建執行SQL的對象
        Statement statement = connection.createStatement();

        //5.執行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("pwd"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("=====================================");
        }

        //6.釋放連接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步驟總結

1. 載入驅動
2. 連接資料庫 DriverManager
3. 創建執行SQL的對象 Statement
4. 獲取返回的結果集
5. 釋放連接
   

DriverManager

//驅動管理
//DriverManager.registerDriver(new Driver());
//載入驅動，建議使用，Driver類中的靜態程式碼塊就是執行的上面的方法
Class.forName("com.mysql.jdbc.Driver");

//連接資料庫
Connection connection = DriverManager.getConnection(url,user,password);
//Connection代表資料庫對象，可以執行資料庫層面的操作
//資料庫設置自動提交
connection.setAutoCommit(false);
connection.setAutoCommit(true);
//事務提交
connection.commit();
//事務回滾
connection.rollback();

URL

//資料庫連接資訊
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//String url = "jdbc:mysql://主機地址:埠號/資料庫名?參數1&參數2&參數3";

Statement

//執行SQL的對象
boolean execute = statement.execute("");//執行任何SQL，但需要判斷，效率最低
ResultSet resultSet = statement.executeQuery();//執行查詢，返回查詢結果集
int i = statement.executeUpdate();//執行更新、插入、刪除，返回受影響的行數

插入示例

刪除示例

更新示例

查詢示例

釋放資源

resultSet.close();
statement.close();
connection.close();//耗資源，用完關掉

初次優化，提取工具類

1. 創建db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=123456

2. 提取工具類JdbcUtils：載入驅動，獲取連接，釋放連接資源
3. 測試增刪改查

package com.qing.demo02.utils;

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

/**
 * JDBC工具類
 */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String user = 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");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            //1.驅動只需要載入一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //獲取連接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    //釋放連接資源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

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) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //獲取連接
            conn = JdbcUtils.getConnection();
            //獲取執行SQL的對象
            st = conn.createStatement();
            //執行SQL
            String sql = "insert into `users`(`id`,`name`,`pwd`,`email`,`birthday`) values(4,'zhangyq','123456','zhangyq2163.com','1988-09-09')";
            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //釋放連接資源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

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) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //獲取連接
            conn = JdbcUtils.getConnection();
            //獲取執行SQL的對象
            st = conn.createStatement();
            //執行SQL
            String sql = "update `users` set `name`='zhangtj' where `id`=3";
            int i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //釋放連接資源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

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) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //獲取連接
            conn = JdbcUtils.getConnection();
            //獲取執行SQL的對象
            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 e) {
            e.printStackTrace();
        } finally {
            //釋放連接資源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

package com.qing.demo02;

import com.qing.demo02.utils.JdbcUtils;

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) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //獲取連接
            conn = JdbcUtils.getConnection();
            //獲取執行SQL的對象
            st = conn.createStatement();
            //執行SQL
            String sql = "select * from `users` where `id`=3";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //釋放連接資源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入問題

//執行SQL
String id = "3 or 1=1";
String sql = "select * from `users` where `id`=" + id;
rs = st.executeQuery(sql);

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

--當輸入了上面的用戶名和密碼，上面的SQL語句變成：
SELECT * FROM user_table WHERE username=
'』or 1 = 1 -- and password='』

"""
--分析SQL語句：
--條件後面username=」or 1=1 用戶名等於 」 或1=1 那麼這個條件一定會成功；

--然後後面加兩個-，這意味著注釋，它將後面的語句注釋，讓他們不起作用，這樣語句永遠都--能正確執行，用戶輕易騙過系統，獲取合法身份。
--這還是比較溫柔的，如果是執行
SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
--其後果可想而知…
"""

PreparedStatement

1. PreparedStatement可以防止SQL注入，並且效率更高。
   

為什麼PreparedStatement能防止sql注入呢？

因為sql語句是預編譯的，而且語句中使用了佔位符，規定了sql語句的結構。用戶可以設置”?”的值，但是不能改變sql語句的結構，因此想在sql語句後面加上如「or 1=1」實現sql注入是行不通的。
實際開發中，一般採用PreparedStatement訪問資料庫，它不僅能防止sql注入，還是預編譯的(不用改變一次參數就要重新編譯整個sql語句，效率高)，此外，它執行查詢語句得到的結果集是離線的，連接關閉後，仍然可以訪問結果集。

package com.qing.demo03;

import com.qing.demo02.utils.JdbcUtils;

import java.sql.*;

/**
 * 測試查詢
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //獲取連接
            conn = JdbcUtils.getConnection();
            //使用?佔位符代替參數
            String sql = "select * from `users` where `id`=?";
            //預編譯，先寫SQL，然後不執行
            st = conn.prepareStatement(sql);
            //手動給參數賦值
            st.setString(1,"3");
            //執行
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //釋放連接資源
            JdbcUtils.release(conn,st,rs);
        }
    }
}

使用IDEA連接資料庫

1. 連接mysql；

2. 選擇mysql驅動；

3. 填寫mysql用戶名，密碼，測試連接；

4. 選擇資料庫；

5. 雙擊數據表，打開表；修改數據，提交；打開命令行，寫SQL；

JDBC操作事務

1. 開啟事務：關閉資料庫自動提交，自動會開啟事務。
2. 一組業務執行完畢，提交事務。
3. 可以在catch語句中顯式的定義回滾語句，也可以不寫，默認失敗就會回滾。
4. finally中開啟資料庫自動提交，然後釋放連接資源。

package com.qing.demo04;

import com.qing.demo02.utils.JdbcUtils;

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

/**
 * 測試事務
 */
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = 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();
            String sql2 = "update `account` set `money`=`money`+100 where `name`='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //提交事務
            conn.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            //顯式回滾，也可以不寫，默認失敗自動回滾
//            try {
//                conn.rollback();
//            } catch (SQLException e1) {
//                e1.printStackTrace();
//            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

資料庫連接池

1. 資料庫連接–》執行完畢–》釋放連接
2. 連接–釋放，十分浪費系統資源
3. 池化技術：準備一些預先的資源，過來就連接預先準備好的
4. 最小連接數
5. 最大連接數
6. 等待超時
7. 編寫連接池，實現一個介面 DataSource
   

常用連接池

使用了這些資料庫連接池之後，我們在項目開發中就不需要編寫連接資料庫的程式碼了！

1. DBCP
   1. jar包：commons-dbcp，commons-pool
   2. 配置文件：dbcpconfig.properties
   3. 工廠模式創建數據源：DataSource dataSource = BasicDataSourceFactory.creatDataSource(properties);
   4. 從數據源獲取連接：dataSource.getConnection();
2. C3P0
   1. jar包：c3p0，mchange-commons-java
   2. 配置文件：c3p0-config.xml
   3. 創建數據源：DataSource dataSource = new ComboPooledDataSource(“MySQL”);//不寫參數就是默認數據源，寫參數就是指定數據源
   4. 從數據源獲取連接：dataSource.getConnection();
3. Druid：阿里巴巴