MYSQL+JDBC Summary

First, the database concept
The warehouse used to store the data

Hierarchical model
Mesh model
Relational model (two-dimensional table) [MySQL, SQL Server, DB2, Oracle]
Second, the design of relational model database
1, conceptual design (E-R Figure 1:1 1:n m:n)
2, logic Design
3, Physical design (Design database)

Three, MySQL installation
Character Set

Four, SQL statements
1, show all the databases
show databases;
2, create a database
Create database name;
3. Delete Database
drop database name;

4, using the database
Use database name;

5, create a table
CREATE TABLE Table (
Column name 1 data type [column-level constraint]
Column Name 2 data type [column-level constraint]
....
[table-level constraint]
)
Data type:
1, numeric type: int bigint double
2, String type: char (2) varchar (8)
3, Date type: Date datetime
4,null type
6, display table structure
DESC table name;

7. Modify the structure of the table
ALTER TABLE table name
Add column name data type;---adding a column
Change old column name new column name data type;----Modify column names
ALTER COLUMN name set default default value/drop defaults----Modify
Modify column name data type; modify data type
Rename new table name----Modify Table name

Rename table name to new table name;

8, delete table

drop table name;

9. Query all data

SELECT * from table name;

10, multi-level query

Select [Column name 1, column Name 2 ....] FROM table name 1, table Name 2 ... where ....

SELECT * FROM table name where ... GROUP by: Having ... [ORDER BY: Limit ...]

11, adding data
Insert into table (column 1, column 2 ...) values (...);
12, modify the data
Update table name set column name = value .... where ....
13. Delete Data
Delete from table where ....

14, Constraints:
1, defaults: Default value
2, unique: Unique
3, not empty: NOT NULL
4, PRIMARY key: Primary key
5, checking: Check () enum ()
6, self-increment: auto_increment[primary key, int]
7, FOREIGN Key: foreign key () references table ()

Five, JDBC
Intermediate components provided by Java for connection operations between applications and databases

Four Steps
1, load driver:
Class.forName ("Com.mysql.jdbc.Driver");
2, establish the connection
Connection con=drivermanage.getconnection ("jdbc:mysql://localhost:3306/database name", "root", "root");
3, Execute SQL statement
Statement sta=con.createstatement ();
Sta.executequery (SQL statement); Sta.executeupdate (SQL statement);

PreparedStatement pa=con.preparestatement (SQL statement);
Pa.executequery (); Pa.executeupdate ();

4, freeing up resources

Pa.close ();
Con.close ();

MYSQL+JDBC Summary