主頁 > 資料庫 > 010_MySQL

010_MySQL

2021-01-15 07:45:42 資料庫

目錄
  • 初識MySQL
    • 為什么學習資料庫
    • 什么是資料庫
    • 資料庫分類
    • MySQL簡介
  • Windows安裝MySQL
    • 安裝建議
    • 軟體下載
    • 安裝步驟
  • 安裝SQLyog
    • 下載安裝
    • 連接資料庫
    • 簡單操作
  • 命令列連接資料庫
  • 操作資料庫
    • SQL語言的分類
    • 注意事項
    • 操作資料庫
    • 資料型別
      • 數值
      • 字串
      • 日期時間
    • 欄位屬性
    • 操作資料表
      • 修改表
    • 資料庫引擎
    • 資料表的字符集編碼
  • MySQL資料管理
    • 外鍵
    • 插入資料
    • 修改資料
    • 洗掉資料
    • 清空表
    • delete和truncate的區別
    • where子句運算子
  • DQL資料查詢語言
    • DQL
    • 簡單查詢和別名(as)
    • 拼接(concat)和去重(distinct)
    • 查詢函式/計算運算式/系統變數
    • where條件字句
      • 邏輯運算子
      • 模糊查詢：比較運算子
    • 聯表查詢join
    • 自連接
    • 排序和分頁
    • 分組和過濾
    • 子查詢和嵌套查詢
  • MySQL函式
    • 常用函式
    • 聚合函式
  • 資料庫級別的MD5加密
  • 事務
    • 什么是事務
    • 事務原則ACID原則
    • 隔離導致的問題
    • 手動處理事務
    • 模擬轉賬
  • 索引
    • 索引的分類
    • 索引的創建
    • 測驗索引
      • 插入100萬資料
      • 分析查詢陳述句
      • 結論
    • 索引原則
    • 索引的資料結構
  • 權限管理和備份
    • 用戶管理
    • MySQL備份
  • 規范資料庫設計
    • 為什么需要設計
    • 設計資料庫的步驟（個人博客）
    • 三大范式
      • 第一范式（1NF）-原子性
      • 第二范式（2NF）
      • 第三范式（3NF）
      • 規范性和性能的問題
  • JDBC
    • 資料庫驅動
    • JDBC
    • 相關jar包
    • 第一個JDBC程式
    • 步驟總結
    • DriverManager
    • URL
    • Statement
    • 釋放資源
    • 初次優化，提取工具類
    • SQL注入問題
    • PreparedStatement
      • 為什么PreparedStatement能防止sql注入呢？
  • 使用IDEA連接資料庫
  • JDBC操作事務
  • 資料庫連接池
    • 常用連接池

初識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簡介

image.png

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

Windows安裝MySQL

安裝建議

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

軟體下載

  1. 下載地址：https://downloads.mysql.com/archives/community/
  2. MySQL5.7.31 64位下載地址：https://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
  1. 安裝MySQL服務：啟動管理員模式下的cmd，并將路徑切換到MySQL下的bin目錄，然后輸入mysqld -install，成功，回傳：Service successfully installed.
cd /d D:\environment\mysql-5.7.31\bin
mysqld -install
  1. 初始化資料檔案：輸入mysqld --initialize-insecure --user=mysql，成功，生成data目錄，data目錄下的目錄對應資料庫，如：mysql，sys，performance_schema，
mysqld --initialize-insecure --user=mysql

image.png

  1. 啟動MySQL：輸入net start mysql，成功，回傳：MySQL 服務正在啟動 MySQL服務已經啟動成功，并且任務管理器中出現MySQL服務，
net start mysql
  1. 進入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
  1. 更改root密碼，其實就是修改mysql資料庫的user表的authentication_string欄位，password()函式用于md5加密，
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
  1. 重繪權限：flush privileges，
flush privileges;
  1. 注釋掉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
  1. 重啟mysql：net stop mysql，net start mysql
# 退出mysql
exit
# 關閉mysql
net stop mysql
# 啟動mysql
net start mysql
  1. 測驗，
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. 下載地址：https://sqlyog.en.softonic.com/
  2. SQLyog-13.1.6下載地址：https://gsf-fl.softonic.com/1ce/278/b337b72b7ac51c9fcc627a7e1aa8a7710b/SQLyog-13.1.6-0.x64Community.exe?Expires=1609550838&Signature=d64bccebb6402a44cda8f80f9016fa852af514d0&url=https://sqlyog.en.softonic.com&Filename=SQLyog-13.1.6-0.x64Community.exe
  3. 按提示安裝，免費版不需要注冊，專業版需要注冊，如果注冊，可以嘗試下面的注冊碼，
    1. 注冊名：kuangshen
    2. 注冊碼：8d8120df-a5c3-4989-8f47-5afc79c56e7c

image.png

連接資料庫

image.png

簡單操作

  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:字串每個字串用二進制資料編譯存盤， 區分大小寫，而且可以存二進制的內容

image.png

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

image.png

CREATE DATABASE `school`CHARACTER SET utf8 COLLATE utf8_general_ci;
  1. 新建表student，引擎：InnoDB，字符集：utf8，核對：utf8_general_ci，

image.png

對應陳述句如下

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;
  1. 查看表：右鍵點擊student，打開表資料視窗，查看表資料視窗，

image.png

  1. 添加記錄：在表資料視窗，輸入記錄，點擊保存或者點擊重繪，保存記錄，
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:字串每個字串用二進制資料編譯存盤， 區分大小寫，而且可以存二進制的內容

  1. 如果表名或者欄位名是一個特殊字符，需要加反引號（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;
  1. 洗掉資料庫
DROP DATABASE IF EXISTS westos1;
  1. 使用資料庫
USE school; --切換資料庫
USE `school`; --如果表名或欄位名是一個特殊字符，需要加反引號，如：`school`
  1. 查看資料庫
SHOW DATABASES; --查看所有資料庫
  1. 查看創建資料庫的陳述句
SHOW CREATE DATABASE `school`; --查看創建資料庫的陳述句
--查詢結果
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */

資料型別

數值

型別 位元組數 含義 備注 對應java
tinyint 1個位元組 十分小的數 Integer
smallint 2個位元組 較小的數 Integer
mediumint 3個位元組 中等的數 Integer
int 4個位元組 整數 常用 Long
bigint 8個位元組 較大的數 BigInteger
float 4個位元組 浮點數 不常用，會精度丟失 Float
double 8個位元組 浮點數 不常用，會精度丟失 Double
decimal 字串形式的浮點數 金融計算時常用 BigDecimal

字串

型別 長度 含義 備注 對應java
char 0~255 固定大小字串 String
varchar 0~65535 可變字串 常用 String
tinytext 2^8 - 1 微型文本 String
text 2^16 - 1 文本串 常用，保存大文本 String

日期時間

型別 格式 含義 備注 對應java
date YYYY-MM-DD 日期 Date
time HH:mm:ss 時間 Time
datetime YYYY-MM-DD HH:mm:ss 日期+時間 常用 Timestamp
timestamp 時間戳，1970.1.1到現在的毫秒數 常用 Timestamp
year YYYY 年份 Date

欄位屬性

  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 (`欄位名`)
) [表型別] [字符集] [校對規則] [表注釋];
  1. 洗掉表
DROP TABLE IF EXISTS `student`;
  1. 查看表
SHOW TABLES; --查看所有表
  1. 查看表結構
DESCRIBE `student`;
DESC `student`;
  1. 查看創建資料表的陳述句
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`;
  1. 增加欄位
-- ALTER TABLE `表名` ADD `欄位名` 資料型別 [約束] [注釋];
ALTER TABLE `student1` ADD `phone` INT(11) DEFAULT NULL COMMENT '電話';
  1. 修改欄位名及欄位型別、約束、注釋，注意：欄位名欄位型別必填，其它可選，
-- ALTER TABLE `表名` CHANGE `舊欄位名` `新欄位名` 欄位型別 [約束] [注釋];
ALTER TABLE `student1` CHANGE `phone` `tel` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '聯系方式';
  1. 修改欄位型別、約束、注釋，注意：欄位型別必填，其它可選，不能修改欄位名，
-- ALTER TABLE `表名` MODIFY `欄位名` 欄位型別 [約束] [注釋];
ALTER TABLE `student1` MODIFY `tel` INT(11) DEFAULT NULL COMMENT '電話';
  1. 洗掉欄位
-- 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 '學員';
  1. 洗掉有外鍵關系的表時，必須先洗掉參考的表（從表），再洗掉被參考的表（主表）
  2. 添加外鍵
-- 創建年級表
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 `被參考表名` (`被參考欄位名`);
  1. 物理外鍵，是資料庫級別的外鍵，不建議使用，
  2. 最佳實踐
    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子句運算子

運算子 含義
= 等于
<> 或 != 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
between ... and ... 在某個范圍內
型別是數字時，包括頭和尾
型別是date時，包括頭和尾
型別是datetime時，帶時分秒，包括頭和尾；不帶時分秒，包頭不包尾
and 與
or 或

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. 檢索的條件由一個或者多個運算式組成，

邏輯運算子

運算子盡量使用英文字母

運算子 語法 描述
and    && a and b    a && b 邏輯與
or
not    ! not a    ! a 邏輯非 
-- 查詢成績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;

模糊查詢：比較運算子

運算子 語法 描述
is null a is null 為null
is not null a is not null 不為null
between ... and ... a between b and c
a betwwen 5 and 10		 在某個范圍內
型別是數字時，包括頭和尾
型別是date時，包括頭和尾
型別是datetime時，帶時分秒，包括頭和尾；不帶時分秒，包頭不包尾
like a like b
a like 'b'
a like '%b'
a like 'b%'
a like '%b%'
a like '_b'
alike 'b'		 a匹配b
通配符,只能用在like中 %代表0到任意個字符，_代表一個字符
in a in (a1,a2,a3...) a是否在括號中的值中 
-- 通配符,只能用在like中 %代表0到任意個字符，_代表一個字符

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

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

聯表查詢join

image.png

操作 描述
inner join 兩個表都必須匹配
left join 左表為主表，左表全部，右表沒有補null
right join 右表為主表，右表全部，左表沒有補null

自連接

-- 自連接：可以把一張表看成兩張一樣的表
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(); -- 查詢版本

聚合函式

函式名稱 描述
count() 計數
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
...... 
-- 聚合函式
SELECT COUNT(`name`) FROM student; -- count(欄位)，會忽略所有的null值，欄位是主鍵時，效率最高
SELECT COUNT(*) FROM student; -- count(*)，不會忽略null值，本質是計算行數
SELECT COUNT(1) FROM student; -- count(1)，不會忽略null值，本質是計算行數

資料庫級別的MD5加密

  1. 什么是MD5?

image.png

  1. 1996年后該演算法被證實存在弱點，可以被加以破解，對于需要高度安全性的資料，專家一般建議改用其他演算法，如SHA-2，2004年，證實MD5演算法無法防止碰撞（collision），因此不適用于安全性認證，如SSL公開密鑰認證或是數字簽名等用途，
  2. MD5由MD4、MD3、MD2改進而來，主要增強演算法復雜度和不可逆性，MD5演算法因其普遍、穩定、快速的特點，仍廣泛應用于普通資料的加密保護領域，
  3. MD5不可逆，兩個相同的值加密后的值是一樣的，
  4. 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原則

image.png

  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`;

創建索引前查詢
image.png
創建索引后查詢
image.png

結論

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

索引原則

  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）-原子性

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

第二范式（2NF）

前提：滿足第一范式
每張表只描述一件事情
image.png

第三范式（3NF）

前提：滿足第一范式和第二范式
確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關，
image.png

規范性和性能的問題

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

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

JDBC

資料庫驅動

程式通過資料庫驅動，和資料庫打交道，
image.png

JDBC

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

image.png

相關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');
  1. 創建一個普通專案，

image.png

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

image.pngimage.png
image.png

  1. 撰寫測驗代碼，

image.png

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

image.png

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

插入示例
image.png
洗掉示例
image.png
更新示例
image.png
查詢示例
image.png

釋放資源

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

初次優化，提取工具類

image.png

  1. 創建db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=123456
  1. 提取工具類JdbcUtils：加載驅動，獲取連接，釋放連接資源
  2. 測驗增刪改查
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注入問題

image.png

//執行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；

image.png

  1. 選擇mysql驅動；

image.png

  1. 填寫mysql用戶名，密碼，測驗連接；

image.png

  1. 選擇資料庫；

image.png

  1. 雙擊資料表，打開表；修改資料，提交；打開命令列，寫SQL；

image.png

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：阿里巴巴

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/248957.html

標籤：其他

上一篇：FATAL: password authentication failed for user "XXX"

下一篇：資料庫概論 （四）完整性

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂