Linux-关于MySQL（二）-CSDN博客

本文链接：https://blog.csdn.net/Joan_H/article/details/107639006

本文详细介绍了在Linux系统中如何修改MySQL的表结构，包括添加、修改字段，以及变更表名。此外，还深入讨论了MySQL的键值类型，如普通索引、唯一索引、主键和外键的使用，包括创建、查看和删除索引的操作，以及主键和外键的约束规则和功能验证。

摘要由CSDN通过智能技术生成

一、修改表结构

1.1 字段约束条件（限制字段赋值）
查看字段约束 desc 库名.表名；
1.1.1 是否允许字段赋空（null）值默认允许

字段名类型 not null ;


mysql> create table db1.t12( name char(10) not  null , addr  char(30) not null );
Query OK, 0 rows affected (0.05 sec)

mysql> desc db1.t12;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | NO   |     | NULL    |       |
| addr  | char(30) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.t12 values (null , null );
ERROR 1048 (23000): Column 'name' cannot be null
mysql> 
mysql> insert into db1.t12 values ("null" , null );
ERROR 1048 (23000): Column 'addr' cannot be null
mysql> 
mysql> insert into db1.t12 values ("null" , "beijing" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t12;
+------+---------+
| name | addr    |
+------+---------+
| null | beijing |
+------+---------+
1 row in set (0.00 sec)

mysql> insert into db1.t12 values ("" , "shanghai" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t12;
+------+----------+
| name | addr     |
+------+----------+
| null | beijing  |
|      | shanghai |
+------+----------+
2 rows in set (0.00 sec)

mysql>

1.1.2 设置字段默认值默认情况默认值是null
添加新行时，不给字段赋值使用默认值赋值

字段名类型 default 默认值

create table db1.t13(name char(10) not null , age int default 25 , class char(7)   default "nsd2004" , loves  set("a","b","c") not null default "c,a" );

mysql> desc db1.t13;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | char(10)         | NO   |     | NULL    |       |
| age   | int(11)          | YES  |     | 25      |       |
| class | char(7)          | YES  |     | nsd2004 |       |
| loves | set('a','b','c') | NO   |     | a,c     |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into  db1.t13(name) values("bob");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t13;
+------+------+---------+-------+
| name | age  | class   | loves |
+------+------+---------+-------+
| bob  |   25 | nsd2004 | a,c   |
+------+------+---------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> insert into db1.t13 values ("tom",null,"nsd2012",null );
ERROR 1048 (23000): Column 'loves' cannot be null
mysql> 
mysql> insert into db1.t13 values ("tom",null,"nsd2012","b" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t13;
+------+------+---------+-------+
| name | age  | class   | loves |
+------+------+---------+-------+
| bob  |   25 | nsd2004 | a,c   |
| tom  | NULL | nsd2012 | b     |
+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql>

1.1.3 键值（默认都没有设置）
1.1.4 额外设置（默认没有设置）

1.2 修改表结构
1.2.1命令格式
alter table 库.表操作动作；
操作如下：

1.2.2 添加新字段 add

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

mysql> alter table  db1.t1 add  class char(7) not null  default "nsd2004" , add email  varchar(30) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(10)    | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| class | char(7)     | NO   |     | nsd2004 |       |
| email | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table  db1.t1  add stu_num  int  first;
mysql> desc db1.t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| name    | char(10)    | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| class   | char(7)     | NO   |     | nsd2004 |       |
| email   | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table   db1.t1 add  QQ  char(11) after name ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| name    | char(10)    | YES  |     | NULL    |       |
| QQ      | char(11)    | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| class   | char(7)     | NO   |     | nsd2004 |       |
| email   | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

		1.2.3 删除表中字段 drop
mysql> alter table  db1.t1  drop  class , drop email;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select  * from db1.t1;
+---------+------+------+------+
| stu_num | name | QQ   | age  |
+---------+------+------+------+
|    NULL | abc  | NULL |   99 |
|    NULL | NULL | NULL | NULL |
|    NULL |      | NULL | NULL |
+---------+------+------+------+
3 rows in set (0.00 sec)

mysql> desc db1.t1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| QQ      | char(11) | YES  |     | NULL    |       |
| age     | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

1.2.3 修改字段类型 modify
如果字段已经有数据了，修改的设置不能与已经存储的数据冲突，冲突不允许修改。

mysql> delete from  db1.stuinfo;
mysql> select  * from stuinfo;

mysql> desc stuinfo;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| QQ      | char(11) | YES  |     | NULL    |       |
| age     | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter  table db1.stuinfo modify age int not null default 19 ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stuinfo;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| QQ      | char(11) | YES  |     | NULL    |       |
| age     | int(11)  | NO   |     | 19      |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter  table db1.stuinfo modify age tinyint ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stuinfo;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int(11)    | YES  |     | NULL    |       |
| name    | char(10)   | YES  |     | NULL    |       |
| QQ      | char(11)   | YES  |     | NULL    |       |
| age     | tinyint(4) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

mysql> insert into db1.stuinfo  values (8,"bob","240398019",58);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.stuinfo;
+---------+------+-----------+------+
| stu_num | name | QQ        | age  |
+---------+------+-----------+------+
|       8 | bob  | 240398019 |   58 |
+---------+------+-----------+------+
1 row in set (0.00 sec)

mysql> alter table  db1.stuinfo  modify age tinyint after name ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select  * from db1.stuinfo;
+---------+------+------+-----------+
| stu_num | name | age  | QQ        |
+---------+------+------+-----------+
|       8 | bob  |   58 | 240398019 |
+---------+------+------+-----------+
1 row in set (0.00 sec)

mysql>

1.2.4 修改字段名 change

mysql> desc db1.stuinfo;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int(11)    | YES  |     | NULL    |       |
| name    | char(10)   | YES  |     | NULL    |       |
| age     | tinyint(4) | YES  |     | NULL    |       |
| QQ      | char(11)   | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table db1.stuinfo change name  user varchar(10) not null default "tarena"  ;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc db1.stuinfo;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| user    | varchar(10) | NO   |     | tarena  |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| QQ      | char(11)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

mysql> alter table  db1.stuinfo change QQ  tenxun char(11) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.stuinfo;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| user    | varchar(10) | NO   |     | tarena  |       |
| age     | tinyint(4)  | YES  |     | NULL    |       |
| tenxun  | char(11)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

1.2.5 修改表名 rename

mysql> alter table  db1.t1 rename db1.stuinfo ;
Query OK, 0 rows affected (0.01 sec)

mysql> select  * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> 
mysql> select  * from db1.stuinfo;
+---------+------+------+------+
| stu_num | name | QQ   | age  |
+---------+------+------+------+
|    NULL | abc  | NULL |   99 |
|    NULL | NULL | NULL | NULL |
|    NULL |      | NULL | NULL |
+---------+------+------+------+
3 rows in set (0.00 sec)

mysql> show tables;

二、MySQL键值

2.1 键值的种类（设置在表的字段上；每一种键都各自的作用）
普通索引 index
唯一索引 unique
主键 primary key
外键 foreign key
全文索引 fulltext

2.2 普通索引Index的使用
2.2.1 什么是索引？为什么要使用索引？
2.2.2 索引的使用：
2.2.2.1
查看 desc 库.表；
查看索引的详细信息 show index
from 库.表；

2.2.2.2 创建索引
建表时创建索引 index(字段)

mysql> create table db1.t14(name char(10) , age int ,  class char(7) , index(name) , index(age));

mysql> desc db1.t14;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  | MUL | NULL    |       |
| age   | int(11)  | YES  | MUL | NULL    |       |
| class | char(7)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> show  index from db1.t14 \G
Key_name: name
Column_name: name
Index_type: BTREE
Null: YES

在已有表创建索引

mysql> create index  xxx  on   db1.t3(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db1.t3;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| name     | char(1)    | YES  | MUL | NULL    |       |
| homeaddr | varchar(3) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> mysql> show  index from db1.t3 \G

2.2.2.3 删除索引

mysql> drop index  xxx  on db1.t3;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db1.t3;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| name     | char(1)    | YES  |     | NULL    |       |
| homeaddr | varchar(3) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show  index  from db1.t3 
    -> ;
Empty set (0.00 sec)

mysql>

2.2.2.4 索引的使用规则？

2.3 主键的使用 (限制字段赋值，设置在表里的列上)
核心功能字段值不允许重复，且不允许赋NULL值
2.3.1 使用规则？
2.3.2 创建主键
建表时创建主键

mysql> create table db1.t15(name char(10), sex enum("m","w"),stu_id int , primary key(stu_id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db1.t15;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| name   | char(10)      | YES  |     | NULL    |       |
| sex    | enum('m','w') | YES  |     | NULL    |       |
| stu_id | int(11)       | NO   | PRI | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

第2种格式

mysql> create table db1.t16(name char(10), sex enum("m","w"),stu_id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> desc db1.t16;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| name   | char(10)      | YES  |     | NULL    |       |
| sex    | enum('m','w') | YES  |     | NULL    |       |
| stu_id | int(11)       | NO   | PRI | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

在已有表里创建主键


mysql> desc t3;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| name     | char(1)    | YES  | MUL | NULL    |       |
| homeaddr | varchar(3) | YES  | MUL | NULL    |       |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select  * from db1.t3;
+------+----------+
| name | homeaddr |
+------+----------+
| a    | jim      |
| a    | ddd      |
| a    | ddd      |
| a    | ddd      |
+------+----------+
4 rows in set (0.00 sec)

mysql> alter  table  db1.t3  add  primary key(name);
ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY'
mysql> 
mysql> delete from  db1.t3;
Query OK, 4 rows affected (0.00 sec)

mysql> select  * from db1.t3;
Empty set (0.00 sec)

mysql> alter  table  db1.t3  add  primary key(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc db1.t3;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| name     | char(1)    | NO   | PRI | NULL    |       |
| homeaddr | varchar(3) | YES  | MUL | NULL    |       |
+----------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> show index from  db1.t3 \G
*************************** 1. row ***************************
        Table: t3
   Non_unique: 0
     Key_name: PRIMARY
 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: t3
   Non_unique: 1
     Key_name: xxx
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t3
   Non_unique: 1
     Key_name: yyy
 Seq_in_index: 1
  Column_name: homeaddr
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> show create table db1.t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `name` char(1) NOT NULL,
  `homeaddr` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `xxx` (`name`),
  KEY `yyy` (`homeaddr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

创建复合主键（表的多列一起做主键，赋值时主键列的值不可以同时重复）

主键主键
clientip port status
1.1.1.1 22 allow
1.1.1.1 3306 deny
2.1.1.1 3306 deny

create  table db1.t17(
clientip  char(15), port  smallint , status  enum("allow","deny"),
primary key( clientip , port ));

mysql> desc  db1.t17;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   | PRI | NULL    |       |
| port     | smallint(6)          | NO   | PRI | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 
mysql> insert into  db1.t17 values ("1.1.1.1",22,"allow");
Query OK, 1 row affected (0.01 sec)

mysql> select  * from db1.t17;
+----------+------+--------+
| clientip | port | status |
+----------+------+--------+
| 1.1.1.1  |   22 | allow  |
+----------+------+--------+
1 row in set (0.00 sec)

mysql> insert into  db1.t17 values ("1.1.1.1",22,"allow");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'
mysql> 
mysql> insert into  db1.t17 values ("1.1.1.1",3306,"allow");
Query OK, 1 row affected (0.00 sec)

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
Query OK, 1 row affected (0.00 sec)

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
ERROR 1062 (23000): Duplicate entry '2.1.1.1-3306' for key 'PRIMARY'
mysql> 
mysql> select * from db1.t17;
+----------+------+--------+
| clientip | port | status |
+----------+------+--------+
| 1.1.1.1  |   22 | allow  |
| 1.1.1.1  | 3306 | allow  |
| 2.1.1.1  | 3306 | allow  |
+----------+------+--------+
3 rows in set (0.00 sec)

mysql> 

mysql> alter table  db1.t17 drop primary key;


mysql> desc  db1.t17;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   |     | NULL    |       |
| port     | smallint(6)          | NO   |     | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from db1.t17;
+----------+------+--------+
| clientip | port | status |
+----------+------+--------+
| 1.1.1.1  |   22 | allow  |
| 1.1.1.1  | 3306 | allow  |
| 2.1.1.1  | 3306 | allow  |
+----------+------+--------+
3 rows in set (0.00 sec)

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
Query OK, 1 row affected (0.01 sec)

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
Query OK, 1 row affected (0.00 sec)

mysql> insert into  db1.t17 values (null,null,"allow");
ERROR 1048 (23000): Column 'clientip' cannot be null
mysql> 

mysql> delete from  db1.t17;
Query OK, 5 rows affected (0.00 sec)

mysql> select  * from db1.t17;
Empty set (0.00 sec)

mysql> desc  db1.t17;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   |     | NULL    |       |
| port     | smallint(6)          | NO   |     | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table  db1.t17 add primary key(clientip , port);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db1.t17;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| clientip | char(15)             | NO   | PRI | NULL    |       |
| port     | smallint(6)          | NO   | PRI | NULL    |       |
| status   | enum('allow','deny') | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t17;
+----------+------+--------+
| clientip | port | status |
+----------+------+--------+
| 2.1.1.1  | 3306 | allow  |
+----------+------+--------+
1 row in set (0.00 sec)

mysql> insert into  db1.t17 values ("2.1.1.1",3306,"allow");
ERROR 1062 (23000): Duplicate entry '2.1.1.1-3306' for key 'PRIMARY'
mysql> 
mysql> 
mysql> insert into  db1.t17 values ("2.1.1.1",3306,"deny");
ERROR 1062 (23000): Duplicate entry '2.1.1.1-3306' for key 'PRIMARY'
mysql> 
mysql> 
mysql> insert into  db1.t17 values ("1.1.1.1",3306,"allow");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t17;
+----------+------+--------+
| clientip | port | status |
+----------+------+--------+
| 1.1.1.1  | 3306 | allow  |
| 2.1.1.1  | 3306 | allow  |
+----------+------+--------+
2 rows in set (0.00 sec)

mysql>

主键通常与auto_increment 连用
自增长

create table  db1.t18( x  int primary key auto_increment , name char(2), age tinyint );

mysql> desc db1.t18;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| x     | int(11)    | NO   | PRI | NULL    | auto_increment |
| name  | char(2)    | YES  |     | NULL    |                |
| age   | tinyint(4) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 

mysql> select  * from db1.t18;
Empty set (0.00 sec)

mysql> insert into db1.t18(name,age)values("A",21);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+---+------+------+
| x | name | age  |
+---+------+------+
| 1 | A    |   21 |
+---+------+------+
1 row in set (0.00 sec)

mysql> insert into db1.t18(name,age)values("B",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+---+------+------+
| x | name | age  |
+---+------+------+
| 1 | A    |   21 |
| 2 | B    |   32 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> insert into db1.t18(name,age)values("C",32);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.t18(name,age)values("C",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+---+------+------+
| x | name | age  |
+---+------+------+
| 1 | A    |   21 |
| 2 | B    |   32 |
| 3 | C    |   32 |
| 4 | C    |   32 |
+---+------+------+
4 rows in set (0.00 sec)

mysql>
mysql> insert into db1.t18  values(null,"C",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+---+------+------+
| x | name | age  |
+---+------+------+
| 1 | A    |   21 |
| 2 | B    |   32 |
| 3 | C    |   32 |
| 4 | C    |   32 |
| 5 | C    |   32 |
+---+------+------+
5 rows in set (0.00 sec)

mysql> insert into db1.t18  values(3,"C",32);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into db1.t18  values(13,"C",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+----+------+------+
| x  | name | age  |
+----+------+------+
|  1 | A    |   21 |
|  2 | B    |   32 |
|  3 | C    |   32 |
|  4 | C    |   32 |
|  5 | C    |   32 |
| 13 | C    |   32 |
+----+------+------+
6 rows in set (0.00 sec)

mysql>
mysql> insert into db1.t18(name,age)values("C",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+----+------+------+
| x  | name | age  |
+----+------+------+
|  1 | A    |   21 |
|  2 | B    |   32 |
|  3 | C    |   32 |
|  4 | C    |   32 |
|  5 | C    |   32 |
| 13 | C    |   32 |
| 14 | C    |   32 |
+----+------+------+
7 rows in set (0.00 sec)

mysql> insert into db1.t18(name,age)values("C2",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+----+------+------+
| x  | name | age  |
+----+------+------+
|  1 | A    |   21 |
|  2 | B    |   32 |
|  3 | C    |   32 |
|  4 | C    |   32 |
|  5 | C    |   32 |
| 13 | C    |   32 |
| 14 | C    |   32 |
| 15 | C2   |   32 |
+----+------+------+
8 rows in set (0.00 sec)

mysql> 
mysql> delete from db1.t18;
Query OK, 8 rows affected (0.01 sec)

mysql> select  * from db1.t18;
Empty set (0.00 sec)

mysql> insert into db1.t18(name,age)values("D",32);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t18;
+----+------+------+
| x  | name | age  |
+----+------+------+
| 16 | D    |   32 |
+----+------+------+
1 row in set (0.00 sec)

mysql>

2.3.3 查看主键
desc 库.表；

2.3.4 测试主键

mysql> insert into  db1.t15 values ("bob","m",1);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t15;
+------+------+--------+
| name | sex  | stu_id |
+------+------+--------+
| bob  | m    |      1 |
+------+------+--------+
1 row in set (0.00 sec)

mysql> insert into  db1.t15 values ("tom","m",1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 
mysql> insert into  db1.t15 values ("tom","m",null);
ERROR 1048 (23000): Column 'stu_id' cannot be null
mysql> 
mysql> insert into  db1.t15 values ("tom","m",2);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t15;
+------+------+--------+
| name | sex  | stu_id |
+------+------+--------+
| bob  | m    |      1 |
| tom  | m    |      2 |
+------+------+--------+
2 rows in set (0.00 sec)

mysql>

2.3.5 删除主键
alter table 库.表 drop primary key ;

2.4 外键的使用
什么是外键？给外键字段赋值时，字段的值只能在参考表的字段值的范围内选择。

工资表gz_tab 员工表 yg_tab
外键 int
工资pay 员工编号姓名 user 员工编号职位性别
30000 8 Lucy 8
6
5 alices 3
null 4
jerry 5
jim 6

键作用: 限制字段赋值的
外键的使用规则？
1 表的存储引擎必须是 innodb
2 被参考的字段必须是主键
3 字段类型必须一致
4 被参考的表必须事先创建***

创建外键命令

create table  库.表1（字段名列表 ，foreign key(表1字段名) references 表2（表2字段名） on update cascade  on  delete cascade  ）engine=innodb;
 例子
mysql> create table db1.yg_tab( yg_id  int  primary key  auto_increment , name char(10) )engine=innodb;   创建被参考的表yg_tab


mysql> create table db1.gz_tab( gz_id  int  ,  pay float(7,2) , foreign key(gz_id)  references yg_tab(yg_id) on update cascade  on delete cascade )engine=innodb;  创建外键

mysql> desc db1.gz_tab; 查看表结构
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>


mysql> show create table db1.gz_tab \G 查看表的外键
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.04 sec)

mysql> 

删除外键
mysql> alter table db1.gz_tab drop  foreign key gz_tab_ibfk_1;
Query OK, 0 rows affected (0.01 sec)


mysql> show create table db1.gz_tab \G 查看外键是否被删除
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

在已有表设置外键字段
mysql> alter table db1.gz_tab add foreign key(gz_id) references yg_tab(yg_id) on update cascade on delete cascade ;


mysql> show create table db1.gz_tab \G 查看添加是否成功
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

验证外键的功能

mysql> select  * from db1.yg_tab;
Empty set (0.00 sec)
mysql> desc db1.yg_tab;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| yg_id | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into db1.yg_tab(name)values("tom"),("lucy"),("jerry");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | lucy  |
|     3 | jerry |
+-------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql> select  * from db1.gz_tab;
Empty set (0.00 sec)

mysql> desc db1.gz_tab;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into db1.gz_tab values(1,10000);
Query OK, 1 row affected (0.04 sec)

mysql> insert into db1.gz_tab values(2,20000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.gz_tab values(3,30000);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     2 | 20000.00 |
|     3 | 30000.00 |
+-------+----------+
3 rows in set (0.05 sec)

mysql>

mysql> insert into db1.gz_tab values(4,40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | lucy  |
|     3 | jerry |
+-------+-------+
3 rows in set (0.00 sec)

mysql>

mysql> insert into db1.gz_tab values(4,40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | lucy  |
|     3 | jerry |
+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into db1.yg_tab(name) values("jim");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | lucy  |
|     3 | jerry |
|     4 | jim   |
+-------+-------+
4 rows in set (0.00 sec)

mysql> insert into db1.gz_tab values(4,40000);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     2 | 20000.00 |
|     3 | 30000.00 |
|     4 | 40000.00 |
+-------+----------+
4 rows in set (0.00 sec)

mysql>

验证同步更新功能 on update cascade

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | lucy  |
|     3 | jerry |
|     4 | jim   |
+-------+-------+
4 rows in set (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     2 | 20000.00 |
|     3 | 30000.00 |
|     4 | 40000.00 |
+-------+----------+
4 rows in set (0.00 sec)

mysql> update  db1.yg_tab set yg_id=8 where yg_id=2;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     3 | jerry |
|     4 | jim   |
|     8 | lucy  |
+-------+-------+
4 rows in set (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
|     4 | 40000.00 |
+-------+----------+
4 rows in set (0.00 sec)

mysql>

验证同步删除功能 on delete cascade

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     3 | jerry |
|     4 | jim   |
|     8 | lucy  |
+-------+-------+
4 rows in set (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
|     4 | 40000.00 |
+-------+----------+
4 rows in set (0.00 sec)

mysql> 
mysql> delete from db1.yg_tab where yg_id=4;
Query OK, 1 row affected (0.04 sec)

mysql> select  * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     3 | jerry |
|     8 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> select  * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
+-------+----------+
3 rows in set (0.00 sec)

mysql>

优化配置

mysql> insert into db1.gz_tab values(3,40000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.gz_tab values(3,40000);
Query OK, 1 row affected (2.32 sec)

mysql> select * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
|     3 | 40000.00 |
|     3 | 40000.00 |
+-------+----------+
5 rows in set (0.08 sec)

mysql> 
mysql> 
mysql> insert into db1.gz_tab values(8,20000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
|     3 | 40000.00 |
|     3 | 40000.00 |
|     8 | 20000.00 |
+-------+----------+
6 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> insert into db1.gz_tab values(null,9999);
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
|     3 | 40000.00 |
|     3 | 40000.00 |
|     8 | 20000.00 |
|  NULL |  9999.00 |
+-------+----------+
7 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> desc db1.gz_tab;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> delete from db1.gz_tab;
Query OK, 7 rows affected (0.01 sec)

mysql> select * from db1.gz_tab;
Empty set (0.00 sec)

mysql> alter table  db1.gz_tab add primary key(gz_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.gz_tab;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | NO   | PRI | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table db1.gz_tab  \G
*************************** 1. row ***************************
       Table: gz_tab
Create Table: CREATE TABLE `gz_tab` (
  `gz_id` int(11) NOT NULL,
  `pay` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> select * from db1.gz_tab;
Empty set (0.00 sec)

mysql> select * from db1.Yg_tab;
ERROR 1146 (42S02): Table 'db1.Yg_tab' doesn't exist
mysql> select * from db1.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     3 | jerry |
|     8 | lucy  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into db1.gz_tab values(4,9999);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 
mysql> insert into db1.gz_tab values(null,9999);
ERROR 1048 (23000): Column 'gz_id' cannot be null
mysql> 
mysql> insert into db1.gz_tab values(1,9999);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.gz_tab values(3,9999);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.gz_tab values(8,9999);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.gz_tab values(8,9999);
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'
mysql> insert into db1.gz_tab values(3,9999);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into db1.gz_tab values(1,9999);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 
mysql> select  * from db1.gz_tab;
+-------+---------+
| gz_id | pay     |
+-------+---------+
|     1 | 9999.00 |
|     3 | 9999.00 |
|     8 | 9999.00 |
+-------+---------+
3 rows in set (0.00 sec)

mysql>