Configuring database connections and database operations

Main FOREIGN Key Description:
Primary key: A unique identifier for a row in a table, with the primary key we can quickly query a piece of data!
Primary KEY attribute: Unique (unique), not NULL, can be referenced!

Association of tables:
　　1. One-to-one
CREATE TABLE ' husband ' (
' ID ' INT (one) PRIMARY KEY auto_increment,
' Hname ' VARCHAR (50)
)

DROP TABLE wife;
SELECT * from wife

CREATE TABLE ' wife ' (
' ID ' INT (one) PRIMARY KEY,
' Wname ' VARCHAR (50),
FOREIGN KEY (ID) REFERENCES Husband (ID)
)

INSERT into husband VALUES (NULL, "Wuhan University Lang");
INSERT into wife VALUES (1, "Pan Jinlian")

SELECT * from husband;
SELECT * from wife;

INSERT into wife VALUES (2, "XI");

　　2. One-to-many
Classic case: Department and employee relationships: One department has multiple employees, but an employee can only be in one department
For a one-to-many relationship table: we usually maintain a foreign key at one end, which points to a primary key

CREATE TABLE Dept (
dept_id INT PRIMARY KEY auto_increment,
Dept_name VARCHAR (50)
)

CREATE TABLE Employee (
ID INT PRIMARY KEY auto_increment,
NAME VARCHAR (50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Dept (dept_id)
)

SHOW TABLES;

INSERT into Dept VALUES (NULL, "logistic")

SELECT * FROM Dept;

INSERT into employee VALUES (NULL, "Aaron Kwok", 4)

SELECT * FROM Employee

DELETE from employee WHERE ID = 4;

　　3. Many-to-many
Classic case: Teachers and students, elective courses

　　　　Single-Table query:
SELECT SUM (score), ID
From Stu
WHERE ID > 2
GROUP by Snum
Having SUM (score) > 90
ORDER by ID ASC
LIMIT 1, 2;

　　　　Multi-Table Query

　　　　　　Vertical Connection (Union, UNION ALL)
Select name from STU1
UNION All
Select name from STU2
　　　　　　Note: Union filters the data to ensure that the data is unique, and the union all does not

　　　　　　Horizontal connection
Join on to complete the horizontal connection
INNER JOIN: Only data that satisfies the criteria will be displayed
SELECT * FROM (person P) inner join (orders O) on p.id = O.oid

1) left outward join: Left JOIN or left OUTER join
The result set of the left outer join includes all rows of the left table specified in the outer clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.

Select a.*,b.* from a LEFT join B on a.id=b.parent_id

2) Right outward join: Right join or right OUTER join
A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.

Select a.*,b.* from a right join B on a.id=b.parent_id

　　　3) Complete external connection: Full join or full OUTER join
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

Select a.*,b.* from a full join B on a.id=b.parent_id

Introduction to JDBC

The basic steps for connecting to a database through JDBC:
Importing Jar Package Driver classes
JDBC Syntax: JDBC: Sub-protocol: Vendor Content
For MySQL:? jdbc:mysql://host Address: Port number/library name
? jdbc:mysql://localhost:3306/test

　　JDBC Gets the database connection:
Prepare four parameters

Load the database driver class

Using DriverManager's Getconnection method to get a database connection

　　　　Example code:

PackageCom.neuedu.manage.util;importJava.sql.connection;importJava.sql.drivermanager;importJava.sql.resultset;importJava.sql.sqlexception;importJava.sql.statement;public classJdbcutil {private static Connection Connection=null; private static String url= "Jdbc:mysql://localhost:3306/republic"; private static string Username= "root" ; private static string Password= "123456" ; private static string driverclass= "Com.mysql.jdbc.Driver" ; public  Connection getconnection () {try  {class.forname ( Driverclass); Connection =  drivermanager.getconnection (URL, username, password),} catch  (Exception e) {//TODO Auto-genera Ted Catch block  E.printstacktrace ();} return  connection;} public static void  Close (connection Con,stat Ement Statement,resultset RSet) {if (rset!=null ) {try  {rset.close ();} catch  (SQLException e) {e.prints Tacktrace (); }} if (Statement!=null ) {try  {statement.close ();} catch  (SQLException e) {e.printstacktrace ();}} if (Con!=null ) {try  {con.close ();} catch  (SQLException e) {e.printstacktrace ()}}}    

Configuring database connections and database operations