Take it out and encourage everyone , Please correct the shortcomings .
Study notes a long time ago , Always stored locally

Copy existing data for filling

INSERT into test_batch(name,sex) select name,sex from test_batch

Simulate inserting large amounts of data

package cn.fon.bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

// Insert a large amount of data , Insert into database 

public class Dbutil {
    

    private static String url = "jdbc:mysql://127.0.0.1:3306/leave_sys?characterEncoding=utf8&useSSL=false";
    private static String user = "root";
    private static String pwd = "123456";

    public static Connection getConn() throws Exception {
    
        Connection conn = null;
        try {
    
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, pwd);
            return conn;
        } catch (Exception e) {
    
            e.printStackTrace();
            throw e;
        }
    }

    public static void main(String[] args) throws Exception {
    
        Connection conn = getConn();
        Statement stmt = conn.createStatement();
        for (int i = 1; i <= 20; i++) {
    
            String sql = "insert into sys_student (id,stuID,classID,stuName,sex,address,stuTel,contact,contactTel)values(";
            sql +="'"+ i + "',";
            sql +="'s"+ 2018+i + "',";
            sql +="'c"+ 18+i + "',";
            sql +="'"+ " Zhou Qinglang "+ i+ "',";
            sql +="'"+ " Woman " + "',";
            sql +="'"+ " Guangzhou Changlong villa area " + "',";
            sql +="'"+ i+"222222222" + "',";
            sql +="'"+ " Liu Yifei "+i + "',";
            sql +="'"+ i + "6666'";
            sql += ")";
            stmt.executeUpdate(sql);
        }

    }
}

The code connects to the database

public class Dbmanage {
    
public Connection initDB() {
    
Connection conn = null;
try {
    
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://127.0.0.1:3306/userdb?charaterEncoding=utf-8&useSSL=false";
      conn = DriverManager.getConnection(url, "root", "123456");
} 
      catch (ClassNotFoundException e) {
    
         e.printStackTrace();
} 
      catch (SQLException e) {
    

e.printStackTrace();
    }
    return conn;
}

public void closeDB(Statement sta, Connection conn) {
    
    try {
    
        sta.close();
        conn.close();
    } catch (SQLException e) {
    
        e.printStackTrace();
    }

}
public void closeDB(ResultSet rs, Statement sta, Connection conn) {
    
    try {
    
        rs.close();
        sta.close();
        conn.close();
    } catch (SQLException e) {
    

        e.printStackTrace();
    }
}

}

Import database script

Create a library in the script and directly enter mysql Environmental Science ：source filePath;

First of all use dbname; Select the data first

No library created , Into the mysql The environment then create database databse_name; use database database_name;source filePath;

Notice the file path filePath---- Out of commission \ Lead to Failed to open file
————————

Export database script

1、 In the left column of the tool import

Referential integrity

Defining foreign codes is mainly for the system to check the referential integrity

The value of foreign code in the reference relationship must actually exist in the referenced relationship or be null

The following actions do not undermine referential integrity ：

1、 Insert .

Insert tuples into the reference relationship , To already exist in the referenced relationship

2、 Delete .

Delete a tuple in the referenced relationship , The foreign code value in the reference relationship = The primary code value of the referenced relationship , Deletion is not allowed

3、 to update .

Reference relation update , The updated foreign code value must actually exist in the referenced relationship , Only when the update is unsuccessful .

Referenced relation update , If the main code value is updated , It has no effect on the reference relationship , You can update .

Timestamp conversion

use format function

DATE_FORMAT(b.OutTime-b.InTime,"%d") %d Output by days format The function will automatically convert

Three paradigms

Three paradigms The greater the number The closer the design data sheet is to the specification

1NF

Attributes are atomic and cannot be further divided

2NF

Non primary attributes are completely functionally dependent on candidate codes —— It's all code

3NF

Non primary attributes can only be directly dependent on candidate codes —— Just code

Connect

Left connection

LEFT JOIN （ Left connection ） The left connection shows all the data in the left table , The table data on the right shows only the common part , If there is no corresponding part, you can only fill in the blank to display , The so-called left watch actually means to put it on left join The watch on the left of

A left join B

The right connection

A right join B

Full connection

OUTER JOIN（ External connection 、 Full connection ） Query all the data in the left and right tables , But remove the duplicate data of the two tables The Wayne diagram is shown as follows

A outer join B

View

advantage

1). Centralize data for users , Simplify user's data query and processing .

2). Simplified operation , Shielding the complexity of the database .

3). Re customize data , Make data easy to share .

4). Merge split data , Facilitate data output to applications .

5). Simplify the management of user rights , Increase security .

Heuristic optimization

The rules 1： Perform the selection operation as early as possible .

The rules 2： Perform projection operations as early as possible .