Tangwx

[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8
[mysqld]
#設置3306端口
port = 3306
# 設置mysql的安裝目錄
basedir=C:\Program Files\mysql-5.6.39-winx64
# 設置mysql資料庫的資料的存放目錄
datadir=C:\Program Files\mysql-5.6.39-winx64\data
# 允許最大連接數
max_connections=200
# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB

# 設置mysql的安裝目錄
basedir=D:\software\MySql\mysql-5.6.44-winx64
# 設置mysql資料庫的資料的存放目錄
datadir=D:\software\MySql\mysql-5.6.44-winx64\data

C:\windows\system32>net start mysql
請求的服務已經啟動。

請鍵入 NET HELPMSG 2182 以獲得更多的幫助。

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 服務正在停止.
MySQL 服務已成功停止。

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

create user 'name'@'192.168.43.%' identified by '密碼';

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用戶名 -p密碼 -hip -P端口號
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 文件夾名

show grant for '用戶名'@'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 資料庫名;

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)

	`表結構、表資料 `

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 表名(字段1 類型);

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示例

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)

# 插入資料null，會自動插入當前時間的時間
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)

#添加一列 默認值是'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

# 手動修改新的列默認值為當前時間
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示例

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)
# timestamp時間的下限是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)
# timestamp時間的上限是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示例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示例

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示例

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)

# 在檢索的時候char數據類型會去掉空格
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

# 來看看對查詢結果計算的長度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

# 給結果拼上個加號會更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
1 row in set (0.00 sec)

# 當存儲的長度超出定義的長度，會截斷
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示例

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

# 選擇enum('female','male')中的一項作為gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)

# 不能同時插入'male,female'兩個值，也不能插入不屬於'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('抽煙','喝酒','燙頭','翻車'));
Query OK, 0 rows affected (0.01 sec)

# 可以任意選擇set('抽煙','喝酒','燙頭','翻車')中的項，並自帶去重功能
mysql> insert into t11 values ('yuan','燙頭,喝酒,燙頭');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| yuan | 喝酒,燙頭     |
+------+---------------+
1 row in set (0.00 sec)

# 不能選擇不屬於set('抽煙','喝酒','燙頭','翻車')中的項，
mysql> insert into t11 values ('alex','燙頭,翻車,看妹子');
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部門','不錯的團隊','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方式
在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄 

   . set null方式
在父表上update/delete記錄時，將子表上匹配記錄的列設為null
要注意子表的外鍵列不能為not null  

   . No action方式
如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作  

   . Restrict方式
同no action, 都是立即檢查外鍵約束

   . Set default方式
父表有變更時,子表將外鍵列設置成一個默認的值 但Innodb不能識別

=====================多對一=====================
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
('北京工業地雷出版社'),
('人民音樂不好聽出版社'),
('知識產權沒有用出版社')
;

insert into book(name,press_id) values
('九陽神功',1),
('九陰真經',2),
('九陰白骨爪',2),
('獨孤九劍',3),
('降龍十巴掌',2),
('葵花宝典',3)
;

sql示例

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, #該字段一定要是唯一的
    -> foreign key(customer_id) references customer(id) #外鍵的字段一定要保證unique
    -> on delete cascade
    -> );

#增加客戶
mysql> insert into customer(name,qq,phone) values
    -> ('韓蕾','31811231',13811341220),
    -> ('楊瀾','123123123',15213146809),
    -> ('翁惠天','283818181',1867141331),
    -> ('楊宗河','283818181',1851143312),
    -> ('袁承明','888818181',1861243314),
    -> ('袁清','112312312',18811431230)

mysql> #增加學生
mysql> insert into student(class_name,customer_id) values
    -> ('脫產1班',3),
    -> ('周末1期',4),
    -> ('周末1期',5)
    -> ;
sql示例

=====================多對多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);


#這張表就存放作者表與書表的關係，即查詢二者的關係查這表就可以了
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)
);


#插入四個作者，id依次排開
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每個作者與自己的代表作如下
egon: 
九陽神功
九陰真經
九陰白骨爪
獨孤九劍
降龍十巴掌
葵花宝典
alex: 
九陽神功
葵花宝典
yuanhao:
獨孤九劍
降龍十巴掌
葵花宝典
wpq:
九陽神功


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示例

C:\Users\HP>mysql -uwanlx -pwanlx -h139.196.217.70 -P8098
mysql: [Warning] Using a passwrord 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 | 萬里霞 | 09級自動化2班         |   31 | 江西省新余市分宜縣 |
|  2 | 湯清華 | 17級自動化1班         |   20 | 江西省贛州市       |
|  3 | 張三   | 19級自動化1班         |   21 | 江西省贛州市       |
|  4 | 李四   | 17級自動化1班         |   22 | 江西省贛州市       |
|  5 | 黃石海 | 19級物聯網工程本科1班 |   22 | 江西省九江市       |
|  6 | 賈景旗 | 19級物聯網工程本科2班 |   22 | 山西省運城市       |
|  7 | 嚴淦   | 19級物聯網工程本科2班 |   22 | 江西省贛州市       |
|  8 | 湯汶熙 | 19級自動化1班         |   22 | 江西省贛州市       |
+----+--------+-----------------------+------+--------------------+
8 rows in set (0.08 sec)