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

作者: Bigyong | 来源:发表于2020-12-02 23:22 被阅读0次

MySQL注入总结
MySQL数据库基础(二)表结构
hive--》MySQL,不同名，不同列
《Gradle构建SpringBoot学习笔记》第四章：Spri
Linux下的MySQL
MySQL常用命令
My SQL基础操作
基础概念
学习MySQL数据库
MySQL进阶：表结构设计

目录
一、表结构
1.1 约束条件
二、修改表结构
2.1 语法结构
2.2 添加新字段
2.3 修改字段类型
2.4 修改字段名
2.5 删除字段
2.6 修改表名
三、MySQL键值
3.1 MySQL键值概述
3.2 索引优缺点
3.3 键值使用 index普通索引创建删除查看
3.4 键值使用 key主键创建删除查看
3.5 键值使用foreign key外键创建删除查看

一、表结构

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

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

设置约束条件

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)

MySQL注入总结
01 MySQL数据库结构及常用函数一、MySQL数据库结构 MySQL数据库采用库=>表=>列=>数据的存储结...
MySQL数据库基础(二)表结构
目录一、表结构1.1 约束条件二、修改表结构2.1 语法结构2.2 添加新字段2.3 修改字段类型2.4 修改字段...
hive--》MySQL,不同名，不同列
1.hive--》MySQL 从目标机上建立与hive表结构类似的MySQL数据库表：切换数据库： [root@...
《Gradle构建SpringBoot学习笔记》第四章：Spri
1.MySQL数据库 1.1数据库结构： 1.2user表结构： 1.3user表数据： 1.4commodity...
Linux下的MySQL
MySQL简述 MySQL是一种关系型数据库关系型数据库：理论基础：关系代数+集合论特点：用二维表组织数据，一行表...
MySQL常用命令
数据库表三部分组成 1）表结构2）表数据3）表索引常用命令关闭mysql 开启mysql 登录mysql 查看...
My SQL基础操作
1）mysql存储结构：数据库 -> 表 -> 数据 sql语句 2）管理数据库： 3) 管理表： 4) 管...
基础概念
1、基本结构 mysql MongoDB 数据库数据库表集合行文档数据库展...
学习MySQL数据库
了解MySQL: 1.MySQL 是一个数据库管理系统，管理多个数据库，及数据库之间的表关系、有结构、表数据. ...
MySQL进阶：表结构设计
摘要：MySQL 表结构设计是MySQL架构设计最为基础的工作，优化表结构设计是开发规范，避免后期因为表结构设计不...