表的基本操作 – FGP

表

数据库相当于一个仓库，仓库里面的东西进行归类，称为表

使用数据库与查看数据库里面的表

use 数据库名; # 使用数据库

show tables; # 查看当前数据库的所有表

创建表

创建一个student表

create table student(
id int,
name varchar(30),
age int
);

show create table student;

创建表（高级）

create table student(
id int auto_increment primary key comment'主键id',
age int not null comment'年龄',
name varchar(30) not null comment'姓名',
phone varchar(20) comment'电话号码',
address varchar(200) default'未知' comment'地址'
)engine=innodb;

age int name 字段

auto_increment 自动增长

primary key 主键 最主要的 靠它来区分表 

comment 注释

not null 不为空（必填）

default 默认值

engine 数据库引擎

查看表结构

desc teacher;

删除表

drop table ….; # 删除多张表可以用逗号隔开

drop table aaa, bbb;

修改表

alter table ‘ 表名’ ‘关键字’ ‘字段名’ ..;

添加字段（add）：

alter table student add address varchar(200) default'未知';

在指定字段后添加字段：

alter table student add grade varchar(50) not null after age;

在首位置添加字段：

alter table student add phone varchar(20) first;

删除字段（drop）：

alter table student drop phone;

更改字段（change 更改字段名和数据类型）：

alter table student change grade grades int(11);

更改数据类型（modify）：

alter table student modify grades varchar(50);

重命名表名（rename to）：

alter table student rename to students;

外键

添加外键

#学生表
create table student(
     stuId int(10) unsigned primary key auto_increment,
     name varchar(20) not null,
     age tinyint unsigned not null,
     phone varchar(20) unique,
     address text
    ) engine=innodb charset=gbk;

#餐厅表
create table eatery(
    id int(10) unsigned primary key auto_increment,
    stuId int(10) unsigned,
    cost decimal(10,4) unsigned not null default'0',
    balance decimal(10,4) unsigned not null default'0',
    foreign key(stuId) references student(stuId)
    ) engine=innodb charset=gbk; 

#单独添加外键
alter table eatery add foreign key(stuId) references student(stuId);

如何删除外键

#查看外键外名：
show create table eatery;
+--------+---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------+
| Table  | Create Table
                                                  |
+--------+---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------+
| eatery | CREATE TABLE `eatery` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `stuId` int(10) unsigned DEFAULT NULL,
 `cost` decimal(10,4) unsigned NOT NULL DEFAULT '0.0000',
 `balance` decimal(10,4) unsigned NOT NULL DEFAULT '0.0000',
 PRIMARY KEY (`id`),
 KEY `stuId` (`stuId`),
 CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `student` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------+

#删除外键：
alter table eatery drop foreign key eatery_ibfk_1;

desc eatery;
+---------+------------------------+------+-----+---------+----------------+
| Field   | Type                   | Null | Key | Default | Extra          |
+---------+------------------------+------+-----+---------+----------------+
| id      | int(10) unsigned       | NO   | PRI | NULL    | auto_increment |
| stuId   | int(10) unsigned       | YES  | MUL | NULL    |                |
| cost    | decimal(10,4) unsigned | NO   |     | 0.0000  |                |
| balance | decimal(10,4) unsigned | NO   |     | 0.0000  |                |
+---------+------------------------+------+-----+---------+----------------+

1. MUL表示可重复

外键的三种操作严格、置空和级联

严格性操作

也就是增删改查表的规范

置空操作

链接外键的表中，原表删除的数据会成为NULL

级联操作

链接外键的表中，原表删除的数据会全部删除

注意：留给外键进行删除数据的时候使用置空，更新使用级联

置空和级联演示

创建主表student

create table student(
    stuId int(5) unsigned auto_increment primary key,
    name varchar(20)
    );

创建从表eatery

create table eatery(
    id int unsigned auto_increment primary key,
    stuId int(5) unsigned,
    money decimal(10,4) unsigned,
    foreign key (stuId) references student(stuId) on delete set null on update cascade
    1. on delete set null 删除使用置空操作
    1. on update cascade 更新使用级联操作
    );

插入数据


mysql> insert into student values (10001, 'Jerry'), (null, 'Tom'), (null, 'Kaly');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select stuId, name from student;
+-------+-------+
| stuId | name  |
+-------+-------+
| 10001 | Jerry |
| 10002 | Tom   |
| 10003 | Kaly  |
+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into eatery values (1, 10001, 20.98), (null, 10003, 17.21), (null, 10001, 14.50), (null, 10001, 13.89), (null, 10002, 25.30);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from eatery;
+----+-------+---------+
| id | stuId | money   |
+----+-------+---------+
|  1 | 10001 | 20.9800 |
|  2 | 10003 | 17.2100 |
|  3 | 10001 | 14.5000 |
|  4 | 10001 | 13.8900 |
|  5 | 10002 | 25.3000 |
+----+-------+---------+
5 rows in set (0.00 sec)

删除 stu 表中 stuId 时， eatery 表外键的数据会变为 NULL ，这里是置空操作

mysql> delete from student where stuId=10001;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-------+------+
| stuId | name |
+-------+------+
| 10002 | Tom  |
| 10003 | Kaly |
+-------+------+
2 rows in set (0.00 sec)

mysql> select * from eatery;
+----+-------+---------+
| id | stuId | money   |
+----+-------+---------+
|  1 |  NULL | 20.9800 |
|  2 | 10003 | 17.2100 |
|  3 |  NULL | 14.5000 |
|  4 |  NULL | 13.8900 |
|  5 | 10002 | 25.3000 |
+----+-------+---------+
5 rows in set (0.00 sec)

更新stu表中stuId时，eatery表外键的数据会相应更改，这里是级联操作

mysql> update student set stuId=10004 where name='Kaly';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+-------+------+
| stuId | name |
+-------+------+
| 10002 | Tom  |
| 10004 | Kaly |
+-------+------+
2 rows in set (0.00 sec)

mysql> select * from eatery;
+----+-------+---------+
| id | stuId | money   |
+----+-------+---------+
|  1 |  NULL | 20.9800 |
|  2 | 10004 | 17.2100 |
|  3 |  NULL | 14.5000 |
|  4 |  NULL | 13.8900 |
|  5 | 10002 | 25.3000 |
+----+-------+---------+
5 rows in set (0.00 sec)

实体和实体之间的关系

数据对象彼此之间相互连接的方式称为联系(Relationship)，也称为关系，联系可分为以下 3 种类型

一对一联系 (1 ∶ 1) 例如，部门与经理的联系是一对一的
一对多联系 (1 ∶ N) 例如，某校教师与教的课程之间存在一对多的
多对多联系 (M ∶ N) 例如，学生与课程间的联系是多对多的

Codd第一范式：确保每列原子

第一范式：

确保每一个字段的数据不能再分了：确保每个字段的原子性
例如一条时间数据：2018-2019
这里应该将其拆开成两个数据，开始时间2018，结束时间2019

Codd第二范式：非键字段必须依赖与键字段，别没事找事

第二范式：

一个表只描述一件事情，不能扯淡
比如学生表就放学生的信息，不放工资数据，没关系的信息就别放在一张表

Codd第三范式：消除传递依赖

第三范式：

例如成绩表，一般情况下不把总分列出来，只需要将成绩表中语数外相加就可以
而高考成绩需要把总分数据列出来，根据具体项目需求确定是否消除传递依赖

表