2018/1/3 MYSQL+JDBC

1. MySQL Foundation
1) MySQL storage structure: Data SQL statements, tables, database
2) Management database:
Add: Create database default character UTF8;
Delete: Drop database;
Modified: ALTER DATABASE default character GBK;
Query: Show databases/show CREATE database;
3) Management table:
Select database: Use database;
Added: Create TABLE Table (Field Name 1 field type, Field Name 2 field type ...);
Delete: drop table;
Modify:
Add Field: ALTER TABLE table add [column] field name type;
Delete field: ALTER TABLE table drop [column] field name;
Modify field type: ALTER TABLE table modify field name new field type;
Modify field name: ALTER TABLE table change old field name new field name fields type;
Modify table name: Alter tables table rename [to] new table name;
Inquire:
Show Tables/desc student;
4) Management data:
Add: INSERT into Table (field 1, Field 2,。。。 Values (value 1, value 2 ...). );
Delete: Delete from table where condition;
Modified: Update table SET field 1= value 1, field 2= value 2 ... where condition;
Inquire:
4.1) All fields: SELECT * from table;
4.2) Specify the field: Select Field 1, Field 2 .... from table;
4.3) Specify alias: Select Field 1 as Alias from table;
4.4) Merge columns: Select (Field 1+ field 2) from table;
4.5) de-weight: SELECT distinct field from table;
4.6) Conditional query:
A) Logical condition: and (with) or (or)
SELECT * FROM table where condition 1 and/or condition 2
b) Comparison conditions: > < >= <= = <> between and (in ... Between
SELECT * FROM table where servlet>=90;
c) conditions for the award of Air:
Judge Null:is null/is NOT NULL
To judge an empty string: = '/<> '
d) Fuzzy condition: Like
%: Replace any character
_: Replace one character
4.7 Paged Query: Limit start Row, query row count
Start line starting from 0
4.8 reviews sorted by: Order By Field Asc/desc
ASC: Positive order, sequence
Desc: reverse order, reverse
4.9 Grouping queries: Group BY fields
4.10: Group after filter: having condition

Classification of SQL statements:
DDL: Data Definition language
Create/drop/alter
DML: Data manipulation statements
Insert/delete/update/truncate
DQL: Data Query Language:
Select/show
2. mysql Enhanced
1) Data constraint (table constraint)
Default value: Default Defaults
Non-empty: NOT NULL
Unique: Unique
Primary key: Primary KEY (non-null + unique)
Self-growth: auto_increment
FOREIGN key: FOREIGN KEY constraint two kinds of table
2) Related query (multi-table query)
2.1 Cross Join (Produce Cartesian product: Reason: Insufficient connection condition) number of tables-1
2.2 Internal connection query: INNER JOIN
Only data that meets the connection criteria will be displayed!!!
2.3 Left "Outside" Connection query: [outer] Join
Left table data must be displayed in full, with left table to match the data in the right table, and if the right table has a signed condition, the data that matches the criteria is displayed, or null if the condition is not met.
2.4 Right "Outside" Connection query: [outer] Join
The data of the right table must be displayed in all, and the right table will match the data of the left table, and if the left table has a signed condition, the data will show the matching criteria, or null if the condition is not met.
2.5 Self-connect queries
3) Stored Procedures
--Create Stored procedure syntax
Delimeter End Symbol
CREATE PROCEDURE name (in/out/inout parameter name parameter type)
Begin
SQL statements with logic
End ending symbol

--Call the stored procedure
Call stored procedure name (actual parameter);

3, the API of the JDBC interface core
Java.sql.* and Javax.sql.*

|-Driver Interface: Represents the Java driver interface. All of the specific database vendors are going to implement this interface.
|-Connect (URL, properties): A way to connect to a database.
URL: The URL of the connection database
URL syntax: JDBC Protocol: Database Sub-protocol://HOST: Port/Database
User: Username for database
Password: Database user password
|-DriverManager class: Driver manager class for managing all registered drivers
|-registerdriver (Driver): Registering a Driver class object
|-connection getconnection (Url,user,password); Get Connection Object

|-Connection interface: Represents a Connection object for Java programs and databases.
|-Statement createstatement (): Create Statement Object
|-PreparedStatement preparestatement (String sql) Creating PreparedStatement objects
|-callablestatement preparecall (String sql) Creating CallableStatement objects

|-statement interface: For executing static SQL statements
|-int executeupdate (String sql): Execute a static update SQL statement (DDL,DML)
|-ResultSet executeQuery (String sql): Static query SQL statement executed (DQL)

|-preparedstatement interface: Used to execute precompiled SQL statements
|-int executeupdate (): Perform Precompiled update SQL statement (DDL,DML)
|-resultset executeQuery (): Execute a Precompiled query SQL statement (DQL)

|-callablestatement interface: SQL statement for execution of stored procedures (call XXX)
|-resultset ExecuteQuery (): Methods for calling stored procedures

|-resultset interface: Used to encapsulate the data of the query
|-Boolean next (): Move the cursor to the next line
|-GETXX (): Gets the value of the column/
4, |--dbutils shutdown resources, load driver
|--Core tool classes for Queryrunner components: Defines all the methods that are associated with database operations (query, update)
INT Update (Connection conn, String sql, Object param); Perform an update of SQL with one placeholder
INT Update (Connection conn, String sql, Object ... param); Perform an update of SQL with multiple placeholders
int[] Batch (Connection conn, String sql, object[][] params) batching
T Query Method (Connection conn, String sql, resultsethandler<t> rsh, Object ... params)

INT Update (String sql, Object param);
INT Update (String sql, Object ... param);
int[] Batch (String sql, object[][] params)
Note: If you call the action database method of the Dbutils component and do not have an incoming connection object, you need to pass in the data source object when instantiating the Queryrunner object: queryrunner qr = new Queryrunner (DS);

Dbutils Some objects that provide the result of encapsulation:
1) Beanhandler: Query returns a single object
2) Beanlisthandler: The query returns the list collection, the collection element is the specified object
3) Arrayhandler, the query returns the first line of the result record, encapsulates the array of objects, that is, return: object[]
4) Arraylisthandler, each row of the query is encapsulated as an array of objects and added to the list collection
5) Scalarhandler query returns the first column of the first row of the result record (used in aggregate function statistics)
6) The first record of the Maphandler query return result is encapsulated as map

2018/1/3 MYSQL+JDBC