MySQL process function storage engine InnoDB simple features

To create a table and insert a data statement:

Mysql> CREATE TABLE salary (userid int,salary decimal (9,2));
Query OK, 0 rows affected (0.11 sec)
Mysql> INSERT into salary values (1,1000), (2,2000), (3,3000), (4,4000), (5,5000), (1,
NULL);
Query OK, 6 rows affected (0.13 sec)
Records:6 duplicates:0 warnings:0

1.if (value,t,f) function: Above 2000 is considered ' high ' and the others are low

Mysql> Select if (salary>2000, ' High ', ' low ') from salary;
+------------------------------+
| if (salary>2000, ' High ', ' low ') |
+------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| Low |
+------------------------------+
6 rows in Set (0.00 sec)

2.ifnull (value1,value2) function: This function can be used to change the null value to 0

Mysql> Select Ifnull (salary,0) from salary;
+------------------+
| Ifnull (salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
6 rows in Set (0.00 sec)

3.case when [value1] then [result]...else [Default] End Function:

Mysql> Select Case when salary<=2000 then ' low ' else ' high ' end from salary;
+---------------------------------------------------+
| case where salary<=2000 then ' low ' else ' high ' end |
+---------------------------------------------------+
| Low |
| Low |
| High |
| High |
| High |
| High |
+---------------------------------------------------+
6 rows in Set (0.00 sec)

4.case [expr] when [value1] then [result] ... else [default] End Function:

Mysql> Select Case salary when the "low" when the then ' mid ' else ' high ' E
nd from salary;
+----------------------------------------------------------------------+
| Case salary when the "low" when the then ' mid ' else ' high ' end |
+----------------------------------------------------------------------+
| Low |
| Mid |
| High |
| High |
| High |
| High |
+----------------------------------------------------------------------+
6 rows in Set (0.02 sec)

5. Some things about the MySQL storage engine:

The storage engine is an important feature of MySQL that differs from other databases, and allows users to customize their own storage engine based on the actual need for this feature.

The MySQL engine has:

Mysql> Show Engines \g;
1. Row ***************************
Engine:myisam
Support:yes
Comment:default engine as of MySQL 3.23 with great performance
2. Row ***************************
Engine:memory
Support:yes
Comment:hash based, stored in memory, useful for temporary tables
3. Row ***************************
Engine:innodb
Support:default
Comment:supports transactions, Row-level locking, and foreign keys
4. Row ***************************
Engine:berkeleydb
Support:no
Comment:supports Transactions and Page-level locking
5. Row ***************************
Engine:blackhole
Support:no
Comment:/dev/null Storage engine (anything you write to it disappears)
6. Row ***************************
Engine:example
Support:no
Comment:example Storage Engine
7. Row ***************************
Engine:archive
Support:yes
Comment:archive Storage Engine
8. Row ***************************
Engine:csv
Support:no
Comment:csv Storage Engine
9. Row ***************************
Engine:ndbcluster
Support:no
Comment:clustered, fault-tolerant, memory-based tables
10. Row ***************************
engine:federated
Support:no
comment:federated MySQL Storage Engine
11. Row ***************************
Engine:mrg_myisam
Support:yes
Comment:collection of identical MyISAM tables
12. Row ***************************
Engine:isam
Support:no
Comment:obsolete Storage Engine
Rows in Set (0.00 sec)

Or use this cmd:

Mysql> Show variables like ' have% ';
+-----------------------+----------+
| variable_name | Value |
+-----------------------+----------+
| have_archive | YES |
| Have_bdb | NO |
| Have_blackhole_engine | NO |
| have_compress | YES |
| Have_crypt | NO |
| Have_csv | NO |
| Have_example_engine | NO |
| Have_federated_engine | NO |
| Have_geometry | YES |
| Have_innodb | YES |
| Have_isam | NO |
| Have_ndbcluster | NO |
| Have_openssl | DISABLED |
| Have_query_cache | YES |
| Have_raid | NO |
| Have_rtree_keys | YES |
| Have_symlink | YES |
+-----------------------+----------+
$ rows in Set (0.00 sec)

Disabled indicates that MySQL supports the engine, but is disabled when it is started.

When creating a table, you can use the engine keyword to specify which engine the table uses:

Mysql> CREATE table AI (i bigint () not NULL auto_increment,primary KEY (i)) ENGI
Ne=myisam DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.03 sec)

You can also modify the engine of the table:

mysql> ALTER TABLE AI Engine=innodb;
Query OK, 0 rows affected (0.13 sec)
records:0 duplicates:0 warnings:0
Mysql> Show create table Ai \g;
1. Row ***************************
Table:ai
Create table:create Table ' ai ' (
' I ' bigint (+) not NULL auto_increment,
PRIMARY KEY (' i ')
) Engine=innodb DEFAULT CHARSET=GBK
1 row in Set (0.00 sec)

The common storage engines are:

Myisam,innodb,memory,merge,ndb

Only InnoDB in the above engines support foreign keys.

The default storage engine for MySQL is MyISAM

Each myisam is stored as 3 files on disk:

. frm (save table definition)

. MYD (MYData, storing data)

. MYI (myindex, storage index)

Some features about the InnoDB:

A. Auto-grow field:

Mysql> CREATE TABLE Autoincre (i smallint NOT null auto_increment,name varchar (20
), primary key (i)) Engine=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO Autoincre values (1, ' 1 '), (2, ' 2 '), (null, ' 3 ');
Query OK, 3 rows affected (0.03 sec)
Records:3 duplicates:0 warnings:0
Mysql> select * from Autoincre;
+---+------+
| I | name |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+------+
3 Rows in Set (0.00 sec)

After inserting a record into the self-growing field, it does not affect the field by itself adding values.

For InnoDB tables, the autogrow field must be an index, and if it is a combined index, it must be the first column of the combined index.

For MyISAM tables, however, the self-growth field can be not the first column of a composite index, and can appear as a second column:

Mysql> CREATE TABLE Autoincre_demo (D1 smallint NOT null AUTO_INCREMENT,D2 Smalli
NT not null,name varchar (ten), Index (D2,D1)) Engine=myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO Autoincre_demo (D2,name) VALUES (2, ' 2 '), (3, ' 3 '), (4, ' 4 '), (2, ' 2 '),
(3, ' 3 '), (4, ' 4 ');
Query OK, 6 rows Affected (0.00 sec)
Records:6 duplicates:0 warnings:0
Mysql> select * from Autoincre_demo;
+----+----+------+
| D1 | D2 | name |
+----+----+------+
|  1 | 2 | 2 |
|  1 | 3 | 3 |
|  1 | 4 | 4 |
|  2 | 2 | 2 |
|  2 | 3 | 3 |
|  2 | 4 | 4 |
+----+----+------+
6 rows in Set (0.00 sec)

The self-growing field D1 as a combined index appears in the second column, and the record of the self-growth field is incremented by the combined index D2.

Only InnoDB in the B.mysql storage engine support FK:

To build a table statement:

Mysql> CREATE TABLE country (country_id smallint unsigned NOT NULL auto_increment
, Country varchar (not null,primary key (country_id))
-Engine=innodb default Charset=utf8;
Query OK, 0 rows affected (0.14 sec)

Mysql> CREATE TABLE City (
-city_id smallint unsigned NOT NULL auto_increment,
-country_id smallint unsigned NOT NULL,
Primary KEY (CITY_ID),
Foreign KEY (country_id) references country (country_id)
-On delete restrict on UPDATE cascade
-Engine=innodb default Charset=utf8;
Query OK, 0 rows affected (0.05 sec)

Insert Record:

Mysql> INSERT into country (country) VALUES (' China ');
Query OK, 1 row affected (0.03 sec)
Mysql> select * from country;
+------------+---------+
| country_id | Country |
+------------+---------+
| 1 | China |
+------------+---------+
1 row in Set (0.00 sec)
mysql> insert INTO city (country_id) values (1);
Query OK, 1 row affected (0.11 sec)

Mysql> SELECT * from the city;
+---------+------------+
| city_id | country_id |
+---------+------------+
|          1 | 1 |
+---------+------------+
1 row in Set (0.00 sec)

The city table relies on the country_id field of the Country table, and the delete error occurs:

Mysql> Delete from country where country_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row:a foreign key Constrai
NT fails (' tom1/city ', CONSTRAINT ' City_ibfk_1 ' FOREIGN KEY (' country_id ') REFER
Ences ' Country ' (' country_id ') on UPDATE CASCADE)

Now that the country_id field in the Country table is updated, the country_id field of the city table will also be updated synchronously, because at the end of the creation of the city table is the add: On UPDATE cascade, which is: cascade operation on Update

mysql> Update country set country_id=1000 where country_id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0
Mysql> SELECT * from the city;
+---------+------------+
| city_id | country_id |
+---------+------------+
|       1 | 1000 |
+---------+------------+
1 row in Set (0.00 sec)

MySQL process function storage engine InnoDB simple features