20150102--mysql foreign key + DELETE and change-02

FOREIGN key

Foreign key: The outside key, a field in the preceding table has a corresponding relationship to the field (primary key) in the other table

FOREIGN KEY keyword: foreign key, also an index. Create a foreign key

The foreign key and the primary key are somewhat similar, but not the same.

1. After all the fields, specify the corresponding foreign key.

Foreign key (field name) References table name (field)

FOREIGN KEY constraints

The role of a foreign key is to maintain the relationship between two tables, which is used to restrict table operations.

Parent table (primary table): A table that is pointed to by a foreign key is called the main table

child table (from table): Table called child table with foreign key field present

1. Restricting the insertion of sub-table data: The value of the foreign key field in the child table must already exist in the parent table beforehand

2. Restrict the parent table to manipulate the data: Update and delete, the limit refers to the modification of the corresponding primary key.

Modify foreign keys: Foreign keys can not be directly modified, should be deleted after the new delete foreign key

ALTER TABLE name drop FOREIGN key foreign key name;

New FOREIGN Key

Modify Table Structure

ALTER TABLE table name add constraint foreign key name foreign key (field) References Table name (field);

Constraint mode

Users can use different constraints according to their own needs of the foreign key

There are three types of constraint modes

Strict mode: Restrict, strict constraint foreign key corresponding table

Empty mode: Set NULL, a foreign key corresponding to the field data to null, when the parent table data changes, the child table data is set to NULL

Cascading mode: CASCADE, cascade Operation, when the parent table data is changed, the child table changes

Grammar

Foreign key (field) references table (field) on delete constraint mode on upldate constraint mode

Usual constraint mode: ON UPDATE cascade on DELETE set NULL

Update parent Table

Delete parent table Data

FOREIGN key condition

1. Both the foreign key table and the referenced table (Parent-child table) should be InnoDB 's storage engine

2. Foreign key fields and referenced primary key fields must be of exactly the same data type

3. The name of the foreign key must be unique

4. Foreign keys can be created in a single table with multiple

5. If you want to use constraint mode: Empty mode, you must ensure that the foreign key field corresponding to the child table is allowed to be empty

Advanced Data Manipulation New data

Standard syntax

Insert into Table name (field list) VALUES (list of values); Worm Replication

Get the data from an existing data, and then insert the data into the corresponding data table.

Insert into Table name (field list) Select field list from table name;

The role of worm replication

1. Quickly increase the data in the table in a short time (meaning little), test the pressure of the data sheet

2. Copying data from a table

A) Duplicate table structure: Create table table name like already existing tables;

b) Worm replication data

Primary KEY violation

Primary key conflict refers to what to do if the primary key conflicts when the data is inserted.

Solution 1: If a primary key conflicts, perform an update operation without a conflict directly inserted

Insert into table name values (value list) on duplicate key Update field = value [, field = value]

Solution 2: If primary key conflicts, first delete, then new

Replace into table name values (value list);

Delete data

Standard syntax

Delete from table name where condition;

Advanced syntax

Delete from table name where condition [order by sort] [limit value]; -- Delete the specified number of bars after sorting data

Truncate syntax: Empty table includes corresponding autogrow: Delete table first, add table after

Update data

Same as advanced delete data: Take advantage of sorting and throttling

Update table name Set field = value where condition order by field limit value; Querying data

Standard syntax

Select Field List/* from table name where condition;

Full syntax

Select mode expression [Field list (alias)/*] [FROM clause] [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [LIMIT clause] Select mode

Selection mode refers to how the data is processed when the select data is found: query all (default all), Deduplication (Remove duplicates: Record duplication, distinct)

An expression

Select specifies the form of data to return

The data that is usually queried is either a field list or a * (all fields), and sometimes there is a problem with the name of the field when querying multiple tables at the same time. You need to use field aliases to deny problems.

Syntax: field [as] Alias

aliases cannot really change field names. FROM clause

The FROM clause refers to the data source

The data source must be followed from, and the data source is not necessarily a table

1. The from can be followed by multiple tables, separated by commas

The Cartesian product has no meaning: it should be avoided as much as possible.

2. Table Aliases

3. The data source is not necessarily an existing table, but the structure of a two-dimensional table is required. A subquery can be followed from the back.

4. Virtual tables

A nonexistent table: dual, any SELECT statement that does not have a data source can increase the FROM clause in order to guarantee the integrity of the syntax, and the data source is dual

WHERE clause

The WHERE clause is used to filter the condition, and the data (record) is judged on a line-by-row basis, and returns a boolean-like result.

where principle

In the where operation, the data is judged directly on disk, and all the clauses after the where are manipulating the in-memory data

Supplemental knowledge

There is no Boolean type in MySQL, but "support" Boolean type, is the internal data conversion, identify the keyword

Where to judge: The returned result is a Boolean type

Comparison operation: =,>,>=,<,<=,between left small and larger, in,is null/is not NULL

Logical operations: &&and and | | Or, for multi-condition joint judgment

Fuzzy match: Like ' patter ', match:% and _

20150102--mysql foreign key + DELETE and change-02