MySQL Learning Notes 3

Label (Space Separation): Note MySQL

Multi-table join query

Internal links

Through INNER JOIN... ON keyword implementation
Specific grammar:

SELECT field1,field2,...fieldn from table_name INNER JOIN join_table ON join_condition;
#Notice here that on is followed by two column names corresponding to the foreign key constraints!

External connection

External joins are divided into two types, left and right; they are the same in usage and opposite in query logic.
Specific grammar:

SELECT field1,field2,...fieldn from table_name LEFT|RIGHT JOIN join_table ON join_condition;

The difference between left-connected query and right-connected query is that which table in the execution statement is the main table, the so-called main table is based on the main table, the data in the main table is displayed, and the data that is not in the main table is not displayed in the results even if it is in the attached table.

Understand the difference between left and right join queries inside and outside a graph

Implementing multi-table join query through internal links
Examples:

//The first method
select cu_name,c_name from commodity inner join (select o_cid,cu_name from `order` inner join customer on o_cuid=cu_id where cu_name='Lau Andy' )as t on c_id=o_cid;
//The second method
select cu_name,c_name
from commodity as c,`order` as o,customer as cu
where c.c_id=o.o_cid and o.o_cuid=cu.cu_id and cu.cu_name = 'Lau Andy';

Implementation of multi-table join query by sub-query
Examples:

//Single row and multiple columns
select * from table_a where (sal,name) = (
select sal,name from table_b where name = 'TomCat'
);
//Multiple columns and rows:
select * from table_a where sal IN (select sal from table_b;);
//The result of the query is in the result of the return field.
select * from table_a where sal NOT IN (select sal from
table_b;); //The query result is not in the return field result
select * from table_a where sal >=ANY (select sal from
table_b;); //The query results are satisfied arbitrarily within the returned field results
select * from table_a where sal >=ALL (select sal from
table_b;); //The query results are all satisfied within the returned field results.

MySQL Transaction Processing

Transactions are the execution of a set of SQL statements in the same batch
If an SQL statement fails, all SQL in the batch will be cancelled.

ACID Principle of Transaction

• Atomicity
• Consist
• Isolated
• Durable

SET AUTOCOMMIT
Use SET statements to change automatic submission mode

SET AUTOCOMMIT = 0;#Turn off automatic submission mode
SET AUTOCOMMIT = 1;#Turn on automatic submission mode

Be careful
- MySQL defaults to automatic submission
- Autocommit should be turned off first when using transactions

The Realization Method of MySQL

START TRANSACTION

• Start a transaction, marking the starting point of the transaction

COMMIT

• Submit a transaction to the database

ROLLBACK

• Roll back the transaction and return the data to the original state of the transaction

SET AUTOCOMMIT = 1

• Restore MySQL database autosubmission

Database Index

Effect:

• Improving Query Speed
• Ensure data uniqueness
• It can accelerate the connection between tables and tables, and realize the referential integrity between tables and tables.
• When using grouping and ranking clauses for data retrieval, the time of grouping and ranking can be significantly reduced.
• Search optimization for full-text search fields

classification

• Primary Key Index (PRIMARY KEY)
• Unique Index (UNIQUE)
• General Index (INDEX)
• Full Text Index (FULLTEXT)

primary key

A group of attributes uniquely identifies a record
For example: student list (student number, name, class, gender, etc.), student number is the only expression, can be used as the main key.

Characteristic:

• The most common type of index
• Ensuring the uniqueness of data records
• Determine the location of specific data records in the database

Example

CREATE TABLE `Table name`(
	`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY
	#Or PRIMARY KEY(`GradeID')
);

unique index

Function: Avoid duplication of values in a data column in the same table
Differentiation from primary key index:

• Primary key index can only have one
• A unique index can have more than one

Example

CREATE TABLE `Grade`(
	`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
	`GradeName` VARCHAR(32) NOT NULL UNIQUE
	#Or UNIQUE KEY `GradeID'(`GradeID')
);

Conventional Index

Role: Quickly locate specific data
Be careful:

• Both index and key keywords can be used to set regular indexes
• The field of search condition should be added
• It is inappropriate to add too many conventional indexes to affect the insertion, deletion and modification of data.

Example

//Add when creating tables
CREATE TABLE `result`(
	//Omit some code
	INDEX/KEY `ind` (`studentNo`,`subjectNo`)
);
//Addition after table creation
ALTER TABLE `resule` ADD INDEX `ind` (`studentNo`,`subjectNo`);

Full-text index

Role: Quickly locate specific data
Be careful:

• Data tables of MyISAM type only
• Can only be used for CHAR, VARCHAR, TEXT data types
• Suitable for large data sets

Example

//Add when creating tables
CREATE TABLE `student`(
	#Eliminate some SQL statements
	FULLTEXT(`StudentName`)
)ENGINE=MYISAM;
//Addition after table creation
ALTER TABLE employeeADD FULLTEXT (`first_name`);

Management Index

Create an index

• Add when creating tables
• After table building, add: (Syntax: ALTER TABLE table name ADD index type (data column name)

Delete index

• DROP INDEX Index Name ON Table Name
• ALTER TABLE Table Name DROP INDEX Index Name
• ALTER TABLE Table Name DROP PRIMARY KEY

View Index

• SHOW INDEX (or KEYS) FROM Table Name

Index criteria

• Indexing is not the more the better
• Don't index constantly changing data
• Tables with small data volumes are not recommended to be indexed
• Generally, the index should be in the field of search condition.