Mysql数据库基础系列（二）：表结构、键值

最新推荐文章于 2023-04-11 10:34:58 发布
最新推荐文章于 2023-04-11 10:34:58 发布
阅读量250 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tian1345/article/details/104321252
版权

表结构

约束条件

查看约束条件

mysql> desc 库名.表名;
在这里插入图片描述
字段名----| 类型---------------| 空------ |键值-- |默认值---- |额外设置 |

设置约束条件

  • 约束条件的作用：限制字段赋值
    null---------------允许为空（默认设置）
    not null-----------不允许为null（空）
    key-----------------键值类型
    default------------设置默认值，缺省为NULL，默认值要与该类型匹配
    extra----------------额外设置
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> use  db2
Database changed
mysql> create  table   db2.t1 (name  char(15) not  null , sex  enum("boy" , "girl") default  "boy" ,likes  set("eat" , "game" , "money" , "it") not  null   default "eat,money" );
Query OK, 0 rows affected (0.03 sec)

mysql> desc  db2.t1;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| name  | char(15)                       | NO   |     | NULL      |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
+-------+--------------------------------+------+-----+-----------+-------+
3 rows in set (0.00 sec)

测试使用默认值赋值:

mysql> insert into db2.t1(name) values ("nb");
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.t1;
+------+------+-----------+
| name | sex  | likes     |
+------+------+-----------+
| nb   | boy  | eat,money |
+------+------+-----------+
1 row in set (0.00 sec)

不使用默认值赋值:

mysql> insert into db2.t1 values ("dmy" , "girl" , "game,it" );
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.t1;
+------+------+-----------+
| name | sex  | likes     |
+------+------+-----------+
| nb   | boy  | eat,money |
| dmy  | girl | game,it   |
+------+------+-----------+
2 rows in set (0.00 sec)


测试null空值的使用:

mysql> insert into db2.t1 values (null , null , null );
ERROR 1048 (23000): Column 'name' cannot be null
#name字段不能为空

mysql> insert into db2.t1 values ("null" , null , null );
Query OK, 1 row affected (0.00 sec)

mysql> insert into db2.t1 values (null , null , null );
ERROR 1048 (23000): Column 'likes' cannot be null
#字段likes不能为空值

mysql> select  *  from  db2.t1;
+------+------+-----------+
| name | sex  | likes     |
+------+------+-----------+
| nb   | boy  | eat,money |
| dmy  | girl | game,it   |
| null | NULL | it        |
+------+------+-----------+
3 rows in set (0.00 sec)

mysql> drop  table  db2.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create  table  db2.t1(
    -> name  char(15) not  null  default "" ,
    -> sex  enum("boy" , "girl")  default  "boy",
    -> likes  set("eat" , "game" , "money", "it") not  null  default  "eat,money" );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t1;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| name  | char(15)                       | NO   |     |           |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
+-------+--------------------------------+------+-----+-----------+-------+

mysql> insert  into db2.t1(sex,likes) values("girl"  , "it");
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  t1;
+------+------+-------+
| name | sex  | likes |
+------+------+-------+
|      | girl | it    |
+------+------+-------+
1 row in set (0.00 sec)

""双引号内没有内容不代表为空，而是表示默认为0个字符
修改表结构

命令格式：
alter table 库名.表名 执行动作;

  • 执行动作：

①添加新字段，新字段默认添加在字段末尾
mysql > alter table 库名.表名 add 字段名 类型（宽度） 约束条件 [after 字段名 | first];

②修改字段类型，修改的字段类型不能与已存储的数据冲突
mysql > alter table 库名.表名 modify 字段名 类型（宽度） 约束条件 [after 字段名 | first];

③修改字段名，也可以用来修改字段类型
mysql > alter table 库名.表名 change 源字段名 新字段名 类型（宽度） 约束条件 ;
当跟新类型和约束条件时，可以修改字段类型

④删除字段，表中有多条记录时，所有列的此字段的值都会被删除
mysql > alter table 库名.表名 drop 字段名;

⑤修改表名，表对应的文件名，也会被改变，表记录不受影响
mysql > alter table 表名 rename 新表名;

mysql> use  db2;
Database changed
mysql> alter  table  t1  add  class  char(7)  default "1234567"  first;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter  table  t1  add  email  varchar(50);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter  table  t1  add  age  tinyint  unsigned   default  23  after  name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.t1;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| class | char(7)                        | YES  |     | 1234567   |       |
| name  | char(15)                       | NO   |     |           |       |
| age   | tinyint(3) unsigned            | YES  |     | 23        |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
| email | varchar(50)                    | YES  |     | NULL      |       |
+-------+--------------------------------+------+-----+-----------+-------+
6 rows in set (0.00 sec)

mysql> insert  into db2.t1(sex ,likes)  values("girl" ,"it");

mysql> select  *  from  db2.t1;
+---------+------+------+------+-------+-------+
| class   | name | age  | sex  | likes | email |
+---------+------+------+------+-------+-------+
| 1234567 |      |   23 | girl | it    | NULL  |
+---------+------+------+------+-------+-------+
1 row in set (0.00 sec)

修改字段类型：

mysql> use  db2;
Database changed

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

mysql> desc  db2.t1;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| class | char(7)                        | YES  |     | 1234567   |       |
| name  | char(15)                       | NO   |     |           |       |
| age   | tinyint(3) unsigned            | YES  |     | 23        |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
| email | varchar(50)                    | YES  |     | NULL      |       |
+-------+--------------------------------+------+-----+-----------+-------+
6 rows in set (0.01 sec)

mysql> select  *  from  t1;
+---------+------+------+------+-------+-------+
| class   | name | age  | sex  | likes | email |
+---------+------+------+------+-------+-------+
| 1234567 |      |   23 | girl | it    | NULL  |
+---------+------+------+------+-------+-------+
1 row in set (0.00 sec)

mysql> alter  table   t1  modify  email   char(30)  default "stu@163.com";
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter  table   t1  modify  age  tinyint  unsigned   default   23  after  class;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.t1;
+-------+--------------------------------+------+-----+-------------+-------+
| Field | Type                           | Null | Key | Default     | Extra |
+-------+--------------------------------+------+-----+-------------+-------+
| class | char(7)                        | YES  |     | 1234567     |       |
| age   | tinyint(3) unsigned            | YES  |     | 23          |       |
| name  | char(15)                       | NO   |     |             |       |
| sex   | enum('boy','girl')             | YES  |     | boy         |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money   |       |
| email | char(30)                       | YES  |     | stu@163.com |       |
+-------+--------------------------------+------+-----+-------------+-------+
6 rows in set (0.00 sec)

修改字段名：   #把name字段变为了user字段

mysql> alter table  t1  change  name  user  char(15)  not  null  default "" ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc   t1;
+-------+--------------------------------+------+-----+-------------+-------+
| Field | Type                           | Null | Key | Default     | Extra |
+-------+--------------------------------+------+-----+-------------+-------+
| class | char(7)                        | YES  |     | 1234567     |       |
| age   | tinyint(3) unsigned            | YES  |     | 23          |       |
| user  | char(15)                       | NO   |     |             |       |
| sex   | enum('boy','girl')             | YES  |     | boy         |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money   |       |
| email | char(30)                       | YES  |     | stu@163.com |       |
+-------+--------------------------------+------+-----+-------------+-------+
6 rows in set (0.00 sec)

删除字段：   #class字段和email字段被删除

mysql> alter  table  t1  drop  class ,  drop  email;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc   t1;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| age   | tinyint(3) unsigned            | YES  |     | 23        |       |
| user  | char(15)                       | NO   |     |           |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
+-------+--------------------------------+------+-----+-----------+-------+
4 rows in set (0.01 sec)

修改表名：

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

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

mysql> select  *  from   stuinfo;
+------+------+------+-------+
| age  | user | sex  | likes |
+------+------+------+-------+
|   23 |      | girl | it    |
+------+------+------+-------+
1 row in set (0.00 sec)

MySQL键值

MySQL键值概述

键值类型
  • 根据数据存储要求，选择键值：
    ① index ---------- 普通索引
    ② unique --------- 唯一索引
    ③ fulltext --------- 全文索引
    ④ primary key — 主键
    ⑤ foreign key — 外键
索引介绍
  • 索引是什么？
    1.类似于书的目录
    2.对表中字段值进行排序
    3.索引类型包括：Btree、B+tree、hash
    默认的索引类型是Btree（二叉树）
    在这里插入图片描述
索引的优缺点
  • 索引优点
  • 通过创建唯一性的索引，可以保证数据库表中每一行数据的唯一性
  • 可以加快数据的查询速度
  • 索引缺点
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的调整，降低了数据的维护速度
  • 索引需要占物理空间
MySQL键值的使用
index 普通索引
  • 使用规则
  • 一个表中可以有多个index字段
  • 字段的值允许重复，且可以赋予NULL值
  • 通常把作为查询条件的字段设置为index字段
  • index字段的标志是 MUL
[root@host51 ~]# ls  /var/lib/mysql/db2/       #索引信息存在stuinfo.ibd目录下
db.opt  stuinfo.frm  stuinfo.ibd
  • 建表时创建索引
  • index （字段名），index（字段名）……
mysql> create  table  db2.t2 (
    -> name  char(10),
    -> chengji  int  ,
    -> class  char(7) ,
    -> index(name) ,index(chengji)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t2;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(10) | YES  | MUL | NULL    |       |
| chengji | int(11)  | YES  | MUL | NULL    |       |
| class   | char(7)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 在已有表里创建索引
  • create index 索引名 on 表名（字段名）;
mysql> create  index  aaa  on db2.stuinfo(user);    #删除时直接删除aaa即可
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.stuinfo;
+-------+--------------------------------+------+-----+-----------+-------+
| Field | Type                           | Null | Key | Default   | Extra |
+-------+--------------------------------+------+-----+-----------+-------+
| age   | tinyint(3) unsigned            | YES  |     | 23        |       |
| user  | char(15)                       | NO   | MUL |           |       |
| sex   | enum('boy','girl')             | YES  |     | boy       |       |
| likes | set('eat','game','money','it') | NO   |     | eat,money |       |
+-------+--------------------------------+------+-----+-----------+-------+
4 rows in set (0.00 sec)
  • 查看索引信息
  • show index from 表名 \G
mysql> show  index  from  db1.t5\G;    #查看db1的t5表，t5表没有做索引，所以显示空
Empty set (0.00 sec)

ERROR: 
No query specified


mysql> show  index  from  db2.stuinfo\G;
*************************** 1. row ***************************
        Table: stuinfo           
   Non_unique: 1
     Key_name: aaa            #索引名称，如果是建表时同时创建索引，则默认索引名和字段名相同
 Seq_in_index: 1
  Column_name: user
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE          #排队类型，Btree（二叉树）
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
  • 删除索引
  • drop index 索引名 on 表名;
mysql> drop  index  chengji  on  db2.t2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> drop  index  name  on  db2.t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

在建表时指定索引名，不使用默认和字段名同名，但不推荐修改索引名，因为如果要删除还要先show create 查找当前表使用的索引名，再次删除

mysql> create  table  db2.y (name  char(3) ,age  int , index(name));
Query OK, 0 rows affected (0.01 sec)

mysql> desc db2.y;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(3) | YES  | MUL | NULL    |       |
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show  create  table db2.y\G
*************************** 1. row ***************************
       Table: y
Create Table: CREATE TABLE `y` (
  `name` char(3) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create  table  db2.z (name  char(3) ,age  int ,key zzz (name));
Query OK, 0 rows affected (0.01 sec)

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

mysql> show  create  table  db2.z\G
*************************** 1. row ***************************
       Table: z
Create Table: CREATE TABLE `z` (
  `name` char(3) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `zzz` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
primary key 主键
  • 使用规则
  • 字段值不允许重复，且不允许赋NULL值
  • 一个表中只能有一个primary key字段
  • 多个字段都作为主键，称为复合主键，必须一起创建
  • 主键字段的标志是PRI
  • 主键通常与auto_increment连用
  • 通常把表中唯一标识记录的字段设置为主键[记录编号字段]
  • 在建表的时候，如果主键字段为int类型，还可以为其设置AUTO_INCREMENT自增属性，这样当添加新的表记录时，此字段的值会自动从1开始逐个增加，无需手动指定。
  • 建表时创建主键
  • primary key（字段名）;
mysql> create  table  db2.t3( name  char(3) , age  int , homeaddr  char(9) , tel  char(11), primary  key(tel) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t3;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(3)  | YES  |     | NULL    |       |
| age      | int(11)  | YES  |     | NULL    |       |
| homeaddr | char(9)  | YES  |     | NULL    |       |
| tel      | char(11) | NO   | PRI | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> create  table  db2.t4( name  char(3) , age  int , homeaddr  char(9) , tel  char(11)  primary  key );   #此时primary  key做参数，也可以创建
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t4;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(3)  | YES  |     | NULL    |       |
| age      | int(11)  | YES  |     | NULL    |       |
| homeaddr | char(9)  | YES  |     | NULL    |       |
| tel      | char(11) | NO   | PRI | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
  • 在已有表里添加主键，要求，必须是空表！！！
  • alter table 表名 add primary key（字段名）;
mysql> select * from  db2.t2;
Empty set(0.00 sec)

mysql> alter  table  db2.t2 add  primary  key(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.t2;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(10) | NO   | PRI | NULL    |       |
| chengji | int(11)  | YES  |     | NULL    |       |
| class   | char(7)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 删除主键
  • 移除主键前，如果有自增属性，必须先去掉
  • alter table 表名 drop primary key;
mysql> alter  table  db2.t2  drop primary  key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.t2;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(10) | NO   |     | NULL    |       |
| chengji | int(11)  | YES  |     | NULL    |       |
| class   | char(7)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除主键后允许写同样的字段值，但依旧不允许为null
  • 创建复合主键（表中多列一起做主键），插入记录是复合主键的值只要不是同时重复即可
  • primary key（字段名列表）;
  • 与auto_increment连用
mysql> create  table  db2.t5(client_ip  char(15)  ,ser_port  smallint  , access_status  enum ("allow" , "deny") , primary  key (client_ip ,ser_port)  );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t5;
+---------------+----------------------+------+-----+---------+-------+
| Field         | Type                 | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+-------+
| client_ip     | char(15)             | NO   | PRI | NULL    |       |
| ser_port      | smallint(6)          | NO   | PRI | NULL    |       |
| access_status | enum('allow','deny') | YES  |     | NULL    |       |
+---------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> insert  into  db2.t5  values("1.1.1.1" , 21 , "allow" );
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.t5  values("1.1.1.1" , 22 , "deny" );
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.t5  values("2.1.1.1" , 22 , "deny" );
Query OK, 1 row affected (0.01 sec)

mysql> select  *  from  db2.t5;
+-----------+----------+---------------+
| client_ip | ser_port | access_status |
+-----------+----------+---------------+
| 1.1.1.1   |       21 | allow         |
| 1.1.1.1   |       22 | deny          |
| 2.1.1.1   |       22 | deny          |
+-----------+----------+---------------+
3 rows in set (0.00 sec)

主键与auto_increment连用
字段值自增长  相当于 i++

mysql> create  table  db2.t6 (id  int  primary  key  auto_increment ,
    -> name  char(10) ,
    -> tel  char(11) ,
    -> qq    char(11) 
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db2.t6;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
| tel   | char(11) | YES  |     | NULL    |                |
| qq    | char(11) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("bob" , "121212" , "2222");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  t6;
+----+------+--------+------+
| id | name | tel    | qq   |
+----+------+--------+------+
|  1 | bob  | 121212 | 2222 |
+----+------+--------+------+
1 row in set (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("aaa" , "133212" , "2222");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  db2.t6;
+----+------+--------+------+
| id | name | tel    | qq   |
+----+------+--------+------+
|  1 | bob  | 121212 | 2222 |
|  2 | aaa  | 133212 | 2222 |
+----+------+--------+------+
2 rows in set (0.00 sec)

mysql> insert  into  db2.t6  values(null  ,"aaa" , "133212" , "2222");
Query OK, 1 row affected (0.00 sec)
#用null时没有值，而我们给它自增，所以可以直接自增存入数据
mysql> select  * from  db2.t6;
+----+------+--------+------+
| id | name | tel    | qq   |
+----+------+--------+------+
|  1 | bob  | 121212 | 2222 |
|  2 | aaa  | 133212 | 2222 |
|  3 | aaa  | 133212 | 2222 |
+----+------+--------+------+
3 rows in set (0.00 sec)

mysql> insert  into  db2.t6  values(9  ,"xxxx" , "133212" , "2334");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  db2.t6;
+----+------+--------+------+
| id | name | tel    | qq   |
+----+------+--------+------+
|  1 | bob  | 121212 | 2222 |
|  2 | aaa  | 133212 | 2222 |
|  3 | aaa  | 133212 | 2222 |
|  9 | xxxx | 133212 | 2334 |
+----+------+--------+------+
4 rows in set (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("sss" , "133212" , "5344");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from  db2.t6;
+----+------+--------+------+
| id | name | tel    | qq   |
+----+------+--------+------+
|  1 | bob  | 121212 | 2222 |
|  2 | aaa  | 133212 | 2222 |
|  3 | aaa  | 133212 | 2222 |
|  9 | xxxx | 133212 | 2334 |
| 10 | sss  | 133212 | 5344 |
+----+------+--------+------+
5 rows in set (0.01 sec)

mysql> delete  from  db2.t6;            #删除所有
Query OK, 5 rows affected (0.00 sec)

mysql> select  *  from t6;
Empty set (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("qqq" , "133512" , "222344");       #再次添加，查看，发现id依然自增
Query OK, 1 row affected (0.01 sec)

mysql> select  *  from  db2.t6;
+----+------+--------+--------+
| id | name | tel    | qq     |
+----+------+--------+--------+
| 11 | qqq  | 133512 | 222344 |
+----+------+--------+--------+
1 row in set (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("eee" , "13332112" , "26744");
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.t6(name ,tel ,qq)  values("rrr" , "004858" , "26989");
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from db2.t6;
+----+------+----------+--------+
| id | name | tel      | qq     |
+----+------+----------+--------+
| 11 | qqq  | 133512   | 222344 |
| 12 | eee  | 13332112 | 26744  |
| 13 | rrr  | 004858   | 26989  |
+----+------+----------+--------+
3 rows in set (0.00 sec)


只要没有删除auto_increment，自增就不会取消
foreign key 外键
  • 外键功能
  • 插入记录时，字段值在另一个表字段值范围内选择
  • 使用规则
  • 表存储引擎必须是innodb
  • 字段类型要一致
    被参照字段必须要是索引类型的一种（primary key）
  • 创建外键
  • create table 表名（字段名列表 ， foreign key（字段名） references 表名（字段名） on update cascade on delete cascade）engine=innodb ;
  • foreign key（字段名） references 表名（字段名） #指定外键
  • on update cascade #同步更新
  • on delete cascade #同步删除
  • engine=innodb #指定存储引擎
mysql> create  table  db2.yg  ( yg_id  int  primary  key  auto_increment , name  char(15) )engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> create  table  db2.gz ( gz_id  int  ,  name  char(15) , gz  float(7,2), foreign  key(gz_id)  references  db2.yg (yg_id)  on  update  cascade  on  delete  cascade )engine=innodb;
Query OK, 0 rows affected (0.02 sec)
#n  update  cascade  on  delete  cascade  同步更新，表示与db2.yg表同步更新

mysql> desc  db2.yg;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| yg_id | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(15) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.06 sec)

mysql> desc  db2.gz;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| name  | char(15)   | YES  |     | NULL    |       |
| gz    | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)



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

mysql> insert  into db2.gz  values(1 , "bob" , 50000) ;
Query OK, 1 row affected (0.00 sec)      #测试插入数据的约束功能

mysql> select  *  from  db2.yg;
+-------+------+
| yg_id | name |
+-------+------+
|     1 | bob  |
+-------+------+
1 row in set (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+------+----------+
| gz_id | name | gz       |
+-------+------+----------+
|     1 | bob  | 50000.00 |
+-------+------+----------+
1 row in set (0.00 sec)
mysql> insert  into db2.gz  values(2 , "tom" , 60000) ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
#测试插入数据的约束功能，不能在子键中直接创建，应该在db2.yg中

测试同步更新：

mysql> update  db2.yg   set  yg_id=8  where  name="bob" ;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  *  from  db2.yg;
+-------+------+
| yg_id | name |
+-------+------+
|     8 | bob  |
+-------+------+
1 row in set (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+------+----------+
| gz_id | name | gz       |
+-------+------+----------+
|     8 | bob  | 50000.00 |
+-------+------+----------+
1 row in set (0.00 sec)

测试同步删除：

mysql> select  *  from  db2.gz;
+-------+------+----------+
| gz_id | name | gz       |
+-------+------+----------+
|     8 | bob  | 50000.00 |
+-------+------+----------+
1 row in set (0.00 sec)

mysql> delete  from db2.yg  where  name="bob";
Query OK, 1 row affected (0.06 sec)

mysql> select  *  from  db2.yg;
Empty set (0.00 sec)

mysql> select  *  from  db2.gz;
Empty set (0.00 sec)
mysql> insert  into  db2.yg  values(6 , "jerry");
Query OK, 1 row affected (0.01 sec)

mysql> insert  into  db2.gz  values(6 , "jerry" ,  65000);
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     6 | jerry |
+-------+-------+
1 row in set (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     6 | jerry | 65000.00 |
+-------+-------+----------+
1 row in set (0.00 sec)

mysql> insert  into  db2.yg(name)  values( "jerry");
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.gz  values(7 , "jerry" ,  66000);
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     6 | jerry |
|     7 | jerry |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     6 | jerry | 65000.00 |
|     7 | jerry | 66000.00 |
+-------+-------+----------+
2 rows in set (0.00 sec)

mysql> insert  into  db2.gz  values(null , "tian" ,  66000);
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     6 | jerry | 65000.00 |
|     7 | jerry | 66000.00 |
|  NULL | tian  | 66000.00 |
+-------+-------+----------+
3 rows in set (0.00 sec)

mysql> insert  into  db2.gz  values(7 , "jerry" ,  66000);
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|     6 | jerry | 65000.00 |
|     7 | jerry | 66000.00 |
|  NULL | tian  | 66000.00 |
|     7 | jerry | 66000.00 |
+-------+-------+----------+
4 rows in set (0.00 sec)
#此时，gz_id表中可以出现两次7以及null，会出现冲突，我们要将gz_id设为主键
mysql> delete  from  db2.gz;
Query OK, 0 rows affected (0.00 sec)

mysql> alter  table  db2.gz  add  primary  key (gz_id) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  db2.gz;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | NO   | PRI | NULL    |       |
| name  | char(15)   | YES  |     | NULL    |       |
| gz    | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> select  *  from  db2.gz;
Empty set (0.01 sec)

mysql> delete  from db2.yg;
Query OK, 2 rows affected (0.00 sec)

mysql> insert  into  db2.yg  values(6, "tom" );
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.yg  values(7, "tian" );
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.yg  values(6, "tom" );
Query OK, 1 row affected (0.00 sec)

mysql> insert  into  db2.yg  values(7, "tian" );
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.yg;
+-------+------+
| yg_id | name |
+-------+------+
|     6 | tom  |
|     7 | tian |
+-------+------+
2 rows in set (0.00 sec)


mysql> insert  into db2.gz values  (6 , "tom" ,20000);
Query OK, 1 row affected (0.01 sec)

mysql> insert  into db2.gz values  (7 , "tian" ,30000);
Query OK, 1 row affected (0.01 sec)

mysql> select  *  from  db2.gz;
+-------+------+----------+
| gz_id | name | gz       |
+-------+------+----------+
|     6 | tom  | 20000.00 |
|     7 | tian | 30000.00 |
+-------+------+----------+
2 rows in set (0.00 sec)

mysql> insert  into db2.gz values  (7 , "tian" ,30000);
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
mysql> insert  into db2.gz values  (6 , "tom" ,20000);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
mysql> insert  into db2.gz values  (null, "haha" ,20000);
ERROR 1048 (23000): Column 'gz_id' cannot be null
#此时gz_id不允许重复以及为空
  • 查看表的外键：
  • mysql> show create table 库名.表名 \G
mysql> show  create  table  db2.yg \G
*************************** 1. row ***************************
       Table: yg
Create Table: CREATE TABLE `yg` (
  `yg_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(15) DEFAULT NULL,
  PRIMARY KEY (`yg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


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

# CONSTRAINT `gz_ibfk_1`默认外键名称
  • 删除外键
  • alter table 表名 drop foreign key 外键名;
mysql> alter  table  db2.gz  drop  foreign  key  gz_ibfk_1 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show  create  table  db2.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `name` char(15) DEFAULT NULL,
  `gz` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


mysql> insert  into  db2.gz  values (11 , "max" , 80000);
Query OK, 1 row affected (0.00 sec)

mysql> select  *  from  db2.gz;
+-------+------+----------+
| gz_id | name | gz       |
+-------+------+----------+
|     6 | tom  | 20000.00 |
|     7 | tian | 30000.00 |
|    11 | max  | 80000.00 |
+-------+------+----------+
3 rows in set (0.00 sec)

#此时添加字段不受外键约束

在已有表添加外键：
mysql> alter  table db2.gz add  foreign key(gz_id) references  db2.yg(yg_id)  on update  cascade  on delete   cascade ;
#此时不允许添加外键，因为yg表中没有id=11字段，受外键约束
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`#sql-443_3`, CONSTRAINT `#sql-443_3_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> delete  from  db2.gz  where  gz_id=11;  #可以删除gz表中id=11的字段，也可以在yg表中添加id=11字段
Query OK, 1 row affected (0.00 sec)

mysql> alter  table db2.gz add  foreign key(gz_id) references  db2.yg(yg_id)  on update  cascade  on delete   cascade ;  #再次添加，此时不受约束，可以添加成功
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show  create  table  db2.gz \G     #查看表的外键
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) NOT NULL,
  `name` char(15) DEFAULT NULL,
  `gz` float(7,2) DEFAULT NULL,
  PRIMARY KEY (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
哦吼吼吼tian
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
[MYSQL / Mariadb]数据库学习-表结构、键值（普通索引、主键、外键）
HYFarashi的博客
05-12 792
涵盖MySQL数据库的表结构、键值、主键、外键的应用
16位mysql键值_MYSQL字段、表结构、以及MySQL键值的基本使用
weixin_42501892的博客
02-22 196
字段约束条件：控制表结构约束条件• Null 允许为空,默认设置• NOT NULL 不允许为空• Key 索引类型• Default 设置默认值,缺省为 NULLmysql> create table t10(-> name char(10) not null,-> age tinyint(2) default 19,-> likes set("eat","sleep...
MYSQL字段、表结构、以及MySQL键值的基本使用
BELONE
10-29 872
字段约束条件：控制表结构 约束条件 • Null 允许为空,默认设置 • NOT NULL 不允许为空 • Key 索引类型 • Default 设置默认值,缺省为 NULL mysql> create table t10( -> name char(10) not null, -> age tinyint(2) default 19, -> likes set("ea...
数据库中的键（码）
齐天佑
08-13 1万+
数据库中的键（key）也可以称为码，是关系模型中的一个重要概念，它是逻辑结构，不是数据库的物理部分。 在一个关系中，存在一个或多个属性，可以在这个关系中将每个元组唯一标识。（可以粗略的理解：关系=表） 超键（码） 能唯一标识元组的属性集，其中一个属性可以为作为一个超键，多个属性组合也可以作为一个超键。 表1 例：上表中...
MySQL报错详解及总结
weixin_45386953的博客
04-11 1647
原文地址：https://www.js1331.com/jiaocheng?id=284310报错原因：密码错误解决方式：检查自己的账号密码是否输入正确2003 - Can’t connect to MySQL server on ‘128.0.0.1’ (10038) 由于目标计算机积极拒绝，无法连接。报错原因：1：数据库地址填写错误。2：数据库端口填写错误。3：数据库或者所在服务器的防火墙或者白名单未开通。4：数据库账号ip访问限制。
11.2： 表结构 、 MySQL键值 、 总结和答疑.docx
03-05
本篇文章将详细讨论在MySQL中关于表结构、键值以及如何处理数据库约束条件、修改表结构的相关知识点。 首先，我们来看案例1：约束条件。在数据库设计中，约束条件用于确保数据的完整性和一致性。在这个例子中，我们...
解析mysql数据库还原错误:(mysql Error Code: 1005 errno 121)
09-10
MySQL数据库在执行还原操作时，可能会遇到各种错误，其中一种常见的错误是`Error Code: 1005`，伴随着`errno 121`。这个错误通常与表的结构有关，特别是涉及到外键约束或者主键冲突时。在本文中，我们将深入探讨这个...
数据库MySQL基础知识点1
12-09
【数据库概念及作用】 数据库是一个组织和存储数据的系统，它由一组按照特定规则组织的特殊格式文件组成。数据库的主要作用是提供高效、安全的数据存储和访问，确保数据...这些知识对于理解和使用MySQL数据库至关重要。
MySQL-01：数据库概述
05-01
1. 数据库管理系统（DBMS）、数据库和数据库表的关系： - 从客户的角度看，DBMS提供了与数据库交互的接口，用户可以通过这些接口查询、修改或添加数据。 - 从开发者的角度看，DBMS提供了数据定义语言（DDL）和数据...
Mysql数据库引擎大揭秘
最新发布
01-17
- 非关系型数据库引擎（NoSQL）：如MongoDB，采用键值对、文档、列族等方式，适合非结构化数据，强调高性能和可伸缩性。 - 内存数据库引擎：如Redis，数据存储在内存中，提供快速读写，适用于临时数据和高并发场景。...
MySql 常见错误代码大全
weixin_64051447的博客
03-27 4559
消息：SELECT 将检查超过 MAX_JOIN_SIZE 的行，如果 SELECT 正常，请检查 WHERE，并使用 SET SQL_BIG_SELECTS=1 或 SET SQL_MAX_JOIN_SIZE=#。消息：由于该 MySQL 从服务器是在不支持 SSL 的情况下编译的，CHANGE MASTER 中的 SSL 参数被忽略，随后，如果启动了具备 SSL 功能的 MySQL，可使用这些参数。消息：线程堆栈溢出，已使用，% ld 堆栈的 % ld。
Windows系统错误代码-----故障检测用
cuiyingchan0663的博客
02-19 1930
Microsoft Windows 系统错误代码 0000 操作已成功完成。 0001 错误的函数。 0002 系统找不到指定的文件。 0003 系统找不到指定的路径。 0004 系统无法打开文件。 0005 拒绝访问。 000...
MYSQL— perror 错误码详情
热门推荐
道行尚浅（老道）的博客
12-04 11万+
[root@localhost ~]# cat test_nothread.py import paramiko import threading import os def ssh2(ip,username,passwd,cmd): file_path='/root/perror.log' logfile=file(file_path,'a') try:
mysql报错1357_mysql8 参考手册--错误代码1343-1367
weixin_31993699的博客
02-08 1096
错误号1343; 符号： ER_FPARSER_ERROR_IN_PARAMETER; SQLSTATE：HY000消息：解析参数'％s'时出错(行：'％s')错误号1344; 符号： ER_FPARSER_EOF_IN_UNKNOWN_PARAMETER; SQLSTATE：HY000消息：跳过未知参数'％s'时文件意外结束错误号1345; 符号： ER_VIEW_NO_EXPLAIN; SQL...
mysql 唯一的区别吗_Mysql主键和唯一键的区别点总结
weixin_29479863的博客
01-21 334
什么是主键？主键是表中唯一标识该表中每个元组(行)的列。主键对表实施完整性约束。表中只允许使用一个主键。主键不接受任何重复值和空值。表中的主键值很少更改，因此在选择主键是需要小心，要选择很少发生更改的地方。一个表的主键可以被另一个表的外键引用。为了更好地理解主键，我们创建一个名为Student的表，它具有roll_number、name、batch、phone_number、citizen_id等...
MySQL报错-错误代码、出错信息对照大全
qq_44981598的博客
11-21 4455
mysql
MYSQL ERROR CODE
weixin_34235105的博客
03-29 615
mysql error code(备忘) 转1005：创建表失败 1006：创建数据库失败 1007：数据库已存在，创建数据库失败 1008：数据库不存在，删除数据库失败 1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败 1012：不能读取系统表中的记录 1020：记录已被其他用户修改...
mysql之explain详解（分析索引的最佳使用）
乐乐的博客
04-17 3万+
在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。
MySQL 单表(employees)基础查询练习 (一)
vcfriend的专栏
02-10 1万+
测试用数据来源于Oracle数据库中的测试数据MySQL数据库表:  employees库表测试试数据#--基本SQL语句练习 #--1.查询员工表所有数据 select * from employees #--2.打印公司里所有的manager_id select manager_id from employees where manager_id is not null #--3.查询所员...
MySQL数据库基础：创建与操作
"这篇教程主要介绍了如何在MySQL中创建数据库，并提供了基础的数据库概念和MySQL数据库的概述。" 在MySQL数据库管理系统中，创建数据库是非常基础的操作。通过`CREATE DATABASE`语句，用户可以建立新的数据库实例。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值