表的基本操作 – 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  |                |
+---------+------------------------+------+-----+---------+----------------+

# 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
	# on delete set null 删除使用置空操作
	# 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第三范式：消除传递依赖

第三范式：

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

表