MySQL表操作
程序员文章站
2022-04-29 18:39:13
MySQL表操作 存储引擎:存储数据的方式 Innodb存储引擎: Myisam存储: Memory存储引擎: 查看当前的默认存储引擎: 表介绍 表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段 还记得我们之前写过的‘员工信息表作业’么?存储这 ......
mysql表操作
存储引擎:存储数据的方式
innodb存储引擎(数据持久化): mysql5.6之后的默认的存储引擎 myisam存储(数据持久化):mysql5.5之前的默认存储引擎 memory存储引擎(断点数据消失):数据存储在内存中,也就是说数据断点消失
innodb存储引擎:
# 数据和索引存储在一起 2个文件 # 数据索引一个文件 # 表结构一个文件 # 原子性操作:不能再拆分 # innodb存储引擎:支持事务,支持行级锁,表级锁,支持外键 # 支持事务:为了保证数据的完整性,将多个操作变成原子性操作,保证数据安全 # 支持行级锁:修改的行少的时候,修改数据频繁的操作 # 表级锁:批量修改多行的时候用,对于大量数据的同时修改 # 支持外键:约束两张表中的关联字段不能随意的添加和删除,降低数据增删改的出错率
myisam存储:
# 数据和索引不存储在一起 3个文件 # 数据 # 索引 # 表结构 #myisam存储只支持表锁
memory存储引擎:
# memory存储引擎(断点数据消失):数据存储在内存中,也就是说数据断点消失 # 表结构
# 查看当前的默认存储引擎:
# show variables like %engin%;
# 面试题: # 了解mysql的存储引擎吗? # innodb myisam memory # 项目用了什么存储引擎,为什么? # innodb # innodb 支持事务 行锁,表锁,外键 # 多个用户操作的过程中对同一张表的数据同时做修改, # innodb支持行级锁,所以用了这个存储引擎 # 为了适应程序未来的扩展,扩展新功能的时候可能会用到,涉及要维护的数据的完整性 # 项目中有两张表,之间的外键关系是什么···,一张表的修改或删除比较频繁,怕出错所以做了外键约束
表介绍
表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段
还记得我们之前写过的‘员工信息表作业’么?存储这员工信息的文件是这样的:
id,name,age,sex,phone,job 1,anwen,23,female,13651054608,it 2,yage,22,male,13304320533,tearcher 3,wudi,25,male,13332353222,it 4,juses,40,male,13332353333,it
如果把上面这个文件改成一张表,应该是下面这个样子:
id,name,age,sex,phone,job称为字段,其余的,一行内容称为一条记录
id | name | age | sex | phone | job |
---|---|---|---|---|---|
1 | anwen | 23 | male | 13651054608 | it |
2 | yage | 20 | female | 13304320533 | it |
3 | wudi | 25 | male | 13804310533 | it |
4 | juses | 22 | male | 13802320533 | it |
创建表
#语法: 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)
查看表结构
mysql> describe staff_info; mysql> desc staff_info; show create table staff_info\g;
修改表结构
语法: 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;
删除表
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+一张新的表
多对多:
=====================多对多===================== 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
一对一:
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), -> ('ll','123123123',15213146809), -> ('xx','283818181',1867141331), -> ('yy','283818181',1851143312), -> ('zz','888818181',1861243314), -> ('qq','112312312',18811431230) mysql> #增加学生 mysql> insert into student(class_name,customer_id) values -> ('1班',3), -> ('2班',4), -> ('3班',5) -> ;
上一篇: Python3 中的Number