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.删除外键
MySQL数据库基础(二)表结构_第1张图片
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)

你可能感兴趣的