"Javaweb Study Notes" 09_mysql Multi-Table &JDBC (contains MySQL database mind map)

Read the words this morning:

Order: Orders
Constraint: (mandatory) constraint
FOREIGN key: FOREIGN key
References: Point to
OrderItem: Order Items
Join: Join
ResourceBundle: Resource Bundle
ClassLoader: Class Loader
Properties: Attributes
InputStream: Input stream

Learning goals Today:

1, can describe the relationship between table and table

2. Can write one-to-many table relational SQL statements independently

3. Can write many-to-many table relational SQL statements independently

4, can use SQL for multi-table query

5, can use JDBC to complete the single-table additions and deletions to check operation

Review the MySQL mind map to get to today's notes

01_ one-to-many table principle

02_ Many-to-many table-building principles

03_ one-on-one table principle

Entity extraction and table relationship analysis of 04_ Mall

05_ internal and external connection differences

Use the Properties configuration file:

Method One:

Method Two:

MySQL Multi-table operation instructions:

Problem Description: First, master-slave table Data Update problem

When two tables do not have any relationship, then you can delete any of the tables in any of the records, but once the two tables established relationship (primary foreign key constraints), then you cannot delete the data in the main table (the data content in the table is related to the data), only want to perform the delete (update operation), Then there will be errors in the.

To delete data from the primary table that is associated with a table, you can do this:

1. To relieve the constraint relationship between master and slave tables
2. Delete the data from the table that is related to the primary table [ThinkPad1], and then delete the data in the primary table.

Also, you can't add data from a table that doesn't exist in the main table!

[ThinkPad1] First delete the data of p003 and P004, then delete the c002 data in the classification table.

Second, about sub-query problems

The general subquery will exist in two associated tables, then we can first find the table with the condition and the relationship field of the other table, and as another table query criteria value, and then query again.

Example: Find out more about "cosmetics" category listings.

First check the primary key of the main table (according to the specified conditions < cosmetics, Cname[thinkpad1] >)

Select CID from category where panme= ' cosmetics '

Find out more about all items (query by Category ID):

SELECT * from product where category_id=cid[thinkpad2]

SELECT * FROM Product where category_id= (select CID from category where cname= ' cosmetics ');

Three, paging query problems

A paged query uses the LIMIT keyword for queries. There are two parameters behind it. The first argument [ThinkPad3] is the starting position, and the second parameter is the number of entries per page that need to be displayed.

For example: There are 10 records in the commodity table, which now needs to be paginated, showing 3 data per page. Now you need to see the data on the second page. Then the SQL statement that should be used is:

SELECT * FROM product limit 3[THINKPAD4], 3;

[ThinkPad1] is the contents of the Main Table category table, then first identify the primary key CID. Then the contents are identified as conditions.

[ThinkPad2] needs to be queried from the main table category first.

[ThinkPad3] value is: (Need to see page 1) multiplied by the second parameter

[THINKPAD4] (2-1)

After the paging query statement is executed, the following results are displayed:

Iv. about MySQL coding problem 1 view MySQL encoding

SHOW VARIABLES like ' char% ';

Because the character set was specified as UTF8 at the time of installation, all encodings are UTF8.

L Character_set_client: The data you send must match the code specified by the client!!! The server uses this encoding to interpret the data sent by the client;

L Character_set_connection: Consistent with client through this code! This encoding does not cause garbled characters! When executing a query statement, the data sent by the client is first converted to the encoding specified by connection. However, as long as the data sent by the client is consistent with the specified code, there will be no problem with the conversion.

L Character_set_database: Database default encoding, when the database is created, if no encoding is specified, the default is to use database encoding;

L character_set_server:mysql Server default encoding;

L Character_set_results: The encoding of the response, which is the encoding returned by the query result to the client. This indicates that the client must decode using the encoding specified by result;

2 console encoding

Modify Character_set_client, Character_set_results, character_set_connection for GBK, will not appear garbled. But in fact only need to modify character_set_client and character_set_results.

There is a problem with the console encoding that can only be GBK and not modified to UTF8. The data sent by the client is GBK, and Character_set_client is UTF8, which indicates that the client data will be garbled after the server side. Since you cannot modify the console's encoding, you can only modify Character_set_client to GBK.

The data that the server sends to the client is encoded as Character_set_result, and if it is UTF8, then the console uses GBK decoding will also be garbled. Because the console encoding cannot be modified, the Character_set_result can only be modified to GBK.

L Modify character_set_client variable:set CHARACTER_SET_CLIENT=GBK;

L Modify Character_set_results variable:set CHARACTER_SET_RESULTS=GBK;

Setting the encoding is only valid for the current connection, which means that each time you log in to the MySQL prompt to modify the two encodings, but you can modify the configuration file to handle the problem: Profile path: D:\Program files\mysql\mysql Server 5.1\ My.ini

3 MySQL Tools

Using the MySQL tool is not garbled, as they will modify the encoding of Character_set_client, Character_set_results, character_set_connection each time they connect. In this way, the configuration on the My.ini is overwritten, and there will be no garbled characters.

"Javaweb Study Notes" 09_mysql Multi-Table &JDBC (contains MySQL database mind map)