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);
}