Ayin Java Notes shrimp
The design paradigm of data table
There are three most common design paradigms in actual development ：
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 ？
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 ？
What is the relationship between primary key and foreign key in relational database ？
Oracle Port number ？MySQL Port number ？
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 .
(CASE WHEN A>B THEN A ELSE B END) as A,
(CASE WHEN B>C THEN B ELSE C END) as B
Long Type how to design the table structure ?Boolean Type how to design the table structure ?
Please introduce the principle of trigger
Are the statements in a stored procedure executed in sequence ?
How to solve SQL Injection problem
Using precompiled objects PreparedStatement
Please talk about the difference between internal connection and external connection
# 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? ？
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
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
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 .
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 .
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
What are stored procedures ？
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 ？