MySQL Database learning Note (vi)----MYSQL multi-table query outside keys, table joins, subqueries, indexes

The main contents of this chapter:

• First, foreign key
  
• Second, table connection
  
• Third, sub-query
  
• Iv. Index

first, foreign key:

• 1. What is a foreign key
• 2. Foreign KEY syntax
• 3, FOREIGN key conditions
• 4. Add foreign keys
• 5. Delete foreign keys

1, what is the foreign key:

PRIMARY KEY : Uniquely identifies a record, cannot be duplicated, is not allowed to be empty, and is used to guarantee data integrity

foreign Key : is the primary key of another table, the foreign key can have duplicate, can be null value, used to establish contact with other table. So, if you talk about a foreign key, it must be at least two tables involved. For example, the following two tables:

There are two tables: Department table (DEPT), Employee table (EMP). ID=DEPT_ID, and dept_id is the foreign key in the employee table: Because employees in the employee table need to know which department they belong to, they can find the corresponding department through the foreign key dept_id, and then can find the various field information in the Department table, so that the two are associated. So the foreign key must be created from the table to find the connection to the primary table , and the table is responsible for maintaining the relationship between the two.

We will first use the following command to create the department table and staff table, to facilitate the following examples:

create table department(
id int primary key auto_increment,
name varchar(20) not null,
description varchar(100)
);
create table employee(
id int primary key auto_increment,
name varchar(10) not null,
gender varchar(2) not null,
salary float(10,2),
age int(2),
gmr int,
dept_id int
);

Then fill in the data of the two tables and show the following results:

Department Table:

Employee table:

2, the use of foreign keys need to meet the following conditions:(here involves the concept of InnoDB)

1. Two tables must all be innodb tables, and they have no temporary tables.

Note: InnoDB is the engine of the database. There are two common engines of MySQL: InnoDB and MyISAM, which do not support foreign keys.

2. The corresponding column that establishes the foreign key relationship must have a similar INNODB internal data type.

3. The corresponding column establishing the foreign key relationship must be indexed.

4. If the constraint symbol is explicitly given, the symbol must be unique in the database. If not explicitly given, InnoDB will be created automatically.

Interview question: What storage engine does your database use? What's the difference?

Answer: Common have MyISAM and InnoDB.

MyISAM: FOREIGN KEY constraints are not supported. Transactions are not supported. When the data is imported in bulk, it will be indexed as it is inserted into the data edge, so in order to improve execution efficiency, the index should be disabled before the index is opened after a full import.

InnoDB: Supports foreign key constraints and supports transactions. The indexes are handled separately, without referencing the index.

3, add the syntax of the foreign key:

There are two ways of doing this:

• Method One: Add when creating a table
• Mode two: The table has been created, continue to modify the structure of the table to add foreign keys

"Way One" is added when creating a table

[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)
REFERENCES tbl_name (主表的字段2)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}

The above code is the same line, and the contents in brackets are optional.

The explanations are as follows:

CONSTRAINT symbol: You can give this foreign key constraint a name, with a name, it is convenient to find it later. If you do not add this parameter, the system will automatically assign a name.

FOREIGN key: The field that will be a foreign key from field 1 in the table.

REFERENCES: Map to the main table of field 2.

On delete Four arguments after: represents the convention that is made when a record of the primary table is deleted.

• RESTRICT (limit): If you want to delete the main table, it has the following on the records from the table, this main table will not be deleted.
• CASCADE (Cascade): If the record of the primary table is deleted, the records associated with the table will be deleted.
• Set NULL: Sets the foreign key to null.
• No ACTION: Do nothing.

Note: Restrict and cascade are generally used for the most.

The "Way two" table has been created and continues to modify the structure of the table to add foreign keys.

We've already built the table in the first paragraph, and the data has been filled out, and now it's time to add the foreign key from the table (employee table) to associate it with the main table (the Department table). Examples of the code are:

ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id);

Code Explanation:

ALTER Table Employee: operates from table employee;

Add FOREIGN Key (dept_id): Adds the field dept_id from the table as a foreign key;

REFERENCES Department (ID): A field that maps to the ID in the main Table department.

After running the above code, we look through navicat to see if the foreign key has been added successfully:

, select Table Employee, click the Design Table button in the Red-box section, with the following interface:

You can see our new foreign key, and the system defaults to the foreign key has a name: Employee_ibfk_1. The default rule is restrict. Next, set the value for the foreign key:

, we open the employee table, then set the value for the foreign key, 1 for the propaganda department, and 2 for the Secretary.

Then we go back to the main Table (departmental table), if you want to delete the propaganda department with ID 1, the following prompt will pop up: (because the default rule for foreign keys is restrict)

4. Delete foreign key:(by SQL statement)

We can delete the foreign keys in the navicat by means of the graphical interface, or we can delete them by SQL statements.

(1) Get the foreign key name:

If you do not know the name of the foreign key on the command line, you can find out the name of the foreign key by looking at the table's definition:

show create table emp;

The results are as follows:

In fact, we can also see in the information of the table: (Note the format of the writing command)

(2) Delete foreign key:

alter table emp drop foreign key 外键名;

Second, table connection (join)

Let's take the following two tables for example: examples of this paragraph

Department Department Table:

Employee Staff Table:

Among them, the foreign key correspondence relation is: Employee.dept_id=department.id. Employee.leader the meaning of the number is: life one of the leader is life second, life second no leader, the life of the three leader is a life.

1. Internal connection: List only matching records

Grammar:

SELECT … FROM join_table
[ON join_condition]
WHERE where_definition

Explanation: Lists only the data rows in these join tables that match the join criteria. Inner can not write, the default is inner connection. [on Join_condition] What is written is the condition of the connection.

Example:

select e.name,d.name from employee e inner join department d on e.dept_id=d.id;

Equivalent to:

select e.name,d.name from employee e,department d where e.dept_id=d.id;

Operating effect:

2. External connection:

Outer connection Category:

• Left outer join (Ieft [OUTER] join)
• Right outer join (R [OUTER] join)
• Full outer join (complete [OUTER] join) NOTE: MySQL5.1 not supported

Grammar:

SELECT … FROM join_table1
(LEFT | RIGHT | FULL) [OUTER] JOIN 
ON join_condition
WHERE where_definition

Explain:

Not only the rows that match the join condition (on) are listed, but also the left table table1 (left outer join), or right table table2 (right outer join), or two tables (full outer joins), all data rows that meet the where filter criteria. It is usually connected with left join or outside.

where [OUTER] section can not write, (left | Right | Full) section to write one of them.

2, 1 left outer joins: The left table lists all, and the right table lists only the matching records.

Example:

2, 2 right outer joins: the right table lists all, and the left table lists only the matching records.

Example:

3. Cross Connect:

Grammar:

SELECT … FROM join_table1 CROSS JOIN join_table2;

There is no on clause and a WHERE clause, which returns the Cartesian product of all data rows in the join table.

Cartesian Product Example: Suppose set a={a,b}, set b={0,1,2}, then the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}

The number of rows in the resulting collection equals the number of data rows in the first table that match the query criteria multiplied by the number of data rows in the second table that meet the query criteria.

Equivalent to: (recommended)

SELECT … FROM table1, table2;

Example:

4. Self-Connection: the tables participating in the connection are the same table. (Virtual two tables by giving the table aliases)

Note: It is very important to have a lot of javaweb in the directory tree.

Example: Querying the names of employees and their leader (similar to finding nodes and their parent nodes)

Let's explain the above code in more detail. For the same employee table, we use E1 as the employee table and E2 as the leadership table. First List all the employees (based on the Left outer connection) and then find the conditions we need: The employee's manager ID (e1.leader) equals the manager table's ID (e2.id).

Example: Find out the names of all leader.

The rationale for the analysis is ibid.

In fact, the above two query results are the following query results Yibufe:

Third, sub-query:

Role: In some cases, when making a query, the condition required is the result of another SELECT statement, which is the time to use a subquery.

Definition: The query (internal query) executed first to provide data to the main query (external query) is called a subquery. In other words, the subquery is executed first, and then the main query is executed based on the results of the subquery.

Keywords: the keywords used for subqueries mainly include in, not in, EXIST, not EXIST, =, <> etc. (the symbol "<>" means: Not equal to).

Note: MySQL does not support SQL subqueries from 4.1 onwards. It is generally said that subqueries are less efficient than connection queries (because subqueries require at least two queries, or at least two SELECT statements.) Sub-query nesting is also more, performance is lower). Table joins can be replaced with subqueries, but not necessarily.

Here's an example of the employee table below:

?

1, Example: query the highest monthly salary of the employee's name

In the above example, we first identified the highest monthly salary by the aggregation function and then identified the corresponding employee's name based on this value.

2, example: To find out the average monthly salary of each department

In the above example, the department is classified first (provided that the department cannot be empty), and then the average wage in each category is calculated separately.

Note: We do not use sub-queries here, because it is more troublesome.

3. For example: The name of the employee who has a higher monthly salary than the average salary (we know that the overall average wage is 250)

Question: What should I do if I want to inquire about employees who have a higher average salary than the department? The following is an incorrect notation:

Iv. Index

The main contents are as follows:

• 1, the concept of the index
• 2. General Index
• 3. Unique index
• 4. Primary KEY index
• 5. Full-Text Indexing
• 6. Deleting and disabling indexes
• 7, the principle of design index

About the index, recommended learning Links:

Http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html (Daniel)

http://blog.csdn.net/cuidiwhere/article/details/8452997

Http://www.cnblogs.com/cq-home/p/3482101.html

1, the concept of the index:

Indexes are the most common tool used in a database to improve query performance.

All MySQL column types can be indexed, and using indexes on related columns is the best way to improve the performance of select operations. Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees.

Indexes on related columns are used when the following operators are used:

• >, <, >=, <=, <>, in, between
• Like ' pattern ' (pattern cannot start with a wildcard, that is, the wildcard cannot be placed in front, even if placed in front, the index is not valid)

Note: The value of the index is constantly changing, so it needs to be maintained. If the amount of data is small, it is recommended not to index.

2. Normal index (first index)

• Method One: Create the index directly:

Grammar:

CREATE INDEX 索引名 ON 表名(列名[(length)]…);

Example:

We can then see the newly created index in the table: (We can modify the index type in this Navicat visual interface)

• Method Two: Add an index when modifying a table

Grammar:

ALTER TABLE 表名 ADD INDEX [索引名] (列名[(length)]…);

• Method Three: Specify the index when creating the table:

CREATE TABLE 表名 ( 表名 (
[...],

Note: If the type of column to be indexed is char, varchar type, length can be less than the actual length of the field, and length must be specified if it is a blob and text type.

3.Unique Index: (second type index)

This index is basically the same as the previous "normal index", but there is one difference: all the values of the indexed column must be unique. For example, you can index a social security number.

The creation method is similar to the normal index above. That is, change the "index" of the normal Index to "unique index".

4. Primary key index (a special unique index)

A primary key is a special unique index that is typically specified when a table is created. In MYSQL, when you create a primary key, the primary key index is also set up, without having to repeat the settings.

Remember that a table can have only one primary key, or only one primary key index.

5, Fulltext Full-text index: (third index)

MySQL supports full-text indexing and full-text retrieval starting with version 3.2. In MySQL, the index type of the full-text index is fulltext.

MySQL5.0 version only MyISAM storage engine supports Fulltext and is only created on columns of char, varchar, and text type.

Note: Full-text indexing is difficult to maintain, so you can understand it.

The creation method is similar to the normal index above. That is: Change the "index" of the normal Index to "fulltext index".

6. Delete and disable indexes:

The general use of "delete" does not use "disabled".

To delete an index:

Grammar:

DROP INDEX 索引名 ON 表名

For MyISAM tables, when you do a bulk import of data, it is indexed as you insert data edges. Therefore, in order to improve the efficiency of execution, the index should be disabled first, after the full import, and then open the index. The InnoDB table is processed independently of the index, without disabling the index.

To disable indexing:

ALTER TABLE 表名 DISABLE KEYS;

Open index:

ALTER TABLE 表名 ENABLE KEYS;

7, the principle of design index:

• The most appropriate column for the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause (ON statement), rather than the column that appears after the select.
• The greater the number of values in the indexed column, the better the index works.
• Use a short index: for char and varchar columns, creating an index with only a portion of it can save the index space and make the query faster.

such as: CREATE INDEX part_of_name on Employees (name (10)); The length specified in this sentence is 10, which is to use a short index, which means to take the first 10 characters of the name.

• Take advantage of the leftmost prefix.
• Create multiple-column indexes based on the keywords you search.
• Do not over-index. Cost to maintain indexes.

MySQL Database learning Note (vi)----MYSQL multi-table query outside keys, table joins, subqueries, indexes