Shulou(Shulou.com)06/01 Report--

Federated query: merges the execution results of multiple query statements

UNION

SELECT clause UNION SELECT cluase

Exercise: import hellodb.sql build Database

(1) in the students table, query the names and ages of male students who are over 25 years old.

(2) the average age of each group is displayed according to ClassID.

(3) showing the grouping and average age of those with an average age of more than 30 in question 2

(4) display the information of the students whose names begin with L

(5) display the relevant information of students whose TeacherID is not empty

(6) after sorting by age, the information of the top 10 oldest students is displayed.

(7) inquire about the information of students older than or equal to 20 years old and less than or equal to 25 years old; use three methods

Exercise: import hellodb.sql, do the following on the students table

1. Group by ClassID to show the number of students in each class.

2. Grouped by Gender to show the sum of their ages.

3. Grouped by ClassID to show the classes whose average age is more than 25.

4. Group by Gender to show the sum of the ages of the students who are over 25 in each group.

Exercise: import hellodb.sql and complete the following questions:

1. Show the names, courses and grades of the first five students

2. Show the names and courses of the students whose scores are higher than 80

3. Ask for the average scores of each of the top 8 students in their own two courses and arrange them in descending order

4. Show the name of each course and the number of students who have taken the course

Think about:

1. How to display the names of students who are older than the average age?

2. How to show the names of the students whose courses are 1st, 2nd, 4th or 7th?

3. How to show the students who are older than the average age of their classmates in a class with at least 3 members?

4. Count the students in each class who are older than the average age of the whole school.

* * MySQL storage engine *

Store table types: the concept of table level does not recommend using different ENGINE (engine) on tables in the same library

Specify the engine when you create the table:

CREATE TABLE... ENGINE [=] STORAGE_ENGINE_NAME...

View table information: SHOW TABLE STATUS

Common storage engines:

MyISAM: unable to guarantee data security after an unexpected crash does not support things

Aria: data security can be ensured after an unexpected crash

InnoDB supports things

MRG_MYISAM is used to connect two MyISAM tables together on the logic layer.

CSV storing files based on text files exchanging files across databases is better but losing data accuracy

The BLACKHOLE black hole storage engine is useful for cascading replication (used later in the semi-synchronous model of the MySQL master-slave model)

MEMORY memory-based storage engine is not suitable for persistent storage but has high performance support for hash indexes, usually for temporary tables.

The mysql interface of the PERFORMANCE_SCHEMA compatible table to display statistics is not a real virtual table (similar to / proc pseudo file system)

ARCHIVE Archive Storage engine for data Warehouse

FEDERATED boast server boast physical host table federation

* * *

InnoDB: InnoBase developed it and gave it to MySQL, and then Oracle bought it.

Not the original InnoDB, but the third-party secondary development board.

Percona-XtraDB, Supports transactions, row-level locking, and foreign keys

Support things, row-level locks, foreign keys

Data is stored in "tablespace (table space)": self-organizing file system

(1) data and indexes of all InnoDB tables are stored in the same tablespace

Tablespace files: in directories defined by datadir

File: ibdata1, ibdata2,...

(2) innodb_file_per_table=ON, which means that each table uses a separate tablespace file

Data files (data and indexes, stored in the database directory): tbl_name.ibd

Definition of table structure: in the database directory, tbl_name.frm

Transactional storage engine is suitable for scenarios with high transaction requirements, but it is more suitable for handling a large number of short-term transactions.

High concurrency is supported based on MVCC (Mutli Version Concurrency Control); four isolation levels are supported, and the default level is REPEATABLE-READ; gap lock to prevent phantom reading.

Use clustered index (primary key index)

Support for "adaptive Hash index"

Lock granularity: row-level lock

Summary:

Data storage: tablespace

Concurrency: MVCC, gap lock, row-level lock

Indexes: clustered index, secondary index

Performance: read-ahead operations, in-memory data buffering, in-memory index caching, adaptive Hash indexes, insert operations cache

Backup: hot backup is supported

* * *

MyISAM:

Support for full-text indexing (FULLTEXT index), compression, spatial functions (GIS)

Transactions are not supported

Lock granularity: table-level lock

The crash cannot guarantee the safe recovery of the table.

Applicable scenarios: read-only or read-only or write-less scenarios, smaller tables (to ensure short recovery time after crash)

Files: each table has three files, which are stored in the database directory

Tbl_name.frm: table format definition

Tbl_name.MYD: data fil

Tbl_name.MYI: index fil

Properties:

Locking and concurrency: table-level locking

Repair: manual or automatic repair, but data may be lost

Indexes: nonclustered index

Defer index updates

Table compression

Line format:

{DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}

* * *

Other storage engines:

CSV: use a CSV file (a text file with comma-separated fields) as an MySQL table file

MRG_MYISAM: a virtual table that combines multiple MyISAM tables

BLACKHOLE: similar to / dev/null, does not really store data

MEMORY: memory storage engine that supports hash indexes, table-level locks, and is commonly used for temporary tables

FEDERATED: storage engine interface for accessing tables on other remote MySQL servers

MariaDB supports many additional storage engines:

OQGraph 、 SphinxSE 、 TokuDB 、 Cassandra 、 CONNECT 、 SQUENCE 、...

Search engine:

Lucene, sphinx

Lucene:Solr, Elasticsearch

* * *

Concurrency control:

Lock: Lock

Why use locks:

When there are multiple concurrent accesses in a table, there is a locking mechanism to prevent others from reading data that is being modified.

Lock type:

Read lock: a shared lock, which can be shared by multiple read operations; can lead to write starvation (uninterrupted write operations must always wait for the read operation to complete)

Write lock: exclusive lock, exclusive lock

Lock granularity:

Table locks: do not apply locks at the table level with low concurrency

Row locks: additional locks are applied at the row level with high concurrency; (rows before and after locked rows are also locked to prevent insertion)

Locking strategy: seeking a balance mechanism between lock granularity and data security

Storage engine: the level and when to impose or release locks are determined by the storage engine itself

MySQL Server: table level, optional, but also allows explicit requests

Lock category:

Explicit locks: locks manually requested by the user

Implicit locks: locks imposed by the storage engine itself as needed

Use of explicit locks:

(1) LOCK TABLES

LOCK TABLES tbl_name [AS alisa] read | write, tbl_name read | write,...

UNLOCK TABLES

(2) FLUSH TABLES writes the table in memory to disk and reopens it

FLUSH TABLES tbl_name,... [WITH READ LOCK]

UNLOCK TABLES

(3) SELECT

[FOR UPDATE | LOCK IN SHARE MODE sharing mode lock]

Example: SELECT * FROM students WHERE StuID IN is released immediately after the completion of the FOR UPDATE;.

Transaction:

Transaction: a set of atomic SQL queries, or a separate unit of work consisting of one or more SQL statements

Transaction log: open up a continuous space on the disk to write continuously in the transaction log, because the continuous write operation can not find the time performance improvement is more obvious, and finally updated to the disk.

If the log space is large, restart after an accident will write the statements in the log space to disk data one by one, which is slow.

Does not prevent the log from being full, so the transaction files are grouped.

Number of innodb_log_files_in_group transaction log groups

Innodb_log_group_home_dir transaction log directory

Innodb_log_file_size single log size

Innodb_mirrored_log_groups writes multiple write log groups (to ensure security, of course, you can't put it on a hard disk)

ACID test: whether things are satisfied or not

A:AUTOMICITY, atomicity; all operations in the entire transaction are either executed successfully or all failed and rolled back

C:CONSISTENCY, when it comes to sex; the database should always change from one consistency state to another.

I:ISOLATION, isolation; whether the actions made by one transaction can be visible to other transactions before they are committed; there are multiple levels of isolation for the purpose of ensuring concurrent operations

D:DURABILITY, persistence; once a transaction is committed, its changes are permanently saved

Auto commit: single statement transaction

Mysql > SELECT @ @ autocommit

+-- +

| | @ @ autocommit |

+-- +

| | 1 |

+-- +

Mysql > SET @ @ session.autocommit=0

Manually control transactions:

Launch: START TRANSACTION

Submit: COMMIT

Rollback: ROLLBACK

Transaction support savepoints: inserting a point in time in multiple statements of a thing can return to a point in time in a thing.

SAVEPOINT identifier insertion point in time

ROLLBACK [WORK] TO [SAVEPOINT] identifier returns to the specified insertion point

RELEASE SAVEPOINT identifier delete specified point in time

* *

Transaction isolation level:

READ-UNCOMMITTED: read unsubmitted-> dirty read; can read data that has not been submitted by others (allows you to read data that has not been submitted by others)

READ-COMMITTED: read submit-- > cannot be read repeatedly; before the data modified by the transaction is submitted. Other things are invisible.

REPEATABLE-READ: repeatable-- > Phantom reading; data has changed data, but what is actually read is old data

SERIALIZABLE: serialization

Mysql > SELECT @ @ session.tx_isolation; to view the current isolation level

+-+

| | @ @ session.tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

There will be deadlocks.

View the status information of the InnoDB storage engine:

SHOW ENGINE innodb STATUS

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.