MySQL next day (MySQL key value, MySQL storage engine)

Day02
The MySQL key value (key) # #设置在字段上, which constrains how to assign a value to a field.

Normal index
Unique Indexes Unique
Primary KEY Primary Key
FOREIGN key foreign key
Full-Text Index fulltext

+++++++++++++++++++++++++++++++++++
1.1 Use of index for normal indexes
1.1.1 What is an index?
Equivalent to "book catalogue"
Advantages and disadvantages of 1.1.2 indexes
Pros: Speed up your query history
Cons: Accounting for physical storage space, slowing down write speed (insert update delete)

Page 100
1-10 catalog page (record directory information)
11-100 body

1.1.3 Index usage rules?

1.1.4 Creating an index field
1) Build table CREATE INDEX Field
CREATE TABLE Table name (
Field List,
Index (field name),
Index (field name)
);

Mysql> CREATE TABLE T10 (
, name Char (TEN) is not NULL,
-Age tinyint (2),
Sex enum ("Boy", "Girl", "no"),
-Likes set ("It", "film", "Game", "Music"),
Index (name),
-Index (Sex)
);
Query OK, 0 rows affected (0.20 sec)

mysql> desc T10; # #查看t10表结构
+-------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------------+------+-----+---------+-------+
| name | char (10) | NO | MUL | NULL | |
| Age | tinyint (2) | YES | | NULL | |
| sex | Enum (' Boy ', ' Girl ', ' no ') | YES | MUL | NULL | |
| Likes | Set (' It ', ' film ', ' Game ', ' Music ') | YES | | NULL | |
+-------+---------------------------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)

Mysql> Show index from T10\G; # #查看t10表索引
1. Row
Table:t10
Non_unique:1
Key_name:name
Seq_in_index:1
Column_name:name
Collation:a
cardinality:0
Sub_part:null
Packed:null
Null:
Index_type:btree
Comment:
Index_comment:
2. Row
Table:t10
Non_unique:1
Key_name:sex
Seq_in_index:1
Column_name:sex
Collation:a
cardinality:0
Sub_part:null
Packed:null
Null:yes
Index_type:btree
Comment:
Index_comment:
2 rows in Set (0.00 sec)

ERROR:
No query specified

2) Set the existing field in the table as an indexed field
Create index name on table name (field name); # #最好索引名 is the same as the field name

1.1.5 to see if there are indexed fields in the table
desc surface; Key----->mul
Show index from table name;

Mysql> CREATE index name on studentinfo (name);
Query OK, 0 rows affected (0.41 sec)
records:0 duplicates:0 warnings:0

Mysql> desc Studentinfo;
+-------+-----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------------+------+-----+---------+-------+
| name | char (10) | YES | MUL | NULL | |
| sex | enum (' Boy ', ' Girl ', ' secret ') | YES | | NULL | |
| age | tinyint (2) unsigned | YES | | 21 | |
| mail | char (30) | YES | | NULL | |
+-------+-----------------------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)

Mysql> Show index from STUDENTINFO\G; # #字段竖着显示
1. Row
Table:studentinfo
Non_unique:1
Key_name:name
Seq_in_index:1
Column_name:name
Collation:a
Cardinality:2
Sub_part:null
Packed:null
Null:yes
Index_type:btree
Comment:
Index_comment:
1 row in Set (0.00 sec)

ERROR:
No query specified

1.1.6 Deleting an index

Mysql> DROP index name on Studentinfo;
Query OK, 0 rows affected (0.09 sec)
records:0 duplicates:0 warnings:0

Mysql> Show index from STUDENTINFO\G;
Empty Set (0.00 sec)

ERROR:
No query specified
+++++++++++++++++++++++++++++++++++++++++++++++++++
Primary key primary key use (primary key field cannot be repeated and cannot be null) primary key flag is PRI
1.1 Usage rules?
1.1.1
1) key table when creating primary key field
The first method:
Mysql> CREATE TABLE T11 (
, name Char (TEN) primary key,
Age int (2),
Sex enum ("B", "G")
);
Query OK, 0 rows affected (0.15 sec)

mysql> desc T11;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | char (10) | NO | PRI | NULL | |
| Age | Int (2) | YES | | NULL | |
| sex | Enum (' B ', ' G ') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in Set (0.01 sec)
The second method:
Mysql> CREATE TABLE T12 (
, name char (10),
Sex enum ("B", "G"),
-primary key (name)
);
Query OK, 0 rows affected (0.33 sec)

mysql> desc T12;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | char (10) | NO | PRI | NULL | |
| sex | Enum (' B ', ' G ') | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in Set (0.00 sec)
Delete primary key

ALTER TABLE library. Tables drop primary key;

2) Create a primary key in a field already in the table
ALTER TABLE library. Form Add primary key (field);

1.1.2 Create composite key (composite key is two primary key) (when two values may be the same, you can use a composite key to prevent two values from being the same, you can have a value of the same)
Mysql> CREATE TABLE T14 (
CIP Char (15),
Port smallint (2),
Status enum ("Deny", "allow"),
Primary KEY (Cip,port)
);
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO t13 values ("1.1.1.1", +, "deny");
ERROR 1146 (42S02): Table ' db.t13 ' doesn ' t exist
mysql> INSERT INTO t14 values ("1.1.1.1", +, "deny");
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO t14 values ("1.1.1.1", "deny");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t14 values ("1.1.1.2", "deny");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t14 values ("1.1.1.2", and "allow");
ERROR 1062 (23000): Duplicate entry ' 1.1.1.2-22 ' for key ' PRIMARY '

mysql> desc t14; # #key中有PRI represents the primary key
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| CIP | char (15) | NO | PRI | NULL | |
| Port | smallint (2) | NO | PRI | NULL | |
| Status | Enum (' Deny ', ' Allow ') | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+
3 Rows in Set (0.00 sec)

Mysql> select * from T14;
+---------+------+--------+
| CIP | Port | Status |
+---------+------+--------+
| 1.1.1.1 | 21 | Deny |
| 1.1.1.1 | 22 | Deny |
| 1.1.1.2 | 22 | Deny |
+---------+------+--------+
3 Rows in Set (0.00 sec)

Remove a composite key
mysql> ALTER TABLE t14 drop PRIMARY key (port); # #不能单独删除一个键
Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' (port) ' in line 1

mysql> ALTER TABLE t14 drop PRIMARY key; # #复合键一起创建且一起删除
Query OK, 3 rows affected (0.50 sec)
Records:3 duplicates:0 warnings:0

Primary key is usually used with auto_increment (field value Auto + 1)

Mysql> CREATE TABLE t18 (
-ID int (2) Zerofill primary key auto_increment,
, name char (10),
-Age tinyint (2) unsigned,
Sex enum ("Boy", "Girl"),
-Likes set ("film", "book"),
Index (name));
Query OK, 0 rows affected (0.17 sec)

mysql> desc t18;
+-------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+----------------+
| ID | Int (2) unsigned Zerofill | NO | PRI | NULL | auto_increment |
| name | char (10) | YES | MUL | NULL | |
| Age | tinyint (2) unsigned | YES | | NULL | |
| sex | Enum (' Boy ', ' Girl ') | YES | | NULL | |
| Likes | Set (' film ', ' book ') | YES | | NULL | |
+-------+--------------------------+------+-----+---------+----------------+
5 rows in Set (0.00 sec)

mysql> INSERT INTO t18 (name,age,sex,likes) VALUES ("Tom", +, "Boy", "Film,book");
Query OK, 1 row affected (0.03 sec)

Mysql> select * from t18;
+----+------+------+------+-----------+
| ID | name | Age | sex | Likes |
+----+------+------+------+-----------+
| 01 | Tom | 21 | Boy | Film,book |
+----+------+------+------+-----------+
1 row in Set (0.00 sec)

mysql> INSERT INTO t18 (name,age,sex,likes) VALUES ("Jim", +, "Boy", "Film,book");
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t18 (name,age,sex,likes) VALUES ("Harry", +, "Boy", "Film,book");
Query OK, 1 row affected (0.05 sec)

Mysql> select * from t18;
+----+-------+------+------+-----------+
| ID | name | Age | sex | Likes |
+----+-------+------+------+-----------+
| 01 | Tom | 21 | Boy | Film,book |
| 02 | Jim | 21 | Boy | Film,book |
| 03 | Harry | 21 | Boy | Film,book |
+----+-------+------+------+-----------+
3 Rows in Set (0.00 sec)

mysql> INSERT INTO t18 values ("Harry", "Boy", "Film,book");
Query OK, 1 row affected (0.03 sec)

Mysql> select * from t18;
+----+-------+------+------+-----------+
| ID | name | Age | sex | Likes |
+----+-------+------+------+-----------+
| 01 | Tom | 21 | Boy | Film,book |
| 02 | Jim | 21 | Boy | Film,book |
| 03 | Harry | 21 | Boy | Film,book |
| 13 | Harry | 21 | Boy | Film,book |
+----+-------+------+------+-----------+
4 rows in Set (0.00 sec)

Find the records you want (index lookup)
Mysql> SELECT * from t18 where id=03;
+----+-------+------+------+-----------+
| ID | name | Age | sex | Likes |
+----+-------+------+------+-----------+
| 03 | Harry | 21 | Boy | Film,book |
+----+-------+------+------+-----------+
1 row in Set (0.00 sec)

Foreign key foreign can be used
Foreign key usage rules?

Create a foreign Key command format
CREATE TABLE Library. Table 1 (
Field List,
Foreign key (field name) References table 2 (field name)
On UPDATE cascade on DELETE CASCADE
) Engine=innodb; # #同步删除同步更新参考 Table 2

Finance 100
Name Study number pay
Bob nsd171108 2w

Reference table for CWB
Foreign key table is BJB

Mysql> CREATE Table CWB (# #创建参考表cwb
-cw_id Int (2) primary key auto_increment,
, name char (10),
Pay float (7,2)
) Engine=innodb;
Query OK, 0 rows affected (0.18 sec)

mysql> desc CWB; # #查看参考表cwb结构
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| cw_id | Int (2) | NO | PRI | NULL | auto_increment |
| name | char (10) | YES | | NULL | |
| Pay | Float (7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
3 rows in Set (0.01 sec)

mysql> INSERT INTO CWB (Name,pay) VALUES ("Bob", 20000), ("Lucy", 18000), ("Jack", 16000); # #插入参考表cwb的记录
Query OK, 3 rows affected (0.08 sec)
Records:3 duplicates:0 warnings:0

Mysql> select * from CWB;
+-------+------+----------+
| cw_id | name | Pay |
+-------+------+----------+
| 1 | Bob | 20000.00 |
| 2 | Lucy | 18000.00 |
| 3 | Jack | 16000.00 |
+-------+------+----------+
3 Rows in Set (0.00 sec)

Mysql> CREATE TABLE BJB (
-bj_id Int (2),
, name char (10),
Foreign KEY (bj_id) references CWB (cw_id)
On UPDATE cascade on DELETE CASCADE # #和cwb表同步更新和同步删除
) Engine=innodb;
Query OK, 0 rows affected (0.41 sec)

Mysql> Show CREATE TABLE BJB; # #显示创建表格bjb外键的命令

mysql> INSERT INTO BJB values (1, "Bob"), (2, "Lucy"); # #插入id records for 1 and 2
Query OK, 2 rows affected (0.03 sec)
Records:2 duplicates:0 warnings:0

mysql> INSERT INTO BJB values (7, "Harry"); # #不成功 because there is no ID 7 in the reference table
ERROR 1452 (23000): Cannot add or update a child row:a FOREIGN KEY constraint fails ( db . bjb , constraint bjb_ibfk_1 Foreig N KEY ( bj_id ) REFERENCES cwb ( cw_id ) on DELETE CASCADE on UPDATE CASCADE)
Mysql> select * from BJB;
+-------+------+
| bj_id | name |
+-------+------+
| 1 | Bob |
| 2 | Lucy |
+-------+------+
2 rows in Set (0.00 sec)

mysql> INSERT INTO CWB (Cw_id,name,pay) VALUES (7, "Harry", 21000); # #参考表中插入id为7的记录
Query OK, 1 row affected (0.02 sec)

Mysql> select * from CWB; # #查看参考表, there's a record
+-------+-------+----------+
| cw_id | name | Pay |
+-------+-------+----------+
| 1 | Bob | 20000.00 |
| 2 | Lucy | 18000.00 |
| 3 | Jack | 16000.00 |
| 7 | Harry | 21000.00 |
+-------+-------+----------+
4 rows in Set (0.00 sec)

Update CWB set cw_id=8 where cw_id=2; Modify records in a reference table

mysql> Update CWB set cw_id=8 where cw_id=2; # #把参照表记录中id为2的改为id为8
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from CWB;
+-------+-------+----------+
| cw_id | name | Pay |
+-------+-------+----------+
| 1 | Bob | 20000.00 |
| 3 | Jack | 16000.00 |
| 7 | Harry | 21000.00 |
| 8 | Lucy | 18000.00 |
+-------+-------+----------+
4 rows in Set (0.00 sec)

Delete from CWB where cw_id=3 # #删除参照表中的记录

Mysql> Delete from CWB where cw_id=3; # #删除参照表id为3的记录
Query OK, 1 row affected (0.02 sec)

Mysql> select * from CWB;
+-------+-------+----------+
| cw_id | name | Pay |
+-------+-------+----------+
| 1 | Bob | 20000.00 |
| 7 | Harry | 21000.00 |
| 8 | Lucy | 18000.00 |
+-------+-------+----------+
3 Rows in Set (0.00 sec)

Delete foreign key
ALTER TABLE BJB drop FOREIGN key foreign key name

ysql> ALTER TABLE BJB drop foreign key bjb_ibfk_1; # #bjb_ibfk_1为外键名
Query OK, 0 rows affected (0.12 sec)
records:0 duplicates:0 warnings:0

Foreign key name can show create table BJB query
Such as:
Mysql> Show CREATE TABLE BJB;
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- -----------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- -----------------------------------------------+
| BJB | CREATE TABLEbjb(
bj_idInt (2) DEFAULT NULL,
nameChar (Ten) DEFAULT NULL,
KEYbj_id(bj_id),
CONSTRAINTbjb_ibfk_1FOREIGN KEY (bj_id) REFERENCEScwb(cw_id) on the DELETE CASCADE on UPDATE CASCADE
) Engine=innodb DEFAULT charset=latin1 |
+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- -----------------------------------------------+
1 row in Set (0.00 sec)

Add a foreign key field to an existing table
ALTER TABLE 1 Add foreign key (field name) References table 2 (field name) on UPDATE cascade on DELETE cascade;

Second, MySQL storage engine
2.1 What is a storage engine
Is the processor of the table
2.2 See which storage engines are supported by the database service
Mysql>show engines;

2.3 Viewing the storage engine used by the table
Mysql>show CREATE table T1;

2.4 Modifying the storage engine used by services and tables
Mysql>alter table name engine= storage engine;
Change the default storage engine
Vim/etc/my.cnf
[Mysqld]
Default-storage-engine=myisam
#]systemctl Stop Mysqld
#]systemctl Start mysqld

2.5 What kind of storage engine is used in the production environment, what are the characteristics?

MyISAM features (comparison of the number of queries more suitable for this, support high concurrency Read)
Transaction, foreign key, thing rollback not supported
Table-level lock support (one person access to lock the table, the number of table locks, less CPU consumption)
Each table corresponds to 3 table files
Table name. frm table name. MYI the table name. MYD
Table Structure Index data

InnoDB features (write more times more suitable for this, high concurrency write)
Support things, foreign keys, things roll back
Support row-level locks (one person accesses this row to lock the row, others can access other rows, and the number of row locks is many)

Each table corresponds to 2 table files
Table name. frm table name. ibd
Table structure Index + data

Lock type
Read lock (Shared lock) Select: Supports concurrent read
Write lock (exclusive lock or mutex) Insert Update Delete: Other threads cannot read or write tables during lock

Transaction? The process of establishing a connection, manipulating data, disconnecting a connection

Transaction rollback? During a transaction execution, any one-step operation fails and all previous operations are resumed

Transaction log files:
Ib_logfile0

Ib_logfile1

Ibdata1

2.6 How to decide which storage engine the table uses
Select---->myisam
Insert Update Delete-----> InnoDB

MySQL next day (MySQL key value, MySQL storage engine)