MySQL表操作
引擎介绍
我们可以简单的理解为存储引擎就是针对相同的数据采用的不同的存取策略
show engines; # 查看所有的存储引擎
# 需要掌握的存储引擎
1.MyISAM
MySQL5.5及之前版本默认的存储引擎
存取数据的速度快 但是功能较少 安全性较低
2.InnoDB
MySQL5.5之后版本默认的存储引擎
存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能
安全性较高
3.Memory
基于内存的存储引擎 存取数据极快 但是断电立刻丢失
4.BlackHole
黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
ps:MySQL中默认是大小写不敏感的(忽略大小写)
# 不同存储引擎之间底层文件的区别
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
ps:windows cmd终端鼠标右键的意思就是粘贴
InnoDB
.frm 表结构
.ibd 表数据、表索引(加快数据查询)
MyISAM
.frm 表结构
.MYD 表数据
.MYI 表索引(加快数据查询)
Memory
.frm 表结构
BlackHole
.frm 表结构
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
表介绍
表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段
还记得我们之前写过的‘员工信息表作业’么？存储这员工信息的文件是这样的：
id,name,age,sex,phone,job
1,Alex,83,female,13651054608,IT
2,jason,26,male,13304320533,Tearcher
3,nezha,25,male,13332353222,IT
4,boss_jin,40,male,13332353333,IT
如果把上面这个文件改成一张表,应该是下面这个样子
| id | name | age | sex | phone | job |
|---|---|---|---|---|---|
| 1 | Alex | 83 | female | 13651054608 | IT |
| 2 | jason | 26 | male | 13304320533 | Teacher |
| 3 | nezha | 25 | male | 13332353222 | IT |
| 4 | boss_jin | 40 | male | 13332353333 | IT |
id,name,age,sex,phone,job称为字段，其余的，一行内容称为一条记录
创建表
#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
# 建表
mysql> create database staff;
Query OK, 1 row affected (0.00 sec)
mysql> use staff;
Database changed
mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_staff |
+-----------------+
| staff_info |
+-----------------+
1 row in set (0.00 sec)
mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select id,name,sex from staff_info;
Empty set (0.00 sec)
mysql> select * from staff_info;
Empty set (0.00 sec)
# 插入数据
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from staff_info;
+------+----------+------+--------+-------------+---------+
| id | name | age | sex | phone | job |
+------+----------+------+--------+-------------+---------+
| 1 | Alex | 83 | female | 13651054608 | IT |
| 2 | Egon | 26 | male | 13304320533 | Teacher |
| 3 | nezha | 25 | male | 13332353222 | IT |
| 4 | boss_jin | 40 | male | 13332353333 | IT |
+------+----------+------+--------+-------------+---------+
4 rows in set (0.00 sec)
查看表结构
查看表结构有两种方式：
describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构
虽然desc命令可以查看表的定义，但是其输出的信息还不够全面，为了得到更全面的表定义信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）
# desc和show create table
mysql> describe staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show create table staff_info\G;
*************************** 1. row ***************************
Table: staff_info
Create Table: CREATE TABLE `staff_info` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`sex` enum('male','female') DEFAULT NULL,
`phone` bigint(11) DEFAULT NULL,
`job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
mysql中的数据类型
字段类型之整型
有负号 没有负号
tinyint 1bytes -128~127 0~255
smallint 2bytes
int 4bytes
bigint 8bytes
1.验证整型是否自带负号
create table t6(id tinyint);
insert into t6 values(-129),(256);
ps:发现自动填写为两个边界值 数据失真 没有实际意义
上述所有的整型类型默认都会带有负号
2.自定义移除负号
'''unsigned 约束条件之一 意思是不需要负号'''
create table t7(id tinyint unsigned);
insert into t7 values(-129),(256);
"""
插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)
方式1:命令临时修改
set session sql_mode='strict_trans_tables' 当前客户端操作界面有效
set global sql_mode='STRICT_TRANS_TABLES' 服务端不重启永久有效
方式2:配置文件永久修改
[mysqld]
sql_mode='STRICT_TRANS_TABLES'
"""
字段类型之浮点型
float
double
decimal
三者都可以存储浮点型数据 但是各自的精确度不一致
使用方式
float(255,30) # 第一个数表示总共多少位 第二个数表示小数占多少位
double(255,30)
decimal(65,30)
验证精确度问题
create table t8(id float(255,30));
create table t9(id double(255,30));
create table t10(id decimal(65,30));
insert into t8 values(1.11111111111111111111111111111);
insert into t9 values(1.11111111111111111111111111111);
insert into t10 values(1.11111111111111111111111111111);
float < double < decimal
"""
一般情况下float足够使用了
如果想追求非常完美的精确度 可以使用字符串来代替
"""
字段类型之字符类型
char 定长
char(4)
最大只能存储四个字符 如果超过范围则直接报错
如果不超出范围 则用空格填充至四个字符
varchar 变长
varchar(4)
最大只能存储四个字符 如果超过范围则直接报错
如果不超出范围 则有几位就存几位
验证两者的区别
create table t11(id int, name char(4));
create table t12(id int, name varchar(4));
1.结果验证 超出范围两者都会报错
注意sql_mode='strict_trans_tables'
2.验证定长和变长特性
char_length() # 统计字段数据的长度
"""
默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格
然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
上述目录是替换 不是新增 所以之前的配置也要写上
"""
3.char VS varchar
char
整存整取 速度快
浪费存储空间
varchar
节省存储空间
存取数据的速度慢于char
"""
char(4)
a son jacktom lili
varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
存取数据都需要操作报头(耗时)
存储人的姓名>>>:varchar
"""
两者使用频率都很高 现在默认很多时候是varchar
数字的含义
数字大部分情况下都是用来限制字段的存储长度 但是整型除外!!!
不是用来限制存储的长度 而是展示的长度
create table t13(id int(3));
create table t14(id int(3) zerofill);
总结
以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
eg:
00000000013
00123123031
字段类型之枚举与集合
枚举
多选一
eg:性别(男 女 其他)
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
集合
多选多(包含多选一)
eg:爱好(唱 跳 rap)
create table t16(
id int,
name varchar(32),
hobbies set('read','run','music','rap')
);
字段类型之日期类型
date 年月日
datetime 年月日时分秒
time 时分秒
year 年份
create table t17(
id int,
name varchar(32),
birth date,
reg_time datetime,
study_time time,
join_time year
);
insert into t17 values(1,'jason','2022-11-11','2000-11-11 11:11:11','11:11:11','1995');
表的完整性约束
字段约束条件
"""
insert into 表名 vlaues() # 默认按照创建表的字段顺序添加
insert into 表名(字段) vlaues() # 可以自定义字段顺序
"""
1.unsigned 无负号
id int unsigned
2.zerofill 零填充
id int zerofill
3.not null 非空
name varchar(32) not null
4.default 默认值
name varchar(32) default 'jason'
5.unique 唯一值
id int unique 单列唯一
host varchar(32)
port int
unique(host,port) 联合唯一
约束条件
1.primary key主键
1.单从约束角度上而言主键等价于非空且唯一 not null unique
create table t1(
id int primary key,
name varchar(32)
);
2.InnoDB存储引擎规定一张表必须有且只有一个主键
2.1.如果创建的表中没有主键也没有非空且唯一的字段 那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询:新华字典的目录)
2.2.如果创建的表中没有主键但是有非空且唯一的字段 那么InnoDB存储引擎会自动将该字段设置为主键
create table t2(
nid int not null unique,
sid int not null unique,
uid int not null unique,
name varchar(32)
);
3.创建表的时候都应该有一个'id'字段 并且该字段应该作为主键
uid、sid、pid、gid、cid、id
补充说明
id int primary key 单列主键
sid int,
nid int,
primary key(sid, nid) 联合主键
2.auto_increment自增
该约束条件不能单独使用 必须跟在键后面(主要配合主键一起使用)
create table t3(
id int auto_increment
);
there can be only one auto column and it must be defined as a key
create table t4(
id int primary key auto_increment,
name varchar(32)
);
补充说明:自增的特点
自增的操作不会因为执行删除数据的操作而回退或者重置
delete from
如果非要重置主键 需要格式化表
truncate 表名; # 删除表数据并重置主键值
约束条件之外键
1.外键前戏
需要创建一张员工表
id name gender dep_name dep_desc
上述表的缺陷
1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
2.字段数据反复存取 浪费存储空间(不严重 无所谓)
3.表的扩展性极差 牵一发动全身(很严重 效率极低)
优化操作>>>:拆表
id name gender
id dep_name dep_desc
拆表之后解决了上述的三个问题 但是出现了一个致命的缺陷
解决措施
id name gender dep_id
添加一个部门编号字段填写部门数据的主键值
外键字段
专门用于记录表与表之间数据的关系
2.外键字段的创建
外键字段是用来记录表与表之间数据的关系 而数据的关系有四种
一对多关系
多对多关系
一对一关系
没有关系
2.1.表数据关系的判定 >>>: '换位思考'
针对员工表和部门表判断数据关系
1.先站在员工表的角度
问:一条员工数据能否对应多条部门数据
翻:一名员工能否属于多个部门
答:不可以
2.再站在部门表的角度
问:一条部门数据能否对应多条员工数据
翻:一个部门能否拥有多个员工
答:可以
完成换位思考之后得出的答案 一个可以一个不可以
那么表关系就是"一对多"
部门是一 员工是多
针对'一对多'的关系 外键字段建在多的一方
ps:没有多对一 统一称为'一对多'
Foreign Key
1.先写普通字段
2.然后再写外键字段
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
"""
1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除
有点不太好 操作限制性太强
"""
级联更新、级联删除
被关联数据一旦变动 关联的数据同步变动
create table emp1(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(32)
);
"""
扩展:
在实际工作中 很多时候可能并不会使用外键
因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
我们为了能够描述出表数据的关系 又不想使用外键
自己通过写SQL 建立代码层面的关系
"""
修改表结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
# alter操作非空和唯一（了解）
create table t(id int unique,name char(10) not null);
#去掉null约束
alter table t modify name char(10) null;
# 添加null约束
alter table t modify name char(10) not null;
# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;
alter处理null和unique约束
# alter操作主键（了解）
1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
);
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
# 为表添加外键（了解）
创建press表
CREATE TABLE `press` (
`id` int(11) NOT NULL,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ；
创建book表
CREATE TABLE `book` (
`id` int(11) DEFAULT NULL,
`bk_name` char(12) DEFAULT NULL,
`press_id` int(11) NOT NULL,
KEY `press_id` (`press_id`)
) ；
为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id);
删除外键
alter table book drop foreign key fk_id;
# 示例
mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 表重命名
mysql> alter table staff_info rename staff;
Query OK, 0 rows affected (0.00 sec)
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 删除sex列
mysql> alter table staff drop sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
# 添加列
mysql> alter table staff add sex enum('male','female');
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 修改id的宽度
mysql> alter table staff modify id int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 创建自增id主键
mysql> alter table staff modify id int(4) primary key auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 添加联合主键
mysql> alter table staff add primary key (sname,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除主键
mysql> alter table staff drop primary key;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
# 创建主键id
mysql> alter table staff add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc staff;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | | |
| sex | enum('male','female') | YES | | NULL | |
| age | int(3) | NO | | 0 | |
| phone | bigint(11) | YES | | NULL | |
| job | varchar(11) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
删除表
DROP TABLE 表名;
多表结构的创建与分析
如何找出两张表之间的关系
分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
建立表之间的关系
#一对多或称为多对一
三张表：出版社，作者信息，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
# sql示例
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
# 其他示例
班级和学生
一个班级可以对应多个学生，但一个学生只能对应一个班级
主机和机房
一个机房可以有多台主机，但是一个主机只能属于一个机房
#多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
# sql示例
=====================多对多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户
关联方式：foreign key+unique
# sql示例
create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
#增加客户
mysql> insert into customer(name,qq,phone) values
-> ('韩蕾','31811231',13811341220),
-> ('杨澜','123123123',15213146809),
-> ('翁惠天','283818181',1867141331),
-> ('杨宗河','283818181',1851143312),
-> ('袁承明','888818181',1861243314),
-> ('袁清','112312312',18811431230)
mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
-> ('脱产1班',3),
-> ('周末1期',4),
-> ('周末1期',5)
-> ;
# 其他示例
例一：一个用户只有一个博客
用户表：
id name
1 egon
2 alex
3 wupeiqi
博客表
fk+unique
id url name_id
1 xxxx 1
2 yyyy 3
3 zzz 2
例二：一个管理员唯一对应一个用户
用户表：
id user password
1 egon xxxx
2 alex yyyy
管理员表：
fk+unique
id user_id password
1 1 xxxxx
2 2 yyyyy
本文作者：ycmyay
本文链接：https://www.cnblogs.com/ycmyay/p/17382135.html
版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步