MySql學習總結

smallkneif發表於2020-09-24

MySql

MySql總結

命令列匯出

mysqldump -h主機名 -u使用者名稱 -p密碼資料庫表1 表2 >磁碟位置檔名

索引利弊

弊端：索引本身很大，可以放在硬碟
不是所有情況都適合
降低增刪改的效率
優勢，提高了I/O的效率
降低CPU使用率
###3層B-tree可以存放上百萬的資料
一般指的B+tree：資料全部存放在葉節點中
B+樹查詢任意資料次數是n次，即數的高度

索引型別

單值索引：單列，例如只有一個欄位name的索引
唯一索引：不能重複。例如不能用年齡age
複合索引：多個列構成的索引，相當於新華字典的二級目錄 z --zhai

9、規範資料庫設計

9.1、為什麼需要設計

當資料庫比較複雜的時候就需要設計了

糟糕的資料可設計

資料冗餘，浪費空間
資料庫插入刪除都特別麻煩、異常
程式的效能差

良好的資料庫設計

節省記憶體空間
保證資料庫完整性
方便開發系統

軟體開發中，關於資料庫的設計

分析需求：分析業務和需要處理業務的需求
概要設計：設計關係圖 E-R圖

設計資料庫的步驟：(個人部落格)

分析需求收集資訊
- 使用者表 (使用者登入登出，使用者個人資訊，寫部落格，建立分類)
- 分類表（文章分類，誰建立的，）
- 文章表（文章的資訊）
- 友情連結（友情連結）
- 評論表
- 自定義表（系統資訊，某個關鍵的字或者主欄位）
- 說說表（發表心情，id content createtime）
標識實體（需求落實到每個欄位）
標識實體之間的關係
- 寫部落格：user–>blog
- 建立分類：user–>category
- 關注：user–>user
- 評論：user–>user–>bolg

9.2、三大正規化

為什麼需要資料規範化？

資訊重複
更新異常
插入異常
- 無法正常顯示資訊
刪除異常
- 丟失有效資訊
三大正規化

第一正規化（1NF）

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

第二正規化（2NF）

前提：滿足第一正規化

每張表只描述一件事情

第三正規化（3NF）

滿足第一第二正規化

確保資料庫表中每一類都與主鍵相關不能間接相關

規範性和效能的問題

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

考慮商業化需求和目標（成本使用者體驗）資料庫的效能更重要
規範效能的問題的時候，適當考慮下規範性
故意增加一些冗餘性（多表查詢變為單表查詢）
故意增加一些計算列（從大資料量減為小資料量的查詢：索引）

10、JDBC （重點）

10.1、資料庫驅動

應用程式需要資料庫驅動和資料庫打交道

10.2、JDBC

sun公司為簡化開發人員對資料庫統一操作，提供一個規範（java運算元據庫），俗稱JDBC

規範的實現由具體的廠商去做

對於開發者來說只需要掌握JDBC即可

java.sql

javax.sql

還需匯入資料庫驅動包mysql-connector-java

10.3、第一個JDBC的程式

建立測試資料庫

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; 
SHOW DATABASE; 
USE `jdbcstudy`;
CREATE TABLE users(
    id INT PRIMARY KEY, 
    NAME VARCHAR(40), 
    PASSWORD VARCHAR(40),
    email VARCHAR(60), 
    birthday DATE 
); 

INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-09-09'), (2,'lisi','123456','ls@sina.com','1985-09-09'), (3,'wangwu','123456','ww@sina.com','1970-09-09');

package com.kuang.lesson01;

import java.sql.*;

public class jdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、載入驅動//風格化法國
        Class.forName("com.mysql.jdbc.Driver");//固定寫法，載入驅動

        //2、使用者資訊和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        //useUnicode=true&characterEncoding=utf-8&useSSL=true 不知道為啥這句引數寫上去報錯
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?";
        String username = "root";
        String password = "1234";

        //3、連結成功，資料庫物件， Connection代表資料庫
        Connection connection = DriverManager.getConnection(url, username, password);

        //4、執行sql的物件 Statement 執行sql的物件
        Statement statement = connection.createStatement();

        //5、執行sql的物件去執行sql  可能存在結果 檢視返回結果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);//返回了結果集，結果集中封裝了我們全部的查詢結果

        while(resultSet.next()){
            System.out.println("id: "+resultSet.getObject("id"));
            System.out.println("name: "+resultSet.getObject("NAME"));
            System.out.println("pwd: "+resultSet.getObject("PASSWORD"));
            System.out.println("email: "+resultSet.getObject("email"));
            System.out.println("birth: "+resultSet.getObject("birthday"));
            System.out.println("==========================================");
        }
        //6、釋放連線
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步驟總結

載入驅動
連線資料庫DriverManager
執行sql的物件Statement
獲得返回的結果集
釋放連線

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定寫法，載入驅動
Connection connection = DriverManager.getConnection(url, username, password);

//connection代表資料庫
//資料庫設定自動提交
//事務提交
//事務回滾
connectionrollback();
connection.commit();
connection.setAutoCommit();

URL

 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql-3306
//協議://主機地址：埠號/資料庫名？引數1&引數2&引數3

//oracle-- 1521
//jdba:oracle:thin:@localhost:1512:sid

Statement

String sql = "SELECT * FROM users";//編寫sql

statement.execute();//執行任何sql
statement.executeUpdate();//更新，插入，刪除都用這個，返回一個受影響的行數
statement.executeQuery()//查詢操作返回ResultSet

ResultSet查詢的結果集，封裝了所有的查詢結果

獲得指定的資料型別

resultSet.getObject()//在不知道型別的情況下使用

resultSet.getInt()
resultSet.getFloat()
resultSet.getString()
resultSet.getDate()

遍歷指標

resultSet.beforeFirst();//移動到最前面
resultSet.afterLast();//移動到最後面
resultSet.next()//移動到下一個資料
resultSet.previous()//移動到前一行
resultSet.absolute(row)//移動到指定行

釋放資源

//6、釋放連線
resultSet.close();
statement.close();
connection.close();

10.4、statement物件

CRUD操作>create

使用executeUpdate(String sql)方法完成資料新增操作

Statement st = connection.createStatement();
String sql ="insert into user(...) values(...)"
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功");
}

CRUD操作>delete

使用executeUpdate(String sql)方法完成資料刪除操作

Statement st = connection.createStatement();
String sql ="delete from user where id ="
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("刪除成功");
}

CRUD操作>update

Statement st = connection.createStatement();
String sql ="update user set name="where name="";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("刪除成功");
}

CRUD操作>-read

Statement st = connection.createStatement();
String sql ="select * from user where id =1";
ResultSet resultSet = st.executeQuery(sql);
while(rs.next){
    //根據獲取的資料型別，分別呼叫rs的相應方法對映到java物件中
}

程式碼實現

1.提取工具類

package com.kuang.lesson02.utils;

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

public class jdbcUtils {

        private static String driver=null;
        private static String url=null;
        private static String username=null;
        private static String password=null;

        static{
            try{
                InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
                Properties properties = new Properties();
                properties.load(in);

                driver = properties.getProperty("driver");
                url = properties.getProperty("url");
                username = properties.getProperty("username");
                password = properties.getProperty("password");

                //1、載入驅動
                Class.forName(driver);

            } catch (IOException | ClassNotFoundException e) {
                e.printStackTrace();
            }

        }
        //2、獲取連線
        public static Connection getConnetion() throws SQLException {
            return DriverManager.getConnection(url,username,password);
        }
        //3、釋放連線資源
        public static void release(Connection connection, Statement statement, ResultSet resultSet){
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }

1.插入

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TesInsert {
    public static void main(String[] args) {
        Connection conn =null;
        Statement st =null;
        ResultSet rs = null;
        
        try {
            conn= jdbcUtils.getConnetion();//獲取資料庫連線
            st = conn.createStatement();
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday)" +
                    "VALUES(4,'qq','123456','zs@sina.com','1990-09-09');";
            int i =st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }
    }
}

2.刪除

package com.kuang.lesson02.utils;

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.getConnetion();//獲取資料庫連線
            st = conn.createStatement();
            String sql = "DELETE FROM users WHERE id = 4;";
            int i =st.executeUpdate(sql);
            if(i>0){
                System.out.println("刪除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }
    }
}

3.修改

package com.kuang.lesson02.utils;

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.getConnetion();//獲取資料庫連線
            st = conn.createStatement();
            String sql = "UPDATE users SET `name` = 'sakjdsak' WHERE id = 1;";
            int i =st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }
    }
}

4.選擇

package com.kuang.lesson02.utils;

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.getConnetion();//獲取資料庫連線
            st = conn.createStatement();
            String sql = "SELECT * FROM users WHERE `NAME` = 'lisi' AND `password`='123456';";
            rs =st.executeQuery(sql);
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,st,rs);
        }
    }
}

SQL隱碼攻擊的問題

SQL存在漏洞，會被攻擊導致洩露 SQL會被拼接

package com.kuang.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) {
        //login（）
        login(" 'or ' 1=1","123456");
    }
    //登入業務
    public static void login(String username, String password) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();//獲取資料庫連線
            st = conn.createStatement();
            //
            String sql = "SELECT * FROM users WHERE `NAME` = '"+username+"' AND `password` = '"+password+"'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("==============================");
            }
        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtils.release(conn, st, rs);
        }
    }
}

10.5、preparedStatement

PreparedStatement可以防止SQL隱碼攻擊，效率更好

1、新增

package com.kuang.lesson03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.kuang.lesson02.utils.jdbcUtils;
import java.util.Date;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn= null;
        PreparedStatement pst =null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();

            //區別
            //使用？ 佔位符代替引數
            String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";
            //手動給引數賦值
            pst = conn.prepareStatement(sql);//預編譯sql 不執行

            pst.setInt(1,4);//id
            pst.setString(2,"qinjiang");
            pst.setString(3,"123455");
            pst.setString(4,"6987@qq.com");
            //new Date().getTime()獲取時間戳
            //java.sql.Date()

            pst.setDate(5,new java.sql.Date(new Date().getTime()));
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

2、刪除

package com.kuang.lesson03;

import com.kuang.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn= null;
        PreparedStatement pst =null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();

            //區別
            //使用？ 佔位符代替引數
            String sql = "DELETE FROM users WHERE id = ?;";
            //手動給引數賦值
            pst = conn.prepareStatement(sql);//預編譯sql 不執行

            pst.setInt(1,4);//id
            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("刪除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

3、更新

package com.kuang.lesson03;

import com.kuang.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn= null;
        PreparedStatement pst =null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();

            //區別
            //使用？ 佔位符代替引數
            String sql = "UPDATE users SET `NAME`=? WHERE id =?;";
            //手動給引數賦值
            pst = conn.prepareStatement(sql);//預編譯sql 不執行

            pst.setString(1,"sad");//id
            pst.setInt(2,1);

            int i = pst.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

4、查詢

package com.kuang.lesson03;

import com.kuang.lesson02.utils.jdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection conn= null;
        PreparedStatement pst =null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();

            //區別
            //使用？ 佔位符代替引數
            String sql = "SELECT * FROM users WHERE `NAME` =? AND `password`=?;";
            //手動給引數賦值
            pst = conn.prepareStatement(sql);//預編譯sql 不執行

            pst.setString(1,"lisi");//id
            pst.setInt(2,123456);

            rs = pst.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println("查詢成功");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

5、防止SQL隱碼攻擊

package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {

        //login(" 'or ' 1=1","123456");
        login("lisi", "123456");
    }

    //登入業務
    public static void login(String username, String password) {

        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();//獲取資料庫連線
            //prepareStatement 防止SQL隱碼攻擊的本質 把傳遞進來的引數當作字元
            //假設其中存在轉義字元 比如' 會被直接轉義
            String sql = "SELECT * FROM users WHERE `NAME` =? AND `password`=?;";
            pst = conn.prepareStatement(sql);
            pst.setString(1, username);
            pst.setString(2, password);

            rs = pst.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (
                SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtils.release(conn, pst, rs);
        }
    }
}

10.8、事務

要麼都成功，要麼都失敗

ACID原則

原子性：要麼都完成要麼都不完成

一致性: 總數不變

隔離性：多個程式互不干擾

永續性：一旦提交不可逆

隔離性的問題：

髒讀:一個事務讀取了另一個沒有提交的事務

不可重複讀：在同一個事務內，重複讀取表中的資料，表資料傳送了改變

虛讀：在一個事務內，讀取到了別人插入的資料，導致前後結果不一致

1.開啟事務 conn.setAutoCommit(false);//開啟事務

2.一組業執行完畢，提交事務

3.可以在catch語句中顯示的定義回滾語句，預設失敗是回滾

package com.kuang.lesson03;
import com.kuang.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtils.getConnetion();

            //關閉資料庫自動提交,自動開啟事務
            conn.setAutoCommit(false);//開啟事務
            String sql1 = "update account set money=money-100 where name ='A'";
            pst = conn.prepareStatement(sql1);
            pst.executeUpdate();

            String sql2= "update account set money=money+100 where name = 'B'";
            pst=conn.prepareStatement(sql2);
            pst.executeUpdate();

            //業務完畢 提交事務
            conn.commit();
            System.out.println("成功提交");

        } catch (SQLException throwables) {
            try {
                conn.rollback();//如果失敗就回滾
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }
    }
}

10.9、資料庫連線池

資料庫連線—執行完畢–釋放

連線—釋放十分浪費資源

池化技術：準別一些預先的資源過來就連線預先準備好的

最小連線數：10

最大連線數：100 業務最高承載上限

等待超時：100ms

編寫連線池，實現介面

mysql檢視學習總結
2015-01-23
MySql
MySQL深入研究--學習總結（4）
2021-03-09
MySql
MySQL深入研究--學習總結（5）
2021-03-16
MySql
MySQL深入研究--學習總結（2）
2021-03-04
MySql
MySQL深入研究--學習總結（3）
2021-03-05
MySql
MySQL深入研究--學習總結（1）
2021-03-01
MySql
MySQL學習筆記之命令總結
2015-12-06
MySql筆記
學習總結
2020-11-30
吐血總結｜史上最全的MySQL學習資料！！
2019-03-20
MySql
MySQL 事務、日誌、鎖、索引學習總結，
2021-08-11
MySql索引
一個DBA總結的MySQL學習筆記
2022-03-09
MySql筆記
Mysql學習總結（50）——Oracle，mysql和SQL Server的區別
2017-11-26
MySqlOracleServer
mysqlimport學習總結
2020-03-30
MySqlImport
Maven學習總結
2020-08-19
Maven
MyBatis 學習總結
2020-05-20
MyBatis
awk 學習總結
2019-12-10
JNI 學習總結
2019-05-25
tkinter學習總結
2019-04-05
SVG學習總結
2019-05-04
SVG
vue學習總結
2019-04-12
Vue
WorkFlow學習總結
2019-07-05
HTML學習總結
2019-02-10
HTML
Mybatis學習總結
2019-02-13
MyBatis
Kafka 總結學習
2021-06-26
Kafka
Typescript學習總結
2021-06-26
TypeScript
【TS】學習總結
2021-08-24
lua 學習總結
2018-08-28
vue 學習總結
2018-08-13
Vue
HSF學習總結
2018-10-12
ElasticSearch 學習總結
2018-09-06
Elasticsearch
BOM學習總結
2018-12-10
JavaWeb學習總結
2018-12-20
JavaWeb
Storm學習總結
2018-07-11
ORM
redis學習總結
2021-01-14
Redis
JVM學習總結
2020-11-01
JVM
Oracle學習總結
2020-11-30
Oracle
Ajax學習總結
2020-09-30
WebRTC學習總結
2018-06-25
Web