Tangwx

[mysql]
# Set the default character set for the MySQL client
default-character-set=utf8
[mysqld]
# Set the port to 3306
port = 3306
# Set the MySQL installation directory
basedir=C:\Program Files\mysql-5.6.39-winx64
# Set the directory for storing MySQL database data
datadir=C:\Program Files\mysql-5.6.39-winx64\data
# Allow maximum connections
max_connections=200
# The default character set used by the server is the 8-bit encoded latin1 character set
character-set-server=utf8
# The default storage engine used when creating new tables
default-storage-engine=INNODB

# Set the MySQL installation directory
basedir=D:\software\MySql\mysql-5.6.44-winx64
# Set the directory for storing MySQL database data
datadir=D:\software\MySql\mysql-5.6.44-winx64\data

C:\windows\system32>net start mysql
The requested service has already been started.

Please type NET HELPMSG 2182 for more help.

C:\windows\system32>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

C:\windows\system32>net stop mysql
MySQL service is stopping.
MySQL service has been stopped successfully.

mysql> select user();
+----------------+ 
| user()         | 
+----------------+ 
| root@localhost | 
+----------------+ 
1 row in set (0.01 sec)

create user 'name'@'192.168.43.%' identified by 'password';

C:\windows\system32>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'name'@'192.168.31.%' identified by '123'
    -> ;
Query OK, 0 rows affected (0.09 sec)

mysql> exit
Bye

mysql -uname -p123 -h192.168.31.144

mysql -u username -p password -h ip -P port
mysql -uwanlx -pwanlx -h139.196.217.70 -P8098

C:\windows\system32>mysql -uname -p123 -h192.168.31.144
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+----------------------+ 
| user()               | 
+----------------------+ 
| name@LAPTOP-URS4B7MF | 
+----------------------+ 
1 row in set (0.00 sec)

mysql>

show databases;

create database folder_name

show grant for 'username'@'192.168.31.%'

grant all on name.* to 'name'@'192.168.31.%';

flush privileges;

grant all on name.* to 'alex'@'%' identified by '123';

mysql> grand all on name.* to 'alex'@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grand all on name.* to 'alex'@'%'' at line 1

mysql -uroot -p
mysql -uname -p123 -h192.168.31.144

create database database_name;

mysql> show tables;
Empty set (0.06 sec)

mysql> show tables;
+----------------+ 
| Tables_in_demo | 
+----------------+ 
| student        | 
+----------------+ 
1 row in set (0.00 sec)

create table student(id int,name char(10));

mysql> desc student;
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| id    | int      | YES  |     | NULL    |       | 
| name  | char(10) | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
2 rows in set (0.01 sec)

mysql> insert into student values (2,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+----------+ 
| id   | name     | 
+------+----------+ 
|    1 | alex     | 
|    2 | zhangsan | 
+------+----------+ 
2 rows in set (0.01 sec)

mysql> update student set name = 'wusir' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+-------+ 
| id   | name  | 
+------+-------+ 
|    1 | alex  | 
|    2 | wusir | 
+------+-------+ 
2 rows in set (0.00 sec)

mysql> delete from student where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+-------+ 
| id   | name  | 
+------+-------+ 
|    2 | wusir | 
+------+-------+ 
1 row in set (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints | 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         | 
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         | 
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
9 rows in set (0.00 sec)

	`Table structure, table data `

mysql> show variables like '%engine%';
+-----------------------------------------+---------------+ 
| Variable_name                           | Value         | 
+-----------------------------------------+---------------+ 
| default_storage_engine                  | InnoDB        | 
| default_tmp_storage_engine              | InnoDB        | 
| disabled_storage_engines                |               | 
| internal_tmp_mem_storage_engine         | TempTable     | 
| secondary_engine_cost_threshold         | 100000.000000 | 
| show_create_table_skip_secondary_engine | OFF           | 
| use_secondary_engine                    | ON            | 
+-----------------------------------------+---------------+ 
7 rows in set, 1 warning (0.00 sec)

mysql> create table t2 (id int,name char(10)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t2\G
*************************** 1. row *************************** 
       Table: t2 
Create Table: CREATE TABLE `t2` ( 
  `id` int DEFAULT NULL, 
  `name` char(10) DEFAULT NULL 
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+ 
| Database           | 
+--------------------+ 
| demo               | 
| information_schema | 
| mysql              | 
| name               | 
| performance_schema | 
| sys                | 
+--------------------+ 
6 rows in set (0.02 sec)

mysql> create database demo_1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+ 
| Database           | 
+--------------------+ 
| demo               | 
| demo_1             | 
| information_schema | 
| mysql              | 
| name               | 
| performance_schema | 
| sys                | 
+--------------------+ 
7 rows in set (0.00 sec)

mysql> use demo_1
Database changed
mysql> select database();
+------------+ 
| database() | 
+------------+ 
| demo_1     | 
+------------+ 
1 row in set (0.00 sec)

mysql> create table t1 (id int,name char(10))
    -> ; 
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t1; 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                                                   | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+ 
| t1    | CREATE TABLE `t1` ( 
  `id` int DEFAULT NULL, 
  `name` char(10) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

mysql> desc t1; 
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| id    | int      | YES  |     | NULL    |       | 
| name  | char(10) | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
2 rows in set (0.01 sec)

mysql> describe t1; 
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| id    | int      | YES  |     | NULL    |       | 
| name  | char(10) | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
2 rows in set (0.00 sec)

mysql>

create table table_name(field1 type);

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| d     | date     | YES  |     | NULL    |       | 
| t     | time     | YES  |     | NULL    |       | 
| dt    | datetime | YES  |     | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
3 rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+ 
| d          | t        | dt                  | 
+------------+----------+---------------------+ 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | 
+------------+----------+---------------------+ 
1 row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------------+----------+---------------------+ 
| d          | t        | dt                  | 
+------------+----------+---------------------+ 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | 
| NULL       | NULL     | NULL                | 
+------------+----------+---------------------+ 
2 rows in set (0.00 sec)

date/time/datetime example

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+ 
| Field | Type      | Null | Key | Default           | Extra                       | 
+-------+-----------+------+-----+-------------------+-----------------------------+ 
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 
+-------+-----------+------+-----+-------------------+-----------------------------+ 
1 row in set (0.00 sec)

# Inserting null will automatically insert the current time
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+ 
| id1                 | 
+---------------------+ 
| 2018-09-21 14:56:50 | 
+---------------------+ 
1 row in set (0.00 sec)

# Adding a column with a default value of '0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row *************************** 
       Table: t5 
Create Table: CREATE TABLE `t5` ( 
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

ERROR: 
No query specified

# Manually modify the new column's default value to the current time
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row *************************** 
       Table: t5 
Create Table: CREATE TABLE `t5` ( 
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+---------------------+ 
| id1                 | id2                 | 
+---------------------+---------------------+ 
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 | 
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | 
+---------------------+---------------------+ 
2 rows in set (0.00 sec)

timestamp example

mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t6;
+-------+-----------+------+-----+-------------------+-----------------------------+ 
| Field | Type      | Null | Key | Default           | Extra                       | 
+-------+-----------+------+-----+-------------------+-----------------------------+ 
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 
+-------+-----------+------+-----+-------------------+-----------------------------+ 
1 row in set (0.01 sec)

mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+---------------------+ 
| t1                  | 
+---------------------+ 
| 1970-01-01 08:00:01 | 
+---------------------+ 
1 row in set (0.00 sec)
# The lower limit of timestamp time is 19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# The upper limit of timestamp time is 2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql> 

timestamp example 2

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
+------+ 
| y    | 
+------+ 
| 2018 | 
+------+ 
1 row in set (0.00 sec)

year example

mysql> create table t8 (dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values ('2018/9/26 12+20+10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values ('20180926122010');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values (20180926122010);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+---------------------+ 
| dt                  | 
+---------------------+ 
| 2018-09-26 12:20:10 | 
| 2018-09-26 12:20:10 | 
| 2018-09-26 12:20:10 | 
| 2018-09-26 12:20:10 | 
+---------------------+ 
4 rows in set (0.00 sec)

datetime example

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t9 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)

# When retrieving, the char data type will remove spaces
mysql> select * from t9;
+------+------+ 
| v    | c    | 
+------+------+ 
| ab   | ab   | 
+------+------+ 
1 row in set (0.00 sec)

# Let's take a look at the length of the calculated query results
mysql> select length(v),length(c) from t9;
+-----------+-----------+ 
| length(v) | length(c) | 
+-----------+-----------+ 
|         4 |         2 | 
+-----------+-----------+ 
1 row in set (0.00 sec)

# Adding a plus sign to the result will make it clearer
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+ 
| concat(v,'+') | concat(c,'+') | 
+---------------+---------------+ 
| ab  +         | ab+           | 
+---------------+---------------+ 
1 row in set (0.00 sec)

# When the stored length exceeds the defined length, it will be truncated
mysql> insert into t9 values ('abcd  ','abcd  ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t9;
+------+------+ 
| v    | c    | 
+------+------+ 
| ab   | ab   | 
| abcd | abcd | 
+------+------+ 
2 rows in set (0.00 sec)

char/varchar example

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)

# Select one of enum('female','male') as the value for gender, can insert normally
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)

# Cannot insert both 'male,female' at the same time, nor can it insert values not belonging to 'male,female'
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('smoking','drinking','haircut','crash'));
Query OK, 0 rows affected (0.01 sec)

# Can arbitrarily choose items from set('smoking','drinking','haircut','crash'), and has deduplication function
mysql> insert into t11 values ('yuan','haircut,drinking,haircut');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+ 
| name | hobby        | 
+------+---------------+ 
| yuan | drinking,haircut | 
+------+---------------+ 
1 row in set (0.00 sec)

# Cannot choose items not belonging to set('smoking','drinking','haircut','crash')
mysql> insert into t11 values ('alex','haircut,crash,looking at girls');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

create table t4(
    id int,
    ip char(15),
    server char(10),
    port int,
    unique(ip,port)
);

mysql> create table t4(
    ->     id int,
    ->     ip char(15),
    ->     server char(10),
    ->     port int,
    ->     unique(ip,port)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> desc t4;
+--------+----------+------+-----+---------+-------+ 
| Field  | Type     | Null | Key | Default | Extra | 
+--------+----------+------+-----+---------+-------+ 
| id     | int      | YES  |     | NULL    |       | 
| ip     | char(15) | YES  | MUL | NULL    |       | 
| server | char(10) | YES  |     | NULL    |       | 
| port   | int      | YES  |     | NULL    |       | 
+--------+----------+------+-----+---------+-------+ 
4 rows in set (0.01 sec)

mysql> show create table t4;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                                                                                                                                               | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| t4    | CREATE TABLE `t4` ( 
  `id` int DEFAULT NULL, 
  `ip` char(15) DEFAULT NULL, 
  `server` char(10) DEFAULT NULL, 
  `port` int DEFAULT NULL, 
  UNIQUE KEY `ip` (`ip`,`port`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | 
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.01 sec)

mysql> insert into t4 values(1,'192.168.12.87','mysql',3306);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values(2,'192.168.12.87','kugou',8000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values(3,'192.168.12.36','mysql',3306);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(4,'192.168.12.36','mysql',3306);
ERROR 1062 (23000): Duplicate entry '192.168.12.36-3306' for key 't4.ip'
mysql>

create table t5(
    id int unique auto_increment,
    username char(10),
    password char(18)
);

mysql> create table t5(
    ->     id int unique auto_increment,
    ->     username char(10),
    ->     password char(18)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc t5;
+----------+----------+------+-----+---------+----------------+ 
| Field    | Type     | Null | Key | Default | Extra          | 
+----------+----------+------+-----+---------+----------------+ 
| id       | int      | NO   | PRI | NULL    | auto_increment | 
| username | char(10) | YES  |     | NULL    |                | 
| password | char(18) | YES  |     | NULL    |                | 
+----------+----------+------+-----+---------+----------------+ 
3 rows in set (0.01 sec)

mysql> insert into t5(username,password) value('alex','alex3345');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5(username,password) value('alex','alex3345');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5(username,password) value('alex','alex3345');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+----+----------+----------+ 
| id | username | password | 
+----+----------+----------+ 
|  1 | alex     | alex3345 | 
|  2 | alex     | alex3345 | 
|  3 | alex     | alex3345 | 
+----+----------+----------+ 
3 rows in set (0.00 sec)

mysql> show create table t5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                                                                                                                                                 | 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| t5    | CREATE TABLE `t5` ( 
  `id` int NOT NULL AUTO_INCREMENT, 
  `username` char(10) DEFAULT NULL, 
  `password` char(18) DEFAULT NULL, 
  UNIQUE KEY `id` (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

mysql>

create table t6(
id int not null unique,
name char(12) not null unique
);

mysql> create table t6(
    -> id int not null unique,
    -> name char(12) not null unique
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc t6;
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| id    | int      | NO   | PRI | NULL    |       | 
| name  | char(12) | NO   | UNI | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
2 rows in set (0.00 sec)

create table t7(
    id int primary key,
    name char(12) not null unique
);

mysql> create table t7(
    ->     id int primary key,
    ->     name char(12) not null unique
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc t7;
+-------+----------+------+-----+---------+-------+ 
| Field | Type     | Null | Key | Default | Extra | 
+-------+----------+------+-----+---------+-------+ 
| id    | int      | NO   | PRI | NULL    |       | 
| name  | char(12) | NO   | UNI | NULL    |       | 
+-------+----------+------+-----+---------+-------+ 
2 rows in set (0.02 sec)

create table t8(
    id int,
    ip char(15),
    server char(10),
    port int,
    primary key(ip,port)
);

mysql> create table post(
    -> postid int primary key,
    -> postname char(10) not null unique,
    -> comment varchar(255),
    -> phone_num char(11)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table staff(
    -> id int primary key auto_increment,
    -> age int,
    -> gender enum('male','female'),
    -> salary float(8,2),
    -> hire_date date,
    -> post_id int,
    -> foreign key(post_id) references post(postid)
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> desc staff;
+-----------+-----------------------+------+-----+---------+----------------+ 
| Field     | Type                  | Null | Key | Default | Extra          | 
+-----------+-----------------------+------+-----+---------+----------------+ 
| id        | int                   | NO   | PRI | NULL    | auto_increment | 
| age       | int                   | YES  |     | NULL    |                | 
| gender    | enum('male','female') | YES  |     | NULL    |                | 
| salary    | float(8,2)            | YES  |     | NULL    |                | 
| hire_date | date                  | YES  |     | NULL    |                | 
| post_id   | int                   | YES  | MUL | NULL    |                | 
+-----------+-----------------------+------+-----+---------+----------------+ 
6 rows in set (0.01 sec)

mysql> desc post;
+-----------+--------------+------+-----+---------+-------+ 
| Field     | Type         | Null | Key | Default | Extra | 
+-----------+--------------+------+-----+---------+-------+ 
| postid    | int          | NO   | PRI | NULL    |       | 
| postname  | char(10)     | NO   | UNI | NULL    |       | 
| comment   | varchar(255) | YES  |     | NULL    |       | 
| phone_num | char(11)     | YES  |     | NULL    |       | 
+-----------+--------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)

mysql> show create table staff;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                  | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| staff | CREATE TABLE `staff` ( 
  `id` int NOT NULL AUTO_INCREMENT, 
  `age` int DEFAULT NULL, 
  `gender` enum('male','female') DEFAULT NULL, 
  `salary` float(8,2) DEFAULT NULL, 
  `hire_date` date DEFAULT NULL, 
  `post_id` int DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `post_id` (`post_id`), 
  CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `post` (`postid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

mysql> insert into post values(1 ,'python department','good team','01066666666');
Query OK, 1 row affected (0.01 sec)

mysql> insert into staff values(1 ,80,'male',100000,20101010101010,1);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>

mysql> create table staff2(
    -> id int primary key auto_increment,
    -> age int,
    -> gender enum('male','female'),
    -> salary float(8,2),
    -> hire_date date,
    -> post_id int,
    -> foreign key(post_id) references post(postid) on update cascade on delete cascade
    -> );

. cascade method
When updating/deleting records on the parent table, the matching records in the child table are updated/deleted synchronously.

   . set null method
When updating/deleting records on the parent table, the matching records in the child table are set to null.
Note that the foreign key column in the child table cannot be not null.

   . No action method
If there are matching records in the child table, updating/deleting operations on the corresponding candidate key in the parent table are not allowed.

   . Restrict method
Same as no action, both immediately check foreign key constraints.

   . Set default method
When changes occur in the parent table, the foreign key column in the child table is set to a default value, but Innodb cannot recognize it.

===================== Many-to-One =====================
create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);


insert into press(name) values
('Beijing Industrial Mine Publishing House'),
('People's Music Not Good Publishing House'),
('Intellectual Property Useless Publishing House')
;

insert into book(name,press_id) values
('Nine Yang Divine Skill',1),
('Nine Yin Manual',2),
('Nine Yin White Bone Claw',2),
('Dugu Nine Swords',3),
('Dragon Ten Palms',2),
('Kite Flower Manual',3)
;

sql example

create table customer(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> qq varchar(10) not null,
    -> phone char(16) not null
    -> );

create table student(
    -> id int primary key auto_increment,
    -> class_name varchar(20) not null,
    -> customer_id int unique, # This field must be unique
    -> foreign key(customer_id) references customer(id) # The foreign key field must ensure uniqueness
    -> on delete cascade
    -> on update cascade
    -> );

# Add customers
mysql> insert into customer(name,qq,phone) values
    -> ('Han Lei','31811231',13811341220),
    -> ('Yang Lan','123123123',15213146809),
    -> ('Weng Huitian','283818181',1867141331),
    -> ('Yang Zonghe','283818181',1851143312),
    -> ('Yuan Chengming','888818181',1861243314),
    -> ('Yuan Qing','112312312',18811431230)

mysql> # Add students
mysql> insert into student(class_name,customer_id) values
    -> ('Full-time Class 1',3),
    -> ('Weekend Class 1',4),
    -> ('Weekend Class 1',5)
    -> ;
sql example

===================== Many-to-Many =====================
create table author(
id int primary key auto_increment,
name varchar(20)
);


# This table stores the relationship between the author table and the book table, i.e., querying the relationship between the two can be done through this table
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);


# Insert four authors, with ids arranged sequentially
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

# Each author has their representative works as follows
egon: 
Nine Yang Divine Skill
Nine Yin Manual
Nine Yin White Bone Claw
Dugu Nine Swords
Dragon Ten Palms
Kite Flower Manual
alex: 
Nine Yang Divine Skill
Kite Flower Manual
yuanhao:
Dugu Nine Swords
Dragon Ten Palms
Kite Flower Manual
wpq:
Nine Yang Divine Skill


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
sql example

C:\Users\HP>mysql -uwanlx -pwanlx -h139.196.217.70 -P8098
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.5.5-10.6.4-MariaDB MariaDB Server

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| iot                | 
| mysql              | 
| performance_schema | 
| sys                | 
| test               | 
+--------------------+ 
6 rows in set (0.03 sec)

mysql> select database();
+------------+ 
| database() | 
+------------+ 
| NULL       | 
+------------+ 
1 row in set (0.05 sec)

mysql> use iot;
Database changed
mysql> select database();
+------------+ 
| database() | 
+------------+ 
| iot        | 
+------------+ 
1 row in set (0.05 sec)

mysql> show tables;
+---------------+ 
| Tables_in_iot | 
+---------------+ 
| ClassInfo     | 
| StuInfo       | 
+---------------+ 
2 rows in set (0.06 sec)

mysql> desc StuInfo;
+---------+------------------+------+-----+---------+----------------+ 
| Field   | Type             | Null | Key | Default | Extra          | 
+---------+------------------+------+-----+---------+----------------+ 
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name    | varchar(80)      | NO   |     | NULL    |                | 
| class   | varchar(80)      | NO   |     | NULL    |                | 
| age     | smallint(6)      | YES  |     | NULL    |                | 
| address | varchar(256)     | YES  |     | NULL    |                | 
+---------+------------------+------+-----+---------+----------------+ 
5 rows in set (0.03 sec)

mysql> select * from StuInfo;
+----+--------+-----------------------+------+--------------------+ 
| id | name   | class                 | age  | address            | 
+----+--------+-----------------------+------+--------------------+ 
|  1 | Wan Lixia | Class 2 of 09 Automation |   31 | Fenyi County, Xinyu City, Jiangxi Province | 
|  2 | Tang Qinghua | Class 1 of 17 Automation |   20 | Ganzhou City, Jiangxi Province | 
|  3 | Zhang San | Class 1 of 19 Automation |   21 | Ganzhou City, Jiangxi Province | 
|  4 | Li Si | Class 1 of 17 Automation |   22 | Ganzhou City, Jiangxi Province | 
|  5 | Huang Shihai | Class 1 of 19 Internet of Things Engineering Undergraduate |   22 | Jiujiang City, Jiangxi Province | 
|  6 | Jia Jingqi | Class 2 of 19 Internet of Things Engineering Undergraduate |   22 | Yuncheng City, Shanxi Province | 
|  7 | Yan Gan | Class 2 of 19 Internet of Things Engineering Undergraduate |   22 | Ganzhou City, Jiangxi Province | 
|  8 | Tang Wenxi | Class 1 of 19 Automation |   22 | Ganzhou City, Jiangxi Province | 
+----+--------+-----------------------+------+--------------------+ 
8 rows in set (0.08 sec)