day_27 MySQL Knowledge Points

1. Row to Column

1.1 What is row to column

The normal query result is this

  But I want to be like this

  To accomplish this through an SQL statement is called row to column

1.2 Sample data preparation

With id, name, course, result

create table test_9(

id int,

name varchar(22),

course varchar(22),

score decimal(18,2)

);

insert into test_9 (id,name,course,score)values(1,'King','java',99);

insert into test_9 (id,name,course,score)values(2,'sheet','java',89.2);

insert into test_9 (id,name,course,score)values(3,'Xiaoli','java',88);

insert into test_9 (id,name,course,score)values(4,'Wang','MySQL',92.2);

insert into test_9 (id,name,course,score)values(5,'Zhang','MySQL',42.2);

insert into test_9 (id,name,course,score)values(6,'Xiaoli','MySQL',59.2);

1.3 Scene 1 (Multiple Rows to One Row and Multiple Columns)  

  You can use the following SQL statements (group by combined with case when):

select name,max(

case course

when 'java' then score

end) Java, max(

case course

when 'MySQL' then score

end) MySQL

from test_9

group by name;

  Idea analysis:

First, by default, we output each name twice, and the end result is only one name, so we must group by name.

select * from test_9 group by name;

For us, id, course, and score are not needed, just name and then java and mysql

select name , 1 as java , 1 as MySQL from test_9  group by name;

Aggregate using aggregation functions (Aggregation, in this case, is equivalent to flattening multiple rows of data into one row)

select name,max(

case course

when 'java' then score

end) Java, max(

case course

when 'MySQL' then score

end) MySQL

from test_9

group by name;

1.4 Scenario 2 (Rows to Rows to Columns)  

  correlation function

         Concat (value,'stitcher', value): stitching, multi-line data will only stitch one line

         Group_ Concat (value,'stitcher', value): stitching, multiline flattening to one line

Idea analysis:

Step 1: Split the problem, first group by group

select name,1 as'Achievements in Subjects'from test_9 group by name;

Step 2: Split the course name and results into a single column

select name,

     concat(course,'=',score) as'achievement in each subject'

    from test_9 group by name;

  Step 3: Using group_ The concat function flattens multiple lines to one line

select name,

     group_concat(course,'=',score) as'achievement in each subject'

    from test_9 group by name;

Step 4: Modify the delimiter (comma by default)

select name,

     Group_ Concat (course,'=', score separator '|') as'Achievements in Subjects'

    from test_9 group by name;

Step 5: Sort by Course Name

select name,

     group_concat(course,'=',score order by course asc)   Separator '|') as'Achievements in Subjects'

    from test_9 group by name;

2.DQL-Join Query

2.1 Cartesian product  

         Cartesian product, also called Cartesian product

         In a multi-table query, the where restriction of a link cannot be less than -1 of the number of tables, otherwise Cartesian product will occur. This restriction is not arbitrary but is used to maintain conditions that map two tables, such as foreign keys.

         The Cartesian product is a memory-intensive operation. The Cartesian product produces a new table whose rows are the product of the rows of the original two tables and whose columns are the sum of the columns of the original two tables. So we need to use some optimization to avoid Cartesian product when joining tables.

         The simplest multitable query: select * from Table 1, Table 2;

Sample data:

create table teacher(

    id int ,

    name varchar(20),

    primary key (id)

);

create table student (

    id int ,

    name varchar(20),

    teacher_id int ,

    primary key (id),

    foreign key (teacher_id) references teacher(id)

);

Insert in   teacher (id,name) values(1,'Mr. Zhang');

Insert in   teacher (id,name) values(2,'Mr. Wang');

Insert in   student (id,name,teacher_id) values(1,'Xiaoming', 1);

Insert in   student (id,name) values(2,'little red');

Insert in   student (id,name,teacher_id) values(3,'Little Black', 2);

 

 

         Each row has two

         If you just write select * from teacher,student;, Then the number of bars of the result is equal to the product of two tables

         So the minimum criterion is one, which is the number of two tables -1

          That's right, because Xiao Hong doesn't have a teacher_id will not be queried

         Although the number of bars is correct, a complete Cartesian product will occur first, then the matched data will be found in the new view, and then the matched data will be displayed, which will consume some memory

         Link query is not recommended.

  Optimize the Cartesian product above:

         Optimize one: Use equivalence join conditions, such as where s.teahcer_above Id = t.id.

         Optimize 2: Do not use left join if you can use inner join.

         Optimize three: Use a table with fewer records as the left table.

But if there are business requirements:

         For example, we have a basic information table for users and an order table for users

        Now we want to show on the page the order records of all users

         In this case we have to use left join because inner join loses data

         Assume there are three A B C users (three records) in the basic information table

         100 order records with A and B in the order form

         In this case, in addition to using left join, we must also make the basic information table the left table and the order table the right table.

         MYSQL supports table join queries with inner join, left join, right join (right join is rarely used in our work).

2.2inner join

  Insert a sample data

INSERT INTO teacher (`id`, `name`) VALUES ('3', `Miss Sun');

1.

select *

from teacher tea

inner join student stu on tea.id = stu.teacher_id;

2.

select *

from student stu

inner join teacher tea on tea.id = stu.teacher_id;

  Summary:

         When a database returns records by joining two or more tables, it generates an intermediate temporary table, which is then returned to the user.
When using join join join join queries, the difference between on and where conditions is as follows:
         1. The on condition is used when generating temporary tables and needs to be used with linked queries.
         2. where condition is the condition that the temporary table is filtered after it is generated. At this point, there is no join meaning (you have to return the record of the table on the left), and if the condition is not true, all are filtered out.

         Link query, Cartesian product will occur, but not the complete Cartesian product, when the view is generated, will match, do not meet the criteria

         Result data is based on left table data, Mr. generate left table data, regenerate right table data

         If an inner join is used, the left table will be used as the benchmark (student). When a new view is generated, the data in the left table will be generated, and then the row will not be generated if there are qualified or not matched in the right table.

         If there is any data in the left table or no data in the right table, it will not be generated.

         If there is one in the right table, none in the left table will be generated, so if the left and right tables change positions, the number of rows will not increase.

         But the data will be lost, and the unqualified data will not be queried, so the Sun teacher just added will not query, even if the teacher table is on the left, it will not query Sun teacher, and the student Xiaohong will not be queried and processed.

         Because teacher_in the student table The ID column doesn't save Sun's ID, and Xiao Hong doesn't save the teacher's ID, so don't either.

         Multi-table queries are divided into left and right tables. Generally, the left table is the main table and the left side is the main table.

         Inner join   You can also write join directly without inner

2.3left join

         left join on: left join, also known as left outer link, is short for left outer join. Using left join and using left outer join are the same thing

1 Query result shows Xiao Hong, but does not show Mr. Sun

select * from student s

    left join teacher t on s.teacher_id = t.id;

2 Query result shows Mr. Sun, but not Xiaohong

select * from teacher t

    left join student s on s.teacher_id = t.id;

  Summary:

         Based on the table on the left, the data in the table on the left is available, whereas those in the table on the right do not meet the criteria are replaced by null on the specified column

         When you generate a view, you are also generating data for the left table

2.4right join

          right join on: Right link, also known as right outer join, is short for right outer join, which is the same with right outer join

  1 Query result shows Mr. Sun, but no little red

select * from student s

    right join teacher t on s.teacher_id = t.id;

2 Query results show Xiao Hong, but not Mr. Sun.

select * from teacher t

    right join student s on s.teacher_id = t.id;

  Summary:

         Based on the right table, the data in the right table is available. If there is no qualified data in the left table, null will be used in the specified column instead.

         However, when the view is generated, the left table data will still be generated

         As you can see above, the student right join teacher shows the same content as the teacher left join student. The teacher right join student displays the same content as the student left join student.

         So right join is basically not used in our work. inner join and left join are used most often.

         PS: Foreign keys have nothing to do with table joins, so don't confuse them.

        Foreign keys are constraints on data integrity, which ensure that you cannot delete/insert/modify data at will.

         Tables are joined because the fields of one table do not meet business needs (the fields you want to look up come from two or more tables)

         One is to add or delete, the other is to check, there is no link between them.

         Use inner join instead of left join

         Use left join instead of right join

2.4 Simulate full join in Oracle  

          In the above link queries

                 inner is data where both tables meet criteria

                 left join is available for both left and right tables

                 right join is available in both right and left tables

         So can we have both tables, regardless of whether they match or not?

                 full join / full outer join, but MySQL is canceled here, like Oracle does

                 Simulate the function of full join

Get union using Union

select * from student s

    left join teacher t on s.teacher_id = t.id

union

select * from student s

    right join teacher t on s.teacher_id = t.id;

  3. Database Import and Export

3.1 Import and export using Navicat data

  3.1.1 Export

The simplest way to use Navicat is to right-click the table or database to be exported, dump the SQL file, only structure: only create table/database statements, no data; Structure and data: There are both creation statements and data.  

  A sql file is then generated.

3.1.2 Import

Right-click Database

Run SQL file

  A window will pop up

  When the selection is complete, the corresponding address will be filled in

  Click to start

  Then refresh the table/database

3.2 Import and export using CMD data

3.2.1 Export

Open the CMD console to enter the bin directory under the MySQL installation path

  Or enter cmd directly at the address, as shown below

  Or open it directly

         Mysqldump-u username-p password   Database to Export > d:\tianliang.sql (location to save exported files)

        - U username-p password database to back u p, > where to save the file, <where to import the file

         Execute the command mysqldump-uroot-proot _ 06_> D:\tianliang.sql

  Is exported

3.2.2 Import  

Or go into the bin directory

         Mysql-u username-p password   Which database to import to < d:\tianliang.sql (file path to imported)

        - u username-p password   Database to Import   sql file to import

         Execute command mysql-uroot-proot _ 06_< D:\tianliang.sql

  Import complete

3.3 Importing and exporting CMD data using Java

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;

public class MySQLUtil {
	// File name to backup
	private static String fileName = "test";
	// Where to save the backup
	private static String backupDir = "D:/";
	private static String database = "day03";
	private static String username = "root";
	private static String password = "root";

	public static void main(String[] args) {
		// backup();
		resume();
	}

	// backups
	public static void backup() {
		// command
		String command = "cmd /c mysqldump -u" + username + " -p" + password
				+ " " + database;
		// Executing commands through java to get data streams
		try {
			Process process = Runtime.getRuntime().exec(command);
			// get data
			InputStream is = process.getInputStream();
			// Encapsulated character buffer stream
			BufferedReader br = new BufferedReader(new InputStreamReader(is));
			BufferedWriter bw = new BufferedWriter(new FileWriter(backupDir
					+ "/" + fileName + ".sql"));
			String temp = null;
			while ((temp = br.readLine()) != null) {
				bw.write(temp);
				bw.newLine();
				bw.flush();
			}
			bw.close();
			br.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// recovery
	public static void resume() {

		// command
		String command = "cmd /c mysql -u" + username + " -p" + password + " "
				+ database;
		// Executing commands through java to get data streams
		try {
			Process process = Runtime.getRuntime().exec(command);
			OutputStream os = process.getOutputStream();
			// Encapsulated character buffer stream
			BufferedReader br = new BufferedReader(new FileReader(backupDir
					+ "/" + fileName + ".sql"));
			BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(os));
			String temp = null;
			while ((temp = br.readLine()) != null) {
				bw.write(temp);
				bw.newLine();
				bw.flush();
			}
			bw.close();
			br.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
}

4. Introduction to JDBC

         When we studied MYSQL before, in order to use the MYSQL service, we usually used a client to connect to the MYSQL service before entering SQL statements to perform various operations on the database. There are two types of client: command line and GUI.

         However, in more environments, it is very inefficient for us to operate the data directly. For example, in the business scenario of Double 11, tens of millions or even hundreds of millions of data are generated in the library in one second. Manual operation by people is unrealistic and can only be done by programs.

         There are many programming languages, such as Java, Python, C/C++. If you want to execute a SQL statement, you must first connect with the database. There are also many database, such as MySQL, Oracle, SQL Server.

         Connecting different databases in different programming languages is quite confusing without a uniform standard or specification. The Java language's solution to this is JDBC.

         JDBC defines a set of specification standards, which correspond to various interfaces and abstract classes (usually corresponding to the classes and interfaces under the java.sql package). The implementation is handed over to the database vendors. MySQL has its own implementation classes and is published as jar packages for use by program developers. Oracle also has its own implementation jar package.

         When we use it, we need to download the database driver for the corresponding version of the database and the program language (Java is a jar package) on the corresponding official website, depending on the database connection. (For example, if we use MySQL 5.1, we're going to MySQL's website to download the jar package for the Java language.)

         JDBC: Java DataBase Connectivity   (java database link)

                   Is an API for linking databases with java

         API: Application Programming Intergace

                    Is a function library

         So JDBC is the application interface that provides java to connect to the database, just the interface or abstract class

         JDBC is a specification provided in java, basically interface and abstract class as parent class, the specific implementation is made by database vendors, but these vendors need to implement according to my interface standards

         If we want to operate the database, we need to import the implementation classes developed by the manufacturer

          Then right-click on the item  ->   Build Path  ->   Configure Build Path... to join our CLASSPATH project.

5. Steps for using JDBC

         Step 0: Guide

         Step 1: Register the driver (do it only once)

Step 2: Connection

Step 3: Create a Statement to run SQL

Step 4: Run the statement

Step 5: Processing Run Results (ResultSet)

         Step 6: Release Resources

If it is add, delete, update operation, there can be no step 5, query will certainly have step 5

  5.0 Guide

  Create a java project

Create lib folder

Copy mysql-connector-java-5.1.38-bin.jar into lib

Right-click  ->   Build Path  -> Add to Build Path

5.1 Registration Driver

  Create a java class JDBC_01_Base_DQL

Class.forName("com.mysql.jdbc.Driver");

 

  5.2 Connection

  The first parameter is url

         jdbc:mysql://IP: Port/Database

The second parameter is the database user name

The third parameter is the database password

 

5.3 Create a Statement to run SQL  

  5.4 Run Statements

  5.5 Processing run results (ResultSet)

while (rs.next()) {
			//  Remove data during loop traversal
			System.out.print(rs.getInt("id") + "    ");
 		// If the incoming integer value is an integer value, the corresponding column will be obtained, for example, the following is to get the value of the first column, which is not recommended
			System.out.print(rs.getInt(1) + "    ");
			System.out.print(rs.getString("id") + "  ");
			// String cannot be received with int unless it is a pure number
			// System.out.print(rs.getInt("name") +"    ");
			System.out.print(rs.getString("name") + "  ");
			System.out.print(rs.getString("course") + "  ");
			System.out.print(rs.getDouble("score")+"  ");
			// 3 corresponds to the name column. If you change the table structure and put the name column in the fourth place, you won't get the name here
			// So not flexible, not recommended
			System.out.println(rs.getString(3));
		}

  5.6 Release Resources

  Open resources before closing

6. Code optimization

          In the above program, there may be problems releasing resources

         For example, if a query statement is written incorrectly and an exception is thrown, the closing statement will not execute

         So we should use try...catch...finally to optimize

         Take the exercise just now as an example, for test_ Query optimization for JDBC tables

Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null ;
		try {
			// 1 Load Driver
			Class.forName("com.mysql.jdbc.Driver");

			// 2 Create database connection object
			// Imports use java.sql's
			 conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
			
			// 3 Create Statement Transfer Object
				String sql = "select * from test_jdbc";
				 stmt = conn.createStatement();
				
				// 4 Receive database result set
				 rs = stmt.executeQuery(sql);
				while (rs.next()) {
					//  Remove data during loop traversal
					System.out.print(rs.getInt("id") + "    ");
					System.out.print(rs.getString("name") + "  ");
					System.out.println(rs.getDouble("money")+"  ");
				}
		
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

7.DML 

         Data Manipulation Language: Data manipulation language

         The keywords involved are: delete,update,insert

         Almost as with a query, change steps 4 and 5  

Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 1 Load Driver
			Class.forName("com.mysql.jdbc.Driver");
			// 2 Create database connection object
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");
			// 3 Statement Transfer Object
			stmt = conn.createStatement();
			String sql = "insert into test_jdbc (id,name,money) values (4,'Small',999.9)";
			// sql = "update test_jdbc set money=money+1000 where id=1 ";
			// sql = "delete from test_jdbc where id = 1";
			// If it is a query, it returns true, not false, which is of little value, so it is not much use. Add, delete, update can use this method
			// stmt.execute(sql);
			// The return value is int, which affects several pieces of data (changed a few / deleted a few / added a few), add, delete, update can use this method
			int count = stmt.executeUpdate(sql);
			System.out.println("Affected " + count + " Bar data");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// Close resources, turn them off top-down, turn them on first
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

8.PreparedStatement

         When adding or updating, try to use PreparedStatement instead of Statement

         Differences between Statement and PreredStatement

         Statements are used to execute static SQL statements, when executed, a prepared SQL Statement must be specified and are relatively unsafe, at risk of SQL injection

         PreparedStatement is a precompiled sql statement object. The sql statement is precompiled and saved in the object. Can dynamically contained parameters be used in encapsulated sql statements? At the time of execution, can you? Pass parameter.

         When sql is executed using the PreparedStatement object, sql is precompiled and pre-parsed by the database and then put into the buffer. Every time the same PreparedStatement object is executed, it will be parsed once, but it will not be recompiled and reused, reducing the number of compilations, improving database performance, and avoiding sql injection, which is relatively safe (put '   Single quotation marks are escaped with\ to avoid sql injection)

8.1DQL

         Use PreparedStatement to execute queries

public static void load(int id) {

		Connection conn = null;
		PreparedStatement prst = null;
		ResultSet rs = null;
		try {
			// 1 Load Driver
			Class.forName("com.mysql.jdbc.Driver");
			// 2 Create database connection object
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

			// What do we use here? Question marks instead of values can be called placeholders or wildcards
			String sql = "select * from test_jdbc where id = ?";
			// 3 Statement Transfer Object
			prst = conn.prepareStatement(sql);
			// Set first? Value of
			prst.setInt(1, id);
			rs = prst.executeQuery();
			while (rs.next()) {
				System.out.print(rs.getInt("id") + "   ");
				System.out.print(rs.getString("name") + "   ");
				System.out.println(rs.getString("money") + "   ");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// Close resources, turn them off top-down, turn them on first
				if (rs != null) {
					rs.close();
				}
				if (prst != null) {
					prst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

8.2DML

         Use PreparedStatement to perform additions and deletions, taking additions as an example  

public static void add(int id, String name, double money) {

		Connection conn = null;
		PreparedStatement prst = null;
		try {
			// 1 Load Driver
			Class.forName("com.mysql.jdbc.Driver");
			// 2 Create database connection object
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

			// What do we use here? Question marks instead of values can be called placeholders or wildcards
			String sql = "insert into test_jdbc (id,name,money) values (?,?,?)";
			// 3 Statement Transfer Object
			prst = conn.prepareStatement(sql);
			// Set first? Value of
			prst.setInt(1, id);
			prst.setString(2, name);
			prst.setDouble(3, money);
			// Return is also the number of items affected
			int count = prst.executeUpdate();
			System.out.println("Affected "+count+" Bar data");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// Close resources, turn them off top-down, turn them on first
				if (prst != null) {
					prst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}

9. Encapsulation Tool Class

         There are two main ways to improve the packaging.

         The following code is repeated in each of our methods, which is unnecessary because the different methods actually execute different SQL statements with the same logic for getting connections and releasing resources. We can absolutely extract this logic, form separate classes and methods, and then call the corresponding classes and methods in practical application.

  1 Create connections to these

 

  2 Close resources These

 

  Create links These can be optimized in this way

public static Connection getConnection() throws ClassNotFoundException,
			SQLException {
		String username = "root";
		String password = "root";
		String url = "jdbc:mysql://127.0.0.1:3306/_06_";

		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url, username,
				password);

		return connection;
	}

Turn off resources These can be optimized in this way

         Because both Connection and Statement/PreparedStatement and ResultSet implement the AutoCloseable interface

So we can write AutoCloseable directly

public static void close(AutoCloseable obj) {
		if (obj != null) {
			try {
				obj.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

10.Batch multi-statement operation

         Execute multiple pieces of data in one task

10.1 Statement Implementation

        Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			stmt.addBatch("insert into test_jdbc (id,name,money) values(21,'stmt Multiple Tests 1',99.12)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(22,'stmt Multiple Tests 2',99.22)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(23,'stmt Multiple Tests 3',99.32)");
			stmt.addBatch("insert into test_jdbc (id,name,money) values(24,'stmt Multiple Tests 4',99.42)");
			stmt.executeBatch();
			System.out.println("Successful execution");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(conn);
		}

10.2 PreparedStatement Implementation

		Connection conn = null;
		PreparedStatement prst = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";
			prst = conn.prepareStatement(sql);
			prst.setInt(1, 31);
			prst.setString(2, "prst Multiple Tests 1");
			prst.setDouble(3, 11.1);
			prst.addBatch();
			prst.setInt(1, 32);
			prst.setString(2, "prst Multiple Tests 2");
			prst.setDouble(3, 21.1);
			prst.addBatch();
			prst.setInt(1, 33);
			prst.setString(2, "prst Multiple Tests 3");
			prst.setDouble(3, 31.1);
			prst.addBatch();
			prst.executeBatch();
			System.out.println("Successful execution");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(prst);
			DBUtil.close(conn);
		}

Tags: Database MySQL

Posted on Sun, 07 Nov 2021 11:08:15 -0500 by morrisoner