MySQL数据库基础(二)表结构

一、表结构

mysql> desc db1.t1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(5)  | YES  |     | NULL    |       |
| homedir | char(50) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

| 字段名   | 类型     | 空   |键值  | 默认值  |额外设置| 

1.1、设置约束条件

• null 允许为空（默认设置)
• not null 不允许为null(空)
• key 键值类型
• default 设置默认值，缺省为NULL
• extra 额外设置

mysql> create table db1.t7(
    -> name char(10) not null,
    -> age tinyint unsigned default 19,
    -> class char(7) not null default "nsd1902",
    -> pay float(7,2) default 28000
    -> );

mysql> create table db2.t2( 
class char(9) default "null", name char(10) not null, age tinyint(4) not
null default "19", likes set('a','b','c','d') default "a,b" );

案例1:
要求如下图设置约束条件:

mysql> desc db2.t2;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| class | char(9)              | YES  |     | NULL    |       |
| name  | char(10)             | NO   |     | NULL    |       |
| age   | tinyint              | NO   |     | 19      |       |
| likes | set('a','b','c','d') | YES  |     | a,b     |       |
+-------+----------------------+------+-----+---------+-------+

1）在db2库里创建t2表时设置字段约束条件

mysql> create database db2;                //建库
Query OK, 1 row affected (0.07 sec)
mysql> use db2;                              //切换数据库
Database changed
mysql> create table db2.t2(        //建表设置字段约束条件
    -> class char(9),
    -> name char(10) not null,
    -> age tinyint(4) not null default "19",
    -> likes set('a','b','c','d') default "a,b"
    -> );

二、修改表结构

2.1 语法结构

mysql> alter table 库名.表名 执行动作;

• add 添加字段
• modify 修改字段类型
• change 修改字段名
• drop 删除字段
• rename 修改表名

2.2 添加新字段 add

mysql> alter table 库名.表名  
       add 字段名 类型(宽度) 约束条件 
       [ after 字段名│ first ];    --可选项 默认最后的字段追加

1）添加字段 grade char(15)

mysql> alter table db1.t2 
    -> add grade char(15)    
    -> after name;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int        | YES  |     | NULL    |       |
| name    | char(5)    | YES  |     | NULL    |       |
| grade   | char(15)   | YES  |     | NULL    |       |
| age     | tinyint    | YES  |     | NULL    |       |
| pay     | float      | YES  |     | NULL    |       |
| money   | float(5,2) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

2.3 修改字段类型

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

2）修改age 字段类型并放到name字段下面

mysql> alter table db1.t2 
    -> modify
    -> age int 
    -> after name;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t2;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| stu_num | int        | YES  |     | NULL    |       |
| name    | char(5)    | YES  |     | NULL    |       |
| age     | int        | YES  |     | NULL    |       |
| grade   | char(15)   | YES  |     | NULL    |       |
| pay     | float      | YES  |     | NULL    |       |
| money   | float(5,2) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
6 rows in set (0.03 sec)

2.4 修改字段名

-也可以用来修改字段类型
**mysql> alter table 库名.表名

   change  源字段名 新字段名  类型(宽度)  约束条件;**

注:当 新字段名 跟新类型和约束条件时，可修改字段类型

3）修改字段 stu_num字段名为id

mysql> alter table db1.t2 change stu_num id int;
Query OK, 0 rows affected (0.04 sec)

2.5 删除字段

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

4）删除字段 drop grade

mysql> alter table db1.t2 drop money,drop  grade;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.6 修改表名

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

5）修改t2表名为school

mysql> alter table db1.t2 rename school;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| school        |
| t1            |
| t3            |
| t4            |
| t5            |
+---------------+

三、MySQL键值

3.1 MySQL键值概述

键值类型
根据数据存储要求，选择键值
1.index 普通索引
2.unique 唯一索引
3.fulltext 全文索引
4.primary key 主键
5.foreign key 外键

索引介绍
-类似于书的目录
-对表中字段值进行排序。
-索引类型包括:Btree、B+tree . hash

3.2 索引优缺点

索引优点
-通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
-可以加快数据的查询速度

索引缺点
-当对表中的数据进行增加、删除和修改的时候，索引也要动态的调整，降低了数据的维护速度
-索引需要占物理空间

3.3 键值使用 index普通索引 创建 删除 查看

使用规则

• 一个表中可以有多个index字段
• 字段的值允许重复，且可以赋NULL值
• 通常把做为查询条件的字段设置为index字段
• index字段标志是 MUL

索引创建
建表的时创建索引

• index(字段名), index(字段名).. ..

1）创建索引字段id、name

mysql> create table tea4(
    -> id char(6) not null,
    -> name varchar(4) not null,
    -> age int(3) not null,
    -> gender enum("boy","girl") default "boy",
    -> index(id),index(name)
    -> );
Query OK, 0 rows affected, 1 warning (0.22 sec)

mysql> desc tea4;
+--------+--------------------+------+-----+---------+-------+
| Field  | Type               | Null | Key | Default | Extra |
+--------+--------------------+------+-----+---------+-------+
| id     | char(6)            | NO   | MUL | NULL    |       |
| name   | varchar(4)         | NO   | MUL | NULL    |       |
| age    | int                | NO   |     | NULL    |       |
| gender | enum('boy','girl') | YES  |     | boy     |       |
+--------+--------------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

在已有的表里创建索引

• create index 索引名 on 表名(字段名);

索引删除

• drop index 索引名 on 表名;

索引查看

-show index from 表名\G;

mysql> show index from tea4\G;
*************************** 1. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: id     //索引字段1
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: tea4
   Non_unique: 1
     Key_name: name    //索引字段2
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE       //使用二叉树木算法
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

3.4 键值使用 key主键 创建 删除 查看

使用规则

• 字段值不允许重复，且不允许赋NULL值
• 一个表中只能有一个primary key字段
• 多个字段都作为主键，称为复合主键，必须一起创建。
• 主键字段的标志是PRI
• 主键通常与主键自动增长 auto_increment 连用
• 通常把表中唯一标识记录的字段设置为主键[记录编号字段]

建表时创建主键
- primary key(字段名)

mysql> create table t8( 
    -> name char(3) primary key,
    -> id int
    -> );
Query OK, 0 rows affected (0.11 sec)
mysql> desc t8;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(3) | NO   | PRI | NULL    |       |
| id    | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.05 sec)

在已有表里添加主键

• alter table 表名 add primary key(字段名);

mysql> alter table t8 add primary key(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除主键

• alter table 表名 drop primary key;

mysql> alter table t8 drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

primary key主键
·创建复合主键
- alter table 表名 add primary key(字段名列表);

mysql> create table t5(
    -> name char(10),
    -> class char(7),
    -> status enum("yes","no"),
    -> primary key (name,class)
    -> );
Query OK, 0 rows affected (0.05 sec)

与主键自动增长auto_increment连用

mysql> create table t6(
    -> id int primary key auto_increment,
    -> name char(10),
    -> sex enum("man","woman")
    -> );
Query OK, 0 rows affected (0.17 sec)

3.5 键值使用foreign key外键 创建 删除 查看

外键功能
一插入记录时，字段值在另一个表字段值范围内选择。
使用规则

• 表存储引擎必须是innodb
• 字段类型要一致
• 被参照字段必须要是索引类型的一种(primary key)

创建外键

  -create table表名（
  字段名列表,
  foreign   key(字段名)  references  表名(字段名)     //指定外键
  on update cascade    //同步更新
  on delete cascade     //同步删除
  )engine=innodb;         //指定存储引|擎**

删除外键
- alter table 表名 drop foreign key外键名;

案例2 : foreign key外键·具体要求如下
1.创建员工表yg
2.创建工资表gz,并设置外键实现同步更新与同步删除-测试外键
3.删除外键

1）创建yg表，用来记录员工工号、姓名，其中yg_id列作为主键，并设置自增属性

mysql> create table yg( 
    -> yg_id int primary key auto_increment,
    -> name char(16) 
    -> )engine=innodb;
Query OK, 0 rows affected (0.06 sec)

2）创建gz表，用来记录员工的工资信息
其中gz_id需要参考员工工号，即gz表的gz_id字段设为外键，将yg表的yg_id字段作
为参考键

mysql> create table gz(
    -> gz_id int,
    -> name char(16),
    -> gz float(7,2),
    -> foreign key(gz_id)
    -> references yg(yg_id) on update cascade on delete cascade )engine=innodb;  //创建外键 同步更新、同步删除
Query OK, 0 rows affected, 1 warning (0.10 sec)

3）为yg表添加2条员工信息记录
因yg_id有AUTO_INCREMENT属性，会自动填充，所以只要为name列赋值就可以了。
插入表记录可使用INSERT指令：

mysql> insert into yg(name) values('Jerry'),('Tom');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | Jerry |
|     2 | Tom   |
+-------+-------+

4）为gz表添加2条工资信息记录
同上，数据参考图-2，插入相应的工资记录（gz_id字段未指定默认值，也未设置自
增属性，所以需要手动赋值）：

mysql> insert into gz(gz_id,name,gz)values(1,'Jerry',12000),
(2,'Tom',8000);

mysql> select * from gz;       //确认yg表的数据记录
+-------+-------+----------+ 
| gz_id | name  | gz       |
+-------+-------+----------+
|     1 | Jerry | 12000.00 |
|     2 | Tom   |  8000.00 |
+-------+-------+----------+

mysql> select * from yg;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | Jerry |
|     2 | Tom   |
+-------+-------+
2 rows in set (0.00 sec)

5）验证表记录的UPDATE更新联动
将yg表中Jerry用户的yg_id修改为1234：

mysql> update yg set yg_id=1234 where name='Jerry';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from gz;     //查看gz确认联动修改结果
+-------+-------+----------+
| gz_id | name  | gz       |
+-------+-------+----------+
|  1234 | Jerry | 12000.00 |
|     2 | Tom   |  8000.00 |
+-------+-------+----------+
2 rows in set (0.00 sec)

6）验证表记录的DELETE删除联动
删除yg表中用户Jerry的记录

mysql> delete from yg where name='Jerry';
Query OK, 1 row affected (0.01 sec)

mysql> select * from gz;
+-------+------+---------+
| gz_id | name | gz      |
+-------+------+---------+
|     2 | Tom  | 8000.00 |
+-------+------+---------+
1 row in set (0.00 sec)

7）删除指定表的外键约束
先通过SHOW指令获取表格的外键约束名称：

mysql> show create table gz\G;
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int DEFAULT NULL,
  `name` char(16) DEFAULT NULL,
  `gz` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`),
  CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE           //其中"gz_ibfk_1"即删除外键约束时要用到的名称。
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table gz drop foreign key gz_ibfk_1;     //删除操作
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table gz\G;         //确认删除结果
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int DEFAULT NULL,
  `name` char(16) DEFAULT NULL,
  `gz` float(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)