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: It feels above 2000 is ' 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. Users can customize their own storage engine based on the actual need to use 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 explains that MySQL supports the engine. However, the boot time is disabled.

When creating a table, you can use Enginekeyword 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 change 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-mentioned 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. Self-active growth 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 you insert a record into the self-growing field. Does not affect the field to add its own value.

For InnoDB tables. Own active growth The field must be an index, assuming that the combined index must also be the first column of the combined index.

But for the MyISAM table, the Self-growth field can be the first column of the combined 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 after 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)

The country_id field in the Country table is now updated. The country_id field of the city table is also updated synchronously. This is due to the addition of: ON UPDATE cascade at the end of the creation of the city table. That is: Cascade operations when updating

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