Ayin  Java Notes shrimp  

 picture


The design paradigm of data table


There are three most common design paradigms in actual development :


 picture


The first paradigm is the most basic paradigm . If all field values in the database table are atomic values that cannot be decomposed , It shows that the database table satisfies the first paradigm ;


The second paradigm needs to ensure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key ( Mainly for the union primary key ). That is to say, in a database table , Only one kind of data can be saved in a table , It is not allowed to save multiple data in the same database table ;


The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key , Not indirectly .


Native JDBC How to connect to the database ?


 picture


You can fill in the code corresponding to these steps in your mind , I'm used to it , It's a little forgotten


What is the mechanism of connection pooling in relational database ?


 picture


What is the relationship between primary key and foreign key in relational database ?


 picture


Oracle Port number ?MySQL Port number ?


 picture


test1 Table has ABC The three column , use SQL Statements for : When A Column greater than B Column time , choice A Column otherwise select B Column , When B Column greater than C Select when column B Column otherwise select C Column .


select     
        (CASE WHEN A>B THEN A ELSE B ENDas A,
        (CASE WHEN B>C THEN B ELSE C ENDas B
from test1


Long Type how to design the table structure ?Boolean Type how to design the table structure ?


 picture


Please introduce the principle of trigger


 picture


Are the statements in a stored procedure executed in sequence ?


 picture


How to solve SQL Injection problem

 

Using precompiled objects PreparedStatement


Please talk about the difference between internal connection and external connection


 picture


# Internal connection 
select * from student s,classes c where s.c_id = c.id
select * from student s INNER JOIN classes c on s.c_id = c.id

# External connection
# The left outer join
select * from student s LEFT JOIN classes c on s.c_id = c.id
# Right connection
select * from student s RIGHT JOIN classes c on s.c_id = c.id


Talk about the understanding of database transactions ? Where should the boundaries of a transaction be placed ? Why? ?


 picture


give an example :

 

Customer A And the customer B All the bank accounts are 10000 RMB , Customer A You need to transfer the 5000 RMB yuan is transferred to the customer B On the account of .


The process seems simple , It actually involves a series of database operations , It can be simply regarded as a two-step basic operation , From the customer A Deduct... From the amount of the account 5000 RMB , And will customers B Add... To the account 5000 RMB .


Hypothesis number 1 1 Step database operation succeeded , And if the second step fails , Will cause the entire operation to fail , And customers A The account amount will be deducted 5000 RMB .


Transaction mechanism can avoid this kind of situation , To ensure the completion of the whole operation , If something goes wrong , All previous database operations will be invalid .


Talk about the characteristics of transactions ACID


 picture


Atomicity (ATOMICITY): 


A transaction is the logical unit of work of a database , Each operation contained in the transaction is either completed , Or it's not done

 

Uniformity (CONSISTENCY): 


The result of transaction execution must be to change the database from one consistency state to another . So when the database contains only the results of a successful transaction commit , Say the database is in a consistent state .


If the database system breaks down , Some transactions are interrupted before they are completed , Some of the changes made to the database by these unfinished transactions have been written to the physical database , The database is in an incorrect state , Or inconsistent state .

 

Isolation, (ISOLATION): 


The execution of a transaction cannot be interfered by other transactions . That is, the operation and data used within a transaction are isolated from other concurrent transactions , Transactions that execute concurrently cannot interfere with each other .

 

persistence (DURABILITY): 


Once a transaction is committed , Its changes to the data in the database should be permanent . The next operation or failure should not have any effect on its execution result .


Talk about the isolation level of transactions


 picture


What are stored procedures ?


 picture


Stored procedures are stored in the database , Can be executed by the application through a single call , And allow users to declare variables 、 Conditional execution and other powerful programming functions .


Stored procedures are compiled on the server when they are created , So the execution is more than a single SQL Fast sentence


Which database connection pools have been used , Why use database connection pool ?


 picture